/* \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 '\nTabella VOLI'
SELECT * FROM VOLI;


/* Carlo Maggiolo */
SELECT *
FROM	 tipi_aerei
WHERE capienza = (
	/* ricerco una sola volta il massimo tra tutti i valori */
	SELECT MAX(capienza)
	FROM tipi_aerei
	/* WHERE MAX(capienza)		funzioni aggregazioni non in where*/
);