In questa sezione puoi trovare esercizi relativi al linguaggio SQL. 

Esercizi di query language

Esercizio 1: Film-Attori-Generi

Dato il seguente schema relazionale di un database:
attori (id_attore, nome, cognome, data_nascita)
film (id_film, nome, trama, durata, data_uscita, id_genere*)
generi (id_genere, nome, descrizione)
recitare (id_attore, id_film, ruolo)

Rispondere in SQL alle seguenti query:

1. Tutte le informazioni degli attori nati dopo il 1970.

2. Nome, durata e data di uscita dei film che hanno una durata superiore a 2 ore o che sono usciti prima del 1991.

3. Trama, durata e data di uscita del film "Interstellar".

4. Elenco dei film che non hanno un genere assegnato.

5. Nome, data di uscita e durata dei film di genere "Romantico".

6. Nome e cognome degli attori che hanno avuto un ruolo da "Non protagonista" in almeno un film. Se un attore è stato non protagonista per più di un film deve comunque apparire una sola volta nel risultato della query.

7. Nome, cognome e data di nascita degli attori che hanno recitato nel film "Titanic" uscito il 16 gennaio 1998.

8. Nome e cognome degli attori nati dopo il 1970 che hanno recitato con ruolo da "Protagonista" in un film "Romantico" di durata maggiore di un’ora e trenta minuti e uscito a partire dal 1990.

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 2: Numeri telefonici - Persone - Smartphone

Dato il seguente schema relazionale di un database:
persone (id_persona, nome, cognome, data_nascita)
numeri_telefonici (id_numero, numero, compagnia_telefonica, id_persona*)
smartphone (id_smartphone, marca, modello, colore)
smartphone_posseduti (id_smartphone, id_persona, data_acquisto)

Rispondere in SQL alle seguenti query:

1. Marca e modello degli smartphone. Evitare che lo stesso modello di una marca compaia più volte a schermo nel risultato della query.

2. Le informazioni delle persone che hanno almeno un numero telefonico. Evitare i duplicati in output.

3. Indicare i colori dello smartphone Samsung S20 che sono registrati nel db.

4. Elenco degli smartphone di marca Samsung posseduti da Massimo Visconti.

5. Elenco delle persone che hanno almeno un numero telefonico con la compagnia "TIM".

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 3: Sanremo

Dato il seguente schema relazionale di un database:
artisti (id_artista, nome, numero_album, citta)
brani (id_brano, nome)
esibizioni (id_artista, id_brano, data)

Rispondere in SQL alle seguenti query:

1. Numero degli artisti nati a Milano o a Roma.

2. Media degli album realizzati dagli artisti nati a Milano.

3. Numero minimo e numero massimo di album realizzati dagli artisti.

4. Nome e numero di album degli artisti nati a Roma. Ordinare le righe della  tabella in ordine decrescente sulla base del numero di album, ed a parità di album ordinare in base al nome dell'artista in ordine alfabetico.

5. Nome del brano con cui si è esibito l'artista "Marco Mengoni" il giorno 7/02/2023.

6. Nome degli artisti e nome del brano con cui si sono esibiti gli artisti il 7/02/2023.

7.  Nome degli artisti che non hanno fatto alcuna esibizione.

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 4: canzoni - playlist - utente

Dato il seguente schema relazionale di un database:
canzoni (id_canzone, titolo, durata, genere, data_uscita, num_ascolti)
canzoni_playlist (id_canzone, cod_playlist)
playlist (cod_playlist, nome, id_utente*)
utenti (id_utente, nome, cognome)

Rispondere in SQL alle seguenti query:

1. Il numero di ascolti maggiore, quello minore e quello medio ricevuto dalle canzoni presenti nel database.

2. Il numero di canzoni  con genere pop e durata maggiore di 3 minuti.

3. Titolo, durata e numero di ascolti delle canzoni uscite dal 2022 in poi. 

4. Elenco delle canzoni presenti nella playlist "Mattia04".

5. Elenco delle canzoni presenti nella playlist dell'utente Mara Sattei.

6. Elenco delle canzoni ordinate sulla base della data di uscita (dalla più recente alla meno recente) e a parità di data di uscita ordinarle sulla base del titolo in ordine alfabetico.

7. Nome e cognome degli utenti che hanno creato playlist al cui interno è presente la canzone "Sorriso grande".

8. Elenco degli utenti che non hanno creato una playlist.

9. Elenco delle canzoni che non si trovano in una playlist.

10. Elenco delle playlist che non hanno una canzone.

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 5: tutorial - social - categorie

Dato il seguente schema relazionale di un database:
categorie (id_categoria, nome, descrizione)
social_network (id_social_network, nome)
tutorial (id_tutorial, titolo, descrizione, livello, durata, link, id_categoria*)
tutorial_social_network (id_tutorial, id_social_network)

Rispondere in SQL alle seguenti query:

1. Titolo, descrizione e livello dei tutorial il cui titolo inizia con la lettera 'I'.

2. Elenco dei tutorial che appartengono alle seguenti categorie: Programmazione, Musica e Sport.

3. Elenco dei tutorial la cui durata è compresa tra 5 minuti e 15 minuti.

4. Per ogni livello indicare quanti tutorial sono presenti nel database.

5. Nomi dei social network in cui sono stati pubblicati almeno 5 tutorial.

6. Elenco dei tutorial che hanno durata pari alla durata massima dei tutorial presenti nel database.

7. Indicare quanti tutorial sono stati pubblicati nel social network 'Facebook'.

8. Elenco dei tutorial di categoria 'Programmazione' presenti nel social network 'Instagram'. 

9. Per ogni social network indicare quanti tutorial di categoria 'Business' sono stati pubblicati.

10. Elenco dei social dove non sono stati pubblicati alcun tutorial.

11. Elenco dei tutorial il cui titolo contiene la parola 'Java'.

12. Per ogni social network indicare la durata media dei tutorial che sono pubblicati in esso.

13. Il titolo dei tutorial che sono di livello di Intermedio e che hanno una durata più lunga della durata media tra tutti i tutorial di livello Intermedio. 

14. Per ogni categoria si vuole conoscere il numero di tutorial presenti nel social network 'Instagram'.

15.  Elenco dei tutorial ordinati da quello più breve a quello più lungo e a parità di durata ordinarli in base al titolo in ordine alfabetico.

16. Per ogni tutorial presente nel database indicare: il nome del tutorial, il livello, la durata, il nome della categoria, ed il nome del social network in cui è pubblicato. Se un tutorial è stato pubblicato in più social network, nella tabella risultante si avranno più righe per quel tutorial.

17. Elenco dei tutorial che hanno un livello "Principiante" o "Intermedio" e sono di categoria "Programmazione" o "Design".

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 6:  discoteche - dj - eventi

Dato il seguente schema relazionale di un database:
discoteche (id_discoteca, nome, indirizzo, citta, telefono)
dj (id_dj, nome, genere_musicale)
eventi (id_evento, nome, data, orario, descrizione, prezzo, id_discoteca*)
dj_eventi (id_dj, id_evento)

Rispondere in SQL alle seguenti query:

1. Le informazioni delle discoteche che hanno eventi disponibili a partire dal 1 aprile 2023.

2. La media dei prezzi degli eventi delle discoteche che si trovano a Roma.

3. Il numero di discoteche presenti a Roma.

4. Per ogni città indicare quante discoteche sono presenti. 

5. Elenco dei dj presenti per l'evento 'Festa House'. Oltre al nome del dj mostrare a schermo anche il suo genere musicale. 

6. Nomi dei dj che partecipano agli eventi che hanno il prezzo più caro.

7. Elenco degli eventi delle discoteche romane a partire dal 01/01/2022 ordinati dal più recente al meno recente.

8. Elenco degli eventi per cui non è previsto alcun dj.

9. Nomi dei dj che hanno partecipato ad eventi presso discoteche di Roma.

10. Indicare il numero di eventi a cui ha partecipato ogni dj presente nel db.

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link.

Esercizio 7:  controlli aeroportuali

Dato il seguente schema relazionale di un database:
passeggeri (id_passeggero, nome, cognome, nazionalita, tipo_documento, numero_documento)
viaggi (id_viaggio, partenza , aeroporto , motivo , id_passeggero*)
merci (id_merce, categoria, descrizione, quantita, id_viaggio*, id_controllo*)
controlli (id_controllo, punto_di_controllo, esito, dazio_doganale, data_inizio, durata, ora_inizio, note , id_passeggero*, id_addetto*)
addetti (id_addetto, nome, cognome)

Rispondere in SQL alle seguenti query:

1. Elenco di tutti i passeggeri che hanno come motivo del viaggio “Lavoro”.

2. Elenco dei passeggeri che hanno avuto come esito del controllo "Negativo".

3. Elenco dei passeggeri che hanno pagato dei dazi doganali. Per ognuno indicare la media della cifra pagata per i dazi doganali.

4. Per ogni passaggero individuare il numero di viaggi effettuati.

5. Selezionare tutte le merci controllate dalle 8 alle 21 e visualizzare L’addetto che ha controllato tale merce. 

6. Visualizzare le informazioni dei viaggi con accanto il nome del passeggero che lo ha effettuato. Ordinare i viaggi a partire dal più recente. 

7. Per ogni passeggero visualizzare coloro che hanno effettuato almeno 2 viaggi.

8. Selezionare il nome e il cognome dei passeggeri che in un controllo abbiano pagato il maggior importo di dazio doganale.

9. Selezionare  l'elenco dei passeggeri che hanno effettuato almeno un viaggio in cui sono state controllate merci. Visualizzare dunque il nome e cognome del passeggero, l’aeroporto del viaggio, la categoria e descrizione della merce, il punto di controllo dove è stato effettuato il controllo ed il nominativo dell’addetto che ha fatto tale controllo.

10. **Ci sono dei punti di controllo che effettuano un grande numero di controlli. In particolare si è interessati ai punti di controllo che hanno effettuato il maggior numero di controlli in assoluto, in modo tale da poter conoscere i nomi dei passeggeri che sono stati controllati in tali punti di controllo. Come poter rispondere a tale query? (Suggerimento creare viste parziali che ci consentono di giungere alla soluzione della query richiesta...), Provare a procedere per viste parziali del database: 

    1. Trovare per ogni punto di controllo il numero di controlli effettuati.

    2. Trovare quale/quali sono i punti di controllo dove sono stati eseguiti  un numero di controlli pari al massimo numero di controlli fatti in un punto di controllo.

    3. Trovare i nomi dei passeggeri che hanno subito un controllo in uno dei punti di controlli indicati dalla query precedente. 

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link. Il testo di questo esercizio deriva dal database ideato per risolvere la prova 2013 dell'esame di stato e dal prezioso aiuto dello studente NATHAN MBUYAMBA 5EI (A.S. 2022/23).

Esercizio 8:  Voli aerei

Dato il seguente schema relazionale di un database:
aeroporti (id_aeroporto, nome, via, citta, nazione, num_terminali, num_piste)
voli (id_volo, codice_volo, data, ora_partenza, ora_arrivo, id_aereo*, id_aeroporto_partenza*, id_aeroporto_arrivo*)
aerei (id_aereo, nome, tipo, num_passeggeri, id_compagnia*)
compagnie (id_compagnia, nome, sitoweb, peso_bagaglio_incluso)

Rispondere in SQL alle seguenti query:

1. Le compagnie aeree per cui non è specificato il sito web.

2. La nazione di partenza e la nazione di arrivo del volo con codice volo "A357Z".

3. Il nome della compagnia dell'aereo usato nel volo con id_volo uguale a 5.

4. Gli aereoporti da cui partono voli internazionali. (Sugg: un volo è considerato internazionale se parte l'aeroporto di arrivo si trova in una nazione diversa dall'aeroporto di partenza).

5. Gli aeroporti italiani da cui sono partiti più di 2 voli.

6. Per ogni compagni indicare il numero di aerei che ha.

7. Per ogni città indicare il numero di aeroporti presenti.

8. Il nome degli aeroporti che hanno il maggior numero di piste.

9. La media del peso del bagaglio incluso nel prezzo dalle compagnie aeree.

10. Nomi delle nazioni con più aeroporti.

Puoi testare il codice della tua soluzione importando su xampp il database in formato .sql presente al seguente link. 

Esercizi di DDL (Data Definition Language) e DML (Data Manipulation Language)

Esercizio 9:  Automobile

Scrivere il codice SQL che consente di creare il database "concessionaria". All'interno del database creare la tabella:

automobili (id_automobile, marca, modello, colore, velocita_massima, numero_cavalli)

In particolare: 

• id_automobile è la chiave prmaria auto incrementante;

• marca e modello devono essere due attributi obbligatori;

• la coppia di valori marca-modello ha sempre dei valori univoci;

• numero_postazioni deve essere messo di default a 5.

In seguito creare la tabella dei clienti che acquistano le automobili:

clienti (id_cliente, nome, cognome, codice_fiscale, genere, data_nascita)

In particolare:

• id_cliente è la chiave primaria auto incrementante;

• nome, cognome e data_nascita sono obbligatori;

• il codice_fiscale è di lunghezza fissa a 16 caratteri ed è univoco;

• il genere è di tipo enumerativo e può assumere i valori 'm' e 'f'.

Presupponendo che un cliente può acquistare molte macchine e che un'automobile può essere acquistata da un cliente, si aggiungano le colonne 'id_cliente' e 'data_acquisto' alla tabella automobili.
Infine si specifichi che la colonna id_cliente della tabella automobili è chiave esterna che fa riferimento alla colonna id_cliente della tabella clienti.

Esercizio 11:  Sport

Si vuole creare un database in grado di tenere traccia degli sport che si giocano alle olimpiadi e agli atleti che partecipano in tale olimpiade. Realizzare:

1.  lo schema E-R;

2. lo schema logico;

3. i comandi di DDL in grado di creare il database degli sport olimpici e le tabelle previste dal modello logico;

4. i comandi di DML in grado di inserire  almeno cinque righe in ogni tabella;

5. le query in grado di mostrare: 

•  l'elenco degli sport di squadra (sono gli sport in cui possono giocare almeno due giocatori);

• l'elenco degli atleti che giocano a  tennis nati a partire dagli anni 2000;

• per ogni sport indicare il numero di atleti che  praticano tale sport;

• indicare i nomi degli sport  che hanno almeno  2 atleti che lo praticano;

• indicare i nomi  degli atleti  che sono più alti della media.