Skip to main content

Module Quiz

Complete this quiz after finishing all concept and practice pages. Save your answers before opening separate quiz calibration. Mix of short-answer, written-argument, and hands-on SQL / EXPLAIN questions.

Schema used throughout the quiz:

CREATE TABLE customer(
    id           BIGSERIAL PRIMARY KEY,
    email        TEXT NOT NULL UNIQUE,
    name         TEXT NOT NULL,
    region       TEXT NOT NULL,
    signed_up_on DATE NOT NULL
);
CREATE TABLE orders(
    id          BIGSERIAL PRIMARY KEY,
    customer_id BIGINT NOT NULL REFERENCES customer(id),
    total_cents INT    NOT NULL CHECK (total_cents >= 0),
    placed_at   TIMESTAMPTZ NOT NULL
);

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.

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.

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.

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.

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.

Question 6: Constraints​

Critique this DDL and propose improvements:

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

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.

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.

Question 9: Isolation​

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

Question 10: Views vs materialized views​

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

Evidence Check​

Before calling yourself done with the module:

  • All 10 questions answered from memory, with the hands-on queries executable against a seeded PostgreSQL database.
  • A retained initial attempt and any actual correction; if correct initially, explain a deliberately faulty query and label it as a negative control.
  • A personal mistake log organized by cluster.

Progression boundary​

Quiz performance calibrates retrieval and reasoning. It does not replace runnable work, changed-case transfer, the module assessment or required semester evidence. Consult practice calibration for the additional independent cases.