SQL Interview Questions with Answers

Structured Query Language (**SQL**) is one of the most heavily tested technical pillars in software engineering, data engineering, and backend development interviews. Whether you are interviewing for fintech backends, distributed data pipelines, or enterprise SaaS platforms, employers expect robust fluency in complex SQL querying, database normalization, indexing strategies, and analytical window functions.

This comprehensive guide covers the most frequently asked SQL interview questions, complete with deep-dive technical explanations and production-grade code examples.

1. What are SQL Window Functions? Explain the difference between `ROW_NUMBER()`, `RANK()`, and `DENSE_RANK()`.

A **window function** performs a calculation across a set of table rows that are somehow related to the current row. Unlike aggregate functions (`SUM`, `AVG`), window functions do not group rows into a single output row; each row retains its individual identity in the query result.

• `ROW_NUMBER()`: Assigns a unique sequential integer to each row within partition partitions, starting from 1, regardless of duplicate values.
• `RANK()`: Assigns a rank to each row within partition partitions. If there are ties, rows receive the same rank, but the next rank(s) are skipped (e.g., 1, 2, 2, 4).
• `DENSE_RANK()`: Assigns a rank to rows with ties without skipping any rank numbers (e.g., 1, 2, 2, 3).

-- SQL Window Functions Comparison Example
SELECT 
    employee_id,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_val,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_val
FROM employees;

2. How do database indexes work? What is the difference between Clustered and Non-Clustered Indexes?

A database index is a data structure (typically a self-balancing **B-Tree**) that improves the speed of data retrieval operations on a database table at the cost of additional storage space and slower write performance (inserts/updates).

• Clustered Index: Determines the physical order of data rows in the table. A table can only have **one** clustered index (usually the Primary Key). Searching via a clustered index is exceptionally fast because data pages are stored sequentially.
• Non-Clustered Index: Stored separately from the physical table data rows. It contains index keys along with a pointer (or row locator) pointing back to the actual data row. A table can have multiple non-clustered indexes.

-- Creating a Composite Non-Clustered Index
CREATE INDEX idx_users_email_status 
ON users (email, status);

3. When should you use a JOIN versus a Subquery or Common Table Expression (CTE)?

Choosing between Joins, Subqueries, and CTEs depends on readability, execution plan optimization, and reusability:

• Joins: Best for combining rows from multiple tables horizontally based on matching relational keys. Highly optimized by database query planners.
• Subqueries: Useful for nested filtering (e.g., `WHERE id IN (SELECT...)`). However, correlated subqueries can be slow if executed row-by-row.
• Common Table Expressions (CTEs): Defined using the `WITH` clause, CTEs significantly improve query readability, allow recursion (hierarchical queries), and can be referenced multiple times within a single query.

-- Recursive CTE Example for Organizational Hierarchies
WITH RECURSIVE EmployeeHierarchy AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT e.id, e.name, e.manager_id, eh.level + 1
    FROM employees e
    JOIN EmployeeHierarchy eh ON e.manager_id = eh.id
)
SELECT * FROM EmployeeHierarchy;

4. What are ACID properties in relational databases? Explain each.

**ACID** is a set of properties that guarantee reliable transaction processing in relational database management systems (RDBMS):

• Atomicity ('All or Nothing'): Ensures that all operations within a transaction succeed completely; if any single operation fails, the entire transaction is rolled back.
• Consistency: Ensures that a transaction brings the database from one valid state to another, maintaining all defined database constraints, foreign keys, and rules.
• Isolation: Ensures that concurrent transactions execute independently without interfering with each other's intermediate states.
• Durability: Guarantees that once a transaction is committed, its changes are permanently saved to non-volatile storage even in the event of a system crash or power failure.

-- Transaction Control Example
BEGIN TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Commit or Rollback based on success
COMMIT;

5. What is Database Normalization? Explain 1NF, 2NF, and 3NF.

**Normalization** is the process of structuring relational database tables to minimize data redundancy and dependency anomalies:

• First Normal Form (1NF): Requires that all columns contain atomic, indivisible values and that each table row is unique (no repeating groups or arrays).
• Second Normal Form (2NF): Must be in 1NF and have all non-key attributes fully functionally dependent on the entire primary key (no partial dependencies on composite keys).
• Third Normal Form (3NF): Must be in 2NF and have no transitive dependencies (non-key columns must depend exclusively on the primary key, not on other non-key columns).

Conclusion

Mastering SQL interview concepts—including analytical window functions, indexing internals, ACID guarantees, and normalization forms—is essential for clearing technical interviews and engineering robust, high-performance database architectures.