The interviewer shares a screen with two tables, employees and departments, and says: "Show me every department and how many people are in it, including the empty ones." You write an INNER JOIN, a GROUP BY, and a COUNT(*). It runs. Then they ask why the Research department is missing, and why it would say 1 instead of 0 if you switched to a LEFT JOIN. The syntax is easy; the reasoning about rows is what gets tested.
The short answer
SQL interviews for freshers and backend roles circle the same ground: keys, joins, grouping, subqueries, classic puzzles (second highest salary, duplicates), window functions, indexes, normalisation, transactions and NULLs. Interviewers care less about keywords than whether you can predict what a query returns, explain why, and say what changes on a large table. Answers below use standard SQL and flag MySQL and PostgreSQL differences.
The examples assume employees(id, name, dept_id, salary, manager_id) and departments(id, name).
Keys, constraints and joins
1. What is the difference between a primary key and a unique key?
Both guarantee no two rows share a value, but a primary key also forbids NULL and there is only one per table. A table can have several unique keys, and a unique column can hold NULLs. MySQL and PostgreSQL allow multiple NULLs in a unique column (SQL Server allows one); PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT to forbid that. In InnoDB the primary key is also the clustered index, so rows are stored in its order.
2. What does a foreign key actually enforce?
It guarantees every child value exists in the parent's key, so no employee can point at a missing department. It also controls what happens when the parent changes: ON DELETE CASCADE deletes the children, ON DELETE SET NULL orphans them deliberately, and the default (RESTRICT / NO ACTION) refuses the delete.
Likely follow-up: "Why do some teams skip foreign keys?" Each write must check the parent, and they cannot span shards. Integrity then moves into application code.
3. Explain INNER JOIN and LEFT JOIN with a query.
INNER JOIN keeps only matching rows: SELECT e.name, d.name FROM employees e INNER JOIN departments d ON e.dept_id = d.id drops unmatched employees and departments. LEFT JOIN keeps every row from the left table and fills the right side with NULLs where nothing matches: SELECT d.name, COUNT(e.id) FROM departments d LEFT JOIN employees e ON e.dept_id = d.id GROUP BY d.id, d.name lists every department, with 0 for empty ones. Note COUNT(e.id), not COUNT(*): the empty department still produces one row (with NULLs), and COUNT(*) would count it as 1.
Likely follow-up: "Why did my LEFT JOIN behave like an INNER JOIN?" A condition on the right table went into WHERE (say WHERE e.salary > 50000), and the NULL-filled rows fail it. Move it into ON.
4. What are RIGHT, FULL OUTER, CROSS and self joins?
RIGHT JOIN is a LEFT JOIN with the tables swapped. FULL OUTER JOIN keeps unmatched rows from both sides. MySQL does not support it, so you emulate it with a LEFT JOIN, then UNION ALL a RIGHT JOIN filtered to rows where the left side is NULL; PostgreSQL supports it directly. CROSS JOIN returns every combination (10 rows by 10 gives 100). A self join joins a table to itself, as with employees and managers: SELECT e.name, m.name AS manager FROM employees e LEFT JOIN employees m ON e.manager_id = m.id.
Grouping, filtering and subqueries
5. What is the difference between WHERE and HAVING?
WHERE filters individual rows before grouping; HAVING filters groups after aggregation. That follows from the logical order a query is evaluated in: FROM and joins, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, then LIMIT. So WHERE COUNT(*) > 5 is an error, because counts do not exist yet, while HAVING COUNT(*) > 5 works.
Likely follow-up: "Why can't I use a SELECT alias in WHERE?" Because SELECT runs after WHERE. MySQL allows aliases in HAVING and ORDER BY as an extension; PostgreSQL allows them in ORDER BY but not in HAVING.
6. What can you put in the SELECT list of a GROUP BY query?
Only grouped columns and aggregates (or columns functionally dependent on the key). SELECT dept_id, name, MAX(salary) FROM employees GROUP BY dept_id asks for "the name" of a group with many names. PostgreSQL rejects it. Older MySQL returned an arbitrary name, often the wrong person; since 5.7.5, ONLY_FULL_GROUP_BY is on by default and it errors too. Use a window function (question 10) instead.
7. When would you use a subquery instead of a join?
Use a join when you need columns from both tables. Use a subquery when you are only testing membership or comparing against an aggregate, such as SELECT name FROM employees WHERE salary > (SELECT AVG(salary) FROM employees). Optimisers often rewrite one into the other, so readability usually decides. One real difference: joining to a one-to-many table can duplicate rows, while WHERE id IN (subquery) cannot.
8. What is a correlated subquery, and how is EXISTS different from IN?
A correlated subquery references the outer row, so conceptually it runs once per outer row: SELECT name FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id) finds people paid above their own department's average. EXISTS asks "is there at least one row?" and stops at the first match; IN compares against a list. The practical difference is NULLs: NOT IN returns nothing at all if the subquery contains a single NULL, while NOT EXISTS behaves as you expect. Prefer NOT EXISTS.
SQL query questions for interview: the classic puzzles
9. Find the second highest salary.
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). It handles ties and returns NULL when there is no second salary. The other common answer is SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1; the DISTINCT matters, because without it two people on the top salary make the "second highest" equal the highest. Note this version returns no row rather than NULL when there is no second salary.
10. Find the Nth highest salary in each department.
Use DENSE_RANK: SELECT * FROM (SELECT name, dept_id, salary, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 3. The window cannot go in WHERE directly, because window functions are evaluated after WHERE, hence the derived table (or a CTE). Window functions need MySQL 8.0 or later; PostgreSQL has had them since 8.4.
11. How do you find duplicate rows?
Group by the columns that define "duplicate" and keep groups with more than one row: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Ask first what counts as a duplicate.
12. How do you delete duplicates but keep one copy?
Keep the lowest id per group and delete the rest. In MySQL: DELETE u1 FROM users u1 JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id. The tempting DELETE FROM users WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email) fails in MySQL with error 1093, because you cannot select from the table you are deleting from in a subquery; wrapping the subquery in a derived table works around it. PostgreSQL accepts the subquery form, and you can also delete where ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) > 1. Then add a unique constraint.
Window functions
13. What is the difference between ROW_NUMBER, RANK and DENSE_RANK?
They differ only on ties. For salaries 100, 90, 90, 80: ROW_NUMBER gives 1, 2, 3, 4 (ties broken arbitrarily unless you add a tiebreaker to ORDER BY); RANK gives 1, 2, 2, 4 (skips after a tie); DENSE_RANK gives 1, 2, 2, 3 (no gaps). Use ROW_NUMBER to pick one row per group and DENSE_RANK for "Nth highest". Unlike GROUP BY, a window function keeps every row.
14. How do you calculate a running total?
SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM orders. Add PARTITION BY customer_id to restart the total per customer. The explicit frame matters: with only ORDER BY, the default frame is RANGE, so rows with the same date all get the same total including each other, which surprises people.
Indexes and performance
15. What is an index and why does it make reads faster?
Most indexes in MySQL and PostgreSQL are B-trees: a sorted structure that lets the database find a value in a logarithmic number of steps instead of scanning every row. An index on email turns WHERE email = ? from a full scan into a few page reads. It also helps ORDER BY and ranges. A composite index on (dept_id, salary) serves queries filtering on dept_id, or on dept_id and salary, but generally not on salary alone: the leftmost-prefix rule (MySQL 8.0.13+ can sometimes use a skip scan).
16. When does an index not help, or actively hurt?
Every write must also update each index, so many indexes slow a write-heavy table. Low-selectivity columns like a boolean gain little; a scan can be cheaper. And an index usually cannot be used when you wrap the column in a function (WHERE YEAR(created_at) = 2026; use a date range instead), use a leading wildcard (LIKE '%gmail.com'), or force a type conversion.
17. How do you investigate a slow query?
Run EXPLAIN: is it a full scan, which index was chosen, how many rows it expects, and does it need a temporary table or filesort? EXPLAIN ANALYZE actually runs the query and reports real timings; PostgreSQL has had it for years, MySQL since 8.0.18. In PostgreSQL, EXPLAIN ANALYZE on an UPDATE really modifies data, so wrap it in a transaction and roll back. Then fix the cause: an index, a rewritten condition, fewer columns, or keyset pagination instead of a large OFFSET.
Design and transactions
18. Explain normalisation up to 3NF.
Normalisation removes redundancy so a fact is stored once and cannot disagree with itself. 1NF: each column holds a single value, so no comma-separated phone numbers. 2NF: every non-key column depends on the whole key, which matters for composite keys; in order_items(order_id, product_id, product_name), product_name depends only on product_id and belongs in products. 3NF: no non-key column depends on another non-key column; storing dept_name on employees means renaming a department requires updating every employee. Mention that reporting tables are sometimes denormalised on purpose.
19. What does ACID mean?
Atomicity: a transaction applies fully or not at all. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's half-finished work, to a degree set by the isolation level. Durability: once committed, data survives a crash, because it reaches the write-ahead log (redo log in InnoDB) before commit returns.
20. What are isolation levels?
They decide which concurrency anomalies are allowed. Read Uncommitted allows dirty reads (seeing uncommitted data). Read Committed prevents those but allows non-repeatable reads: the same row read twice can change. Repeatable Read keeps rows you have read stable, but the SQL standard still allows phantoms (new rows matching a re-run range query); InnoDB and PostgreSQL avoid most of them at this level using snapshots, and InnoDB adds next-key locks for locking reads. Serializable also prevents phantoms and write skew. Serializable makes transactions behave as if run one at a time. Defaults differ: InnoDB uses Repeatable Read, PostgreSQL uses Read Committed.
21. How do you use a transaction, and what is a savepoint?
Wrap related statements in START TRANSACTION (or BEGIN) and finish with COMMIT, or ROLLBACK if anything fails. A SAVEPOINT marks a point you can roll back to without abandoning the whole transaction. Two traps: MySQL runs in autocommit mode by default, so each statement outside an explicit transaction commits immediately; and in MySQL most DDL such as ALTER TABLE causes an implicit commit, while PostgreSQL can roll back DDL.
22. What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows one by one, can take a WHERE, fires triggers and is logged per row, so it is slow on big tables. TRUNCATE empties the table quickly, with no WHERE and no delete triggers. DROP removes the table itself, structure and all. In MySQL, TRUNCATE causes an implicit commit and cannot be rolled back, and it resets AUTO_INCREMENT; in PostgreSQL TRUNCATE is transactional and keeps the sequence unless you add RESTART IDENTITY.
Sets, NULLs and views
23. What is the difference between UNION and UNION ALL?
UNION combines result sets and removes duplicates, which requires a sort or hash step. UNION ALL keeps every row and skips that work. If the sides cannot overlap, use UNION ALL. Both need matching column counts and compatible types.
24. How does SQL handle NULL?
NULL means unknown, so any comparison with it, including NULL = NULL, yields unknown, and WHERE keeps only rows that are true. That is why WHERE manager_id = NULL returns nothing; use IS NULL. Aggregates skip NULLs: COUNT(*) counts rows, COUNT(manager_id) counts non-NULL values, and AVG(bonus) averages only people with a bonus; use COALESCE(bonus, 0) if that is not what you mean.
25. What is a view, and is it stored?
A view is a saved query that you can select from like a table. It stores no data; the query runs each time, so it costs the same as writing the query out. Views hide complexity and restrict access, such as exposing employees without salary. Simple views are updatable. A materialised view stores the result and must be refreshed: PostgreSQL has CREATE MATERIALIZED VIEW and REFRESH MATERIALIZED VIEW; MySQL has no materialised views, so teams use a summary table.
How to practise these
Create the two tables locally with a NULL manager, a salary tie and an empty department, and predict each query's output before running it. Edge cases are where interviewers dig, and where knowing the answer isn't enough on its own.
If you are early in your career, pair this with the broader IT interview questions for freshers and the fresher interview preparation guide; SQL often appears inside a Java round too, so see the Java interview questions for freshers. Also read how to handle a question you don't know.
To rehearse out loud, a backend practice round on 99interview with Sia, our AI interviewer, asks one question at a time and follows up on what you actually said; you choose a stack, a difficulty (Beginner, Standard or Senior) and a round length, and get a scored written breakdown at the end. A live 40-minute interview with a working engineer adds scored human feedback.