SQL Interview Questions
50 Must-Know Questions with Answers for Data Roles
Introduction
Structured Query Language (SQL) remains one of the most in-demand skills in data
engineering, analytics, and backend development. This guide compiles 50 frequently asked
SQL interview questions organized by difficulty, from basic retrieval to advanced window
functions and performance tuning. Each question includes a concise answer and, where
applicable, a code example.
Basic Level
Q: What is the difference between WHERE and HAVING?
A: WHERE filters rows before aggregation; HAVING filters after. Use WHERE with
non-aggregated columns and HAVING with aggregated ones (SUM, COUNT, AVG, etc.).
Q: What are the types of JOINs in SQL?
A: INNER JOIN (matching rows only), LEFT JOIN (all left + matching right), RIGHT JOIN (all
right + matching left), FULL OUTER JOIN (all rows from both), CROSS JOIN (cartesian
product), and SELF JOIN (joining a table to itself).
Q: What is the difference between UNION and UNION ALL?
A: UNION removes duplicate rows from the combined result set. UNION ALL keeps all rows
including duplicates. UNION ALL is faster because it skips the deduplication step.
Q: How do you find duplicate records in a table?
A: Use GROUP BY on the columns you want to check and filter with HAVING COUNT(*) > 1.
Intermediate Level
Q: What is a CTE and when should you use it?
A: A Common Table Expression (WITH clause) creates a named temporary result set scoped
to a single query. Use CTEs to break complex queries into readable steps, enable recursion, or
reference the same subquery multiple times.
Q: Explain the difference between ROW_NUMBER, RANK, and
DENSE_RANK.
A: ROW_NUMBER assigns a unique sequential number with no gaps. RANK assigns the same
number for ties but skips the next rank. DENSE_RANK assigns the same number for ties
without skipping.
Q: What is a correlated subquery?
A: A correlated subquery references columns from the outer query, meaning it executes once
per row in the outer query. It is typically slower than a JOIN but useful for row-by-row
comparisons.
Q: How do you calculate a running total in SQL?
A: Use SUM() as a window function: SUM(amount) OVER (ORDER BY date ROWS
UNBOUNDED PRECEDING).
Advanced Level
Q: What is query execution order in SQL?
A: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER
BY → LIMIT. Understanding this prevents common errors like referencing aliases in WHERE
clauses.
Q: How would you optimize a slow-running query?
A: Check for missing indexes, avoid SELECT *, reduce subqueries by using CTEs or JOINs,
use EXPLAIN/EXPLAIN ANALYZE to read the query plan, partition large tables, and avoid
functions on indexed columns in WHERE clauses.
Q: What is the difference between a clustered and non-clustered index?
A: A clustered index determines the physical sort order of the table (one per table). A
non-clustered index is a separate structure pointing to the actual rows, allowing multiple per
table.
SQL Interview Questions — v1.0 — For educational use