SQL Query Writing (Part 2) — Interview Questions

How to use this lesson

The second batch of query questions leans on joins and date and string functions. Two of the source answers here contain classic bugs: LIMIT 1 hides ties, and an INNER JOIN drops customers with zero orders. Knowing why those fail is exactly what separates a strong candidate.

The sample tables:

employees(id, full_name, first_name, department, salary, manager_id, hire_date)
employee_history(employee_id, department_id, from_date, to_date)
accounts(user_id, name)            orders(order_id, user_id, created_at)
project_a(employee_name)           project_b(employee_name)
salaries(employee_id, salary)

Q1. Fetch the top N records by salary.

SELECT id, first_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;                                        -- top 5 rows

To include everyone tied at the cut-off (all employees in the top 5 distinct salaries):

SELECT id, first_name, salary
FROM (SELECT e.*, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees e) t
WHERE rnk <= 5;

Q2. Find all employees who report to a particular manager.

-- by manager id
SELECT id, first_name FROM employees WHERE manager_id = 17;

-- by manager name, using a self-join (safer than a scalar subquery if names aren't unique)
SELECT e.id, e.first_name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE m.full_name = 'Priya Nair';

Common trap: the scalar-subquery answer WHERE manager_id = (SELECT id FROM employees WHERE name = 'X') fails with "Subquery returns more than 1 row" when two managers share a name. Use a join, or IN.

Learn it in depth → SQL Joins

Q3. Extract only the first name from a full-name column.

SELECT full_name,
       SUBSTRING_INDEX(TRIM(full_name), ' ', 1) AS first_name
FROM employees;

Key points to cover:

• SUBSTRING_INDEX(str, ' ', 1) returns everything before the first space, and the whole string if there's no space.
• The common SUBSTRING(full_name, 1, LOCATE(' ', full_name)) answer includes the trailing space, and returns an empty string for single-word names, because LOCATE returns 0 when there's no space.

Q4. Get the employees hired in the last 8 months.

SELECT id, first_name, hire_date
FROM employees
WHERE hire_date >= CURDATE() - INTERVAL 8 MONTH;

Key points to cover:

• Keep the column bare on one side of the comparison, so an index on hire_date can be used. WHERE TIMESTAMPDIFF(MONTH, hire_date, CURDATE()) < 8 can't use the index.

Q5. Retrieve the name of the employee with the maximum salary.

SELECT first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);       -- returns ALL top earners if there's a tie

Common trap: ORDER BY salary DESC LIMIT 1 returns an arbitrary single row when several employees share the top salary. Ask the interviewer whether ties should all be returned.

Q6. Find employees who have worked in more than one department.

SELECT employee_id, COUNT(DISTINCT department_id) AS departments
FROM employee_history
GROUP BY employee_id
HAVING COUNT(DISTINCT department_id) > 1;

Key points to cover:

• DISTINCT matters. An employee who moved away from a department and back again has two history rows for the same department.
• Join to employees if the interviewer wants names.

Q7. Use UNION to list employees who worked on Project A or Project B, without duplicates.

SELECT employee_name FROM project_a
UNION                                   -- removes duplicates (someone on both projects appears once)
SELECT employee_name FROM project_b
ORDER BY employee_name;

Key points to cover:

• UNION ALL would keep people who are on both projects twice.
• The ORDER BY applies to the combined result.

Q8. Fetch the records common to two tables.

-- MySQL 8.0.31+, PostgreSQL, Oracle, SQL Server
SELECT * FROM table1
INTERSECT
SELECT * FROM table2;

-- works on any MySQL version: compare on the identifying columns
SELECT t1.*
FROM table1 t1
WHERE EXISTS (SELECT 1 FROM table2 t2 WHERE t2.id = t1.id AND t2.name = t1.name);

Key points to cover:

• INTERSECT compares all selected columns, and treats NULLs as equal for this purpose. A join on = doesn't match NULLs. Use <=> (MySQL's NULL-safe equality) if NULLs must match.

Q9. Show users who have placed fewer than 3 orders.

Short answer: Use a LEFT JOIN, so that users with zero orders are included, and count a column from the orders table.

SELECT a.user_id, a.name, COUNT(o.order_id) AS order_count
FROM accounts a
LEFT JOIN orders o ON o.user_id = a.user_id
GROUP BY a.user_id, a.name
HAVING COUNT(o.order_id) < 3;

Common trap: writing JOIN (an inner join), which is how most answer keys do it. Users with no orders then disappear completely, although "fewer than 3" obviously includes 0. And count o.order_id, not *: COUNT(*) would count the NULL-filled row as 1 order.

Q10. Show employees with salary > 15,000 when the salary is stored in a separate table.

SELECT e.id, e.first_name, s.salary
FROM employees e
JOIN salaries s ON s.employee_id = e.id
WHERE s.salary > 15000;

Key points to cover:

• If salaries keeps history (several rows per employee), first pick the current row (for example WHERE s.to_date IS NULL, or the latest effective_date), or the join multiplies the results.

Follow-up questions this topic invites — and their answers

Q: How do you find customers who have never placed an order? A: With an anti-join: SELECT a.* FROM accounts a LEFT JOIN orders o ON o.user_id = a.user_id WHERE o.order_id IS NULL, or with NOT EXISTS.

Q: How do you get each department's top 3 earners? A: SELECT * FROM (SELECT e.*, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) r FROM employees e) t WHERE r <= 3.

Q: How would you compute a running total of daily sales? A: With a window function: SUM(amount) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW).

Q: How do you find gaps in a sequence of IDs? A: Compare each ID with the next using LEAD(id) OVER (ORDER BY id), and keep the rows where next_id - id > 1.