Vai al contenuto
code-to-code

Apro l'indice…

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

SQL5. Riassumerep. 119

Riassumere, le soluzioni

Il ragionamento, il codice, le trappole

Per ogni esercizio, sempre lo stesso ordine: in italiano → il codice → le trappole. Per i più difficili, anche il passo per passo.

La tua soluzione è diversa ma i test sono verdi? Probabilmente va bene: spesso ci sono più strade giuste. Confronta il ragionamento, non le singole parole.

Le soluzioni complete

La stessa cartella, con il codice al posto di pass. Aprila dopo averci provato: i test sono gli stessi.

sql-riassumere-soluzioni.zip21 KB

1 · Il quadro dei tre giorni

In italiano. Dalla tabella delle corse, quattro numeri in una riga: tutte le righe, le righe con un ritardo, i macchinisti diversi, la somma dei passeggeri.

SQL
SELECT COUNT(*) AS corse,
       COUNT(ritardo_min) AS ritardi_registrati,
       COUNT(DISTINCT macchinista_id) AS macchinisti,
       SUM(passeggeri) AS passeggeri
FROM corse;
 corse | ritardi_registrati | macchinisti | passeggeri 
-------+--------------------+-------------+------------
    20 |                 17 |           4 |       2661
(1 row)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
COUNT(*) AS ritardi_registratiCOUNT(*) conta anche le tre corse con il ritardo NULL: 20 invece di 17test_es01_corse_ritardi_registrati_macchinisti_e_passeggeri
COUNT(macchinista_id) AS macchinistiSenza DISTINCT ogni corsa conta il suo macchinista: 20 invece di 4test_es01_corse_ritardi_registrati_macchinisti_e_passeggeri

2 · I ritardi e i giorni

In italiano. Media dei ritardi (le funzioni saltano i NULL, quindi è la media delle sole corse registrate), il ritardo più grande, la data più vecchia e la più recente.

SQL
SELECT ROUND(AVG(ritardo_min), 2) AS ritardo_medio,
       MAX(ritardo_min) AS ritardo_massimo,
       MIN(data) AS primo_giorno,
       MAX(data) AS ultimo_giorno
FROM corse;
 ritardo_medio | ritardo_massimo | primo_giorno | ultimo_giorno 
---------------+-----------------+--------------+---------------
          3.94 |              15 | 2026-11-16   | 2026-11-18
(1 row)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
AVG(COALESCE(ritardo_min, 0))I ritardi non registrati diventano zero ed entrano nella media: esce 3.35 invece di 3.94test_es02_media_dei_soli_ritardi_registrati_massimo_e_giorni
SUM(ritardo_min) / COUNT(*)Divide per 20 corse invece che per le 17 registrate: 3.35test_es02_media_dei_soli_ritardi_registrati_massimo_e_giorni

3 · L'incasso per tariffa

In italiano. Unisci le vendite alle tariffe per avere il nome, fai un gruppo per tariffa e per ogni gruppo conta le righe e somma il prezzo pagato. Poi metti in fila dall'incasso più grande.

SQL
SELECT t.nome AS tariffa,
       COUNT(*) AS biglietti,
       SUM(v.prezzo_pagato) AS incasso
FROM vendite v
JOIN tariffe t ON t.id = v.tariffa_id
GROUP BY t.nome
ORDER BY incasso DESC;
   tariffa   | biglietti | incasso 
-------------+-----------+---------
 giornaliero |         6 |   47.20
 ordinario   |        14 |   33.80
 ridotto     |         4 |    5.20
(3 rows)

Il settimanale non c'è: nessuna vendita, nessun gruppo.

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
SUM(t.prezzo) AS incassoUsa il prezzo di oggi invece di quello pagato: 6 vendite sono di prima dell'aumento, e l'incasso esce 48.00, 35.00test_es03_una_riga_per_tariffa_con_il_prezzo_pagato, test_es03_dall_incasso_piu_grande
ORDER BY incassoSenza DESC si parte dall'incasso più piccolo: le righe sono giuste, l'ordine notest_es03_dall_incasso_piu_grande
tolto il GROUP BYPostgreSQL rifiuta: t.nome non è né nel GROUP BY né dentro una funzionetest_es03_una_riga_per_tariffa_con_il_prezzo_pagato, test_es03_dall_incasso_piu_grande

4 · Chi ha guidato di più

In italiano. Unisci le corse al personale tramite macchinista_id. Un gruppo per macchinista (cognome e nome insieme). Per ogni gruppo: quante corse, quanti passeggeri. In ordine: più corse prima, e a parità il cognome.

SQL
SELECT p.cognome, p.nome,
       COUNT(*) AS corse,
       SUM(c.passeggeri) AS passeggeri
FROM corse c
JOIN personale p ON p.id = c.macchinista_id
GROUP BY p.cognome, p.nome
ORDER BY corse DESC, p.cognome;
 cognome  |  nome   | corse | passeggeri 
----------+---------+-------+------------
 Colombo  | Sergio  |     7 |        726
 Galbiati | Rita    |     6 |       1007
 Greco    | Tommaso |     6 |        718
 Pozzi    | Ernesto |     1 |        210
(4 rows)

Rita e Tommaso hanno 6 corse a testa: il cognome decide chi viene prima.

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
ORDER BY passeggeri DESCL'ordine segue i passeggeri: Rita Galbiati (1007) passa davanti a Sergio Colombotest_es04_piu_corse_poi_cognome
GROUP BY p.cognomep.nome è nel SELECT ma non nel GROUP BY: PostgreSQL rifiutatest_es04_una_riga_per_macchinista, test_es04_piu_corse_poi_cognome

5 · Le stazioni che vendono di più

In italiano. Unisci le vendite alle stazioni, un gruppo per stazione, conta e somma. Tieni i gruppi con almeno 3 righe: è una condizione sul conteggio, quindi HAVING.

SQL
SELECT s.nome AS stazione,
       COUNT(*) AS vendite,
       SUM(v.prezzo_pagato) AS incasso
FROM vendite v
JOIN stazioni s ON s.id = v.stazione_id
GROUP BY s.nome
HAVING COUNT(*) >= 3
ORDER BY vendite DESC;
   stazione   | vendite | incasso 
--------------+---------+---------
 Porta Codice |       9 |   31.30
 Piazza Query |       6 |   24.10
 Ponte Ciclo  |       3 |   12.70
(3 rows)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
WHERE COUNT(*) >= 3Una funzione nel WHERE: aggregate functions are not allowed in WHERE. I gruppi si filtrano con HAVINGtest_es05_solo_le_stazioni_con_almeno_3_vendite, test_es05_dalle_vendite_piu_numerose
HAVING COUNT(*) > 3Ponte Ciclo ha esattamente 3 vendite, e «almeno 3» le include: resta fuoritest_es05_solo_le_stazioni_con_almeno_3_vendite, test_es05_dalle_vendite_piu_numerose

6 · Novembre, stazione per stazione

In italiano. Due filtri, di due tipi. «Dal 1 novembre» riguarda la riga: WHERE, prima dei gruppi. «Almeno 8 euro» riguarda il totale del gruppo: HAVING, dopo.

SQL
SELECT s.nome AS stazione,
       COUNT(*) AS vendite,
       SUM(v.prezzo_pagato) AS incasso
FROM vendite v
JOIN stazioni s ON s.id = v.stazione_id
WHERE v.data >= '2026-11-01'
GROUP BY s.nome
HAVING SUM(v.prezzo_pagato) >= 8
ORDER BY incasso DESC;

Il disegno.

inizio FROM vendite v JOIN stazioni s per ogni riga se v.data >= '2026-11-01' no: passo scarta la riga GROUP BY s.nome per ogni gruppo se SUM(v.prezzo_pagato) >= 8 no: passo scarta il gruppo SELECT nome, COUNT(*), SUM(...) ORDER BY incasso DESC fine quattro righeFROM vendite v JOIN stazionisper ogni rigav.data >='2026-11-01'scarta la rigaGROUP BY s.nomeper ogni gruppoSUM(v.prezzo_pagato)>= 8scarta il gruppoSELECT nome, COUNT(*),SUM(...)ORDER BY incasso DESCquattro righenosìfinitinosìfiniti
Novembre, poi i gruppi
    stazione    | vendite | incasso 
----------------+---------+---------
 Porta Codice   |       6 |   19.30
 Piazza Query   |       4 |   14.30
 Ponte Ciclo    |       2 |   10.50
 Darsena Indice |       1 |    8.00
(4 rows)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
senza WHERE v.data >= …Entrano anche le vendite di ottobre: le stesse stazioni, ma con vendite e incassi più grandi (Porta Codice 9 vendite, 31.30)test_es06_solo_novembre_e_almeno_8_euro, test_es06_dall_incasso_piu_grande
HAVING SUM(v.prezzo_pagato) >= 8 AND v.data >= '2026-11-01'La data nell'HAVING: non è nel GROUP BY, e PostgreSQL rifiutatest_es06_solo_novembre_e_almeno_8_euro, test_es06_dall_incasso_piu_grande
HAVING SUM(v.prezzo_pagato) > 8Darsena Indice ha incassato esattamente 8.00: con > resta fuoritest_es06_solo_novembre_e_almeno_8_euro, test_es06_dall_incasso_piu_grande

7 · Anche i treni fermi

In italiano. Parti da tutti i treni (LEFT JOIN: ogni treno resta, anche senza corse) e attacca le corse. Un gruppo per treno. Conta le corse vere: COUNT(c.id) guarda la colonna di destra, e un NULL non si conta.

SQL
SELECT t.codice,
       COUNT(c.id) AS corse
FROM treni t
LEFT JOIN corse c ON c.treno_id = t.id
GROUP BY t.codice
ORDER BY corse DESC, t.codice;
 codice | corse 
--------+-------
 M12    |     7
 M14    |     3
 M16    |     3
 T28    |     3
 M18    |     2
 M20    |     1
 T05    |     1
 T31    |     0
(8 rows)

Passo per passo.

PassoCosa faCosa resta
FROM treni ttutti i treni8 righe
LEFT JOIN corse c …attacca le corse; chi non ne ha riceve una riga con NULL a destra21 righe: 20 corse, più una riga vuota per il T31
GROUP BY t.codiceun gruppo per treno8 gruppi (il T31 ha un gruppo con una riga, tutta vuota a destra)
COUNT(c.id)conta gli id di destra, i NULL noM12 7, …, T31 0
ORDER BY corse DESC, t.codicepiù corse prima, i pari merito per codiceM14, M16, T28 a quota 3 in ordine di codice

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
JOIN corse c …La JOIN semplice perde chi non ha coppie: il T31 sparisce e le righe sono 7test_es07_tutti_i_treni_anche_con_zero_corse, test_es07_piu_corse_poi_codice
COUNT(*) AS corse con la LEFT JOINConta la riga vuota creata dalla LEFT JOIN: il T31 risulta con 1 corsa invece di 0test_es07_tutti_i_treni_anche_con_zero_corse, test_es07_piu_corse_poi_codice

8 · I treni da controllare

In italiano. Unisci treni e corse, un gruppo per treno. Per ogni gruppo: conta le corse con il ritardo registrato e fai la media dei ritardi (che salta già i NULL). Poi filtra i gruppi: almeno 2 corse registrate e media almeno 1. Ordina dalla media più alta.

SQL
SELECT t.codice AS treno,
       COUNT(c.ritardo_min) AS corse_registrate,
       ROUND(AVG(c.ritardo_min), 2) AS ritardo_medio
FROM treni t
JOIN corse c ON c.treno_id = t.id
GROUP BY t.codice
HAVING COUNT(c.ritardo_min) >= 2
   AND AVG(c.ritardo_min) >= 1
ORDER BY ritardo_medio DESC, t.codice;
 treno | corse_registrate | ritardo_medio 
-------+------------------+---------------
 M12   |                6 |          6.83
 M16   |                2 |          3.00
 M14   |                3 |          1.00
 M18   |                2 |          1.00
(4 rows)

Passo per passo. I gruppi, prima dell'HAVING:

TrenoCorseRegistrateMedia dei registrati>= 2 registrate>= 1 di mediaResta?
M12766.83sìsìsì
M14331.00sìsìsì
M16323.00sìsìsì
M18221.00sìsìsì
T28320.50sìnono
M20116.00nosìno
T05118.00nosìno

Il T31 non compare: con la JOIN semplice un treno senza corse non ha righe e quindi non ha un gruppo. Qui va bene, perché non avrebbe superato l'HAVING comunque. M20 e T05 hanno una media alta ma una sola corsa registrata: troppo poco per fidarsi, e l'HAVING li scarta. M16 ha tre corse ma una sola con il ritardo NULL: ne contano due, e il conteggio giusto è COUNT(c.ritardo_min).

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
COUNT(*) AS corse_registrate nel SELECTConta anche la corsa con il ritardo NULL: M16 risulta con 3 corse registrate invece di 2, e M12 con 7 invece di 6test_es08_treni_con_ritardo_medio_di_almeno_1_su_2_corse_registrate, test_es08_dal_ritardo_medio_piu_alto
AVG(COALESCE(c.ritardo_min, 0))I NULL diventano zero: la media di M16 scende da 3.00 a 2.00, e anche quella di M12 cambiatest_es08_treni_con_ritardo_medio_di_almeno_1_su_2_corse_registrate, test_es08_dal_ritardo_medio_piu_alto
HAVING COUNT(c.ritardo_min) > 2 …Con > restano fuori M16 e M18, che hanno esattamente 2 corse registratetest_es08_treni_con_ritardo_medio_di_almeno_1_su_2_corse_registrate, test_es08_dal_ritardo_medio_piu_alto