Top 40 SQL Interview Questions 2026: Complete Answers

Core Concepts (Q1-Q10)

Q1: WHERE vs HAVING? WHERE filters rows before grouping. HAVING filters groups after GROUP BY and can use aggregates.

Q2: DELETE vs TRUNCATE vs DROP? DELETE: removes specific rows, can roll back. TRUNCATE: removes all rows fast, DDL. DROP: removes table and structure entirely.

Q3: What are window functions? Calculations across rows without collapsing them. ROW_NUMBER(), RANK(), DENSE_RANK(), LAG(), LEAD(), SUM() OVER().

Q4: RANK() vs DENSE_RANK()? RANK() leaves gaps after ties: 1,1,3. DENSE_RANK() no gaps: 1,1,2. Use DENSE_RANK for Nth highest.

Q5: What is a CTE? Common Table Expression — WITH clause. Makes complex queries readable. Supports recursion for hierarchical data.

Window Functions in Practice (Q11-Q20)

Q11: 3rd highest salary.

SELECT DISTINCT salary FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t WHERE rnk = 3;

Q12: Employees earning more than manager.

SELECT e.name FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Q13: Running total.

SELECT date, amount, SUM(amount) OVER (ORDER BY date) AS running_total
FROM sales;

Performance (Q21-Q30)

Q21: How does an index work? B-tree stores values sorted with row pointers. O(log n) lookup vs O(n) full scan. Speeds SELECT, slows INSERT/UPDATE/DELETE.

Q22: What is EXPLAIN ANALYZE? Shows actual execution plan with timing. Look for Seq Scan on large tables (missing index) and high rows removed by filter.

Q23: Composite index leftmost prefix rule.

CREATE INDEX idx ON orders(user_id, status);
-- Uses index: WHERE user_id=1 AND status='pending'
-- Uses index: WHERE user_id=1
-- Does NOT use: WHERE status='pending' (skips user_id)

Advanced (Q31-Q40)

Q31: Deadlock? Thread A holds Lock 1 waits Lock 2. Thread B holds Lock 2 waits Lock 1. Both wait forever. Prevention: always acquire locks in the same order.

Q32: ACID? Atomicity: all or nothing. Consistency: valid state to valid state. Isolation: concurrent transactions don't interfere. Durability: committed data survives crashes.

Q33: Materialized view? Stored cached query result — fast to read, refresh manually: REFRESH MATERIALIZED VIEW my_view.

Q34: UNION vs UNION ALL? UNION removes duplicates (slower). UNION ALL keeps all rows (faster).

See SQL Academy for hands-on practice.