Esercizi SQL - polimi.it

1 1 Esercizi SQL 2 Target list miste quando non c la clausola group by Attributi nella select o nella having che non siano anche nella group by (quando c una clausola group by) Aggregati di aggregati Aggregati nella clausola where [ WHERE max(X) ] HAVING max(X). max non un predicato!! Clausole where auto-contraddittorie [ WHERE anno=1992 and anno=1993 ] IN / NOT IN con Niente a sinistra [ WHERE NOT IN .. ] Schemi che non si corrispondono Predicati con query nidificate a dx senza ANY o ALL Le cose che non vogliamo vedere 3 Aeroporti AEROPORTO (Citt , Nazione, NumPiste) volo (IdVolo, GiornoSett, Citt Part, OraPart, Citt Arr, OraArr, TipoAereo) AEREO (TipoAereo, NumPasseggeri, QtaMerci) 4 Trovare le citt da cui partono voli diretti a Roma, ordinate alfabeticamente Trovare le citt con un aeroporto di cui non noto il numero di piste SELECT Citt FROM Aeroporto WHERE NumPiste IS NULL Rinfreschiamoci la memoria 5 Trovare le citt da cui partono voli diretti a Roma, ordinate alfabeticamente SELECT DISTINCT Citt Par FROM volo WHERE Citt Arr= Roma ORDER BY Citt Par Trovare le citt con un aeroporto di cui non noto il numero di piste SELECT Citt FROM Aeroporto WHERE NumPiste IS NULL Rinfreschiamoci la memoria 6 Trovare le citt da cui partono voli diretti a Roma.

2 Ordinate alfabeticamente SELECT DISTINCT Citt Par FROM volo WHERE Citt Arr= Roma ORDER BY Citt Par Trovare le citt con un aeroporto di cui non noto il numero di piste Rinfreschiamoci la memoria 7 Trovare le citt da cui partono voli diretti a Roma, ordinate alfabeticamente SELECT DISTINCT Citt Par FROM volo WHERE Citt Arr= Roma ORDER BY Citt Par Trovare le citt con un aeroporto di cui non noto il numero di piste SELECT Citt FROM Aeroporto WHERE NumPiste IS NULL Rinfreschiamoci la memoria 8 SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo AS V, AEREO AS A WHERE = and NumPasseggeri > 0 and QtaMerci > 0 (sintassi equivalente) SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo V INNER JOIN AEREO A ON = WHERE NumPasseggeri > 0 and QtaMerci > 0 Di ogni volo misto (merci e passeggeri) estrarre il codice e i dati relativi al trasporto 9 SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo AS V, AEREO AS A WHERE = and NumPasseggeri > 0 and QtaMerci > 0 (sintassi equivalente) SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo V INNER JOIN AEREO A ON = WHERE NumPasseggeri > 0 and QtaMerci > 0 Di ogni volo misto (merci e passeggeri) estrarre il codice e i dati relativi al trasporto 10 SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo AS V, AEREO AS A WHERE = and NumPasseggeri > 0 and QtaMerci > 0 (sintassi equivalente)

3 SELECT IdVolo, NumPasseggeri, QtaMerci FROM volo V JOIN AEREO A ON = WHERE NumPasseggeri > 0 and QtaMerci > 0 Di ogni volo misto (merci e passeggeri) estrarre il codice e i dati relativi al trasporto 11 Le nazioni di partenza e arrivo del volo AZ274 SELECT , FROM (AEROPORTO A1 JOIN volo ON =Citt Arr) JOIN AEROPORTO A2 ON Citt Par= WHERE IdVolo= AZ274 12 Le nazioni di partenza e arrivo del volo AZ274 SELECT , FROM (AEROPORTO A1 JOIN volo ON =Citt Arr) JOIN AEROPORTO A2 ON Citt Par= WHERE IdVolo= AZ274 13 Ad esempio si pu usare una query annidata SELECT Citt , NumPiste FROM AEROPORTO WHERE Nazione= Italia and NumPiste = ( SELECT max(numPiste) FROM AEROPORTO WHERE Nazione= Italia ) Trovare l aeroporto italiano con il maggior numero di piste 14 SELECT Citt , max(NumPiste) FROM AEROPORTO WHERE Nazione = Italia Trovare l aeroporto italiano con il maggior numero di piste 15 SELECT Citt , max(NumPiste) FROM AEROPORTO WHERE Nazione = Italia Trovare l aeroporto italiano con il maggior numero di piste (errore sintattico) NO!

4 16 SELECT Citt , max(NumPiste) FROM AEROPORTO WHERE Nazione = Italia GROUP BY Citt Trovare l aeroporto italiano con il maggior numero di piste 17 SELECT Citt , max(NumPiste) FROM AEROPORTO WHERE Nazione = Italia GROUP BY Citt Trovare l aeroporto italiano con il maggior numero di piste (errore semantico) NO! 18 Ad esempio si pu usare una query annidata SELECT Citt , NumPiste FROM AEROPORTO WHERE Nazione= Italia and NumPiste = (SELECT max(numPiste) FROM AEROPORTO WHERE Nazione= Italia ) Trovare l aeroporto italiano con il maggior numero di piste (soluzione corretta) 19 oppure SELECT Citt , NumPiste FROM AEROPORTO WHERE Nazione= Italia and NumPiste >= ALL (SELECT numPiste FROM AEROPORTO WHERE Nazione= Italia ) Trovare l aeroporto italiano con il maggior numero di piste (soluzione corretta) 20 Per ogni nazione, trovare quante piste ha l aeroporto con pi piste.

5 SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione 21 Per ogni nazione, trovare quante piste ha l aeroporto con pi piste. SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione 22 Per ogni nazione, trovare quante piste ha l aeroporto con pi piste (purch almeno 3). SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione .. HAVING max(NumPiste) > 2 Dobbiamo raggruppare tutte le tuple e poi considerare solo i gruppi di tuple (a pari nazione) in cui il massimo numero di piste sia almeno 3 23 Per ogni nazione, trovare quante piste ha l aeroporto con pi piste (purch almeno 3). SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione HAVING max(NumPiste) > 2 Dobbiamo raggruppare tutte le tuple e poi considerare solo i gruppi di tuple (a pari nazione) in cui il massimo numero di piste sia almeno 3 24 SELECT Nazione, max(NumPiste) FROM AEROPORTO WHERE NumPiste > 2 GROUP BY Nazione Soluzione alternativa: scarta subito tutte le tuple che non abbiano almeno tre piste; poi raggruppa solo quelle, e considera tutti i gruppi, ma chiaramente l effetto lo stesso PER INCLUDERE LA CITTA BISOGNA CAMBIARE STRATEGIA Per ogni nazione, trovare quante piste ha l aeroporto con pi piste (purch almeno 3).

6 25 Trovare le citt in cui si trovano gli aeroporti con pi piste di ogni nazione indicare citt , nazione e numero di piste (ancora col vincolo che siano almeno 3) SELECT * FROM AEROPORTO WHERE ( Nazione, NumPiste ) IN ( SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione HAVING max(NumPiste) > 2) 26 Trovare le citt in cui si trovano gli aeroporti con pi piste di ogni nazione indicare citt , nazione e numero di piste (ancora col vincolo che siano almeno 3) SELECT * FROM AEROPORTO WHERE ( Nazione, NumPiste ) IN (SELECT Nazione, max(NumPiste) FROM AEROPORTO GROUP BY Nazione HAVING max(NumPiste) > 2) 27 Trovare le citt in cui si trovano gli aeroporti con pi piste di ogni nazione indicare citt , nazione e numero di piste (ancora col vincolo che siano almeno 3) SELECT * FROM AEROPORTO A1 WHERE NumPiste IN (SELECT max(NumPiste) FROM AEROPORTO A2 WHERE GROUP BY Nazione HAVING max(NumPiste) > 2) 28 Trovare gli aeroporti da cui partono voli internazionali SELECT DISTINCT Citt Par FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> Il distinct essenziale per la chiarezza e leggibilit del risultato 29 Trovare gli aeroporti da cui partono voli internazionali SELECT DISTINCT Citt Par FROM (AEROPORTO AS A1 JOIN volo ON Citt Par = )

7 JOIN AEROPORTO AS A2 ON Citt Arr = WHERE <> Il distinct essenziale per la chiarezza e leggibilit del risultato 30 Trovare gli aeroporti da cui partono voli internazionali SELECT DISTINCT Citt Par FROM (AEROPORTO AS A1 JOIN volo ON Citt Par = ) JOIN AEROPORTO AS A2 ON Citt Arr = WHERE <> Il distinct essenziale per la chiarezza e leggibilit del risultato 31 Trovare il numero totale di partenze internazionali (del gioved ) da tutti gli aeroporti SELECT ? FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved 32 Trovare il numero totale di partenze internazionali (del gioved ) da tutti gli aeroporti SELECT count(*) FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved qui niente distinct!

8 33 Trovare il numero di aeroporti che hanno almeno una partenza internazionale (al gioved ) SELECT ? FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved 34 Trovare il numero di aeroporti che hanno almeno una partenza internazionale (al gioved ) SELECT count( distinct Citt Par ) FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved 35 Trovare il numero di partenze internazionali (del gioved ) da ogni aeroporto SELECT ? FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved ?

9 36 Trovare il numero di partenze internazionali (del gioved ) da ogni aeroporto SELECT Citt Par, count(*) AS NumPartInt FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved GROUP BY Citt Par 37 Le citt francesi da cui ogni settimana partono pi di 20 voli diretti x la Germania SELECT Citt Par, count(*) AS NumVoliGer FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= ? WHERE Francia AND Germania GROUP BY Citt Par .. 38 Le citt francesi da cui ogni settimana partono pi di 20 voli diretti x la Germania SELECT Citt Par, count(*) AS NumVoliGer FROM (AEROPORTO AS A1 JOIN volo ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE Francia AND Germania GROUP BY Citt Par HAVING count(*) > 20 39 SELECT Citt Part, count(*) FROM volo WHERE GiornoSett = Gioved ?

10 GROUP BY Citt Part HAVING count(*) >= 100 SELECT Citt Part, count(*) FROM volo WHERE GiornoSett = Gioved AND Citt Part IN ( SELECT Citt Part FROM volo GROUP BY Citt Part HAVING count(*) > 100 ) GROUP BY Citt Part Trovare il numero di voli del gioved di ogni aeroporto da cui partano almeno 100 voli a settimana 40 Il secondo conteggio deve avvenire su tutti i voli dell aeroporto, non solo su quelli del gioved Trovare il numero di voli del gioved di ogni aeroporto da cui partano almeno 100 voli a settimana SELECT Citt Part, count(*) FROM volo WHERE GiornoSett = Gioved GROUP BY Citt Part HAVING count(*) >= 100 SELECT Citt Part, count(*) FROM volo WHERE GiornoSett = Gioved AND Citt Part IN ( SELECT Citt Part FROM volo GROUP BY Citt Part HAVING count(*) >= 100 ) GROUP BY Citt Part 41 Filmografie REGISTA ( Nome, DataNa)