/* Autore: sì */

/* \c postgres */

DROP DATABASE IF EXISTS aeroporti; /* Cancellazione della versione precedente del db */

CREATE DATABASE aeroporti; /* Creazione del db */


/* Connessione al db */
\c aeroporti


/* Creazione delle tabelle */

CREATE TABLE CITTA (
    NomeCitta VARCHAR(20) PRIMARY KEY,
    Stato CHAR(3) NOT NULL
);

CREATE TABLE TIPI_AEREI (
    TipoAereo VARCHAR(2) PRIMARY KEY,
    Capienza INTEGER NOT NULL
);

CREATE TABLE VOLI (
    IdeVolo INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
    GiornoSetti CHAR(2) NOT NULL,
    NomeCittaParte VARCHAR(20) REFERENCES CITTA(NomeCitta) NOT NULL,
    NomeCittaArri VARCHAR(20) REFERENCES CITTA(NomeCitta) NOT NULL,
    TipoAereo VARCHAR(2) REFERENCES TIPI_AEREI(TipoAereo)
);


/* Inserimento di righe e visualizzazione .. */

/* .. nella tabella CITTA */
INSERT INTO CITTA VALUES
    ('Catania', 'ITA'),
    ('Lyon', 'FRA'),
    ('Milano', 'ITA'),
    ('Paris', 'FRA'),
    ('Rimini', 'ITA'),
    ('Roma', 'ITA'),
    ('Venezia', 'ITA');

\qecho '\nTabella CITTA'
SELECT * FROM CITTA;

/* .. nella tabella TIPI_AEREI */
INSERT INTO TIPI_AEREI VALUES
    ('A', 100),
    ('B', 70),
    ('C', 100);

\qecho '\nTabella TIPI_AEREI'
SELECT * FROM TIPI_AEREI;

/* .. nella tabella VOLI */
INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('LU', 'Roma', 'Milano'); /* Inserisce: 1 in IdeVolo, NULL in TipoAereo */

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
    VALUES('DO', 'Paris', 'Roma', 'B');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
    VALUES('DO', 'Paris', 'Milano', 'B');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
    VALUES('DO', 'Lyon', 'Roma', 'B');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
    VALUES('DO', 'Roma', 'Lyon', 'B');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('LU', 'Milano', 'Roma');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('LU', 'Venezia', 'Milano');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('LU', 'Venezia', 'Milano');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
  VALUES('LU', 'Milano', 'Venezia', 'C');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('DO', 'Catania', 'Milano');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
  VALUES('DO', 'Milano', 'Catania', 'A');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
  VALUES('DO', 'Milano', 'Catania', 'C');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('DO', 'Milano', 'Paris');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('LU', 'Milano', 'Paris');

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri)
  VALUES('DO', 'Roma', 'Paris');

\qecho '\nTabella VOLI'
SELECT * FROM VOLI;


\qecho '\nQuesito A' /*Quesito A dell'esercizio*/

/*Ho scritto tutti gli attributi al posto di usare "*" in modo da poter mettere la colonna dello Stato di partenza
       vicina alla città di partenza (analogo per arrivi) */
/*Primo Join per le partenze, secondo Join sul primo per avere anche gli arrivi
    Per eliminare righe inutili al quesito ho messo un AND che rimuove tutte le combinazioni per cui il nome della
    città di partenza è lo stesso di quello di arrivo*/

SELECT IdeVolo,GiornoSetti,NomeCittaParte,C_P.Stato AS StatoParte,NomeCittaArri,C_A.Stato AS StatoArri,TipoAereo FROM
    (VOLI JOIN CITTA AS C_P ON Voli.NomeCittaParte = C_P.NomeCitta)
        JOIN CITTA AS C_A ON Voli.NomeCittaArri = C_A.NomeCitta AND NomeCittaParte <> C_A.NomeCitta;

\qecho '\nQuesito B' /*Quesito B dell'esercizio*/

SELECT IdeVolo FROM VOLI
    WHERE TipoAereo IS NULL;

\qecho '\nQuesito C' /*Quesito C dell'esercizio*/

SELECT IdeVolo FROM VOLI
    WHERE VOLI.NomeCittaParte = 'Milano';

\qecho '\nQuesito D' /*Quesito D dell'esercizio*/
       
/*Primo Join per colonna degli stati di partenza, secondo join per le capienze
       Utilizzato il termine "distinct" per evitare ripetizioni dello stesso Stato (dovuto a partenze di più aerei
       con capienza >=100 dallo stesso Stato) */
       
SELECT DISTINCT C_P.Stato FROM
    (VOLI JOIN CITTA AS C_P  ON Voli.NomeCittaParte = C_P.NomeCitta)
        JOIN TIPI_AEREI ON VOLI.TipoAereo = TIPI_AEREI.TipoAereo
    WHERE TIPI_AEREI.Capienza >= 100;