/* \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 due 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 */
/* Citta` dalle quali parte un volo diretto a Roma. */
SELECT DISTINCT V.NomeCittaParte AS NomeCittaCollegataARoma
FROM VOLI AS V
WHERE V.NomeCittaArri = 'Roma'
  UNION
/* Citta` nelle quali arriva un volo partito da Roma. */
SELECT DISTINCT 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. */

DELETE FROM VOLI;

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

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

INSERT INTO VOLI(GiornoSetti, NomeCittaParte, NomeCittaArri, TipoAereo)
    VALUES('LU', 'Paris', 'Roma', 'C');

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

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

SELECT * FROM CITTA;

SELECT * FROM VOLI;


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