Esercitazioni SQL: crea e interroga un database

⭐ Facile · CREATE TABLE, INSERT, SELECT con WHERE, ORDER BY e LIKE

Una rubrica telefonica salva i contatti di una classe: nome, cognome, telefono (che non può ripetersi), email (facoltativa), città e data di nascita.

Lo schema logico

contatti (id, nome, cognome, telefono, email, citta, data_nascita)

I dati da inserire

contatti

Cosa fare

1. Crea il database es_rubrica e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Cognome, nome e telefono di tutti i contatti, in ordine alfabetico di cognome e nome.
   2. I contatti che abitano a Torino.
   3. I contatti che non hanno l'email.
   4. I contatti il cui cognome inizia con la lettera B.
   5. Chi compie gli anni a marzo (suggerimento: la funzione MONTH).
   6. Quanti contatti ci sono in ogni città.

Da pensare

• Giulia Bianchi ha finalmente un'email: giulia.bianchi@mail.it. Aggiorna il suo contatto e controlla con una SELECT.
• Prova a inserire un nuovo contatto con lo stesso telefono di Marco Rossi. Che cosa succede? Perché?

⭐ Facile · chiave esterna, JOIN tra due tabelle, LEFT JOIN, AVG e COUNT

Una videoteca registra i film e i loro registi. Ogni film ha un solo regista; un regista può aver girato più film, oppure nessuno di quelli presenti in archivio.

Lo schema logico

registi (id, nome, cognome, nazionalita)
film (id, titolo, anno, durata, genere, regista_id*)   regista_id → registi(id)

I dati da inserire

registi

film

Cosa fare

1. Crea il database es_videoteca e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Titolo, anno, nome e cognome del regista di tutti i film, dal più recente (a parità di anno, per titolo).
   2. I film girati da registi italiani.
   3. I film usciti dal 2000 in poi che durano più di due ore e mezza.
   4. La durata media dei film per ogni genere, arrotondata al minuto.
   5. Quanti film ha girato ogni regista, compresi quelli che in archivio non ne hanno nessuno.
   6. Il film più lungo, con il suo regista (usa una sottoquery con MAX).

Da pensare

• Prova a cancellare il regista Christopher Nolan. Che cosa succede? Come si potrebbe fare se si volessero cancellare anche i suoi film?

⭐⭐ Medio · relazione N:M con tabella ponte, LEFT JOIN, HAVING

Una palestra offre dei corsi, ciascuno in un giorno e a un'ora fissi e con un numero massimo di posti. Ogni iscritto può frequentare più corsi e ogni corso ha più iscritti: per ogni iscrizione si registra la data.

Lo schema logico

corsi (id, nome, giorno, ora, posti)
iscritti (id, nome, cognome, data_nascita)
iscrizioni (iscritto_id*, corso_id*, data_iscrizione)   iscritto_id → iscritti(id), corso_id → corsi(id)

I dati da inserire

corsi

iscritti

iscrizioni

Cosa fare

1. Crea il database es_palestra e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Nome e cognome degli iscritti al corso di Spinning.
   2. Per ogni corso, il numero di iscritti (anche zero).
   3. I corsi che non hanno nemmeno un iscritto.
   4. Per ogni corso, i posti ancora liberi.
   5. Gli iscritti che frequentano più di un corso, con il numero di corsi.
   6. I corsi frequentati da Anna Neri, con giorno e ora, in ordine di nome del corso.

Da pensare

• Il corso di Spinning è pieno. Il database impedisce di aggiungere un quarto iscritto? Se no, dove andrebbe fatto il controllo?

⭐⭐ Medio · JOIN su tre tabelle, SUM, BETWEEN, sottoquery, chiave primaria non numerica

Un'officina meccanica tiene traccia dei clienti, delle loro auto (identificate dalla targa) e degli interventi eseguiti su ciascuna auto, con data, descrizione e costo.

Lo schema logico

clienti (id, nome, cognome, telefono)
auto (targa, marca, modello, anno, cliente_id*)   cliente_id → clienti(id)
interventi (id, targa*, data, descrizione, costo)   targa → auto(targa)

I dati da inserire

clienti

auto

interventi

Cosa fare

1. Crea il database es_officina e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Gli interventi sulle auto di Mario Esposito: targa, modello, data, descrizione e costo, in ordine di data.
   2. Quanto ha speso in tutto ogni cliente che ha fatto almeno un intervento, dal più alto.
   3. Le auto mai riparate, con nome e cognome del proprietario.
   4. L'intervento più costoso: targa, descrizione, costo e cognome del cliente.
   5. Quanti tagliandi sono stati fatti e qual è il loro costo medio.
   6. Gli interventi del primo semestre 2026, in ordine di data.

⭐⭐ Medio · due chiavi esterne verso la stessa tabella, alias multipli, valori NULL

La scuola organizza un torneo di calcetto tra le classi. Ogni partita si gioca tra una squadra di casa e una squadra ospite; finché la partita non è giocata, i gol restano vuoti (NULL).

Lo schema logico

squadre (id, nome, classe)
partite (id, data, casa_id*, ospite_id*, gol_casa, gol_ospite)   casa_id → squadre(id), ospite_id → squadre(id)

I dati da inserire

squadre

partite

Cosa fare

1. Crea il database es_torneo e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Il calendario completo: data, nome della squadra di casa, nome della squadra ospite e gol, in ordine di data.
   2. Le partite ancora da giocare.
   3. Le partite finite in pareggio.
   4. Le vittorie della squadra di casa, con il nome della squadra che ha vinto.
   5. Tutte le partite dei Bit, giocate in casa o fuori.
   6. Sfida. I gol segnati in tutto da ogni squadra, in casa e fuori (suggerimento: UNION ALL in una sottoquery).

Da pensare

• Inserisci il risultato della partita del 19 ottobre tra Packet Lost e Gli Ottetti: 3 a 2.

⭐⭐⭐ Impegnativo · N:M con attributo (quantità), calcoli nelle query, LIMIT, HAVING

Una pizzeria consegna a domicilio. Ogni ordine appartiene a un cliente, ha data e ora e un indicatore che dice se è già stato consegnato. In un ordine ci sono una o più pizze, ciascuna con la sua quantità.

Lo schema logico

pizze (id, nome, prezzo)
clienti (id, nome, cognome, indirizzo)
ordini (id, cliente_id*, data_ora, consegnato)   cliente_id → clienti(id)
dettagli_ordine (ordine_id*, pizza_id*, quantita)   ordine_id → ordini(id), pizza_id → pizze(id)

I dati da inserire

pizze

clienti

ordini

dettagli_ordine

Cosa fare

1. Crea il database es_pizzeria e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Il dettaglio dell'ordine numero 2: nome della pizza, quantità, prezzo unitario e subtotale.
   2. Il totale di ogni ordine, con data, ora e cognome del cliente.
   3. La pizza più venduta, contando le quantità (suggerimento: ORDER BY e LIMIT 1).
   4. Le pizze che nessuno ha mai ordinato.
   5. Gli ordini non ancora consegnati, con l'indirizzo di consegna.
   6. I clienti che in tutto hanno speso più di 30 €.

Da pensare

• Il prezzo delle pizze cambia nel tempo: se domani la Margherita costa 6,50 €, che cosa succede ai totali degli ordini già fatti? Come cambieresti lo schema?

⭐⭐⭐ Impegnativo · date e orari, TIMESTAMPDIFF, NOT IN con condizioni, self join

I docenti prenotano i laboratori della scuola per le loro classi: per ogni prenotazione si registrano il laboratorio, il docente, la classe, la data e l'ora di inizio e di fine. Due prenotazioni dello stesso laboratorio non dovrebbero sovrapporsi… ma nei dati ce n'è una sbagliata.

Lo schema logico

laboratori (id, nome, posti)
docenti (id, nome, cognome)
prenotazioni (id, laboratorio_id*, docente_id*, classe, data, ora_inizio, ora_fine)   laboratorio_id → laboratori(id), docente_id → docenti(id)

I dati da inserire

laboratori

docenti

prenotazioni

Cosa fare

1. Crea il database es_laboratori e scrivi le CREATE TABLE, scegliendo i tipi e i vincoli giusti: chiavi primarie, NOT NULL, UNIQUE e CHECK dove servono, chiavi esterne.

2. Inserisci i dati con le INSERT.

3. Scrivi le query:

   1. Le prenotazioni del 20 ottobre 2026: laboratorio, ora di inizio e di fine, classe e cognome del docente, in ordine di laboratorio e di ora.
   2. Quante ore di laboratorio ha prenotato in tutto ogni docente (suggerimento: TIMESTAMPDIFF(HOUR, inizio, fine)).
   3. I laboratori liberi il 20 ottobre 2026 alle 12:00 (un laboratorio è occupato se una prenotazione inizia entro le 12:00 e finisce dopo).
   4. I docenti con almeno 3 prenotazioni.
   5. Sfida. Trova le prenotazioni sovrapposte: stesso laboratorio, stessa data, orari che si accavallano (suggerimento: la tabella prenotazioni unita con sé stessa, con due alias).

⭐⭐⭐ Impegnativo · dal testo al database, come all'Esame di Stato

Questa volta niente schema già pronto: si parte dal testo, come nella seconda prova. Prima di cominciare, rileggi il metodo della lezione 16.

Un cinema multisala vuole gestire la programmazione e la vendita dei biglietti. Il cinema ha alcune sale, ciascuna con un nome e un numero di posti. Di ogni film si registrano titolo, durata, genere e anno di uscita. La programmazione è fatta di proiezioni: ogni proiezione è un film, in una sala, in un giorno e a un'ora precisi, con un prezzo del biglietto (che può cambiare, per esempio, tra pomeriggio e sera). Alla cassa o online si vendono i biglietti: ogni acquisto riguarda una proiezione e uno o più posti, e va registrato con data e ora. Non si possono vendere più biglietti dei posti della sala.

Cosa fare

1. Scrivi le ipotesi aggiuntive che ti servono (per esempio: i posti sono numerati?).

2. Disegna il modello E/R, con entità, attributi, associazioni e cardinalità.

3. Ricava lo schema logico e scrivi il DDL con tutti i vincoli.

4. Inserisci dati di prova: almeno 3 sale, 4 film, 6 proiezioni in due giorni e alcuni biglietti, con almeno uno spettacolo tutto esaurito.

5. Scrivi le query:

   1. la programmazione di un certo giorno: ora, film, sala e prezzo, in ordine di ora;
   2. i posti ancora liberi per ogni proiezione, comprese quelle senza biglietti venduti;
   3. l'incasso totale di ogni film, dal più alto;
   4. il film con più spettatori in un certo mese;
   5. le sale non usate in un certo giorno;
   6. le proiezioni con il tutto esaurito.

Seconda parte (scegli un quesito)

• Il cinema vuole i posti numerati (fila e numero). Come cambia lo schema? Come eviti di vendere due volte lo stesso posto per la stessa proiezione?
• Due casse vendono nello stesso istante l'ultimo posto libero. Che cosa può succedere? Come lo eviti con una transazione?

Qui i risultati attesi non ci sono, perché dipendono dalle tue ipotesi e dai tuoi dati: confronta il tuo lavoro con il docente.