📎 This article includes 2 downloadable practice files ↓

📘 SQL Beginner Course · Lesson 7 of 12

Some questions need two steps: “products priced above the average” first needs the average. A subquery does the first step inside the main query.

Compare with a single value

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products);

The inner query runs first and returns one number; the outer query uses it.

IN and NOT IN with a list

-- customers with at least one cancelled order
SELECT name FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders WHERE status = 'Cancelled');

-- customers with no orders in September 2026
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders
                          WHERE order_date BETWEEN '2026-09-01' AND '2026-09-30');

The row with the maximum

SELECT o.order_id, o.qty * p.price AS value
FROM orders o JOIN products p ON p.product_id = o.product_id
WHERE o.qty * p.price = (SELECT MAX(o2.qty * p2.price)
                         FROM orders o2 JOIN products p2 ON p2.product_id = o2.product_id);

Returns all orders tied for the largest value; ORDER BY … LIMIT 1 would hide ties.

Subquery in FROM (a derived table)

SELECT region, sales
FROM (SELECT c.region, SUM(o.qty * p.price) AS sales
      FROM orders o
      JOIN customers c ON c.customer_id = o.customer_id
      JOIN products p  ON p.product_id = o.product_id
      WHERE o.status = 'Delivered'
      GROUP BY c.region)
WHERE sales > 3000000;

CTE: the readable version

A WITH clause names the subquery first, which is easier to read and reuse:

WITH region_sales AS (
  SELECT c.region, SUM(o.qty * p.price) AS sales
  FROM orders o JOIN customers c ON c.customer_id = o.customer_id
                JOIN products p  ON p.product_id = o.product_id
  WHERE o.status = 'Delivered'
  GROUP BY c.region)
SELECT * FROM region_sales WHERE sales > 3000000;

EXISTS

SELECT name FROM customers c
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id AND o.qty >= 10);

True as soon as one matching row is found; safe with NULLs.

Where beginners go wrong

Practice

Download shop.db and this lesson’s exercise file below. Open the database in the free DB Browser for SQLite (or sqlite3 shop.db), paste the exercises into the Execute SQL tab and write a query for each task. The expected result is printed under every task; answers are at the bottom of the file.