Databases & SQL: oefenvragen met uitleg

1Wat een relationele database is

Een relationele database slaat gegevens op in tabellen. Een tabel lijkt op een spreadsheet: elke rij is één record, elke kolom is één eigenschap.

Een winkel kan een tabel customers hebben met de kolommen id, name en city, en een tabel orders met de kolommen id, customer_id en total.

Een primaire sleutel is een kolom, of een set kolommen, die elke rij uniek identificeert. Ze kan nooit worden herhaald en nooit leeg zijn. Hier is dat de id van elke tabel.

Een vreemde sleutel (foreign key) is een kolom die verwijst naar de primaire sleutel van een andere tabel. In orders zegt customer_id welke klant de bestelling plaatste.

Zo worden tabellen gekoppeld: één klant kan veel bestellingen hebben, maar elke bestelling hoort bij één klant. Dat is een een-op-veel-relatie.

SQL (Structured Query Language) is de taal waarmee je tabellen maakt, gegevens wijzigt en er vragen over stelt.

2Gegevens lezen met SELECT

De meeste vragen gaan over de SELECT-opdracht. Ze is opgebouwd uit clausules, altijd in deze volgorde geschreven:

• SELECT noemt de kolommen die je wilt. DISTINCT verwijdert dubbele rijen.
• FROM noemt de tabel, of de tabellen die aan elkaar gekoppeld zijn.
• WHERE houdt alleen de rijen over die aan een voorwaarde voldoen, bijvoorbeeld WHERE total > 20.
• GROUP BY stopt rijen met dezelfde waarde in één groep, zodat je per groep kunt tellen of optellen.
• HAVING houdt alleen de groepen over die aan een voorwaarde voldoen. Ze mag aggregaten gebruiken, wat WHERE niet mag.
• ORDER BY sorteert het resultaat, standaard oplopend; voeg DESC toe voor hoogste eerst.

De aggregatiefuncties vatten veel rijen samen in één waarde: COUNT, SUM, AVG, MIN en MAX.

De database voert de clausules niet uit in de volgorde waarin je ze schrijft. Ze voert eerst FROM uit, dan WHERE, GROUP BY, HAVING, SELECT en ten slotte ORDER BY.

Andere opdrachten wijzigen gegevens: INSERT voegt rijen toe, UPDATE wijzigt rijen en DELETE verwijdert rijen. CREATE, ALTER en DROP wijzigen de tabellen zelf.

3Joins, normalisatie en transacties

Een join combineert rijen van twee tabellen met een voorwaarde, meestal vreemde sleutel = primaire sleutel:

• INNER JOIN (of JOIN) houdt alleen rijen over die in beide tabellen een match hebben.
• LEFT JOIN houdt elke rij van de linkertabel. Waar geen match is, zijn de kolommen van de rechtertabel NULL.
• NULL betekent dat een waarde ontbreekt of onbekend is. Toets het met IS NULL, nooit met = NULL.

Normalisatie splitst tabellen zodat elk feit één keer wordt opgeslagen. Zo voorkom je dat hetzelfde feit op meerdere plaatsen moet worden bijgewerkt.

• 1NF: elke cel bevat één atomaire waarde, zonder herhalende groepen.
• 2NF: 1NF, en elke niet-sleutelkolom hangt af van de hele sleutel, niet van een deel van een samengestelde sleutel.
• 3NF: 2NF, en geen enkele niet-sleutelkolom hangt af van een andere niet-sleutelkolom.

Een transactie groepeert opdrachten tot één eenheid. COMMIT maakt haar definitief en ROLLBACK maakt haar ongedaan. Transacties volgen ACID: atomiciteit (alles of niets), consistentie, isolatie en duurzaamheid.

4De methode stap voor stap

Om een query te schrijven, of uit te zoeken wat een gegeven query teruggeeft:

1. Zoek de tabellen die de gegevens bevatten, en hoe ze door sleutels zijn gekoppeld.
2. Schrijf FROM met de joins. Kies LEFT JOIN als rijen zonder match moeten blijven.
3. Voeg WHERE toe voor voorwaarden op afzonderlijke rijen.
4. Als de vraag “per” of “voor elke” zegt, voeg dan GROUP BY en het aggregaat toe.
5. Voeg HAVING toe voor voorwaarden op het aggregaat, en dan ORDER BY als de volgorde ertoe doet.
6. Controleer het resultaat op een klein voorbeeld: som de rijen op na elke clausule, in de volgorde waarin de database ze uitvoert.

Benoem bij een theorievraag eerst het begrip (een sleutel, een jointype, een normaalvorm, een ACID-eigenschap) en kies dan de optie die bij de definitie past.

5Een uitgewerkt voorbeeld

Volg elke stap. Probeer de volgende stap te voorspellen voordat je hem opent.

6Veelgemaakte fouten

• Een aggregaat gebruiken in WHERE. Voorwaarden op COUNT, SUM of AVG horen in HAVING.
• = NULL schrijven. Elke vergelijking met NULL is onbekend; gebruik IS NULL of IS NOT NULL.
• Een INNER JOIN gebruiken als rijen zonder match moeten blijven. Gebruik een LEFT JOIN.
• De joinvoorwaarde vergeten. Zonder ON wordt elke rij met elke rij gekoppeld (een cross join).
• Een kolom selecteren die niet in GROUP BY staat en ook niet in een aggregaat.
• DELETE (verwijdert rijen) en DROP TABLE (verwijdert de hele tabel) door elkaar halen.
• UPDATE of DELETE uitvoeren zonder WHERE. Het verandert elke rij in de tabel.

7Hoe tentamenvragen eruitzien

• Makkelijk: kernbegrippen en feiten. Wat SELECT, WHERE, ORDER BY of DELETE doen, primaire en vreemde sleutels, wat NULL betekent.
• Gemiddeld: concepten toepassen. De juiste query voor een taak, WHERE tegenover HAVING, inner tegenover outer joins, de normaalvormen, ACID.
• Moeilijk: gevallen en analyse. Subqueries, isolatieniveaus en hun anomalieën, BCNF, vensterfuncties, indexen en queryprestaties, SQL-injectie.

Traceer een gegeven query op een paar rijen in de volgorde FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. De meeste valkuilen komen binnen drie rijen naar boven.