SQL is the one skill that appears in almost every technical interview, whatever the role: developer, tester, data analyst, support engineer or business analyst. It is also the skill where preparation pays off the most, because the questions repeat across companies.

This guide uses one small, realistic database throughout, so every query makes sense. Read each answer, then type the queries yourself in any free online SQL editor. Ten minutes of typing teaches more than an hour of reading.

Our sample database: employees(emp_id, name, dept_id, manager_id, score, joining_date) and departments(dept_id, dept_name). "Score" is a performance score. Interviewers often use a salary column instead; the queries work the same way.

Basics

1. What is SQL? What are DDL, DML, DCL and TCL?

SQL (Structured Query Language) is the standard language for working with relational databases. DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML works with data (SELECT, INSERT, UPDATE, DELETE). DCL controls access (GRANT, REVOKE). TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

2. What are primary key, foreign key and unique key?

A primary key uniquely identifies each row and cannot be NULL; a table has one primary key (which can span several columns). A foreign key links a column to the primary key of another table, enforcing valid references (for example employees.dept_id → departments.dept_id). A unique key prevents duplicate values but, in most databases, allows NULL.

3. What constraints do you know?

NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK (for example CHECK (score >= 0)) and DEFAULT.

4. DELETE vs TRUNCATE vs DROP?

DELETE removes selected rows (with WHERE), can be rolled back inside a transaction and fires triggers. TRUNCATE removes all rows quickly and resets storage; it keeps the table structure. DROP removes the table itself, structure and data. Exact rollback behaviour of TRUNCATE varies by database.

5. WHERE vs HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY, so it can use aggregate functions.

SELECT dept_id, COUNT(*) AS headcount
FROM employees
WHERE joining_date >= '2024-01-01'
GROUP BY dept_id
HAVING COUNT(*) > 5;

6. What is the order of execution of a SELECT query?

Logically: FROM (and JOIN) → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT/OFFSET. This is why you cannot use a SELECT alias inside WHERE in most databases.

Joins

7. Explain the types of joins.

INNER JOIN returns matching rows from both tables. LEFT JOIN returns all rows from the left table, with NULLs where the right has no match. RIGHT JOIN is the reverse. FULL OUTER JOIN returns all rows from both. CROSS JOIN returns every combination. A SELF JOIN joins a table to itself.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

8. Find departments that have no employees.

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

9. Show each employee with their manager’s name (self join).

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.emp_id;

10. Find employees whose score is higher than their manager’s.

SELECT e.name
FROM employees e
JOIN employees m ON e.manager_id = m.emp_id
WHERE e.score > m.score;

Aggregates and grouping

11. Count employees in each department, including empty ones.

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

Note COUNT(e.emp_id), not COUNT(*): COUNT of a column ignores NULLs, so empty departments show 0.

12. COUNT(*) vs COUNT(column) vs COUNT(DISTINCT column)?

COUNT(*) counts all rows; COUNT(column) counts non-NULL values; COUNT(DISTINCT column) counts unique non-NULL values.

13. Find the highest score in each department.

SELECT dept_id, MAX(score) AS top_score
FROM employees
GROUP BY dept_id;

The classic "second highest" family

14. Find the second highest score.

Using a subquery:

SELECT MAX(score) AS second_highest
FROM employees
WHERE score < (SELECT MAX(score) FROM employees);

Using a window function, which also generalises to the Nth highest and handles ties:

SELECT DISTINCT score
FROM (
  SELECT score, DENSE_RANK() OVER (ORDER BY score DESC) AS rnk
  FROM employees
) t
WHERE rnk = 2;

15. Find the top scorer in each department.

SELECT name, dept_id, score
FROM (
  SELECT name, dept_id, score,
         RANK() OVER (PARTITION BY dept_id ORDER BY score DESC) AS rnk
  FROM employees
) t
WHERE rnk = 1;

16. ROW_NUMBER vs RANK vs DENSE_RANK?

For scores 90, 90, 80: ROW_NUMBER gives 1, 2, 3 (always unique); RANK gives 1, 1, 3 (skips after ties); DENSE_RANK gives 1, 1, 2 (no gaps).

Duplicates and NULLs

17. Find duplicate names.

SELECT name, COUNT(*) AS times
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

18. Delete duplicate rows but keep one.

One common approach with a CTE (this runs in PostgreSQL and SQL Server; MySQL may need the subquery wrapped in a derived table):

WITH ranked AS (
  SELECT emp_id,
         ROW_NUMBER() OVER (PARTITION BY name, joining_date ORDER BY emp_id) AS rn
  FROM employees
)
DELETE FROM employees
WHERE emp_id IN (SELECT emp_id FROM ranked WHERE rn > 1);

19. How do you handle NULL?

Use IS NULL / IS NOT NULL, never = NULL. Replace NULLs in output with COALESCE(column, default). Remember that comparisons with NULL return unknown, so rows with NULL can disappear from WHERE filters unexpectedly.

Subqueries, CTEs and set operations

20. Subquery vs correlated subquery?

A normal subquery runs once and its result is used by the outer query. A correlated subquery refers to the outer row and runs for each row:

-- employees scoring above their department average
SELECT name, dept_id, score
FROM employees e
WHERE score > (SELECT AVG(score) FROM employees x WHERE x.dept_id = e.dept_id);

21. What is a CTE?

A Common Table Expression (WITH name AS (...)) is a named temporary result used inside one query. It makes complex queries readable and supports recursion (for example, walking a manager hierarchy).

22. IN vs EXISTS?

Both filter by another query. EXISTS checks whether at least one matching row exists and stops early, which is often efficient for large correlated checks. Be careful with NOT IN when the subquery can return NULL: it can return no rows at all. NOT EXISTS avoids that trap.

23. UNION vs UNION ALL?

UNION combines results and removes duplicates; UNION ALL keeps duplicates and is faster.

Design and performance

24. What is normalisation? Explain 1NF, 2NF and 3NF.

Normalisation organises data to reduce duplication and update errors. 1NF: each column holds single (atomic) values, no repeating groups. 2NF: 1NF plus every non-key column depends on the whole primary key (no partial dependency on part of a composite key). 3NF: 2NF plus no non-key column depends on another non-key column (no transitive dependency); for example, store dept_name in departments, not in every employee row.

25. What is an index? Clustered vs non-clustered?

An index is a structure that speeds up searches on columns, like a book index, at the cost of storage and slower inserts and updates. A clustered index decides the physical order of rows (one per table, often the primary key). A non-clustered index is a separate structure that points to rows (many per table). Exact implementation varies by database.

26. What are ACID properties?

Atomicity (all or nothing), Consistency (rules remain valid), Isolation (concurrent transactions do not interfere incorrectly) and Durability (committed data survives failures). Example: a bank transfer must debit and credit together or not at all.

27. What is a view? A stored procedure?

A view is a saved query that behaves like a virtual table, useful for simplifying and restricting access. A stored procedure is saved SQL logic, with parameters, that runs inside the database.

28. How would you find why a query is slow?

Look at the execution plan (EXPLAIN), check for missing indexes on filtered or joined columns, avoid SELECT *, avoid functions on indexed columns in WHERE, and check whether the query returns far more rows than needed.

Quick-fire questions

  • LIKE patterns: 'A%' starts with A; '%a' ends with a; '_a%' second letter a.
  • BETWEEN is inclusive of both ends.
  • CASE adds conditional logic: CASE WHEN score >= 80 THEN 'High' ELSE 'Normal' END.
  • CHAR vs VARCHAR: fixed length vs variable length.
  • Getting the first N rows differs by database: LIMIT (MySQL, PostgreSQL), TOP (SQL Server), FETCH FIRST (standard SQL, Oracle).

How to practise in one week

DayPractise
1SELECT, WHERE, ORDER BY, LIKE, IN, BETWEEN
2All join types, including self join and finding missing records
3GROUP BY, HAVING, aggregates
4Subqueries and correlated subqueries
5Window functions: ROW_NUMBER, RANK, DENSE_RANK, running totals
6Keys, normalisation, indexes, ACID: explain each aloud
7Mixed mock test: 15 questions in 45 minutes

Related reading: Python Interview Questions · Java Interview Questions · Software Testing Interview Questions · Data Analyst vs Data Scientist · Technical Round in Interview.

Frequently Asked Questions

Which SQL topics are most important for freshers?

Joins (including self join and finding missing records), GROUP BY with HAVING, subqueries, the second highest value question, window functions, keys, normalisation and indexes.

How do I find the second highest value in SQL?

Use MAX with a subquery that excludes the maximum, or use DENSE_RANK in a window function and pick rank 2. The window function method also works for the Nth highest and handles ties.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions.

What is the difference between DELETE and TRUNCATE?

DELETE removes selected rows and can use a WHERE condition; TRUNCATE removes all rows quickly while keeping the table structure.

Is SQL asked in testing and support interviews?

Yes. Testers use SQL to verify data, and support engineers use it to investigate issues, so basic to intermediate SQL is commonly asked.

Which database should I practise on?

Any mainstream database such as MySQL, PostgreSQL or SQL Server. The core SQL is the same; only some syntax such as LIMIT vs TOP differs.