Esercizi proposti su SQL SELECT avanzata Laura Farinetti - DAUIN Politecnico di Torino Esercizio 1 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Trovare i titoli dei film dello stesso regista di “Casablanca” L. Farinetti - Politecnico di Torino 2 Soluzione esercizio 1 FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Trovare i titoli dei film dello stesso regista di “Casablanca” SELECT f.Titolo FROM FILM AS f WHERE f.Regista = (SELECT f.Regista FROM FILM f WHERE f.Titolo = "Casablanca") L. Farinetti - Politecnico di Torino 3 Esercizio 2 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) HOTEL (CodHotel, NomeHotel, Indirizzo, Citta, Stato) RECENSIONE_HOTEL (CodRec, CodHotel, DataRecensione, VotoRecensione) Visualizzare nome e citta degli hotel che hanno ricevuto almeno una recensione con voto maggiore di 8 e non hanno ricevuto nessuna recensione nel 2013 L. Farinetti - Politecnico di Torino 4 Soluzione esercizio 2 HOTEL (CodHotel, NomeHotel, Indirizzo, Citta, Stato) RECENSIONE HOTEL (CodRec, CodHotel, DataRecensione, VotoRecensione) Visualizzare nome e citta degli hotel che hanno ricevuto almeno una recensione con voto maggiore di 8 e non hanno ricevuto nessuna recensione nel 2013 SELECT NomeHotel, Citta FROM HOTEL H, RECENSIONE_HOTEL R WHERE H.CodHotel = R.CodHotel AND Voto > 8 AND CodHotel NOT IN (SELECT CodHotel FROM RECENSIONE_HOTEL WHERE DataRecensione > 31/12/2012 AND DataRecensione < 1/1/2014) L. Farinetti - Politecnico di Torino 5 Esercizio 3 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) CAMPEGGIO (CodC, NomeC, Indirizzo, Comune, Stato) PIAZZOLA (NumeroP, CodC, Posizione, TipoPiazzola) OCCUPAZIONE (CodC, NumeroP, Data, CodFiscaleCliente) Visualizzare numero piazzola, nome del campeggio e comune del campeggio per le piazzole di tipo "camper" che non sono mai state occupate nel mese di gennaio 2015 L. Farinetti - Politecnico di Torino 6 Soluzione esercizio 3 CAMPEGGIO (CodC, NomeC, Indirizzo, Comune, Stato) PIAZZOLA (NumeroP, CodC, Posizione, TipoPiazzola) OCCUPAZIONE (CodC, NumeroP, Data, CodFiscaleCliente) Visualizzare numero piazzola, nome del campeggio e comune del campeggio per le piazzole di tipo "camper" che non sono mai state occupate nel mese di gennaio 2015 SELECT NumeroP, NomeC, Comune FROM CAMPEGGIO C, PIAZZOLA P WHERE P.CodC = C.CodC AND TipoPiazzola = 'camper' AND (CodC, NumeroP) NOT IN (SELECT CodC, NumeroP FROM OCCUPAZIONE WHERE Data > 31/12/2014 AND Data < 1/2/2015) L. Farinetti - Politecnico di Torino 7 Esercizio 4 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Trovare i titoli dei film che non sono mai stati proiettati a Torino L. Farinetti - Politecnico di Torino 8 Soluzione esercizio 4 FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Trovare i titoli dei film che non sono mai stati proiettati a Torino SELECT f.Titolo FROM FILM AS f WHERE NOT EXISTS (SELECT * FROM PROIEZIONI AS p, SALE AS s WHERE s.Città="Torino" AND f.CodFilm=p.CodFilm AND p.CodSala =s.CodSala) L. Farinetti - Politecnico di Torino 9 Soluzione alternativa esercizio 4 FILM (CodFilm, Titolo, AnnoProduzione, Nazionalità, Regista, Genere) PROIEZIONI (CodProiezione, CodFilm, CodSala, Incasso, DataProiezione) SALE (CodSala, Posti, Nome, Città) Trovare i titoli dei film che non sono mai stati proiettati a Torino SELECT f.Titolo FROM FILM AS f WHERE "Torino" NOT IN (SELECT s.Città FROM PROIEZIONI AS p, SALE AS s WHERE f.CodFilm=p.CodFilm AND p.CodSala=s.CodSala) L. Farinetti - Politecnico di Torino 10 Esercizi 5 – 6 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) ATLETA (CodA, NomeAtleta, NazioneAtleta) GARA-NUOTO (CodG, Data, Disciplina, LuogoGara, NazioneGara) CLASSIFICA (CodG, CodA, PosizioneArrivo, Tempo) Per le gare a cui hanno partecipato almeno 50 atleti appartenenti a 10 nazioni diverse, visualizzare codice della gara, data, disciplina e numero complessivo di atleti che hanno partecipato Visualizzare codice e nome degli atleti che hanno partecipato a tutte le gare della disciplina '100m dorso' che sono state disputate in Italia, classificandosi sempre nelle prime 10 posizioni L. Farinetti - Politecnico di Torino 11 Soluzione esercizio 5 ATLETA (CodA, NomeAtleta, NazioneAtleta) GARA-NUOTO (CodG, Data, Disciplina, LuogoGara, NazioneGara) CLASSIFICA (CodG, CodA, PosizioneArrivo, Tempo) Per le gare a cui hanno partecipato almeno 50 atleti appartenenti a 10 nazioni diverse, visualizzare codice della gara, data, disciplina e numero complessivo di atleti che hanno partecipato SELECT CodG, Data, Disciplina, count(*) FROM GARA-NUOTO G, ATLETA A, CLASSIFICA C WHERE G.CodG = C.CodG AND A.CodA = C.CodA GROUP BY G.CodG, Data, Disciplina HAVING count(*) >= 50 AND count(DISTINCT Nazione) >= 10 L. Farinetti - Politecnico di Torino 12 Soluzione esercizio 6 ATLETA (CodA, NomeAtleta, NazioneAtleta) GARA-NUOTO (CodG, Data, Disciplina, LuogoGara, NazioneGara) CLASSIFICA (CodG, CodA, PosizioneArrivo, Tempo) Visualizzare codice e nome degli atleti che hanno partecipato a tutte le gare della disciplina '100m dorso' che sono state disputate in Italia, classificandosi sempre nelle prime 10 posizioni SELECT CodA, NomeAtleta FROM ATLETA A, GARA-NUOTO G, CLASSIFICA C WHERE G.CodG = C.CodG AND A.CodA = C.CodA AND Disciplina = '100m dorso' AND NazioneGara = 'Italia' AND PosizioneArrivo <= 10 GROUP BY A.CodA, NomeAtleta HAVING count(*) = (SELECT count(*) FROM GARA-NUOTO WHERE Disciplina = '100m dorso' AND Nazione = 'Italia') L. Farinetti - Politecnico di Torino 13 Soluzione alternativa esercizio 6 ATLETA (CodA, NomeAtleta, NazioneAtleta) GARA-NUOTO (CodG, Data, Disciplina, LuogoGara, NazioneGara) CLASSIFICA (CodG, CodA, PosizioneArrivo, Tempo) Visualizzare codice e nome degli atleti che hanno partecipato a tutte le gare della disciplina '100m dorso' che sono state disputate in Italia, classificandosi sempre nelle prime 10 posizioni SELECT CodA, NomeAtleta FROM ATLETA A, CLASSIFICA C WHERE A.CodA = C.CodA AND PosizioneArrivo <= 10 AND CodG IN (SELECT CodG FROM GARA-NUOTO WHERE Disciplina = '100m dorso' AND Nazione = 'Italia') GROUP BY A.CodA, NomeAtleta HAVING count(*) = (SELECT count(*) FROM GARA-NUOTO WHERE Disciplina = '100m dorso' L. Farinetti - Politecnico di Torino AND Nazione = 'Italia') 14 Esercizio 7 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo) Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo) … … usando le table function (invece della correlazione fra interrogazioni) L. Farinetti - Politecnico di Torino 15 Soluzione esercizio 7 VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo) Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (usando le table function) SELECT CodV FROM VIAGGIO V, (SELECT LuogoPartenza, LuogoArrivo, AVG(OraArrivo-OraPartenza) AS DurataMedia FROM VIAGGIO GROUP BY LuogoPartenza, LuogoArrivo) AS MEDIA WHERE V.LuogoPartenza = MEDIA.LuogoPartenza AND V.LuogoArrivo = MEDIA.LuogoArrivo AND OraArrivo – OraPartenza < DurataMedia L. Farinetti - Politecnico di Torino 16 Esercizio 8 Sia dato lo schema relazionale costituito dalle tabelle (le chiavi primarie sono sottolineate) P (CodP, NomeP, Colore, Taglia, Magazzino) F (CodF, NomeF, NSoci, Sede) FP (CodF, CodP, Qta) Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima … … usando le table function (invece della correlazione fra interrogazioni) L. Farinetti - Politecnico di Torino 17 Soluzione esercizio 8 P (CodP, NomeP, Colore, Taglia, Magazzino) F (CodF, NomeF, NSoci, Sede) FP (CodF, CodP, Qta) Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima (usando le table function) SELECT FP.CodP, CodF FROM FP, (SELECT CodP, MAX(Qta) AS QtaMax FROM FP GROUP BY CodP) AS MAXQ WHERE FP.CodP = MAXQ.CodP AND Qta = QtaMax L. Farinetti - Politecnico di Torino 18