/* \i 'H:\\Linguaggi_1\\PostgreSQL\\Aeroporti-1_CreateModify.sql' */
/* 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 '\n Indicate il numero di voli internazionali che partono la domenica da Milano. 
La domenica è identificata mediante la stringa DO. 
L’Italia è identificata mediante la stringa ITA.'


SELECT COUNT(Voli.idevolo) 
FROM Citta AS C_P 
    JOIN Citta AS C_A 
        ON  C_P.nomecitta = 'Milano' AND C_A.Stato <> 'ITA'
    JOIN Voli 
        ON Voli.nomecittaparte =C_P.nomecitta AND Voli.giornosetti = 'DO';