SQL Interview Questions and Answers with Examples
Short answer: SQL interviews combine definitions with live query writing. Expect questions on joins, keys, grouping, indexes, normalization and transactions. Practice explaining why a query works, not only writing it.
Basics
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes rows, optionally with a WHERE clause, and can usually be rolled back. TRUNCATE removes all rows quickly and keeps the table structure. DROP removes the table itself.
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping, and HAVING filters groups after aggregation.
What is a primary key and a foreign key?
A primary key uniquely identifies each row and cannot be NULL. A foreign key is a column that refers to a primary key in another table and keeps related data consistent.
Joins
Be ready to describe each join with an example.
- INNER JOIN: only matching rows.
- LEFT JOIN: all rows from the left table plus matches.
- FULL JOIN: all rows from both tables.
- CROSS JOIN: every combination of rows.
A common task is finding customers with no orders: SELECT c.id FROM customers c LEFT JOIN orders o ON o.customer_id = c.id WHERE o.id IS NULL;
Aggregation and subqueries
How do you find the second highest salary?
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Window functions such as DENSE_RANK() handle ties more cleanly.
How do you find duplicates?
SELECT email, COUNT(*) FROM users
GROUP BY email HAVING COUNT(*) > 1;
Indexes and performance
What is an index?
A data structure, often a B-tree, that lets the database find rows without scanning the whole table. It speeds up reads and adds cost to writes.
How would you speed up a slow query?
- Read the execution plan, for example with EXPLAIN.
- Add or adjust indexes on filtered and joined columns.
- Select only the columns you need.
- Avoid functions on indexed columns in WHERE clauses.
Design and transactions
What is normalization?
Organizing tables to reduce repeated data. First normal form removes repeating groups, second removes partial dependencies and third removes transitive dependencies.
What are ACID properties?
Atomicity (all or nothing), Consistency (rules stay valid), Isolation (transactions do not disturb each other) and Durability (committed data survives failures).
NULL and tricky points
- NULL means unknown, so
NULL = NULLis not true. UseIS NULL. COUNT(*)counts rows, whileCOUNT(column)skips NULLs.- UNION removes duplicates, while UNION ALL keeps them and is faster.
Window function example
To rank employees by salary inside each department, use SELECT name, department, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank_in_dept FROM employees;. PARTITION BY restarts the ranking for every department, and the ranking functions differ on ties: RANK leaves gaps, DENSE_RANK does not and ROW_NUMBER never repeats.
Tips for the live round
- Repeat the question and ask about the table structure.
- Start with a simple query, then refine it.
- Talk through edge cases such as NULLs and ties.
- Mention how you would check performance on large tables.
Frequently asked questions
Which SQL database should I practice on?
Any of PostgreSQL, MySQL or SQLite works for interview practice, since the core syntax is the same.
What is a view?
A saved query that behaves like a virtual table, useful for simplifying complex queries or limiting access to columns.
What is a window function?
A function that calculates across related rows without collapsing them, such as ROW_NUMBER or RANK.
