SQL · HARD CODEX

SQL Interview Questions — Hard

Q: Detail how Multi-Version Concurrency Control (MVCC) is implemented and how it avoids read-write blocking.

Mechanism: Instead of direct in-place updates, UPDATE creates a new version of the row, and DELETE marks the existing row as deleted (using hidden system metadata columns like xmin, xmax in PostgreSQL

Q: What is Write-Ahead Logging (WAL) / Redo Logging, and how does it guarantee durability (D) in ACID?

Problem: Writing modified data pages directly to random locations on disk for every transaction is extremely slow. WAL Principle: Before any data page is modified in-memory (buffer pool), the changes

Q: Contrast B-Tree, B+Tree, and LSM-Tree storage structures. When is each selected?

B-Tree: Internal nodes store both search keys and data records. Good for single-key lookups. B+Tree: Internal nodes store only search keys; all data records reside in leaf nodes, which are linked in a

Q: Detail Hash Joins, Merge Joins, and Nested Loop Joins. When does the optimizer choose each?

Nested Loop Join: For each row in the outer table, scan the inner table. Chosen for very small datasets, or when the inner table has a highly efficient index on the join column. Hash Join: Builds an i

Q: How does a Cost-Based Optimizer (CBO) evaluate query execution plans?

Mechanism: Parses the query and generates multiple logically equivalent algebraic execution trees. Costing Model: Estimates the execution "cost" (primarily physical disk I/O operations and CPU cycles)

Deep runtime internals, memory models, distributed design, concurrency failure modes, and architectural decisions.

50 Theory Questions7 Implementation Folios5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 50 questions

Q1Detail how Multi-Version Concurrency Control (MVCC) is implemented and how it avoids read-write blocking.

Mechanism: Instead of direct in-place updates, UPDATE creates a new version of the row, and DELETE marks the existing row as deleted (using hidden system metadata columns like xmin, xmax in PostgreSQL, or rollback pointers and undo logs in MySQL InnoDB).
Isolation: Transactions are assigned a monotonically increasing transaction ID (or logical timestamp). When reading, a transaction receives a snapshot of the database state consistent with its own start time, filtering out row versions created by uncommitted or newer transactions.
Benefits: Readers never block writers, and writers never block readers, maximizing concurrency in high-throughput systems.

Q2What is Write-Ahead Logging (WAL) / Redo Logging, and how does it guarantee durability (D) in ACID?

Problem: Writing modified data pages directly to random locations on disk for every transaction is extremely slow.
WAL Principle: Before any data page is modified in-memory (buffer pool), the changes are written sequentially to an append-only transaction log on disk (Redo Log/WAL) and flushed during COMMIT.
Crash Recovery: If a crash occurs, the database engine scans the WAL on startup. It performs a REDO phase to roll forward committed changes not yet flushed to data pages, followed by an UNDO phase to roll back transactions that were active but uncommitted during the crash.

Q3Contrast B-Tree, B+Tree, and LSM-Tree storage structures. When is each selected?

B-Tree: Internal nodes store both search keys and data records. Good for single-key lookups.
B+Tree: Internal nodes store only search keys; all data records reside in leaf nodes, which are linked in a sequential doubly-linked list. Excellent for range scans and sorting. (Selected by most traditional relational databases like MySQL InnoDB, PostgreSQL, SQL Server).
LSM-Tree (Log-Structured Merge-Tree): Appends writes to an in-memory buffer (MemTable) and periodically flushes them to sequential disk files (SSTables) which are merged via compaction. Highly optimized for massive write rates. (Selected by NoSQL engines like Cassandra, RocksDB, and some hybrid systems).

Q4Detail Hash Joins, Merge Joins, and Nested Loop Joins. When does the optimizer choose each?

Nested Loop Join: For each row in the outer table, scan the inner table. Chosen for very small datasets, or when the inner table has a highly efficient index on the join column.
Hash Join: Builds an in-memory hash table of the join key from the smaller (build) table, then scans the larger (probe) table to match keys. Chosen for large, unsorted tables when join conditions are equalities (=).
Merge Join: Sorts both tables by the join key (if not already sorted by an index) and scans them in parallel to merge matches. Chosen for very large tables when join keys are already sorted or when range conditions are used.

Q5How does a Cost-Based Optimizer (CBO) evaluate query execution plans?

Mechanism: Parses the query and generates multiple logically equivalent algebraic execution trees.
Costing Model: Estimates the execution "cost" (primarily physical disk I/O operations and CPU cycles) of each tree using database statistics (table row counts, page counts, index height, data distribution histograms). It selects the plan with the lowest overall estimated cost.

Unlock the remaining 52 SQL (Hard) questions

You've completed the 5 free sample questions. Get unrestricted lifetime access to every question, model answer, implementation challenge, and all 27+ technologies for a single payment.

Unlock Everything — Already Enrolled? Sign In

₹399 India / $9 International · One-time settlement · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing SQL

EASY LEVEL

Foundations & Core Concepts

MEDIUM LEVEL

Mechanisms & Practical Patterns