SQL Interview Questions and Answers: Joins, Window Functions, Indexes, and Real Queries
If you’re preparing for SQL interview questions, you’ll face two kinds: concept questions (“what’s the difference between WHERE and HAVING?”) and live query problems (“find the second highest salary”).
Interviewers use the second kind to see how you think, so they care less about memorized syntax than about how you handle ties, NULL values, and edge cases.
This guide covers both, in the order most interviews unfold: fundamentals, joins, grouping, subqueries and CTEs, window functions, classic query problems, database design, and performance. Every query uses the same small schema, so you can follow along in any database.
The sample schema
employees(id, name, department_id, manager_id, salary, hire_date, status)
departments(id, name)
customers(id, name, email)
orders(id, customer_id, order_date, amount)
Most queries below run in PostgreSQL, MySQL 8+, and SQL Server, and I flag where dialects differ. As for versions, PostgreSQL 18 was released on September 25, 2025, with a new asynchronous I/O subsystem, virtual generated columns, and native UUIDv7 support, and PostgreSQL 14 reaches end of life on November 12, 2026. If you know a database’s current release, mention it, but interviewers care far more about your SQL fundamentals.
Fundamentals
1. What are the main categories of SQL commands?
DDL defines structure (CREATE, ALTER, DROP), DML changes data (INSERT, UPDATE, DELETE), DQL queries data (SELECT), DCL controls access (GRANT, REVOKE), and TCL controls transactions (COMMIT, ROLLBACK, SAVEPOINT).
2. What’s the difference between DELETE, TRUNCATE, and DROP?
DELETE removes selected rows, can use WHERE, and fires triggers. TRUNCATE removes all rows quickly, usually without row-level logging or triggers, and often resets identity counters. DROP removes the whole table, including its structure.
3. WHERE vs HAVING?
WHERE filters individual rows before grouping. HAVING filters groups after aggregation, so it can use aggregate functions.
4. What’s the logical order of query execution?
FROM and joins, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. This explains why you can’t use a column alias from SELECT inside WHERE, and why window functions can’t appear in WHERE.
5. UNION vs UNION ALL?
UNION combines results and removes duplicates, which costs a sort or hash. UNION ALL keeps duplicates and is faster. Use UNION ALL unless you need deduplication.
6. How does NULL behave?
NULL means unknown, so NULL = NULL isn’t true. Use IS NULL and IS NOT NULL. Aggregates like SUM and AVG ignore nulls, and COUNT(*) counts rows while COUNT(column) counts non-null values.
7. The NOT IN trap?
If the subquery returns a NULL, NOT IN returns no rows, because the comparison becomes unknown. Prefer NOT EXISTS, or filter nulls out of the subquery.
8. Primary key vs foreign key vs unique key?
A primary key uniquely identifies a row and can’t be null. A foreign key references another table’s key and enforces referential integrity. A unique key prevents duplicates but typically allows a null value (details vary by database).
9. What constraints exist?
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT.
10. IN vs EXISTS?
IN compares against a list or subquery result. EXISTS checks whether any row exists and can stop early. Modern optimizers often treat them similarly, but EXISTS is safer with nulls.
Joins
11. Explain the join types.
INNER JOIN returns matching rows only. LEFT JOIN returns all left rows plus matches (or nulls). RIGHT JOIN is the mirror image. FULL JOIN returns all rows from both sides. CROSS JOIN returns every combination. A self join joins a table to itself.
12. Find customers who never placed an order.
SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;
The same result with NOT EXISTS:
SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);
13. Find employees who earn more than their manager.
SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;
14. Why does a filter in WHERE break a LEFT JOIN?
A condition on the right-hand table in WHERE removes the null-extended rows, turning the outer join into an inner join. Put such conditions in the ON clause when you want to keep unmatched left rows.
Grouping and aggregation
15. Departments with more than 5 employees?
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;
16. Find duplicate emails.
SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;
17. Delete duplicates, keeping one row.
WITH ranked AS (
SELECT id,
ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
FROM customers
)
DELETE FROM customers
WHERE id IN (SELECT id FROM ranked WHERE rn > 1);
SQL Server lets you delete from the CTE directly. Always run the SELECT version first, and do it inside a transaction.
18. Turn rows into columns (a simple pivot).
SELECT department_id,
SUM(CASE WHEN status = ‘active’ THEN 1 ELSE 0 END) AS active,
SUM(CASE WHEN status = ‘inactive’ THEN 1 ELSE 0 END) AS inactive
FROM employees
GROUP BY department_id;
Subqueries and CTEs
19. What’s a correlated subquery?
A subquery that references the outer query, so it logically runs once per outer row. It’s often replaceable with a join or window function for better performance.
20. What’s a CTE, and why use one?
A common table expression (WITH name AS (…)) names a subquery so you can read the query top-down and reuse it. A recursive CTE handles hierarchies:
WITH RECURSIVE chain AS (
SELECT id, name, manager_id, 1 AS level
FROM employees
WHERE manager_id IS NULL
UNION ALL
SELECT e.id, e.name, e.manager_id, c.level + 1
FROM employees e
JOIN chain c ON e.manager_id = c.id
)
SELECT * FROM chain;
SQL Server omits the RECURSIVE keyword.
21. CTE vs subquery vs temp table?
CTEs and subqueries are about readability and logic. Temp tables persist for the session, can be indexed, and help when you reuse a large intermediate result several times.
Window functions
22. What’s a window function?
A function that computes over a set of rows related to the current row without collapsing them like GROUP BY does. The pattern is function() OVER (PARTITION BY … ORDER BY …).
23. ROW_NUMBER vs RANK vs DENSE_RANK?
ROW_NUMBER gives unique sequential numbers, even for ties. RANK gives ties the same rank and then skips numbers (1, 2, 2, 4). DENSE_RANK gives ties the same rank without gaps (1, 2, 2, 3). With ties, DENSE_RANK is usually the right choice for “top N” questions.
24. Why can’t you filter on a window function in WHERE?
Window functions are evaluated after WHERE has already run, so the rank doesn’t exist yet. Wrap the query in a subquery or CTE, then filter.
25. Running total and month-over-month change?
SELECT order_date, amount,
SUM(amount) OVER (
ORDER BY order_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_total
FROM orders;
SELECT month, revenue,
revenue – LAG(revenue) OVER (ORDER BY month) AS change_vs_prev
FROM monthly_revenue;
LAG and LEAD look at the previous and next rows, and the frame clause (ROWS vs RANGE) matters for rolling calculations.
Classic query problems
26. Find the second highest salary.
This is probably the most-asked SQL interview question, because there’s no single keyword for it and it tests ties and empty results. Several approaches work:
— 1. MAX below the MAX (second highest distinct value)
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
— 2. OFFSET (MySQL / PostgreSQL / SQLite)
SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;
— 3. Window function (also gives you the people)
SELECT name, salary
FROM (
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) x
WHERE rnk = 2;
Always clarify the ties question: “second highest distinct salary” or “second row”? Without DISTINCT, LIMIT 1 OFFSET 1 returns the second row, which may still equal the top salary. Also clarify what to return when there is no second salary (usually NULL). Standard SQL and SQL Server spell the offset as OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY.
27. Find the Nth highest salary.
Parameterize the rank: use DENSE_RANK() and filter rnk = N, or LIMIT 1 OFFSET N-1 with DISTINCT. This is a classic follow-up and a well-known LeetCode problem.
28. Highest salary in each department, with names.
SELECT department_id, name, salary
FROM (
SELECT department_id, name, salary,
DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
) x
WHERE rnk = 1;
This “top per group” shape appears constantly. Use ROW_NUMBER instead if you want exactly one row per group.
29. Find numbers or days that appear at least three times consecutively.
Use LAG and LEAD (or a self-join) to compare each row with its neighbors, or the “gaps and islands” trick of subtracting ROW_NUMBER() from the value to group consecutive runs.
30. Employees hired in the last 30 days, grouped by department?
SELECT department_id, COUNT(*) AS new_hires
FROM employees
WHERE hire_date >= CURRENT_DATE – INTERVAL ’30 days’
GROUP BY department_id;
Date syntax varies: SQL Server uses DATEADD(day, -30, GETDATE()), and MySQL uses CURDATE() – INTERVAL 30 DAY.
Database design and transactions
31. What is normalization?
Organizing tables to reduce redundancy. 1NF means atomic values and no repeating groups. 2NF removes partial dependencies on part of a composite key. 3NF removes transitive dependencies, where non-key columns depend on other non-key columns. BCNF is a stricter form of 3NF.
32. When would you denormalize?
When read performance matters more than update simplicity, such as reporting tables or precomputed aggregates, accepting the extra storage and the need to keep copies consistent.
33. What does ACID mean?
Atomicity (all or nothing), Consistency (data stays valid), Isolation (concurrent transactions don’t corrupt each other), and Durability (committed data survives crashes).
34. What are the isolation levels and the anomalies they prevent?
Read uncommitted, read committed, repeatable read, and serializable. Higher levels prevent dirty reads, non-repeatable reads, and phantom reads, at the cost of concurrency. Defaults differ: PostgreSQL and SQL Server default to read committed, while MySQL’s InnoDB defaults to repeatable read.
35. What’s a deadlock?
Two transactions each hold a lock the other needs, so neither can proceed. The database detects it and aborts one. Reduce deadlocks by accessing tables in a consistent order and keeping transactions short.
36. Views vs materialized views?
A view is a saved query that runs each time. A materialized view stores its result and must be refreshed, which speeds up heavy reads.
37. Stored procedure vs function vs trigger?
A stored procedure is a saved routine you call, which can change data. A function returns a value and is usable inside queries. A trigger runs automatically on events such as insert or update.
Indexes and performance
38. What is an index, and what are the trade-offs?
A data structure (usually a B-tree) that speeds up lookups, sorts, and joins, at the cost of extra storage and slower writes. A clustered index defines the physical row order (one per table in engines like SQL Server and InnoDB), while non-clustered indexes are separate structures pointing to rows.
39. What’s a composite index, and why does column order matter?
An index on several columns. It follows the leftmost-prefix rule: an index on (department_id, hire_date) helps queries filtering on department_id or both, but not on hire_date alone.
40. When won’t an index be used?
When you wrap the indexed column in a function (WHERE YEAR(order_date) = 2026), use a leading wildcard (LIKE ‘%abc’), mismatch data types, or the optimizer estimates a full scan is cheaper. Write conditions so they’re “SARGable”:
— Slow: function on the column blocks index use
WHERE YEAR(order_date) = 2026
— Fast: range on the raw column
WHERE order_date >= ‘2026-01-01’ AND order_date < ‘2027-01-01’
41. How do you find out why a query is slow?
Read the execution plan with EXPLAIN (or EXPLAIN ANALYZE in PostgreSQL), look for full table scans, bad row estimates, expensive sorts, and nested loops over large sets. Then check indexes, statistics, and the query shape.
42. How do you paginate efficiently?
OFFSET pagination slows down as the offset grows, because the database still reads and discards the skipped rows. Keyset pagination is faster:
SELECT id, name
FROM customers
WHERE id > :last_seen_id
ORDER BY id
LIMIT 20;
43. Why avoid SELECT *?
It reads and transfers unneeded columns, defeats covering indexes, and breaks code when the schema changes.
Scenario questions
44. A report query takes minutes on production. What do you do?
Reproduce it with EXPLAIN ANALYZE, check indexes and filters, look for functions on columns and unnecessary joins, consider pre-aggregating or using a read replica, and avoid running heavy reports on the primary during peak hours.
45. You need to delete 50 million old rows safely.
Delete in batches inside short transactions, use an indexed condition, pause between batches, watch replication lag and locks, and consider partitioning by date so old partitions can be dropped instantly.
46. How would you add a column to a huge table with no downtime?
It depends on the database and version. Many modern engines can add nullable columns quickly, but others rewrite the table. Test on a copy, use online schema change tools where needed, and deploy in steps: add the column, backfill in batches, then add constraints.
47. Design a schema for an online store.
Customers, products, orders, order items (a junction table holding quantity and the price at purchase time), addresses, and payments. Use foreign keys, index lookup columns, and explain why the order item stores a copy of the price: product prices change.
How to answer well
- Clarify first. Ask about ties, nulls, duplicates, and the database dialect before you write.
- Start simple, then improve. A correct subquery beats a clever query that’s wrong.
- Think aloud about the execution order, and test with a tiny dataset in your head.
- Mention performance after correctness: indexes, plans, and cost.
- Say what you’d verify with EXPLAIN instead of guessing.
Where to practice
- Free local options: SQLite, or PostgreSQL or MySQL installed on your laptop, with a sample dataset.
- Online sandboxes: browser-based SQL playgrounds, good for quick puzzles.
- Your own server: a small VPS lets you run PostgreSQL or MySQL, load a bigger dataset, and practice indexing and EXPLAIN on a real machine. HostingFirst’s page (updated September 27, 2026) lists a Hostinger VPS hosting discount where KVM 1 starts at $6.49 per month with 1 vCPU, 4 GB of RAM, and 50 GB NVMe, with root access and a renewal price of $11.99 per month.
- Waiting for a sale: HostingFirst’s roundup lists Hostinger Black Friday VPS deals with KVM 1 at 67% off ($6.49 per month), a sale window of October 19 to December 14, 2026, and Black Friday on November 27. Promotional prices apply to the first term only, so confirm the renewal rate at checkout.