Guides › Interviews
Top 50 SQL Interview Questions (with Answers)
The 50 SQL questions freshers get asked most — basics, joins, GROUP BY, subqueries, window functions and the classic query puzzles, each with a short answer.
SQL comes up in interviews for almost every fresher role — developer, analyst, tester or support. These are the 50 questions asked most often, grouped from basic to advanced, each with a short answer you can say out loud. For hands-on practice, try the SQL practice problems — they run real queries in your browser.
The examples use two tables: employees(id, name, salary, dept_id, manager_id) and departments(id, name).
Basics (1–12)
1. What is SQL? Structured Query Language — the standard language for creating, reading, updating and deleting data in relational databases.
2. What are DDL, DML, DCL and TCL?
- DDL (definition):
CREATE,ALTER,DROP,TRUNCATE - DML (manipulation):
SELECT,INSERT,UPDATE,DELETE - DCL (control):
GRANT,REVOKE - TCL (transactions):
COMMIT,ROLLBACK,SAVEPOINT
3. What is a primary key?
A column (or set of columns) that uniquely identifies each row. It cannot be NULL, and a table has only one.
4. What is a foreign key? A column that refers to the primary key of another table, enforcing that the referenced row exists (referential integrity).
5. Primary key vs unique key?
Both prevent duplicates. A table has one primary key and it cannot be NULL; it can have many unique keys, which (in most databases) allow NULL.
6. What is a candidate key? A composite key? A candidate key is any minimal set of columns that could be the primary key. A composite key is a key made of more than one column.
7. DELETE vs TRUNCATE vs DROP?
DELETE removes chosen rows (with WHERE) and can be rolled back. TRUNCATE removes all rows quickly, keeps the table structure, and usually resets identity counters. DROP removes the whole table.
8. WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregates like COUNT(*) > 5.
9. What are constraints?
Rules on columns: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT.
10. What does NULL mean?
"Unknown / missing". It is not zero or an empty string. Compare it with IS NULL / IS NOT NULL — = NULL never matches.
11. What is the order of execution of a SELECT?
FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This is why you cannot use a SELECT alias inside WHERE.
12. UNION vs UNION ALL?
UNION removes duplicate rows (slower); UNION ALL keeps them all.
Filtering, sorting and functions (13–20)
13. How do you find names starting with "A"?
SELECT name FROM employees WHERE name LIKE 'A%';
14. IN vs BETWEEN?
IN (a, b, c) matches any value in a list; BETWEEN x AND y matches a range and is inclusive on both ends.
15. What are aggregate functions?
COUNT, SUM, AVG, MIN, MAX — they turn many rows into one value. All except COUNT(*) ignore NULLs.
16. COUNT(*) vs COUNT(column)?
COUNT(*) counts rows; COUNT(column) counts non-null values in that column.
17. How do you remove duplicates from a result?
SELECT DISTINCT dept_id FROM employees;
18. How do you sort by two columns?
ORDER BY dept_id ASC, salary DESC — sorts by department, then by salary inside each department.
19. What does COALESCE do?
Returns the first non-null argument: COALESCE(phone, 'not given').
20. What is a CASE expression?
SQL's if/else:
SELECT name, CASE WHEN salary > 50000 THEN 'High' ELSE 'Normal' END AS band FROM employees;
Joins (21–28)
21. What types of joins are there?
INNER JOIN (matching rows only), LEFT JOIN (all left rows + matches), RIGHT JOIN (all right rows + matches), FULL OUTER JOIN (all rows from both), CROSS JOIN (every combination) and self join.
22. Show each employee with their department name.
SELECT e.name, d.name AS department
FROM employees e JOIN departments d ON d.id = e.dept_id;
23. Find departments with no employees.
SELECT d.name FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;
24. What is a self join? Give an example. Joining a table to itself — for example, employees with their manager's name:
SELECT e.name, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;
25. Find employees who earn more than their manager.
SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;
26. If table A has 3 rows and B has 4, how many rows does a CROSS JOIN return?
12 — every row of A paired with every row of B.
27. Why might a LEFT JOIN return more rows than the left table has?
If a left row matches several right rows, it appears once per match.
28. Join vs subquery — which is better?
Both are often equivalent and optimisers frequently produce the same plan. Joins are usually clearer when you need columns from both tables; EXISTS subqueries read well for "has at least one…" checks.
Grouping and subqueries (29–37)
29. Count employees in each department.
SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id;
30. Departments with more than 5 employees.
SELECT dept_id FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;
31. Second highest salary.
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
32. Nth highest salary (e.g. 3rd).
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC LIMIT 1 OFFSET 2;
33. Employees earning above their department's average.
SELECT e.name FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
This is a correlated subquery — it runs once per outer row.
34. Correlated vs non-correlated subquery? A non-correlated subquery runs once on its own; a correlated one refers to the outer query's row and is evaluated for each row.
35. EXISTS vs IN?
EXISTS stops at the first match and handles NULLs safely; NOT IN returns nothing if the subquery contains a NULL — a classic bug. Prefer NOT EXISTS.
36. Find duplicate emails.
SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1;
37. Delete duplicate rows, keeping the lowest id.
DELETE FROM users
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
(MySQL needs the subquery wrapped in a derived table.)
Window functions (38–43)
38. What is a window function?
A function computed over a set of rows related to the current row, without collapsing them — written with OVER (...).
39. RANK vs DENSE_RANK vs ROW_NUMBER?
For salaries 100, 100, 90: ROW_NUMBER → 1, 2, 3; RANK → 1, 1, 3; DENSE_RANK → 1, 1, 2.
40. Highest-paid employee in each department.
SELECT name, dept_id, salary FROM (
SELECT *, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r
FROM employees
) t WHERE r = 1;
41. Running total of sales by date.
SELECT day, amount, SUM(amount) OVER (ORDER BY day) AS running_total FROM sales;
42. What do LAG and LEAD do?
Read a value from the previous / next row — useful for "change since yesterday".
43. PARTITION BY vs GROUP BY?
GROUP BY returns one row per group; PARTITION BY keeps every row and adds the group-level value beside it.
Design and performance (44–50)
44. What is normalisation? Organising tables to remove redundancy: 1NF (atomic values), 2NF (no partial dependency on part of a composite key), 3NF (no transitive dependency — non-key columns depend only on the key), BCNF (every determinant is a candidate key).
45. When would you denormalise? For read-heavy reporting where joins are too slow — you accept duplicated data for faster reads.
46. What is an index and what does it cost? A data structure (usually a B-tree) that makes lookups fast, like a book index. It costs storage and slows down inserts and updates.
47. Clustered vs non-clustered index? A clustered index decides the physical order of rows (only one per table, usually the primary key). A non-clustered index is a separate structure pointing to rows; you can have many.
48. What are ACID properties? Atomicity (all or nothing), Consistency (rules always hold), Isolation (concurrent transactions don't interfere), Durability (committed data survives crashes).
49. What is a view? A saved query that behaves like a virtual table — handy for reuse and for hiding columns from some users.
50. What is a stored procedure? A trigger?
A stored procedure is named SQL code stored in the database and called explicitly. A trigger runs automatically on INSERT, UPDATE or DELETE.
How to practise
Reading answers is not enough — interviewers usually ask you to write a query. Solve the SQL practice problems (second highest salary, earn more than manager, top earner per department, running revenue and more), then revise theory with DBMS flashcards. The detailed notes on SQL joins with output tables, transactions and ACID and indexing and B+ trees explain the theory answers above in depth, and Top 50 DBMS interview questions covers the non-SQL side. For the complete plan, see the 6-month placement plan.
More guides
- How to Use Codify: A 7-Day Start Plan — New here? Pick a roadmap, make a study plan, start the DSA sheet, revise with notes, practise for placements and test yourself — one step a day, then a daily routine.
- Infosys Preparation Guide: Online Test, Pseudocode and Interview — Roles (SE, DSE, SP), what each section of the Infosys online test checks, how to crack pseudocode and reasoning, a 4-week plan and the interview.
- Wipro Elite NLTH Preparation: Aptitude, Essay and Coding — The Wipro fresher test round by round — quant, logical and verbal, the written essay, two coding problems — plus a 3-week plan and interview tips.
- Accenture Preparation Guide: Cognitive, Technical, Coding and Communication — Every Accenture round explained — cognitive, MS Office/networking/cloud technical MCQs, pseudocode, coding and the spoken-English test — with a 4-week plan.