Relational Database Implementation and SQL
Domain 3 - Relational Database Implementation and SQL
Expect questions on constraints, index behaviour, join semantics, window functions, transactions and why a query is slow.
Schema and integrity
DDL defines tables; constraints enforce meaning - PRIMARY KEY (unique and not null), FOREIGN KEY (referential integrity), UNIQUE, CHECK, NOT NULL, DEFAULT. Choose natural versus surrogate keys deliberately: surrogates are stable, natural keys carry business meaning. Indexes (typically B-tree) accelerate equality and range predicates plus ORDER BY, at the cost of write amplification and storage. A composite index on (region, order_date, customer_id) serves filters on region, then region+order_date, then all three - the left-prefix rule - but not order_date alone.
Query mechanics
INNER JOIN drops unmatched rows; LEFT JOIN preserves the left side and yields NULLs on the right. Window functions compute across partitions without collapsing rows: ROW_NUMBER, RANK, SUM() OVER. CTEs make multi-step logic readable and reusable. WHERE filters rows before grouping; HAVING filters after aggregation. Transactions provide ACID; isolation levels trade consistency for concurrency (read committed, repeatable read, serializable), and deadlocks arise from conflicting lock ordering.
Worked example
"Top three customers by revenue per region." A single GROUP BY region, customer with ORDER BY revenue DESC LIMIT 3 returns three rows overall, not three per region. Correct approach: aggregate revenue per region and customer in a CTE, then ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC), filter rn <= 3; use RANK or DENSE_RANK if tied customers must be kept together. If the plan shows a sequential scan of the fact table, suspect a missing index on (region, customer_id) or a predicate wrapped in a function that blocks index use.
Traps
- NOT IN against a subquery containing NULL returns no rows; use NOT EXISTS.
- COUNT(*) counts rows while COUNT(column) skips NULLs - a classic reconciliation mismatch.
- Implicit type conversion (comparing a VARCHAR key to a number) silently disables the index.
- A one-to-many join to an extra table multiplies SUM() results.
- Selecting non-aggregated columns that are absent from GROUP BY.