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.

Cheat sheet

Subqueries allow combining multiple queries into one. They're useful when you can't use aggregate functions like AVG() directly in a WHERE clause.

Basic subquery syntax:

SELECT column1, column2
FROM table_name
WHERE column1 > (
    SELECT AVG(column1)
    FROM table_name
);

The subquery (inner query) executes first to calculate the average, then the outer query uses that result to filter the data.

Try it yourself

Test yourself

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

Subqueries Part 1

Cheat sheet

All lessons in Fundamentals