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.
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.
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-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.
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
| name | department | salary |
|---|---|---|
| Maya | Sales | 72000 |
| Omar | Sales | 58000 |
| Lena | Sales | 58000 |
| Nora | Support | 46000 |
| Theo | Support | 43000 |
| Sam | Sales | 75000 |
AND is applied before OR, so this reads as: everyone in Sales, or people in Support under 60000. Put parentheses around the two department tests and run it again: four rows.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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT name, department, salary
FROM employees
WHERE department IN ('Sales', 'Support') AND salary < 60000
ORDER BY salary DESC, name| name | department | salary |
|---|---|---|
| Lena | Sales | 58000 |
| Omar | Sales | 58000 |
| Nora | Support | 46000 |
| Theo | Support | 43000 |
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.
For each department, return the department, the number of employees and the highest salary.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT department, COUNT(*), MAX(salary)
FROM employees
GROUP BY department| department | COUNT(*) | MAX(salary) |
|---|---|---|
| Engineering | 4 | 95000 |
| Sales | 4 | 75000 |
| Support | 2 | 46000 |
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.
Table: orders
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
| No rows |
WHERE throws every row away before anything is added up. A condition on a total goes after the grouping: delete the WHERE line, add HAVING SUM(amount) > 250 at the end and run it again. Three customers.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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT department, COUNT(*)
FROM employees
WHERE hire_date >= '2021-01-01'
GROUP BY department
HAVING COUNT(*) >= 2| department | COUNT(*) |
|---|---|
| Engineering | 2 |
| Sales | 3 |
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.
List the orders placed by customers from Denver: the order's id, the customer's name and the amount.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
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'| id | name | amount |
|---|---|---|
| 101 | Ava | 240 |
| 103 | Ava | 120 |
| 104 | Chloe | 180 |
| 107 | Ava | 35 |
| 108 | Chloe | 120 |
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.
Return the name and city of every customer who has never placed an order.
Tables: customers, orders
| id | name | city |
|---|---|---|
| 1 | Ava | Denver |
| 2 | Ben | Austin |
| 3 | Chloe | Denver |
| 4 | Dan | Boston |
| 5 | Eve | Austin |
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
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| name | city |
|---|---|
| Eve | Austin |
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.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
| salary |
|---|
| 88000 |
OFFSET 2 skips two rows, not two salaries. Write SELECT DISTINCT salary and run it again: 81000.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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT MAX(salary)
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees)| 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.
| id | name | |
|---|---|---|
| 1 | Ava Reed | ava@example.com |
| 2 | Ben Ito | ben@example.com |
| 3 | Ava R. | ava@example.com |
| 4 | Cho Park | cho@example.com |
| 5 | Ben Ito | ben@example.com |
| 6 | Dee Vance | dee@example.com |
| 7 | A. Reed | ava@example.com |
Return each email that appears more than once in contacts, with how many times it appears.
Table: contacts
| id | name | |
|---|---|---|
| 1 | Ava Reed | ava@example.com |
| 2 | Ben Ito | ben@example.com |
| 3 | Ava R. | ava@example.com |
| 4 | Cho Park | cho@example.com |
| 5 | Ben Ito | ben@example.com |
| 6 | Dee Vance | dee@example.com |
| 7 | A. Reed | ava@example.com |
Show the answer
SELECT email, COUNT(*)
FROM contacts
GROUP BY email
HAVING COUNT(*) > 1| COUNT(*) | |
|---|---|
| ava@example.com | 3 |
| ben@example.com | 2 |
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.
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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT name, department, salary
FROM employees e
WHERE salary = (SELECT MAX(salary) FROM employees WHERE department = e.department)| name | department | salary |
|---|---|---|
| Raj | Engineering | 95000 |
| Nora | Support | 46000 |
| Sam | Sales | 75000 |
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.
Return the name of each employee who earns more than their manager, with the employee's salary and the manager's name.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
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| name | salary | name |
|---|---|---|
| Sam | 75000 | Maya |
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.
Return the name, department and salary of every employee who earns more than the average salary of their own department.
Table: employees
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
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| name | department | salary |
|---|---|---|
| Raj | Engineering | 95000 |
| Maya | Sales | 72000 |
| Sam | Sales | 75000 |
| Nora | Support | 46000 |
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.
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
| id | customer_id | product | amount | order_date |
|---|---|---|---|---|
| 101 | 1 | Desk | 240 | 2026-01-05 |
| 102 | 2 | Lamp | 35 | 2026-01-09 |
| 103 | 1 | Chair | 120 | 2026-01-17 |
| 104 | 3 | Monitor | 180 | 2026-02-02 |
| 105 | 2 | Desk | 240 | 2026-02-11 |
| 106 | 4 | Lamp | 35 | 2026-02-20 |
| 107 | 1 | Lamp | 35 | 2026-03-03 |
| 108 | 3 | Chair | 120 | 2026-03-14 |
Show the answer
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders
ORDER BY order_date| order_date | amount | running_total |
|---|---|---|
| 2026-01-05 | 240 | 240 |
| 2026-01-09 | 35 | 275 |
| 2026-01-17 | 120 | 395 |
| 2026-02-02 | 180 | 575 |
| 2026-02-11 | 240 | 815 |
| 2026-02-20 | 35 | 850 |
| 2026-03-03 | 35 | 885 |
| 2026-03-14 | 120 | 1005 |
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.
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
| id | name | department | salary | manager_id | hire_date |
|---|---|---|---|---|---|
| 1 | Maya | Sales | 72000 | NULL | 2019-03-11 |
| 2 | Omar | Sales | 58000 | 1 | 2021-06-01 |
| 3 | Lena | Sales | 58000 | 1 | 2022-02-14 |
| 4 | Raj | Engineering | 95000 | NULL | 2018-09-03 |
| 5 | Zoe | Engineering | 88000 | 4 | 2020-01-20 |
| 6 | Ivan | Engineering | 81000 | 4 | 2021-11-08 |
| 7 | Nora | Support | 46000 | NULL | 2020-07-15 |
| 8 | Theo | Support | 43000 | 7 | 2023-04-03 |
| 9 | Kim | Engineering | 88000 | 4 | 2022-08-22 |
| 10 | Sam | Sales | 75000 | 1 | 2024-01-08 |
Show the answer
SELECT name, salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees| name | salary | salary_rank |
|---|---|---|
| Raj | 95000 | 1 |
| Zoe | 88000 | 2 |
| Kim | 88000 | 2 |
| Ivan | 81000 | 3 |
| Sam | 75000 | 4 |
| Maya | 72000 | 5 |
| Omar | 58000 | 6 |
| Lena | 58000 | 6 |
| Nora | 46000 | 7 |
| Theo | 43000 | 8 |
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.