SQL Interview Questions & Answers
The most common SQL interview questions with clear answers — joins, keys, indexes, window functions and more. Practise them live in a free AI mock interview.
Practise these in a free AI mock interview1. What are the different types of JOINs?
INNER (matches in both), LEFT and RIGHT (all rows from one side plus matches), FULL OUTER (all rows from both), and CROSS (every combination). A self-join joins a table to itself.
2. Primary key vs foreign key?
A primary key uniquely identifies each row (unique, not null). A foreign key references another table's primary key, enforcing the relationship and integrity between tables.
3. What is an index and when should you use one?
An index speeds up lookups, filters and sorts on a column, like a book's index. Add them on columns used in WHERE/JOIN/ORDER BY — but not everywhere, since indexes slow writes and use storage.
4. Explain GROUP BY with an example.
GROUP BY collapses rows sharing a value so you can aggregate: SELECT region, SUM(sales) FROM orders GROUP BY region returns total sales per region.
5. What is a window function?
A function that computes across a set of rows related to the current row without collapsing them — ROW_NUMBER(), RANK(), running SUM() OVER (PARTITION BY ... ORDER BY ...). Ideal for rankings and running totals.
6. Difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows (can use WHERE, logged, roll-back-able). TRUNCATE quickly removes all rows with minimal logging. DROP removes the entire table structure.
7. What is a subquery?
A query nested inside another, in SELECT, FROM or WHERE — e.g. find employees earning above the average using (SELECT AVG(salary) ...).
8. WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY. Use WHERE for raw conditions and HAVING for aggregate conditions.
9. How do you find duplicate rows in SQL?
GROUP BY the columns that define a duplicate and use HAVING COUNT(*) > 1 to list the values that repeat.
10. Normalization vs denormalization?
Normalization splits data to remove redundancy (better integrity, more joins). Denormalization combines data to speed reads (fewer joins, some redundancy) — common in reporting and analytics.
11. What is the difference between RANK, DENSE_RANK and ROW_NUMBER?
ROW_NUMBER gives every row a unique number. RANK gives ties the same rank but skips the next number (1, 1, 3). DENSE_RANK gives ties the same rank without skipping (1, 1, 2).
12. What is a CTE?
A Common Table Expression (WITH ... AS) is a named temporary result you reference in the query. It improves readability, lets you build a query in steps, and enables recursion.
13. What is the difference between UNION and UNION ALL?
Both stack result sets with the same columns. UNION removes duplicate rows (slower); UNION ALL keeps everything including duplicates (faster). Use UNION ALL when duplicates cannot occur or you want them.
14. What is a stored procedure?
A saved, reusable set of SQL statements you call by name, often with parameters. It centralises logic, can improve performance, and avoids repeating the same queries.
15. What is a view?
A saved query that behaves like a virtual table. It simplifies complex queries, gives a consistent interface, and can restrict which columns or rows a user sees.
16. What is the difference between CHAR and VARCHAR?
CHAR is fixed-length and pads to its size; VARCHAR is variable-length and stores only what is needed. Use CHAR for fixed codes and VARCHAR for text of varying length.
17. What are aggregate functions?
Functions that compute one value over many rows — COUNT, SUM, AVG, MIN and MAX — usually combined with GROUP BY.
18. What is a self-join?
Joining a table to itself using aliases to relate rows within the same table, for example matching each employee to their manager in an Employees table.
19. What is the logical execution order of a SQL query?
FROM, then WHERE, GROUP BY, HAVING, SELECT, ORDER BY and finally LIMIT. That is why you cannot use a SELECT alias in WHERE but can in ORDER BY.
20. How do you find the second-highest salary?
One way: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or use DENSE_RANK() over salary descending and filter for rank 2.
21. What is a composite key?
A primary key made of two or more columns together, used when no single column uniquely identifies a row — for example order_id plus product_id in an order-lines table.
22. What is a correlated subquery?
A subquery that references the outer query's current row, so it runs once per outer row — for example finding employees who earn more than their own department average.
23. What are ACID properties?
Atomicity, Consistency, Isolation and Durability — the guarantees that make transactions reliable, so a set of operations either all succeed or all fail without corrupting data.
24. How do you improve a slow SQL query?
Read the execution plan, add suitable indexes, avoid SELECT *, filter as early as possible, reduce unnecessary joins and subqueries, and make sure table statistics are up to date.
25. What is the difference between a clustered and a non-clustered index?
A clustered index sorts the table rows physically by its key, so there is one per table. A non-clustered index is a separate structure pointing back to the rows, and a table can have several.
Knowing the answers isn’t enough — say them out loud
Practise these SQL questions in a free AI mock interview: answer by voice, get instant feedback on your strengths and the gaps to fix.
Admissions open · free to apply
Request your admission
Attended a masterclass or have a friend's referral code? You get ₹15,000 off. Fill this and our team takes it from here — pay by cash or online.