SQL practice › SQL interview questions

SQL interview questions with answers: 12 you solve by writing the query

Twelve SQL interview questions, each with its sample data. You answer by writing the query: it runs on a real database in this page and is checked on the spot, on the tables shown and again on changed data, so typing the values in doesn't pass. Under each question is the answer and the explanation you would give the interviewer, including the trap the question is there to test.

These are questions of the kind asked in SQL interviews, from a first filter to window functions. They are not a list leaked from any company, and no page can tell you what one interviewer will ask. The patterns are what carry over.

Skip to the 12 exercises ↓

The tables

Most questions use employees: ten people in three departments. manager_id holds the id of another employee, and the three department heads (Maya, Raj, Nora) have none. Two pairs share a salary: Omar and Lena, Zoe and Kim. Dates are text, year-month-day.

employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08

The join questions use a small shop: five customers and eight orders. Each order stores the id of its customer in customer_id. Eve has never ordered.

customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14

Basics: filter, sort, group

The first questions check that you can describe rows exactly: which ones, in what order, counted how. Their traps give wrong rows without any error, and two of them run below before you start.

Live queryAND before OR: six rows instead of four
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 6 rows
namedepartmentsalary
MayaSales72000
OmarSales58000
LenaSales58000
NoraSupport46000
TheoSupport43000
SamSales75000
Exercise 1Filter and sort

Return the name, department and salary of everyone in Sales or Support who earns less than 60000. Highest salary first; when two salaries are equal, alphabetical by name.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, department, salary
FROM employees
WHERE department IN ('Sales', 'Support') AND salary < 60000
ORDER BY salary DESC, name
Result · 4 rows
namedepartmentsalary
LenaSales58000
OmarSales58000
NoraSupport46000
TheoSupport43000

The filter has two parts: the department is one of two, and the salary is under 60000. The trap is department = 'Sales' OR department = 'Support' AND salary < 60000: AND binds tighter than OR, so that returns all of Sales plus the lower-paid of Support. IN or parentheses fix it, and the second sort key makes the order of Lena and Omar, both on 58000, something you chose and not luck.

Exercise 2Count per group

For each department, return the department, the number of employees and the highest salary.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, COUNT(*), MAX(salary)
FROM employees
GROUP BY department
Result · 3 rows
departmentCOUNT(*)MAX(salary)
Engineering495000
Sales475000
Support246000

GROUP BY department folds the rows into one per department, and COUNT(*) and MAX(salary) are worked out inside each group. Two things to say out loud: a column you select that isn't inside an aggregate belongs in the GROUP BY, and COUNT(*) counts rows while COUNT(column) skips NULLs, so COUNT(manager_id) would miss the three department heads.

Live queryWHERE filters orders, not totals: no rows
Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Result · 0 rows
No rows
Exercise 3WHERE and HAVING in one query

Which departments have hired at least two people since the start of 2021? Return the department and how many of those hires it has.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT department, COUNT(*)
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY department
HAVING COUNT(*) >= 2
Result · 2 rows
departmentCOUNT(*)
Engineering2
Sales3

Two filters at two different moments. WHERE runs before the grouping and picks rows (hired since 2021); HAVING runs after it and picks groups (at least two such rows). The trap is mixing them up: an aggregate in WHERE is an error, and without the HAVING, Support comes back with its single hire.

Joins

Join questions are about which rows survive. An inner join keeps the matches; a LEFT JOIN keeps every row of the first table, which is how you find what has no match at all.

Exercise 4Join two tables

List the orders placed by customers from Denver: the order's id, the customer's name and the amount.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT o.id, c.name, o.amount
FROM orders o
JOIN customers c ON c.id = o.customer_id
WHERE c.city = 'Denver'
Result · 5 rows
idnameamount
101Ava240
103Ava120
104Chloe180
107Ava35
108Chloe120

The city is in customers and the amount in orders, so the two tables have to be joined on the column that links them: orders.customer_id to customers.id. The traps: joining id to id because the names match, and leaving the join condition out, which pairs every order with every Denver customer.

Exercise 5Find what's missing

Return the name and city of every customer who has never placed an order.

Tables: customers, orders
customers
idnamecity
1AvaDenver
2BenAustin
3ChloeDenver
4DanBoston
5EveAustin
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT c.name, c.city
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL
Result · 1 row
namecity
EveAustin

A LEFT JOIN keeps every customer, and those with no order get NULL in the order columns, so WHERE o.id IS NULL leaves exactly them. The traps are = NULL, which is never true, and an inner join, which has already dropped the rows you are looking for. NOT EXISTS with a subquery is an equally good answer; if you use NOT IN, say that a single NULL in the subquery's column makes it return nothing.

The classics

Four questions that come with a known trap each: ties, the wrong grouping, the direction of a self join. The first trap runs below: the query was meant to return the third highest salary.

Live queryOFFSET skips rows, not salaries
Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Result · 1 row
salary
88000
Exercise 6Second highest salary

Return the second highest salary in the company, as one number. If several people share the highest salary, the answer is the next salary below it.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)
Result · 1 row
MAX(salary)
88000

The highest salary below the highest salary: the inner query finds the top, the outer one the best of what is left. SELECT DISTINCT salary … ORDER BY salary DESC LIMIT 1 OFFSET 1 is as good. The trap is OFFSET 1 without DISTINCT: it returns the second row, which is the top salary again whenever two people share it.

The next question uses a contact list where the same person was entered more than once.

contacts
idnameemail
1Ava Reedava@example.com
2Ben Itoben@example.com
3Ava R.ava@example.com
4Cho Parkcho@example.com
5Ben Itoben@example.com
6Dee Vancedee@example.com
7A. Reedava@example.com
Exercise 7Find duplicates

Return each email that appears more than once in contacts, with how many times it appears.

Table: contacts
contacts
idnameemail
1Ava Reedava@example.com
2Ben Itoben@example.com
3Ava R.ava@example.com
4Cho Parkcho@example.com
5Ben Itoben@example.com
6Dee Vancedee@example.com
7A. Reedava@example.com
Ctrl/⌘ + Enter to run
Show the answer
SELECT email, COUNT(*)
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1
Result · 2 rows
emailCOUNT(*)
ava@example.com3
ben@example.com2

Group the rows by the column that should be unique and keep the groups with more than one row. The count only exists after the grouping, so the filter is HAVING, not WHERE. The trap is grouping by the wrong column: by name, Ava Reed, Ava R. and A. Reed look like three different people. The usual follow-up is "which rows would you delete?": every row whose id is not the smallest of its email group.

Exercise 8Top earner in each department

Return the name, department and salary of the highest-paid person in each department. If two people tie for the top salary of a department, return both.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, department, salary
FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = e.department)
Result · 3 rows
namedepartmentsalary
RajEngineering95000
NoraSupport46000
SamSales75000

For each row, the subquery finds the top salary of that row's own department, and the row is kept if its salary equals it. With a window function: RANK() OVER (PARTITION BY department ORDER BY salary DESC) in a subquery, then keep rank 1. The trap is ties: ROW_NUMBER() returns one person per department even when two share the top salary, so say which behavior you chose and why.

Exercise 9Employees who earn more than their manager

Return the name of each employee who earns more than their manager, with the employee's salary and the manager's name.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT e.name, e.salary, m.name
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary
Result · 1 row
namesalaryname
Sam75000Maya

The manager is another row of the same table, so employees is joined to itself under two aliases: e for the employee and m for the manager, matched by m.id = e.manager_id. The trap is the direction of that match: flip it and you get the managers who earn more than their reports. The inner join also drops the three department heads, who have no manager, which is what you want here.

Window functions

A window function computes something over the rows related to the current one (its department, every row up to its date) without folding them into one row the way GROUP BY does. The rule that trips people up: a window function can go in the SELECT list and in ORDER BY, but not in WHERE, so filtering on its result takes a subquery or a CTE.

Exercise 10Above their department's average

Return the name, department and salary of every employee who earns more than the average salary of their own department.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, department, salary
FROM (
  SELECT name, department, salary,
    AVG(salary) OVER (PARTITION BY department) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg
Result · 4 rows
namedepartmentsalary
RajEngineering95000
MayaSales72000
SamSales75000
NoraSupport46000

The window function puts the department's average on every row without folding the rows, and the outer query keeps the rows above it. A correlated subquery in WHERE does the same job. The traps: comparing with the company-wide average, and writing the window function straight into WHERE, which is an error because window functions are computed after WHERE has run.

Exercise 11Running total

For each order, oldest first, return its order_date, its amount and the running total of the amounts up to and including that order.

Table: orders
orders
idcustomer_idproductamountorder_date
1011Desk2402026-01-05
1022Lamp352026-01-09
1031Chair1202026-01-17
1043Monitor1802026-02-02
1052Desk2402026-02-11
1064Lamp352026-02-20
1071Lamp352026-03-03
1083Chair1202026-03-14
Ctrl/⌘ + Enter to run
Show the answer
SELECT order_date, amount,
  SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date
Result · 8 rows
order_dateamountrunning_total
2026-01-05240240
2026-01-0935275
2026-01-17120395
2026-02-02180575
2026-02-11240815
2026-02-2035850
2026-03-0335885
2026-03-141201005

SUM(amount) OVER (ORDER BY order_date) adds up every row from the first date to the current one. The ORDER BY inside OVER is what makes the total run: leave it out and every row gets the grand total. The trap to mention is ties: two orders on the same date would both show the total that includes both, unless you add a tie-breaker such as ORDER BY order_date, id.

Exercise 12Rank with ties and no gaps

Rank everyone by salary, highest first. Return name, salary and rank. People on the same salary share a rank, and the rank after a tie is not skipped: 1, 2, 2, 3.

Table: employees
employees
idnamedepartmentsalarymanager_idhire_date
1MayaSales72000NULL2019-03-11
2OmarSales5800012021-06-01
3LenaSales5800012022-02-14
4RajEngineering95000NULL2018-09-03
5ZoeEngineering8800042020-01-20
6IvanEngineering8100042021-11-08
7NoraSupport46000NULL2020-07-15
8TheoSupport4300072023-04-03
9KimEngineering8800042022-08-22
10SamSales7500012024-01-08
Ctrl/⌘ + Enter to run
Show the answer
SELECT name, salary,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
Result · 10 rows
namesalarysalary_rank
Raj950001
Zoe880002
Kim880002
Ivan810003
Sam750004
Maya720005
Omar580006
Lena580006
Nora460007
Theo430008

DENSE_RANK() gives tied rows the same rank and carries on with the next number. The question is testing whether you can tell the three ranking functions apart: RANK() also ties but then skips (1, 2, 2, 4), and ROW_NUMBER() never ties, so Zoe and Kim would get different numbers for the same salary.

Questions people ask

Are these the SQL questions companies really ask?

They are questions of the kind asked: the second highest salary, duplicates, the top row of each group and "who earns more than their manager" are the well-known classics. Nothing here was taken from a company's question list, and this page can't know what a particular interviewer will ask. What carries over is the pattern behind each question and the trap it hides, which is why every answer explains both.

How do I find the second highest salary in SQL?

Take the highest salary that is lower than the highest: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). Or sort the distinct salaries and skip one: SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1. Without DISTINCT the second form breaks when two people share the top salary. There is a full page on it: second highest salary.

Do these answers work in MySQL, PostgreSQL and SQL Server?

They were run on SQLite only, which is what this page uses. The rules they rely on (WHERE before grouping and HAVING after, no window functions in WHERE) are stated the same way in PostgreSQL's documentation, and the subqueries in FROM carry an alias (t) because the SQL standard asks for one. One difference to expect: SQL Server limits rows with SELECT TOP (1) …, where these queries end in LIMIT 1. Run your answer on the database the job uses before the interview if you can.

Can I use a window function in WHERE?

No. Window functions are allowed in the SELECT list and in ORDER BY only, because they are computed after WHERE, GROUP BY and HAVING have run. To filter on one, compute it in a subquery or a CTE and filter in the outer query, as in the "above their department's average" question.

Is there an SQL test I can take before an interview?

Yes: the free SQL test is ten questions on a real database, from SELECT to JOINs and subqueries, with no sign-up. For practice on one topic at a time, lessons 1 to 4 are free: SELECT and WHERE, GROUP BY, JOINs, and subqueries and CTEs.

Keep going

Ten questions on a real database, from SELECT to JOINs and subqueries. Free, no sign-up.

Related

SQL interview questions for data analystsSQL second highest salary, with tiesSQL query to find duplicatesSQL top N per group, with live queriesSQL JOIN: INNER and LEFT, with live queriesSQL self join: employees and their managersSQL WHERE vs HAVING, with live queriesSQL window functions: OVER and PARTITION BY, live queriesRANK vs DENSE_RANK vs ROW_NUMBER, side by sideSQL running total with SUM() OVERSQL skills testSQL practice online

Written and tested by Nico, who builds SheetDrill (about). Every query on this page was run on SQLite, and every exercise checked against right and wrong answers, before the page was published. Other databases differ in places; where they do, the page says so. Checked against: SQLite: SQL Language Expressions, SQLite: Window Functions, SQLite: Built-in Aggregate Functions, PostgreSQL documentation: Aggregate Functions (tutorial), PostgreSQL documentation: Window Functions (tutorial), PostgreSQL documentation: Table Expressions, Microsoft Learn: TOP (Transact-SQL). Last checked November 2026.