/* 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'

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'

SELECT IdeVolo FROM VOLI
    WHERE TipoAereo IS NULL;

\qecho '\nQuesito C'

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

\qecho '\nQuesito D'
SELECT DISTINCT C_P.Stato FROM
    (VOLI JOIN CITTA AS C_P  ON Voli.NomeCittaParte = C_P.NomeCitta)  NATURAL JOIN TIPI_AEREI
    WHERE TIPI_AEREI.Capienza >= 100;