SQL Query Interview Questions and Answers

13 questions

Write a query to find the second highest salary.

Take the highest salary below the maximum. It handles ties and returns NULL when there is no second salary.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

The answer is 90000, though two people earn it. The OFFSET form needs DISTINCT, or a tie at the top returns the top salary again:

-- MySQL, PostgreSQL, SQLite: DISTINCT matters when salaries repeat
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

-- SQL Server
SELECT DISTINCT salary FROM employees
ORDER BY salary DESC OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;

It also returns no row, not NULL, when there is only one salary.

Practice it: Second Largest Number

How do you find the Nth highest salary?

Rank the salaries with DENSE_RANK and keep rank N. Ties share a rank without gaps, so N means the Nth distinct value.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT DISTINCT salary FROM ranked WHERE rnk = 3;

The distinct salaries are 120000, 90000, 82000, 70000 and 60000, so the 3rd highest is 82000. Add PARTITION BY dept for the Nth highest per department.

Practice it: Kth Largest Element in an Array

How do you find duplicate rows in a table?

Group by the columns that should be unique and keep groups with more than one row.

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES
  (1, 'asha@example.com'), (2, 'ravi@example.com'), (3, 'asha@example.com'),
  (4, 'ravi@example.com'), (5, 'meera@example.com'), (6, 'asha@example.com');
SELECT email, COUNT(*) AS copies
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY copies DESC;

The asha address appears three times and the ravi address twice. To see the full duplicate rows, filter on COUNT(*) OVER (PARTITION BY email) > 1 in a CTE.

Practice it: Contains Duplicate

How do you delete duplicate rows but keep one copy of each?

Keep the row with the smallest id in each group and delete the rest.

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT);
INSERT INTO users VALUES
  (1, 'asha@example.com'), (2, 'ravi@example.com'), (3, 'asha@example.com'),
  (4, 'ravi@example.com'), (5, 'meera@example.com');
DELETE FROM users
WHERE id NOT IN (SELECT MIN(id) FROM users GROUP BY email);
SELECT id, email FROM users ORDER BY id;

Rows 1, 2 and 5 remain. MySQL rejects this query (error 1093: you cannot select from the table you are deleting from), so use a self join there:

-- MySQL
DELETE u FROM users u
JOIN users keep ON keep.email = u.email AND keep.id < u.id;

Then add a UNIQUE constraint so the duplicates cannot come back.

Practice it: Remove Duplicates from Sorted Array

Write a query to find the highest paid employee in each department.

Rank employees inside each department and keep rank 1.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT name, dept, salary,
         RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT dept, name, salary FROM ranked WHERE rnk = 1 ORDER BY dept;

RANK returns both people if two tie for the top; ROW_NUMBER returns exactly one. Without window functions, join back to SELECT dept, MAX(salary) ... GROUP BY dept.

How do you get the top N salaries in each department?

Same pattern with <= N instead of = 1; here N is 2.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH ranked AS (
  SELECT name, dept, salary,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
  FROM employees
)
SELECT dept, name, salary, rnk
FROM ranked
WHERE rnk <= 2
ORDER BY dept, salary DESC, name;

Engineering returns three people because Ravi and Meera tie at rank 2. DENSE_RANK gives the top 2 salary levels and ROW_NUMBER exactly 2 people, so ask which the interviewer means.

Practice it: Top K Frequent Elements

Find employees who earn more than their manager.

Self join the employees to their managers on manager_id and compare salaries.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
SELECT e.name, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

Priya earns 82000 and her manager John 70000. An inner join is right here: someone with no manager has nobody to compare with.

What does `COUNT(*)` return for an empty department after a `LEFT JOIN`?

SQL

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1);
SELECT d.name, COUNT(*)
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE d.name = 'HR'
GROUP BY d.name;

It prints HR | 1, though HR has no employees: the LEFT JOIN keeps one HR row with NULLs, and COUNT(*) counts it. Count a right-table column instead, since COUNT(e.id) skips NULL:

SQL

CREATE TABLE departments (id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO departments VALUES (1, 'Engineering'), (2, 'HR');
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept_id INTEGER);
INSERT INTO employees VALUES (1, 'Asha', 1), (2, 'Ravi', 1);
SELECT d.name, COUNT(*) AS count_star, COUNT(e.id) AS count_id
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.name
ORDER BY d.name;

HR shows 1 under COUNT(*) and 0 under COUNT(e.id).

How do you fetch alternate (odd or even) rows from a table?

Number the rows with ROW_NUMBER() and keep the odd numbers. Filtering on id % 2 breaks once the ids have gaps.

SQL

CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, manager_id INTEGER);
INSERT INTO employees VALUES
  (1, 'Asha', 'Engineering', 120000, NULL),
  (2, 'Ravi', 'Engineering', 90000, 1),
  (3, 'Meera', 'Engineering', 90000, 1),
  (4, 'John', 'Sales', 70000, 1),
  (5, 'Priya', 'Sales', 82000, 4),
  (6, 'Karan', 'HR', 60000, 1);
WITH numbered AS (
  SELECT name, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees
)
SELECT name FROM numbered WHERE rn % 2 = 1;

The result is Asha, Meera and Priya; use rn % 2 = 0 for the even rows.

How do you swap 'M' and 'F' in a gender column with one UPDATE?

Use one UPDATE with a CASE in the SET, so each row changes exactly once.

SQL

CREATE TABLE people (id INTEGER, name TEXT, gender TEXT);
INSERT INTO people VALUES (1, 'Asha', 'F'), (2, 'Ravi', 'M'), (3, 'Meera', 'F');
UPDATE people
SET gender = CASE gender WHEN 'M' THEN 'F' WHEN 'F' THEN 'M' ELSE gender END;
SELECT name, gender FROM people ORDER BY id;

Two separate updates would turn every row into the same value. ELSE gender keeps any other value instead of setting it to NULL.

Find all numbers that appear at least three times in a row.
Compare each row with its neighbors using LAG and LEAD; if all three match, the number appears three times in a row.
SQL
CREATE TABLE logs (id INTEGER PRIMARY KEY, num INTEGER); INSERT INTO logs VALUES (1, 1), (2, 1), (3, 1), (4, 2), (5, 1), (6, 2), (7, 2); SELECT DISTINCT num FROM ( SELECT num, LAG(num) OVER (ORDER BY id) AS prev, LEAD(num) OVER (ORDER BY id) AS next FROM logs ) AS t WHERE num = prev AND num = next;
Only 1 qualifies; 2 appears twice in a row at the end. A triple self join on id + 1 breaks when ids have gaps. For runs of any length, ROW_NUMBER() OVER (ORDER BY id) - ROW_NUMBER() OVER (PARTITION BY num ORDER BY id) is constant within a run (gaps and islands).
Practice it: Longest Consecutive Sequence
How do you find missing numbers in a sequence of ids?
Generate the full range with a recursive CTE and keep the numbers that are not in the table.
SQL
CREATE TABLE invoices (id INTEGER PRIMARY KEY); INSERT INTO invoices VALUES (1), (2), (4), (5), (8); WITH RECURSIVE seq(n) AS ( SELECT 1 UNION ALL SELECT n + 1 FROM seq WHERE n < (SELECT MAX(id) FROM invoices) ) SELECT n AS missing_id FROM seq WHERE n NOT IN (SELECT id FROM invoices);
The missing ids are 3, 6 and 7. NOT IN is safe here because a primary key is never NULL. PostgreSQL can generate the range with generate_series(1, n).
Practice it: Missing Number

Write a query to find overlapping bookings for the same room.

Two intervals overlap when each starts before the other ends: a.start < b.end AND b.start < a.end. Self join on the room, with a.id < b.id so each pair appears once.

SQL

CREATE TABLE bookings (id INTEGER, room TEXT, start_time TEXT, end_time TEXT);
INSERT INTO bookings VALUES
  (1, 'A', '09:00', '10:00'),
  (2, 'A', '09:30', '11:00'),
  (3, 'A', '11:00', '12:00'),
  (4, 'B', '09:00', '10:00');
SELECT a.id AS booking, b.id AS clashes_with
FROM bookings a
JOIN bookings b
  ON a.room = b.room
 AND a.id < b.id
 AND a.start_time < b.end_time
 AND b.start_time < a.end_time;

Only bookings 1 and 2 clash. Booking 3 starts exactly when 2 ends, which strict < does not count, and booking 4 is in another room.

Practice it: Meeting Rooms, Merge Intervals