DEVPREP CODEX · #24

SQL Interview Questions

171 curated questions graded from Foundations (Easy) to Practical Patterns (Medium) and Internals & Architecture (Hard).

TOTAL QUESTIONS

171

THEORY QUESTIONS

150

IMPLEMENTATION FOLIOS

FREE QUESTIONS

5 / Level

Easy Level·57 Questions Total

Foundations & Core Concepts

Open Full Easy List

Q1What is SQL and how does it differ from NoSQL?

SQL (Structured Query Language): Relational database management system (RDBMS) language. Uses structured schemas, tabular relations (tables/columns/rows), and enforces ACID properties.
NoSQL (Not Only SQL): Non-relational database system. Uses flexible schemas (document, key-value, wide-column, graph), scales horizontally, and prioritizes BASE properties (Basically Available, Soft state, Eventual consistency).

Q2Categorize standard SQL commands into their sub-languages. What are DDL, DML, TCL, and DCL?

DDL (Data Definition Language): Defines/modifies schema structures. Commands: CREATE, ALTER, DROP, TRUNCATE.
DML (Data Manipulation Language): Manipulates and queries data. Commands: SELECT, INSERT, UPDATE, DELETE.
TCL (Transaction Control Language): Manages database transactions. Commands: COMMIT, ROLLBACK, SAVEPOINT.
DCL (Data Control Language): Manages access permissions. Commands: GRANT, REVOKE.

Q3What is a Primary Key and how does it differ from a Unique Key?

Primary Key (PK): Uniquely identifies a record in a table. Does not allow NULLs. Only one PK allowed per table. Creates a clustered index by default.
Unique Key (UK): Ensures all values in a column are distinct. Allows NULL values (single or multiple depending on SQL dialect). Multiple UKs allowed per table. Creates a non-clustered index by default.

Q4What is a Foreign Key, and how does it maintain referential integrity?

Foreign Key (FK): A column or group of columns in one table that references the PK/UK of another table.
Referential Integrity: Ensures relationships between tables remain consistent. Prevents orphaned rows by restricting invalid inserts, and handles deletes/updates via rules like ON DELETE CASCADE or ON DELETE SET NULL.

Q5What are the different types of Joins in SQL?

INNER JOIN: Returns records with matching values in both tables.
LEFT (OUTER) JOIN: Returns all records from the left table, and matching records from the right; unmatched right side returns NULL.
RIGHT (OUTER) JOIN: Returns all records from the right table, and matching records from the left; unmatched left side returns NULL.
FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table; unmatched rows return NULL.
CROSS JOIN: Returns the Cartesian product of both tables (every combination of rows).

52 More Easy Questions Locked

Unlock the complete SQL Easy question bank

Get instant access to all 57 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Medium Level·57 Questions Total

Mechanisms & Practical Patterns

Open Full Medium List

Q1Explain database normalization and its key stages (1NF, 2NF, 3NF, BCNF).

Normalization: Organizing data in a database to reduce redundancy and improve data integrity.
1NF (First Normal Form): Atomic values only; no repeating groups or arrays. Columns must contain single values.
2NF (Second Normal Form): Must be in 1NF, and all non-key columns must be fully functionally dependent on the primary key (no partial dependencies on composite keys).
3NF (Third Normal Form): Must be in 2NF, and all non-key columns must have no transitive dependencies on the primary key (no non-key column can depend on another non-key column).
BCNF (Boyce-Codd Normal Form): Stronger version of 3NF. For every functional dependency $X \rightarrow Y$, $X$ must be a super key.

Q2What is denormalization, and when is it preferred over normalization?

Denormalization: The intentional introduction of redundancy by combining tables or adding redundant columns.
Purpose: Boosts read performance in OLAP (Analytical) systems by eliminating complex, expensive multi-table joins.
Trade-off: Speeds up reads but slows down writes (inserts/updates/deletes) and increases storage requirements and risk of anomalies.

Q3Explain the difference between clustered and non-clustered indexes.

Clustered Index: Determines the physical sorting order of the actual table rows on disk. Only one clustered index can exist per table. Leaf nodes contain the actual data rows.
Non-Clustered Index: Contains a sorted list of indexed key values alongside pointers (row locators or primary key values) to the actual physical rows. Multiple non-clustered indexes can exist. Leaf nodes contain pointers rather than raw table data.

Q4What is a composite index? Explain the leftmost prefix rule.

Composite Index: An index constructed on two or more columns (e.g., INDEX (col1, col2, col3)).
Leftmost Prefix Rule: The query optimizer will only utilize the composite index if the filter criteria include the columns from left to right without gaps. A filter on col1 or col1 AND col2 uses the index, but a filter on col2 or col2 AND col3 alone cannot use it.

Q5What is the overhead of indexes on write operations?

Overhead: Every INSERT, UPDATE, or DELETE requires updating not only the base table but also all associated indexes to maintain sorted structures (B-Tree splits/re-balancing).
Mitigation: Avoid over-indexing tables; index only columns that are frequently used in WHERE, JOIN, ORDER BY, or GROUP BY clauses.

52 More Medium Questions Locked

Unlock the complete SQL Medium question bank

Get instant access to all 57 questions, in-depth model answers, code sandboxes, and all 27+ technologies for a single one-time payment.

Unlock All — One-time payment · Lifetime access

Hard Level·57 Questions Total

Internals & System Architecture

Open Full Hard List

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.

52 More Hard Questions Locked