SQL interview questions, with queries and answers

A JOIN combines rows from two tables on a related column.

• INNER JOIN — only rows that match in both tables
• LEFT JOIN — every row from the left table, plus matches from the right (NULLs where there's no match)
• RIGHT JOIN — the mirror image of a LEFT JOIN
• FULL OUTER JOIN — every row from both tables, matched where possible (MySQL lacks it; combine a LEFT and a RIGHT JOIN with UNION instead)
• CROSS JOIN — every row of one table paired with every row of the other
• Self join — a table joined to itself, such as employees to their managers

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id;

Two common answers:

-- Works on every database
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- With a window function (MySQL 8+, PostgreSQL); change 2 to N for the Nth highest
SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

Mention the edge cases: if everyone earns the same, the first query returns NULL; and DENSE_RANK treats equal salaries as one rank, so it returns the true second-highest value even when the top salary is shared.

WHERE filters rows before they're grouped; HAVING filters groups after GROUP BY, so it can use aggregate functions.

SELECT department_id, AVG(salary) AS avg_salary
FROM employees
WHERE status = 'active'          -- filters rows
GROUP BY department_id
HAVING AVG(salary) > 50000;      -- filters groups

You can't write WHERE AVG(salary) > 50000, because the average doesn't exist yet when WHERE runs.

Group by the columns that define a duplicate, and keep the groups with more than one row:

SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

To delete duplicates but keep one row of each, number the copies with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) and delete the rows whose number is greater than 1.

• DELETE removes the rows you choose with WHERE, one at a time, firing any triggers. It can be rolled back inside a transaction.
• TRUNCATE removes all rows at once, much faster, and usually resets auto-increment counters. It's treated as a DDL statement: in MySQL it commits immediately and can't be rolled back, while in PostgreSQL and SQL Server it can be rolled back inside a transaction.
• DROP removes the whole table — data, structure and indexes — from the database.

• Primary key — uniquely identifies each row. It can't be NULL, and a table has only one (though it can span several columns).
• Unique key — also prevents duplicate values, but a table can have several, and most databases allow NULLs in it.
• Foreign key — a column that refers to the primary key of another table and ensures the referenced row exists. For example, orders.customer_id refers to customers.id.

Normalisation organises tables to remove redundant data and the update problems it causes.

• 1NF — every column holds a single (atomic) value, with no lists inside a cell, and every row is unique.
• 2NF — 1NF, and every non-key column depends on the whole primary key, not just part of a composite key.
• 3NF — 2NF, and non-key columns depend only on the key, not on other non-key columns (no transitive dependencies).

For example, storing a customer's city on every order row breaks 3NF; move it to a customers table. In practice, read-heavy systems sometimes denormalise on purpose for speed.

An index is a separate structure, usually a B-tree, that lets the database find rows without scanning the whole table — like the index at the back of a book. It speeds up reads on the indexed columns, but slows down writes a little and takes up space.

A clustered index determines the physical order of the rows themselves, so a table can have only one; in MySQL's InnoDB engine, it's the primary key. A non-clustered index is a separate structure that points to the rows, and a table can have many.

Both combine the results of two queries that return the same columns. UNION removes duplicate rows, which costs an extra sort or hashing step. UNION ALL keeps every row and is faster. Use UNION ALL unless you actually need the duplicates removed.

Window functions calculate across a set of rows related to the current row without collapsing them into one row, the way GROUP BY does. For salaries of 100, 90, 90 and 80:

• ROW_NUMBER() gives 1, 2, 3, 4 — always unique
• RANK() gives 1, 2, 2, 4 — ties share a rank, followed by a gap
• DENSE_RANK() gives 1, 2, 2, 3 — ties share a rank, with no gap

SELECT name, salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

Rank the employees within each department using PARTITION BY:

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 1;

RANK returns everyone tied for the top salary; use ROW_NUMBER instead if you need exactly one row per department.

A subquery is a query nested inside another. A normal subquery runs once, and the outer query uses its result. A correlated subquery refers to the current row of the outer query, so it's evaluated once per row:

-- Employees who earn more than their department's average
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(salary)
  FROM employees
  WHERE department_id = e.department_id
);

Correlated subqueries can be slow on large tables; a join against a grouped subquery, or a window function, is often faster.

A view is a saved query you can select from as if it were a table. It doesn't store data itself — unless it's a materialised view — so each time you query it, the underlying query runs. Views simplify complicated joins, hide columns some users shouldn't see, and give other code a stable interface even if the tables underneath change.

ACID describes how a database keeps transactions reliable:

• Atomicity — all the statements in a transaction succeed, or none of them do; a money transfer debits and credits, or does neither.
• Consistency — a transaction takes the database from one valid state to another, respecting every constraint.
• Isolation — transactions running at the same time don't see each other's half-finished work (to a degree set by the isolation level).
• Durability — once a transaction is committed, it survives a crash.

Not in the order you write it. Logically, the clauses run in this order: FROM (and joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT. That's why you can't use a column alias from SELECT inside WHERE — it doesn't exist yet — but you can use it in ORDER BY.

COUNT(*) counts every row, including rows with NULLs. COUNT(column) counts the rows where that column isn't NULL. COUNT(DISTINCT column) counts the different non-NULL values. In a table of 10 rows where 3 have no email, COUNT(*) is 10 and COUNT(email) is 7.