SQL Query Writing (Part 1) — Interview Questions

How to use this lesson

These are the SQL queries service and product companies ask freshers to write on the spot. Many answer keys online contain subtle bugs: a hard-coded offset, T-SQL syntax passed off as MySQL, or ties ignored. Each question below gives a correct MySQL 8 query, and points out the trap. The sample table:

CREATE TABLE employees (
    id          BIGINT PRIMARY KEY,
    first_name  VARCHAR(50),
    last_name   VARCHAR(50),
    department  VARCHAR(50),
    salary      DECIMAL(12,2),
    manager_id  BIGINT,
    hire_date   DATE
);

Q1. Find the Nth highest salary.

Short answer: Use DENSE_RANK() (MySQL 8+). It handles ties correctly, and N is a parameter.

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = :n;                    -- e.g. :n = 3 → the 3rd highest distinct salary

The LIMIT/OFFSET alternative:

SELECT DISTINCT salary FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;                  -- OFFSET = N - 1: this is the 3rd highest; it returns no row if there are fewer

Common trap: presenting LIMIT 1 OFFSET 2 as "the Nth highest". It only finds the 3rd highest. The offset must be N − 1, and DISTINCT is essential, or duplicate salaries shift the result. Know what ROW_NUMBER / RANK / DENSE_RANK each do with ties.

Learn it in depth → Window Functions

Q2. Find the 2nd highest salary in MySQL.

Short answer: The classic answer is a subquery with MAX:

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

Key points to cover:

• It returns NULL (not "no rows") when there's no second-highest value. Interviewers often want exactly that behaviour, as in the LeetCode version.
• It generalises poorly to N > 2. Switch to DENSE_RANK for the general case.

Q3. Find all employees with duplicate names.

SELECT first_name, last_name, COUNT(*) AS occurrences
FROM employees
GROUP BY first_name, last_name
HAVING COUNT(*) > 1;

To list the full rows of the duplicates:

SELECT e.*
FROM employees e
JOIN (SELECT first_name, last_name FROM employees GROUP BY first_name, last_name HAVING COUNT(*) > 1) d
  ON d.first_name = e.first_name AND d.last_name = e.last_name
ORDER BY e.first_name, e.last_name;

Q4. Find the second-highest salary (another common phrasing).

Short answer: Any of the above works. The ORDER BY/LIMIT form is also common:

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

Key points to cover:

• Say which behaviour you're giving when there's only one distinct salary: the MAX subquery returns NULL, and the LIMIT form returns no row. Showing that you considered it is what interviewers look for.

Q5. Create an empty table with the same structure as another table.

Short answer (MySQL):

CREATE TABLE employees_archive LIKE employees;          -- copies columns, indexes, PK, AUTO_INCREMENT (not FKs)

Portable alternative (copies the columns only, with no indexes or constraints):

CREATE TABLE employees_archive AS SELECT * FROM employees WHERE 1 = 0;

Common trap: the widely copied answer SELECT * INTO new_table FROM old_table WHERE 1 = 2 is SQL Server / T-SQL syntax. In MySQL, SELECT … INTO assigns values to variables or writes to a file. It doesn't create tables.

Q6. Increase every employee's salary by 5%.

UPDATE employees
SET salary = ROUND(salary * 1.05, 2);

Key points to cover:

• Run it inside a transaction, and check the affected row count. In MySQL Workbench, safe-update mode rejects an UPDATE without a key-based WHERE.
• For a subset: WHERE department = 'Sales'.

Q7. Find employees whose name starts with "A".

SELECT first_name, last_name FROM employees WHERE first_name LIKE 'A%';

Key points to cover:

• % matches any number of characters, and _ matches exactly one.
• Case sensitivity depends on the column's collation. MySQL's default utf8mb4_0900_ai_ci is case-insensitive, so 'a%' matches too.
• LIKE 'A%' can use an index on first_name. LIKE '%a' can't.

Q8. Count the employees in department 'ABC'.

SELECT COUNT(*) AS employees_in_abc FROM employees WHERE department = 'ABC';

For every department at once:

SELECT department, COUNT(*) AS headcount FROM employees GROUP BY department ORDER BY headcount DESC;

Q9. Show employees whose first name ends with 'A' and has exactly 6 letters.

SELECT * FROM employees WHERE first_name LIKE '_____a';            -- five underscores + 'a' = 6 characters
-- or, more explicitly:
SELECT * FROM employees WHERE CHAR_LENGTH(first_name) = 6 AND first_name LIKE '%a';

Common trap: writing '_ _ _ _ _ A' with spaces. Spaces in a LIKE pattern are literal characters, so that pattern needs 11 characters. Also, a column named first-name would need backticks (and is a bad name anyway).

Q10. Show employees whose salary is between 10,000 and 50,000.

SELECT * FROM employees WHERE salary BETWEEN 10000 AND 50000;     -- inclusive on both ends

Key points to cover:

• BETWEEN is inclusive. With dates and timestamps, prefer half-open ranges (>= '2026-01-01' AND < '2026-02-01'), so times on the last day aren't lost.

Q11. Fetch duplicate records from a table.

Short answer: Group by the columns that define a duplicate, and keep the groups with more than one row:

SELECT first_name, last_name, department, COUNT(*) AS cnt
FROM employees
GROUP BY first_name, last_name, department
HAVING COUNT(*) > 1;

To delete the duplicates but keep one (the lowest id), in MySQL 8:

DELETE FROM employees
WHERE id IN (
    SELECT id FROM (
        SELECT id, ROW_NUMBER() OVER (PARTITION BY first_name, last_name, department ORDER BY id) AS rn
        FROM employees
    ) t
    WHERE rn > 1
);

Key points to cover:

• The extra derived table (t) is needed because MySQL doesn't allow a subquery to read directly from the table being deleted from.
• Afterwards, add a UNIQUE constraint, so the duplicates can't come back.

Follow-up questions this topic invites — and their answers

Q: ROW_NUMBER vs RANK vs DENSE_RANK? A: For salaries 100, 90, 90, 80: ROW_NUMBER → 1, 2, 3, 4 (unique numbers). RANK → 1, 2, 2, 4 (a gap after ties). DENSE_RANK → 1, 2, 2, 3 (no gaps). Use DENSE_RANK for "Nth highest distinct value".

Q: How do you find the highest salary in each department? A: SELECT department, MAX(salary) FROM employees GROUP BY department. To get the full employee rows, including ties, use RANK() OVER (PARTITION BY department ORDER BY salary DESC) = 1.

Q: How do you find employees who earn more than their manager? A: Use a self-join: SELECT e.first_name FROM employees e JOIN employees m ON m.id = e.manager_id WHERE e.salary > m.salary.

Q: How do you paginate a result? A: ORDER BY id LIMIT 20 OFFSET 40 for page 3. For deep pages on large tables, keyset pagination (WHERE id > :lastSeenId ORDER BY id LIMIT 20) is much faster.