SQL · MEDIUM CODEX
SQL Interview Questions — Medium
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
col1orcol1 AND col2uses the index, but a filter oncol2orcol2 AND col3alone cannot use it.
Q5What is the overhead of indexes on write operations?
- Overhead: Every
INSERT,UPDATE, orDELETErequires 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, orGROUP BYclauses.
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.
₹399 India / $9 International · One-time settlement · Zero subscription