Basi di dati e SQL: domande di esercitazione con spiegazioni

1Che cos’è un database relazionale

Un database relazionale memorizza i dati in tabelle. Una tabella assomiglia a un foglio di calcolo: ogni riga è un record, ogni colonna è una proprietà.

Un negozio potrebbe avere una tabella customers con le colonne id, name e city, e una tabella orders con le colonne id, customer_id e total.

Una chiave primaria è una colonna, o un insieme di colonne, che identifica in modo univoco ogni riga. Non può mai essere ripetuta e non può mai essere vuota. Qui è l’id di ciascuna tabella.

Una chiave esterna è una colonna che fa riferimento alla chiave primaria di un’altra tabella. In orders, customer_id dice quale cliente ha effettuato l’ordine.

Così le tabelle sono collegate: un cliente può avere molti ordini, ma ogni ordine appartiene a un solo cliente. Questa è una relazione uno-a-molti.

SQL (Structured Query Language) è il linguaggio che usi per creare tabelle, modificare dati e fare domande su di essi.

2Leggere i dati con SELECT

La maggior parte delle domande riguarda l’istruzione SELECT. È costruita con clausole, sempre scritte in quest’ordine:

• SELECT elenca le colonne che vuoi. DISTINCT elimina le righe duplicate.
• FROM indica la tabella, o le tabelle unite.
• WHERE mantiene solo le righe che soddisfano una condizione, per esempio WHERE total > 20.
• GROUP BY raggruppa le righe con lo stesso valore, così puoi contare o sommare per gruppo.
• HAVING mantiene solo i gruppi che soddisfano una condizione. Può usare le aggregazioni, cosa che WHERE non può fare.
• ORDER BY ordina il risultato, in ordine crescente per impostazione predefinita; aggiungi DESC per avere prima i più alti.

Le funzioni di aggregazione riassumono molte righe in un solo valore: COUNT, SUM, AVG, MIN e MAX.

Il database non esegue le clausole nell’ordine in cui le scrivi. Esegue prima FROM, poi WHERE, GROUP BY, HAVING, SELECT e infine ORDER BY.

Altre istruzioni modificano i dati: INSERT aggiunge righe, UPDATE modifica righe e DELETE elimina righe. CREATE, ALTER e DROP modificano le tabelle stesse.

3Join, normalizzazione e transazioni

Un join combina le righe di due tabelle usando una condizione, di solito chiave esterna = chiave primaria:

• INNER JOIN (o JOIN) mantiene solo le righe che hanno una corrispondenza in entrambe le tabelle.
• LEFT JOIN mantiene ogni riga della tabella di sinistra. Dove non c’è corrispondenza, le colonne della tabella di destra sono NULL.
• NULL significa che un valore manca o è sconosciuto. Verificalo con IS NULL, mai con = NULL.

La normalizzazione divide le tabelle in modo che ogni fatto sia memorizzato una sola volta. Così si evita di aggiornare lo stesso fatto in più posti.

• 1NF: ogni cella contiene un valore atomico, senza gruppi ripetuti.
• 2NF: 1NF, e ogni colonna non chiave dipende dall’intera chiave, non da una parte di una chiave composta.
• 3NF: 2NF, e nessuna colonna non chiave dipende da un’altra colonna non chiave.

Una transazione raggruppa le istruzioni in un’unica unità. COMMIT la rende permanente e ROLLBACK la annulla. Le transazioni seguono le proprietà ACID: atomicità (tutto o niente), coerenza, isolamento e durabilità.

4Il metodo passo dopo passo

Per scrivere una query, o per capire che cosa restituisce una query data:

1. Trova le tabelle che contengono i dati, e come sono collegate dalle chiavi.
2. Scrivi FROM con i join. Scegli LEFT JOIN quando le righe senza corrispondenza devono restare.
3. Aggiungi WHERE per le condizioni sulle singole righe.
4. Se la domanda dice “per” o “per ciascuno”, aggiungi GROUP BY e l’aggregazione.
5. Aggiungi HAVING per le condizioni sull’aggregazione, poi ORDER BY se l’ordine conta.
6. Verifica il risultato su un piccolo esempio: elenca le righe dopo ogni clausola, nell’ordine in cui le esegue il database.

Per una domanda teorica, individua prima il concetto (una chiave, un tipo di join, una forma normale, una proprietà ACID), poi scegli l’opzione che corrisponde alla sua definizione.

5Un esempio svolto

Segui ogni passaggio. Prova a prevedere il successivo prima di aprirlo.

6Errori comuni

• Usare un’aggregazione in WHERE. Le condizioni su COUNT, SUM o AVG vanno in HAVING.
• Scrivere = NULL. Qualsiasi confronto con NULL è sconosciuto; usa IS NULL o IS NOT NULL.
• Usare un INNER JOIN quando le righe senza corrispondenza devono restare. Usa un LEFT JOIN.
• Dimenticare la condizione del join. Senza ON, ogni riga viene abbinata a ogni riga (un cross join).
• Selezionare una colonna che non è né in GROUP BY né dentro un’aggregazione.
• Confondere DELETE (elimina righe) e DROP TABLE (elimina l’intera tabella).
• Eseguire UPDATE o DELETE senza WHERE. Modifica ogni riga della tabella.

7Come sono fatte le domande d’esame

• Facile: termini e fatti chiave. Che cosa fanno SELECT, WHERE, ORDER BY o DELETE, chiavi primarie ed esterne, che cosa significa NULL.
• Medio: applicare i concetti. La query giusta per un compito, WHERE contro HAVING, join interni contro esterni, le forme normali, ACID.
• Difficile: casi e analisi. Sottoquery, livelli di isolamento e relative anomalie, BCNF, funzioni finestra, indici e prestazioni delle query, SQL injection.

Quando viene data una query, simulala su poche righe nell’ordine FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. La maggior parte delle trappole emerge entro tre righe.