Top 10 SQL interview questions

Three standard answers; know at least two.

-- 1. Subquery
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- 2. DENSE_RANK (generalises to Nth highest, handles ties)
SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) t WHERE r = 2;

-- 3. LIMIT / OFFSET
SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1;

Mention edge cases: ties at the top (use DISTINCT or DENSE_RANK), and returning NULL when there is no second salary (the MAX version does that naturally). For "highest salary per department", add PARTITION BY department_id.

Using employees(id, name, dept_id) and departments(id, name):

• INNER JOIN: only employees whose department exists.
• LEFT JOIN: all employees; department columns NULL if not matched.
• RIGHT JOIN: all departments; employee columns NULL for empty departments.
• FULL OUTER JOIN: everything from both sides.
• CROSS JOIN: every employee paired with every department.
• SELF JOIN: the table joined to itself, e.g. employees with their managers:

SELECT e.name AS employee, m.name AS manager
FROM employees e LEFT JOIN employees m ON m.id = e.manager_id;

Interview tip: say that a filter on the right-hand table placed in WHERE turns a LEFT JOIN into an INNER JOIN; put it in the ON clause to keep unmatched rows.

• WHERE filters individual rows before grouping and can't use aggregates.
• HAVING filters groups after GROUP BY and usually uses aggregates.

SELECT dept_id, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'          -- rows
GROUP BY dept_id
HAVING AVG(salary) > 50000;      -- groups

Filtering in WHERE when possible is faster because fewer rows reach the grouping step.

Find duplicates:

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Delete duplicates but keep one (the lowest id):

-- PostgreSQL / standard
DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users
  ) t WHERE rn > 1
);

-- Self-join version (works in MySQL)
DELETE u1 FROM users u1
JOIN users u2 ON u1.email = u2.email AND u1.id > u2.id;

Then prevent it from happening again with a UNIQUE constraint on email.

Window functions calculate across a set of related rows (the "window") while keeping each row, unlike GROUP BY, which collapses rows. Syntax: fn() OVER (PARTITION BY … ORDER BY …).

For scores 95, 90, 90, 85:

Other common ones: SUM() OVER (ORDER BY date) for running totals, LAG/LEAD for comparing with the previous or next row, NTILE(4) for quartiles, AVG() OVER (… ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) for moving averages.

An index is a separate data structure (usually a B-tree) that keeps a column's values sorted with pointers to the rows, so the database can jump to matching rows instead of scanning the whole table: O(log n) instead of O(n).

Use on columns frequently used in WHERE, JOIN, ORDER BY and GROUP BY on large tables, and for uniqueness.

Trade-offs: every INSERT/UPDATE/DELETE must also update the index (slower writes), and indexes take disk space. Low-selectivity columns (like a boolean) often aren't worth indexing.

Know: clustered (defines the table's physical order, one per table) vs non-clustered (separate structure); composite indexes follow the leftmost-prefix rule; wrapping a column in a function (WHERE YEAR(created_at) = 2026) prevents normal index use; check with EXPLAIN.

Normalisation organises tables so each fact is stored once, avoiding update, insert and delete anomalies.

• 1NF: each column holds atomic values; no repeating groups or lists in a cell.
• 2NF: in 1NF, and every non-key column depends on the whole primary key (no partial dependency on part of a composite key).
• 3NF: in 2NF, and no transitive dependency: non-key columns depend only on the key. Example: storing dept_name in employees violates 3NF, because it depends on dept_id, not on the employee.
• BCNF: a stricter 3NF where every determinant is a candidate key.

Denormalisation adds controlled redundancy for read performance (reporting tables, cached counts); it's a deliberate trade-off.

A transaction groups statements into one unit of work. ACID:

• Atomicity: all or nothing (a bank transfer debits and credits together, or neither happens).
• Consistency: the database moves from one valid state to another; constraints hold.
• Isolation: concurrent transactions don't interfere; controlled by isolation levels (Read Committed, Repeatable Read, Serializable).
• Durability: once committed, data survives crashes (thanks to the write-ahead log).

BEGIN;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;   -- or ROLLBACK on error

Type: DELETE is DML; TRUNCATE and DROP are DDL.

WITH sales AS (
  SELECT p.category, p.name, SUM(o.qty * o.price) AS revenue
  FROM order_items o JOIN products p ON p.id = o.product_id
  GROUP BY p.category, p.name
),
ranked AS (
  SELECT *, DENSE_RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS r
  FROM sales
)
SELECT category, name, revenue
FROM ranked
WHERE r <= 3
ORDER BY category, revenue DESC;

Walk the interviewer through it: aggregate first, rank within each category with a window function, then filter, because you can't use a window function directly in WHERE. Clarify ties: DENSE_RANK may return more than 3 rows when products tie; use ROW_NUMBER if exactly 3 are required.