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, 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.

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 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) 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)

3 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! 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!

4 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. 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).

5 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 = ) 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 = )

7 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! 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 ?

8 FROM (AEROPORTO AS A1 JOIN VOLO ON Citt Par= ) JOIN AEROPORTO AS A2 ON Citt Arr= WHERE <> and GiornoSett = Gioved ? 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 ?

9 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, DataNascita, Nazionalit ) ATTORE ( Nome, DataNascita, Nazionalit ) INTERPRETA ( Attore, Film, Personaggio ) FILM ( Titolo, NomeRegista, Anno) PROIEZIONE ( NomeCin, Citt Cin, TitoloFilm ) CINEMA ( Citt , NomeCinema, #Sale, #Posti ) 42 Selezionare le Nazionalit dei registi che hanno diretto qualche film nel 1992 ma non hanno diretto alcun film nel 1993 SELECT DISTINCT Nazionalit FROM REGISTA WHERE Nome IN (SELECT NomeRegista FROM FILM WHERE Anno= 1992 ) AND Nome NOT IN (SELECT NomeRegista FROM FILM WHERE Anno= 1993 ) 43 Selezionare le Nazionalit dei registi che hanno diretto qualche film nel 1992 ma non hanno diretto alcun film nel 1993 SELECT DISTINCT Nazionalit FROM REGISTA WHERE Nome IN (SELECT NomeRegista FROM FILM WHERE Anno= 1992 ) AND Nome NOT IN (SELECT NomeRegista FROM FILM WHERE Anno= 1993 )

10 44 Selezionare le Nazionalit dei registi che hanno diretto qualche film nel 1992 ma non hanno diretto alcun film nel 1993 SELECT DISTINCT Nazionalit FROM REGISTA, FILM WHERE Nome = NomeRegista AND Anno= 1992 AND Nome NOT IN (SELECT NomeRegista FROM FILM WHERE Anno= 1993 ) 45 Si pu usare EXCEPT (a patto di discriminare in base alla chiave) SELECT Nazionalit FROM REGISTA WHERE Nome IN ( SELECT NomeRegista FROM FILM WHERE Anno = 1992 EXCEPT SELECT NomeRegista FROM FILM WHERE Anno = 1993 ) Nazionalit dei registi con film nel 1992 ma non nel 1993 (soluzione altermativa) 46 SELECT Nazionalit FROM FILM JOIN REGISTA ON NomeRegista=Nome WHERE Anno = 1992 EXCEPT SELECT Nazionalit FROM FILM JOIN REGISTA ON NomeRegista=Nome WHERE Anno = 1993 Attenzione: in SQL gli operatori insiemistici eliminano i duplicati (come se davanti a Nazionalit ci fosse distinct) NON si pu usare la EXCEPT direttamente se nella target list non incluso l attributo discriminante per l esclusione 47 Nazionalit dei registi con film nel 1992 ma non nel 1993 (errore tipico) SBAGLIATO ricorrere ad un JOIN con condizione nella WHERE.