SQL rounds are the most predictable part of a fresher interview: a handful of concepts, and one or two queries to write on the spot. These are the questions that come up, with the queries written out — because being able to describe a join is not the same as being able to write one under pressure.
What is the difference between INNER JOIN and LEFT JOIN?
INNER JOIN returns only rows with a match on both sides. LEFT JOIN returns every row from the left table, and fills the right-hand columns with NULL where there is no match. If the question is "customers who have never ordered", you need the LEFT JOIN — an inner join deletes exactly the rows you are looking for.
SQL
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL; -- kept only the rows with no match
WHERE filters rows before grouping; HAVING filters groups after aggregation. So a condition on a raw column belongs in WHERE, and a condition on COUNT(*) or SUM(...) belongs in HAVING. Putting the row filter in HAVING usually still works and is slower, because you aggregated rows you were about to discard.
SQL
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'PAID' -- before grouping
GROUP BY customer_id
HAVING COUNT(*) > 3; -- after grouping
What is the difference between UNION and UNION ALL?
UNION removes duplicate rows, which requires a sort or hash over the whole result. UNION ALL concatenates and keeps everything. When you know the inputs are disjoint, UNION ALL is the same answer for meaningfully less work.
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows one at a time, can carry a WHERE clause, fires triggers and can be rolled back. TRUNCATE deallocates the table's pages in one operation — far faster, no WHERE, and it resets identity counters. DROP removes the table itself, structure included.
A join of a table to itself, using two aliases, to relate rows within one table — an employee to their manager, a record to its predecessor. It is a hierarchy question in disguise, which is why it is asked.
SQL
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
Queries you will be asked to write
5 questions
The live-coding half. Practise writing these, not reading them.
The safe answer handles ties and an empty result. DENSE_RANK is the clearest, and works when several employees share the top salary; the OFFSET version is shorter but returns the second row, not the second distinct salary.
SQL
-- Handles ties correctly
SELECT DISTINCT salary
FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees) ranked
WHERE rnk = 2;
-- Shorter, but "second row" not "second distinct salary"
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC OFFSET 1 LIMIT 1;
The follow-upExpect "now make it the Nth" and "what if two people share the top salary?" — which is exactly what separates the two queries above.
Group by the columns that define a duplicate and keep the groups with more than one row.
SQL
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
The follow-upThen: delete the duplicates but keep one. Use ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete where the number is greater than 1.
What are window functions, and how do they differ from GROUP BY?
GROUP BY collapses rows into one per group. A window function computes across a set of rows but keeps every row — so you can show each order next to its customer's total, which GROUP BY cannot do in one pass. ROW_NUMBER, RANK, DENSE_RANK, LAG and LEAD are the ones worth knowing.
SQL
SELECT id, customer_id, total,
SUM(total) OVER (PARTITION BY customer_id) AS customer_total,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at) AS nth_order
FROM orders;
What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
For values 100, 100, 90: ROW_NUMBER gives 1, 2, 3 — always distinct. RANK gives 1, 1, 3 — ties share a rank and the next value skips. DENSE_RANK gives 1, 1, 2 — ties share a rank and nothing is skipped.
A subquery that references a column from the outer query, so it is evaluated once per outer row rather than once in total. It is easy to read and often slow; most correlated subqueries can be rewritten as a join or a window function, and doing so is a good thing to volunteer.
Design, indexes and transactions
6 questions
The concept half — where the follow-ups get specific.
What is normalisation, and what do 1NF, 2NF and 3NF mean?
Organising columns so each fact is stored exactly once, to remove insert, update and delete anomalies. 1NF: every cell holds one value, no repeating groups. 2NF: in 1NF, and no non-key column depends on only part of a composite key. 3NF: in 2NF, and no non-key column depends on another non-key column.
The follow-upThen: is 3NF always right? For transactional systems, usually. Read-heavy and analytical systems often denormalise on purpose to avoid joins on a hot path — a deliberate trade with a plan for keeping copies consistent.
How does an index make a query faster, and what does it cost?
An index is a B-tree kept sorted on the indexed column with pointers to the rows, so the database navigates to matching rows in logarithmic time instead of scanning every row. The cost is that every INSERT, UPDATE and DELETE must maintain it, plus the disk it occupies. Faster reads, slower writes.
Most often: the column is wrapped in a function (WHERE YEAR(created_at) = 2026 — rewrite it as a date range); the LIKE pattern starts with a wildcard; the types on either side of the comparison do not match; the predicate skips the index's leftmost column; or the query would return so much of the table that a scan is genuinely cheaper. The last one is the optimiser being right.
The follow-upThen: how would you check? EXPLAIN ANALYZE — it reports the plan actually used, not the one you assumed.
An index on (a, b, c) is sorted by a, then b within equal a, then c. It can serve predicates on a, on a and b, or on all three — but not on b alone, exactly as a phone book sorted by surname cannot find people by first name.
Atomicity — a transaction happens entirely or not at all. Consistency — it moves the database from one valid state to another, constraints intact. Isolation — concurrent transactions do not see each other's partial work. Durability — once committed, it survives a crash.
The follow-upThen: name an isolation problem. A dirty read is seeing another transaction's uncommitted change; a phantom read is a second run of the same query returning new rows.
What is the difference between a primary key and a unique key?
Both enforce uniqueness. A primary key additionally forbids NULL and there is only one per table; a unique constraint allows NULLs (how many depends on the database) and you can have several. The primary key is the row's identity; a unique key is a business rule.
What is the difference between IN, EXISTS and JOIN for a membership test?
All three can express "rows that have a match". EXISTS stops at the first match per row and handles NULLs correctly. IN with a subquery is clear and equivalent in most planners — but NOT IN silently returns no rows if the subquery contains a NULL, which is the trap. A JOIN can duplicate rows when the match is not unique, so use it when you also need columns from the other table.
How do you paginate results, and why is OFFSET a problem at scale?
LIMIT n OFFSET m is the simple answer, and it degrades: the database still reads and discards every skipped row, so page 10,000 is slow. Keyset pagination fixes it — remember the last row's sort key and use WHERE created_at < :last, which stays fast at any depth but cannot jump to an arbitrary page.
It returns the first non-NULL argument. It matters because NULL propagates through arithmetic and comparisons — NULL + 5 is NULL, and x = NULL is never true. COALESCE(discount, 0) is how a missing value stops silently erasing a total.
How do you write conditional logic inside a query?
A CASE expression. Combined with an aggregate it becomes conditional counting, which is how you pivot rows into columns without a pivot feature.
SQL
SELECT customer_id,
COUNT(*) AS total,
COUNT(CASE WHEN status = 'PAID' THEN 1 END) AS paid,
COUNT(CASE WHEN status = 'FAILED' THEN 1 END) AS failed
FROM orders
GROUP BY customer_id;
Truncate the timestamp and group by that — DATE_TRUNC('month', created_at) in Postgres, DATE_FORMAT in MySQL. Keep the filtering on the raw column as a range, though: wrapping the column in a function in the WHERE clause is what stops the index being used.
A named query that behaves like a table. It is useful for hiding a complicated join behind a simple name and for restricting which columns a role can see. It is computed on every read; a materialised view stores the result and must be refreshed, trading freshness for speed.
What is a stored procedure, and why is it controversial?
Precompiled SQL stored in the database, callable by name. It saves round trips and centralises logic close to the data. The objection is that business logic in the database is harder to version, test and review than application code, so most teams keep procedures for data-heavy operations only.
Parameterised queries, always — the driver sends the SQL and the values separately, so a value can never be parsed as SQL. String concatenation with escaping is not equivalent and eventually fails. An ORM gives you this by default; the risk returns the moment someone builds a raw query by concatenation.
Learn it properly firstIf any answer needed more than a paragraph, the structured lessons go deeper.
Most candidates know the material and still stumble when asked out loud. Take a SQL mock interview where the interviewer follows up on what you actually say.