SQL interview questions and answers: 25 with worked queries
These SQL interview questions and answers run from basic to advanced: joins, GROUP BY, window functions, subqueries, CTEs, indexes and NULL handling. Each has a short query on four small tables, so you can run every answer yourself.
What does this set of SQL interview questions use as sample data?
Four small tables, like the sketch you often get on a shared screen.
| Table | Columns | Rows |
|---|---|---|
customers | customer_id, name, city | 1 Ana (Austin), 2 Ben (Boston), 3 Cara (Austin), 4 Dev (city is NULL) |
orders | order_id, customer_id, order_date, amount | 6 orders, Jan to Mar 2026. Customer 1 has three, customer 2 has two, customer 3 has one with a NULL amount, customer 4 has none |
employees | emp_id, name, dept_id, manager_id, salary | Maya (Eng, 150, no manager), Raj and Lin (Eng, 120 each), Tom (Sales, 90), Zoe (Sales, 80), Sam (dept_id NULL, 70) |
departments | dept_id, dept_name | 10 Eng, 20 Sales, 30 HR (nobody works in HR) |
The orders are 101 (customer 1, Jan 5, 50), 102 (1, Feb 10, 70), 103 (2, Jan 20, 30), 104 (3, Mar 2, NULL), 105 (1, Mar 15, 20) and 106 (2, Mar 20, 90). Managers are Maya for Raj, Lin and Tom, and Tom for Zoe and Sam.
What are the basic SQL questions asked in an interview?
Basic SQL questions test five things: filtering and sorting with WHERE and ORDER BY, grouping with GROUP BY and HAVING, joining tables, handling NULL, and counting correctly. Questions 1 to 7 cover them.
1. How do you filter and sort rows?
Filter with WHERE, sort with ORDER BY. Orders over 40, biggest first:
SELECT order_id, amount FROM orders WHERE amount > 40 ORDER BY amount DESC;
Result: 106 (90), 102 (70), 101 (50). The NULL row drops out because a comparison with NULL is never true.
2. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. Customers whose spend since February is over 50:
SELECT customer_id, SUM(amount) AS total FROM orders WHERE order_date >= '2026-02-01' GROUP BY customer_id HAVING SUM(amount) > 50;
Result: customer 1 with 90 and customer 2 with 90.
3. What does COUNT(*), COUNT(column) and COUNT(DISTINCT column) return?
SELECT COUNT(*), COUNT(amount), COUNT(DISTINCT customer_id) FROM orders;
Result: 6, 5, 3. COUNT(*) counts rows. COUNT(column) skips NULLs. COUNT(DISTINCT) skips NULLs and repeats.
4. How do you handle NULL?
Never write = NULL. Use IS NULL, and COALESCE to supply a default.
SELECT order_id, COALESCE(amount, 0) AS amount FROM orders WHERE amount IS NULL OR amount < 30;
Result: 104 with 0, and 105 with 20. AVG(amount) ignores the NULL (52.0), while AVG(COALESCE(amount, 0)) gives about 43.3.
5. How do GROUP BY and aggregate functions work?
Every selected column must be either grouped or aggregated.
SELECT customer_id, COUNT(*) AS orders, SUM(amount) AS total FROM orders GROUP BY customer_id;
Result: customer 1 has 3 orders and 140, customer 2 has 2 and 120, customer 3 has 1 and a NULL total, because SUM of only NULLs is NULL.
6. What is the difference between UNION and UNION ALL?
UNION removes duplicate rows, which costs a sort or hash. UNION ALL keeps everything and is faster. SELECT city FROM customers UNION ALL SELECT 'Austin'; returns Austin three times. With UNION it appears once.
7. How do you get the top three rows?
SELECT name, salary FROM employees ORDER BY salary DESC, emp_id LIMIT 3;
This runs in MySQL, PostgreSQL and SQLite. The standard form is FETCH FIRST 3 ROWS ONLY in place of LIMIT (PostgreSQL and Oracle accept it; SQLite and MySQL do not). SQL Server uses TOP 3. Maya is first, and Raj and Lin tie at 120, so both are always in the top 3. Only the order of rows 2 and 3 is open unless you add a tiebreaker such as emp_id.
What are the 5 basic SQL queries?
Think of them as five commands to know first. SELECT reads rows. INSERT adds a row. UPDATE changes rows. DELETE removes rows. CREATE TABLE defines a table. Without WHERE, UPDATE and DELETE hit every row.
INSERT INTO customers (customer_id, name, city) VALUES (5, 'Eli', 'Denver');UPDATE customers SET city = 'Dallas' WHERE customer_id = 5;DELETE FROM customers WHERE customer_id = 5;
What are the 5 types of SQL?
The five command groups are DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK, SAVEPOINT).
What are the 10 common SQL queries to know?
Write these from memory: questions 1, 2, 3, 4, 5, 7, 8, 9, 17 and 19.
SQL interview questions on joins
8. What is the difference between INNER JOIN and LEFT JOIN?
INNER keeps only matching rows. LEFT keeps every row from the left table and fills NULL where nothing matches.
SELECT c.name, o.order_id FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id;
Result: six rows, and Dev is missing. Change INNER to LEFT and Dev appears with a NULL order_id.
9. How do you find rows with no match?
Left join, then keep the NULLs on the right side.
SELECT c.name FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL;
Result: Dev. A common slip: filtering o in WHERE (other than IS NULL) turns the left join back into an inner join. Use ON instead.
10. What is a self join?
A table joined to itself, with two aliases. Each employee and their manager:
SELECT e.name AS employee, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.emp_id;
Result: Maya has no manager (NULL). Raj, Lin and Tom report to Maya. Zoe and Sam report to Tom.
11. How do you keep zero-count groups in a report?
Start from the table that must keep all its rows, then LEFT JOIN. Count a column from the right table, not *.
SELECT d.dept_name, COUNT(e.emp_id) AS headcount FROM departments d LEFT JOIN employees e ON e.dept_id = d.dept_id GROUP BY d.dept_name;
Result: Eng 3, Sales 2, HR 0. With COUNT(*) HR would show 1.
Subqueries, EXISTS and CTEs
12. What is a correlated subquery?
A subquery that refers to the outer row, so it runs once per outer row in principle. Employees paid above their own department average:
SELECT name, salary FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees x WHERE x.dept_id = e.dept_id);
Result: Maya (150 against 130) and Tom (90 against 85). Sam is left out: his dept_id is NULL.
13. What is a CTE, and when do you use one?
A named subquery with WITH. It makes multi-step logic readable. Customers who spent more than the average customer:
WITH totals AS (SELECT customer_id, SUM(amount) AS total FROM orders GROUP BY customer_id) SELECT c.name, t.total FROM totals t JOIN customers c ON c.customer_id = t.customer_id WHERE t.total > (SELECT AVG(total) FROM totals);
Result: Ana with 140. The average of 140 and 120 is 130, and the NULL total is ignored.
14. When do you use EXISTS?
When you only need to know whether a match exists. Customers with at least one order over 60:
SELECT name FROM customers c WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.amount > 60);
Result: Ana and Ben.
15. Why is NOT IN dangerous?
If the subquery returns any NULL, NOT IN returns no rows. SELECT dept_name FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees); returns nothing, because Sam's dept_id is NULL. The safe version is:
SELECT dept_name FROM departments d WHERE NOT EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);
Result: HR.
Window function questions for a data analyst interview
These are the SQL interview questions for data analyst roles that separate strong answers from average ones.
16. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
All three number rows in an ordered window and differ on ties. Order employees by salary, highest first:
SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, RANK() OVER (ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (ORDER BY salary DESC) AS dr FROM employees;
Raj and Lin tie at 120. ROW_NUMBER gives them 2 and 3 in an arbitrary order. RANK gives both 2, then Tom gets 4. DENSE_RANK gives both 2, then Tom gets 3.
17. How do you find the second highest salary?
SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2;
Result: 120. The no-window answer is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);.
18. How do you get the top earner in each department?
SELECT dept_id, name, salary FROM (SELECT dept_id, name, salary, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rk FROM employees WHERE dept_id IS NOT NULL) t WHERE rk = 1;
Result: Maya for Eng and Tom for Sales. RANK shows all ties. ROW_NUMBER gives exactly one.
19. How do you write a running total?
SELECT order_id, order_date, SUM(amount) OVER (ORDER BY order_date, order_id) AS running_total FROM orders;
Result, in date order: 50, 80, 150, 150 (the NULL order adds nothing), 170, 260.
20. How do you compare a month with the previous month?
Aggregate first, then use LAG.
WITH m AS (SELECT EXTRACT(MONTH FROM order_date) AS mth, SUM(amount) AS total FROM orders GROUP BY EXTRACT(MONTH FROM order_date)) SELECT mth, total, total - LAG(total) OVER (ORDER BY mth) AS diff FROM m;
Result: January 80 (diff NULL), February 70 (minus 10), March 110 (plus 40). Dialects: EXTRACT works in PostgreSQL, MySQL and Oracle. SQL Server uses MONTH(order_date). SQLite uses strftime('%m', order_date).
21. How do you get each customer's latest order?
SELECT customer_id, order_id FROM (SELECT customer_id, order_id, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) t WHERE rn = 1;
Result: customer 1 order 105, customer 2 order 106, customer 3 order 104.
22. How do you show each order as a percent of the total?
SELECT order_id, ROUND(100.0 * amount / SUM(amount) OVER (), 1) AS pct FROM orders WHERE amount IS NOT NULL;
Result: 19.2, 26.9, 11.5, 7.7 and 34.6 for orders 101, 102, 103, 105 and 106. Empty parentheses mean one window over the whole result. Use 100.0 to avoid integer division.
Advanced and performance SQL interview questions
23. How do you pivot rows into columns?
Use conditional aggregation with CASE.
SELECT customer_id, SUM(CASE WHEN order_date < '2026-02-01' THEN COALESCE(amount, 0) ELSE 0 END) AS jan, SUM(CASE WHEN order_date >= '2026-02-01' THEN COALESCE(amount, 0) ELSE 0 END) AS later FROM orders GROUP BY customer_id;
Result: customer 1 has 50 and 90, customer 2 has 30 and 90, customer 3 has 0 and 0. Without COALESCE inside the SUM, customer 3 would show 0 and NULL, because the NULL amount passes through the CASE.
24. How do you find duplicates?
Group by the columns that should be unique and keep groups larger than one.
SELECT salary, COUNT(*) AS n FROM employees GROUP BY salary HAVING COUNT(*) > 1;
Result: 120 appears twice. To delete duplicates, number the rows with ROW_NUMBER over the duplicate key and delete those above 1. The syntax differs by database.
25. What is an index, and when does it not help?
An index is a sorted structure that lets the database find rows without scanning the whole table.
CREATE INDEX idx_orders_customer ON orders (customer_id);
It speeds up filters, joins and sorts on that column. It slows writes and takes space. It often does not help when you wrap the column in a function (WHERE YEAR(order_date) = 2026, where YEAR() is MySQL and SQL Server syntax), when the filter returns most of the table, or when the table is tiny. Check the plan with EXPLAIN.
SQL interview questions for business analyst roles
Business analyst interviews use the same joins and aggregation, plus translating a vague request into a query. Customers with no March 2026 order:
SELECT c.name FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.order_date >= '2026-03-01' AND o.order_date < '2026-04-01');
Result: Dev.
T-SQL interview questions: what changes in SQL Server?
T-SQL is Microsoft's dialect for SQL Server. The core is the same, so name the differences: SELECT TOP 3 instead of LIMIT, ISNULL(a, b) (two arguments) beside the standard COALESCE, GETDATE() for the current time, DATEDIFF for date gaps, temp tables named with #, and TRY...CATCH for errors.
How do you think aloud in a SQL interview?
Many failed SQL rounds come from poor communication, not syntax. A routine that works:
- Repeat the question and the grain. "One row per customer, with total spend. Right?"
- Ask about edge cases: NULLs, ties, empty groups, duplicates.
- Say the plan before the code. "Aggregate orders by customer, then join to customers."
- Build in steps. Write the inner query, test it on a few rows out loud, then wrap it.
- Check the result by walking the sample rows through the query.
- Offer an alternative. "A window function also works. I chose a CTE for readability."
If you are blank, say what you know: the tables, the join key, the grain. List SQL on your CV or resume only at the level you can show. For a full loop beyond SQL, see the data analyst interview questions guide and technical interview tips.
A live option when you get stuck
Practice beats any tool, and the interviewer's rules come first. If allowed to use help, Hintly is a desktop app for Mac and Windows that listens to a call and shows live answers from your own notes, with its overlay left out of screen shares. It is pre-launch, and the way in is the waitlist. See the AI interview assistant page for how it works.
Questions
What SQL topics come up most in interviews?
Joins, GROUP BY with HAVING, NULL handling, subqueries and window functions. Data analyst roles lean on aggregation and window functions. Backend roles add indexes and query plans. Check the job posting.
Which SQL dialect should I practice?
Ask the recruiter which database the team uses. If you cannot find out, practice standard SQL on PostgreSQL. The core ideas work everywhere. Date functions, LIMIT versus TOP and string functions are where dialects differ most.
How many SQL questions should I practice?
Work through the 25 below, then rewrite each from memory a day later. After that, write two or three queries a day on a database you loaded yourself, so you meet real mistakes.