SQL interview questions and answers

The tables used

The practical questions use three SpeedQL tables: employees (staff, with their manager’s id), customers and their orders (customers, orders), and sales (sales).

WHERE or HAVING: which one should filter?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY. So a condition on an aggregate (COUNT, SUM, AVG…) goes in HAVING, and a condition on an ordinary column goes in WHERE, which is also faster.

Review: the HAVING card and the HAVING exercises with solutions.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN keeps only the rows that have a match in both tables. LEFT JOIN keeps every row of the left table and fills the right-hand columns with NULL when there is no match. Use LEFT JOIN when you must not lose any row of the first table, for example to list every customer, including those with no orders.

Review: the JOIN exercises with solutions.

What is the difference between UNION and UNION ALL?

UNION stacks the results of two queries and removes duplicates; UNION ALL keeps every row, duplicates included, and is faster because it does not have to remove them. Here, the 10 employees belong to 3 departments: UNION returns 3 rows, UNION ALL 20.

SELECT COUNT(*)
FROM (SELECT department FROM staff UNION SELECT department FROM staff)
UNION ALL SELECT COUNT(*)
FROM (SELECT department FROM staff UNION ALL SELECT department FROM staff);

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows, all of them or only those matching a WHERE, one by one; TRUNCATE empties the whole table at once, much faster, with no condition allowed; DROP removes the table itself, structure included. SQLite has no TRUNCATE: a DELETE without WHERE does that job.

What are a primary key and a foreign key?

The primary key identifies each row uniquely and cannot be NULL: staff.id, for example. A foreign key is a column that refers to the primary key of another table (or the same one): orders.customer_id points to customers.id, and staff.manager_id to staff.id. It guarantees you cannot create an order for a customer who does not exist.

What is an index for, and when should you create one?

An index is a sorted structure, like the index of a book, that finds rows without reading the whole table. It speeds up WHERE, joins and ORDER BY on the indexed columns, but slightly slows down INSERT and UPDATE and takes up space. So you index the columns you often filter or join on, such as foreign keys.

How does NULL behave, and what does COUNT count?

NULL means “unknown value”: any comparison with NULL, even NULL = NULL, is neither true nor false. So you test IS NULL or IS NOT NULL. COUNT(*) counts every row, while COUNT(column) skips NULLs: here, 10 employees, 9 of whom have a manager.

SELECT COUNT(*) AS rows_count, COUNT(manager_id) AS with_manager
FROM staff;

In what order is a SQL query executed?

The logical order is not the order it is written in: FROM and JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY and finally LIMIT. That is why, in standard SQL, an alias defined in SELECT cannot be used in WHERE, but can be used in ORDER BY.

ROW_NUMBER, RANK or DENSE_RANK: what is the difference?

All three number the rows and only differ on ties. ROW_NUMBER always gives a unique number; RANK gives tied rows the same rank, then skips ranks; DENSE_RANK gives the same rank with no gap. Look at Farid and Iris, tied at 47,000.

SELECT name, salary, ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn, RANK() OVER (ORDER BY salary DESC) AS rk, DENSE_RANK() OVER (ORDER BY salary DESC) AS drk
FROM staff;

Review: the window function exercises with solutions.

What is database normalisation?

It is the way tables are organised to avoid repetition and inconsistencies. In practice: one value per cell (first normal form), every column depends on the whole key (second), and not on another non-key column (third). Instead of repeating the customer’s name and city in every order, you store them once in customers and link orders through customer_id.

How do you find the second highest salary?

The classic answer: take the highest salary among those lower than the maximum. This version handles ties and returns NULL if there is only one salary.

SELECT MAX(salary) AS second_salary
FROM staff
WHERE salary < (SELECT MAX(salary) FROM staff);

Another way, with DISTINCT so the same salary is not counted twice:

SELECT DISTINCT salary
FROM staff
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

How do you find duplicate values in a column?

Group on the column and keep the groups that have more than one row, with HAVING COUNT(*) > 1.

SELECT department, COUNT(*) AS n
FROM staff
GROUP BY department
HAVING COUNT(*) > 1;

How do you list employees who earn more than their manager?

It is a self-join: you join the staff table to itself, once for the employee (e) and once for their manager (m), then compare the two salaries.

SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM staff e
JOIN staff m ON m.id = e.manager_id
WHERE e.salary > m.salary;

How do you find customers who have never ordered?

With a LEFT JOIN, customers without orders have NULL in the orders columns: keep them with IS NULL.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Same result with NOT EXISTS, often more readable and safer than NOT IN when the subquery may contain NULLs:

SELECT name
FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

How do you get the top row of each group?

Number the rows within each group with ROW_NUMBER() OVER (PARTITION BY …), in a CTE, then keep number 1. For the top N, just write n <= N.

WITH r AS (
  SELECT name, department, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS n
  FROM staff)
SELECT department, name, salary
FROM r
WHERE n = 1;

How do you compute a running total and a share of the total?

A window function computes over a set of rows without grouping them. SUM() OVER (ORDER BY …) gives a running total:

SELECT month, SUM(amount) AS total, SUM(SUM(amount)) OVER (ORDER BY month) AS running_total
FROM sales
GROUP BY month
ORDER BY month;

And SUM() OVER () (an empty window) gives the grand total, used to compute a percentage share:

SELECT region, SUM(amount) AS total, ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct
FROM sales
GROUP BY region;