# Top SQL Interview Queries — Solved and Explained

Source: https://skills101.in/learn/dbms-sql/sql-interview-queries — Skills101 free tutorial (DBMS & SQL). Updated 2026-11-09.

These queries appear in almost every SQL round for developer, analyst and support roles. Each one is solved on our sample company database.

![RANK vs DENSE_RANK vs ROW_NUMBER on tied salaries — the key to many interview queries below](https://skills101.in/learn-img/dbms-sql/window-rank.svg)

```sql
CREATE TABLE departments (dept_id INT PRIMARY KEY, dept_name VARCHAR(30));
INSERT INTO departments VALUES (10, 'Engineering'), (20, 'Sales'), (30, 'HR'), (40, 'Marketing');
CREATE TABLE employees (emp_id INT PRIMARY KEY, name VARCHAR(30), dept_id INT, salary INT, city VARCHAR(20), manager_id INT, joined DATE);
INSERT INTO employees VALUES
 (1, 'Asha', 10, 95000, 'Jaipur', NULL, '2019-04-01'), (2, 'Ravi', 10, 72000, 'Pune', 1, '2021-07-15'),
 (3, 'Neha', 20, 58000, 'Jaipur', 1, '2020-01-10'), (4, 'Kabir', 20, 58000, 'Delhi', 3, '2022-03-01'),
 (5, 'Om', 30, 45000, 'Pune', 1, '2023-06-20'), (6, 'Meera', 10, 88000, 'Bengaluru', 1, '2018-11-05'),
 (7, 'Arjun', NULL, 40000, 'Delhi', 3, '2024-02-12');
```

## 1. Second highest salary (without window functions)

```sql
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
```

```text
second_highest
88000
```

For the **Nth** highest, use `DENSE_RANK() = N` (see the [window functions lesson](https://skills101.in/learn/dbms-sql/sql-window-functions)), or `ORDER BY salary DESC LIMIT 1 OFFSET N-1` on `SELECT DISTINCT salary`.

## 2. Find duplicate values

*Which salaries appear more than once?*

```sql
SELECT salary, COUNT(*) AS times
FROM employees
GROUP BY salary
HAVING COUNT(*) > 1;
```

```text
salary | times
58000 | 2
```

### Delete duplicates, keeping the lowest id

```sql
CREATE TABLE signups (id INT PRIMARY KEY, email VARCHAR(40));
INSERT INTO signups VALUES (1, 'a@x.in'), (2, 'b@x.in'), (3, 'a@x.in'), (4, 'a@x.in');
DELETE FROM signups
WHERE id NOT IN (SELECT MIN(id) FROM signups GROUP BY email);
SELECT * FROM signups ORDER BY id;
```

```text
id | email
1 | a@x.in
2 | b@x.in
```

## 3. Employees earning more than their department's average

```sql
SELECT name, dept_id, salary FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);
```

```text
name | dept_id | salary
Asha | 10 | 95000
Meera | 10 | 88000
```

## 4. Department-wise count, including empty departments

```sql
SELECT d.dept_name, COUNT(e.emp_id) AS staff
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY staff DESC, d.dept_name;
```

```text
dept_name | staff
Engineering | 3
Sales | 2
HR | 1
Marketing | 0
```

`COUNT(e.emp_id)` (not `COUNT(*)`) gives 0 for Marketing, because the unmatched row has NULL there.

## 5. Highest salary in each department, with the name

```sql
SELECT d.dept_name, e.name, e.salary
FROM employees e
JOIN departments d ON d.dept_id = e.dept_id
WHERE e.salary = (SELECT MAX(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY d.dept_name, e.name;
```

```text
dept_name | name | salary
Engineering | Asha | 95000
HR | Om | 45000
Sales | Kabir | 58000
Sales | Neha | 58000
```

(Both Sales employees are returned because they tie — mention this in the interview!)

## 6. Managers with the number of people reporting to them

```sql
SELECT m.name AS manager, COUNT(*) AS reports
FROM employees e JOIN employees m ON e.manager_id = m.emp_id
GROUP BY m.name
ORDER BY reports DESC;
```

```text
manager | reports
Asha | 4
Neha | 2
```

## 7. Employees who joined in the last N years / a given year

```sql
SELECT name, joined FROM employees
WHERE joined >= '2022-01-01' AND joined < '2024-01-01'
ORDER BY joined;
```

```text
name | joined
Kabir | 2022-03-01
Om | 2023-06-20
```

(A date range keeps the query index-friendly — better than `YEAR(joined) IN (2022, 2023)`.)

## 8. Percentage of payroll per department

```sql
SELECT dept_id, SUM(salary) AS payroll,
       ROUND(100.0 * SUM(salary) / (SELECT SUM(salary) FROM employees), 1) AS pct
FROM employees
GROUP BY dept_id
ORDER BY payroll DESC;
```

```text
dept_id | payroll | pct
10 | 255000 | 55.9
20 | 116000 | 25.4
30 | 45000 | 9.9
NULL | 40000 | 8.8
```

`100.0 *` forces decimal division (integer division would give 0 in some databases).

## How to answer SQL questions in interviews

1. **Restate** the question with the tables and columns.
2. Say the **approach** first ("group by department, then filter with HAVING").
3. Mention **edge cases**: ties, NULLs, empty groups.
4. Write clean SQL with aliases and line breaks.

## Common mistakes

- Using `COUNT(*)` after a LEFT JOIN when you want zeros for empty groups.
- Forgetting ties in "highest per group" questions.
- Integer division in percentages.

## Quick recap

- Second highest: MAX below MAX, or DENSE_RANK.
- Duplicates: GROUP BY … HAVING COUNT(*) > 1; delete keeping MIN(id).
- Above-average: correlated subquery.
- Include empty groups: LEFT JOIN + COUNT(column).


Practice quiz and free certificate: https://skills101.in/learn/dbms-sql/sql-interview-queries