/* Autore: Antonio Giunta */


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

INSERT INTO CITTA VALUES ('Lyon', 'FRA');

INSERT INTO CITTA VALUES ('Milano', 'ITA');

INSERT INTO CITTA VALUES ('Paris', 'FRA');

INSERT INTO CITTA VALUES ('Rimini', 'ITA');

INSERT INTO CITTA VALUES ('Roma', 'ITA');

INSERT INTO CITTA VALUES ('Venezia', 'ITA');

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

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

INSERT INTO TIPI_AEREI VALUES ('B', 70);

INSERT INTO TIPI_AEREI VALUES ('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 '\nCoppie di città appartenenti a voli internazionali'
SELECT C_P.NomeCitta, C_A.NomeCitta
FROM Citta AS C_P 
    JOIN Citta AS C_A 
        ON C_P.Stato <> C_A.Stato 
    JOIN Voli 
        ON Voli.NomeCittaArri = C_A.NomeCitta AND Voli.NomeCittaParte = C_P.NomeCitta