Classic SQL interview queries
55 min · Advanced
Need help? Ask a tutor on WhatsApp (opens in a new tab)Placement tests and interviews return again and again to a few SQL questions on an employee table: Nth highest salary, duplicates, employee versus manager. The interviewer checks that you handle ties and NULLs, and can give a second approach.
Nth highest salary: LIMIT/OFFSET, correlated subquery and DENSE_RANK
DENSE_RANK() is a window function that numbers rows by their sort order and gives equal values the same number without skipping any number afterwards: 1, 2, 2, 3. It is the cleanest way to answer "Nth highest salary".
The salaries are 95000, 90000, 75000 (three people), 65000, 60000 and 50000, so the 3rd highest distinct salary is 75000. The SQL finds it three ways.
Example 1 · Third highest salary, three ways
Runs in: MySQL 8
CREATE TABLE emp (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT, manager_id INT);INSERT INTO emp VALUES (1, 'Asha', 'IT', 90000, NULL), (2, 'Bhavna', 'IT', 75000, 1), (3, 'Chetan', 'IT', 95000, 1), (4, 'Deepa', 'HR', 60000, NULL), (5, 'Elan', 'HR', 65000, 4), (6, 'Farah', 'Sales', 75000, NULL), (7, 'Gopal', 'Sales', 50000, 6), (8, 'Hari', 'IT', 75000, 1);-- 1) LIMIT / OFFSET on distinct salariesSELECT DISTINCT salary FROM emp ORDER BY salary DESC LIMIT 1 OFFSET 2;-- 2) Correlated subquery: exactly 2 distinct salaries are higherSELECT DISTINCT salary FROM emp e1WHERE 2 = (SELECT COUNT(DISTINCT salary) FROM emp e2 WHERE e2.salary > e1.salary);-- 3) DENSE_RANK: also lists who earns itSELECT name, salary FROM ( SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM emp) ranked WHERE rnk = 3 ORDER BY name+--------+ | salary | +--------+ | 75000 | +--------+ +--------+ | salary | +--------+ | 75000 | +--------+ +--------+--------+ | name | salary | +--------+--------+ | Bhavna | 75000 | | Farah | 75000 | | Hari | 75000 | +--------+--------+
What this code does
- All three agree on 75000. OFFSET 2 skips the two higher distinct salaries; the subquery counts how many distinct salaries beat each candidate; DENSE_RANK gives every 75000 earner rank 3.
- Without DISTINCT,
LIMIT 1 OFFSET 2would return the third row of 95000, 90000, 75000, 75000, ... which happens to be right here but fails when the top salaries repeat.
Three standard answers: SELECT DISTINCT salary ... ORDER BY salary DESC LIMIT 1 OFFSET N-1 (simple, MySQL and PostgreSQL syntax); a correlated subquery that keeps the salary with exactly N − 1 distinct salaries above it (works everywhere, but O(n²)); and DENSE_RANK() (MySQL 8+), which also returns every employee at that salary. Without DISTINCT, the LIMIT version counts duplicates and gives the wrong answer. Revise: Ranking window functions (opens in a new tab).
Predict the output
Ties and the Nth highest
Work out the result before you pick.
Round 1 of 3
What does this query return?
Finding and deleting duplicate rows
Duplicate rows are rows that repeat the values that should be unique, such as the same email registered twice. Find them with GROUP BY on those columns and HAVING COUNT(*) > 1.
An applicants table has the same email registered twice. The SQL first finds the duplicates, then deletes every copy except the oldest.
Example 2 · Finding and deleting duplicate emails
Runs in: MySQL 8
CREATE TABLE applicant (id INT PRIMARY KEY, email VARCHAR(40), name VARCHAR(20));INSERT INTO applicant VALUES (1, 'priya@c.edu', 'Priya'), (2, 'rahul@c.edu', 'Rahul'), (3, 'priya@c.edu', 'Priya S'), (4, 'arun@c.edu', 'Arun'), (5, 'priya@c.edu', 'Priya'), (6, 'rahul@c.edu', 'Rahul K');-- findSELECT email, COUNT(*) AS copies FROM applicant GROUP BY email HAVING COUNT(*) > 1 ORDER BY email;-- delete every row that has an older row with the same emailDELETE a FROM applicant a JOIN applicant b ON a.email = b.email AND a.id > b.id;SELECT id, email, name FROM applicant ORDER BY id;-- stop it happening againALTER TABLE applicant ADD CONSTRAINT uq_email UNIQUE (email)+-------------+--------+ | email | copies | +-------------+--------+ | priya@c.edu | 3 | | rahul@c.edu | 2 | +-------------+--------+ +----+-------------+-------+ | id | email | name | +----+-------------+-------+ | 1 | priya@c.edu | Priya | | 2 | rahul@c.edu | Rahul | | 4 | arun@c.edu | Arun | +----+-------------+-------+
What this code does
- The join pairs each row with every older row of the same email; rows 3, 5 and 6 have an older twin and are deleted, keeping ids 1, 2 and 4.
- Window version:
DELETE FROM applicant WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM applicant) t WHERE rn > 1); the extra derived table is needed because MySQL does not let a DELETE read its own table directly in a subquery.
To delete them while keeping one copy, keep the row with the smallest id: a self join DELETE a FROM t a JOIN t b ON a.email = b.email AND a.id > b.id deletes every row that has an older twin. The window-function version numbers rows per email with ROW_NUMBER() and deletes those with a number above 1. Afterwards, add a UNIQUE constraint so duplicates cannot come back. Revise: Delete and truncate (opens in a new tab).
Fix the error
Fix the query error
Read the real MySQL error, then pick the line that fixes it.
Round 1 of 2
List the emails that appear more than once.
Error
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE copies > 1' at line 4
Employees earning more than their manager (self join)
A self join joins a table to itself using two aliases, so one row can be compared with another row of the same table, such as an employee with their manager.
The emp table stores each employee's manager_id in the same table. To compare an employee's salary with the manager's, the SQL uses the table twice under two aliases.
Example 3 · Employees earning more than their manager
Runs in: MySQL 8
CREATE TABLE emp (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT, manager_id INT);INSERT INTO emp VALUES (1, 'Asha', 'IT', 90000, NULL), (2, 'Bhavna', 'IT', 75000, 1), (3, 'Chetan', 'IT', 95000, 1), (4, 'Deepa', 'HR', 60000, NULL), (5, 'Elan', 'HR', 65000, 4), (6, 'Farah', 'Sales', 75000, NULL), (7, 'Gopal', 'Sales', 50000, 6), (8, 'Hari', 'IT', 75000, 1);SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salaryFROM emp eJOIN emp m ON e.manager_id = m.emp_idWHERE e.salary > m.salary;
SELECT e.name AS employee, COALESCE(m.name, '(none)') AS managerFROM emp e LEFT JOIN emp m ON e.manager_id = m.emp_idORDER BY e.emp_id+----------+--------+---------+----------------+ | employee | salary | manager | manager_salary | +----------+--------+---------+----------------+ | Chetan | 95000 | Asha | 90000 | | Elan | 65000 | Deepa | 60000 | +----------+--------+---------+----------------+ +----------+---------+ | employee | manager | +----------+---------+ | Asha | (none) | | Bhavna | Asha | | Chetan | Asha | | Deepa | (none) | | Elan | Deepa | | Farah | (none) | | Gopal | Farah | | Hari | Asha | +----------+---------+
What this code does
- The same table plays two roles through the aliases e and m. Chetan earns 95000 against Asha's 90000, and Elan 65000 against Deepa's 60000.
- The LEFT JOIN keeps Asha, Deepa and Farah, who have no manager; COALESCE shows "(none)" instead of NULL.
The alias e is the employee and m the manager, linked by e.manager_id = m.emp_id; then e.salary > m.salary compares them. An inner join drops employees with no manager (manager_id is NULL), which is right for this question. For "list each employee with their manager's name, including those without one", use a LEFT JOIN so they stay with a NULL manager. Revise: Self join (opens in a new tab).
Predict the output
Employee versus manager
Work out the result before you pick.
Round 1 of 2
Which employee earns more than their manager?
Department-wise max salary and counts with GROUP BY/HAVING
HAVING filters groups after GROUP BY has aggregated them, while WHERE filters single rows before grouping. "Departments with more than 2 employees" needs HAVING, because the count exists only after grouping.
A report needs one row per department with its head count and top salary. The SQL also shows how to get the name of the top earner, not just the number.
Example 4 · Department-wise counts, maximums and the top earner
Runs in: MySQL 8
CREATE TABLE emp (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT, manager_id INT);INSERT INTO emp VALUES (1, 'Asha', 'IT', 90000, NULL), (2, 'Bhavna', 'IT', 75000, 1), (3, 'Chetan', 'IT', 95000, 1), (4, 'Deepa', 'HR', 60000, NULL), (5, 'Elan', 'HR', 65000, 4), (6, 'Farah', 'Sales', 75000, NULL), (7, 'Gopal', 'Sales', 50000, 6), (8, 'Hari', 'IT', 75000, 1);SELECT dept, COUNT(*) AS staff, MAX(salary) AS max_salary, ROUND(AVG(salary)) AS avg_salaryFROM emp GROUP BY dept ORDER BY dept;
SELECT dept, COUNT(*) AS staff FROM emp GROUP BY dept HAVING COUNT(*) > 2;
SELECT e.dept, e.name, e.salaryFROM emp eJOIN (SELECT dept, MAX(salary) AS top FROM emp GROUP BY dept) d ON e.dept = d.dept AND e.salary = d.topORDER BY e.dept+-------+-------+------------+------------+ | dept | staff | max_salary | avg_salary | +-------+-------+------------+------------+ | HR | 2 | 65000 | 62500 | | IT | 4 | 95000 | 83750 | | Sales | 2 | 75000 | 62500 | +-------+-------+------------+------------+ +------+-------+ | dept | staff | +------+-------+ | IT | 4 | +------+-------+ +-------+--------+--------+ | dept | name | salary | +-------+--------+--------+ | HR | Elan | 65000 | | IT | Chetan | 95000 | | Sales | Farah | 75000 | +-------+--------+--------+
What this code does
- HAVING filters after grouping, so only IT (4 people) passes
COUNT(*) > 2; WHERE could not use COUNT(*). - The last query joins each department's maximum back to the table to get the name. If two people tied for the top, both would appear, which is usually what the interviewer wants.
GROUP BY makes one result row per group, and aggregate functions (COUNT, SUM, AVG, MAX, MIN) summarise each group. Selecting name next to MAX(salary) is not valid in standard SQL, and MySQL's default ONLY_FULL_GROUP_BY mode rejects it. The top earner needs the maximum per department joined back to the table, a correlated subquery, or RANK() OVER (PARTITION BY dept ...). Revise: WHERE versus HAVING (opens in a new tab).
Fix the error
Fix the grouping error
Read the real MySQL error, then pick the line that fixes it.
Round 1 of 2
Show the highest salary in each department.
Error
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'shop.emp.name' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
Second highest without LIMIT; DELETE vs TRUNCATE vs DROP
TRUNCATE removes all rows at once by recreating the table. It takes no WHERE, resets AUTO_INCREMENT and in MySQL commits at once, so it cannot be rolled back. DELETE removes chosen rows and can be rolled back; DROP removes the table itself.
Two more classics: the second highest salary without LIMIT, and the difference between DELETE, TRUNCATE and DROP. The SQL runs all of them on a small table.
Example 5 · Second highest without LIMIT, and DELETE vs TRUNCATE vs DROP
Runs in: MySQL 8
CREATE TABLE emp (emp_id INT PRIMARY KEY, name VARCHAR(20), dept VARCHAR(10), salary INT, manager_id INT);INSERT INTO emp VALUES (1, 'Asha', 'IT', 90000, NULL), (2, 'Bhavna', 'IT', 75000, 1), (3, 'Chetan', 'IT', 95000, 1), (4, 'Deepa', 'HR', 60000, NULL), (5, 'Elan', 'HR', 65000, 4), (6, 'Farah', 'Sales', 75000, NULL), (7, 'Gopal', 'Sales', 50000, 6), (8, 'Hari', 'IT', 75000, 1);SELECT MAX(salary) AS second_highest FROM empWHERE salary < (SELECT MAX(salary) FROM emp);
CREATE TABLE log_entry (id INT AUTO_INCREMENT PRIMARY KEY, msg VARCHAR(20));INSERT INTO log_entry (msg) VALUES ('a'), ('b'), ('c');START TRANSACTION;DELETE FROM log_entry WHERE id > 1;ROLLBACK;SELECT COUNT(*) AS rows_after_rollback FROM log_entry;TRUNCATE TABLE log_entry;INSERT INTO log_entry (msg) VALUES ('d');SELECT id, msg FROM log_entry;DROP TABLE log_entry;SELECT * FROM log_entry+----------------+ | second_highest | +----------------+ | 90000 | +----------------+ +---------------------+ | rows_after_rollback | +---------------------+ | 3 | +---------------------+ +----+------+ | id | msg | +----+------+ | 1 | d | +----+------+ ERROR 1146 (42S02): Table 'college.log_entry' doesn't exist
What this code does
- The subquery finds 95000; the outer query takes the largest salary below it, 90000. With a one-row table, MAX over no rows returns NULL.
- The DELETE was rolled back, so 3 rows remain. TRUNCATE emptied the table and reset AUTO_INCREMENT, so the next row gets id 1. After DROP the table no longer exists, hence error 1146.
Second highest without LIMIT: take the maximum of the salaries below the overall maximum, SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp). It returns NULL, not an empty result, when there is no second salary.
Compare the three removal statements:
- DELETE: DML, WHERE allowed, rollback possible, slower for whole tables.
- TRUNCATE: DDL in MySQL, no WHERE, resets AUTO_INCREMENT, fast, implicit commit.
- DROP: DDL, removes data, structure, indexes and constraints.
- TRUNCATE fails on a table referenced by a foreign key from another table (in InnoDB).
Predict the output
Rollback and ids
Work out the result before you pick.
Round 1 of 3
How many rows does log_entry have at the end?
Next step
Check your understanding
12 quick questions