Subqueries Part 2

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

There are three main types of subqueries:

Scalar Subqueries - Returns a single value (one row, one column)
Row Subqueries - Returns a single row with multiple columns
Table Subqueries - Returns multiple rows and columns

For example here are some use cases for each subquery type:

Scalar Subquery - Find employees who earn more than the average salary:

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

Row Subquery - Find employee(s) with the same department and salary as Alice

SELECT name
FROM employees
WHERE (department, salary) = (
    SELECT department, salary
    FROM employees
    WHERE name = 'Alice'
);

Table Subqueries - Show departments and their employee count

SELECT department, count
FROM (
    SELECT department, COUNT(*) as count
    FROM employees
    GROUP BY department
) as dept_counts;

Notice the use cases:

Scalar - Simple comparisons (>, <, =)

When you need to match multiple columns

When you need to query from a result set

Challenge

Easy

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

Find categories where their total quantity is greater than the average of all quantities in the shop.

Important: You must use a subquery inside the HAVING clause to calculate the average. Do not use a hardcoded value or a separate query — the subquery should compute AVG(quantity) from the shop table directly inside HAVING.

Steps to solve:

First, write a subquery that calculates the average of all quantities in the shop: SELECT AVG(quantity) FROM shop
Then, for each category, sum their quantities and compare with this subquery using the HAVING keyword

Cheat sheet

There are three main types of subqueries:

Scalar Subqueries - Returns a single value (one row, one column)
Row Subqueries - Returns a single row with multiple columns
Table Subqueries - Returns multiple rows and columns

Scalar Subquery - Simple comparisons (>, <, =):

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

Row Subquery - Match multiple columns:

SELECT name
FROM employees
WHERE (department, salary) = (
    SELECT department, salary
    FROM employees
    WHERE name = 'Alice'
);

Table Subquery - Query from a result set:

SELECT department, count
FROM (
    SELECT department, COUNT(*) as count
    FROM employees
    GROUP BY department
) as dept_counts;

Try it yourself

Test yourself

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

Subqueries Part 2

Cheat sheet

All lessons in Fundamentals