· 8 years ago · Mar 14, 2018, 10:04 PM
1--Autorka pomocy dydaktycznej: Aldona Biewska
2--Cel: pomoc w przygotowaniu siÄ™ do kolokwium
3--Pomoc dydaktyczna powstała w ramach przedmiotu
4--aplikacje bazodanowe w dniu 30.04.2015 r.
5--pod kierunkiem dra Roberta Fidytka
6--Została wykorzystana baza danych sklep internetowy
7--Drobne poprawki wprowadził dr Robert Fidytek
8
9--==============================================1.
10--Stwórz kolumne liczba_zamowien w tabeli klient
11--z domyślną wartością 0.
12--Napisz wyzwalacz, który po dodaniu nowego zamówienia,
13--będzie aktualizował liczbę zamówień w kolumnie liczba_zamowien. (1w)
14
15ALTER TABLE klient ADD liczba_zamowien INT DEFAULT '0';
16GO
17UPDATE klient SET liczba_zamowien=0;
18GO
19
20--DROP TRIGGER dodaj_do_liczba_zamowien
21
22CREATE TRIGGER dodaj_do_liczba_zamowien ON zamowienie
23AFTER INSERT
24AS
25BEGIN
26 UPDATE klient SET liczba_zamowien=liczba_zamowien + 1 WHERE id_klient IN (SELECT id_klient FROM INSERTED)
27END;
28GO
29
30--test: dodajemy nowe zamówienie dla id_klient=4, liczba_zamowien dla id_klient=4 wzrosła o 1
31INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (44,1,4,'2011-03-06 11:35',100,23);
32SELECT * FROM klient;
33GO
34
35--==========================================2.
36--Napisz funkcję pomocniczą spr_status, która zwraca wartość true jeśli zamówienie ma status dostarczony
37--( w tabeli status nazwa='dostarczenie przesyłki') lub false w innym przypadku.(1f)
38--Napisz wyzwalacz, który po dodaniu nowego rekordu w tabeli zamowienie_status, będzie aktualizował kolumnę
39--liczba_zamowien odejmując zamówienie, wykorzystaj funkcję spr_status (nie przejmujemy sie wartościami ujemnymi).(2w)
40
41
42--DROP FUNCTION dbo.spr_status
43
44CREATE FUNCTION spr_status(@id_zamowienie INT)
45RETURNS BIT
46BEGIN
47IF (SELECT COUNT(s.id_status) FROM status s INNER JOIN zamowienie_status zs ON s.id_status=zs.id_status
48 WHERE zs.id_zamowienie=@id_zamowienie AND s.nazwa='dostarczenie przesyłki' )=1
49 RETURN 1
50RETURN 0
51END;
52GO
53
54--test: sprawdzamy, czy zamówienie ma status dostarczony dla id_zamowienie=1 orad id_zamowienie=21
55SELECT dbo.spr_status(1); --domyślnia baza: tak
56SELECT dbo.spr_status(21); --domyślna baza: nie
57GO
58--DROP TRIGGER odejmij_od_ilosc_zamowien
59
60CREATE TRIGGER odejmij_od_ilosc_zamowien ON zamowienie_status
61AFTER INSERT
62AS
63BEGIN
64 DECLARE @id INT
65 SELECT @id=id_zamowienie FROM inserted
66 IF dbo.spr_status(@id)=1
67 BEGIN
68 UPDATE klient SET liczba_zamowien=liczba_zamowien - 1 WHERE id_klient IN
69 (SELECT z.id_klient FROM zamowienie_status zs INNER JOIN zamowienie z ON z.id_zamowienie=@id)
70 END
71END;
72GO
73
74--test: dodajemy nowe zamówienie ze statusem 'dostarczenie zamowienia' dla id_zamowienie=21,
75--kolumna liczba_zamowien dla klienta o id=19 zmalała o 1.
76
77INSERT INTO zamowienie_status(id_zamowienie,id_status,data_zmiany_statusu,uwagi) VALUES (21,6,'2013-02-16 21:05','brak');
78SELECT * FROM klient;
79GO
80
81
82--=========================================3.
83--Napisz funkcję pomocniczą spr_koszyk, która zwraca ilość zamówień(rodzaju produktu) danego klienta w koszyku podczas jednego zamówienia(transakcji).(2f)
84--Napisz wyzwalacz,który uniemożliwi dodanie kolejnego zamówienia(rodzaju produktu) do koszyka podczas jednego zamówienia (transakcji),
85--jeśli w koszyku są już 4 zamówienia(rodzaju produktu), skorzystaj z funkcji spr_koszyk.(3w)
86
87
88--DROP FUNCTION dbo.spr_koszyk
89
90CREATE FUNCTION spr_koszyk(@id_klient INT,@id_zamowienie INT)
91RETURNS INT
92BEGIN
93 RETURN ( SELECT COUNT(*) FROM koszyk ko INNER JOIN zamowienie zm ON ko.id_zamowienie=zm.id_zamowienie
94 WHERE zm.id_klient=@id_klient AND ko.id_zamowienie=@id_zamowienie )
95END;
96GO
97
98--test: sprawdzamy ilość zamówień(rodzaju produktu) dla id_klient=9 i id_zamowienie=12
99SELECT dbo.spr_koszyk(9,12)
100GO
101
102
103--DROP TRIGGER oganiczenie_zamowien
104
105CREATE TRIGGER oganiczenie_zamowien ON koszyk
106AFTER INSERT
107AS
108BEGIN
109 DECLARE @id_zamowienie INT, @id_klient INT
110 SELECT @id_zamowienie=id_zamowienie FROM inserted
111 SET @id_klient=(SELECT id_klient FROM zamowienie WHERE id_zamowienie=@id_zamowienie)
112 IF dbo.spr_koszyk( @id_klient, @id_zamowienie)>4
113 BEGIN
114 RAISERROR('Max 4 zamowienia na koszyk podczas jednej transakcji',1,2)
115 ROLLBACK
116 END
117END;
118GO
119
120--test: dodajemy dla id_klient=7, zamówienia, jeśli przekroczy 4 wyświetli się komunikat
121INSERT INTO koszyk(id_zamowienie,id_produkt,cena_netto,podatek,ilosc_sztuk) VALUES (4,13,229,23,1100);
122INSERT INTO koszyk(id_zamowienie,id_produkt,cena_netto,podatek,ilosc_sztuk) VALUES (4,14,229,23,1100);
123GO
124--======================================4.
125--Napisz funkcję pomocniczą, która sprawdza poprawność zapisu email(3f)
126--Napisz wyzwalacz, który uniemożliwi modyfikację emaila
127--w tabeli klient na niepoprawny format, wykorzystaj funkcjÄ™ sprawdz_email.(4w)
128
129
130--DROP FUNCTION dbo.sprawdz_email
131
132CREATE FUNCTION sprawdz_email(@email VARCHAR(30))
133RETURNS BIT
134BEGIN
135 IF @email LIKE '%_@_%_.__%'
136 RETURN 1
137 RETURN 0
138END;
139GO
140
141--test: sprawdzamy poprawność maila
142SELECT dbo.sprawdz_email('qweq3wp.pl');
143GO
144SELECT dbo.sprawdz_email('radzetom68@o2.pl');
145GO
146
147
148--DROP TRIGGER spr_dodawany_email;
149
150CREATE TRIGGER spr_dodawany_email ON klient
151AFTER UPDATE
152AS
153BEGIN
154 DECLARE @email VARCHAR(30)
155 SET @email=-1
156 SELECT @email=email FROM inserted;
157 IF dbo.sprawdz_email(@email)=0
158 BEGIN
159 RAISERROR('Nie można modyfikowac maila na zły format!', 1, 2)
160 ROLLBACK
161 END
162END;
163GO
164
165--test: sprawdzamy poprawność aktualizowanego maila dla id_klient=1;
166UPDATE klient SET email='dobry@wp.pl' WHERE id_klient=1;
167UPDATE klient SET email='zle' WHERE id_klient=1;
168GO
169
170--==========================================5.
171--Stwórz widok raport_kategorii(id_kategoria,nazwa, suma_produktów), gdzie
172--id_kategoria, nazwa to kolumny z tabeli kategoria, suma_produktów to
173--suma produktów w danej kategorii, uwzględnij kategorie bez produktów.
174--Utwórz wyzwalacz, który po wykonaniu zapytania:
175--INSERT INTO raport_produktów(nazwa) VALUES ('Nowa kategoria');
176--doda nowÄ… kategoriÄ™ do tabeli kategoria. (5w)
177
178--DROP VIEW raport_kategorii
179
180CREATE VIEW raport_kategorii
181AS
182SELECT k.id_kategoria,k.nazwa,COUNT(p.id_produkt) as suma_produktów
183 FROM kategoria k LEFT JOIN podkategoria pod ON k.id_kategoria=pod.id_kategoria
184 LEFT JOIN produkt p ON pod.id_podkategoria=p.id_podkategoria
185 GROUP BY k.id_kategoria, k.nazwa;
186GO
187
188--test: sprawdzamy utworzony widok
189SELECT * FROM raport_kategorii;
190GO
191
192
193--DROP TRIGGER dodaj_kategorie
194
195CREATE TRIGGER dodaj_kategorie ON raport_kategorii
196INSTEAD OF INSERT AS
197BEGIN
198 DECLARE kursor CURSOR FOR SELECT nazwa, id_kategoria FROM inserted
199 DECLARE @nazwa VARCHAR(20), @id_kategoria INT
200 OPEN kursor
201 FETCH NEXT FROM kursor INTO @nazwa, @id_kategoria
202 WHILE @@FETCH_STATUS=0
203 BEGIN
204 INSERT INTO kategoria(id_kategoria, nazwa) VALUES (@id_kategoria, @nazwa)
205 FETCH NEXT FROM kursor INTO @id_kategoria, @nazwa
206 END
207 CLOSE kursor
208 DEALLOCATE kursor
209END;
210GO
211
212--test: dodajemy kategoriÄ™ do raport_kategorii, nowe kategorie dodaje siÄ™ do tabeli kategoria
213INSERT INTO raport_kategorii(id_kategoria, nazwa) VALUES (70,'NOWA KAT5');
214INSERT INTO raport_kategorii(id_kategoria, nazwa) VALUES (80,'NOWA KAT6');
215SELECT * FROM kategoria;
216GO
217
218--============================6.
219--Stwórz widok widok_producent(id_producent,nazwa,id_adres,ulica,numer,kod,miejscowosc), gdzie
220--id_producent, nazwa to kolumny z tabeli producent,
221--id_adres,ulica,numer,kod,miejscowosc to kolumny tabeli adres
222--Utwórz procedurę pomocniczą dodaj_producenta umożliwająca dodanie nowego producenta
223--i adresu producenta jeśli ten nie istnieje w tabeli adres(1p)
224--Utwórz wyzwalacz producenci_dodaj, który po wykonaniu zapytania:
225--INSERT INTO widok_producent (id_producent,nazwa, id_adres,ulica, numer, kod , miejscowosc) VALUES (123,'polkom',123,'ulica','12','12-222','poznan');
226--doda nowego producenta do tabeli producent oraz doda nowy adres do tabeli adres, jeśli ten nie istnieje (6w)
227--uwagi ze względu na brak autoinkrementacji w insert podajemy id
228
229
230--DROP VIEW widok_producent
231
232CREATE VIEW widok_producent
233AS
234SELECT p.id_producent, p.nazwa,m.id_adres,m.miejscowosc, m.ulica,m.numer,m.kod
235 FROM producent p INNER JOIN adres m ON p.id_producent=m.id_adres;
236GO
237
238--test: sprawdzamy utworzony widok
239SELECT * FROM widok_producent;
240GO
241
242
243--DROP PROCEDURE dodaj_producenta
244
245CREATE PROCEDURE dodaj_producenta
246@id_producent INT,
247@nazwa VARCHAR(30),
248@id_adres INT,
249@miejscowosc VARCHAR(30),
250@ulica VARCHAR(30),
251@numer CHAR(10),
252@kod CHAR(6)
253AS
254BEGIN
255 DECLARE @id_adresPOM INT
256IF NOT EXISTS (SELECT * FROM adres WHERE miejscowosc=@miejscowosc AND ulica=@ulica AND numer=@numer AND kod=@kod)
257BEGIN
258 INSERT INTO adres(id_adres,ulica,numer,kod,miejscowosc) VALUES (@id_adres,@ulica, @numer, @kod, @miejscowosc)
259 INSERT INTO producent(id_producent, id_adres, nazwa) VALUES (@id_producent,@id_adres,@nazwa)
260END
261ELSE
262BEGIN
263 SELECT @id_adresPOM=id_adres FROM adres WHERE miejscowosc=@miejscowosc AND ulica=@ulica AND numer=@numer AND kod=@kod
264 INSERT INTO producent(id_producent, id_adres, nazwa) VALUES (@id_producent,@id_adresPOM,@nazwa)
265END
266END;
267GO
268--test: sprawdzamy procedurę, dodajemy producenta wraz z adresem, w tabeli producent pojawił się producent o id 132 i 133
269--w tabeli adres, pojawił adres o id 132, gdyż drugi producent ma ten sam adres
270
271exec dodaj_producenta 132,'SUPER PRODUCENT',132,'Poznan','Morasko','11','50-500';
272exec dodaj_producenta 133,'JESZCZE LEPSZY PRODUCENT',133,'Poznan','Morasko','11','50-500';
273GO
274
275SELECT * FROM producent;
276SELECT * FROM adres;
277GO
278--DROP TRIGGER producenci_dodaj
279
280CREATE TRIGGER producenci_dodaj
281ON widok_producent
282INSTEAD OF INSERT
283AS
284BEGIN
285 DECLARE @id_producent INT, @nazwa VARCHAR(30),@id_adres INT, @miejscowosc VARCHAR(30), @ulica VARCHAR(30), @numer VARCHAR(30), @kod VARCHAR(30)
286 SELECT @id_producent=id_producent,@nazwa=nazwa,@id_adres=id_adres, @miejscowosc=miejscowosc, @ulica=ulica, @numer=numer, @kod=kod FROM inserted
287 exec dodaj_producenta @id_producent, @nazwa, @id_adres, @miejscowosc, @ulica, @numer, @kod
288END;
289GO
290
291--test: dodajemy do widoku producenta wraz z adresem, w tabeli producent pojawił się producent o id 161 i 157
292--w tabeli adres, pojawił adres o id 161, gdyż drugi producent ma ten sam adres
293
294INSERT INTO widok_producent (id_producent,nazwa, id_adres,ulica, miejscowosc, numer, kod) VALUES (161,'L',161,'Poznan','Batorego','11','50-500');
295INSERT INTO widok_producent (id_producent,nazwa, id_adres,ulica, miejscowosc, numer, kod) VALUES (157,'P',157,'Poznan','Batorego','11','50-500');
296
297SELECT * FROM producent;
298SELECT * FROM adres;
299GO
300
301--============================7.
302--Utwórz procedurę pomocniczą, która zmienia wartość czy_oplacona w tabeli faktura na true (2p)
303--Utwórz funkcję pomoczniczą, która zwraca true jeśli status zamówienia jest 'otrzymano zapłatę',
304--w przciwnym wypadku zwraca false.(4f)
305--Utwórz wyzwalacz, który po dodaniu nowego rekordu w tabeli zamówienie_status ze statusem 'otrzymano zapłatę',
306--będzie aktualizował kolumne czy_opacona w tabeli faktura na true.(7w)
307
308
309--DROP PROCEDURE zmien_faktura
310
311CREATE PROCEDURE zmien_faktura
312@id_zamowienie INT
313AS
314BEGIN
315 UPDATE faktura SET czy_oplacona=1 WHERE id_zamowienie=@id_zamowienie;
316END;
317GO
318
319--test: zamieniamy wartość kolumny czy_oplacona dla id_zamowianie=3 na true
320exec zmien_faktura 3;
321select * from faktura;
322GO
323
324--DROP FUNCTION spr_faktura
325
326CREATE FUNCTION spr_faktura(@id_zamowienie INT)
327RETURNS BIT
328BEGIN
329IF (SELECT COUNT(s.id_status) FROM status s INNER JOIN zamowienie_status zs ON s.id_status=zs.id_status
330 WHERE zs.id_zamowienie=@id_zamowienie AND s.nazwa='otrzymano zapłatę' )=1
331 RETURN 1
332RETURN 0
333END;
334GO
335
336--test: sprawdzamy czy id_zamowienie=1 oraz id_zamowienie=51 ma status_zamowienia='otrzymano zapłatę'
337SELECT dbo.spr_faktura(1); --tak
338SELECT dbo.spr_faktura(51); --nie
339GO
340--DROP TRIGGER sprawdz_faktura
341
342CREATE TRIGGER sprawdz_faktura ON zamowienie_status
343AFTER INSERT
344AS
345BEGIN
346 DECLARE @id INT
347 SELECT @id=id_zamowienie FROM inserted
348 IF dbo.spr_faktura(@id)=1
349 BEGIN
350 exec zmien_faktura @id;
351 END
352END;
353GO
354
355--test: aktualizujemy kolumne czy_opacona w tabeli faktura na true
356--dodajemy nowe zamowienie
357INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (56,14,22,'2013-10-12 16:46',50,123);
358
359--dodajemy fakture id_faktura=56 do dodanego zamowienia
360INSERT INTO faktura(id_faktura,id_zamowienie,id_klient,id_pracownik,nr_faktury,data_wystawienia,data_platnosci,czy_oplacona) VALUES (56,56,22,1,'013','2011-06-23 12:12','2011-06-30 22:32',0);
361
362--dodajemy zamowienie_status o id_status=3(czyli otzymano_zapłatę)
363INSERT INTO zamowienie_status(id_zamowienie,id_status,data_zmiany_statusu,uwagi) VALUES (56,3,'2011-03-07 11:35','brak');
364
365--kolumne czy_opacona w tabeli faktura dla wiersza o id_faktura=56 zmieniła się na true;
366SELECT * FROM FAKTURA;
367GO
368
369--============================8.
370--Utwórz funkcję spr_stanowiska z trzema parametrami stanowisko,data1 i data2, która zwraca ilość zamówień
371--obsłużonych przez pracowników z zadanym stanowiskiem, w określonym przedziale czasowym.(5f)
372--Utwórz wyzwalacz, który po dodaniu nowego zamówienia, będzie zwiększał o 50 dodatek wszystkim pracownikom
373--danego stanowiska, jeśli suma zamówień które obsłuzyli przekroczyła 4 w czasie od 2013-01-01 do 2014-01-01,
374--skorzystaj z fucnkji spr_stanowiska.(9w)
375
376--DROP FUNCTION spr_stanowiska
377
378CREATE FUNCTION spr_stanowiska(@stanowisko VARCHAR(30), @data1 DATETIME, @data2 DATETIME)
379RETURNS INT
380BEGIN
381RETURN (SELECT COUNT(z.id_zamowienie)AS suma_zamowien
382 FROM pracownik p LEFT JOIN zamowienie z ON p.id_pracownik=z.id_pracownik
383 WHERE stanowisko=@stanowisko AND data_zamowienia BETWEEN @data1 AND @data2)
384END;
385GO
386
387--test: sprawdzamy ilośc zamówień obsłużonych przez pracowników ze stanowiskiem księgowy w czasie 2013-01-01 - 2014-01-01
388SELECT dbo.spr_stanowiska('księgowy','2013-01-01','2014-01-01');
389GO
390
391--DROP TRIGGER edytuj_premie
392
393CREATE TRIGGER edytuj_premie ON zamowienie
394AFTER INSERT
395AS
396BEGIN
397 DECLARE @stanowisko VARCHAR(30), @id INT
398 SELECT @id=id_pracownik FROM inserted
399 SELECT @stanowisko=stanowisko FROM pracownik WHERE id_pracownik=@id
400 IF dbo.spr_stanowiska(@stanowisko,'2013-01-01','2014-01-01')>4
401 BEGIN
402 UPDATE pracownik SET dodatek=dodatek+50 WHERE stanowisko=@stanowisko
403 END
404END;
405GO
406
407
408--test: dodajemy zamówienia dla pracowników ze stanowiskiem sprzedawca w odpowiednim przedziale czasu, suma zamówien przekroczyła 4 więc zwiększamy dodatek
409--pracownikom stanowiska sprzedawca o 50
410INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (83,2,10,'2013-03-06 11:35',100,23);
411INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (84,8,10,'2013-03-06 11:35',100,23);
412INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (62,1,10,'2011-03-06 11:35',100,23);
413
414SELECT * FROM pracownik;
415GO
416
417--============================9.
418--Stwórz funkcję spr_suma_zamowienie, która zwraca sumę całego zamówienia danego klienta (weź pod uwagę podatek).(5f)
419--Napisz procedurę suma_zamowienie,posiadająca dwa parametry id_klient, id_zamowienie, która korzystając z funkcji
420--spr_suma_zamowienie, zwiększa rabat danego klienta o 200, jeśli suma zadanego zamówienia klienta przekroczyła 5000.(3p)
421--Utwórz wyzwalacz, który po dodaniu,edycji zamówienia do koszyka będzie pobierał id_klienta i id_zamowienia z dodanych i wywoływał procedurę.(10w)
422
423
424--DROP FUNCTION spr_suma_zamowienie
425
426CREATE FUNCTION spr_suma_zamowienie(@id_klient INT, @id_zamowienie INT)
427RETURNS DECIMAL(8,2)
428BEGIN
429 RETURN (SELECT SUM((1+ko.podatek)*ko.cena_netto) FROM koszyk ko INNER JOIN zamowienie z ON ko.id_zamowienie=z.id_zamowienie
430 WHERE z.id_klient=@id_klient AND z.id_zamowienie=@id_zamowienie)
431END;
432GO
433
434--test: sprawdzamy sumę zamówienia o id_zamowienie=23, dla klienta o id=23
435SELECT dbo.spr_suma_zamowienie(23,23);
436GO
437
438--DROP PROCEDURE suma_zamowienie
439
440CREATE PROCEDURE suma_zamowienie
441@id_klient INT,
442@id_zamowienie INT
443AS
444BEGIN
445 IF dbo.spr_suma_zamowienie(@id_klient, @id_zamowienie)>5000
446 UPDATE klient SET rabat=rabat+200 WHERE id_klient=@id_klient
447END;
448GO
449
450--test: sprawdzamy, czy suma zamowienia dla id_zamowienie=1 przekroczyła 5000,
451--jeśli tak zwiększamy rabat klienta id_klient=10 o 200
452exec suma_zamowienie 10,1;
453select * from klient;
454GO
455
456--DROP TRIGGER zwieksz_rabat
457
458CREATE TRIGGER zwieksz_rabat ON koszyk
459AFTER INSERT
460AS
461BEGIN
462 DECLARE @id_klient INT, @id_zamowienie INT
463 SELECT @id_zamowienie=id_zamowienie FROM inserted
464 SELECT @id_klient=z.id_klient FROM zamowienie z inner join koszyk ko
465 ON z.id_zamowienie=ko.id_zamowienie WHERE ko.id_zamowienie=@id_zamowienie
466 exec suma_zamowienie @id_klient, @id_zamowienie
467END;
468GO
469
470--test: dodajemy dla id_klient=23 nowe zamówienie do koszyka, jego rabat zwiększył sie o 200
471INSERT INTO koszyk(id_zamowienie,id_produkt,cena_netto,podatek,ilosc_sztuk) VALUES (23,22,229,23,1100);
472SELECT * FROM klient;
473GO
474
475--===========================10.
476--Dodaj do tabeli adres kolumnę dziennik z domyślną wartością NULL oraz kolumnę data.
477--Stwórz wyzwalacz, który po zmodyfikowaniu tabeli adres, będzie modyfikował kolumnę
478--dziennik na napis 'zedytowano adres', oraz kolumnÄ™ data na datÄ™ modyfikacji(11w)
479
480ALTER TABLE adres ADD dziennik VARCHAR(100) DEFAULT NULL, data DATETIME ;
481GO
482
483--DROP TRIGGER edytuj_dziennik
484
485CREATE TRIGGER edytuj_dziennik ON adres
486AFTER UPDATE
487AS
488BEGIN
489 declare @id int, @ulica VARCHAR(30), @numer VARCHAR (30), @kod VARCHAR(30), @miejscowosc VARCHAR(30)
490 select @id=id_adres, @ulica=ulica,@numer=numer, @kod=kod, @miejscowosc=miejscowosc from inserted
491 IF(SELECT COUNT(*) FROM adres WHERE ulica=@ulica AND numer=@numer AND kod=@kod AND miejscowosc=@miejscowosc)>0
492 BEGIN
493 UPDATE adres SET dziennik='zedytowano adres' WHERE id_adres=@id
494 UPDATE adres SET data= GETDATE() WHERE id_adres=@id
495 END
496END;
497GO
498
499--test: edytujemy adres o id=2,4,12, w kolumnie dziennik pojawił się napis 'zedytowano adres' w kolumnie data pojawiła się data edycji
500UPDATE adres SET ulica='marcinkowska' where id_adres=2;
501UPDATE adres SET ulica='helska' where id_adres=4;
502UPDATE adres SET ulica='batorego' where id_adres=12;
503
504select * from adres;
505GO
506
507
508--==========================11.
509--Napisz funkcję spr_pensje, która zwraca średnią pensje pracowników danego stanowiska jeśli była większa niż 4700 (6f)
510--Napisz procedurę zwieksz_pensje, która zwiększa pensję pracowników danego stanowiska o 10% jeśli ich średnia pensja
511--była nie mniejsza niż 5000 i o 5% jeśli ich pensja była większa niż 5000 i mniejsza niż 6000.(4p)
512
513--DROP FUNCTION spr_pensje
514
515CREATE FUNCTION spr_pensje(@stanowisko VARCHAR(30))
516RETURNS DECIMAL(8,2)
517BEGIN
518 RETURN (SELECT AVG(pensja) FROM pracownik WHERE stanowisko=@stanowisko
519 HAVING AVG(pensja)>4700)
520END;
521GO
522
523--test: sprawdzamy średnią pensję dla podanych stanowisk, średnia pensja wyświetla się jeśli jest większa niż 4700
524SELECT dbo.spr_pensje('kierownik');
525SELECT dbo.spr_pensje('sprzedawca');
526SELECT dbo.spr_pensje('ksiegowy');
527GO
528
529--DROP PROCEDURE zwieksz_pensje
530
531CREATE PROCEDURE zwieksz_pensje
532@stanowisko VARCHAR(30)
533AS
534BEGIN
535 IF dbo.spr_pensje(@stanowisko)<=5000 AND dbo.spr_pensje(@stanowisko)!=NULL UPDATE pracownik SET pensja=pensja+pensja*0.10 WHERE stanowisko=@stanowisko
536 IF dbo.spr_pensje(@stanowisko)>5000 UPDATE pracownik SET pensja=pensja+pensja*0.05 WHERE stanowisko=@stanowisko
537END;
538GO
539
540--test: uruchamiając procedurę zwiększamy pensję kolejno
541--stanowiska sprzedawca (nie zwiększy się bo poniżej średniej) i kierownik
542exec zwieksz_pensje 'sprzedawca'
543exec zwieksz_pensje 'kierownik'
544select * from pracownik;
545GO
546
547--=======================================12.
548--Napisz wyzwalacz, który po dodaniu lub zmodyfikowaniu zamówienia, zmniejszy kolumne cena_netto_dostawy w tabeli zamowienie o 50%,
549--jeśli zamowienie zostało zamówione w dniach 2013-02-14 2013-02-16(12w)
550
551--DROP TRIGGER zmniejsz_cene_dostawy
552
553CREATE TRIGGER zmniejsz_cene_dostawy ON zamowienie
554AFTER INSERT, UPDATE
555AS
556BEGIN
557 DECLARE @data DATETIME, @id INT
558 SELECT @data=data_zamowienia, @id=id_zamowienie FROM inserted
559 IF @data BETWEEN '2013-02-14' AND '2013-02-16'
560 UPDATE zamowienie SET cena_netto_dostawy=cena_netto_dostawy*0.5 WHERE id_zamowienie=@id
561END;
562GO
563
564--test: dodajemy nowe zamówienia o id=100,101 w zadanym przedziale czasowym
565INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (100,1,10,'2013-02-15 11:35',100,23);
566INSERT INTO zamowienie(id_zamowienie,id_pracownik,id_klient,data_zamowienia,cena_netto_dostawy,podatek) VALUES (101,1,10,'2013-02-15 11:35',100,23);
567
568--edytujemy zamowienia o id=1 na zadany przedział czasowy
569UPDATE zamowienie SET data_zamowienia='2013-02-16' WHERE id_zamowienie=1;
570
571--zamówienia o id=100,101,1 mają cene dostawy mniejsza o 50%
572select * from zamowienie;
573GO
574
575--==========================13.
576--Napisz procedurę zwieksz_rabat_data, która zwiększa o 400 rabat klientowi, który został dodany najwcześniej.(5p)
577
578--DROP PROCEDURE zwieksz_rabat_data
579
580CREATE PROCEDURE zwieksz_rabat_data
581AS
582BEGIN
583
584 UPDATE klient SET rabat=rabat+400 WHERE
585 id_klient=(SELECT id_klient FROM klient WHERE data_dodania=(SELECT MIN(data_dodania) FROM klient))
586END;
587GO
588
589--test: sprawdzamy jaki klient został dodany najwcześniej i zwiększamy jego rabat o 400
590EXEC zwieksz_rabat_data;
591GO
592
593--==========================14.
594--Napisz funkcję brak_produktow, która zwróci nazwy id producentów, którzy nie sprowadzili żadnego produktu.
595--(podpowiedź: funkcja tabelowa)(7f)
596--Napisz wyzwalacz, który zabroni modyfikacji rekordów w tabeli producent, jeśli dany producent sprowadził produkt,
597--skorzystaj z funkcji brak_produktów. (13w)
598
599--DROP FUNCTION brak_produktow
600
601CREATE FUNCTION brak_produktow()
602RETURNS TABLE AS
603 RETURN ( SELECT id_producent FROM producent WHERE id_producent NOT IN(SELECT id_producent FROM produkt));
604GO
605
606--test: wyświetlamy id_producentów, którzy nie sprowadzili żadnego produktu
607SELECT * FROM brak_produktow();
608GO
609
610
611--DROP TRIGGER zabron_brak_produktow
612
613CREATE TRIGGER zabron_brak_produktow ON producent
614AFTER UPDATE
615AS
616BEGIN
617 DECLARE @id INT
618 SELECT @id=id_producent FROM deleted
619 IF NOT EXISTS ( SELECT * FROM brak_produktow() WHERE id_producent=@id)
620 BEGIN
621 RAISERROR('Nie mozna edytowac producenta, który sprowadził produkt',1,2)
622 ROLLBACK
623 END
624END;
625GO
626
627--test:sprawdzamy czy da się zmienić nazwę producenta o id=12,15, nie da się jeśli dany producent sprowadził produkt
628UPDATE producent SET nazwa='NOWY2' where id_producent=15;
629UPDATE producent SET nazwa='NOWY1' where id_producent=12;
630SELECT * FROM producent;
631GO
632
633
634--============================15.
635--Utwórz funkcję znajdz_produkty, która wyświetla id_produktu, nazwe oraz ilosc(ile razy zakupiony dany produkt),
636--dla produktów, które zostały zakupione conajmniej 4 razy.(8f)
637--Utwórz procedurę zwieksz_cene_produktu, która podnosi cena_netto w kolumnie produkt o 20%, jeśli
638--dany produkt był zakupiony conajmniej 4 razy, skorzystaj z funkcji znajdz_produkty (6p)
639
640--DROP FUNCTION znajdz_produkty
641
642CREATE FUNCTION znajdz_produkty()
643RETURNS TABLE AS
644 RETURN ( SELECT p.id_produkt, p.nazwa, COUNT(p.id_produkt) AS ilosc FROM produkt p
645 INNER JOIN koszyk k ON p.id_produkt=k.id_produkt GROUP BY p.id_produkt, p.nazwa
646 HAVING COUNT(p.id_produkt)>=4 );
647GO
648
649--test: wyświetlamy przy pomocy funkcji produkty, które zostały zakupione conajmniej 4 razy
650SELECT * FROM znajdz_produkty();
651GO
652
653--DROP PROCEDURE zwieksz_cene_produktu
654
655CREATE PROCEDURE zwieksz_cene_produktu
656AS
657BEGIN
658 UPDATE produkt SET cena_netto=cena_netto+0.20*cena_netto WHERE
659 id_produkt IN (SELECT id_produkt FROM znajdz_produkty())
660END;
661GO
662
663--test: zwiększamy cene produktów, które zostały zakupione conajmniej 4 razy
664EXEC zwieksz_cene_produktu;
665SELECT * FROM produkt;
666GO
667
668--============================16.
669--Utwórz procedurę najczesciej_klient, przyjmująca parametry min_ilosc, id_klient
670--która zwróci id_klienta, imie, nazwisko klienta, który najczęsciej składał zamówienia,
671--a minimalna ilosc najczęstszych zamowień jest określona parametrem min_ilosc.(9f)
672--Utwórz procedurę, która będzie przymowała jako parametr id_klienta, min_ilosc,
673--i korzystając z funkcji najczesciej_klient, do której przekazuje parametr min_ilosc, będzie
674--sprawdzała czy dany klient jest klientem najczęsciej zamawiajcym, jeśli tak zwiększy jego rabat o 50%.(7p)
675
676--DROP FUNCTION najczesciej_klient
677
678CREATE FUNCTION najczesciej_klient(@min_ilosc INT)
679RETURNS TABLE AS
680 RETURN (SELECT k.id_klient,k.imie, k.nazwisko, COUNT (z.id_klient)AS ilosc FROM klient k INNER JOIN zamowienie z ON k.id_klient=z.id_klient
681 GROUP BY k.id_klient, k.imie, k.nazwisko
682 HAVING COUNT(z.id_klient)=
683 (
684 SELECT TOP 1 COUNT (z.id_klient)AS ilosc
685 FROM zamowienie z
686 GROUP BY z.id_klient
687 HAVING COUNT (z.id_klient)>@min_ilosc
688 ORDER BY ilosc DESC
689 )
690 )
691GO
692
693--test: wyświetlamy przy pomocy funkcji id_klienta, który najczęsciej składał zamówienia, w nawiasie min ilośc zamówień
694SELECT * FROM najczesciej_klient(3);
695SELECT * FROM najczesciej_klient(4);
696GO
697
698
699--DROP PROCEDURE zwieksz_rabat_data_klient
700
701CREATE PROCEDURE zwieksz_rabat_data_klient
702@id_klient INT,
703@min_ilosc INT
704AS
705BEGIN
706 IF EXISTS(SELECT id_klient FROM najczesciej_klient(3) WHERE id_klient=@id_klient)
707 BEGIN
708 UPDATE klient SET rabat=rabat+0.50*rabat WHERE id_klient=@id_klient
709 END
710END;
711GO
712
713--test: zwiększamy rabat klientowi o zadanym id, jeśli był on najczęsciej zamawiającym, drugi parametr to min ilość zamówień
714EXEC zwieksz_rabat_data_klient 2,3;
715EXEC zwieksz_rabat_data_klient 3,4;
716EXEC zwieksz_rabat_data_klient 10,3;
717
718SELECT * FROM klient;
719GO
720
721--============================17.
722--Procedura o nazwie zmien_telefon, która posiada dwa parametry: id klienta i numer telefonu.
723--Procedura sprawdza, czy istnieje klient o zadanym id, jeśli istnieje to zmienia numer telefonu na zadany numer,
724--jeśli nie istnieje klient o zadanym id to wyświetla napis "Nie ma klienta o zadanym ID!". (8p)
725
726--DROP PROCEDURE zmien_telefon
727
728CREATE PROCEDURE zmien_telefon
729 @id INT,
730 @telefon VARCHAR(20)
731AS
732BEGIN
733 IF EXISTS(SELECT * FROM klient WHERE id_klient=@id)
734 BEGIN
735 UPDATE klient
736 SET telefon=@telefon
737 WHERE id_klient=@id
738 END
739 ELSE
740 PRINT 'Nie ma klienta o zadanym ID!'
741END;
742GO
743
744--test: sprawdzamy czy klient o id=2,23 istnieje jeśli tak zmieniamy mu numer telefony na zadany, jeśli nie wyświetlamy komunikat
745EXECUTE zmien_telefon '2','123456789';
746EXECUTE zmien_telefon '123','123456789';
747GO
748--==============================18.
749--Napisz funkcję spr_telefon, która zwraca true jeśli numer telefonu, ma 9 cyfr i jest w formacie 123-123-123 lub 12-123-12-12
750--oraz false w przeciwnym przypadku(10f)
751--Napisz wyzwalacz, który zabroni modyfikacji rekordów, które mają zły format numeru telefonu, oraz modyfikacji na zły format numeru telefonu
752--skorzystaj z funkcji spr_telefon. (14w)
753
754--DROP FUNCTION spr_telefon
755
756CREATE FUNCTION spr_telefon(@telefon VARCHAR(30))
757RETURNS BIT
758BEGIN
759 IF @telefon LIKE '[0-9][0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9][0-9]' OR @telefon LIKE '[0-9][0-9]-[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]'
760 RETURN 1
761 RETURN 0
762END;
763GO
764
765--test: sprawdzamy poprawność zapisu numeru telefonu
766SELECT dbo.spr_telefon('111-111-111');
767SELECT dbo.spr_telefon('11-111-11-11');
768SELECT dbo.spr_telefon('aaa-aaa-aaa');
769GO
770--DROP TRIGGER zabron_numer
771
772CREATE TRIGGER zabron_numer ON klient
773AFTER UPDATE
774AS
775BEGIN
776 DECLARE @telefon VARCHAR(30)
777 SELECT @telefon=telefon FROM inserted
778 IF dbo.spr_telefon(@telefon)=0
779 BEGIN
780 RAISERROR('Nie mozna edytowac, bo zly format numeru telefonu!',1,2)
781 ROLLBACK
782 END
783END;
784GO
785
786--test: edytujemy numer telefonu dla klientów o id=1,8 jeśli numer telefonu ma nieproprwany format, wyświetlamy napis i zabraniamy edycji
787UPDATE klient SET telefon='111-111-111' where id_klient=1;
788GO
789UPDATE klient SET email='trolo@wp.pl' where id_klient=8;
790GO
791UPDATE klient SET telefon='111-111-111s' where id_klient=1;
792GO
793SELECT * FROM klient;
794GO