Data Management and SQL
This domain tests the relational model and the ability to write a query whose result set is exactly what the question asks for, including NULL behaviour and join cardinality.
Relational foundations
Primary and foreign keys enforce entity and referential integrity. Normalisation: 1NF removes repeating groups so values are atomic; 2NF removes partial dependencies on part of a composite key; 3NF removes transitive dependencies on non-key columns. Indexes accelerate reads and cost storage plus write throughput.
Query semantics
- Logical evaluation order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.
- WHERE filters rows before aggregation; HAVING filters groups after it.
- NULL means unknown: NULL = NULL evaluates to unknown, so use IS NULL. NOT IN against a list containing NULL returns no rows.
- Joins: inner, left, right, full, cross, self. A left join with several matches on the right multiplies rows, the classic fan-out.
- Window functions compute across a partition without collapsing rows: ROW_NUMBER, RANK (leaves gaps), DENSE_RANK (no gaps), LAG/LEAD, and SUM() OVER (PARTITION BY ... ORDER BY ...).
- Transactions follow ACID; isolation levels are read uncommitted, read committed, repeatable read, serializable, with dirty read, non-repeatable read, and phantom read as the anomalies.
Worked example
Customers(id, name) and Orders(id, customer_id, total), where one customer can have several orders. SELECT c.name, COUNT(o.id) FROM Customers c LEFT JOIN Orders o ON o.customer_id = c.id GROUP BY c.name counts actual orders and returns 0 for customers with none. COUNT(*) would return 1 for them, because the unmatched row still exists. Adding a second one-to-many table to the same query doubles the totals; aggregate in subqueries first.
Traps
WHERE versus HAVING; COUNT(*) versus COUNT(column) versus COUNT(DISTINCT column); NULL inside NOT IN; fan-out double counting; and integer division silently truncating.