Quiz

-- 10 questions. Pass: >= 7. Answers + explanations at the bottom.

-- ===== Q1 =====
-- Which data type should you use for money in Postgres?
-- A) FLOAT
-- B) DOUBLE PRECISION
-- C) NUMERIC(p, s)
-- D) MONEY

-- ===== Q2 =====
-- What is the difference between LEFT JOIN and INNER JOIN?
-- A) LEFT keeps left-side rows even when no match; INNER keeps only matches
-- B) LEFT is faster
-- C) They produce identical results when there is a foreign key
-- D) INNER keeps NULLs; LEFT excludes them

-- ===== Q3 =====
-- Why prefer TIMESTAMPTZ over TIMESTAMP?
-- A) TIMESTAMPTZ stores the offset in the row
-- B) TIMESTAMPTZ normalises to UTC and converts on read
-- C) TIMESTAMP cannot store dates
-- D) No difference; alias

-- ===== Q4 =====
-- What does ANALYZE do?
-- A) Compresses tables
-- B) Refreshes statistics for the query planner
-- C) Same as VACUUM FULL
-- D) Indexes the table

-- ===== Q5 =====
-- A B-tree index on (a, b, c) accelerates which queries?
-- A) WHERE a = ?
-- B) WHERE a = ? AND b = ?
-- C) WHERE b = ?
-- D) WHERE a = ? AND b = ? AND c = ?

-- ===== Q6 =====
-- What does WAL stand for, and what does it do?
-- A) Write-Ahead Log — durability + crash recovery
-- B) Wait-And-Lock — concurrency control
-- C) Worker Affinity List — connection pooling
-- D) Without Auto Lock — explicit locking mode

-- ===== Q7 =====
-- How do you safely add a NOT NULL column to a busy production table?
-- A) ALTER TABLE ... ADD COLUMN x INT NOT NULL DEFAULT 0
-- B) Add as nullable, backfill in batches, then SET NOT NULL
-- C) Use UPDATE ... DEFAULT 0
-- D) Either A or B, depending on size

-- ===== Q8 =====
-- What is JSONB compared to JSON?
-- A) JSONB is text; JSON is binary
-- B) JSONB is binary + indexable; JSON is text + preserves whitespace
-- C) Same; alias
-- D) JSON allows duplicate keys; JSONB doesn't

-- ===== Q9 =====
-- How do you UPSERT in Postgres?
-- A) INSERT ... ON CONFLICT (key) DO UPDATE SET ...
-- B) MERGE INTO ... USING ... WHEN MATCHED ...
-- C) Both A and B (B requires PG 15+)
-- D) INSERT OR UPDATE

-- ===== Q10 =====
-- What is a CTE?
-- A) Common Table Expression — a named subquery in WITH
-- B) Cached Table Entry — cache hint
-- C) Composite Type Element — column type
-- D) Constraint Table Extension — FK helper

-- ===== Answers =====
-- 1. C — NUMERIC for exact decimal money. MONEY exists but has locale issues.
-- 2. A — LEFT keeps unmatched left-side rows with NULLs on the right.
-- 3. B — TIMESTAMPTZ converts to UTC on store; converts back on read using session tz.
-- 4. B — ANALYZE updates planner stats; VACUUM reclaims space.
-- 5. A, B, D — composite index works on left-anchored prefixes; not bare 'b ='.
-- 6. A — WAL is Postgres's write-ahead log; basis for durability + replication.
-- 7. B — adding NOT NULL with DEFAULT rewrites the table on PG < 11; backfill is safer.
-- 8. B — JSONB is decomposed binary, indexable via GIN; JSON is text + preserves whitespace + order.
-- 9. C — both ON CONFLICT (since 9.5) and MERGE (since 15) work.
-- 10. A — Common Table Expression; PG 12+ inlines them by default.

-- ===== Patterns to internalise =====
-- - NUMERIC for money; TIMESTAMPTZ for time; JSONB > JSON; UUID PKs
-- - Composite indexes match leftmost-prefix queries
-- - ANALYZE after big data changes
-- - ON CONFLICT for upserts; MERGE when complex matching

-- 3 questions per lesson.