SQL: SELECT, JOIN e GROUP BY con esempi svolti

Se sei in quinta all’istituto tecnico a indirizzo informatico (o in quarta, secondo come è organizzato il piano), da ottobre a dicembre di solito si lavora su SQL: creazione delle tabelle, inserimento dei dati, interrogazioni con JOIN e raggruppamenti. L’argomento è appena iniziato o sta per iniziare, e la verifica o la parte di esame che lo contiene cade fra cinque e nove settimane. Dal diagramma E-R alle tabelle (chiavi esterne, relazioni N:N) e dalle JOIN ai raggruppamenti sono i punti dove vedo perdere più punti, quindi conviene fissare lo schema mentale adesso.

Non confonderlo con il post su E-R, modello relazionale e SQL per l’esame universitario: quello parte da normalizzazione e modellazione a livello di corso universitario. Qui restiamo sulle query, con un esempio piccolo che puoi rifare a mano. Se sei in quinta, nello stesso periodo hai anche sistemi e reti: vale la stessa regola, meglio partire presto.

Lo schema d’esempio

Tre tabelle, con la relazione N:N tra studenti e corsi risolta dalla tabella iscrizioni, che contiene le due chiavi esterne:

CREATE TABLE studenti (
  id     INT PRIMARY KEY,
  nome   VARCHAR(30),
  classe VARCHAR(5)
);

CREATE TABLE corsi (
  id     INT PRIMARY KEY,
  titolo VARCHAR(30),
  ore    INT
);

CREATE TABLE iscrizioni (
  studente_id INT REFERENCES studenti(id),
  corso_id    INT REFERENCES corsi(id),
  voto        INT,
  PRIMARY KEY (studente_id, corso_id)
);

Dati:

Nota: Luca (id 4) non ha nessuna iscrizione. Tienilo d’occhio: è lui il motivo per cui esiste il LEFT JOIN.

SELECT, WHERE, ORDER BY

L’ordine con cui scrivi una query non è quello con cui viene eseguita. Quello logico è: FROM (da dove), WHERE (quali righe), GROUP BY (come raggrupparle), HAVING (quali gruppi), SELECT (quali colonne), ORDER BY (in che ordine). Capirlo risolve metà degli errori su HAVING.

SELECT nome
FROM studenti
WHERE classe = '5B'
ORDER BY nome;

Risultato: Luca, Sara.

INNER JOIN e LEFT JOIN

Per sapere chi ha preso quale voto in quale corso servono tutte e tre le tabelle. La condizione di join collega chiave esterna e chiave primaria:

SELECT s.nome, c.titolo, i.voto
FROM studenti s
JOIN iscrizioni i ON i.studente_id = s.id
JOIN corsi c      ON c.id = i.corso_id
ORDER BY s.nome, c.titolo;

Sei righe, tante quante le iscrizioni. Luca non compare: JOIN da solo è un INNER JOIN, tiene solo le righe con corrispondenza. Per tenere anche gli studenti senza iscrizioni:

SELECT s.nome, i.corso_id
FROM studenti s
LEFT JOIN iscrizioni i ON i.studente_id = s.id
WHERE s.nome = 'Luca';

Risultato: una riga, Luca con corso_id NULL. Il LEFT JOIN conserva tutte le righe della tabella di sinistra e riempie con NULL dove manca la corrispondenza.

GROUP BY, funzioni aggregate, HAVING

GROUP BY forma un gruppo per ogni valore della colonna indicata, e le funzioni aggregate (COUNT, SUM, AVG, MIN, MAX) lavorano su ciascun gruppo. Ogni colonna in SELECT che non è dentro un’aggregata deve comparire nel GROUP BY.

WHERE e HAVING: WHERE filtra le righe prima del raggruppamento e non può contenere aggregate; HAVING filtra i gruppi dopo e può contenerle.

Errori tipici

• Dimenticare la condizione di join e ottenere il prodotto cartesiano (qui $4\times 6=24$ righe invece di 6).
• Mettere un’aggregata nel WHERE (WHERE COUNT(*) > 1 è un errore: serve HAVING).
• Colonna in SELECT non nel GROUP BY e non aggregata.
• COUNT(*) con LEFT JOIN: conta anche la riga NULL, quindi Luca vale 1 invece di 0. Per contare le iscrizioni usa COUNT(i.corso_id).
• Condizione sulla tabella di destra nel WHERE di un LEFT JOIN: trasforma di fatto il LEFT JOIN in un INNER JOIN, perché le righe NULL vengono scartate.
• N:N senza tabella ponte, o ponte senza chiave primaria composta.

Esercizio 1: media dei voti per corso

Per ogni corso, titolo, numero di iscritti e media dei voti arrotondata a due decimali.

SELECT c.titolo, COUNT(*) AS iscritti, ROUND(AVG(i.voto), 2) AS media
FROM corsi c
JOIN iscrizioni i ON i.corso_id = c.id
GROUP BY c.titolo
ORDER BY c.titolo;

Conti. Database: voti 8, 6, 9, somma 23, media $23/3=7,67$. Python: solo 7. Reti: 7 e 8, media 7,5.

Esercizio 2: studenti con il numero di corsi, anche zero

Elenca tutti gli studenti con quanti corsi seguono, anche chi ne segue zero.

SELECT s.nome, COUNT(i.corso_id) AS corsi
FROM studenti s
LEFT JOIN iscrizioni i ON i.studente_id = s.id
GROUP BY s.nome
ORDER BY s.nome;

Conti. Anna: 2 iscrizioni. Luca: nessuna iscrizione, COUNT(i.corso_id) salta il NULL e dà 0. Marco: 1. Sara: 3.

Esercizio 3: WHERE contro HAVING

Studenti con almeno due esami e media dei voti almeno 7,5.

SELECT s.nome, COUNT(*) AS esami, AVG(i.voto) AS media
FROM studenti s
JOIN iscrizioni i ON i.studente_id = s.id
GROUP BY s.nome
HAVING COUNT(*) >= 2 AND AVG(i.voto) >= 7.5
ORDER BY s.nome;

Conti. Anna: 2 esami, media $(8+7)/2=7,5$, passa. Marco: 1 esame, scartato. Sara: 3 esami, media $(9+8+7)/3=8$, passa. Luca non compare (INNER JOIN).

Prova a riscrivere la stessa richiesta mettendo COUNT(*) >= 2 nel WHERE: il database ti darà errore. Capire perché è il modo più veloce per ricordare la differenza.

Metti alla prova

Per informatica non c’è ancora una simulazione dedicata. Per allenarti sul metodo (leggere un testo, tradurlo in passaggi, controllare i conti) puoi usare le simulazioni di matematica e fisica del liceo. Il modo migliore per prepararti all’esame è riscrivere le query dell’esempio a mano, prevedere il risultato prima di eseguirle e poi controllare. Se vuoi farlo con me, faccio ripetizioni 1-a-1 online.