R08 · Week 8 Recall & Drill

Assessment scope

Attempt the recall prompts before reading the worked drill and answer explanations. The worked examples below restore the combined teaching task, with explicit contracts and checks; a local model is distinguished from a reproduced external service. The minute allocations are adjustable practice suggestions, not evidence of learning speed.

Sessions covered

• S036 — SQL Basics — SELECT, WHERE, ORDER BY, LIMIT
• S037 — Joins — INNER, LEFT, RIGHT, FULL, Anti-Join
• S038 — Aggregations — GROUP BY, HAVING, Subqueries
• S039 — Window Functions — the Game-Changer
• S040 — CTEs & Recursive Queries

By the end of this revision you can

1. Use the logical query-order mnemonic while distinguishing dialect-specific alias visibility from physical execution order.

2. Use three-valued null logic correctly, including why an equality comparison against null matches nothing.

3. Predict row multiplication before running a join, and write an anti-join two different ways.

4. State the difference between counting rows and counting a column, and explain what an aggregate over zero matching rows returns.

5. Choose between row-number, rank, and dense-rank based purely on how ties must behave, and write top-N-per-group correctly.

6. Write a recursive CTE with a termination argument, explicit cycle policy and a depth limit whose truncation is reported.

90-min structure

Treat this as an adjustable practice budget: 5 minutes to recall the five subjects, 30 for the blank-page questions, 30 for the worked drill, 15 to compare answers and test a counterexample, and 10 to record a gap and plan an unseen retest. The solutions and transfer exercises can take longer; the goal is correct reasoning, not racing a timer.

Blank-page reconstruction · 30 min

S036 · SQL Basics

1. Write the logical execution order of a query, all eight stages, in order.
2. Explain why comparing a column to null with an equality operator returns no rows, and give the correct form.
3. Give two concrete production reasons to avoid selecting every column.

S037 · Joins

1. Build the decision table: inner, left, full outer, anti — one business question each.
2. Explain row multiplication in one sentence, with a concrete example of how it inflates a sum.
3. Write an anti-join both ways, and say when you would prefer each.

S038 · Aggregations

1. Explain the difference between filtering before grouping and filtering after grouping, and give a condition only the latter can express.
2. Say what an aggregate returns when no rows match, and how to make it return zero instead.
3. Describe the average-of-averages bug with a concrete example.

S039 · Window Functions

1. State the fundamental difference between grouping and windowing, in terms of rows in versus rows out.
2. Given a salary list containing a tie, write out what row-number, rank, and dense-rank each return.
3. Explain why you cannot filter on a window function's result in the same select, and what the standard workaround is.

S040 · CTEs & Recursive Queries

1. Name the four required parts of a recursive CTE.
2. Compare UNION ALL and UNION in recursive queries, including their differing multiplicity and cycle behavior.
3. Name three real hierarchies other than an org chart that fit a recursive CTE.

Hands-on drill · 30 min

Task: build one small database and reproduce every trap from this week, so you have seen the wrong number rather than been warned about it.

Step 1 — the schema and data (5 min)

-- setup.sql
-- Run only in a fresh in-memory database.
PRAGMA foreign_keys=ON;
 
CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  tier TEXT                      -- deliberately nullable
);
 
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  placed_on   TEXT NOT NULL,
  amount      REAL                -- deliberately nullable
);
 
CREATE TABLE order_items (
  id       INTEGER PRIMARY KEY,
  order_id INTEGER NOT NULL REFERENCES orders(id),
  sku      TEXT NOT NULL
);
 
INSERT INTO customers VALUES
  (1,'Ada','gold'), (2,'Bob','gold'), (3,'Cleo',NULL), (4,'Dan','silver');
 
INSERT INTO orders VALUES
  (10,1,'2024-01-05',100.0),
  (11,1,'2024-01-20',200.0),
  (12,2,'2024-02-02',50.0),
  (13,2,'2024-02-11',NULL),      -- amount unknown
  (14,4,'2024-03-01',75.0);
  -- Cleo has no orders at all.
 
INSERT INTO order_items VALUES
  (100,10,'sku-a'), (101,10,'sku-b'), (102,10,'sku-c'),
  (103,11,'sku-a'),
  (104,12,'sku-d'), (105,12,'sku-e');

Create one Python sqlite3.connect(":memory:") connection, run setup.sql once, and keep that same connection for all later fixture queries. Execute statements individually and fetch their result rows; executescript is useful for setup but does not expose each SELECT result. No persistent database is created.

Step 2 — reproduce the traps (12 min)

Predict every answer on paper first, then run it.

-- traps.sql
 
SELECT '--- 1. null equality ---';
SELECT count(*) AS matched_with_equals FROM customers WHERE tier = NULL;
SELECT count(*) AS matched_with_is_null FROM customers WHERE tier IS NULL;
 
SELECT '--- 2. count(*) vs count(col) after LEFT JOIN ---';
SELECT c.name,
       count(*)    AS wrong_order_count,
       count(o.id) AS right_order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY c.id;
 
SELECT '--- 3. row multiplication inflates SUM ---';
SELECT sum(amount) AS honest_total FROM orders;
SELECT sum(o.amount) AS inflated_total
FROM orders o
JOIN order_items i ON i.order_id = o.id;
 
SELECT '--- 4. aggregates skip nulls ---';
SELECT count(*)      AS rows_total,
       count(amount) AS rows_with_amount,
       avg(amount)   AS avg_skipping_nulls,
       avg(coalesce(amount, 0)) AS avg_treating_null_as_zero
FROM orders WHERE customer_id = 2;
 
SELECT '--- 5. empty aggregate ---';
SELECT sum(amount) AS empty_sum,
       coalesce(sum(amount), 0) AS empty_sum_fixed
FROM orders WHERE customer_id = 3;

Continue on the populated connection from Step 1; opening a new in-memory connection here would create a different, empty database.

Expected outcome, and check each against your prediction: the equality comparison matches zero rows while the null test matches one. Cleo shows a wrong count of 1 and a right count of 0. The inflated total is strictly larger than the honest total, because the order with three items is counted three times — this is the bug that silently corrupts revenue dashboards. Bob's row count exceeds his count of amounts, so the two averages differ. And the empty sum is null, not zero, until you coalesce it.

Step 3 — windows and top-N-per-group (8 min)

-- windows.sql
 
-- Ranking family on a tie. Note gold appears twice.
SELECT name, tier,
       row_number() OVER (ORDER BY tier)  AS rn,
       rank()       OVER (ORDER BY tier)  AS rnk,
       dense_rank() OVER (ORDER BY tier)  AS drnk
FROM customers;
 
-- Top order per customer: window in a CTE, filter outside it.
WITH ranked AS (
  SELECT o.id, o.customer_id, o.amount,
         row_number() OVER (
           PARTITION BY o.customer_id
           ORDER BY o.amount DESC, o.id ASC
         ) AS rn
  FROM orders o
)
SELECT c.name, r.id AS order_id, r.amount
FROM ranked r
JOIN customers c ON c.id = r.customer_id
WHERE r.rn = 1
ORDER BY c.name;
 
-- Running total: frame stated explicitly, not left to the default.
SELECT id, placed_on, amount,
       sum(coalesce(amount, 0)) OVER (
         ORDER BY placed_on, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM orders
ORDER BY placed_on, id;

Expected outcome: on the tie, row-number assigns distinct consecutive integers, rank leaves a gap after the tied pair, and dense-rank does not. The first query has no outer ORDER BY, so compare each row’s computed ranks rather than assuming displayed row order. In SQLite ascending order puts Cleo’s NULL tier first; Ada and Bob are gold peers with ranks 2,2, and Dan has rank 4 versus dense rank 3. The tied row-number identities are unspecified. The top-order query returns exactly one row per customer that has orders — and note Cleo is absent, which is correct for an inner join and would be a bug if the question were "every customer". The running total is nondecreasing for this nonnegative fixture. The explicit frame and unique ordering make row-by-row accumulation deterministic; negative refunds could decrease the total.

Step 4 — recursive CTE with a guard (5 min)

-- recursive.sql
WITH RECURSIVE mondays(d, n) AS (
    SELECT date('2026-01-05'), 1               -- base case: first Monday of 2026
  UNION ALL
    SELECT date(d, '+7 days'), n + 1           -- recursive step
    FROM mondays
    WHERE date(d, '+7 days') <= '2026-12-31' AND n < 100         -- termination + depth guard
)
SELECT count(*) AS monday_count, min(d) AS first, max(d) AS last FROM mondays;

Expected result: 52, first 2026-01-05, last 2026-12-28. Testing the current date instead of the next date would incorrectly emit 2027-01-04. A depth guard bounds this chain but can silently truncate a hierarchy and does not by itself prevent exponential branching. For cyclic graphs, choose explicit path-based cycle detection or deduplicate an appropriate state; report truncation rather than presenting a partial hierarchy as complete.

Query reasoning and corrected answers

A useful logical-order mnemonic is FROM/JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. It is not the physical execution plan or a universal alias-visibility rule: SQLite allows some aliases in WHERE as an extension. Use a subquery when portable alias reuse matters. SQL NULL comparisons generally yield UNKNOWN, and WHERE keeps only TRUE; use IS NULL for missingness. Avoid SELECT * when unneeded large/sensitive columns or unstable result schemas matter. Keyset pagination needs a matching index, stable unique order and explicit snapshot/update policy; it is not guaranteed fast at any depth for arbitrary predicates.

For the fixture, honest order sum is 425, joined-item sum is 600: order 10 contributes 300, order 11 contributes 200, and order 12 contributes 100. Orders 13 and 14 have no item matches and disappear, so a join can inflate some contributions while removing others. Equal row counts before/after a join do not by themselves prove correctness. Bob has row count 2, non-NULL amount count 1, AVG(amount)=50 and AVG(COALESCE(amount,0))=25. Neither mean is automatically right: missing does not mean zero unless the domain contract says so. COUNT(*) and COUNT(column) return zero over an empty input; SQLite SUM/AVG/MIN/MAX return NULL, while SQLite total() is a documented exception returning 0.0. “Every aggregate except count skips NULL” is not a universal rule for custom or JSON aggregates.

A mean of means weights groups equally. For [0] and [10,10,10], the group means average to 5 but the pooled mean is 7.5. Combine sums and non-NULL counts when those match the desired population. WHERE filters input rows; HAVING can filter COUNT(*)>1 after grouping. A window attaches a result to each retained row rather than collapsing the partition.

For salaries 100,90,90,80 in descending salary order, row_number is 1,2,3,4 (tie identity unspecified), rank is 1,2,2,4, and dense_rank is 1,2,2,3. Adding employee ID to a rank window eliminates salary peers, so use the tie-breaker only in row_number when one deterministic winner is wanted. SQLite requires a wrapper to filter a window result; other engines may offer QUALIFY. With ORDER BY, SQLite's default RANGE frame includes peers; explicit ROWS and a unique order make each running-total step unambiguous.

Inner join asks for matching pairs, left join preserves each left row, full outer join preserves unmatched rows on both sides, and anti-join finds no match. These two anti-joins both return Cleo, because orders.id cannot be NULL:

SELECT c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id=c.id);
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id=c.id
WHERE o.id IS NULL;

Testing an actually nullable right-side attribute would not reliably distinguish an unmatched row. A nullable NOT IN subquery has another trap: NULL can make every comparison UNKNOWN. Place a right-side restriction in ON when excluded matches should still leave the customer as an unmatched row. Not every right-side WHERE condition collapses the join: WHERE o.id IS NULL is precisely the anti-join counterexample.

A recursive CTE needs a seed, union operator, recursive member and a termination argument. UNION ALL preserves multiplicities; UNION deduplicates entire result rows and is sometimes appropriate for reachability. Adding a changing depth column means repeated vertices may no longer deduplicate, so do not call UNION a universal cycle fix. Dependencies, category trees and bills of materials are other applications. CTE materialization/inlining is engine- and plan-dependent; naming a subquery does not make it a free cached result.

Transfer exercise: add a second order with the same amount/date for Ada. Use row_number ordered by amount descending then order ID ascending to select one winner. Use rank ordered only by amount to return all top ties. Explain which output cardinality your business question needs before choosing the function.

Cycle policy and visible truncation: executable transfer solution

The calendar drill is a chain, so it cannot demonstrate a cycle policy by itself. This separate in-memory graph contains 1→2→1 and a longer acyclic branch 2→3→4. IDs are integers, so comma-delimited path membership is unambiguous. UNION ALL retains distinct simple paths; instr prevents revisiting a vertex on the current path. The cap still truncates a valid branch, and the output says so rather than pretending the traversal is complete.

WITH RECURSIVE
  edges(src,dst) AS (VALUES (1,2),(2,1),(2,3),(3,4)),
  walk(id,path,depth) AS (
    SELECT 1, ',1,', 0
    UNION ALL
    SELECT e.dst, w.path || e.dst || ',', w.depth+1
    FROM walk w JOIN edges e ON e.src=w.id
    WHERE w.depth < 2
      AND instr(w.path, ',' || e.dst || ',')=0
  )
SELECT w.id,w.depth,
       EXISTS(SELECT 1 FROM edges e WHERE e.src=w.id
              AND instr(w.path, ',' || e.dst || ',')>0) AS cycle_edge_skipped,
       (w.depth=2 AND EXISTS(
          SELECT 1 FROM edges e WHERE e.src=w.id
          AND instr(w.path, ',' || e.dst || ',')=0
       )) AS truncated
FROM walk w ORDER BY w.depth,w.id,w.path;

Expected rows (id,depth,cycle_edge_skipped,truncated) are (1,0,0,0), (2,1,1,0), (3,2,0,1). Vertex 2 reports the skipped edge back to 1; vertex 3 reports that 4 was not visited because of the cap. Increasing both cap literals from 2 to 3 includes vertex 4 with no truncation. Path enumeration can still be exponential in a DAG; a depth cap is not a global row/memory budget. For reachability alone, omit depth/path from the recursive state and use UNION over vertex ID to deduplicate visits instead.

Transfer solutions for ties, joins and grouping

The setup fixture stores amounts as REAL for a tiny teaching example; real money commonly needs an exact scaled-integer/decimal representation and an explicit rounding policy. To avoid fanout when you only need orders having items, use EXISTS rather than joining item rows into the sum. To include every order, aggregate at order grain without filtering to item-bearing orders. SUM(DISTINCT amount) is not a repair: separate orders may legitimately share an amount.

-- Run after setup.sql on the same in-memory connection.
SELECT sum(o.amount) FROM orders o
WHERE EXISTS (SELECT 1 FROM order_items i WHERE i.order_id=o.id);
 
-- Every customer, restricting qualifying orders in ON, not WHERE.
SELECT c.name, coalesce(sum(o.amount),0) AS spend
FROM customers c LEFT JOIN orders o
  ON o.customer_id=c.id AND o.placed_on >= '2024-02-01'
GROUP BY c.id,c.name ORDER BY c.id;
 
-- A condition on grouped counts belongs in HAVING.
SELECT customer_id, count(*) FROM orders
GROUP BY customer_id HAVING count(*)>1 ORDER BY customer_id;
 
-- Tie fixture local to this query; no mutation of setup data.
WITH extra_orders AS (
  SELECT id,customer_id,amount FROM orders
  UNION ALL SELECT 15,1,200.0
), ranked AS (
  SELECT *,
    row_number() OVER (PARTITION BY customer_id ORDER BY amount DESC,id) AS rn,
    rank() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rnk
  FROM extra_orders
)
SELECT id,rn,rnk FROM ranked WHERE customer_id=1 ORDER BY rn;

The EXISTS total is 350, counting orders 10,11,12 once each (not 600); the every-customer spend rows are Ada=0, Bob=50, Cleo=0, Dan=75; HAVING returns (1,2),(2,2). In the local tie fixture the ranking rows are (11,1,1),(15,2,1),(10,3,3). Filtering rn≤1 picks one winner; rnk≤1 keeps both top ties. More generally rn≤N means at most N rows, rank≤N includes ties at the first N rank positions, and dense_rank≤N means N distinct value levels. Prefer NOT EXISTS for a pure absence question; a left anti-join is also correct when its tested column is nonnullable for matched rows.

For the original customer-signup extension, a predicate on customers.joined_on belongs in WHERE, while an order-date restriction belongs in ON if customers without qualifying orders must survive. The current schema intentionally has no signup column, so do not run a query naming it without extending the schema first. Right outer join reverses the preserved side; full outer join preserves unmatched rows on both sides. In a finite inner equijoin, key multiplicities m and n produce m×n matches and the entire output is bounded by the Cartesian-product size—not literally unbounded for fixed inputs.

Answer constraints and corrections

COUNT(*) counts the manufactured unmatched row while COUNT(order_id) does not. Ranking outputs are 1,2,3,4; 1,2,2,4; and 1,2,2,3 under descending salaries. Alias visibility, NULL semantics and default window frames must be checked for the named SQL dialect; the simplified logical-order mnemonic is not a universal parser rule.

Gap analysis + next week preview · 10 min

Score each original recall question separately: explain unaided; explain with a hint; or not yet. A fluent verbal answer is not a successful implementation. Choose one failed concept, write a counterexample that distinguishes the mistaken rule from the correct one, and schedule an unseen retest. Keep successes as well as failures in the log so the plan does not become a list of increasingly familiar problems.

For the drill, require an explicit input contract, a correctness argument or domain assumption, one ordinary case, one boundary case and an independent expected result. A chart, compile success or passing sample alone does not meet that rubric. Use the stated input contracts and named runtime when interpreting a result; finite tests and timing samples are not universal correctness or performance guarantees.

Next week (S041–S045) moves under the query language into how the database actually works: index structures and when to add one; transactions, ACID, isolation levels and multi-version concurrency; query planning with execution plans and tuning; the NoSQL landscape across key-value, document, column and graph stores; and dimensional data modelling. Everything you just wrote will get faster or slower depending on the index choices in S041.

Corrective source notes

• SELECT — A left join manufactures NULL-side rows for unmatched left rows. Source retrieved 2026-10-07; passage and applicability reviewed 2026-10-07.
• sqlite-aggregate official reference — SQLite sum/total differ on empty non-NULL input; sums ignore NULL. Source retrieved 2026-10-07; passage and applicability reviewed 2026-10-07.
• Window Functions — row_number assigns consecutive integers within the window order, not final output order. The default RANGE frame includes peers; explicit ROWS and unique ordering distinguish row accumulation. Source retrieved 2026-10-07; passage and applicability reviewed 2026-10-07.
• The WITH Clause — Recursive UNION deduplicates entire rows; UNION ALL preserves them, so changing depth can defeat vertex deduplication. Source retrieved 2026-10-07; passage and applicability reviewed 2026-10-07.

The cited passages support these named claims. The algebra, counterexamples and executable fixtures supply the lesson-specific reasoning; finite checks are not universal benchmarks.