Vai al contenuto
code-to-code

Apro l'indice…

↑ ↓ per scegliere, Invio per aprire, Esc per chiudere

SQL5. Riassumerep. 114

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.

sql-riassumere.zip20 KB

Prima di iniziare

Scompatta lo zip. Dal terminale (su Windows quello di Ubuntu), un comando alla volta:

Terminale
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:

  1. Apri il file in palestra/. In alto, nei commenti: la storia, cosa fai (colonne, ordine, righe), come provi.
  2. Sotto i commenti scrivi la query e salva.
  3. Guarda cosa risponde il database: psql -d sql_riassumere -f palestra/es01.sql.
  4. Lancia il test: pytest palestra/test_es01.py -v, oppure la provetta di VS Code.
  5. 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.

#EsercizioCosa faiDifficoltà
1Il quadro dei tre giorniCOUNT, COUNT(colonna), COUNT(DISTINCT …), SUM★
2I ritardi e i giorniAVG, MAX, MIN su una data★
3L'incasso per tariffaGROUP BY dopo una JOIN★★
4Chi ha guidato di piùGROUP BY con due colonne, ordine su un conteggio★★
5Le stazioni che vendono di piùHAVING★★
6Novembre, stazione per stazioneWHERE e HAVING insieme★★
7Anche i treni fermiLEFT JOIN e COUNT(colonna di destra)★★★
8I treni da controllareHAVING 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
Una query con solo funzioni nel SELECT dà una riga sola: niente GROUP BY.
Aiutino 2
Tre conteggi diversi: tutte le righe, quelle con un valore in 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
Per i due decimali: 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
Il nome della tariffa sta in tariffe, i biglietti in vendite: una JOIN con vendite.tariffa_id.
Aiutino 2
Una riga per tariffa vuol dire GROUP BY sul nome. Il nome è anche nel SELECT: è consentito.
Aiutino 3
Per l'incasso somma la colonna di 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
Unisci corse con personale, poi raggruppa.
Aiutino 2
Nel 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
Un conteggio per stazione: JOIN con stazioni, GROUP BY sul nome.
Aiutino 2
«Almeno 3» è una condizione sul gruppo, non sulla riga: non va nel 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
Due condizioni: una sulla riga (la data) e una sul gruppo (l'incasso). Una va nel WHERE, l'altra nell'HAVING.
Aiutino 2
La data è >= '2026-11-01', tra apici.
Aiutino 3
L'ordine delle clausole: 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    |     0

Come provi: pytest palestra/test_es07.py -v

Aiutino 1
Un treno senza corse sparirebbe da una JOIN semplice. Quale JOIN tiene tutte le righe della tabella a sinistra?
Aiutino 2
Parti da treni, attacca corse. Il T31 avrà una riga con NULL a destra.
Aiutino 3
Per avere 0 e non 1, conta una colonna della tabella di destra (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
Tutte e due le condizioni riguardano il gruppo: vanno nell'HAVING, unite da AND.
Aiutino 2
Le corse con il ritardo registrato si contano con COUNT(c.ritardo_min): COUNT(*) conterebbe anche quelle con il tabellone guasto.
Aiutino 3
Nell'HAVING non puoi usare i nomi dati con AS: riscrivi AVG(c.ritardo_min) >= 1. Nell'ORDER BY invece sì.