--Imię i nazwisko: Marcel Dajnowicz
--Numer indeksu: 253971
--Temat bazy danych: Lotnisko

-- 0) Poprawione rozwiązanie zadania 1b (skrypt generujący strukturę bazy danych)

--Zmiana formatu daty (polecenie zgodne z MSSQL)
SET DATEFORMAT ymd;
GO

--Utworzenie wymaganych tabel
CREATE TABLE oplata (
  id_oplata int IDENTITY(1,1) PRIMARY KEY,
  nazwa_terminala VARCHAR(30) NOT NULL,
  data DATETIME NOT NULL,
  kwota MONEY NOT NULL CHECK(kwota>=0),
);
GO

INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2018-09-02','200');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2018-5-22','23450');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2018-12-12','3560');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2016-02-12','856');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2017-04-22','550');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2017-03-02','456');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2016-12-22','3250');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2016-02-12','26');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2017-04-22','7250');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('a','2018-12-01','32504');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('b','2018-09-22','256');
INSERT INTO oplata(nazwa_terminala,data,kwota) VALUES ('c','2018-07-02','6250');


CREATE TABLE typ_oplaty (
  id_typ_oplaty int IDENTITY(1,1) PRIMARY KEY,
  nazwa VARCHAR(30) NOT NULL,
  oplata_id INTEGER NOT NULL REFERENCES oplata(id_oplata) ON UPDATE CASCADE,
);
GO



INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',1);
INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',2);
INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Karta Kredytowa',3);
INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Krata Kredytowa',4);
INSERT INTO typ_oplaty(nazwa,oplata_id) VALUES ('Gotówka',5);





CREATE TABLE klasa_samolotowa (
  id_klasa_samolotowa int IDENTITY(1,1) PRIMARY KEY,
  nazwa_klasy VARCHAR(20) NOT NULL,
  opis_klasy VARCHAR(20) NULL,
);
GO

INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('Economic','tanie');
INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('Super-economic','super tanie');
INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('luxary','dla bogaczy');
INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('super-luxary','dla super bogaczy');
INSERT INTO klasa_samolotowa(nazwa_klasy,opis_klasy) VALUES ('normal','dla sredniakow');


CREATE TABLE posilek (
  id_posilek int IDENTITY(1,1) PRIMARY KEY,
  nazwa VARCHAR(30) NOT NULL,
  klasa_samolotowa_id INTEGER NOT NULL REFERENCES klasa_samolotowa(id_klasa_samolotowa) ON UPDATE CASCADE,
);
GO

INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('zupa',1);
INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('drugie danie',2);
INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('hot dog',3);
INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('napoj',4);
INSERT INTO posilek(nazwa,klasa_samolotowa_id) VALUES ('pelny posilek',5);

CREATE TABLE typ_samolotu (
  id_typ_samolotu int IDENTITY(1,1) PRIMARY KEY,
  nazwa_typu VARCHAR(10) NOT NULL,
  opis VARCHAR(100) NOT NULL,
);
GO

INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('BOJING','potwor');
INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Posejdon','przewozi czolgi');
INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Mars','leci na wojne');
INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('Copter','maly');
INSERT INTO typ_samolotu(nazwa_typu, opis) VALUES ('AIRFORCE 1','malo-wazny');




CREATE TABLE producent_samolotu (
  id_producent_samolotu int IDENTITY(1,1) PRIMARY KEY,
  nazwa_producenta VARCHAR(10) NOT NULL,
  ceo VARCHAR(20) NOT NULL,
  rok_zalozenia DATETIME NULL,
);
GO

INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Ferrari','Adam Johnson', '2008-05-09');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Bugatti', 'Adam Johnson','2010-03-29');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Maluch', 'Adam Johnson','2007-03-19');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Amrix', 'Adam Johnson','1997-12-01');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('Intel', 'Adam Johnson','1997-09-19');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('IBM', 'Adam Johnson','1996-09-19');
INSERT INTO producent_samolotu(nazwa_producenta, ceo, rok_zalozenia) VALUES ('SpaceX', 'Adam Johnson','2018-01-19');


CREATE TABLE samolot (
  id_samolot int IDENTITY(1,1) PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL UNIQUE,
  numer VARCHAR(20) NOT NULL UNIQUE,
  model VARCHAR(20) NOT NULL UNIQUE,
  pojemnosc VARCHAR(10) NOT NULL,
  producent_samolotu_id INTEGER NOT NULL REFERENCES producent_samolotu(id_producent_samolotu) ON UPDATE CASCADE,
  typ_samolotu_id INTEGER NOT NULL REFERENCES typ_samolotu(id_typ_samolotu) ON UPDATE CASCADE,
);
GO

INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Markotny','23','1 Generacja','201',4,5);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Latajacy','223','X1X','5',5,4);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Nurek','1','Supreme','566',3,3);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('SuperSlim','235','Stealth','23',2,2);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Pitchfork','2','Short','1',1,1);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Niszczyciel','12223','SuperHej','66',1,3);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('Furiat','278','Quicki','234',1,2);
INSERT INTO samolot(nazwa,numer,model, pojemnosc, producent_samolotu_id, typ_samolotu_id) VALUES ('SuperFer','2232','Space','1',1,1);


CREATE TABLE miejsce_samolotowe (
  id_miejsce_samolotowe int IDENTITY(1,1) PRIMARY KEY,
  numer_miejsca VARCHAR(5) NOT NULL UNIQUE,
  klasa_samolotowa_id INTEGER NOT NULL REFERENCES klasa_samolotowa(id_klasa_samolotowa),
  samolot_id INTEGER NOT NULL REFERENCES samolot(id_samolot),
);
GO

INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('56',1,1);
INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('78',2,2);
INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('101',3,3);
INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('523',4,4);
INSERT INTO miejsce_samolotowe(numer_miejsca,klasa_samolotowa_id,samolot_id) VALUES ('23',5,5);


CREATE TABLE status_lotu (
  id_status_lotu int IDENTITY(1,1) PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL UNIQUE,
  opis VARCHAR(30) NOT NULL,
  data DATETIME NOT NULL DEFAULT GETDATE(),
  opoznienia VARCHAR(20) NULL,
  );
GO

INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('Great Line','papieros na pokaldzie','2012-02-03','');
INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('MAGA','cos nie tak','2016-11-14','');
INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('InterCont','atak','2017-10-25','2 godziny');
INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('WOHO','wszystko w normie','2014-01-12','');
INSERT INTO status_lotu(nazwa, opis,data,opoznienia) VALUES ('LETSGO','wszystko w normie','2018-12-03','dwa dni');


CREATE TABLE lot (
  id_lot int IDENTITY(1,1) PRIMARY KEY,
  opis VARCHAR(20) NULL,
  status_lotu_id INTEGER NOT NULL REFERENCES status_lotu(id_status_lotu) ON UPDATE CASCADE,
  typ_samolotu_id INTEGER NOT NULL REFERENCES typ_samolotu(id_typ_samolotu) ON UPDATE CASCADE,
);
GO

INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('fajny',1,1);
INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('niebezpieczny',1,1);
INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('wszystko-ok',1,2);
INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('',2,3);
INSERT INTO lot(opis, status_lotu_id,typ_samolotu_id) VALUES ('zderzenie',3,4);


CREATE TABLE cena_za_bilet (
  id_cena_za_bilet int IDENTITY(1,1) PRIMARY KEY,
  cena_za_bilet VARCHAR(10) NOT NULL,
  miejsce_samolotowe_id INTEGER NOT NULL REFERENCES miejsce_samolotowe(id_miejsce_samolotowe) ON UPDATE CASCADE,
  lot_id INTEGER NOT NULL REFERENCES lot(id_lot) ON UPDATE CASCADE,
);
GO

INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('123',1,1);
INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('1232',2,2);
INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('745',3,3);
INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('235',4,4);
INSERT INTO cena_za_bilet(cena_za_bilet, miejsce_samolotowe_id,lot_id) VALUES ('3462',5,5);


CREATE TABLE kraj (
  kraj_id int IDENTITY(1,1) PRIMARY KEY,
  nazwa VARCHAR(20) NOT NULL,
);
GO

INSERT INTO kraj(nazwa) VALUES ('Polska');
INSERT INTO kraj(nazwa) VALUES ('USA');
INSERT INTO kraj(nazwa) VALUES ('Argentyna');
INSERT INTO kraj(nazwa) VALUES ('Niemcy');
INSERT INTO kraj(nazwa) VALUES ('UK');


CREATE TABLE pasazer (
  id_pasazer int IDENTITY(1,1) PRIMARY KEY,
  imie VARCHAR(20) NOT NULL CHECK(LEN(imie)>2),
  drugie_imie VARCHAR(20) NOT NULL,
  nazwisko VARCHAR(30) NOT NULL CHECK(LEN(nazwisko)>2),
  numer_telefonu VARCHAR(20) NOT NULL,
  adres_email VARCHAR(30) NOT NULL,
  numer_paszportu VARCHAR(30) NOT NULL UNIQUE,
  data_urodzenia DATETIME,
  czy_wydał_w_liniach MONEY,
  kraj_id INTEGER NOT NULL REFERENCES kraj(kraj_id) ON UPDATE CASCADE,
);
GO

INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);
INSERT 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);


CREATE TABLE rezerwacja (
  id_rezerwacja int IDENTITY(1,1) PRIMARY KEY,
  cena_za_bilet_id INTEGER NOT NULL REFERENCES cena_za_bilet(id_cena_za_bilet) ON UPDATE CASCADE,
  pasazer_id INTEGER NOT NULL REFERENCES pasazer(id_pasazer) ON UPDATE CASCADE,
  uwagi VARCHAR(30) NULL,
);
GO

INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (1,1,'meh');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (2,1,'sad');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,1,'wooooow');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,1,'nice');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (4,2,'zly system');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (5,2,'jest oki');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (2,2,'swietna sprawa');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (3,3,'genialny system');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (4,4,'SUPER BAZA DANYCH');
INSERT INTO rezerwacja(cena_za_bilet_id,pasazer_id,uwagi) VALUES (5,5,'ok');

CREATE TABLE kierunek (
  id_kierunek int IDENTITY(1,1) PRIMARY KEY,
  kierunek_swiata VARCHAR(20) NOT NULL,
);
GO

INSERT INTO kierunek(kierunek_swiata) VALUES ('polnoc');
INSERT INTO kierunek(kierunek_swiata) VALUES ('poludnie');
INSERT INTO kierunek(kierunek_swiata) VALUES ('wschod');
INSERT INTO kierunek(kierunek_swiata) VALUES ('polnocny-zachod');
INSERT INTO kierunek(kierunek_swiata) VALUES ('zachod');


CREATE TABLE lotnisko (
  id_lotnisko int IDENTITY(1,1) PRIMARY KEY,
  nazwa_lotniska VARCHAR(20) NOT NULL,
  miasto VARCHAR(20) NOT NULL,
  ulica VARCHAR(30) NOT NULL,
  numer_ulicy VARCHAR(10) NOT NULL,
  kod_pocztowy VARCHAR(10) NOT NULL,
  kraj_id INTEGER NOT NULL REFERENCES kraj(kraj_id) ON UPDATE CASCADE,
  kierunek_id INTEGER NOT NULL REFERENCES kierunek(id_kierunek) ON UPDATE CASCADE,
);
GO

INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Lech Walesa','Gdansk','legionow','201','12-344',1,4);
INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Marcel Airport','Marcelolandia','Marcela','1','57-784',1,4);
INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Okecie','Warszawa','legionow','201','12-344',1,1);
INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Luton','London','legionow','201','12-344',5,3);
INSERT INTO lotnisko(nazwa_lotniska,miasto,ulica, numer_ulicy, kod_pocztowy, kraj_id, kierunek_id) VALUES ('Dutch','Amsterdam','legionow','201','12-344',4,2);


CREATE TABLE plan_lotu (
  id_plan_lotu int IDENTITY(1,1) PRIMARY KEY,
  czas_wylotu DATE NOT NULL,
  czas_przylotu DATE NOT NULL,
  kierunek_id INTEGER NOT NULL REFERENCES kierunek(id_kierunek) ON UPDATE CASCADE,
  lot_id INTEGER NOT NULL REFERENCES lot(id_lot) ON UPDATE CASCADE,
);
GO

INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-12-03','2018-12-03',3,5);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2014-01-12','2014-01-12',1,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2017-10-25','2017-10-25',1,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2016-11-13','2016-11-14',2,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2012-02-03','2012-02-04',2,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-06','2018-05-07',1,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-01','2018-05-02',2,3);
INSERT INTO plan_lotu(czas_wylotu, czas_przylotu,kierunek_id,lot_id) VALUES ('2018-05-28','2018-05-29',2,3);



--1a) Tworzy widok o nazwie "pasazer_informacje", który wyświetla o każdym pasażerze takie informacje jak:
--id_pracownik, imie, nazwisko, kolumna wyliczeniowa "ilosc_lat",
--kolumna wyliczeniowa "ilosc_rez", czyli całkowita ilość rezerwacji samolotwych, kolumna wyliczeniowa
--"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).
--(UŻYCIE CASE)
CREATE VIEW pasazer_informacje AS
SELECT p.id_pasazer,p.imie,p.nazwisko, DATEDIFF(YY,p.data_urodzenia,GETDATE()) AS "ilosc_lat", COUNT(r.pasazer_id) AS "ilosc_rez",
CASE 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
GROUP BY p.id_pasazer,p.imie,p.nazwisko,p.data_urodzenia,p.czy_wydał_w_liniach;
GO

--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
SELECT * 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') ;
Go

--2a) Tworzymy funkcję 1 o nazwie producent_ile_samolotów, która będzie zwracać ilośc samolotów które wyprodukował dany producent.
--(UŻYCIE IF-ELSE)
CREATE FUNCTION dbo.producent_ile_samolotów (
 @id_producent_samolotu INT
) RETURNS INT
BEGIN
IF (SELECT COUNT(*) FROM samolot WHERE producent_samolotu_id=@id_producent_samolotu) =0
RETURN 0
ELSE
RETURN (SELECT COUNT(*) FROM samolot
 WHERE producent_samolotu_id=@id_producent_samolotu)
RETURN 0
END;
GO

--2b) Sprawdzenie, że funkcja 1 działa poprzez przykład producenta "Ferrari".
SELECT dbo.producent_ile_samolotów(1) AS ile_samolotów;

--3a) Tworzymy funkcję 2 o nazwie ile_samolotów posiadającą dwa parametry czas_wylot i czas_przylotu. Funkcja powinna
--zwrócić ilość lotów samolotowych w zadanym przedziale czasowym.
CREATE FUNCTION dbo.ile_samolotów (
    @czas_wylotu DATE, @czas_przylotu DATE
    ) RETURNS INT
    BEGIN RETURN (SELECT COUNT(*) FROM plan_lotu
                            WHERE czas_przylotu<=@czas_przylotu AND czas_wylotu >=@czas_wylotu )
END;
GO

--3b) Sprawdzenie, że funkcja 2 działa poprzez sprawdzenie ile samalotów latało w Maju.
SELECT dbo.ile_samolotów('2018-05-01', '2018-05-29') AS ile_samolotów_w_maju;
GO

SELECT * FROM pasazer

--4a) Tworzymy procedurę 1, która obniża cene dla pasażera który najczęsciej podróżuje.
CREATE PROC obniżka_opłat_dla_najczesciej_podrozujacych
@obnizka MONEY
AS BEGIN
DECLARE @id_najczestszego_pasazera INT
SET @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
ORDER BY COUNT(p.id_pasazer ) DESC)
UPDATE pasazer SET czy_wydał_w_liniach=czy_wydał_w_liniach-@obnizka WHERE id_pasazer=@id_najczestszego_pasazera;
END

--4b) Sprawdzenie, że procedura 1 działa
EXEC obniżka_opłat_dla_najczesciej_podrozujacych 500;
GO

--5a) Tworzymy procedurę 2, która tóra z bieżącej bazy danych usunie wszystkie klucze obce.
--(UŻYCIE IF EXISTS)
CREATE PROCEDURE usun_klucze_obce AS
BEGIN
    WHILE(EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE CONSTRAINT_TYPE='FOREIGN KEY'))
    BEGIN
        DECLARE @sql NVARCHAR(2000)
        SELECT TOP 1 @sql=('ALTER TABLE ' + TABLE_SCHEMA + '.[' + TABLE_NAME
        + '] DROP CONSTRAINT [' + CONSTRAINT_NAME + ']')
        FROM information_schema.table_constraints
        WHERE CONSTRAINT_TYPE = 'FOREIGN KEY'
        EXEC (@sql)
    END
END

--5b) Sprawdzenie, że procedura 2 działa
EXEC usun_klucze_obce

SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS;
GO

--6a) Tworzymy wyzwalacz 1 który po dodaniu kolejnego zakupu pasazera zmniejszy jego dług o 10% wraz z zakupionym ostatnio produktem(Milionowy Klient).
--(UŻYCIE WHILE)
CREATE TRIGGER obnizka ON pasazer
FOR UPDATE AS
BEGIN
    DECLARE kursor_pasazer_update CURSOR
    FOR SELECT czy_wydał_w_liniach, id_pasazer FROM DELETED;
    OPEN kursor_pasazer_update
    DECLARE @czy_wydał_w_liniach MONEY, @id_pasazer INT
    FETCH NEXT FROM kursor_pasazer_update INTO @czy_wydał_w_liniach, @id_pasazer
    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE pasazer SET czy_wydał_w_liniach=czy_wydał_w_liniach*0.90 WHERE id_pasazer=@id_pasazer
        FETCH NEXT FROM kursor_pasazer_update INTO @czy_wydał_w_liniach, @id_pasazer
    END
    CLOSE kursor_pasazer_update
    DEALLOCATE kursor_pasazer_update
END
GO

--6b) Sprawdzenie, że wyzwalacz 1 działa
UPDATE pasazer SET czy_wydał_w_liniach=10000 WHERE id_pasazer IN(1);

--7a) Tworzymy wyzwalacz 2, który zablokuje nam dodanie nowego pasażera z długiem.
CREATE TRIGGER pasazer_ins ON pasazer
AFTER INSERT AS
BEGIN
    DECLARE @czy_wydał_w_liniach MONEY
    SET @czy_wydał_w_liniach=-1
    SELECT @czy_wydał_w_liniach=czy_wydał_w_liniach FROM INSERTED WHERE czy_wydał_w_liniach>0
    IF @czy_wydał_w_liniach>0
    BEGIN
        RAISERROR('nowy pasazer nie moze miec dlugu', 1, 2);
        ROLLBACK
    END
END
GO

--7b) Sprawdzenie, że wyzwalacz 2 działa
INSERT 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);

--8a) Tworzymy wzywalacz 3, który przy usuwaniu producenta samolotu daje nam informacje o jego załozycielu i nazwie.
--(UZYCIE KURSORA)
CREATE TRIGGER usun_producenta_samolotu ON producent_samolotu
AFTER DELETE
AS
BEGIN
    DECLARE kursor__producent_samolot_delete CURSOR
    FOR SELECT nazwa_producenta, ceo  FROM DELETED;
    DECLARE @nazwa_producenta VARCHAR(10), @ceo VARCHAR(20)

	OPEN kursor__producent_samolot_delete
    FETCH NEXT FROM kursor__producent_samolot_delete INTO @nazwa_producenta, @ceo
    WHILE @@FETCH_STATUS = 0
    BEGIN
        PRINT 'Usunieto ' + @nazwa_producenta+ ' zalozonego przez ' + @ceo
        FETCH NEXT FROM kursor__producent_samolot_delete INTO @nazwa_producenta, @ceo
	END
    CLOSE kursor__producent_samolot_delete
    DEALLOCATE kursor__producent_samolot_delete
END

--8b) Sprawdzenie, że wyzwalacz 3 działa
DELETE FROM producent_samolotu WHERE id_producent_samolotu IN(6, 7);
GO

SELECT * FROM producent_samolotu;

--9a) Tworzymy wyzwalacz 4, który nie pozwala nam dodawac zmieniac i usuwac informacji o typach samolotów.
CREATE TRIGGER typ_samolotu_blokada ON typ_samolotu
INSTEAD OF INSERT, UPDATE, DELETE
AS
    PRINT('NIE MOZNA ZMIENIAC TYPU SAMOLOTU')
GO

--9b) Sprawdzenie, że wyzwalacz 4 działa
DELETE FROM typ_samolotu WHERE id_typ_samolotu IN(1,2)

--10) Tworzę tabelę przestawną, która przedstawia sume wpłat dla trzech ostatnich lat na trzy stanowiska.
SELECT nazwa_terminala, [2018] as ROK2018, [2017] AS ROK2017, [2016] AS ROK2016
FROM
(
	SELECT nazwa_terminala, YEAR(data) as wplata, kwota
	FROM oplata
) tabela
PIVOT
(
	SUM(kwota)
	FOR wplata IN ([2018],[2017],[2016])
) AS p
ORDER BY nazwa_terminala

--KONIEC

--DROPS

drop view pasazer_informacje;
drop function dbo.producent_ile_samolotów;
drop function dbo.ile_samolotów
drop proc obniżka_opłat_dla_najczesciej_podrozujacych
drop proc usun_klucze_obce
drop trigger obnizka
drop trigger double_lotnisko
drop trigger usun_producenta_samolotu
drop trigger usun_producenta_samolotu
drop trigger typ_samolotu_blokada