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.
Joins and set operations
Asked in almost every SQL round, usually with a whiteboard query attached.
Basic
Q1
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.
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
Basic
Q2
What is the difference between WHERE and HAVING?
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.
SELECT customer_id, COUNT(*) AS orders
FROM orders
WHERE status = 'PAID' -- before grouping
GROUP BY customer_id
HAVING COUNT(*) > 3; -- after grouping
Basic
Q3
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.
Basic
Q4
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.
Intermediate
Q5
What is a self join, and when do you need one?
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.
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
The live-coding half. Practise writing these, not reading them.
Intermediate
Q6
Find the second highest salary.
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.
-- 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.
Basic
Q7
Find duplicate rows in a table.
Group by the columns that define a duplicate and keep the groups with more than one row.
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.
Advanced
Q8
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.
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;
Advanced
Q9
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.
Advanced
Q10
What is a correlated subquery?
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
The concept half — where the follow-ups get specific.
Intermediate
Q11
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.
Intermediate
Q12
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.
Advanced
Q13
Why would a query not use an index that exists?
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.
Advanced
Q14
What is the leftmost prefix rule?
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.
Intermediate
Q15
What does ACID stand for?
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.
Basic
Q16
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.
More queries and practical SQL
The second half of a live SQL round.
Advanced
Q17
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.
Advanced
Q18
How do you compute a running total?
A window function with an ordered frame. Before window functions this needed a self-join or a correlated subquery, which is why it is asked.
SELECT created_at, amount,
SUM(amount) OVER (ORDER BY created_at
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM payments;
Advanced
Q19
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.
Basic
Q20
What does COALESCE do, and where do you need it?
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.
Intermediate
Q21
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.
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;
Intermediate
Q22
How would you group rows by day, month or week?
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.
Intermediate
Q23
What is a view, and when would you use one?
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.
Intermediate
Q24
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.
Advanced
Q25
How do you avoid SQL injection?
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.
If any answer above needed more than a paragraph, the structured lessons go deeper: SQL on JBattle Learn.