Subqueries Part 1

Part of the Fundamentals section of Coddy's SQL journey — lesson 38 of 72.

Subqueries allow us to combine multiple queries into one. For example consider the following employees table:

id	salary
1	48
2	34
3	46
4	13
5	28

We need a subquery because we can't use WHERE salary > AVG(salary) directly since aggregate functions like AVG() can't be used in a WHERE clause - they can only be used after the data has been grouped.

SELECT id, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

A subquery helps us find employees who earn more than the company's average salary by first calculating the average (inner query) and then using that value to filter employees (outer query).

Since the average is: (48 + 34 + 46 + 13 + 28) / 5 = 33.8, this is the result of the query:

id	salary
1	48
2	34
3	46

Challenge

Easy

Available tables and columns:
shop: price, quantity, category, list_date

Write a query to find all items (select all columns) where the price is higher than the average price of all items.

Use a subquery in the WHERE clause to calculate the average price, then compare each item's price against this average.

Try it yourself

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

Subqueries Part 1

All lessons in Fundamentals