Vai al contenuto
code-to-code

Apro l'indice…

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

SQL4. JOINp. 84

JOIN, la palestra

Otto esercizi da scaricare, con i test

  • 60–90 minuti di lavoro

Otto esercizi, dal ★ al ★★★, ambientati sulla Linea Menta. Metti insieme due, tre, quattro tabelle; cerchi chi non ha mai fatto qualcosa; scegli se una condizione va nell'ON o nel WHERE. 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-join.zip20 KB

Prima di iniziare

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

Terminale
cd ~/Downloads/sql-join               # 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_join
psql -d sql_join -f linea_menta.sql
code .

Per ogni esercizio:

  1. Apri il file in palestra/. In alto, nei commenti: la storia, cosa fai, come provi.
  2. Sotto i commenti scrivi la query e salva.
  3. Guarda cosa risponde il database: psql -d sql_join -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 (le righe che mancano, quelle di troppo, l'errore di PostgreSQL).

I test guardano il risultato, non il modo in cui scrivi la query: ogni query giusta passa. L'ordine lo controllano solo gli esercizi che lo chiedono. Gli altri non hanno un ordine preciso: nel tuo psql le righe possono uscire in un altro ordine rispetto agli esempi qui sotto, ed è normale. I nomi delle colonne del risultato non contano (AS è libero), l'ordine delle colonne sì.

#EsercizioCosa faiDifficoltà
1Il treno di ogni corsaJOIN, WHERE, ORDER BY★
2Chi lavora doveJOIN che toglie chi non ha coppie★
3I ritardi più seridue JOIN in fila★★
4Da dove a dovela stessa tabella due volte★★
5I treni mai usati«chi non ha mai»★★
6Le patenti e chi le hadue LEFT JOIN attraverso il ponte★★★
7Tutti i treni, un giorno solocondizione nell'ON★★★
8Il prezzo di quel giornotre tabelle, due prezzi da confrontare★★★

1 · Il treno di ogni corsa ★

palestra/es01.sql

Sergio Colombo vuole il foglio del 16 novembre: per ogni corsa, a che ora è partita e con quale treno. Nella tabella corse c'è solo il numero del treno: il codice sta in treni. Dal più presto al più tardi.

Il risultato atteso (7 righe, due colonne):

 partenza | codice 
----------+--------
 05:42:00 | M12
 06:10:00 | M14
 ...

Come provi: pytest palestra/test_es01.py -v

Aiutino 1
Parti da corse c. L'altra tabella ha il codice del treno: quale colonna di corse punta a treni?
Aiutino 2
L'ON confronta la chiave esterna con la chiave primaria: t.id = c.treno_id.
Aiutino 3
Il giorno si filtra con il WHERE, come nel capitolo Leggere: una data va tra apici, '2026-11-16'.

2 · Chi lavora dove ★

palestra/es02.sql

Marta Villa vuole attaccare in bacheca l'elenco di chi ha una stazione fissa: nome, cognome e nome della stazione. Chi non ha una stazione non deve comparire. Nessun ordine particolare: 12 righe.

  nome  | cognome |     nome      
--------+---------+---------------
 Lucia  | Ferri   | Porta Codice
 Nadia  | Russo   | Vecchia Fiera
 ...

Come provi: pytest palestra/test_es02.py -v

Aiutino 1
È l'esempio A degli esercizi spiegati, quasi uguale.
Aiutino 2
Con la JOIN semplice chi ha stazione_id vuoto non ha coppie e sparisce: è proprio quello che vuoi.
Aiutino 3
Se PostgreSQL dice ambiguous, manca un soprannome davanti a nome.

3 · I ritardi più seri ★★

palestra/es03.sql

Lucia Ferri prepara il bollettino per la direzione: le corse con più di 5 minuti di ritardo, con il treno e il macchinista che le ha guidate. Dal ritardo più grande.

Colonne, in quest'ordine: la data, il codice del treno, il cognome del macchinista, il ritardo. 5 righe.

    data    | codice | cognome | ritardo_min 
------------+--------+---------+-------------
 2026-11-18 | M12    | Colombo |          15
 2026-11-16 | M12    | Pozzi   |          12
 ...

Come provi: pytest palestra/test_es03.py -v

Aiutino 1
Le corse hanno due chiavi esterne che ti servono: treno_id e macchinista_id. Due JOIN, una dopo l'altra.
Aiutino 2
Il macchinista sta in personale: JOIN personale p ON p.id = c.macchinista_id.
Aiutino 3
DESC mette il più grande per primo. Il test dell'ordine è separato da quello delle righe: se uno è verde e l'altro no, sai dove guardare.

4 · Da dove a dove ★★

palestra/es04.sql

Il cartellone di Piazza Query deve dire, per ogni corsa del 17 novembre, il nome della stazione di partenza e quello della stazione di arrivo. Le corse hanno solo due numeri, e la tabella da cui prendere i nomi è la stessa. Dalla partenza più presto.

Colonne, in quest'ordine: la partenza, il nome della stazione di partenza, il nome della stazione di arrivo. 7 righe.

 partenza |      nome      |      nome      
----------+----------------+----------------
 05:42:00 | Porta Codice   | Piazza Query
 06:50:00 | Via Variabile  | Largo Lista
 ...

Come provi: pytest palestra/test_es04.py -v

Aiutino 1
Ti serve stazioni due volte: nel FROM ci saranno due JOIN stazioni … con soprannomi diversi.
Aiutino 2
Un soprannome per la partenza (da), uno per l'arrivo (a). Il primo si lega con da_stazione_id, il secondo con a_stazione_id.
Aiutino 3
Se partenza e arrivo escono uguali in tutte le righe, hai usato la stessa chiave esterna nei due ON.

5 · I treni mai usati ★★

palestra/es05.sql

Ernesto Pozzi cammina nel deposito e conta i treni fermi. Vuole il codice di quelli che non hanno mai fatto una corsa. Una colonna, nessun ordine: 1 riga.

 codice 
--------
 T31
(1 row)

Come provi: pytest palestra/test_es05.py -v

Aiutino 1
«Chi non ha mai»: rileggi in teoria la ricetta in tre pezzi.
Aiutino 2
Parti dai treni, perché è l'elenco dei treni che vuoi. Poi LEFT JOIN corse.
Aiutino 3
Il WHERE cerca la chiave di corse vuota: IS NULL, mai = NULL.

6 · Le patenti e chi le ha ★★★

palestra/es06.sql

Paolo Riva compila il registro delle patenti del personale. Per ogni patente vuole il nome di chi la ha, e vuole vedere anche quelle che non ha nessuno: per quelle nome e cognome restano vuoti. Nessun ordine: 10 righe.

Colonne: nome della patente, nome, cognome.

    nome    |  nome   | cognome  
------------+---------+----------
 metro      | Sergio  | Colombo
 tram       | Sergio  | Colombo
 ...
 funicolare |         | 

Come provi: pytest palestra/test_es06.py -v

Aiutino 1
Parti dalle patenti: è la tabella che deve restare intera. Poi il ponte, poi personale.
Aiutino 2
Il ponte personale_patenti sta in mezzo. Due ON: pp.patente_id = pa.id e p.id = pp.personale_id.
Aiutino 3
Tutte e due devono essere LEFT JOIN. Se la seconda è una JOIN semplice, la riga vuota della funicolare viene scartata lì.

7 · Tutti i treni, un giorno solo ★★★

palestra/es07.sql

Ernesto Pozzi vuole il foglio del 17 novembre con tutti i treni, anche quelli che quel giorno non sono mai usciti dal deposito: per quelli la partenza resta vuota. Un treno con due corse quel giorno compare due volte. Nessun ordine: 9 righe.

Colonne: il codice, la partenza.

 codice | partenza 
--------+----------
 M14    | 06:50:00
 M12    | 08:20:00
 M12    | 05:42:00
 ...
 M18    | 
 ...

Come provi: pytest palestra/test_es07.py -v

Aiutino 1
Parti dai treni, con LEFT JOIN corse: nessun treno deve sparire.
Aiutino 2
Il giorno è una condizione sulla tabella di destra. Se la scrivi nel WHERE, M18 e T31 spariscono. Rileggi la tabella «ON o WHERE» in teoria.
Aiutino 3
Metti la data dopo l'ON, unita con AND: ON c.treno_id = t.id AND c.data = '…'.

8 · Il prezzo di quel giorno ★★★

palestra/es08.sql

Dal 1° novembre i prezzi sono cambiati, ma alcune vendite portano ancora il prezzo vecchio: in vendite.prezzo_pagato c'è la fotografia del prezzo del giorno in cui il biglietto è stato venduto. Il controllo vuole sapere quali vendite hanno un prezzo_pagato diverso dal prezzo attuale della tariffa. Nessun ordine: 6 righe.

Colonne, in quest'ordine: il nome della stazione, il nome della tariffa, il prezzo pagato, il prezzo attuale.

     nome     |    nome     | prezzo_pagato | prezzo 
--------------+-------------+---------------+--------
 Piazza Query | giornaliero |          7.60 |   8.00
 Porta Codice | ordinario   |          2.20 |   2.50
 ...

Come provi: pytest palestra/test_es08.py -v

Aiutino 1
Le vendite hanno due chiavi esterne: tariffa_id e stazione_id. Due JOIN, una per ciascuna.
Aiutino 2
Il confronto tra le due tabelle è un WHERE come gli altri: v.prezzo_pagato <> t.prezzo. Due colonne, una per tabella.
Aiutino 3
Il nome della stazione e il nome della tariffa sono tutte e due nome: ti servono i soprannomi per distinguerli nel SELECT.

Finiti? Le soluzioni sono nella pagina successiva, con il ragionamento e le trappole. Se qualcosa non va, c'è la pagina «Se qualcosa va storto».