Skip to main content

Quiz Calibration

Attempt the quiz before reading this page. Preserve the original answer, correction and help received. These explanations calibrate retrieval; successful recall alone does not complete the module or its practical evidence gates. For context such as shared schemas, use the question page.

Question 1: Relational model vs SQL table​

State two concrete ways an SQL table differs from a Codd relation, and for each give a one-sentence practical consequence.

Calibration: (1) SQL tables are multisets of tuples, not sets -- so SELECT can return duplicate rows and you must add DISTINCT or GROUP BY to recover set semantics. (2) SQL columns carry NULL -- "three-valued logic" -- so WHERE col = NULL is never true, you must use IS NULL.

Question 2: Keys​

Given employee(id, email, tax_id, name, dept_id), suppose id, email, and tax_id are each individually unique and non-null, and no other candidate keys exist. Identify all superkeys, candidate keys, and a reasonable choice of primary key; justify.

Calibration: Superkeys include any set containing a candidate key: {id}, {email}, {tax_id}, {id, email}, {id, name}, {id, email, tax_id, name, dept_id}, etc. Candidate keys are the minimal superkeys: {id}, {email}, {tax_id}. Primary key: id -- a surrogate that is stable (email can change; tax_id is PII).

Question 3 (HANDS-ON SQL): Top-N-per-group​

Write a single PostgreSQL query that returns, for each region, up to three customers with orders and the highest total order spend in calendar year 2025 (UTC; ties broken by customer id), with columns region, name, total_dollars, and rank.

Calibration: ```sql WITH spend AS ( SELECT c.id, c.region, c.name, SUM(o.total_cents) / 100.0 AS total_dollars FROM customer c JOIN orders o ON o.customer_id = c.id WHERE o.placed_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00' AND o.placed_at < TIMESTAMPTZ '2026-01-01 00:00:00+00' GROUP BY c.id, c.region, c.name ), ranked AS ( SELECT id, region, name, total_dollars, ROW_NUMBER() OVER ( PARTITION BY region ORDER BY total_dollars DESC, id ) AS rank FROM spend ) SELECT region, name, total_dollars, rank FROM ranked WHERE rank <= 3 ORDER BY region, rank;


Group by customer identity so equal names remain distinct. `ROW_NUMBER` with the id tie-break gives at most three rows; `RANK` can return more when ties cross the boundary. If the requirement includes every tied customer, use a different explicit tie policy. Test duplicate names, equal spends, a region with two customers and timestamps at both UTC boundaries. See [PostgreSQL window functions](https://www.postgresql.org/docs/current/functions-window.html).

## Question 4 (HANDS-ON SQL): Anti-join

Write a query returning customers who have never placed an order, in two different ways (`NOT EXISTS` and `LEFT JOIN ... IS NULL`). State which you prefer and why.

**Calibration:** ```sql
-- NOT EXISTS
SELECT c.id, c.name
FROM customer c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- LEFT JOIN ... IS NULL
SELECT c.id, c.name
FROM customer c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Preference: NOT EXISTS -- clearer intent, the planner can short-circuit, no risk of row multiplication if the left side's predicate later changes.

Question 5: Normalization​

Given enrollment(student_id, student_name, course_code, course_title, grade) and FDs student_id -> student_name, course_code -> course_title, decompose to 3NF. Identify which anomalies existed and which go away.

Calibration: Decompose into student(student_id PK, student_name), course(course_code PK, course_title), enrollment(student_id FK, course_code FK, grade, PRIMARY KEY(student_id, course_code)). Anomalies eliminated: update anomaly (renaming a course had to touch every enrolled row), insertion anomaly (cannot insert a course with no students), deletion anomaly (deleting last student for a course loses the course).

Question 6: Constraints​

Critique this DDL and propose improvements:

CREATE TABLE account(
    id INT,
    email TEXT,
    balance NUMERIC
);

Calibration: Missing PRIMARY KEY (add id BIGSERIAL PRIMARY KEY). email should be NOT NULL UNIQUE. balance should be NOT NULL with a domain constraint (e.g., CHECK (balance >= 0) if business rule allows). NUMERIC is an exact decimal type, not binary floating point. Choose an explicit precision/scale and rounding policy, or integer minor units with currency/overflow rules; neither representation chooses those business rules for you.

Question 7 (HANDS-ON EXPLAIN): Interpret a plan​

Seq Scan on orders  (cost=0.00..234_567.89 rows=50 width=...)
                    (actual time=812..820 rows=45 loops=1)
  Filter: (customer_id = 42)
  Rows Removed by Filter: 9_999_955

Explain what is happening, propose a testable improvement, and state what this plan alone cannot establish.

Calibration: The scan examines about ten million rows and filters to 45. The row estimate is close for this case, but the plan does not prove that no suitable index exists, that all statistics are healthy or that an index will meet a fixed latency target. Inspect indexes, predicates, selectivity and buffers. Test an index on orders(customer_id) on a representative disposable copy, compare EXPLAIN (ANALYZE, BUFFERS) under recorded conditions, and account for write/storage cost. No sub-millisecond result is guaranteed.

Question 8: Online migration​

You need to add a NOT NULL column country to a 200 M-row customer table. Describe the phases of the online migration and the state of the application at each phase.

Calibration: Expand with a nullable column; teach all active writers to supply the intended country value. Backfill bounded batches from an authoritative mapping, retaining unknowns for repair. PostgreSQL has no direct UPDATE ... LIMIT; one illustrative batch, assuming a unique country_backfill(customer_id, country) staging mapping, is:

WITH batch AS (
    SELECT c.id, b.country
    FROM customer c
    JOIN country_backfill b ON b.customer_id = c.id
    WHERE c.country IS NULL AND b.country IS NOT NULL
    ORDER BY c.id
    LIMIT 10000
    FOR UPDATE OF c SKIP LOCKED
)
UPDATE customer c SET country = batch.country
FROM batch WHERE c.id = batch.id AND c.country IS NULL;

Commit each batch and verify coverage; zero updated rows can mean missing mapping or locked rows, not completion. After all writers comply, add and validate CHECK (country IS NOT NULL), then set NOT NULL in a separately reviewed change. Plan lock acquisition, timeouts, rollback compatibility and data verification; metadata-only work can still wait for a strong lock. No old country column was specified, so inventing a dual-write/old-column removal step is unjustified. See PostgreSQL UPDATE and batching.

Question 9: Isolation​

Describe a "lost update" scenario at READ COMMITTED and the two standard fixes.

Calibration: Session A reads balance = 100; Session B reads balance = 100, subtracts 20, writes 80. Session A, unaware, subtracts 30 and writes 70 -- B's update is lost. Fixes: (i) single-statement atomic update UPDATE account SET balance = balance - 30 WHERE id = ? AND balance >= 30; (ii) SELECT ... FOR UPDATE inside a transaction to lock the row; (iii) escalate to REPEATABLE READ / SERIALIZABLE and retry on serialization errors.

Question 10: Views vs materialized views​

When is a materialized view the right answer? When is it the wrong answer?

Calibration: Right answer: expensive aggregations over largely-append-only data whose readers tolerate staleness bounded by the refresh cadence (e.g., daily dashboards). Wrong answer: up-to-the-second correctness (use a plain view or an index), write-heavy data where staleness is unacceptable, or when the refresh itself cannot finish within the acceptable window.