SQL Interview Practice

25 Easy + 20 Medium — question, solution, and explanation, one by one

The Schema
Every question below uses this one schema, so concepts build on each other.
employees(employee_id, name, department, salary, manager_id, hire_date)
departments(department_id, department_name, location)
customers(customer_id, name, country, signup_date)
orders(order_id, customer_id, order_date, amount, status)
products(product_id, product_name, category, price)
order_items(order_id, product_id, quantity)
Easy
Easy 1. Return every column for all employees.
Solution
SELECT * FROM employees;
Why it works. SELECT * grabs all columns. Fine for exploring, but in real queries name
columns explicitly so output stays stable if the table changes.

Easy 2. Return only the name and salary of each employee.

Solution
SELECT name, salary FROM employees;
Why it works. You list the exact columns you want, comma-separated. This is the preferred
habit over *.

Easy 3. Employees earning more than 50,000.

Solution
SELECT * FROM employees
WHERE salary > 50000;
Why it works. WHERE filters rows before anything is returned. Only rows where the condition is
true survive.

Easy 4. All employees in the Sales department.

Solution
SELECT * FROM employees
WHERE department = 'Sales';
Why it works. Text comparisons use single quotes. String matching here is exact (and case-
sensitive in some databases like PostgreSQL).

Easy 5. All employees ordered from highest to lowest salary.

Solution
SELECT * FROM employees
ORDER BY salary DESC;
Why it works. ORDER BY sorts the output. DESC = descending; ASC (the default) =
ascending.

Easy 6. The 5 highest-paid employees.

Solution
SELECT * FROM employees
ORDER BY salary DESC
LIMIT 5;
Why it works. ORDER BY first, then LIMIT keeps the top 5. In SQL Server use SELECT TOP
5 ...; in Oracle use FETCH FIRST 5 ROWS ONLY.

Easy 7. List each department once.

Solution
SELECT DISTINCT department FROM employees;
Why it works. DISTINCT collapses duplicate rows in the selected columns into one.

Easy 8. How many employees are there?

Solution
SELECT COUNT(*) AS total_employees FROM employees;
Why it works. COUNT(*) counts all rows. AS renames the output column (an alias) for
readability.

Easy 9. Employees hired after January 1, 2020.

Solution
SELECT * FROM employees
WHERE hire_date > '2020-01-01';
Why it works. Dates compare like numbers when stored as proper date types. Always use the
'YYYY-MM-DD' format to stay unambiguous.

Easy 10. Employees whose name starts with “A”.

Solution
SELECT * FROM employees
WHERE name LIKE 'A%';
Why it works. LIKE does pattern matching. % = any number of characters; _ = exactly one
character. 'A%' means “A followed by anything.”

Easy 11. Employees in Sales or Marketing.

Solution
SELECT * FROM employees
WHERE department IN ('Sales', 'Marketing');
Why it works. IN is shorthand for multiple OR conditions. Cleaner than department = 'Sales' OR
department = 'Marketing'.

Easy 12. Employees with salary between 40,000 and 60,000.

Solution
SELECT * FROM employees
WHERE salary BETWEEN 40000 AND 60000;
Why it works. BETWEEN is inclusive on both ends (40,000 and 60,000 both qualify).

Easy 13. Employees who have no manager.

Solution
SELECT * FROM employees
WHERE manager_id IS NULL;
Why it works. NULL means “unknown/missing.” You cannot test it with = NULL — you must use
IS NULL or IS NOT NULL.

Easy 14. The average salary across the company.

Solution
SELECT AVG(salary) AS avg_salary FROM employees;
Why it works. AVG() is an aggregate function — it collapses many rows into one summary
value. It ignores NULLs.

Easy 15. The highest and lowest salaries.

Solution
SELECT MAX(salary) AS highest, MIN(salary) AS lowest
FROM employees;
Why it works. Multiple aggregates can sit in one SELECT. Each returns a single value over all
rows.

Easy 16. Number of employees in each department.

Solution
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department;
Why it works. GROUP BY splits rows into buckets (one per department); the aggregate then
runs within each bucket. Rule of thumb: any non-aggregated column in SELECT must appear in
GROUP BY.

Easy 17. Total salary paid per department.

Solution
SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;
Why it works. Same grouping idea as above, with SUM() instead of COUNT().

Easy 18. Departments with more than 5 employees.

Solution
SELECT department, COUNT(*) AS num_employees
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;
Why it works. HAVING filters groups after aggregation; WHERE filters rows before it. You can't
put an aggregate in WHERE, which is exactly why HAVING exists.

Easy 19. All customers from the USA.

Solution
SELECT * FROM customers
WHERE country = 'USA';
Why it works. Basic equality filter — same pattern as Q4, on the customers table.

Easy 20. Orders over 100, largest first.

Solution
SELECT * FROM orders
WHERE amount > 100
ORDER BY amount DESC;
Why it works. WHERE runs before ORDER BY: filter the rows, then sort what remains.

Easy 21. Number of orders placed by each customer.

Solution
SELECT customer_id, COUNT(*) AS order_count
FROM orders
GROUP BY customer_id;
Why it works. Group by the customer, count their rows. This is the foundational “count per
entity” pattern.

Easy 22. What distinct order statuses exist?

Solution
SELECT DISTINCT status FROM orders;
Why it works. Quick way to see the set of possible values in a column.

Easy 23. Electronics products, cheapest to most expensive.

Solution
SELECT * FROM products
WHERE category = 'Electronics'
ORDER BY price ASC;
Why it works. Combines a category filter with an ascending price sort.

Easy 24. Each employee's name alongside their department's location.

Solution
SELECT [Link], [Link]
FROM employees e
JOIN departments d
ON [Link] = d.department_name;
Why it works. A JOIN stitches two tables together on a matching condition (ON). Table aliases
(e, d) keep it short. A plain JOIN is an INNER JOIN: only rows that match in both tables are
returned.

Easy 25. Customers who signed up in 2023.

Solution
SELECT * FROM customers
WHERE EXTRACT(YEAR FROM signup_date) = 2023;
Why it works. EXTRACT(YEAR FROM ...) pulls the year out of a date (PostgreSQL).
MySQL/SQL Server: YEAR(signup_date) = 2023. Alternative everywhere: signup_date
BETWEEN '2023-01-01' AND '2023-12-31'.
Medium
Medium 1. Find the second-highest salary.
Solution
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Why it works. The inner subquery finds the top salary; the outer query finds the max of
everything below it. Clean and handles ties. A window-function alternative uses
DENSE_RANK() (see Medium 10).

Medium 2. Employees earning more than the company average.

Solution
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
Why it works. The subquery returns a single number (the average), which the outer WHERE
compares against. The subquery runs once, then each row is checked against that value.

Medium 3. Which department pays the most on average?

Solution
SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY avg_salary DESC
LIMIT 1;
Why it works. Group by department, average within each, sort descending, keep the top one. If
two departments tie, LIMIT 1 arbitrarily picks one — use a window function if ties matter.

Medium 4. Each employee's name and their manager's name.

Solution
SELECT [Link] AS employee, [Link] AS manager
FROM employees e
LEFT JOIN employees m
ON e.manager_id = m.employee_id;
Why it works. A self-join joins a table to itself using two aliases. e is the employee; m is the
same table viewed as the manager. LEFT JOIN keeps employees who have no manager
(manager shows NULL).

Medium 5. Customers who have never placed an order.

Solution
SELECT c.customer_id, [Link]
FROM customers c
LEFT JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;
Why it works. LEFT JOIN keeps all customers, attaching order data where it exists. Customers
with no matching order get NULLs, so filtering WHERE o.order_id IS NULL isolates exactly
those. This “anti-join” pattern is an interview staple.

Medium 6. The highest-paid employee in each department.

Solution
SELECT department, name, salary
FROM (
SELECT department, name, salary,
ROW_NUMBER() OVER (PARTITION BY department
ORDER BY salary DESC) AS rn
FROM employees
) ranked
WHERE rn = 1;
Why it works. ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) numbers rows within
each department by descending salary. The outer query keeps only number 1 per department.
PARTITION BY is like GROUP BY for window functions, but it doesn't collapse rows.

Medium 7. Rank employees by salary within their department.

Solution
SELECT name, department, salary,
RANK() OVER (PARTITION BY department
ORDER BY salary DESC) AS salary_rank
FROM employees;
Why it works. RANK() assigns 1, 2, 2, 4 … leaving gaps after ties. DENSE_RANK() gives 1, 2,
2, 3 (no gaps). ROW_NUMBER() never ties. Knowing the difference between these three is a
very common interview question.

Medium 8. A running total of order amounts ordered by date.

Solution
SELECT order_date, amount,
SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;
Why it works. A windowed SUM with ORDER BY (but no PARTITION BY) accumulates from
the first row up to the current one — a running/cumulative total. Each row keeps its detail and
gains the rolling sum.

Medium 9. For each order, show the previous order's amount and the difference.
Solution
SELECT order_date, amount,
LAG(amount) OVER (ORDER BY order_date) AS prev_amount,
amount - LAG(amount) OVER (ORDER BY order_date) AS diff
FROM orders;
Why it works. LAG() reaches back to the prior row's value (LEAD() looks forward). The first row
has no predecessor, so prev_amount is NULL there. Great for period-over-period change.

Medium 10. Find the 3rd-highest salary.

Solution
SELECT salary
FROM (
SELECT salary,
DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk = 3;
Why it works. DENSE_RANK() ranks salaries (ties share a rank, no gaps), then you filter for the
rank you want. Change = 3 to any N. Using DENSE_RANK rather than ROW_NUMBER means
tied salaries count as the same place, which is usually the intended meaning of “Nth highest.”

Medium 11. Departments whose average salary beats the company-wide average.
Use a CTE.
Solution
WITH dept_avg AS (
SELECT department, AVG(salary) AS avg_sal
FROM employees
GROUP BY department
)
SELECT department, avg_sal
FROM dept_avg
WHERE avg_sal > (SELECT AVG(salary) FROM employees);
Why it works. A CTE (WITH ... AS) is a named temporary result you can reference below, like a
readable variable for a subquery. It computes each department's average, then the main query
compares those against the overall average. CTEs make multi-step logic far easier to read than
nested subqueries.

Medium 12. Label each employee Low / Medium / High by salary.

Solution
SELECT name, salary,
CASE
WHEN salary < 40000 THEN 'Low'
WHEN salary < 70000 THEN 'Medium'
ELSE 'High'
END AS salary_band
FROM employees;
Why it works. CASE is SQL's if/else. It checks WHEN conditions top-down and returns the first
match; ELSE catches everything left over. Conditions are evaluated in order, so the second
WHEN implicitly means 40000–69999.

Medium 13. Total revenue generated by each product.

Solution
SELECT p.product_name,
SUM([Link] * [Link]) AS revenue
FROM order_items oi
JOIN products p
ON oi.product_id = p.product_id
GROUP BY p.product_name
ORDER BY revenue DESC;
Why it works. Join line items to products to get each item's price, multiply quantity by price per
row, then SUM per product. Joining then aggregating is one of the most common real-world
analytics patterns.

Medium 14. Customers whose total spend exceeds 1,000.

Solution
SELECT [Link], SUM([Link]) AS total_spent
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
GROUP BY [Link]
HAVING SUM([Link]) > 1000;
Why it works. Join customers to their orders, sum spend per customer, then HAVING filters to
those over the threshold. The spend filter must be in HAVING, not WHERE, because it's an
aggregate.

Medium 15. Number of orders per month.

Solution
SELECT DATE_TRUNC('month', order_date) AS month,
COUNT(*) AS order_count
FROM orders
GROUP BY DATE_TRUNC('month', order_date)
ORDER BY month;
Why it works. DATE_TRUNC('month', ...) snaps every date down to the first of its month, so all
of January collapses to one bucket (PostgreSQL). MySQL equivalent:
DATE_FORMAT(order_date, '%Y-%m'). Then it's a normal group-and-count.

Medium 16. Employees hired the same year as the employee named 'Alice'.
Solution
SELECT name, hire_date
FROM employees
WHERE EXTRACT(YEAR FROM hire_date) = (
SELECT EXTRACT(YEAR FROM hire_date)
FROM employees
WHERE name = 'Alice'
);
Why it works. The subquery finds Alice's hire year; the outer query returns everyone matching
that year. This assumes exactly one 'Alice' — if there could be several, the subquery might
return multiple rows and you'd need IN instead of =.

Medium 17. Each category's revenue as a percentage of overall revenue.

Solution
SELECT [Link],
SUM([Link] * [Link]) AS category_revenue,
100.0 * SUM([Link] * [Link])
/ SUM(SUM([Link] * [Link])) OVER ()
AS pct_of_total
FROM order_items oi
JOIN products p ON oi.product_id = p.product_id
GROUP BY [Link];
Why it works. The trick: SUM(...) OVER () with an empty OVER() sums across all groups after
the GROUP BY aggregation — giving the grand total on every row to divide by. The 100.0 (not
100) forces decimal division so you don't get integer-rounded zeros.

Medium 18. Find customer names that appear more than once.
Solution
SELECT name, COUNT(*) AS occurrences
FROM customers
GROUP BY name
HAVING COUNT(*) > 1;
Why it works. Group by the column you suspect is duplicated, count each group, and HAVING
COUNT(*) > 1 surfaces only the repeated ones. The standard “detect duplicates” recipe.

Medium 19. 3-order moving average of order amount by date.

Solution
SELECT order_date, amount,
AVG(amount) OVER (
ORDER BY order_date
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
) AS moving_avg_3
FROM orders;
Why it works. The ROWS BETWEEN 2 PRECEDING AND CURRENT ROW frame tells the
window to average the current row plus the two before it — a 3-row sliding window. Adjusting
the frame is how you build moving averages, rolling sums, etc.
Medium 20. For each customer, count orders by status (Completed / Pending /
Cancelled) in separate columns.
Solution
SELECT customer_id,
COUNT(CASE WHEN status = 'Completed' THEN 1 END) AS completed,
COUNT(CASE WHEN status = 'Pending' THEN 1 END) AS pending,
COUNT(CASE WHEN status = 'Cancelled' THEN 1 END) AS cancelled
FROM orders
GROUP BY customer_id;
Why it works. This “pivots” rows into columns. Each CASE returns 1 only for its target status
(NULL otherwise), and COUNT ignores NULLs — so each column counts just that status.
SUM(CASE WHEN ... THEN 1 ELSE 0 END) works identically. Extremely common in analytics
interviews.
Quick reference: order of evaluation
SQL doesn't run top-to-bottom as written. The logical order is:
1. FROM / JOIN — assemble the rows
2. WHERE — filter rows
3. GROUP BY — bucket them
4. HAVING — filter buckets
5. SELECT — pick/compute columns (window functions run here)
6. ORDER BY — sort
7. LIMIT — cut
This explains why you can't use a SELECT alias in WHERE (it doesn't exist yet), and why
aggregate filters go in HAVING.