Top 50 SQL Interview Questions for Senior Developers

Part 1: Core SQL Concepts (Questions 1–10)

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters after aggregation. Use HAVING only with aggregate functions.

SELECT department, COUNT(*) AS emp_count
FROM employees
WHERE status = 'active'
GROUP BY department
HAVING COUNT(*) > 5;

2. What is the difference between UNION and UNION ALL?

UNION removes duplicates and performs a sort; UNION ALL keeps all rows and is faster. Prefer UNION ALL when duplicates are impossible or acceptable.

3. Explain the difference between DELETE, TRUNCATE, and DROP.

DELETE removes rows row-by-row, can be filtered, and is logged for rollback.
TRUNCATE removes all rows quickly, resets identity, and is minimally logged.
DROP removes the entire table structure and data.

4. What is a primary key vs. a unique key?

A primary key cannot be NULL and there can be only one per table. A unique key allows one NULL (in most databases) and multiple unique constraints per table.

5. What is a foreign key and why use it?

A foreign key enforces referential integrity between two tables. It prevents inserting child rows without a matching parent and can cascade updates or deletes.

CREATE TABLE orders (
  order_id INT PRIMARY KEY,
  customer_id INT,
  FOREIGN KEY (customer_id) REFERENCES customers(id)
    ON DELETE CASCADE
);

6. What is the difference between CHAR and VARCHAR?

CHAR(n) is fixed-length and padded with spaces; VARCHAR(n) is variable-length and stores only the actual characters plus a small length overhead.

7. What are the different types of JOINs?

INNER JOIN — matching rows only.
LEFT JOIN — all left rows, matched right rows or NULLs.
RIGHT JOIN — all right rows, matched left rows or NULLs.
FULL OUTER JOIN — all rows from both sides.
CROSS JOIN — Cartesian product.
SELF JOIN — joining a table to itself.

8. What is a self-join and when would you use it?

A self-join is a regular join where the same table is referenced twice using aliases. Common use cases include employee-manager hierarchies and adjacency lists.

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

9. What is the difference between IN and EXISTS?

IN compares a value against a list or subquery result. EXISTS checks for the existence of rows returned by a subquery and short-circuits. For large datasets, EXISTS is often faster because it stops scanning once a match is found.

10. How does COALESCE work?

COALESCE returns the first non-NULL value from its arguments. It is useful for providing defaults.

SELECT name, COALESCE(phone, email, 'No contact') AS contact
FROM customers;

Part 2: Aggregation and Grouping (Questions 11–18)

11. How does GROUP BY work?

GROUP BY collapses rows sharing the same values in specified columns into a single row, allowing aggregate functions like COUNT, SUM, AVG, MIN, and MAX.

12. What is the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)?

COUNT(*) counts all rows including NULLs.
COUNT(column) counts non-NULL values in that column.
COUNT(DISTINCT column) counts unique non-NULL values.

13. How do you find the second highest salary?

-- Using LIMIT
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

-- Using a subquery (more portable)
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

14. How do you find duplicate rows?

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

15. What is the difference between RANK, DENSE_RANK, and ROW_NUMBER?

ROW_NUMBER() assigns a unique sequential number.
RANK() assigns the same rank to ties, then skips the next ranks.
DENSE_RANK() assigns the same rank to ties without skipping.

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

16. What is the difference between GROUP BY and DISTINCT?

Both deduplicate rows, but GROUP BY is designed for aggregation while DISTINCT simply removes duplicates. Use DISTINCT for simple deduplication and GROUP BY when computing aggregates.

17. How do you pivot data in SQL?

SELECT department,
  SUM(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) AS male_count,
  SUM(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) AS female_count
FROM employees
GROUP BY department;

18. What is a CTE and why use it?

A Common Table Expression (CTE) is a temporary named result set defined with WITH. It improves readability, supports recursion, and can be referenced multiple times in a query.

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT department, COUNT(*)
FROM high_earners
GROUP BY department;

Part 3: Window Functions (Questions 19–25)

19. What are window functions?

Window functions perform calculations across a set of rows related to the current row, without collapsing them like GROUP BY does. They use the OVER() clause.

20. Explain PARTITION BY.

PARTITION BY divides the result set into partitions to which the window function is applied independently.

SELECT name, department, salary,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank
FROM employees;

21. What is the difference between ROWS and RANGE in window frames?

ROWS frames by physical row offsets; RANGE frames by logical value ranges. RANGE can include peers with the same ordering value.

22. How do you compute a running total?

SELECT order_id, order_date, amount,
  SUM(amount) OVER (ORDER BY order_date
                    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM orders;

23. How do you compute a moving average?

SELECT date, sales,
  AVG(sales) OVER (ORDER BY date
                   ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS seven_day_avg
FROM daily_sales;

24. How do you access the previous and next row values?

SELECT date, sales,
  LAG(sales)  OVER (ORDER BY date) AS prev_sales,
  LEAD(sales) OVER (ORDER BY date) AS next_sales
FROM daily_sales;

25. How do you find the top N per group?

WITH ranked AS (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT * FROM ranked WHERE rn <= 3;

Part 4: Subqueries and Set Operations (Questions 26–31)

26. What is a correlated subquery?

A correlated subquery references columns from the outer query and is re-executed for each row. It can be slower than non-correlated alternatives.

SELECT name, salary
FROM employees e
WHERE salary > (SELECT AVG(salary)
                FROM employees
                WHERE department = e.department);

27. What is the difference between a scalar, multi-row, and correlated subquery?

Scalar returns a single value.
Multi-row returns multiple rows (used with IN, ANY, ALL).
Correlated depends on the outer query.

28. What are INTERSECT and EXCEPT (MINUS)?

INTERSECT returns rows common to both queries. EXCEPT (called MINUS in Oracle) returns rows from the first query not present in the second.

29. When would you use a subquery vs. a JOIN?

Use a JOIN when you need columns from multiple tables. Use a subquery when you only need a filter condition or a scalar value. Modern optimizers often rewrite them equivalently, but readability matters.

30. What is a derived table?

A derived table is a subquery in the FROM clause that produces a temporary result set.

SELECT dept_avg.department, dept_avg.avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) dept_avg
WHERE dept_avg.avg_salary > 80000;

31. How do ANY and ALL work?

ANY returns true if the comparison holds for at least one value; ALL requires it to hold for every value.

Part 5: Performance and Optimization (Questions 32–40)

32. What is an index and what types exist?

An index is a data structure that speeds up retrieval. Common types include B-tree (default), hash, bitmap, full-text, and composite indexes.

33. What is the difference between a clustered and non-clustered index?

A clustered index determines the physical order of rows in a table (one per table). A non-clustered index is a separate structure with pointers to the rows (many allowed).

34. When does an index not get used?

Using functions on indexed columns: WHERE UPPER(name) = 'JOHN'
Leading wildcards: WHERE name LIKE '%son'
Type mismatches or implicit conversions.
Small tables where a full scan is cheaper.
Using OR across non-indexed columns.

35. What is a covering index?

A covering index includes all columns needed by a query, so the database can satisfy the query from the index alone without accessing the table.

36. How do you read an execution plan?

Look for table scans (often bad on large tables), index seeks (good), expensive operators like sorts and hashes, and estimated vs. actual row counts. Use EXPLAIN (MySQL/PostgreSQL) or SET SHOWPLAN_TEXT ON (SQL Server).

EXPLAIN ANALYZE
SELECT customer_id, SUM(amount)
FROM orders
WHERE order_date >= '2024-01-01'
GROUP BY customer_id;

37. What is query caching and when is it harmful?

Query caching stores results of identical queries. It can harm performance in write-heavy systems because cache invalidation overhead grows. Modern databases like MySQL 8 have removed the built-in query cache for this reason.

38. How do you optimize a slow query?

Identify bottlenecks via execution plan.
Add appropriate indexes.
Rewrite to avoid functions on indexed columns.
Reduce result set size early with filters.
Replace correlated subqueries with joins or CTEs.
Use pagination instead of fetching all rows.

39. What is an index-only scan?

An index-only scan reads data directly from the index without touching the heap/table. It requires a covering index and is one of the fastest access methods.

40. What is the N+1 query problem?

The N+1 problem occurs when an ORM executes one query to fetch a list of entities and then N additional queries to fetch related data for each entity. Solve it with JOINs, eager loading, or batch fetching.

Part 6: Transactions and Concurrency (Questions 41–44)

41. What are ACID properties?

Atomicity — all operations in a transaction succeed or none do.
Consistency — the database moves from one valid state to another.
Isolation — concurrent transactions don't interfere.
Durability — committed data survives crashes.

42. What are isolation levels?

Read Uncommitted — allows dirty reads.
Read Committed — no dirty reads; allows non-repeatable reads.
Repeatable Read — no non-repeatable reads; allows phantom reads.
Serializable — full isolation, no anomalies, lowest concurrency.

43. What are deadlocks and how do you prevent them?

A deadlock occurs when two transactions hold locks the other needs. Prevent by accessing tables in a consistent order, keeping transactions short, using appropriate isolation levels, and adding proper indexes to reduce lock scope.

44. What is optimistic vs. pessimistic locking?

Optimistic locking assumes conflicts are rare and checks at commit time using a version column. Pessimistic locking acquires locks upfront using SELECT ... FOR UPDATE. Use optimistic for read-heavy workloads and pessimistic for write-heavy or high-contention scenarios.

-- Pessimistic
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- perform updates
COMMIT;

-- Optimistic
UPDATE products
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;

Part 7: Advanced Topics and Database Design (Questions 45–50)

45. What is normalization and what are the normal forms?

Normalization organizes tables to reduce redundancy. Key forms include 1NF (atomic values), 2NF (no partial dependencies), 3NF (no transitive dependencies), and BCNF (every determinant is a candidate key).

46. When would you denormalize?

Denormalize for read-heavy workloads where joins become expensive, in data warehouses, or when caching computed aggregates. Trade write simplicity and storage for read performance.

47. What is a materialized view?

A materialized view stores the result of a query physically and can be refreshed periodically. Unlike a regular view, it improves read performance at the cost of storage and refresh overhead.

CREATE MATERIALIZED VIEW dept_stats AS
SELECT department, COUNT(*) AS emp_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

REFRESH MATERIALIZED VIEW dept_stats;

48. What are stored procedures and triggers?

Stored procedures are precompiled SQL code stored in the database, callable by name. Triggers are procedures that fire automatically on INSERT, UPDATE, or DELETE events. Use procedures for reusable logic and triggers for auditing or cascading changes — but use triggers sparingly because they can hide business logic and complicate debugging.

49. How do you handle NULL values correctly?

NULL represents unknown or missing values. Comparisons with NULL yield NULL (treated as false in WHERE). Use IS NULL, IS NOT NULL, COALESCE, and NULLIF to handle them explicitly.

SELECT name,
  COALESCE(bonus, 0) AS bonus,
  NULLIF(divisor, 0) AS safe_divisor
FROM employees
WHERE manager_id IS NOT NULL;

50. What is the difference between OLTP and OLAP?

OLTP (Online Transaction Processing) handles short, fast, transactional queries against normalized schemas — e.g., an e-commerce checkout. OLAP (Online Analytical Processing) handles complex analytical queries over large historical datasets, typically using star or snowflake schemas in data warehouses.

Best Practices for SQL Interviews and Production Code

Always qualify columns with table aliases to avoid ambiguity.
Prefer explicit JOINs over comma-separated FROM lists.
Use CTEs to break complex queries into readable steps.
Index strategically — every index speeds reads but slows writes.
Avoid SELECT * in production; specify columns explicitly.
Test with realistic data volumes — a query that works on 100 rows may fail on 10 million.
Understand your database's specifics — PostgreSQL, MySQL, SQL Server, and Oracle differ in syntax and optimization behavior.
Think out loud in interviews — explain trade-offs, not just syntax.
Handle edge cases like NULLs, duplicates, and empty result sets.
Profile before optimizing — never guess where the bottleneck is.

Conclusion

Mastering these 50 SQL interview questions gives senior developers a comprehensive foundation across querying, optimization, transactions, and database design. The key to standing out in interviews is not just reciting definitions but demonstrating the ability to reason about trade-offs, performance implications, and real-world scenarios. Practice writing queries by hand, study execution plans on your own datasets, and always be ready to explain why a particular approach is better than another. SQL is a skill that compounds with experience — the more deeply you understand its internals, the more effectively you can build systems that scale gracefully under real workloads.