tSwiPHxK

1LAB 08 ( CREATE TABLE, ALTER, DROP & ALTER, CREATE &ALTER,)
2
3CREATE TABLE
4
5--28.1 Utwórz tabelę pracownik2(id_pracownik, imie, nazwisko, pesel, data_zatr, pensja), gdzie
6--* id_pracownik – jest numerem pracownika nadawanym automatycznie, jest to klucz główny
7--* imie i nazwisko – to niepuste łańcuchy znaków zmiennej długości,
8--* pesel – unikatowy łańcuch jedenastu znaków stałej długości,
9--* data_zatr – domyślna wartość daty zatrudnienia to bieżąca data systemowa,
10--* pensja – nie może być niższa niż 1000zł. 
11
12--DROP TABLE pracownik2;
13--GO
14CREATE TABLE pracownik2(
15 id_pracownik INT IDENTITY(1,1) PRIMARY KEY,
16 imie VARCHAR(15) NOT NULL,
17 nazwisko VARCHAR(20) NOT NULL,
18 pesel CHAR(11) UNIQUE,
19 data_zatr DATETIME DEFAULT GETDATE(),
20 pensja MONEY CHECK(pensja>=1000)
21); 
22
23--28.2 Utwórz tabelę naprawa2(id_naprawa, data_przyjecia, opis_usterki, zaliczka), gdzie
24--* id_naprawa – jest unikatowym, nadawanym automatycznie numerem naprawy, jest to klucz główny,
25--* data_przyjecia – nie może być późniejsza niż bieżąca data systemowa,
26--* opis usterki – nie może być pusty, musi mieć długość powyżej 10 znaków,
27--* zaliczka – nie może być mniejsza niż 100zł ani większa niż 1000zł. 
28
29CREATE TABLE naprawa2(
30id_naprawa INT IDENTITY(1,1) PRIMARY KEY UNIQUE,
31data_przyjecia DATETIME DEFAULT GETDATE(),
32opis_usterki VARCHAR(10) NOT NULL,
33zaliczka MONEY CHECK (zaliczka >100 AND zaliczka <1000)
34);
35
36--28.3 Utwórz tabelę wykonane_naprawy2(id_pracownik, id_naprawa, data_naprawy, opis_naprawy, cena), gdzie
37--* id_pracownik – identyfikator pracownika wykonującego naprawę, klucz obcy powiązany z tabelą pracownik2,
38--* id_naprawa – identyfikator zgłoszonej naprawy, klucz obcy powiązany z tabelą naprawa2,
39--* data_naprawy – domyślna wartość daty naprawy to bieżąca data systemowa,
40--* opis_naprawy – niepusty opis informujący o sposobie naprawy,
41--* cena – cena naprawy. 
42
43CREATE TABLE wykonane_naprawy2(
44id_pracownik INT NOT NULL REFERENCES pracownik2(id_pracownik) ON UPDATE CASCADE,
45id_naprawa INT NOT NULL REFERENCES naprawa2(id_naprawa) ON UPDATE CASCADE,
46data_naprawy DATETIME DEFAULT GETDATE(),
47opis_naprawy VARCHAR NOT NULL,
48cena MONEY,
49);
50
51ALTER TABLE
52
53--29.1 Dana jest tabela:
54CREATE TABLE student2(
55id_student INT IDENTITY(1,1) PRIMARY KEY,
56nazwisko VARCHAR(20),
57nr_indeksu INT,
58stypendium MONEY
59);
60--Wprowadź ograniczenia na tabelę student2:
61--* nazwisko – niepusta kolumna,
62--* nr_indeksu – unikatowa kolumna,
63--* stypendium –nie może być niższe niż 1000zł,
64--* dodatkowo dodaj niepustÄ… kolumnÄ™ imie. 
65
66ALTER TABLE student2 ALTER COLUMN nazwisko VARCHAR(20) NOT NULL;
67ALTER TABLE student2 ADD CONSTRAINT unikatowy_nr_indeksu UNIQUE (nr_indeksu);
68ALTER TABLE student2 ADD CONSTRAINT sprawdz_stypendium CHECK (stypendium>=1000);
69ALTER TABLE student2 ADD imie VARCHAR(15) NOT NULL;
70
71--29.2 Dane sÄ… tabele:
72CREATE TABLE dostawca2(
73id_dostawca INT IDENTITY(1,1) PRIMARY KEY,
74nazwa VARCHAR(30)
75);
76CREATE TABLE towar2(
77id_towar INT IDENTITY(1,1) PRIMARY KEY,
78kod_kreskowy INT,
79id_dostawca INT
80);
81--Zmodyfikuj powyższe tabele:
82--* kolumna nazwa z tabeli dostawca2 powinna być unikatowa,
83--* do tabeli towar2 dodaj niepustÄ… kolumnÄ™ nazwa,
84--* kolumna kod_kreskowy w tabeli towar2 powinna być unikatowa,
85--* kolumna id_dostawca z tabeli towar2 jest kluczem obcym z tabeli dostawca2. 
86
87ALTER TABLE dostawca2 ADD CONSTRAINT unikatowa_nazwa2 UNIQUE (nazwa);
88ALTER TABLE towar2 ADD nazwa VARCHAR(15) NOT NULL;
89ALTER TABLE towar2 ADD CONSTRAINT unikatowy_kod_kreskowy UNIQUE (kod_kreskowy);
90ALTER TABLE towar2 ADD CONSTRAINT fk_id_dostawca FOREIGN KEY (id_dostawca) REFERENCES dostawca2(id_dostawca);
91
92--29.3 Dane sÄ… tabele:
93CREATE TABLE kraj2(
94id_kraj INT IDENTITY(1,1) PRIMARY KEY,
95nazwa VARCHAR(30)
96);
97CREATE TABLE gatunek2(
98id_gatunek INT IDENTITY(1,1) PRIMARY KEY,
99nazwa VARCHAR(30)
100);
101CREATE TABLE zwierze2(id_zwierze INT IDENTITY(1,1) PRIMARY KEY,
102id_gatunek INT,
103id_kraj INT,
104cena MONEY
105);
106--Zmodyfikuj powyższe tabele:
107--* kolumny nazwa z tabel kraj2 i gatunek2 mają być niepuste,
108--* kolumna id_gatunek z tabeli zwierze2 jest kluczem obcym z tabeli gatunek2,
109--* kolumna id_kraj z tabeli zwierze2 jest kluczem obcym z tabeli kraj2. 
110
111ALTER TABLE kraj2 ALTER COLUMN nazwa VARCHAR(30) NOT NULL;
112ALTER TABLE gatunek2 ALTER COLUMN nazwa VARCHAR(30) NOT NULL;
113ALTER TABLE zwierze2 ADD CONSTRAINT fk_id_gatunek FOREIGN KEY (id_gatunek) REFERENCES gatunek2(id_gatunek);
114ALTER TABLE zwierze2 ADD CONSTRAINT fk_id_kraj FOREIGN KEY (id_kraj) REFERENCES kraj2(id_kraj);
115
116DROP AND ALTER
117
118--30.1 Dane sÄ… tabele: 
119CREATE TABLE kategoria2(
120id_kategoria INT PRIMARY KEY,
121nazwa VARCHAR(30)
122);
123CREATE TABLE przedmiot2(
124id_przedmiot INT PRIMARY KEY,
125id_kategoria INT REFERENCES kategoria2(id_kategoria),
126nazwa VARCHAR(30)
127);
128--Napisać instrukcje SQL, która usuną tabele kategoria2 i przedmiot2. 
129
130--wersja podstawowa
131DROP TABLE przedmiot2;
132DROP table kategoria2;
133--wersja trudeniejsza
134IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='przedmiot2')
135DROP TABLE przedmiot2;
136IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name='kategoria2')
137DROP TABLE kategoria2; 
138
139--30.2 Dana jest tabela: 
140CREATE TABLE osoba2(
141id_osoba INT,
142imie VARCHAR(15),
143imie2 VARCHAR(15)
144);
145--Napisać instrukcję SQL, która z tabeli osoba2 usunie kolumnę imie2. 
146ALTER TABLE osoba2 DROP COLUMN imie2;
147
148--30.3 Dana jest tabela: 
149CREATE TABLE uczen2(
150id_uczen INT PRIMARY KEY,
151imie VARCHAR(15),
152nazwisko VARCHAR(20) CONSTRAINT uczen_nazwisko_unique UNIQUE
153);
154--Napisać instrukcję SQL, która usunie narzucony warunek unikatowości na kolumnę nazwisko.
155ALTER TABLE uczen2 DROP CONSTRAINT uczen_nazwisko_unique;
156
157CREATE AND ALTER
158
159--31.1 Utwórz tabelę wlasciciel2(id_wlasciciel, imie,nazwisko,data_ur,ulica,numer,kod,miejscowosc) i
160--zwierze2(id_zwierze,id_wlasciciel,rasa,data_ur,imie) w taki sposób, aby po usunięciu informacji o właścicielu
161--zwierzęcia z tabeli wlasciciel2, SZBD automatycznie identyfikator właściciela w tabeli zwierze2 ustawiał na wartość
162--NULL. Nie zapomnij o doborze pozostałych ograniczeń na kolumny (można to zrobić wg uznania). 
163
164 --DROP TABLE zwierze2;
165--DROP TABLE wlasciciel2;
166CREATE TABLE wlasciciel2(
167 id_wlasciciel INT IDENTITY(1,1)PRIMARY KEY,
168 imie VARCHAR(15) NOT NULL CHECK(LEN(imie)>2),
169 nazwisko VARCHAR(15) NOT NULL CHECK(LEN(nazwisko)>2),
170 data_ur DATE NOT NULL DEFAULT GETDATE(),
171 ulica VARCHAR(50),
172 numer VARCHAR(8),
173 kod CHAR(6) NOT NULL CHECK(LEN(kod)=6),
174 miejscowosc VARCHAR(30) NOT NULL CHECK(LEN(miejscowosc)>1)
175);
176CREATE TABLE zwierze2(
177 id_zwierze INT IDENTITY(1,1) PRIMARY KEY,
178 id_wlasciciel INT REFERENCES wlasciciel2(id_wlasciciel) ON DELETE SET NULL,
179 rasa VARCHAR(30) NOT NULL CHECK(LEN(rasa)>2),
180 data_ur DATE NOT NULL DEFAULT GETDATE(),
181 imie VARCHAR(15) NOT NULL CHECK(LEN(imie)>2)
182);
183
184--INSERT INTO wlasciciel2(imie,nazwisko,data_ur,ulica,numer,kod,miejscowosc)
185--VALUES ('Jan','Kos','1999-01-11','Polna','1','11-222','Nysa'); 
186--INSERT INTO zwierze2(id_wlasciciel,rasa,data_ur,imie)
187--VALUES (1,'Jamnik','2010-03-03','Krótki');
188--SELECT * FROM zwierze2;
189--DELETE FROM wlasciciel2 WHERE id_wlasciciel=1;
190--SELECT * FROM zwierze2; 
191
192--31.2 Dane sÄ… tabele: 
193CREATE TABLE film2(
194id_film INT PRIMARY KEY,
195tytul VARCHAR(50) NOT NULL
196);
197CREATE TABLE gatunek2(
198id_gatunek INT PRIMARY KEY,
199nazwa VARCHAR(50) NOT NULL
200);
201CREATE TABLE film2_gatunek2(
202id_film INT,
203id_gatunek INT,PRIMARY KEY(id_film,id_gatunek)
204);
205--Zmodyfikuj strukturę powyższych tabel (polecenie ALTER) taka aby:
206--a) w przypadku usunięcia filmu z tabeli film2 zostały automatycznie usuwane informacje jakich gatunków był
207--usunięty film (nie usuwamy gatunków z tabeli gatunek2),
208--b) w przypadku usunięcia gatunku z tabeli gatunek2 zostały automatycznie usuwane informacje jakie filmy były tego
209--gatunku (nie usuwamy filmów z tabeli film2). 
210---------------------------------------------------------------------------------------------------------------------
211LAB 10 (CREATE PROCUDRE, CREATE FUNCTION, CREATE VIEW, CREATE INDEX)
212
213CREATE PROCEDURE
214
215--32.1 Napisać procedurę o nazwie wypisz_samochody, która posiada tylko jeden parametr - marka samochodu.
216--Procedura powinna wyświetlać wszystkie informacje z tabeli samochód o samochodach zadanej marki. 
217
218-- DROP PROCEDURE wypisz_samochody;
219CREATE PROCEDURE wypisz_samochody @marka VARCHAR(20)
220AS
221SELECT * FROM samochod WHERE marka=@marka;
222GO
223EXECUTE wypisz_samochody 'opel';
224--32.2 Napisać procedurę o nazwie zwieksz_pensje posiadającą dwa parametry: identyfikator pracownika i kwotę.
225--Procedura powinna zwiększyć pensję pracownikowi, na którego wskazuje zadany identyfikator o zadaną kwotę.
226--Przetestuj utworzoną procedurę – zwiększ pracownikowi o identyfikatorze równym 1 pensję o 1000 zł. 
227
228CREATE PROC zwieksz_pensje @id_prac INT, @kwota MONEY
229AS
230UPDATE pracownik SET pensja=pensja+@kwota WHERE id_pracownik=@id_prac;
231GO
232EXECUTE zwieksz_pensje 1, 1000;
233GO
234
235--32.3 Napisz procedurę o nazwie dodaj_klienta umożliwiającą dodanie nowego klienta. Dane klienta powinny być
236--odczytane z parametrów procedury. Dobierz odpowiednio parametry dla tworzonej procedury na podstawie kolumn
237--tabeli klient. Przetestuj utworzoną procedurę – dodaj nowego klienta. 
238
239CREATE PROC dodaj_klienta
240@id_klient INT, @imie VARCHAR(15), @nazwisko VARCHAR(20),
241@nr_karty_kred CHAR(20), @firma VARCHAR(40), @ulica VARCHAR(24),
242@numer VARCHAR(8), @miasto VARCHAR(24), @kod CHAR(6),
243@nip CHAR(11), @telefon VARCHAR(16)
244AS
245INSERT INTO klient VALUES(@id_klient, @imie, @nazwisko, @nr_karty_kred, @firma, @ulica,
246@numer, @miasto, @kod, @nip, @telefon);
247GO
248EXECUTE dodaj_klienta 21, "Pawel", "Zachara", "000000000000000000001111", "goyello", "grunwaldzka", "15", "Gdansk", "83-110", "11111111111", "544311324";
249GO
250
251CREATE FUNCTION
252
253--33.1 Napisać funkcję o nazwie aktywnosc_klienta, która będzie zwracać ilość wypożyczeń samochodów dla klienta o
254--identyfikatorze zadanym jako parametr funkcji. Przetestuj utworzoną funkcję – sprawdź ile samochodów wypożyczył
255--klient o identyfikatorze równym 3. 
256
257--DROP FUNCTION dbo.aktywnosc_klienta;
258--GO
259CREATE FUNCTION dbo.aktywnosc_klienta (
260 @id_klient INT
261) RETURNS INT
262BEGIN
263RETURN (SELECT COUNT(*) FROM wypozyczenie
264 WHERE id_klient=@id_klient)
265END;
266GO
267SELECT dbo.aktywnosc_klienta(3) AS ile_wyp;
268
269--33.2 Napisać funkcję o nazwie ile_wypozyczen posiadającą dwa parametry data_od i data_do. Funkcja powinna
270--zwrócić ilość wypożyczeń samochodów w zadanym przedziale czasowym. Przetestuj utworzoną funkcję – sprawdź ile
271--zostało wypożyczonych samochodów od 01.01.2000r. do 31.12.2000r.
272CREATE FUNCTION ile_wypozyczen (
273	@data_od DATETIME, @data_wyp DATETIME
274	) RETURNS INT
275	BEGIN RETURN (SELECT COUNT(*) FROM wypozyczenie
276							WHERE data_wyp>=@data_wyp AND data_odd<=@data_od)
277END;
278GO
279SELECT dbo.ile_wypozyczen('2000-31-12', '2000-01-01') AS ile_wyp;
280GO
281
282--33.3 Napisać funkcję o nazwie roznica_pensji nie posiadającą parametrów i zwracającą różnicę pomiędzy największą i
283--najmniejszą pensją wśród pracowników wypożyczalni. Przetestuj utworzoną funkcję. 
284
285CREATE FUNCTION roznica_pensji()
286RETURNS MONEY
287BEGIN RETURN (SELECT (MAX(pensja) - MIN(pensja)) FROM pracownik)
288END;
289GO
290SELECT dbo.roznica_pensji();
291GO
292
293CREATE VIEW
294
295--34.1 Utwórz widok o nazwie klient_raport zawierający informacje o ilości wypożyczeń każdego z klientów (id_klient,
296--imie, nazwisko). Uwzględnij klientów, którzy ani razu nie wypożyczyli samochodu. Za pomocą utworzonego widoku
297--znajdź klientów, którzy wypożyczyli samochód więcej niż raz.  
298
299--DROP VIEW klient_raport;
300--GO
301CREATE VIEW klient_raport
302AS
303SELECT k.id_klient, k.imie, k.nazwisko, COUNT(w.id_klient) AS "ilosc_wyp"
304FROM klient k LEFT JOIN wypozyczenie w ON k.id_klient=w.id_klient
305GROUP BY k.id_klient, k.imie, k.nazwisko;
306GO
307SELECT * FROM klient_raport WHERE ilosc_wyp>1;
308
309--34.2 Utwórz widok o nazwie samochod_raport zawierający informacje o ilości wypożyczeń każdego z samochodów
310--(id_samochod, marka, typ). Uwzględnij samochody, które ani razu nie zostały wypożyczone. Za pomocą utworzonego
311--widoku znajdź samochód/samochody, które były najczęściej wypożyczane. 
312
313CREATE VIEW samochod_raport
314AS
315SELECT s.id_samochod, s.marka, s.typ, COUNT(w.id_samochod) AS "ilosc_wyp" FROM samochod s LEFT JOIN wypozyczenie w ON s.id_samochod=w.id_samochod
316GROUP BY s.id_samochod, s.marka, s.typ;
317GO
318SELECT TOP 1 * FROM samochod_raport ORDER BY ilosc_wyp DESC;
319GO
320
321--34.3 Utwórz widok o nazwie pracownik_raport zawierający informacje o ilości wypożyczeń samochodów przez
322--pracowników. Nie zapomnij uwzględnić pracowników, którzy nie wypożyczyli żadnego samochodu. Za pomocą
323--utworzonego widoku znajdź pracowników, dla których ilość wypożyczeń jest większa od średniej ilości wypożyczeń
324--samochodów przez pracowników. 
325
326CREATE VIEW pracownik_raport
327AS
328SELECT p.id_pracownik, p.nazwisko, COUNT(w.id_pracow_wyp) AS "ilosc_wyp" FROM pracownik p LEFT JOIN wypozyczenie w ON p.id_pracownik=w.id_pracow_wyp
329GROUP BY p.id_pracownik, p.nazwisko;
330GO
331SELECT * FROM pracownik_raport GROUP BY id_pracownik, nazwisko, ilosc_wyp HAVING ilosc_wyp>(SELECT AVG(ilosc_wyp) FROM pracownik_raport);
332GO
333
334CREATE INDEX
335
336--35.1 Utwórz unikalny indeks dla kolumny telefon w tabeli klient.  
337
338--DROP INDEX klient.index_klient_telefon;
339--GO
340CREATE UNIQUE INDEX index_klient_telefon ON klient(telefon);
341GO
342
343--35.2 Utwórz indeks klastrowy dla kolumn nazwisko i imie w tabeli klient. 
344CREATE CLUSTERED INDEX index_klient_nazwisko_imie ON klient(nazwisko, imie);
345GO
346
347--35.3 Utwórz indeks nieklastrowy dla kolumn marka i typ w tabeli samochód. 
348CREATE INDEX index_samochod_marka_typ ON samochod(marka, typ);
349GO
350--------------------------------------------------------------------------------------
351
352LAB 11 (CREATE TRIGER, WYZWALACZE I KURSOSRY, WIDOKI +CASE, PROCEDURY + DYNAMICZNY SQL)
353
354--CREATE TRIGGER
355
356
357--36.1 Napisać wyzwalacz, który uniemożliwi usunięcie klienta.
358--Przetestuj utworzony wyzwalacz – spróbuj usunąć wszystkich klientów. 
359
360--DROP TRIGGER klient_anuluj_usuwanie;
361--GO
362CREATE TRIGGER klient_anuluj_usuwanie ON klient
363FOR DELETE
364AS
365RAISERROR('Zabronione jest usuwanie klientów!',1,2) 
366ROLLBACK
367GO
368DELETE FROM klient;
369
370
371-- 36.2 Napisać wyzwalacz, który uniemożliwi dodanie pracownika z pensją lub dodatkiem równym zero lub NULL.
372--Przetestuj utworzony wyzwalacz – spróbuj dodać kilku pracowników jednocześnie. 
373
374CREATE TRIGGER pracownik_ins ON pracownik
375AFTER INSERT AS
376BEGIN
377	DECLARE @pensja MONEY
378	SET @pensja=-1
379	DECLARE @dodatek MONEY
380	SET @dodatek=-1
381	SELECT @pensja=pensja FROM INSERTED WHERE pensja=0 OR pensja IS NULL
382	IF @pensja=0 OR @pensja IS NULL
383	BEGIN
384		RAISERROR('pensja nie moze byc 0/nullem', 1, 2);
385		ROLLBACK
386	END
387	SELECT @dodatek=dodatek FROM INSERTED WHERE dodatek=0 OR dodatek IS NULL
388	IF @dodatek=0 OR @dodatek IS NULL
389	BEGIN
390		RAISERROR('dodatek nie moze byc 0/nullem', 1, 2);
391		ROLLBACK
392	END
393END
394GO
395
396
397INSERT INTO pracownik(id_pracownik,imie,nazwisko,data_zatr,dzial,stanowisko,pensja,id_miejsce,telefon) VALUES(101,'Jan','Kowalski','1997-02-01 00:00:00.000','oblsuga','kierownik',-5,1,'5062231435');
398INSERT INTO pracownik(id_pracownik,imie,nazwisko,data_zatr,dzial,stanowisko,pensja,id_miejsce,telefon) VALUES(102,'Jan','Smieszny','1997-02-01 00:00:00.000','oblsuga','kierownik',-5,1,'5355231435');
399INSERT INTO pracownik(id_pracownik,imie,nazwisko,data_zatr,dzial,stanowisko,pensja,id_miejsce,telefon) VALUES(103,'Jan','dsad','1997-02-01 00:00:00.000','oblsuga','kierownik',-5,1,'505234435');
400
401--36.3 Napisać wyzwalacz o nazwie "duplikat_miejsce", który uniemożliwi jednorazowo dodanie więcej niż jednego
402--miejsca oraz dodatkowo uniemożliwi dodanie miejsca o takich samych ulicach, numerze, mieście i kodzie.
403
404CREATE TRIGGER duplikat_miejsce ON miejsce
405AFTER INSERT AS
406IF @@ROWCOUNT > 1
407BEGIN
408	PRINT 'miejsca mozna dodawac tylko pojedynczo'
409	ROLLBACK
410END
411GO
412	
413
414--CREATE TRIGGER
415
416
417--37.1 a) Do tabeli samochód dodaj kolumnę "usuniety" typu BIT o wartości domyślnej równej 0.
418--b) W tabeli samochod zmień wszystkie wartości kolumny usuniety z NULL na wartość 0.
419--c) Utwórz wyzwalacz o nazwie " usuniety_samochod ", który uniemożliwi fizyczne usunięcie samochodu z tabeli
420--samochod, a usuwany samochod oznaczy poprzez ustawienie kolumny usuniety na wartość 1. 
421
422ALTER TABLE samochod ADD usuniety BIT DEFAULT 0;
423UPDATE samochod SET usuniety=0;
424GO
425CREATE TRIGGER usuniety_samochod ON samochod
426INSTEAD OF DELETE AS
427BEGIN
428 UPDATE samochod
429 SET usuniety=1
430 WHERE id_samochod IN (SELECT id_samochod FROM deleted)
431END
432GO
433DELETE FROM samochod WHERE id_samochod=3;
434SELECT * FROM samochod;
435
436--37.2 Utwórz wyzwalacz o nazwie "usun_miejsce_i_wypozyczenia", który uniemożliwi usunięcie równocześnie więcej
437--niż 1 miejsca z tabeli miejsce oraz przed usunięciem pojedynczego miejsca usunie najpierw wszystkie rekordy w tabeli
438--wypożyczenie zawierające informację o usuwanym miejscu (id_miejsca_wyp, id_miejsca_odd).
439--Przetestuj napisany wyzwalacz. 
440
441CREATE TRIGGER usun_miejsce_i_wypozyczenia ON miejsce
442INSTEAD OF DELETE AS
443IF @@ROWCOUNT > 1
444BEGIN
445	PRINT 'tylko 1 usuniecie na raz'
446	ROLLBACK
447END
448BEGIN
449	DECLARE @id_miejsce INT
450	SELECT @id_miejsce=id_miejsce FROM DELETED
451	
452	DELETE FROM wypozyczenie WHERE id_miejsca_odd IN (SELECT id_miejsca_odd FROM DELETED)
453	DELETE FROM wypozyczenie WHERE id_miejsca_wyp IN (SELECT id_miejsca_wyp FROM DELETED)
454	
455	DELETE FROM miejsce WHERE id_miejsce=@id_miejsce
456END
457GO
458
459--37.3 Utwórz wyzwalacz o nazwie "samochod_blokada", który uniemożliwia wprowadzenie jakiejkolwiek zmiany w
460--tabeli samochód. Przetestuj utworzony wyzwalacz dla instrukcji INSERT, UPDATE, DELETE. 
461
462CREATE TRIGGER samochod_blokada ON samochod
463INSTEAD OF INSERT, UPDATE, DELETE
464AS
465	PRINT('nie mozna nic zmieniac')
466GO
467
468
469--WYZWALACZE I KURSORY
470
471--38.1 Napisać wyzwalacz, który będzie wypisywał na ekranie nazwiska i imiona usuniętych pracowników. Użyj kursora.
472--Przetestuj utworzony wyzwalacz – usuń wszystkich pracowników. 
473
474--DROP TRIGGER pracownik_usuwanie;
475--GO
476CREATE TRIGGER pracownik_usuwanie ON pracownik
477FOR DELETE
478AS
479BEGIN
480 --deklaracja kursora
481 DECLARE kursor_deleted CURSOR
482 FOR SELECT imie, nazwisko FROM deleted;
483 --otwarcie kursora
484 OPEN kursor_deleted
485 --zmienne pomocnicze do przechowywania danych pobranych z kursora
486 DECLARE @imie varchar(15), @nazwisko varchar(20)
487 --pobranie pierwszego wiersza z kursora
488 FETCH NEXT FROM kursor_deleted INTO @imie, @nazwisko
489 --wykonuj dopóki pobranie wiersza z kursora zakończy się sukcesem
490 WHILE @@FETCH_STATUS = 0
491 BEGIN
492 --wyswietlenie wartosci zmiennych @imie i @nazwisko
493 print 'Usunięto: '+@imie+' '+ @nazwisko
494 --pobranie kolejnego wiersza z kursora
495 FETCH NEXT FROM kursor_deleted INTO @imie, @nazwisko
496 END
497 --zamkniecie kursora
498 CLOSE kursor_deleted
499 --usuniecie kursora z bazy danych
500 DEALLOCATE kursor_deleted
501 END
502GO
503DELETE FROM pracownik 
504
505--38.2 Stwórz tabelę o nazwie samochod_delete o identycznej strukturze jak tabela samochód.
506--Napisz wyzwalacz, który wszystkie usunięte samochody z tabeli samochód wpisze do tabeli samochod_delete.
507--Przetestuj utworzony wyzwalacz – usuń z tabeli samochód samochody o identyfikatorach z przedziału od 1 do 4. 
508
509SELECT * INTO samochod_delete FROM samochod;
510GO
511CREATE TRIGGER usun_samochod ON samochod
512FOR DELETE AS
513BEGIN
514	DECLARE kursor_samochod_delete CURSOR
515	FOR SELECT * FROM DELETED;
516	OPEN kursor_samochod_delete
517	DECLARE @id_samochod INT, @marka VARCHAR(20), @typ VARCHAR(16), @data_prod DATETIME, @kolor VARCHAR(16), @poj_silnika SMALLINT, @przebieg INT
518	FETCH NEXT FROM kursor_samochod_delete INTO @id_samochod, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
519	WHILE @@FETCH_STATUS = 0
520	BEGIN
521		INSERT INTO samochod_delete VALUES (@id_samochod, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg)
522		FETCH NEXT FROM kursor_samochod_delete INTO @id_samochod, @marka, @typ, @data_prod, @kolor, @poj_silnika, @przebieg
523	END
524	CLOSE kursor_samochod_delete
525	DEALLOCATE kursor_samochod_delete
526END
527GO
528
529DELETE FROM samochod WHERE id_samochod IN(1, 2, 3, 4);
530GO
531
532-- 38.3 Napisać wyzwalacz, który po podwyżce pensji pracownikowi będzie zerował jego dodatek.
533--Przetestuj utworzony wyzwalacz – pracownikom o identyfikatorach równych 1, 2 i 3 zwiększ pensję o 200 zł. 
534
535CREATE TRIGGER podwyzka ON pracownik
536FOR UPDATE AS
537BEGIN
538	DECLARE kursor_pracownik_update CURSOR
539	FOR SELECT pensja, id_pracownik FROM DELETED;
540	OPEN kursor_pracownik_update
541	DECLARE @pensja MONEY, @id_pracownik INT
542	FETCH NEXT FROM kursor_pracownik_update INTO @pensja, @id_pracownik
543	WHILE @@FETCH_STATUS = 0
544	BEGIN
545		UPDATE pracownik SET dodatek=0 WHERE id_pracownik=@id_pracownik
546		FETCH NEXT FROM kursor_pracownik_update INTO @pensja, @id_pracownik
547	END
548	CLOSE kursor_pracownik_update
549	DEALLOCATE kursor_pracownik_update
550END
551GO
552
553--WIDOKI +CASE
554
555--39.1 Utwórz widok o nazwie ocena_klienta, który dla każdego klienta (id_klient, imie, nazwisko) wyświetli informację,
556--czy to jest stały klient (czyli wypisze słowo TAK lub NIE jako wartość kolumny "staly_klient").
557--Przyjmijmy, że stały klient to taki, który wypożyczył co najmniej dwa razy samochód.
558--Użyj powyższego widoku i wypisz stałych klientów sortując wynik alfabetycznie po nazwisku i imieniu klienta. 
559
560CREATE VIEW ocena_klienta AS
561SELECT k.id_klient, k.imie, k.nazwisko, CASE WHEN COUNT(w.id_klient)>=2 THEN 'TAK' ELSE 'NIE' END AS staly_klient
562FROM klient k LEFT JOIN wypozyczenie w ON k.id_klient=w.id_klient
563GROUP BY k.id_klient, k.imie, k.nazwisko;
564GO
565SELECT * FROM ocena_klienta WHERE staly_klient='TAK' ORDER BY nazwisko, imie;
566
567--39.2 Utwórz widok o nazwie pensja_pracownika, który dla każdego pracownika (id_pracownik, imie, nazwisko)
568--wyświetli informację w nowej kolumnie o nazwie "zarobki" o rzędzie jego zarobków: MAŁO, ŚREDNIO, DUŻO.
569--Przyjmijmy, że pracownik zarabia MAŁO jeśli pensja jest poniżej 1500 zł, zarabia ŚREDNIO jeśli pensja jest od 1500 zł
570---do 3000 zł, a zarabia dużo jeśli pensja jest większa od 3000 zł.
571--Użyj powyższego widoku do znalezienia pracowników zarabiających MAŁO. 
572
573CREATE VIEW pensja_pracownika AS
574SELECT p.id_pracownik,p.imie,p.nazwisko,  CASE WHEN p.pensja<1500 THEN 'malo' WHEN p.pensja<3000 THEN 'srednio' ELSE 'duzo' END AS zarobki
575FROM pracownik p;
576
577SELECT*FROM pensja_pracownika where zarobki='malo';
578
579--39.3 Utwórz widok o nazwie "pracownik_informacje", który wyświetla o każdym pracowniku takie informacje jak:
580--a) id_pracownik, b) imie, c) nazwisko, d) kolumna wyliczeniowa "staz_pracy", czyli długość zatrudnienia w latach,
581--e) kolumna wyliczeniowa "wypozyczenia", czyli całkowita ilość wypożyczeń samochodów, f) kolumna wyliczeniowa
582--"dodatek" z wartościami TAK/NIE/BRAK (TAK gdy dodatek>0, NIE gdy dodatek=0, BRAK w pozostałych przypadkach).
583--Przetestuj utworzony widok wyszukując pracowników, dla których dodatek nie został przyznany.
584
585CREATE VIEW pracownik_informacje AS
586SELECT p.id_pracownik,p.imie,p.nazwisko, DATEDIFF(YY,p.data_zatr,GETDATE()) AS staz_pracy, COUNT(w.id_pracow_wyp) AS wypozyczenia,
587CASE WHEN p.dodatek>0 THEN 'TAK' WHEN p.dodatek=0 THEN 'NIE' else 'BRAK' END AS dodatek FROM pracownik p LEFT JOIN wypozyczenie w ON p.id_pracownik=w.id_pracow_wyp
588GROUP BY p.id_pracownik,p.imie,p.nazwisko,p.data_zatr,p.dodatek;
589GO
590
591SELECT*FROM pracownik_informacje
592
593--PROCEDURY + DYNAMICZNY SQL
594
595
596--40.1 Napisać procedurę o nazwie "usun_widoki", która z bieżącej bazy danych usunie wszystkie widoki.
597--Przetestuj działanie utworzonej procedury.
598--Wsk 1: SELECT * FROM INFORMATION_SCHEMA.TABLES;
599--Wsk 2: EXEC sp_executesql @zapytanie_sql 
600
601CREATE PROCEDURE usun_widoki AS
602BEGIN
603 DECLARE @sql NVARCHAR(200);
604 WHILE EXISTS(SELECT TOP 1 1
605 FROM INFORMATION_SCHEMA.TABLES
606 WHERE TABLE_CATALOG=DB_NAME() AND TABLE_TYPE='VIEW')
607 BEGIN
608 SELECT @sql='DROP VIEW ' + TABLE_NAME
609 FROM INFORMATION_SCHEMA.TABLES
610 WHERE TABLE_CATALOG=DB_NAME() AND TABLE_TYPE='VIEW';
611 EXEC sp_executesql @sql
612 END
613END
614GO
615EXEC usun_widoki; 
616
617-- 40.2 Napisać procedurę o nazwie "usun_klucze_obce", która z bieżącej bazy danych usunie wszystkie klucze obce.
618--Przetestuj działanie utworzonej procedury.
619--Wsk 1: SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
620
621
622CREATE PROCEDURE usun_klucze_obce AS
623BEGIN
624	WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
625	BEGIN
626		DECLARE @sql NVARCHAR(2000)
627		SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
628		+ '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
629		FROM information_schema.table_constraints
630		WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
631		EXEC (@sql)
632	END
633END
634
635EXEC usun_klucze_obce
636
637SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
638GO
639
640-- 40.3 Napisać procedurę o nazwie "klient_dodaj_kolumne_rabat", która sprawdza czy w tabeli klient istnieje kolumna
641--o nazwie "rabat", jeśli nie to dodaje ją do tabeli klient. Kolumna "rabat" powinna być typu INT i przyjmować
642--domyślnie wartość 0. Wszyscy istniejący klienci w tabeli klient powinni mieć ustawioną wartość rabatu na 0.
643--Przetestuj działanie procedury. 
644
645CREATE PROCEDURE klient_dodaj_kolumne_rabat AS
646BEGIN
647	IF COL_LENGTH('klient','rabat') IS NULL
648	 BEGIN
649	 ALTER TABLE klient ADD rabat INT DEFAULT 0
650	 INSERT INTO klient(rabat) VALUES (0)
651	 END
652END
653
654EXEC klient_dodaj_kolumne_rabat