← Back to blog

SQL Interview Questions: 10 You'll Actually Be Asked, With Answers

SQL rounds look endless from the outside: lists of 200 questions, every one slightly different. In practice they come back to about ten patterns. If you can write these from memory and explain why they work, you can handle most of what a fresher or mid-level SQL round throws at you.

Every example below uses the same three tables, so you can focus on the pattern, not the schema:

employees   (id, name, department_id, manager_id, salary, hired_on)
departments (id, name)
orders      (id, customer_id, amount, created_at)

The queries are standard SQL and run on MySQL 8 and PostgreSQL unless a note says otherwise.

1. Find the second-highest salary

The classic opener. The simplest correct answer:

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

It handles ties at the top (two people on the highest salary still gives you the next distinct value) and returns NULL when there's no second value, which is usually what the interviewer wants to hear you mention.

The follow-up is always "now the Nth highest". Reach for DENSE_RANK:

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

The trap: ORDER BY salary DESC LIMIT 1 OFFSET 1 looks right but returns the top salary again if two people share it. Say that out loud before the interviewer does.

2. Highest-paid employee in each department

"Top N per group" is the single most common window-function question.

SELECT department_id, name, salary
FROM (
  SELECT department_id, name, salary,
         RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 1;

Be ready to explain the choice of function. RANK returns everyone tied for first. ROW_NUMBER returns exactly one row per department and picks between ties arbitrarily unless you add a tie-breaker to the ORDER BY. Neither is wrong; the right one depends on what the business question means by "the highest-paid".

3. Departments with no employees

This tests whether you understand what a LEFT JOIN produces when nothing matches:

SELECT d.name
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.id IS NULL;

The trap is a close cousin of this query. Suppose you want every department with its count of well-paid employees, including departments with none:

-- Wrong: the WHERE removes the departments with no match
SELECT d.name, COUNT(e.id)
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
WHERE e.salary > 50000
GROUP BY d.name;

-- Right: filter inside the join
SELECT d.name, COUNT(e.id)
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id AND e.salary > 50000
GROUP BY d.name;

A condition on the right-hand table in WHERE quietly turns a LEFT JOIN back into an INNER JOIN. Interviewers love this one because the wrong query runs without an error.

4. WHERE vs HAVING

"Which departments hired more than five people since 2024?"

SELECT department_id, COUNT(*) AS hires
FROM employees
WHERE hired_on >= '2024-01-01'
GROUP BY department_id
HAVING COUNT(*) > 5;

WHERE filters rows before grouping; HAVING filters groups after. The clearest way to explain it is the logical order a query runs in:

FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

That order also answers the follow-up "why can't I use my column alias in WHERE?": the alias is defined in SELECT, which runs later. (MySQL lets you use an alias in HAVING; PostgreSQL doesn't, so repeating COUNT(*) works everywhere.)

5. Find duplicates, then delete them

Finding them is a GROUP BY:

SELECT email, COUNT(*) AS copies
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Deleting all but one copy is where the dialects differ. Keep the row with the lowest id:

-- MySQL
DELETE c1
FROM customers c1
JOIN customers c2 ON c1.email = c2.email AND c1.id > c2.id;

-- PostgreSQL
DELETE FROM customers c1
USING customers c2
WHERE c1.email = c2.email AND c1.id > c2.id;

Mention that you'd run it as a SELECT first, and inside a transaction on real data. That's the answer of someone who has deleted the wrong rows before.

6. Employees who earn more than their manager

A self-join: the same table, joined to itself under two names.

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;

People without a manager drop out because of the inner join, which is correct here. If the question were "list everyone with their manager, if any", it would be a LEFT JOIN.

7. A running total

SELECT id, created_at, amount,
       SUM(amount) OVER (ORDER BY created_at, id) AS running_total
FROM orders;

The detail worth mentioning: with an ORDER BY and no explicit frame, the window includes every row with the same created_at as the current one. Two orders at the same timestamp would show the same running total. Adding id as a tie-breaker makes each row's total distinct and the output deterministic.

8. Compare each row with the previous one

"Show month-over-month revenue change" is a LAG question:

SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS revenue_change
FROM (
  SELECT DATE_FORMAT(created_at, '%Y-%m') AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY DATE_FORMAT(created_at, '%Y-%m')
) monthly
ORDER BY month;

DATE_FORMAT is MySQL; in PostgreSQL use to_char(created_at, 'YYYY-MM') or date_trunc('month', created_at). The first month's change is NULL, because there's no previous row. LEAD is the same idea looking forward.

9. The NULL questions

Three facts cover most of them:

• = NULL is never true. Use IS NULL. Any comparison with NULL is unknown, not false.
• COUNT(*) counts rows; COUNT(column) skips NULLs. The same goes for SUM and AVG: AVG divides by the number of non-NULL values, not the number of rows.
• NOT IN with a NULL in the list returns nothing. This is the one that catches experienced people:

-- Returns no rows at all if any orders.customer_id is NULL
SELECT id FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);

-- Safe
SELECT c.id FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

10. "Why is this query slow?"

Mid-level rounds often end with performance. The most common planted problem is a function wrapped around an indexed column:

-- Can't use an index on created_at: the function hides the column
WHERE YEAR(created_at) = 2026

-- Can: a plain range on the column itself
WHERE created_at >= '2026-01-01' AND created_at < '2027-01-01'

Two more points worth having ready: a composite index on (department_id, salary) is built for queries that filter on department_id (optionally plus salary); a filter on salary alone generally can't use it well. That's the leftmost-prefix rule (newer MySQL and PostgreSQL versions have a limited "skip scan" workaround, but don't count on it). And EXPLAIN is how you check instead of guessing.

Quick-fire questions for freshers

These come up as one-line checks, often before the query-writing part:

• UNION vs UNION ALL: UNION removes duplicates (and pays for it); UNION ALL keeps everything and is faster.
• DELETE vs TRUNCATE vs DROP: DELETE removes chosen rows and can be filtered; TRUNCATE empties the whole table quickly; DROP removes the table itself.
• Primary key vs unique key: both enforce uniqueness; a table has one primary key and it can't be NULL, while it can have several unique keys.
• Normalization: splitting data so each fact is stored once. Be ready to say why you'd sometimes denormalize on purpose, usually for read performance.

A cheat sheet: the words in the question tell you the tool

How to practice so it sticks

Reading answers feels productive and mostly isn't. What actually builds the skill:

• Write each query before you look at the answer. On paper or in a blank editor, not by editing someone else's.
• Explain it out loud. In a real round you're graded on your reasoning as much as the final query. "I'm using DENSE_RANK because ties should share a rank" is the sentence that separates candidates.
• Revisit what you got wrong, sooner. The pattern you missed last week is the one to see again this week, not the one you already know.

That last point is how SkillLynk's free daily practice drill works. It tracks topics as narrow as SQL joins or window functions, and a topic you miss comes back sooner than one you've got right. When you're ready to practice explaining out loud, an AI voice mock interview asks follow-up questions the way a real interviewer does. And if you have a specific job in mind, Target Interview builds practice from the actual job posting, so a data-heavy role gets data-heavy questions.

Keep reading

Target Interview

DSA Interview Prep That Adapts to the Actual Job You're Interviewing For

Generic DSA prep builds pattern fluency but never matches your target job's real language or domain. Here's how to fix that.

Sep 17, 2026 · 6 min read Daily Coding Challenge

Daily Coding Challenge Practice: How to Actually Build the Habit

Cramming the week before an interview doesn't work. Here's how a daily coding challenge routine builds the habit that does.

Sep 16, 2026 · 6 min read AI Interviews

What an AI Mock Interview Actually Feels Like

The good parts, the awkward parts, and how a live AI interview actually gets graded.

Aug 30, 2026 · 6 min read