Common SQL Interview Questions and Answers

SQL interviews tend to reuse the same handful of question types: filtering and sorting, joins, grouping, and at least one question that expects you to reach for a window function or a self-join. This article walks through eight of the most common ones, each with a query that actually runs and a short explanation of why it's written that way — not just what it outputs. It's written for anyone preparing for a data, backend, or analytics interview that includes a SQL round.

The example schema

Every query below uses the same two tables, so you can follow along without re-reading the schema each time:

employees(id, name, department_id, salary, manager_id)
departments(id, name)

manager_id points back to another row's id in the same employees table — a top-level manager has manager_id set to NULL.

Basic queries

How do you find all employees in a department, sorted by salary?

SELECT e.name, e.salary
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering'
ORDER BY e.salary DESC;

The join resolves the department name against the lookup table, WHERE filters down to the department you care about, and ORDER BY ... DESC sorts the result from highest salary to lowest.

How do you count how many employees are in each department, including departments with none?

SELECT d.name AS department, COUNT(e.id) AS employee_count
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.name;

Starting the join from departments with a LEFT JOIN keeps every department in the result even if it has zero employees. COUNT(e.id) (not COUNT(*)) correctly counts 0 for those departments, since COUNT ignores NULL values.

Joins and subqueries

What's the real difference between INNER JOIN and LEFT JOIN?

-- INNER JOIN: only employees that have a matching department
SELECT e.name, d.name AS department
FROM employees e
INNER JOIN departments d ON d.id = e.department_id;

-- LEFT JOIN: every employee; department is NULL if there's no match
SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.department_id;

INNER JOIN drops any row from the left table that has no matching row on the right. LEFT JOIN keeps every row from the left table regardless, filling in NULL for columns that have no match — which is why it's the right choice whenever "no match" is itself a result you need to see.

How do you find employees who earn more than their department's average salary?

-- Option 1: correlated subquery
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department_id = e.department_id
);

-- Option 2: CTE (usually easier to read)
WITH dept_avg AS (
  SELECT department_id, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg a ON a.department_id = e.department_id
WHERE e.salary > a.avg_salary;

Both return the same rows. The correlated subquery re-runs once per outer row, recomputing the department average each time. The CTE computes each department's average exactly once up front and then joins against it — functionally equivalent, but usually clearer to read and easier for the query planner to optimize on larger tables.

Which departments have an average salary above $90,000?

SELECT d.name, AVG(e.salary) AS avg_salary
FROM employees e
JOIN departments d ON d.id = e.department_id
GROUP BY d.name
HAVING AVG(e.salary) > 90000;

WHERE can't filter on an aggregate like AVG(salary) because it runs before grouping happens. HAVING runs after the GROUP BY, so it can filter on the aggregated value directly.

Window functions and advanced queries

How do you find the second-highest salary in the company?

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

DENSE_RANK() assigns rank 1 to the highest salary, rank 2 to the next distinct salary, and so on — without skipping numbers when two people tie for the top spot, which a plain RANK() or a naive LIMIT/OFFSET approach would get wrong.

How do you find duplicate rows in a table?

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

Grouping by the column you suspect has duplicates and filtering with HAVING COUNT(*) > 1 surfaces exactly the values that appear more than once, along with how many times each one does.

How do you look up each employee's manager by name?

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

This is a self-join — the employees table joined to itself, once as e for the employee and once as m for their manager. A LEFT JOIN is used because top-level managers have a NULL manager_id and should still appear in the results, just with a NULL manager name.

For a broader walkthrough of what else to expect in a data-focused technical round, see How to Prepare for a Data Engineer Interview, and if the role also covers distributed data processing, pair this with Common PySpark Interview Questions and Answers. If you want live help structuring answers like these during the actual interview, see what Meetin AI offers or create a free account.

FAQ

Should I memorize these exact queries?

No — memorizing syntax without understanding why it works falls apart the moment the interviewer changes the schema slightly. Focus on understanding what each clause does (why HAVING instead of WHERE, why a window function instead of a subquery) so you can adapt on the spot.

Do I need to know a specific SQL dialect?

These examples use standard ANSI SQL that works across PostgreSQL, MySQL (8+), and most modern engines. Minor syntax differences exist between databases, but the underlying logic in every answer above is portable.

What if I can't remember exact window function syntax under pressure?

Say what you'd do in plain English first — "I'd rank salaries within the whole table and pick the second distinct value" — then write the query. Interviewers usually care more about the reasoning than a syntax-perfect first draft.