SQL interview questions and answers
SQL comes up in more Indian interviews than any single programming language, because analysts, testers, backend engineers and data engineers all get asked it. It is also the round where people fail for an avoidable reason: they can describe a join perfectly and still freeze when asked to write the query on a whiteboard.
Asked of: Backend, data analyst, data engineer, QA and support engineering roles. Every answer below describes what a strong response covers rather than a script to memorise, because the follow-up question is where these rounds are actually decided.
Joins and filtering
Every join type with an example you can draw in two small tables, and what happens to rows that do not match.
Aggregation and grouping
GROUP BY, HAVING, and how NULL behaves inside each aggregate function. The NULL half is where most answers come apart.
Subqueries, CTEs and window functions
Second-highest-salary style puzzles, running totals, and per-group ranking. Window functions are now expected from about two years in.
Schema design and constraints
Keys, normal forms, and a defensible answer for when you would denormalise on purpose.
Performance
Indexes, the execution plan, and why a query that was fast in your local database crawls in production.
SQL interview questions for freshers
Explain the types of join with an example.
INNER for matches on both sides, LEFT to keep every row from the left table, RIGHT for the mirror image, FULL for both, CROSS for every combination. Draw two four-row tables and show which rows come back as NULL. Drawing it is what earns the mark.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, HAVING filters groups after aggregation. So an aggregate function can appear in HAVING and cannot appear in WHERE.
Write a query to find the second highest salary.
Several answers are correct: a subquery for the max below the max, LIMIT with OFFSET, or DENSE_RANK. Whichever you pick, say out loud what happens when two employees earn the same salary, because that is the follow-up.
Primary key or unique key?
One primary key per table, never NULL, and it identifies the row. A unique constraint enforces uniqueness on any column and tolerates NULL, though how many NULLs are allowed depends on the database, so name the one you use.
DELETE, TRUNCATE or DROP?
DELETE removes rows, takes a WHERE clause and can be rolled back. TRUNCATE empties the table quickly and usually resets auto-increment. DROP removes the table itself. Mention which of them you would ever run on production without a backup, which is none.
What is normalisation? Explain 1NF, 2NF and 3NF.
Atomic values, then no partial dependency on part of a composite key, then no transitive dependency through a non-key column. Give a small example. Then say that reporting tables are often denormalised deliberately, which shows you know it is a trade-off.
How do aggregate functions treat NULL?
COUNT(*) counts rows, COUNT(column) skips NULLs, and SUM and AVG ignore NULLs rather than treating them as zero. This single difference produces more wrong reports in real jobs than any other SQL detail.
Subquery or join?
Both can be correct. Say which reads better for the case in front of you, and mention that a correlated subquery runs per row conceptually, so it is the one to check when a query is slow.
What is an index, and when does it not help?
A separate structure, usually a B-tree, that makes lookups fast and writes slower. It does not help on a column with very few distinct values, and it stops being used entirely if you wrap the column in a function inside your WHERE clause.
UNION or UNION ALL?
UNION removes duplicates, which costs a sort. UNION ALL keeps everything and is faster. Use UNION ALL unless you actually need deduplication.
SQL interview questions for experienced candidates
ROW_NUMBER, RANK or DENSE_RANK?
All three number rows inside a partition. They differ on ties: ROW_NUMBER never repeats, RANK repeats and then skips, DENSE_RANK repeats and does not skip. Ask which behaviour the business actually wants before you pick.
How would you find and delete duplicate rows?
ROW_NUMBER() OVER (PARTITION BY the columns that define a duplicate), then delete where the number is greater than one. On a large table, add that you would do it in batches so you are not holding one enormous transaction.
This query is slow. What do you do first?
Read the execution plan before changing anything. Look for a sequential scan on a large table, and for a big gap between estimated and actual rows, which usually means stale statistics. Then check whether the WHERE clause is even able to use an index.
Explain isolation levels and the anomalies they prevent.
Dirty reads, non-repeatable reads and phantom reads, mapped onto read uncommitted through serializable. Name your database's default. The good answer ends with the trade-off, which is that stricter isolation costs concurrency.
What causes a deadlock and how do you fix it?
Two transactions taking the same locks in a different order. The fix is a consistent ordering and shorter transactions. If you have read a deadlock log in production, describe that instead of the textbook version.
Why did NOT IN return no rows?
Because the subquery returned a NULL, and a comparison against NULL is unknown rather than false. Use NOT EXISTS, or filter the NULLs out. This is a favourite question precisely because it catches people who learned SQL from examples.
When would you use a CTE over a subquery?
Readability first, especially when the same intermediate result is used twice, and recursion for hierarchies such as an employee-manager tree. Add that whether a CTE is materialised depends on the database and version, so you check rather than assume.
Does the column order in a composite index matter?
Yes. An index on (a, b) helps a query filtering on a, or on a and b, and does nothing for one filtering only on b. Explain the leftmost prefix rule in your own words and give one example.
How do you paginate a table with crores of rows?
OFFSET gets slower the deeper you go because the database still walks the skipped rows. Use keyset pagination: remember the last sort key and ask for rows after it. This one question tells an interviewer whether you have worked at scale.
When would you denormalise on purpose?
Read-heavy reporting where the join cost dominates, or a value that is expensive to compute and rarely changes. State the cost you accept in return, which is keeping the copies correct on every write.
- Write the query out, talking as you build it. Interviewers score the order you think in as much as the final answer.
- Say which database you use. Postgres, MySQL and Oracle differ on NULL handling, pagination syntax and string functions, and an unqualified answer sounds memorised.
- Ask how big the table is before you optimise anything. The right answer for ten thousand rows is not the right answer for ten crore.
- Do not claim query tuning experience if you have never opened an execution plan. It is the easiest claim in an interview to test in one follow-up.
Answer them out loud before someone asks
Reading a question and answering it under a follow-up are different skills. Practise these against an AI interviewer that pushes back and scores your answer, or check your resume first with the free ATS resume checker.
Start My Free Mock Interview