SQL & Database Interview Questions: Indexing, ACID Transactions & Query Optimization

Every production application depends on reliable data persistence. Regardless of whether you are interviewing for a Backend Engineer, Full-Stack Developer, or Data Science role, senior interviewers will test your understanding of database indexes, transaction isolation levels, window aggregations, and query optimization.

Below are the most critical SQL and relational database interview questions asked in top technology engineering loops.

1. B-Tree Indexes & Composite Index Rules

Q1: "How does a B-Tree index work under the hood, and why does column ordering matter in composite indexes?"

The Comprehensive Answer: A B-Tree (Balanced Tree) is a multi-way self-balancing search tree optimized for systems that read and write large blocks of memory. Unlike binary trees, each B-Tree node can contain hundreds of keys and child pointers, keeping tree depth shallow (typically 3 to 4 levels even for millions of rows), which minimizes disk I/O.

The Leftmost Prefix Rule for Composite Indexes: Suppose you create a composite index on (tenant_id, status, created_at): - Query 1: WHERE tenant_id = 42 AND status = 'ACTIVE' -> Index Used (Optimal). - Query 2: WHERE tenant_id = 42 -> Index Used. - Query 3: WHERE status = 'ACTIVE' -> Index NOT Used (Full Table Scan). Because the index is sorted primarily by tenant_id, searching by status alone is like trying to find people named "John" in a phonebook organized alphabetically by last name.

Covering Index: An index that contains all columns requested in the query's SELECT and WHERE clauses. The database engine satisfies the query entirely from index leaf nodes without fetching the underlying table heap pages (Index-Only Scan).

2. ACID Transactions & Concurrency Anomalies

Q2: "Explain the four standard SQL Transaction Isolation Levels and the anomalies they prevent."

Anomalies Defined: - Dirty Read: Transaction A reads uncommitted modifications made by Transaction B. If Transaction B rolls back, Transaction A acted on phantom data. - Non-Repeatable Read: Transaction A reads a row, Transaction B modifies or deletes that row and commits, Transaction A re-reads the row and gets different data. - Phantom Read: Transaction A queries rows matching a range condition; Transaction B inserts a new row matching that range; Transaction A re-queries and sees "phantom" rows.

3. Complex Query Writing: Advanced Window Functions

Q3: "Find the top 2 highest-paid engineers in every department without using correlated subqueries."

-- Schema: Employees (id, name, department_id, salary)
WITH RankedSalaries AS (
    SELECT 
        id,
        name,
        department_id,
        salary,
        DENSE_RANK() OVER (
            PARTITION BY department_id 
            ORDER BY salary DESC
        ) AS salary_rank
    FROM Employees
)
SELECT 
    department_id,
    name,
    salary,
    salary_rank
FROM RankedSalaries
WHERE salary_rank <= 2
ORDER BY department_id, salary_rank;

Why `DENSE_RANK()` over `ROW_NUMBER()` or `RANK()`? - ROW_NUMBER() assigns sequential numbers, arbitrarily breaking ties. - RANK() skips numbers after a tie (1, 1, 3). - DENSE_RANK() does not skip numbers after ties (1, 1, 2), guaranteeing both employees tied for 1st place are included without skipping 2nd place.

4. Common Table Expressions (CTEs) & Recursive Hierarchies

Q4: "How do you query an organizational hierarchy (Manager -> Employee tree) of unknown depth in SQL?"

-- Recursive Common Table Expression
WITH RECURSIVE OrgChart AS (
    -- Anchor member: Start with top executive (manager_id is NULL)
    SELECT 
        id, 
        name, 
        manager_id, 
        1 AS org_level
    FROM Employees
    WHERE manager_id IS NULL

UNION ALL

-- Recursive member: Find all direct reports SELECT e.id, e.name, e.managerid, o.orglevel + 1 FROM Employees e INNER JOIN OrgChart o ON e.managerid = o.id ) SELECT * FROM OrgChart ORDER BY orglevel, manager_id; ```

5. How to Read and Optimize EXPLAIN ANALYZE

When diagnosing slow database performance in production: 1. Look for `Seq Scan` (Sequential Scan): If a sequential scan occurs on a large table with selective filtering, a missing index or data type mismatch is preventing index usage. 2. Examine `Rows Removed by Filter`: High numbers indicate the database read thousands of rows from disk only to discard them. Add an index on the filter column. 3. Check `Buffers: shared hit vs read`: read represents physical disk I/O; hit represents memory cache. Optimizing buffer hits is critical for sub-millisecond query response.

6. Practice Database Design & System Architecture on Veyrai

Database indexing and data modeling decisions are core components of senior system design interviews. Test your query strategies in our Live Coding Arena, and practice designing high-throughput distributed database architectures with Veyrai's System Design Interviewer.