SQL · MEDIUM CODEX

SQL Interview Questions — Medium

Q: Explain 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 va

Q: What 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, expen

Q: Explain 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: Con

Q: What 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 in

Q: What 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-i

Real-world mechanisms, state management, edge cases, performance trade-offs, and practical coding.

50 Theory Questions7 Implementation Folios5 Free Model Answers

THEORY QUESTIONS & SOLUTIONSShowing 5 of 50 questions

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.

Unlock the remaining 52 SQL (Medium) 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

One-time settlement () · Lifetime access · Zero subscription

CROSS-DIFFICULTY NAVIGATION

Continue Preparing SQL

EASY LEVEL

Foundations & Core Concepts

HARD LEVEL

Internals & System Architecture