Vai al contenuto
code-to-code

Apro l'indice…

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

SQL3. Le relazionip. 62

Le relazioni, le soluzioni

Il ragionamento, la query, le trappole

Per ogni esercizio, sempre lo stesso ordine: in italiano → la query → le trappole. Per il più difficile, anche il passo per passo.

La tua soluzione è diversa ma il test è verde? Probabilmente va bene: il test guarda il risultato, non come hai scritto la query. Confronta il ragionamento. E leggi comunque le trappole: alcune sembrano giuste e si rompono su un test.

Le soluzioni complete

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

sql-relazioni-soluzioni.zip18 KB

La palestra

1 · Dove punta la freccia

In italiano. In \d corse, sotto Foreign-key constraints, c'è la riga FOREIGN KEY (macchinista_id) REFERENCES personale(id). La freccia va a personale.

SQL
SELECT 'personale';
 ?column?  
-----------
 personale
(1 row)

La risposta è il nome della tabella, quella dopo REFERENCES. Il nome di quello che c'è dentro (un macchinista) è la parte del nome della colonna.

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
SELECT 'macchinisti';Una tabella dei macchinisti non esiste: i macchinisti sono righe di personale, con il ruolo macchinistatest_es01_macchinista_id_punta_alla_tabella_del_personale
SELECT 'id';Hai risposto con la colonna a cui punta, non con la tabella. Il nome della tabella sta prima della parentesitest_es01_macchinista_id_punta_alla_tabella_del_personale

2 · Le corse del T28

In italiano.

  1. Cerco il treno T28 in treni: mi dà il suo id, il 3.
  2. Cerco in corse le righe che hanno treno_id uguale a 3.
SQL
SELECT id
FROM treni
WHERE codice = 'T28';

SELECT data, partenza, passeggeri
FROM corse
WHERE treno_id = 3;
 id 
----
  3
(1 row)

    data    | partenza | passeggeri 
------------+----------+------------
 2026-11-16 | 06:25:00 |         38
 2026-11-17 | 09:05:00 |         17
 2026-11-18 | 06:25:00 |         41
(3 rows)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
WHERE id = 3 nella seconda queryIl 3 è l'id del treno, ma id in corse è l'id della corsa. Esce la corsa numero 3, che è di un M12: una riga sola, quella sbagliatatest_es02_le_corse_del_t28
WHERE treno_id = 'T28'treno_id è un numero e 'T28' è un testo: invalid input syntax for type integer: "T28"test_es02_le_corse_del_t28

La prima trappola è la più insidiosa: nessun errore, una riga che sembra plausibile. È capitata perché il treno 3 e la corsa 3 hanno lo stesso numero per coincidenza. Nel dubbio, guarda il nome della colonna prima del numero: treno_id punta al treno, id è della corsa.


3 · Gli oggetti delle stazioni grandi

In italiano.

  1. Cerco in stazioni quelle con 4 binari: escono due id, 1 e 5.
  2. Cerco in oggetti_smarriti le righe la cui stazione_id è uno dei due.
SQL
SELECT id
FROM stazioni
WHERE binari = 4;

SELECT descrizione, valore
FROM oggetti_smarriti
WHERE stazione_id IN (1, 5);
 id 
----
  1
  5
(2 rows)

       descrizione        | valore 
--------------------------+--------
 Ombrello nero            |  12.00
 ombrello a pois          |   8.50
 Telefono con cover rossa | 220.00
 Quaderno a quadretti     |   2.00
 Cuffie senza fili        |  60.00
(5 rows)

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
WHERE stazione_id = 1Hai tenuto un id solo: escono tre oggetti e mancano quelli di Piazza Query. Nessun erroretest_es03_gli_oggetti_delle_stazioni_con_4_binari
WHERE id IN (1, 5)Gli id delle stazioni confrontati con l'id dell'oggetto: escono lo zaino blu e il telefono, che con le stazioni grandi non c'entranotest_es03_gli_oggetti_delle_stazioni_con_4_binari

4 · Quali treni, quali stazioni

In italiano.

  1. La tabella ha due colonne: il treno e la stazione.
  2. Ognuna punta alla sua tabella madre: REFERENCES treni(id) e REFERENCES stazioni(id).
  3. La coppia non si ripete: la chiave primaria è fatta da tutte e due le colonne.
SQL
CREATE TABLE treni_stazioni (
    treno_id     INTEGER REFERENCES treni(id),
    stazione_id  INTEGER REFERENCES stazioni(id),
    PRIMARY KEY (treno_id, stazione_id)
);

Passo per passo. Guardala con \d treni_stazioni dopo averla creata:

             Table "public.treni_stazioni"
   Column    |  Type   | Collation | Nullable | Default 
-------------+---------+-----------+----------+---------
 treno_id    | integer |           | not null | 
 stazione_id | integer |           | not null | 
Indexes:
    "treni_stazioni_pkey" PRIMARY KEY, btree (treno_id, stazione_id)
Foreign-key constraints:
    "treni_stazioni_stazione_id_fkey" FOREIGN KEY (stazione_id) REFERENCES stazioni(id)
    "treni_stazioni_treno_id_fkey" FOREIGN KEY (treno_id) REFERENCES treni(id)
PezzoCosa fa
treno_id INTEGER REFERENCES treni(id)Il numero deve essere un id di treni: il treno 99 non entra
stazione_id INTEGER REFERENCES stazioni(id)Lo stesso per le stazioni
PRIMARY KEY (treno_id, stazione_id)La coppia è unica: la stessa coppia due volte non entra. Il treno 2 può comparire in tante righe, con stazioni diverse
not null nell'output di \dLa chiave primaria rende obbligatorie le due colonne, anche se non l'hai scritto

E il database fa rispettare la regola. Prova a mano, in psql (poi DROP TABLE treni_stazioni;):

INSERT INTO treni_stazioni VALUES (99, 1);
ERROR:  insert or update on table "treni_stazioni" violates foreign key constraint "treni_stazioni_treno_id_fkey"
DETAIL:  Key (treno_id)=(99) is not present in table "treni".

INSERT INTO treni_stazioni VALUES (2, 1);
INSERT 0 1

INSERT INTO treni_stazioni VALUES (2, 1);
ERROR:  duplicate key value violates unique constraint "treni_stazioni_pkey"
DETAIL:  Key (treno_id, stazione_id)=(2, 1) already exists.

Le trappole.

Il codice sbagliatoCosa succedeIl test rosso
Senza i due REFERENCES (solo INTEGER)Le colonne sono due numeri qualsiasi: la tabella accetta il treno 99 e la stazione 99, senza nessun erroretest_es04_le_due_chiavi_esterne, test_es04_un_treno_che_non_esiste_non_entra, test_es04_una_stazione_che_non_esiste_non_entra
treno_id INTEGER PRIMARY KEY (la chiave su una colonna sola)Un treno può comparire una volta sola: non può fermarsi in due stazioni. Il secondo INSERT del test è rifiutatotest_es04_le_colonne_e_la_chiave_primaria_doppia, test_es04_un_treno_in_tante_stazioni_e_una_stazione_con_tanti_treni
Senza nessuna chiave primariaLa stessa coppia entra due volte, e due righe uguali dicono la stessa cosa due voltetest_es04_le_colonne_e_la_chiave_primaria_doppia, test_es04_la_stessa_coppia_due_volte_no

Nel secondo caso il test sulle colonne è rosso per un motivo diverso dal terzo: la chiave primaria c'è, ma su una colonna sola, e il test vuole la coppia. Il messaggio dice cosa ha trovato e cosa si aspettava.