25 SQL Interview Queries for Freshers, With Answers and Output

SQL questions in placement interviews come back to the same few ideas: ranking salaries, joining a table to itself, grouping with HAVING, and window functions. This post works through 25 of them on one small pair of tables, so you can see each query's actual output and check your own answers against it.

Every query below runs on MySQL 8 and PostgreSQL. Where the two differ (dates, text matching), both versions are shown.

The two tables used below

CREATE TABLE department (
  dept_id   INT PRIMARY KEY,
  dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employee (
  emp_id     INT PRIMARY KEY,
  name       VARCHAR(50) NOT NULL,
  email      VARCHAR(100),
  dept_id    INT REFERENCES department(dept_id),
  manager_id INT REFERENCES employee(emp_id),
  salary     INT NOT NULL,
  hire_date  DATE NOT NULL
);

department

employee

Things to notice before you start: Ravi and Meena have the same salary (ties matter in ranking), Legal has nobody in it (matters for joins), and Arjun was entered with Asha's email (a duplicate to find later). Queries 1–23 only read data. Queries 24 and 25 change it, so they come last.

Salaries and ranking

1. Second highest salary

SELECT MAX(salary) AS second_highest
FROM employee
WHERE salary < (SELECT MAX(salary) FROM employee);

Output: 120000. The inner query finds the top salary (150000); the outer query takes the highest salary below it. If every employee earned the same, this returns NULL instead of a wrong number — say that in the interview, it is usually the follow-up question.

2. Nth highest salary (here N = 3)

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employee
) AS ranked
WHERE rnk = 3;

Output: 95000. DENSE_RANK gives tied salaries the same rank and does not skip numbers, so the ranks are 150000 → 1, 120000 → 2, 95000 → 3. The alias AS ranked is required: MySQL refuses a subquery in FROM without one ("Every derived table must have its own alias").

Without window functions, the same answer is SELECT DISTINCT salary FROM employee ORDER BY salary DESC LIMIT 1 OFFSET 2; (the offset is N − 1).

3. Highest salary in each department

SELECT d.dept_name, MAX(e.salary) AS top_salary
FROM employee e
JOIN department d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY d.dept_name;

4. Who earns the highest salary in their department

SELECT e.name, d.dept_name, e.salary
FROM employee e
JOIN department d ON d.dept_id = e.dept_id
WHERE e.salary = (
  SELECT MAX(salary) FROM employee WHERE dept_id = e.dept_id
);

Output: Asha (Engineering, 150000), John (Sales, 95000), Priya (HR, 70000). Query 3 can only give the number; to get the name, compare each row with its own department's maximum. The inner query mentions e.dept_id from the outer query, which makes it a correlated subquery: it runs once per employee.

5. Top 2 earners in each department

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employee
) AS ranked
WHERE rnk <= 2
ORDER BY dept_id, salary DESC;

PARTITION BY dept_id restarts the ranking for each department. Engineering returns three rows because Ravi and Meena tie for second. If the interviewer wants exactly two rows per department, use ROW_NUMBER() instead and say how you break the tie.

6. ROW_NUMBER vs RANK vs DENSE_RANK

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS row_num,
       RANK()       OVER (ORDER BY salary DESC)         AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC)         AS dense_rnk
FROM employee
ORDER BY row_num;

• ROW_NUMBER always counts 1, 2, 3… even for ties. The emp_id tie-breaker decides that Ravi comes before Meena; without it the order of tied rows is not guaranteed.
• RANK gives ties the same number and then skips (2, 2, 4).
• DENSE_RANK gives ties the same number and does not skip (2, 2, 3).

Joining a table to itself

7. Employees who earn more than their manager

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

Output: John (95000) earns more than his manager Kiran (90000). The table is joined to itself: e is the employee row and m is the row of that employee's manager.

8. Every employee with their manager's name

SELECT e.name AS employee,
       COALESCE(m.name, 'No manager') AS manager
FROM employee e
LEFT JOIN employee m ON m.emp_id = e.manager_id;

Output: seven rows; Asha's manager shows as "No manager". With an inner JOIN, Asha would disappear, because there is no row for her manager to match. COALESCE replaces the NULL with readable text.

9. Employees with no manager

SELECT name
FROM employee
WHERE manager_id IS NULL;

Output: Asha. WHERE manager_id = NULL returns nothing, because a comparison with NULL is never true. Use IS NULL / IS NOT NULL.

10. Employees who are managers

SELECT e.name
FROM employee e
WHERE EXISTS (
  SELECT 1 FROM employee r WHERE r.manager_id = e.emp_id
);

Output: Asha, Kiran. EXISTS stops at the first matching report, so it does not matter how many people report to someone.

Grouping and counting

11. Duplicate emails

SELECT email, COUNT(*) AS times
FROM employee
GROUP BY email
HAVING COUNT(*) > 1;

Output: asha@corp.in, 2. Group by the column that should be unique, then keep only the groups with more than one row.

12. Headcount per department, including empty departments

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
GROUP BY d.dept_name
ORDER BY headcount DESC, d.dept_name;

Two details decide whether Legal shows 0: start from department with a LEFT JOIN, and count e.emp_id, not *. COUNT(*) counts the joined row itself, so Legal would show 1.

13. Departments with no employees

SELECT d.dept_name
FROM department d
LEFT JOIN employee e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

Output: Legal. A LEFT JOIN keeps every department; where nobody matched, the employee columns are NULL, and that is what the WHERE picks out.

14. Departments with more than two employees

SELECT dept_id, COUNT(*) AS headcount
FROM employee
GROUP BY dept_id
HAVING COUNT(*) > 2;

Output: departments 1 and 2, three people each. WHERE filters rows before grouping; HAVING filters groups after. A condition on COUNT, SUM or AVG must go in HAVING.

15. Departments whose average salary is above the company average

SELECT dept_id, AVG(salary) AS avg_salary
FROM employee
GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employee);

Output: department 1, average 130000. The company average is 705000 / 7 ≈ 100714; Sales averages about 81667 and HR 70000.

16. Employees who earn more than their department's average

SELECT e.name, e.dept_id, e.salary
FROM employee e
WHERE e.salary > (
  SELECT AVG(salary) FROM employee WHERE dept_id = e.dept_id
);

Output: Asha (above Engineering's 130000), Kiran and John (above Sales' ≈81667). Priya earns exactly HR's average, so she is not included; > and >= give different answers here.

17. Department with the highest total salary bill

SELECT d.dept_name, SUM(e.salary) AS total
FROM employee e
JOIN department d ON d.dept_id = e.dept_id
GROUP BY d.dept_name
ORDER BY total DESC
LIMIT 1;

Output: Engineering, 390000. LIMIT works in MySQL and PostgreSQL. SQL Server uses SELECT TOP 1, and Oracle uses FETCH FIRST 1 ROW ONLY.

18. Hires per year

SELECT EXTRACT(YEAR FROM hire_date) AS hire_year, COUNT(*) AS hires
FROM employee
GROUP BY EXTRACT(YEAR FROM hire_date)
ORDER BY hire_year;

Output: 2019 → 1, 2020 → 2, 2021 → 1, 2022 → 1, 2023 → 1, 2024 → 1. EXTRACT works in both databases; MySQL also has YEAR(hire_date).

19. Count employees by salary band in one row

SELECT
  SUM(CASE WHEN salary < 80000 THEN 1 ELSE 0 END)                  AS below_80k,
  SUM(CASE WHEN salary BETWEEN 80000 AND 120000 THEN 1 ELSE 0 END) AS from_80k_to_120k,
  SUM(CASE WHEN salary > 120000 THEN 1 ELSE 0 END)                 AS above_120k
FROM employee;

This is called conditional aggregation. It turns rows into columns (a pivot) without any special pivot syntax. BETWEEN includes both ends, so the two 120000 salaries fall in the middle band.

Running totals and row tricks

20. Running total of salaries by hire date

SELECT name, hire_date, salary,
       SUM(salary) OVER (ORDER BY hire_date) AS running_total
FROM employee
ORDER BY hire_date;

SUM(...) OVER (ORDER BY ...) adds up every row up to the current one, without collapsing the rows the way GROUP BY would.

21. Every other row (odd-numbered rows)

SELECT emp_id, name
FROM (
  SELECT emp_id, name, ROW_NUMBER() OVER (ORDER BY emp_id) AS rn
  FROM employee
) AS numbered
WHERE MOD(rn, 2) = 1
ORDER BY emp_id;

Output: emp_id 1, 3, 5, 7. A table has no "row 1" until you choose an order, so number the rows with ROW_NUMBER() OVER (ORDER BY …) first, then keep the odd ones. For even rows, use = 0.

Dates and text

22. Employees hired in the last 90 days

MySQL:

SELECT name, hire_date
FROM employee
WHERE hire_date >= CURDATE() - INTERVAL 90 DAY;

PostgreSQL:

SELECT name, hire_date
FROM employee
WHERE hire_date >= CURRENT_DATE - INTERVAL '90 days';

Date arithmetic is where databases differ most. If the interviewer has not named a database, ask which one, or say you are writing MySQL.

23. Names that start with A

SELECT name
FROM employee
WHERE name LIKE 'A%';

Output: Asha, Arjun. % matches any number of characters and _ matches exactly one, so '_r%' finds names whose second letter is r (Arjun, Priya). In MySQL, LIKE usually ignores case (it depends on the column's collation); in PostgreSQL it does not, and you use ILIKE for a case-insensitive match.

Changing data

Run these inside a transaction and check the rows with a SELECT first; an UPDATE or DELETE without the right WHERE changes every row.

24. Give everyone in Sales a 10% raise

UPDATE employee
SET salary = salary * 1.10
WHERE dept_id = (SELECT dept_id FROM department WHERE dept_name = 'Sales');

Kiran goes from 90000 to 99000, John from 95000 to 104500 and Arjun from 60000 to 66000. Looking the department up by name keeps the query correct even if Sales' id is different on another database.

25. Delete duplicate rows, keeping the first

DELETE FROM employee
WHERE emp_id NOT IN (
  SELECT keep_id FROM (
    SELECT MIN(emp_id) AS keep_id
    FROM employee
    GROUP BY email
  ) AS keep
);

This deletes Arjun (emp_id 7), the second row with asha@corp.in, and keeps the lowest id for each email. The extra SELECT keep_id FROM (...) AS keep wrapper is for MySQL, which otherwise refuses to read from the table it is deleting from (error 1093). PostgreSQL accepts it as written.

How to practise these

1. Create the two tables on your laptop (MySQL, PostgreSQL, or an online SQL editor) and paste in the rows above.
2. Cover the answer, write each query yourself, and compare your output with the tables in this post.
3. Then change the data — add a second person to Legal, or make two people share the top salary — and predict what each query returns before you run it. Interviewers like to ask exactly that.

SQL usually comes up inside a technical round, alongside OS, networks and a coding problem. A mock technical interview asks you to explain answers like these out loud, which is harder than writing them. The coding practice covers the programming side.

FAQ