Nested subqueries

A nested subquery is a query tucked inside another query, so the inner result feeds directly into the outer question.

About 5–10 minutes · no sign-up needed

Example

Using Nested Subqueries for Retail Benchmarks

SELECT order_id, customer_id, revenue
FROM orders
WHERE revenue > (SELECT AVG(revenue) FROM orders)

Result:

order_id	customer_id	revenue
O2001	C101	120.5
O2003	C103	310.75

Run the query without AVG so you can see what the unfiltered values look like.

SELECT order_id, customer_id, revenue
FROM orders
WHERE revenue > (SELECT revenue FROM orders);

AVG returns the mean of the values in a column.

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

AS gives the AVG result a friendly column name so the output reads cleanly.

SELECT order_id, customer_id, revenue
FROM orders
WHERE revenue > (SELECT AVG(revenue) AS avg_result FROM orders);

Try each one in your head first, then open the answer.

1. Using orders, write a query to find rows where revenue equals the maximum revenue of the customer_id group it belongs to.

SELECT * FROM orders WHERE revenue = (SELECT MAX(revenue) FROM (SELECT revenue FROM orders) AS sub)

2. Using customers, write a query to find rows where phone equals the maximum phone of the full_name group it belongs to.

SELECT * FROM customers WHERE phone = (SELECT MAX(phone) FROM (SELECT phone FROM customers) AS sub)

3. Using products, write a query to find rows where price equals the maximum price of the product_id group it belongs to.

SELECT * FROM products WHERE price = (SELECT MAX(price) FROM (SELECT price FROM products) AS sub)

4. Using store_sales, write a query to find rows where revenue equals the maximum revenue of the sale_id group it belongs to.

SELECT * FROM store_sales WHERE revenue = (SELECT MAX(revenue) FROM (SELECT revenue FROM store_sales) AS sub)

5. Using employees, write a query to find rows where salary equals the maximum salary of the role group it belongs to.

SELECT * FROM employees WHERE salary = (SELECT MAX(salary) FROM (SELECT salary FROM employees) AS sub)

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

About 5–10 minutes · no sign-up needed