Riassumere, la palestra
Otto esercizi da scaricare, con i test
- 60–90 minuti di lavoro
Otto esercizi, dal ★ al ★★★, sui numeri della Linea Menta: corse, passeggeri, biglietti, treni. Ognuno ha il suo file .sql e il suo test: quando è tutto verde, hai finito.
Gli esercizi da scaricare
Una cartella con i file da completare e i test già pronti. Come si usa: la guida in cinque passi.
Prima di iniziare
Scompatta lo zip. Dal terminale (su Windows quello di Ubuntu), un comando alla volta:
cd ~/Downloads/sql-riassumere # o dove hai scompattato lo zip
python3 --version # 3.12 o più
python3 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
pg_isready # «accepting connections»
createdb sql_riassumere
psql -d sql_riassumere -f linea_menta.sql
code .Per ogni esercizio:
- Apri il file in
palestra/. In alto, nei commenti: la storia, cosa fai (colonne, ordine, righe), come provi. - Sotto i commenti scrivi la query e salva.
- Guarda cosa risponde il database:
psql -d sql_riassumere -f palestra/es01.sql. - Lancia il test:
pytest palestra/test_es01.py -v, oppure la provetta di VS Code. - Rosso? Leggi il messaggio: è in italiano e dice cosa non torna.
I test guardano il risultato, non il modo in cui lo scrivi: ogni query giusta passa. L'ordine delle colonne conta (è scritto in Colonne:); i nomi che dai con AS no. Le medie vanno a due decimali: con o senza ROUND il test le accetta.
| # | Esercizio | Cosa fai | Difficoltà |
|---|---|---|---|
| 1 | Il quadro dei tre giorni | COUNT, COUNT(colonna), COUNT(DISTINCT …), SUM | ★ |
| 2 | I ritardi e i giorni | AVG, MAX, MIN su una data | ★ |
| 3 | L'incasso per tariffa | GROUP BY dopo una JOIN | ★★ |
| 4 | Chi ha guidato di più | GROUP BY con due colonne, ordine su un conteggio | ★★ |
| 5 | Le stazioni che vendono di più | HAVING | ★★ |
| 6 | Novembre, stazione per stazione | WHERE e HAVING insieme | ★★ |
| 7 | Anche i treni fermi | LEFT JOIN e COUNT(colonna di destra) | ★★★ |
| 8 | I treni da controllare | HAVING con due condizioni, NULL che non contano | ★★★ |
1 · Il quadro dei tre giorni ★
palestra/es01.sql
Lucia Ferri prepara il quadro dei tre giorni di corse (dal 16 al 18 novembre): una riga sola con quante corse, per quante c'è il ritardo registrato, quanti macchinisti diversi hanno guidato e quanti passeggeri in tutto. Tabella corse.
Il risultato atteso (1 riga, 4 colonne): corse, ritardi_registrati, macchinisti, passeggeri. Il primo numero è 20.
Come provi: pytest palestra/test_es01.py -v
Aiutino 1
SELECT dà una riga sola: niente GROUP BY.Aiutino 2
ritardo_min, e i valori diversi di macchinista_id.Aiutino 3
COUNT(*), COUNT(colonna), COUNT(DISTINCT colonna): riguarda la teoria. Per i passeggeri serve SUM.2 · I ritardi e i giorni ★
palestra/es02.sql
Marta Villa, capostazione di Piazza Query, vuole due numeri sui ritardi e il periodo che coprono: il ritardo medio, il ritardo più grande, il primo e l'ultimo giorno delle corse.
Il risultato atteso (1 riga, 4 colonne): ritardo_medio, ritardo_massimo, primo_giorno, ultimo_giorno. La media ha due decimali e conta le sole corse con il ritardo registrato.
Come provi: pytest palestra/test_es02.py -v
Aiutino 1
AVG salta da solo i NULL: non devi fare niente per le corse con il tabellone guasto.Aiutino 2
MIN e MAX funzionano anche con le date.Aiutino 3
ROUND(AVG(colonna), 2).3 · L'incasso per tariffa ★★
palestra/es03.sql
Bruno Fontana chiude i conti del mese: quanto ha incassato ogni tariffa. Per ogni tariffa venduta: il nome, quanti biglietti e quanto si è incassato davvero (la somma di vendite.prezzo_pagato, non il prezzo di oggi). La tariffa mai venduta non compare. Dall'incasso più grande.
Il risultato atteso (3 righe): tariffa, biglietti, incasso. Le prime righe:
tariffa | biglietti | incasso
-------------+-----------+---------
giornaliero | 6 | 47.20
ordinario | 14 | 33.80
...Come provi: pytest palestra/test_es03.py -v
Aiutino 1
tariffe, i biglietti in vendite: una JOIN con vendite.tariffa_id.Aiutino 2
GROUP BY sul nome. Il nome è anche nel SELECT: è consentito.Aiutino 3
vendite: il prezzo in tariffe è quello di oggi, e alcune vendite sono più vecchie.4 · Chi ha guidato di più ★★
palestra/es04.sql
Ernesto Pozzi, capo deposito, compila la classifica dei macchinisti per il turno del mese prossimo. Per ogni macchinista che ha guidato: cognome, nome, quante corse e quanti passeggeri trasportati. Prima chi ha più corse; a parità, per cognome. corse.macchinista_id punta a personale.id.
Il risultato atteso (4 righe): cognome, nome, corse, passeggeri. La prima riga:
cognome | nome | corse | passeggeri
---------+--------+-------+------------
Colombo | Sergio | 7 | 726
...Come provi: pytest palestra/test_es04.py -v
Aiutino 1
corse con personale, poi raggruppa.Aiutino 2
SELECT ci sono due colonne di testo (cognome e nome): vanno tutte e due nel GROUP BY.Aiutino 3
ORDER BY accetta il nome che hai dato con AS (corse), e puoi mettere una seconda colonna per i pari merito.5 · Le stazioni che vendono di più ★★
palestra/es05.sql
Marta Villa vuole sapere dove si vendono più biglietti: le stazioni con molte vendite meritano un'altra macchinetta. Le stazioni con almeno 3 vendite: nome, numero di vendite, incasso (somma di prezzo_pagato). Dalle vendite più numerose.
Il risultato atteso (3 righe): stazione, vendite, incasso. La prima riga:
stazione | vendite | incasso
--------------+---------+---------
Porta Codice | 9 | 31.30
...Come provi: pytest palestra/test_es05.py -v
Aiutino 1
JOIN con stazioni, GROUP BY sul nome.Aiutino 2
WHERE.Aiutino 3
HAVING COUNT(*) e un confronto. Occhio all'estremo: «almeno 3» include il 3.6 · Novembre, stazione per stazione ★★
palestra/es06.sql
Bruno Fontana vuole l'incasso del solo mese di novembre, e solo per le stazioni che hanno portato almeno 8 euro. Considera le vendite dal 1 novembre 2026 in poi. Per ogni stazione: nome, numero di vendite e incasso. Dall'incasso più grande.
Il risultato atteso (4 righe): stazione, vendite, incasso. La prima riga:
stazione | vendite | incasso
--------------+---------+---------
Porta Codice | 6 | 19.30
...Come provi: pytest palestra/test_es06.py -v
Aiutino 1
WHERE, l'altra nell'HAVING.Aiutino 2
>= '2026-11-01', tra apici.Aiutino 3
WHERE, GROUP BY, HAVING, ORDER BY. Nell'HAVING si scrive la funzione intera: SUM(v.prezzo_pagato).7 · Anche i treni fermi ★★★
palestra/es07.sql
Ernesto Pozzi vuole l'elenco dei treni con quante corse hanno fatto, compreso chi non è mai uscito dal deposito. Per ogni treno (tutti e 8): il codice e il numero di corse; chi non ne ha deve comparire con 0. Prima chi ha più corse; a parità, per codice.
Il risultato atteso (8 righe): codice, corse. Le prime e l'ultima:
codice | corse
--------+-------
M12 | 7
M14 | 3
...
T31 | 0Come provi: pytest palestra/test_es07.py -v
Aiutino 1
JOIN semplice. Quale JOIN tiene tutte le righe della tabella a sinistra?Aiutino 2
treni, attacca corse. Il T31 avrà una riga con NULL a destra.Aiutino 3
c.id), non COUNT(*).8 · I treni da controllare ★★★
palestra/es08.sql
Ivo Brambati, il manutentore, sceglie quali treni controllare per primi: quelli che ritardano di più, ma solo se i dati sono abbastanza. Per ogni treno: il codice, quante corse hanno il ritardo registrato e il ritardo medio (due decimali, sulle sole corse registrate). Tieni i treni con almeno 2 corse registrate e ritardo medio di almeno 1. Dal ritardo medio più alto; a parità, per codice.
Il risultato atteso (4 righe): treno, corse_registrate, ritardo_medio. La prima riga:
treno | corse_registrate | ritardo_medio
-------+------------------+---------------
M12 | 6 | 6.83
...Come provi: pytest palestra/test_es08.py -v
Aiutino 1
HAVING, unite da AND.Aiutino 2
COUNT(c.ritardo_min): COUNT(*) conterebbe anche quelle con il tabellone guasto.Aiutino 3
HAVING non puoi usare i nomi dati con AS: riscrivi AVG(c.ritardo_min) >= 1. Nell'ORDER BY invece sì.