· 6 years ago · Jan 16, 2020, 05:46 PM
1use BD1d_cz_1B
2
3go
4
5/*1.zdefiniowac wspolne wyrazenia tabelowe (CTE), nazwane cte_WZ, które bedzie wyswietlac numery i wartossco zamowien (IDz, wz)*/
6with cte_WZ(IDz, wz) AS
7(
8select IDzamówienia, SUM(CenaJednostkowa)
9from PozycjeZamówienia
10group by IDzamówienia
11)select IDz from cte_WZ
12
13with cte_WZ AS
14(
15select IDzamówienia IDz, SUM(CenaJednostkowa*Ilość) wz
16from PozycjeZamówienia
17group by IDzamówienia
18)select * from cte_WZ;
19/*2. korzystajac z def cte_WZ zdefiniowac CTE, nazwane cte_Avg_WZ, ktore
20bedzie wyswietlac srednia watos zamowien (avg_wz) w bazie*/
21with cte_WZ(IDz, wz) AS
22(
23 select IDzamówienia, SUM(CenaJednostkowa*Ilość)
24 from PozycjeZamówienia
25 group by IDzamówienia
26),
27cte_Avg_WZ(avg_wz) AS
28(
29 select AVG(wz)
30 from cte_WZ
31)
32select IDz, wz, (0.9*(select avg_wz from cte_Avg_WZ)) from cte_WZ
33where wz < 0.9*(select avg_wz from cte_Avg_WZ);
34
35/*3.korzystajac z def cte_Avg_WZ(zad.2) zdefiniować CTE, nazwane cte_Zupa,
36które bedzie wyswietlac zamowienie cośtam cośtam serdnia wartosc zamowienia*/
37with cte_WZ(IDz, wz) AS
38(
39 select IDzamówienia, SUM(CenaJednostkowa*Ilość)
40 from PozycjeZamówienia
41 group by IDzamówienia
42),
43cte_Avg_WZ(avg_wz) AS
44(
45 select AVG(wz)
46 from cte_WZ
47),cte_ZUpA(ID, [wartosc]) AS
48(
49 select IDzamówienia, Sum(CenaJednostkowa*Ilość)
50 from PozycjeZamówienia
51 group by IDzamówienia
52 having SUM(CenaJednostkowa*Ilość) > (select avg_wz
53 from cte_Avg_WZ)
54)
55Select* from cte_ZUpA;
56
57/*druga wersja*/
58with cte_WZ(IDz, wz) AS
59(
60 select IDzamówienia, SUM(CenaJednostkowa*Ilość)
61 from PozycjeZamówienia
62 group by IDzamówienia
63),
64cte_Avg_WZ(avg_wz) AS
65(
66 select AVG(wz)
67 from cte_WZ
68),cte_ZUpA(ID, [wartosc]) AS
69(
70 select IDz, wz
71 from cte_WZ
72 where wz > (select avg_wz
73 from cte_Avg_WZ
74 )
75)
76Select * from cte_ZUpA;
77/*trzecia wersja*/
78with cte_WZ(IDz, wz) AS
79(
80 select IDzamówienia, SUM(CenaJednostkowa*Ilość)
81 from PozycjeZamówienia
82 group by IDzamówienia
83),
84cte_Avg_WZ(avg_wz) AS
85(
86 select AVG(wz)
87 from cte_WZ
88),
89cte_ZUpA(ID, [wartosc], wz_avg) AS
90(
91 select IDz, wz,(select avg_wz from cte_Avg_WZ)
92 from cte_WZ
93 where wz > (select avg_wz
94 from cte_Avg_WZ
95 )
96)
97Select * from cte_ZUpA;
98/*4.zdefiniowac CTE pod nazwa cte_First_Last, przy uzyciu ktorego
99bedzie mozna uzyskac daty : pierwszegoi ostatniego zamowienia
100przyjetego przez kazdego z pracownikow*/
101with cte_First_Last(IDp, dF, dL) as
102(
103 select IDpracownika, min(DataZamówienia), max(DataZamówienia)
104 from Zamówienia
105 group by IDpracownika
106)
107select IDp cast(dF as date),cast(dL as time)
108from cte_First_Last
109where IDp is not null;
110
111/*wyrazeiaaaaaaaaaaaaaa widoki*/
112/*zdefiniowac widok v_Adresy_Klientow, wyswietlajacy zesatawiania adresowe i osobe kontaktowa
113(id firmy, nazwaq firmy, adres, miejscowosc, kraj, przedstawiciel)
114uzyc widoku do wyswietlania listy adresowej klientow, posortowanej alfabetycznie wg. krajow i anzw firm.
115odfiltrowac adresy, w ktorych ktorekolwiek pole ma wartosc null*/
116
117drop view if exists dbo.v_Adresy_Klientów;
118go
119create view dbo.v_Adresy_Klientów (IDk, klient, adres, kod, miejscowość,kraj,przedstawiciel) as
120(
121 select IDklienta, NazwaFirmy,Adres,KodPocztowy,Miasto,Kraj,Przedstawiciel
122 from Klienci
123);
124go
125select IDk, klient, adres, kod, miejscowość,kraj
126from v_Adresy_Klientów
127where adres is not null and kod is not null and miejscowość is not null and kraj is not null
128order by kraj, klient;
129/*NIE WKLADAC ORDER BY DO SELECTA DEFINIUJACEGO PERSPEKTYWE!!!!*/
130
131/*ZA POMOCA PONIZSZEGO POLECENIA UTWORZYC TABELE Produkty_Kopia()*/
132
133drop table if exists dbo.Produkty_Kopia;
134go
135select p.IDproduktu IDp, p.NazwaProduktu produkt, k.NazwaKategorii kategoria, p.CenaJednostkowa cena, p.IlośćJednostkowa stan
136into Produkty_Kopia
137from Produkty p join Kategorie k on p.IDkategorii=k.IDkategorii;
138
139/*6.zdefiniowac widok v_Pokaz_Produkty_Kopia, ktory bedzie wyswietlał zawartosc tabeli*/
140drop view if exists dbo.v_Pokaz_Produkty_Kopia;
141go
142create view v_Pokaz_Produkty_Kopia as
143(
144 select * from Produkty_Kopia
145);
146select * from v_Pokaz_Produkty_Kopia;
147/*uzywajac widoku v_Pokaz_Produkty_Kopia
148 a) dopisac nowy wiersz (1001, 'Ser Sułtański','Nabiał',69.99,100,0)
149 b) zmodyfikowac ten wiersz dopisujac 25% przeceny sprawdzic nowa cene
150 c) usunac ten wiersz. srawdzic zawarosc tabeli Produkty_Kopia*/
151 --a)
152 insert into v_Pokaz_Produkty_Kopia
153 values(1001, 'Ser Sułtański','Nabiał',69.99,100);
154
155 insert into v_Pokaz_Produkty_Kopia(stan, cena, IDp, produkt,kategoria)
156 values(50,29.99,1002, 'Ser bielski','Nabiał')
157 /* moge zmienic kolejnosc kolum a wtedy wprowadzi się tak legitnie*/
158
159 select *
160 from v_Pokaz_Produkty_Kopia
161 where IDp >=1001;
162
163 --b)
164 update v_Pokaz_Produkty_Kopia
165 set cena = convert(decimal(10,2), (cena-0.25*cena))
166 where IDp = 1001;
167 select *
168 from v_Pokaz_Produkty_Kopia
169 where IDp >=1001;
170 --c)
171 delete from v_Pokaz_Produkty_Kopia
172 where IDp =1001;
173 select *
174 from v_Pokaz_Produkty_Kopia
175 order by IDp desc
176/*----------------------------------------------------------------------------------------------------------------------------------------------*/
177 -- Zadania do samodzielnego wykonania:
178/*----------------------------------------------------------------------------------------------------------------------------------------------*/
179/* 09.01. Dla poniższego zapytania (wersja 1), w którym do policzenia średnich wartości zamówień w poszczególnych latach
180 zasosowano złączenie wewnętrzne z tabelę pochodną, proszę zdefiniować alternatywne wspólne wyrażenia tabelowe (wersja 2). */
181
182 -- wersja 1 - - tabele pochodne
183 SELECT YEAR(z1.DataZamówienia) Rok, AVG(a.[Wartość zamówienia]) AS [Średnia wartość zamówienia]
184 FROM Zamówienia z1 JOIN ( SELECT YEAR(z2.DataZamówienia) [Rok], z2.IDzamówienia,
185 SUM(pz2.CenaJednostkowa * pz2.Ilość) [Wartość zamówienia]
186 FROM Zamówienia z2 JOIN PozycjeZamówienia pz2 ON z2.IDzamówienia = pz2.IDzamówienia
187 GROUP BY YEAR(z2.DataZamówienia), z2.IDzamówienia ) a ON z1.IDzamówienia = a.IDzamówienia
188 GROUP BY YEAR(z1.DataZamówienia);
189
190 -- wersja 2 - CTE (wspólne wyrażenie tabelowe):
191with cte_T1(year_T1, IDz, sum_T1) AS
192(
193 SELECT YEAR(z.DataZamówienia), z.IDzamówienia, SUM(pz.CenaJednostkowa * pz.Ilość) suma
194 FROM Zamówienia z JOIN PozycjeZamówienia pz ON z.IDzamówienia = pz.IDzamówienia
195 GROUP BY YEAR(z.DataZamówienia), z.IDzamówienia
196),
197cte_T2(year_T2, avg_T2) AS
198(
199 select year_t1 Rok, AVG(sum_T1)
200 from Zamówienia z1 join cte_T1 a on z1.IDzamówienia = a.IDz
201 group by year_T1
202)
203Select * from cte_T2;
204
205
206/* 09.02. Zdefiniować widok v_Wartość_pozycji_zamówienia, wyświetlający ID zamówienia,
207 nazwę produktu oraz wartość zamówionego produktu WPZ. */
208drop view if exists dbo.v_Wartość_pozycji_zamówienia;
209go
210create view dbo.v_Wartość_pozycji_zamówienia (IDz, nazwa, wartosc) as
211(
212 select z.IDzamówienia, p.NazwaProduktu, p.CenaJednostkowa*pz.Ilość
213 from Zamówienia z join PozycjeZamówienia pz on pz.IDzamówienia=z.IDzamówienia
214 join Produkty p on pz.IDproduktu=p.IDproduktu
215);
216go
217select *
218from v_Wartość_pozycji_zamówienia
219
220/* 09.03. Stosując widok v_Wartość_pozycji_zamówienia, wyświetlić ID zamówienia, datę zamówienia,
221 liczbę pozycji LPZ na zamówieniu oraz sumaryczną wartość zamówienia SWZ. */
222
223
224/* 09.04. Zdefiniować widok v_Wartość_zamówienia, wyświetlający ID zamówienia
225 oraz całkowitą wartość zamówienia CWZ. */
226
227
228/* 09.05. Korzystając z już zdefiniowanych widoków, utwórz widok v_Drukuj_Faktury, zestawiający wszystkie niezbędne dane identyfikujące zamówienie, pracownika
229 oraz klienta w ramach danego zamówienia (ID i data przyjęcia zamówienia, imię i nazwisko pracownika,
230 nazwa firmy klienta i jego adres oraz wartość zamówienia WZ. */
231
232
233/* 09.06. Za pomocą poniższego polecenia utworzyć tabelę Faktury: */
234 DROP TABLE IF EXISTS dbo.Faktury;
235 GO
236 SELECT ID, [data], pracownik, klient, [adres klienta], WZ AS WZ
237 INTO Faktury
238 FROM dbo.v_Drukuj_Faktury;
239
240 -- Sprawdzić zawartość tabeli Faktury
241
242
243/* 09.07. Zdefiniować widok v_Faktury wyświetlający zawartość tabeli Faktury. */
244
245
246/* 09.08. Używając widoku v_Faktury:
247 a) Wyświetlić chronologicznie zamówienia z lipca 1997 r.
248 b) Wyświetlić zamówienia od klientów z Francji lub USA.
249 c) Zmodyfikować wiersze, w których występuje pracownik Michael Suyama, zerując wartość w kolumnnie WZ.
250 Sprawdzić zawartość tabeli Faktury.
251 d) Usunąć wiersze, w których wartość WZ jest mniejsza niż 100.
252 Sprawdzić zawartość tabeli Faktury.
253 */