· 8 years ago · May 29, 2018, 05:36 PM
1--ImiÄ™ i nazwisko: Marcel Dajnowicz
2--Numer indeksu: 253971
3--Temat bazy danych: Lotnisko
4
5-- 0) Poprawione rozwiÄ…zanie zadania 1b (skrypt generujÄ…cy strukturÄ™ bazy danych)
6
7--Zmiana formatu daty (polecenie zgodne z MSSQL)
8SET DATEFORMAT ymd;
9GO
10
11--Utworzenie wymaganych tabel
12CREATE TABLE oplata (
13 id_oplata int IDENTITY(1,1) PRIMARY KEY,
14 nazwa_terminala VARCHAR(30) NOT NULL,
15 data DATETIME NOT NULL,
16 kwota MONEY NOT NULL CHECK(kwota>=0),
17);
18GO
19
20INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2018-09-02','200');
21INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2018-5-22','23450');
22INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2018-12-12','3560');
23INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2016-02-12','856');
24INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2017-04-22','550');
25INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2017-03-02','456');
26INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2016-12-22','3250');
27INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2016-02-12','26');
28INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2017-04-22','7250');
29INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2018-12-01','32504');
30INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2018-09-22','256');
31INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2018-07-02','6250');
32
33
34CREATE TABLE typ_oplaty (
35 id_typ_oplaty int IDENTITY(1,1) PRIMARY KEY,
36 nazwa VARCHAR(30) NOT NULL,
37 oplata_id INTEGER NOT NULL REFERENCES oplata(id_oplata) ON UPDATE CASCADE,
38);
39GO
40
41
42
43INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',1);
44INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',2);
45INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Karta Kredytowa',3);
46INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Krata Kredytowa',4);
47INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',5);
48
49
50
51
52
53CREATE TABLE klasa_samolotowa (
54 id_klasa_samolotowa int IDENTITY(1,1) PRIMARY KEY,
55 nazwa_klasy VARCHAR(20) NOT NULL,
56 opis_klasy VARCHAR(20) NULL,
57);
58GO
59
60INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('Economic','tanie');
61INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('Super-economic','super tanie');
62INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('luxary','dla bogaczy');
63INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('super-luxary','dla super bogaczy');
64INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('normal','dla sredniakow');
65
66
67CREATE TABLE posilek (
68 id_posilek int IDENTITY(1,1) PRIMARY KEY,
69 nazwa VARCHAR(30) NOT NULL,
70 klasa_samolotowa_id INTEGER NOT NULL REFERENCES klasa_samolotowa(id_klasa_samolotowa) ON UPDATE CASCADE,
71);
72GO
73
74INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('zupa',1);
75INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('drugie danie',2);
76INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('hot dog',3);
77INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('napoj',4);
78INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('pelny posilek',5);
79
80CREATE TABLE typ_samolotu (
81 id_typ_samolotu int IDENTITY(1,1) PRIMARY KEY,
82 nazwa_typu VARCHAR(10) NOT NULL,
83 opis VARCHAR(100) NOT NULL,
84);
85GO
86
87INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('BOJING','potwor');
88INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Posejdon','przewozi czolgi');
89INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Mars','leci na wojne');
90INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Copter','maly');
91INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('AIRFORCE 1','malo-wazny');
92
93
94
95
96CREATE TABLE producent_samolotu (
97 id_producent_samolotu int IDENTITY(1,1) PRIMARY KEY,
98 nazwa_producenta VARCHAR(10) NOT NULL,
99 ceo VARCHAR(20) NOT NULL,
100 rok_zalozenia DATETIME NULL,
101);
102GO
103
104INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Ferrari','Adam Johnson', '2008-05-09');
105INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Bugatti', 'Adam Johnson','2010-03-29');
106INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Maluch', 'Adam Johnson','2007-03-19');
107INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Amrix', 'Adam Johnson','1997-12-01');
108INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Intel', 'Adam Johnson','1997-09-19');
109INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('IBM', 'Adam Johnson','1996-09-19');
110INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('SpaceX', 'Adam Johnson','2018-01-19');
111
112
113CREATE TABLE samolot (
114 id_samolot int IDENTITY(1,1) PRIMARY KEY,
115 nazwa VARCHAR(20) NOT NULL UNIQUE,
116 numer VARCHAR(20) NOT NULL UNIQUE,
117 model VARCHAR(20) NOT NULL UNIQUE,
118 pojemnosc VARCHAR(10) NOT NULL,
119 producent_samolotu_id INTEGER NOT NULL REFERENCES producent_samolotu(id_producent_samolotu) ON UPDATE CASCADE,
120 typ_samolotu_id INTEGER NOT NULL REFERENCES typ_samolotu(id_typ_samolotu) ON UPDATE CASCADE,
121);
122GO
123
124INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Markotny','23','1 Generacja','201',4,5);
125INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Latajacy','223','X1X','5',5,4);
126INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Nurek','1','Supreme','566',3,3);
127INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('SuperSlim','235','Stealth','23',2,2);
128INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Pitchfork','2','Short','1',1,1);
129INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Niszczyciel','12223','SuperHej','66',1,3);
130INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Furiat','278','Quicki','234',1,2);
131INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('SuperFer','2232','Space','1',1,1);
132
133
134CREATE TABLE miejsce_samolotowe (
135 id_miejsce_samolotowe int IDENTITY(1,1) PRIMARY KEY,
136 numer_miejsca VARCHAR(5) NOT NULL UNIQUE,
137 klasa_samolotowa_id INTEGER NOT NULL REFERENCES klasa_samolotowa(id_klasa_samolotowa),
138 samolot_id INTEGER NOT NULL REFERENCES samolot(id_samolot),
139);
140GO
141
142INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('56',1,1);
143INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('78',2,2);
144INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('101',3,3);
145INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('523',4,4);
146INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('23',5,5);
147
148
149CREATE TABLE status_lotu (
150 id_status_lotu int IDENTITY(1,1) PRIMARY KEY,
151 nazwa VARCHAR(20) NOT NULL UNIQUE,
152 opis VARCHAR(30) NOT NULL,
153 data DATETIME NOT NULL DEFAULT GETDATE(),
154 opoznienia VARCHAR(20) NULL,
155 );
156GO
157
158INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('Great Line','papieros na pokaldzie','2012-02-03','');
159INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('MAGA','cos nie tak','2016-11-14','');
160INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('InterCont','atak','2017-10-25','2 godziny');
161INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('WOHO','wszystko w normie','2014-01-12','');
162INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('LETSGO','wszystko w normie','2018-12-03','dwa dni');
163
164
165CREATE TABLE lot (
166 id_lot int IDENTITY(1,1) PRIMARY KEY,
167 opis VARCHAR(20) NULL,
168 status_lotu_id INTEGER NOT NULL REFERENCES status_lotu(id_status_lotu) ON UPDATE CASCADE,
169 typ_samolotu_id INTEGER NOT NULL REFERENCES typ_samolotu(id_typ_samolotu) ON UPDATE CASCADE,
170);
171GO
172
173INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('fajny',1,1);
174INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('niebezpieczny',1,1);
175INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('wszystko-ok',1,2);
176INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('',2,3);
177INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('zderzenie',3,4);
178
179
180CREATE TABLE cena_za_bilet (
181 id_cena_za_bilet int IDENTITY(1,1) PRIMARY KEY,
182 cena_za_bilet VARCHAR(10) NOT NULL,
183 miejsce_samolotowe_id INTEGER NOT NULL REFERENCES miejsce_samolotowe(id_miejsce_samolotowe) ON UPDATE CASCADE,
184 lot_id INTEGER NOT NULL REFERENCES lot(id_lot) ON UPDATE CASCADE,
185);
186GO
187
188INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('123',1,1);
189INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('1232',2,2);
190INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('745',3,3);
191INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('235',4,4);
192INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('3462',5,5);
193
194
195CREATE TABLE kraj (
196 kraj_id int IDENTITY(1,1) PRIMARY KEY,
197 nazwa VARCHAR(20) NOT NULL,
198);
199GO
200
201INSERT INTO kraj(nazwa) VALUES ('Polska');
202INSERT INTO kraj(nazwa) VALUES ('USA');
203INSERT INTO kraj(nazwa) VALUES ('Argentyna');
204INSERT INTO kraj(nazwa) VALUES ('Niemcy');
205INSERT INTO kraj(nazwa) VALUES ('UK');
206
207
208CREATE TABLE pasazer (
209 id_pasazer int IDENTITY(1,1) PRIMARY KEY,
210 imie VARCHAR(20) NOT NULL CHECK(LEN(imie)>2),
211 drugie_imie VARCHAR(20) NOT NULL,
212 nazwisko VARCHAR(30) NOT NULL CHECK(LEN(nazwisko)>2),
213 numer_telefonu VARCHAR(20) NOT NULL,
214 adres_email VARCHAR(30) NOT NULL,
215 numer_paszportu VARCHAR(30) NOT NULL UNIQUE,
216 data_urodzenia DATETIME,
217 czy_wydał_w_liniach MONEY,
218 kraj_id INTEGER NOT NULL REFERENCES kraj(kraj_id) ON UPDATE CASCADE,
219);
220GO
221
222INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Marcel','Michal','Dajnowicz','12345678','dajnowiczmarcel@wp.pl','12312','1998-12-03',2000,1);
223INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Julia','Blanka','Zubka','71727364','powazny@.pl','41245','1997-05-09',19534,2);
224INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Magda','Aga','Czekalska','3252345','buziaczek@.wp.pl','512512','1996-05-17',0,2);
225INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Jakub','Paweł','Nowak','25323523','hejka@wp.pl','125125','1995-02-03',0,4);
226INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Jakub','Mateusz','Rachwał','745457','lekarz@.pl','421245','1994-04-03',4324,5);
227
228
229CREATE TABLE rezerwacja (
230 id_rezerwacja int IDENTITY(1,1) PRIMARY KEY,
231 cena_za_bilet_id INTEGER NOT NULL REFERENCES cena_za_bilet(id_cena_za_bilet) ON UPDATE CASCADE,
232 pasazer_id INTEGER NOT NULL REFERENCES pasazer(id_pasazer) ON UPDATE CASCADE,
233 uwagi VARCHAR(30) NULL,
234);
235GO
236
237INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (1,1,'meh');
238INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (2,1,'sad');
239INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,1,'wooooow');
240INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,1,'nice');
241INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (4,2,'zly system');
242INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (5,2,'jest oki');
243INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (2,2,'swietna sprawa');
244INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,3,'genialny system');
245INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (4,4,'SUPER BAZA DANYCH');
246INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (5,5,'ok');
247
248CREATE TABLE kierunek (
249 id_kierunek int IDENTITY(1,1) PRIMARY KEY,
250 kierunek_swiata VARCHAR(20) NOT NULL,
251);
252GO
253
254INSERT INTO kierunek(kierunek_swiata) VALUES ('polnoc');
255INSERT INTO kierunek(kierunek_swiata) VALUES ('poludnie');
256INSERT INTO kierunek(kierunek_swiata) VALUES ('wschod');
257INSERT INTO kierunek(kierunek_swiata) VALUES ('polnocny-zachod');
258INSERT INTO kierunek(kierunek_swiata) VALUES ('zachod');
259
260
261CREATE TABLE lotnisko (
262 id_lotnisko int IDENTITY(1,1) PRIMARY KEY,
263 nazwa_lotniska VARCHAR(20) NOT NULL,
264 miasto VARCHAR(20) NOT NULL,
265 ulica VARCHAR(30) NOT NULL,
266 numer_ulicy VARCHAR(10) NOT NULL,
267 kod_pocztowy VARCHAR(10) NOT NULL,
268 kraj_id INTEGER NOT NULL REFERENCES kraj(kraj_id) ON UPDATE CASCADE,
269 kierunek_id INTEGER NOT NULL REFERENCES kierunek(id_kierunek) ON UPDATE CASCADE,
270);
271GO
272
273INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Lech Walesa','Gdansk','legionow','201','12-344',1,4);
274INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Marcel Airport','Marcelolandia','Marcela','1','57-784',1,4);
275INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Okecie','Warszawa','legionow','201','12-344',1,1);
276INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Luton','London','legionow','201','12-344',5,3);
277INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Dutch','Amsterdam','legionow','201','12-344',4,2);
278
279
280CREATE TABLE plan_lotu (
281 id_plan_lotu int IDENTITY(1,1) PRIMARY KEY,
282 czas_wylotu DATE NOT NULL,
283 czas_przylotu DATE NOT NULL,
284 kierunek_id INTEGER NOT NULL REFERENCES kierunek(id_kierunek) ON UPDATE CASCADE,
285 lot_id INTEGER NOT NULL REFERENCES lot(id_lot) ON UPDATE CASCADE,
286);
287GO
288
289INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-12-03','2018-12-03',3,5);
290INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2014-01-12','2014-01-12',1,3);
291INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2017-10-25','2017-10-25',1,3);
292INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2016-11-13','2016-11-14',2,3);
293INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2012-02-03','2012-02-04',2,3);
294INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-06','2018-05-07',1,3);
295INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-01','2018-05-02',2,3);
296INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-28','2018-05-29',2,3);
297
298
299
300--1a) Tworzy widok o nazwie "pasazer_informacje", który wyświetla o każdym pasażerze takie informacje jak:
301--id_pracownik, imie, nazwisko, kolumna wyliczeniowa "ilosc_lat",
302--kolumna wyliczeniowa "ilosc_rez", czyli całkowita ilość rezerwacji samolotwych, kolumna wyliczeniowa
303--"czy_wydał_w_liniach" z wartościami TAK/NIE/BRAK (TAK gdy pasażer wydał coś na pokładzie, NIE gdy nigdy nic nie kupuil, BRAK w pozostałych przypadkach).
304--(UŻYCIE CASE)
305CREATE VIEW pasazer_informacje AS
306SELECT p.id_pasazer,p.imie,p.nazwisko, DATEDIFF(YY,p.data_urodzenia,GETDATE()) AS "ilosc_lat", COUNT(r.pasazer_id) AS "ilosc_rez",
307CASE WHEN p.czy_wydał_w_liniach>0 THEN 'TAK' WHEN p.czy_wydał_w_liniach=0 THEN 'NIE' else 'BRAK' END AS czy_kupowal FROM pasazer p LEFT JOIN rezerwacja r ON p.id_pasazer=r.pasazer_id
308GROUP BY p.id_pasazer,p.imie,p.nazwisko,p.data_urodzenia,p.czy_wydał_w_liniach;
309GO
310
311--1b) Sprawdzenie, że widok działa dla osób, które kupiły więcej bieletów niż srednia kupionych oraz którzy są pełnoletni i coś kiedyś kupili na pokładzie
312SELECT * FROM pasazer_informacje GROUP BY id_pasazer,imie, nazwisko, ilosc_rez, ilosc_lat,czy_kupowal HAVING ilosc_rez>(SELECT AVG(ilosc_rez) FROM pasazer_informacje WHERE ilosc_lat > 18 AND czy_kupowal = 'TAK') ;
313Go
314
315--2a) Tworzymy funkcję 1 o nazwie producent_ile_samolotów, która będzie zwracać ilośc samolotów które wyprodukował dany producent.
316--(UŻYCIE IF-ELSE)
317CREATE FUNCTION dbo.producent_ile_samolotów (
318 @id_producent_samolotu INT
319) RETURNS INT
320BEGIN
321IF (SELECT COUNT(*) FROM samolot WHERE producent_samolotu_id=@id_producent_samolotu) =0
322RETURN 0
323ELSE
324RETURN (SELECT COUNT(*) FROM samolot
325 WHERE producent_samolotu_id=@id_producent_samolotu)
326RETURN 0
327END;
328GO
329
330--2b) Sprawdzenie, że funkcja 1 działa poprzez przykład producenta "Ferrari".
331SELECT dbo.producent_ile_samolotów(1) AS ile_samolotów;
332
333--3a) Tworzymy funkcję 2 o nazwie ile_samolotów posiadającą dwa parametry czas_wylot i czas_przylotu. Funkcja powinna
334--zwrócić ilość lotów samolotowych w zadanym przedziale czasowym.
335CREATE FUNCTION dbo.ile_samolotów (
336 @czas_wylotu DATE, @czas_przylotu DATE
337 ) RETURNS INT
338 BEGIN RETURN (SELECT COUNT(*) FROM plan_lotu
339 WHERE czas_przylotu<=@czas_przylotu AND czas_wylotu >=@czas_wylotu )
340END;
341GO
342
343--3b) Sprawdzenie, że funkcja 2 działa poprzez sprawdzenie ile samalotów latało w Maju.
344SELECT dbo.ile_samolotów('2018-05-01', '2018-05-29') AS ile_samolotów_w_maju;
345GO
346
347SELECT * FROM pasazer
348
349--4a) Tworzymy procedurę 1, która obniża cene dla pasażera który najczęsciej podróżuje.
350CREATE PROC obniżka_opłat_dla_najczesciej_podrozujacych
351@obnizka MONEY
352AS BEGIN
353DECLARE @id_najczestszego_pasazera INT
354SET @id_najczestszego_pasazera=(SELECT TOP 1 p.id_pasazer FROM pasazer p JOIN rezerwacja r ON p.id_pasazer=r.pasazer_id GROUP BY p.id_pasazer
355ORDER BY COUNT(p.id_pasazer ) DESC)
356UPDATE pasazer SET czy_wydał_w_liniach=czy_wydał_w_liniach-@obnizka WHERE id_pasazer=@id_najczestszego_pasazera;
357END
358
359--4b) Sprawdzenie, że procedura 1 działa
360EXEC obniżka_opłat_dla_najczesciej_podrozujacych 500;
361GO
362
363--5a) Tworzymy procedurę 2, która tóra z bieżącej bazy danych usunie wszystkie klucze obce.
364--(UŻYCIE IF EXISTS)
365CREATE PROCEDURE usun_klucze_obce AS
366BEGIN
367 WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
368 BEGIN
369 DECLARE @sql NVARCHAR(2000)
370 SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
371 + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
372 FROM information_schema.table_constraints
373 WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
374 EXEC (@sql)
375 END
376END
377
378--5b) Sprawdzenie, że procedura 2 działa
379EXEC usun_klucze_obce
380
381SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
382GO
383
384--6a) Tworzymy wyzwalacz 1 który po dodaniu kolejnego zakupu pasazera zmniejszy jego dług o 10% wraz z zakupionym ostatnio produktem(Milionowy Klient).
385--(UŻYCIE WHILE)
386CREATE TRIGGER obnizka ON pasazer
387FOR UPDATE AS
388BEGIN
389 DECLARE kursor_pasazer_update CURSOR
390 FOR SELECT czy_wydał_w_liniach, id_pasazer FROM DELETED;
391 OPEN kursor_pasazer_update
392 DECLARE @czy_wydał_w_liniach MONEY, @id_pasazer INT
393 FETCH NEXT FROM kursor_pasazer_update INTO @czy_wydał_w_liniach, @id_pasazer
394 WHILE @@FETCH_STATUS = 0
395 BEGIN
396 UPDATE pasazer SET czy_wydał_w_liniach=czy_wydał_w_liniach*0.90 WHERE id_pasazer=@id_pasazer
397 FETCH NEXT FROM kursor_pasazer_update INTO @czy_wydał_w_liniach, @id_pasazer
398 END
399 CLOSE kursor_pasazer_update
400 DEALLOCATE kursor_pasazer_update
401END
402GO
403
404--6b) Sprawdzenie, że wyzwalacz 1 działa
405UPDATE pasazer SET czy_wydał_w_liniach=10000 WHERE id_pasazer IN(1);
406
407--7a) Tworzymy wyzwalacz 2, który zablokuje nam dodanie nowego pasażera z długiem.
408CREATE TRIGGER pasazer_ins ON pasazer
409AFTER INSERT AS
410BEGIN
411 DECLARE @czy_wydał_w_liniach MONEY
412 SET @czy_wydał_w_liniach=-1
413 SELECT @czy_wydał_w_liniach=czy_wydał_w_liniach FROM INSERTED WHERE czy_wydał_w_liniach>0
414 IF @czy_wydał_w_liniach>0
415 BEGIN
416 RAISERROR('nowy pasazer nie moze miec dlugu', 1, 2);
417 ROLLBACK
418 END
419END
420GO
421
422--7b) Sprawdzenie, że wyzwalacz 2 działa
423INSERT INTO pasazer(imie,drugie_imie,nazwisko, numer_telefonu, adres_email, numer_paszportu, data_urodzenia, czy_wydał_w_liniach, kraj_id) VALUES ('Dagmara','Iwona','Kowalska','129371','dagmara@wp.pl','1241244','1963-12-03',124,1);
424
425--8a) Tworzymy wzywalacz 3, który przy usuwaniu producenta samolotu daje nam informacje o jego załozycielu i nazwie.
426--(UZYCIE KURSORA)
427CREATE TRIGGER usun_producenta_samolotu ON producent_samolotu
428AFTER DELETE
429AS
430BEGIN
431 DECLARE kursor__producent_samolot_delete CURSOR
432 FOR SELECT nazwa_producenta, ceo FROM DELETED;
433 DECLARE @nazwa_producenta VARCHAR(10), @ceo VARCHAR(20)
434
435 OPEN kursor__producent_samolot_delete
436 FETCH NEXT FROM kursor__producent_samolot_delete INTO @nazwa_producenta, @ceo
437 WHILE @@FETCH_STATUS = 0
438 BEGIN
439 PRINT 'Usunieto ' + @nazwa_producenta+ ' zalozonego przez ' + @ceo
440 FETCH NEXT FROM kursor__producent_samolot_delete INTO @nazwa_producenta, @ceo
441 END
442 CLOSE kursor__producent_samolot_delete
443 DEALLOCATE kursor__producent_samolot_delete
444END
445
446--8b) Sprawdzenie, że wyzwalacz 3 działa
447DELETE FROM producent_samolotu WHERE id_producent_samolotu IN(6, 7);
448GO
449
450SELECT * FROM producent_samolotu;
451
452--9a) Tworzymy wyzwalacz 4, który nie pozwala nam dodawac zmieniac i usuwac informacji o typach samolotów.
453CREATE TRIGGER typ_samolotu_blokada ON typ_samolotu
454INSTEAD OF INSERT, UPDATE, DELETE
455AS
456 PRINT('NIE MOZNA ZMIENIAC TYPU SAMOLOTU')
457GO
458
459--9b) Sprawdzenie, że wyzwalacz 4 działa
460DELETE FROM typ_samolotu WHERE id_typ_samolotu IN(1,2)
461
462--10) Tworzę tabelę przestawną, która przedstawia sume wpłat dla trzech ostatnich lat na trzy stanowiska.
463SELECT nazwa_terminala, [2018] as ROK2018, [2017] AS ROK2017, [2016] AS ROK2016
464FROM
465(
466 SELECT nazwa_terminala, YEAR(data) as wplata, kwota
467 FROM oplata
468) tabela
469PIVOT
470(
471 SUM(kwota)
472 FOR wplata IN ([2018],[2017],[2016])
473) AS p
474ORDER BY nazwa_terminala
475
476--KONIEC
477
478--DROPS
479
480drop view pasazer_informacje;
481drop function dbo.producent_ile_samolotów;
482drop function dbo.ile_samolotów
483drop proc obniżka_opłat_dla_najczesciej_podrozujacych
484drop proc usun_klucze_obce
485drop trigger obnizka
486drop trigger double_lotnisko
487drop trigger usun_producenta_samolotu
488drop trigger usun_producenta_samolotu
489drop trigger typ_samolotu_blokada