SQL Interview Questions for Software Engineers (With Answers)
The SQL interview questions software engineers get asked most: joins, grouping, window functions, indexes, transactions and query optimization, with clear answers and example queries.
LiveHint.ai team · · 5 min read
SQL comes up in backend, full-stack and data engineering interviews, sometimes as a written exercise and sometimes as conceptual questions in a technical screen. The good news is that the set of topics is small and stable. Here are the questions that come up most often, grouped by topic, with answers you can explain out loud.
The examples use two tables:
employees(id, name, department_id, manager_id, salary, hired_at)
departments(id, name)
Joins
What's the difference between INNER, LEFT, RIGHT and FULL OUTER JOIN?
- INNER JOIN returns only rows that match in both tables.
- LEFT JOIN returns every row from the left table, with matching rows from the right, or NULLs where there's no match.
- RIGHT JOIN is the mirror image. Most people rewrite it as a LEFT JOIN for readability.
- FULL OUTER JOIN returns every row from both tables, with NULLs where either side has no match.
Find departments with no employees.
SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;
This "anti-join" pattern comes up often. NOT EXISTS works too and is equally common.
Find each employee's manager's name.
A self-join:
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
Use a LEFT JOIN so employees with no manager still appear.
Grouping and aggregation
What's the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after aggregation. You can't use an aggregate like COUNT(*) in a WHERE clause.
Find departments with more than 10 employees.
SELECT department_id, COUNT(*) AS headcount
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 10;
What does COUNT(*) count compared with COUNT(column)?
COUNT(*) counts rows. COUNT(column) counts rows where that column isn't NULL. COUNT(DISTINCT column) counts unique non-NULL values.
Window functions
Window functions are the most common "harder" SQL topic. They compute a value across related rows without collapsing them into one row the way GROUP BY does.
Find the second-highest salary.
SELECT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 2
LIMIT 1;
Mention the alternatives: ORDER BY salary DESC LIMIT 1 OFFSET 1 fails when the top salary is tied, which is why DENSE_RANK is safer. Say what should happen if there's no second salary.
Find the highest-paid employee in each department.
SELECT name, department_id, salary
FROM (
SELECT *, RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 1;
What's the difference between ROW_NUMBER, RANK and DENSE_RANK?
For salaries 100, 100, 90:
| Function | Result |
|---|---|
| ROW_NUMBER | 1, 2, 3 |
| RANK | 1, 1, 3 |
| DENSE_RANK | 1, 1, 2 |
Calculate a running total of hires by month.
SELECT month, hires,
SUM(hires) OVER (ORDER BY month) AS running_total
FROM (
SELECT DATE_TRUNC('month', hired_at) AS month, COUNT(*) AS hires
FROM employees
GROUP BY 1
) monthly;
DATE_TRUNC is PostgreSQL syntax. Mention that date functions differ between databases.
NULLs
Why does WHERE manager_id = NULL return nothing?
Comparisons with NULL return "unknown", not true or false. Use IS NULL or IS NOT NULL. This also matters for NOT IN: if the subquery returns any NULL, NOT IN returns no rows. NOT EXISTS avoids the problem.
Indexes and performance
What is an index and how does it help?
An index is a separate data structure, usually a B-tree, that keeps a column's values sorted with pointers to the rows. It turns a full table scan into a lookup that's roughly O(log n). The cost is extra storage and slower writes, because every insert and update has to maintain the index.
When won't the database use an index?
- The query wraps the column in a function, such as
WHERE LOWER(email) = ..., unless there's an index on that expression. - A leading wildcard:
LIKE '%smith'. - The query would return a large fraction of the table, so a scan is cheaper.
- A composite index is used without its leading column. An index on
(department_id, salary)helps queries filtering bydepartment_id, but not queries filtering only bysalary.
How would you investigate a slow query?
Run EXPLAIN (or EXPLAIN ANALYZE) to see the query plan. Look for sequential scans on large tables, row estimates far from reality, and expensive sorts or nested loops. Then consider an index, rewriting the query, or fetching less data. Mention checking whether the query is slow every time or only under load, since that points to locking or resource contention instead.
Transactions and consistency
What does ACID mean?
- Atomicity: all of a transaction's changes happen, or none do.
- Consistency: a transaction moves the database from one valid state to another, respecting constraints.
- Isolation: concurrent transactions don't see each other's partial work, to the degree the isolation level allows.
- Durability: once committed, changes survive a crash.
What are isolation levels, and what problems do they prevent?
| Level | Prevents |
|---|---|
| Read uncommitted | Almost nothing |
| Read committed | Dirty reads |
| Repeatable read | Dirty reads and non-repeatable reads |
| Serializable | All of the above plus phantom reads |
Higher isolation means fewer anomalies but more locking or retries. Many databases default to read committed. PostgreSQL's repeatable read also prevents phantoms in practice, which is worth mentioning if you know it.
How would you prevent two users from booking the same seat?
Options to discuss: a unique constraint on (event_id, seat_id) so the second insert fails; SELECT ... FOR UPDATE to lock the row inside a transaction; or optimistic locking with a version column. The unique constraint is usually simplest and most robust.
Design questions
When would you denormalize?
When read performance matters more than write simplicity, for example storing an order's total instead of summing line items on every read. The trade-off is keeping copies in sync. Explain how you'd do that: in the same transaction, with triggers, or by accepting that the copy is eventually consistent.
SQL or NoSQL?
Avoid declaring a winner. Relational databases fit structured data with relationships and transactions. Document or key-value stores fit very high write volumes, flexible schemas, or simple access patterns at scale. Most systems are well served by a relational database until there's a specific reason not to. Our system design interview framework covers how to discuss storage choices.
How to answer SQL questions in an interview
- Clarify the schema and edge cases first. Ties, NULLs, empty tables.
- Start simple. Write a working query, then improve it.
- Talk through it as you would in a coding interview.
- Mention the database you're assuming if syntax differs.
To rehearse explaining these out loud, run an AI mock interview set to a technical screen using a backend job description. The interviewer asks follow-ups on whatever's in the posting, including databases.
Keep reading
13 min read
STAR Method Examples: 12 Interview Answers That Work
Twelve full STAR method examples for software engineers, from conflict and failure to tight deadlines and production incidents, plus a checklist to adapt them.
- behavioral interview
- STAR method
- interview tips
- software engineering
4 min read
How to Handle a Difficult Interviewer (Silent, Rude or Distracted)
What to do when an interviewer is silent, cold, interrupting, distracted or openly skeptical: why it happens, how to stay composed, and exact phrases that keep the interview on track.
- interview tips
- interview anxiety
- communication
- software engineering