SQL · HARD CODEX
SQL Interview Questions — Hard
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,
UPDATEcreates a new version of the row, andDELETEmarks the existing row as deleted (using hidden system metadata columns likexmin,xmaxin 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.
₹399 India / $9 International · One-time settlement · Zero subscription