15 SQL Subquery Practice Problems with Solutions

Solution

SELECT *
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Explanation

The subquery SELECT AVG(salary) FROM employees runs first and returns a single number — the average salary. The outer query then uses that number as the filter threshold, keeping only employees whose salary exceeds it. This is the classic introductory subquery pattern — using an aggregate from the same table as a dynamic filter value.

Solution

SELECT product_name, category, price_usd
FROM products
WHERE price_usd > (
  SELECT MAX(price_usd)
  FROM products
  WHERE category = 'Accessories'
);

Explanation

The subquery finds the maximum price within the Accessories category. The outer query then returns all products — from any category — priced above that value. Notice the subquery filters by category while the outer query does not, so the results can include products from any category that exceed the Accessories price ceiling.

Solution

SELECT order_id, customer_id, total_usd
FROM orders
WHERE total_usd > (SELECT AVG(total_usd) FROM orders);

Explanation

The subquery calculates the average order total across all orders. The outer query then filters to only orders that exceed that average. Because the subquery references the same table as the outer query, this pattern is sometimes called a self-referencing subquery.

Solution

SELECT *
FROM rental_listings
WHERE price_per_night < (
  SELECT MIN(price_per_night)
  FROM rental_listings
  WHERE city = 'London'
);

Explanation

The subquery finds the cheapest London listing. The outer query then returns all listings — from any city — priced below that value. This means the results may include listings from London itself if they somehow undercut the minimum, but more typically will surface cheaper listings in other cities.

Solution

SELECT first_name, last_name, department, salary
FROM employees
WHERE salary > (
  SELECT MAX(salary)
  FROM employees
  WHERE department = 'Support'
);

Explanation

The subquery returns the maximum salary within the Support department. The outer query then finds all employees — from any department — who out-earn even the highest-paid Support employee. The results will not include Support employees themselves since no one in that department can exceed their own department's maximum.

Solution

SELECT order_id, customer_id, total_usd
FROM orders
WHERE customer_id IN (
  SELECT customer_id
  FROM customers
  WHERE city = 'New York'
);

Explanation

The subquery returns a list of customer IDs for all customers in New York. The outer query then uses IN to keep only orders where the customer_id appears in that list. This achieves the same result as a JOIN but using a subquery instead — a common pattern when you only need data from one of the tables in the final result.

Solution

SELECT product_name, category
FROM products
WHERE product_id NOT IN (
  SELECT product_id
  FROM order_items
);

Explanation

The subquery returns all product IDs that appear in the order_items table — meaning they have been ordered at least once. NOT IN then excludes those products from the outer query results, leaving only products that have never appeared in an order. This is a very common real-world pattern for finding unmatched records.

Solution

SELECT first_name, last_name, department
FROM employees
WHERE department IN (
  SELECT department
  FROM employees
  WHERE job_title = 'Manager'
);

Explanation

The subquery returns a list of departments that contain at least one Manager. The outer query then returns all employees — regardless of their own job title — who work in one of those departments. This includes the managers themselves as well as all their colleagues in the same department.

Solution

SELECT first_name, last_name, email
FROM customers
WHERE customer_id NOT IN (
  SELECT customer_id
  FROM orders
);

Explanation

The subquery returns all customer IDs that appear in the orders table — customers who have placed at least one order. NOT IN excludes those customers, leaving only customers with no orders at all. This is a clean alternative to a LEFT JOIN with a NULL check, and often easier to read.

Solution

SELECT *
FROM rental_listings
WHERE city IN (
  SELECT city
  FROM rental_listings
  WHERE rating > 4.8
);

Explanation

The subquery returns a list of cities that have at least one listing rated above 4.8. The outer query then returns all listings — regardless of their own rating — in any of those cities. So a 3.5-rated listing in a high-performing city would still be included, because the filter is on the city, not the individual listing's rating.

Solution

SELECT department, avg_salary
FROM (
  SELECT department, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_averages
WHERE avg_salary > 65000;

Explanation

The inner query calculates the average salary per department and aliases the result as dept_averages. The outer query then treats that result like a table and filters it with WHERE avg_salary > 65000. Note that this could also be written using HAVING — but using a derived table is a useful pattern to know, especially when the filtering logic is more complex than a simple aggregate comparison.

Solution

SELECT customer_id, total_spend
FROM (
  SELECT customer_id, SUM(total_usd) AS total_spend
  FROM orders
  GROUP BY customer_id
) AS customer_totals
WHERE total_spend > 500;

Explanation

The inner query sums each customer's orders and groups by customer_id, producing one row per customer with their total spend. The outer query then filters that derived table to only customers who spent more than $500. Again, HAVING could achieve the same result — but as queries grow more complex, wrapping aggregations in a derived table often makes the logic easier to read and extend.

Solution

SELECT city, avg_price
FROM (
  SELECT city, AVG(price_per_night) AS avg_price
  FROM rental_listings
  GROUP BY city
) AS city_averages
ORDER BY avg_price DESC
LIMIT 3;

Explanation

The inner query calculates the average nightly price per city. The outer query then sorts those averages from highest to lowest and limits the result to 3 rows. This is a good example of where a derived table adds clarity — the aggregation and the sorting/limiting logic are cleanly separated into two layers.

Solution

SELECT product_id, total_quantity
FROM (
  SELECT product_id, SUM(quantity) AS total_quantity
  FROM order_items
  GROUP BY product_id
) AS product_totals
WHERE total_quantity BETWEEN 10 AND 50;

Explanation

The inner query totals the quantity ordered per product. The outer query filters the derived table using BETWEEN 10 AND 50, which is inclusive on both ends — products with exactly 10 or exactly 50 units ordered are included. BETWEEN is a cleaner alternative to writing WHERE total_quantity >= 10 AND total_quantity <= 50.

Solution

SELECT customer_id, avg_order_total
FROM (
  SELECT customer_id, AVG(total_usd) AS avg_order_total
  FROM orders
  GROUP BY customer_id
) AS customer_averages
WHERE avg_order_total > (SELECT AVG(total_usd) FROM orders);

Explanation

This problem combines both patterns from this set. The FROM subquery calculates each customer's average order total. The WHERE clause then uses a second subquery to calculate the overall average across all orders — and filters to only customers whose personal average exceeds it. Using two subqueries in a single query is common in real-world SQL and a strong signal of intermediate-to-advanced fluency.