/* \i 'H:\\0-BasidiDati_2024-25\\Moodle_2-SoluzioniDiEsercizi\\Aeroporti-2-CanaB_Query.sql' */


\qecho '\nB. Elencate i voli gestiti da un tipo sconosciuto di aereo.'
/* NULL */
SELECT *
FROM VOLI
WHERE TipoAereo IS NULL;


\qecho '\nC. Elencate i tipi di aereo usati nei voli che partono da Milano.'
/* NULL */
SELECT DISTINCT TipoAereo
FROM VOLI
WHERE TipoAereo IS NOT NULL
  AND NomeCittaParte = 'Milano';


\qecho '\nD. Elencate gli stati dai quali partono voli che possono trasportare almeno 100 passeggeri.'
/* JOIN con 3 relazioni */
/* Risolto da: Gianmarco Fossato */
/* CITTA contiene gli stati, TIPI_AEREI contiene le capienze.
Il termine "distinct" evita ripetizioni dello stesso stato da cui partono più aerei con capienza >=100.
*/
SELECT DISTINCT C_P.Stato
FROM (CITTA AS C_P JOIN VOLI AS V ON C_P.NomeCitta = V.NomeCittaParte)
  JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo
WHERE TA.Capienza >= 100;


\qecho '\nE.I Per il volo con identificativo 3,'
\qecho '   elencate il giorno della settimana, lo stato di partenza e quello di arrivo.'
/* SELF JOIN con 3 relazioni */

\qecho '\n\nE.1. Risolvete mediante Sql basico.'
SELECT V.GiornoSetti, C_P.Stato, C_A.Stato
FROM (VOLI AS V JOIN CITTA AS C_P ON V.NomeCittaParte = C_P.NomeCitta)
  JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta
WHERE V.IdeVolo = 3;

\qecho '\nF. Elencate le coppie di citta` collegate da voli internazionali.'
\qecho '\n\nF.1. Risolvete mediante Sql basico.'
/* SELF JOIN con 3 relazioni */
/* Risolto da: Leonardo Ongaro */
SELECT DISTINCT V.NomeCittaParte, V.NomeCittaArri
FROM (VOLI AS V JOIN CITTA AS C_P ON V.NomeCittaParte = C_P.NomeCitta)
  JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta
  AND C_P.Stato <> C_A.Stato;


\qecho '\nG. Indicate il numero di voli internazionali che partono la domenica da Milano.'
/* COUNT */
/* Risolto da: Daniel Ginato e altri */
SELECT COUNT(*)
FROM VOLI AS V JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta
WHERE V.NomeCittaParte = 'Milano'
  AND C_A.Stato <> 'ITA'
  AND V.GiornoSetti = 'DO';


\qecho '\nH. Per ogni citta` italiana, indicate il numero di voli internazionali in partenza.'
\qecho '\  L''elenco deve contenere soltanto le citta` in cui questo numero e` > 0.'
/* GROUP BY. */
/* Risolto da: Riccardo Marcuzzi */
SELECT V.NomeCittaParte, COUNT(*)
FROM (VOLI AS V JOIN CITTA AS C_P ON V.NomeCittaParte = C_P.NomeCitta)
  JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta
WHERE C_P.Stato = 'ITA'
  AND C_A.Stato <> 'ITA'
GROUP BY V.NomeCittaParte;


\qecho '\nI. Elencate le citta` francesi da cui partono almeno venti voli alla settimana diretti in ITA.'
/* HAVING. */
/* Risolto da: Riccardo Marcuzzi */
SELECT V.NomeCittaParte
FROM (VOLI AS V JOIN CITTA AS C_P ON V.NomeCittaParte = C_P.NomeCitta)
  JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta
WHERE C_P.Stato = 'FRA'
  AND C_A.Stato = 'ITA' 
GROUP BY V.NomeCittaParte
HAVING COUNT(*) >= 20;


\qecho '\nJ. Elencate le citta` da cui partono voli diretti a Milano, ordinate alfabeticamente.'
/* ORDER BY */
/* Risolto da: */
SELECT DISTINCT NomeCittaParte
FROM VOLI
WHERE NomeCittaArri = 'Milano'
ORDER BY NomeCittaParte;


\qecho '\nK. Elencate ogni citta` che e` collegata a Roma (mediante un volo che parte da Roma o ci arriva).'
\qecho 'L''elenco deve avere un''unica colonna.'
/* UNION. */
/* Risolto da: Alberto Parravicini */
/* Ogni citta` dalla quale parte un volo diretto a Roma. */
SELECT V.NomeCittaParte AS Nome_Citta_Collegata_A_Roma
FROM VOLI AS V
WHERE V.NomeCittaArri = 'Roma'
  UNION
/* Ogni citta` nella quale arriva un volo partito da Roma. */
SELECT V.NomeCittaArri
FROM VOLI AS V
WHERE V.NomeCittaParte = 'Roma';


\qecho '\nL. Elencate le citta` collegate soltanto mediante voli effettuati la domenica.'
\qecho 'L''elenco deve avere un''unica colonna. Non usate join.'
/* EXCEPT, UNION. */
/* Risolto da: */
/*
1: Ogni citta` collegata da voli, scomponibile in:
(1.1: Ogni citta` dalla quale parte un volo) UNION (1.2: Ogni citta` nella quale arriva un volo)
  EXCEPT
2. Ogni citta` collegata da voli non domenicali, scomponibile in:
(2.1: Ogni citta` dalle quale parte un volo non domenicale) UNION (2.2: Ogni citta` nella quale arriva un volo non domenicale)
*/
( /* 1: Ogni citta` collegata da voli */
/* 1.1: Ogni citta` dalla quale parte un volo */
SELECT NomeCittaParte AS Nome_Citta_Collegata_Solo_Domenica
FROM VOLI
  UNION
/* 1.2: Ogni citta` nella quale arriva un volo */
SELECT NomeCittaArri
FROM VOLI
) EXCEPT (
/* 2. Ogni citta` collegata da voli non domenicali */
/* 2.1: Ogni citta` nella quale parte un volo non domenicale */
SELECT NomeCittaParte
FROM VOLI
WHERE GiornoSetti <> 'DO'
  UNION
/* 2.2: Ogni citta` nella quale arriva un volo non domenicale */
SELECT NomeCittaArri
FROM VOLI
WHERE GiornoSetti <> 'DO'
);


\qecho '\nM. Per ogni volo che parte da Milano,'
\qecho '   elencate la citta` di arrivo e, se sono disponibili, il tipo e la capienza dell''aereo.'
/* JOIN ESTERNO */
/* Risolto da: */
SELECT V.IdeVolo, V.NomeCittaArri, TA.TipoAereo, TA.Capienza 
FROM VOLI AS V LEFT JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo 
WHERE V.NomeCittaParte = 'Milano';


\qecho '\nN. Elencate i tipi di aereo aventi la massima capienza.'
/* Punto di MAX */
/* Risolto da: Enrico Pizzolato, Carlo Maggiolo */
SELECT TipoAereo
FROM TIPI_AEREI
WHERE Capienza = ( /* Capienza massima */
    SELECT MAX(Capienza)
    FROM TIPI_AEREI
);


\qecho '\nO. Elencate le citta` che sono servite dai tipi di aereo aventi la massima capienza.'
\qecho '\L''elenco deve avere un''unica colonna.'
/* Punto di MAX e UNION */
/* Risolto da: Manuel Doria, Enrico Pizzolato */
/* Ogni citta` dalla quale partono voli aventi la massima capienza. */
SELECT DISTINCT V.NomeCittaParte AS Nome_Citta_Con_Voli_Con_Capienza_Max
FROM VOLI AS V JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo 
WHERE TA.Capienza = ( /* Capienza massima */
    SELECT MAX(Capienza)
    FROM TIPI_AEREI
)
  UNION
/* Ogni citta` nella quale arrivano voli aventi la massima capienza. */
SELECT DISTINCT V.NomeCittaArri
FROM VOLI AS V JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo 
WHERE TA.Capienza = ( /* Capienza massima */
    SELECT MAX(Capienza)
    FROM TIPI_AEREI
);


\qecho '\nP. Elencate le citta` italiane che hanno la massima "ricezione".'
\qecho 'La ricezione di una citta` si ottiene sommando la capienza dei voli che arrivano in quella citta`.'
\qecho 'L''elenco deve avere un''unica colonna.'

DELETE FROM VOLI;

DELETE FROM TIPI_AEREI;

INSERT INTO TIPI_AEREI VALUES ('A', 100);

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

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

INSERT INTO TIPI_AEREI VALUES ('D', 200);

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

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

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

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

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

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

\qecho '\nDati usati per il test:\n'

SELECT * FROM TIPI_AEREI;

SELECT * FROM VOLI;

\qecho 'P.a. Risolvete mediante Sql basico.'
/* Risolto da: Pietro Trabuio */
SELECT C_A.NomeCitta AS Nome_Citta_Con_Ricezione_Max
FROM (VOLI AS V JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta)
  JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo
WHERE C_A.Stato = 'ITA'
GROUP BY C_A.NomeCitta
HAVING SUM(TA.Capienza) >= ALL ( /* Ricezioni di ogni citta` italiana */
    SELECT SUM(TA.Capienza)
    FROM (VOLI AS V JOIN CITTA AS C_A ON V.NomeCittaArri = C_A.NomeCitta)
      JOIN TIPI_AEREI AS TA ON V.TipoAereo = TA.TipoAereo
    WHERE C_A.Stato = 'ITA'
    GROUP BY C_A.NomeCitta
);