SQL Interview Questions and Answers (2026 Edition)

SQL Fundamentals

1. DDL vs DML vs DCL vs TCL?

2. Primary key vs Unique key vs Foreign key?

• Primary key — uniquely identifies a row; one per table; cannot be NULL.
• Unique key — uniqueness only; multiple allowed; can be NULL (usually).
• Foreign key — column whose values must exist in another table's primary/unique key — enforces referential integrity.

3. NULL — what does it really mean?

"Unknown" or "not applicable". Not equal to anything, including itself (NULL = NULL is NULL, not TRUE). Always use IS NULL / IS NOT NULL.

Joins

4. Inner vs Left vs Right vs Full Outer Join?

SELECT * FROM A INNER JOIN B ON A.id = B.a_id;  -- only matching rows
SELECT * FROM A LEFT  JOIN B ON A.id = B.a_id;  -- all of A, B null if no match
SELECT * FROM A RIGHT JOIN B ON A.id = B.a_id;  -- all of B, A null if no match
SELECT * FROM A FULL  OUTER JOIN B ON A.id = B.a_id;  -- everything

5. Self join — when?

A table joined to itself. Common for hierarchies (employee → manager) or comparing rows in the same table:

SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON e.manager_id = m.id;

6. CROSS JOIN vs INNER JOIN with no condition?

Same effect — Cartesian product. CROSS JOIN is the explicit, readable way to do it.

Aggregation and Grouping

7. WHERE vs HAVING?

• WHERE — filters rows BEFORE grouping.
• HAVING — filters groups AFTER aggregation.

SELECT dept, COUNT(*) FROM employees
WHERE active = 1                  -- before grouping
GROUP BY dept
HAVING COUNT(*) > 10;             -- after grouping

8. COUNT(*) vs COUNT(col) vs COUNT(DISTINCT col)?

• COUNT(*) — all rows.
• COUNT(col) — rows where col IS NOT NULL.
• COUNT(DISTINCT col) — unique non-null values.

9. Window functions — example?

Compute a per-row aggregate without collapsing rows:

SELECT
  name,
  salary,
  AVG(salary) OVER (PARTITION BY dept)       AS dept_avg,
  RANK()      OVER (PARTITION BY dept ORDER BY salary DESC) AS rank_in_dept,
  salary - LAG(salary) OVER (ORDER BY hire_date) AS diff_from_previous
FROM employees;

ROW_NUMBER(), RANK(), DENSE_RANK(), LAG, LEAD, NTILE, SUM/AVG/COUNT OVER.

Indexes and Performance

10. When does the database use an index?

When the query's WHERE, JOIN, or ORDER BY predicate is on an indexed column AND the optimiser estimates the index path is cheaper than a full scan.

Functions on the column defeat the index:

WHERE LOWER(email) = 'a@b.c'    -- index on email NOT used
WHERE email = 'a@b.c'           -- index used (or use a functional index)

11. Clustered vs Non-clustered index?

• Clustered — physically orders the table by the index key. One per table. The PK is usually clustered.
• Non-clustered — separate structure pointing at the table rows. Many per table.

12. Composite index — column order?

INDEX (a, b, c) helps queries that filter on a, (a,b), or (a,b,c) — left-prefix rule. Doesn't help queries that filter only on b or c.

13. EXPLAIN / EXPLAIN ANALYZE?

• EXPLAIN — show the planner's intended execution plan.
• EXPLAIN ANALYZE — actually run the query and show actual timings + row counts. Essential for diagnosing slow queries.

Transactions

14. ACID — what is each letter?

• Atomicity — all-or-nothing.
• Consistency — constraints always hold.
• Isolation — concurrent transactions don't see each other's partial state.
• Durability — committed data survives crashes.

15. Isolation levels?

16. Deadlock — what causes it, how to avoid?

Two transactions each hold a lock the other needs. Avoid by acquiring locks in the same order everywhere, keeping transactions short, and using SELECT … FOR UPDATE SKIP LOCKED patterns where appropriate.

Schema Design

17. Normalisation (1NF / 2NF / 3NF)?

• 1NF — atomic columns (no comma-separated lists in one cell).
• 2NF — all non-key columns depend on the FULL primary key (not just part of it).
• 3NF — no transitive dependencies (A → B → C should be split).

18. When to denormalise?

For read-heavy workloads where joins become a bottleneck — copy lookup values into the fact table (e.g. order.customer_name cached from customers.name). Trade write complexity for read speed.

19. Soft delete vs hard delete?

• Soft delete — deleted_at column; row stays but is filtered out. Good for audit, recovery.
• Hard delete — actual DELETE. Frees space; may break foreign-key history.

Real-World

20. How would you find the second-highest salary?

SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- Or with window functions (better for top-N):
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rk
  FROM employees
) t WHERE rk = 2;

21. How do you paginate efficiently for huge tables?

LIMIT 20 OFFSET 1_000_000 is slow — the DB still scans the first million. Use keyset (seek) pagination:

SELECT * FROM events WHERE id < :last_seen_id ORDER BY id DESC LIMIT 20;

22. How do you avoid N+1 queries?

Eager-load with a single JOIN or IN (…) subquery:

SELECT u.*, json_agg(o.*) AS orders FROM users u
LEFT JOIN orders o ON o.user_id = u.id GROUP BY u.id;

In an ORM: Post::with('author')->get() (Laravel) / Post.objects.select_related('author') (Django).

23. CTE vs subquery?

WITH recent AS (SELECT * FROM orders WHERE created_at > now() - interval '7 days')
SELECT customer_id, COUNT(*) FROM recent GROUP BY customer_id;

CTEs are usually easier to read and reuse within the query. Some databases materialise CTEs (extra cost); modern Postgres can inline.

24. Database scaling — read replica vs sharding?

• Read replicas — async copies for read-heavy workloads. Slight staleness.
• Sharding — split data across N databases by a key (user_id mod N, geographic region). Eliminates write bottleneck but adds operational complexity.