· 6 years ago · Jan 16, 2020, 05:14 PM
1USE [BD1d_pn_1B]
2
3GO
4
5/* 1. */
6
7WITH cte_WZ(IDz, wz) As
8(
9 SELECT IDzamówienia, SUM(CenaJednostkowa*Ilość)
10 FROM PozycjeZamówienia
11 GROUP BY IDzamówienia
12)
13 SELECT * FROM cte_WZ;
14
15 /* lub tak */
16
17 WITH cte_WZ As
18(
19 SELECT IDzamówienia IDz, SUM(CenaJednostkowa*Ilość) wz
20 FROM PozycjeZamówienia
21 GROUP BY IDzamówienia
22)
23 SELECT * FROM cte_WZ;
24
25/* 2. Korzystajac z definicji cte_WZ (zad1), zdeiniowac CTE, nazwane (cte_Avg_WZ),
26 ktore bedzie wystwietlac srednia wartosc zamowien (avg_wz) w bazie.*/
27
28 WITH cte_WZ(IDz, wz) As
29(
30 SELECT IDzamówienia, SUM(CenaJednostkowa*Ilość)
31 FROM PozycjeZamówienia
32 GROUP BY IDzamówienia
33),
34 cte_Avg_WZ(avg_wz) AS
35 (
36 SELECT AVG(wz)
37 FROM cte_WZ
38 )
39 SELECT * FROM cte_Avg_WZ;
40
41 /* przykład */
42
43 WITH cte_WZ(IDz, wz) As
44(
45 SELECT IDzamówienia, SUM(CenaJednostkowa*Ilość)
46 FROM PozycjeZamówienia
47 GROUP BY IDzamówienia
48),
49 cte_Avg_WZ(avg_wz) AS
50 (
51 SELECT AVG(wz)
52 FROM cte_WZ
53 )
54 SELECT IDz, wz, (0.9*(SELECT avg_wz FROM cte_Avg_WZ)) [90%ofAVG]
55 FROM cte_WZ
56 WHERE wz < 0.9*(SELECT avg_wz FROM cte_Avg_WZ);
57
58/* 3. Korzystajac z definicji cte_Avg_WZ (zad 2.) zdefiniowac CTE, nazwane cte_ZUpa, któe bedzię wystwetlac zamowienia (id, wartosc) o wartosci
59 przekraczajacej srdnia wartosc zamowienia */
60
61 WITH cte_WZ(IDz, wz) As
62(
63 SELECT IDzamówienia, SUM(CenaJednostkowa*Ilość)
64 FROM PozycjeZamówienia
65 GROUP BY IDzamówienia
66),
67 cte_Avg_WZ(avg_wz) AS
68 (
69 SELECT AVG(wz)
70 FROM cte_WZ
71 ),
72
73 cte_ZUpA(ID, [wartość]) AS
74 (
75 SELECT IDZamówienia, SUM(CenaJednostkowa*Ilość)
76 FROM PozycjeZamówienia
77 GROUP BY IDzamówienia
78 HAVING SUM(CenaJednostkowa*Ilość) > (SELECT avg_wz FROM cte_Avg_WZ)
79 )
80 SELECT * FROM cte_ZUpA;
81
82 /* wersja 2 */
83
84 /* nie zdazylem XD */
85
86/* 4. Zdefiniowac CTE pod nazwa cte_First_Last, przy uzyciu ktorego bedzie mozna uzyskac daty: pierwszego i ostatniego
87 zamowienia, przyjetych przez kazdego z pracownikow */
88
89 WITH cte_First_Last(IDp, dF, dL) AS
90 (
91 SELECT IDpracownika, MIN(DataZamówienia), MAX(DataZamówienia)
92 FROM Zamówienia
93 GROUP BY IDpracownika
94 )
95 SELECT * FROM cte_First_Last
96 WHERE IDp IS NOT NULL;
97
98/* 5. Zdefiniowac widok v_Adresy_Klientow, wystwielajacy zestawienie adresowe i osbote kontaktowa (id, firmy, nazwa firmy, adres,
99 miejscowosc, kraj, przedstawiciel). Uzyc widoku do wystwietlania listy adresowej klientow, posrotowanej alfabetycznie wg krajow i nazwa
100 firm. Odfiltrowac adresy, w ktorych ktrekolwiek pole ma wartosc NULL */
101
102 DROP VIEW IF EXISTS dbo.v_Adresy_Klientów;
103 GO
104 CREATE VIEW v_Adresy_Klientów(IDk, klient, adres, kod, miejscowość, kraj, przedstawiciel) AS
105 (
106 SELECT IDklienta, NazwaFirmy, Adres, KodPocztowy, Miasto, Kraj, Przedstawiciel
107 FROM Klienci
108 )
109 GO
110 SELECT IDk, klient,adres, kod, miejscowość, kraj
111 FROM v_Adresy_Klientów
112 WHERE adres IS NOT NULL AND kod IS NOT NULL AND miejscowość IS NOT NULL AND kraj IS NOT NULL
113 ORDER BY kraj,klient;
114
115 /* Za pomocą poniższego polecenia utworzyć tabelę Produkty_Kopia(): */
116
117 DROP TABLE IF EXISTS dbo.Produkty_Kopia;
118 GO
119 SELECT p.IDproduktu IDp, p.NazwaProduktu produkt, k.NazwaKategorii kategoria, p.CenaJednostkowa cena
120 INTO Produkty_Kopia
121 FROM Produkty p JOIN Kategorie k ON p.IDkategorii = k.IDkategorii;
122
123/* 6. ZDEFINIOWAC WIDOK V_pOKAz_pRODUKTY_kOPIA, ktory bedzie wyswietlal zawartosc tabeli */
124
125DROP VIEW IF EXISTS dbo.v_Pokaz_Produkty_Kopia;
126GO
127CREATE VIEW v_Pokaz_Produkty_Kopia AS
128(
129 SELECT * FROM Produkty_Kopia
130);
131SELECT * FROM v_Pokaz_Produkty_Kopia;
132
133/* 7. P */
134
135/* dodawanie czy ki chuj */
136
137INSERT INTO v_Pokaz_Produkty_Kopia
138VALUES(1001, 'Ser Sułtański', 'Nabiał', 69.99, 100, 0);
139
140INSERT INTO v_Pokaz_Produkty_Kopia(stan, cena, wycofany, IDp, produkt, kategoria)
141VALUES(50, 29.99, 1 , 1002, 'Ser bielski', 'Nabiał');
142
143SELECT *
144FROM v_Pokaz_Produkty_Kopia
145WHERE IDp >= 1001;
146
147UPDATE v_Pokaz_Produkty_Kopia
148SET cena = CONVERT(DECIMAL(10,2), (cena - 0.25*cena))
149WHERE IDp = 1001
150
151SELECT *
152FROM v_Pokaz_Produkty_Kopia
153WHERE IDp >= 1001
154
155-- c
156
157DELETE FROM v_Pokaz_Produkty_Kopia
158WHERE IDp = 1001;
159SELECT *
160FROM v_Pokaz_Produkty_Kopia
161ORDER BY IDp DESC;