Key Idea: SQL does two different jobs: it defines the containers, and it works on what is in them. Ask of any statement: does it change the shape of the database, or its contents?
Paper 1
- Definitions, keys, normal forms.
- Identify the dependency; state the form broken.
Paper 2
- A scenario to design or normalise.
- Construct an ERD, tables, or SQL.
Both
- Name the dependency, not just the normal form.
- Every design choice needs a justification.
🏗️ The two sublanguages
| DDL — definition | DML — manipulation | |
|---|---|---|
| Changes | The shape | The contents |
| Statements | CREATE TABLE, ALTER TABLE, DROP TABLE | SELECT, INSERT, UPDATE, DELETE |
| Example | Adding a column | Adding a row |
Important: DROP TABLE removes the table's definition and every row — the table stops existing. DELETE FROM removes rows and leaves the empty table, its columns and its constraints standing.
🔍 Building a query
| Clause | What it does |
|---|---|
| SELECT | Which columns |
| FROM / JOIN | Which tables |
| WHERE | Filters rows, before grouping |
| GROUP BY | Combines rows into groups |
| HAVING | Filters groups, after grouping |
| ORDER BY | Sorts the result |
WHERE is applied to individual rows before grouping, so it can only test values that exist in a row. HAVING is applied to the groups after, so it is the only one that can test an aggregate such as a count.
Joins
- A join matches rows from two tables on a shared column — usually a foreign key to a primary key
- INNER JOIN keeps only rows matching on both sides; unmatched rows vanish silently
- LEFT JOIN keeps every row of the left table, filling the rest with nulls
- Use a LEFT JOIN when the unmatched rows are part of the answer — a class with no students
- Omitting the ON condition gives every possible pairing: a Cartesian product
💳 Transactions
Why they exist
- A transaction is a group of statements treated as one indivisible unit — all, or none
- COMMIT makes them permanent; ROLLBACK undoes them all
- A bank transfer is two updates; a failure between them would destroy money
- Uncommitted changes are invisible to others, because they may still be rolled back
📝 Exam-style questions
Tables are Member(MemberID, name, joined) and Loan(LoanID, MemberID, bookTitle, returned). Construct SQL listing every member's name with how many loans they have, including members with none, most loans first.
🔒 Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
A clerk runs UPDATE Member SET name = 'Smith' with no WHERE clause. Explain what happens and why nothing warns them.
🔒 Model answer plan
See the mark-by-mark plan — for / against / judgement, with marking guidance — in study mode.
✅ Quick check
Cover the answers.
Which clause filters GROUPS rather than rows? HAVING. WHERE filters rows before grouping and cannot test an aggregate.
What does omitting the ON condition from a join produce? A Cartesian product — every row paired with every row. It succeeds, which is what makes it dangerous.
DROP TABLE or DELETE FROM — which leaves the table standing? DELETE FROM. DROP removes the structure as well.
Why is COUNT(*) wrong after a LEFT JOIN? It counts the null row an unmatched record produces, giving 1 instead of 0.
What makes a bank transfer safe? Wrapping both updates in a transaction: all or nothing, so money cannot vanish between them.
Exam tips
- Write the clauses in order: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY.
- Qualify every column with its table in a join — an ambiguous name is rejected.
- LEFT JOIN when the unmatched rows are part of the answer; COUNT a column, not a star.
- A condition on a count needs HAVING, never WHERE.
- UPDATE and DELETE without WHERE affect EVERY row and report success. SELECT first.
- A transaction is all-or-nothing — that is what makes a two-step transfer safe.