Vai al contenuto
code-to-code

Apro l'indice…

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

SQL7. Disegnare un databasep. 176

Il panettone di Porta Codice

Un'indagine della Linea Menta, a sei livelli

  • 90–120 minuti di lavoro

Milano, lunedì 7 dicembre 2026. Sant'Ambrogio. Linea Menta.

La Linea Menta compie sessant'anni. Sul mezzanino di Porta Codice, in una teca di vetro, aspetta il panettone gigante della Pasticceria Bellini: 30 chili, fatto da Rosa Bellini in persona. Alle sette il commissario Mapelli deve tagliarlo davanti ai giornalisti.

Alle 06:15 la teca è vuota. Per terra, zucchero a velo e il segno di due ruote.

«Ho un fascicolo», dice Mapelli, e lo posa sul tavolo. È un database: persone, segnalazioni, carrelli del deposito, corse, le bancarelle del mercatino di Sant'Ambrogio. «Ho anche un'ora di tempo. Fatelo parlare.»

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-disegnare-un-database.zip40 KB

Lo zip è lo stesso della palestra: la storia è nella cartella storia/. Usa tutto il libro: leggere, scrivere, collegare, riassumere, e le frecce tra le tabelle.

Il fascicolo

Prima di tutto, conosci il caso. Il commissario ti chiede di guardare le tabelle: se non ricordi come, è nel capitolo Tabelle, tipi e psql. Dopo averlo caricato (vedi sotto), \dt dice:

                 List of relations
 Schema |        Name        | Type  |    Owner    
--------+--------------------+-------+-------------
 public | bancarelle         | table | davidelista
 public | bancarelle_soci    | table | davidelista
 public | carrelli           | table | davidelista
 public | corse              | table | davidelista
 public | forniture          | table | davidelista
 public | persone            | table | davidelista
 public | prelievi_carrelli  | table | davidelista
 public | prodotti           | table | davidelista
 public | segnalazioni       | table | davidelista
 public | stazioni           | table | davidelista
 public | treni              | table | davidelista
 public | vendite_bancarelle | table | davidelista
(12 rows)

(La colonna Owner mostra il tuo nome utente.) Dodici tabelle. Questo schema l'ha disegnato qualcuno prima di te: mentre indaghi, guarda come. Con \d prelievi_carrelli vedi le sue frecce e una regola UNIQUE: a cosa serve? Quale tabella fa da ponte tra le persone e le bancarelle?

persone ◀── prelievi_carrelli ──▶ carrelli
   ▲
   ├──── bancarelle_soci ──▶ bancarelle ◀── forniture ──▶ prodotti
   │                              ▲                          ▲
   │                              └── vendite_bancarelle ────┘
   │
   └──── corse (il macchinista) ──▶ treni
                │
                └──▶ stazioni (due frecce: da e a)

I file

FileCosa c'èSi tocca?
panettone.sqlIl caso. I testi sono sigillati: aprire il file non serve, si caricaNo
livello1.sql … livello6.sqlUn file per livello, con la domanda in altoSì
test_livello1.py …I test. Usano dati di prova (prova.sql), non il caso: nessun test ti dice la soluzioneNo
verifica.pyIl commissario controlla il nome che gli daiNo

Come si gioca

Una volta sola, dalla cartella principale dello zip, con il venv acceso:

Terminale
createdb sql_disegnare_un_database_panettone
psql -d sql_disegnare_un_database_panettone -f storia/panettone.sql

Poi, per ogni livello (qui il primo):

Terminale
pytest storia/test_livello1.py -v                                     # il livello, sui dati di prova
psql -d sql_disegnare_un_database_panettone -f storia/livello1.sql    # la stessa query, sul caso vero

Il test controlla che la query sia giusta; psql ti mostra cosa trova nel caso vero. Scrivi le risposte su un foglio.

Il foglio di Nadia

Dalle 5 alle 6 del 7 dicembre il lettore del cancello del deposito era guasto. La guardia Nadia Russo ha segnato a mano chi ha preso un carrello, a che ora, e se l'ha riportato. Il carrello ha un codice, la persona un cognome: gli id li trovi nelle tabelle carrelli e persone. Serve al livello 2.

CarrelloChiPreso alleRiportato alle
C-2Brambati05:2005:50
C-6Galli05:31
C-4Galimberti05:38
C-1Sala05:1205:44

Una casella vuota vuol dire che il carrello non è ancora stato riportato: NULL.


Livello 1 · Cosa dicono le segnalazioni

«Voglio sapere cosa è stato scritto, oggi, sul panettone.»

livello1.sql: le segnalazioni del 7 dicembre 2026 che parlano di un panettone, in ordine di ora. Colonne: ora, testo.

La cosa nuova: niente. È il capitolo Leggere. Ma attenzione: il panettone lo hanno scritto in tre modi.

Aiutino 1
Stai cercando un pezzo di testo dentro una colonna: LIKE, con il %. Ma LIKE guarda le maiuscole.
Aiutino 2
Con ILIKE «panettone», «Panettone» e «PANETTONE» sono la stessa parola. E la data va tra apici.

Leggi bene i testi. Contengono quasi tutto quello che ti serve per i livelli dopo: un orario, un treno, un foglio, un lettore guasto.


Livello 2 · Il foglio di Nadia

«Il foglio ce l'ha Nadia in cabina. Copiatelo nel database, così possiamo interrogarlo.»

livello2.sql: le quattro righe del foglio, nella tabella prelievi_carrelli, tutte del 7 dicembre 2026. Colonne della tabella: carrello_id, persona_id, giorno, preso_alle, reso_alle.

La cosa nuova: scrivere. È il capitolo Scrivere, con una complicazione: nel foglio ci sono un codice e un cognome, e la tabella vuole degli id. Li trovi con due SELECT su carrelli e persone.

Aiutino 1
Una sola INSERT con quattro gruppi tra parentesi, separati da virgole, è più comoda di quattro INSERT.
Aiutino 2
Per le caselle vuote del foglio, NULL senza apici. Le ore invece vogliono gli apici: '05:20'.
Aiutino 3
Attento a quante volte lanci il file sul caso vero: la seconda volta PostgreSQL rifiuta le righe che ci sono già.

Livello 3 · Il carrello che non è tornato

«Chi ha preso un carrello tra le 05:00 e le 06:00, e non l'ha ancora riportato?»

livello3.sql: nome, cognome, codice del carrello e ora del prelievo. In ordine di ora. Solo il 7 dicembre.

La cosa nuova: tre tabelle in fila, e un NULL che significa «non ancora». Capitoli JOIN e Leggere.

Aiutino 1
prelievi_carrelli ha due frecce: verso persone e verso carrelli. Due JOIN, una per freccia.
Aiutino 2
Quattro condizioni insieme: il giorno, l'ora da 05:00 compresa a 06:00 esclusa, e reso_alle vuoto.
Aiutino 3
Per cercare il vuoto: IS NULL. Mai = NULL.

Livello 4 · Il treno delle 05:54

«Quel carrello è salito su un treno. Quale? E chi lo guidava?»

livello4.sql: il treno partito alle 05:54 del 7 dicembre. Colonne: codice del treno, nome e cognome del macchinista, stazione di partenza, stazione di arrivo.

La cosa nuova: la stessa tabella due volte. corse ha due frecce verso stazioni (da_stazione_id e a_stazione_id): serve due volte, con due soprannomi diversi. Capitolo JOIN.

Aiutino 1
Parti da corse: ha le frecce verso treni, persone (il macchinista), e due volte stazioni. Quattro JOIN.
Aiutino 2
Le due stazioni: JOIN stazioni da ON da.id = c.da_stazione_id e JOIN stazioni a ON a.id = c.a_stazione_id.
Aiutino 3
Alle 05:54 parte un treno ogni mattina. Il giorno conta.

Livello 5 · Le bancarelle del mercatino

«Alle sette, a Ponte Ciclo, il mercatino vendeva le fette di panettone. Quali bancarelle ne hanno vendute almeno trenta, oggi?»

livello5.sql: le bancarelle che il 7 dicembre hanno venduto almeno 30 «fetta di panettone», con il numero di fette e il prezzo medio a cui le hanno vendute (arrotondato a due decimali). Colonne: nome, fette, prezzo_medio. Da chi ne ha vendute di più.

La cosa nuova: raggruppare e contare. Capitolo Riassumere. E un dato che hai incontrato in questo capitolo: il prezzo pagato (vendite_bancarelle.prezzo_pagato) non è il prezzo di listino (prodotti.listino). Confrontali.

Aiutino 1
Si conta per bancarella: GROUP BY sul nome. Il prodotto («fetta di panettone») e il giorno si filtrano prima, nel WHERE.
Aiutino 2
Il filtro sul conteggio non può stare nel WHERE: i gruppi non ci sono ancora. Serve HAVING.
Aiutino 3
Il prezzo medio è AVG(prezzo_pagato); per arrotondare, ROUND(…, 2).

Livello 6 · I soci

«Una di queste bancarelle vende panettone senza averne mai comprato uno. Chi ci sta dietro?»

livello6.sql: le persone socie di una bancarella che non ha mai ricevuto una fornitura di «panettone intero» (nella tabella prodotti ha l'id 1). Colonne: nome, cognome.

La cosa nuova: «chi non ha mai», con LEFT JOIN, dentro un legame molti a molti (la tabella ponte bancarelle_soci). Capitoli JOIN e Le relazioni.

Aiutino 1
Parti da bancarelle, poi bancarelle_soci e persone per arrivare ai nomi (due JOIN normali).
Aiutino 2
Poi LEFT JOIN forniture: le bancarelle senza forniture restano, con le colonne di forniture vuote.
Aiutino 3
La condizione sul prodotto va nell'ON della LEFT JOIN, non nel WHERE: altrimenti butti via proprio le righe che cerchi. Nel WHERE resta solo IS NULL.

Il finale

Il commissario ha tre domande, e il tuo lavoro ti ha dato le risposte.

  • Al livello 3: chi non ha riportato il carrello?
  • Al livello 4: chi guidava il treno?
  • Al livello 6: chi sono i soci della bancarella che vende panettone senza averlo comprato?

Mettile una accanto all'altra. Una persona sola compare nelle risposte giuste, e non è il macchinista.

Quando hai un nome:

Terminale
python storia/verifica.py "Nome Cognome"

Se è sbagliato, per le piste più probabili il commissario ti dice dove hai preso la strada sbagliata.