Lesson 5 of 10

Subqueries

14 min
5 checks
3 hints
Go to task

Goal

A query inside a query

Explanation

A subquery is a SELECT you write in parentheses inside another query. It runs first, and its result is then used as if you had typed it in yourself.

If it returns a single value, you compare against it, as in price > (SELECT AVG(price) FROM products). That way you don't need to know the average in advance, or update it when prices change. If it returns a column of values, you ask whether a value is among them, as in id IN (SELECT product_id FROM order_items).

A JOIN with order_items might look like a shorter route, but it repeats a product once for every line item it was sold in. IN only asks whether the product is there, so nothing gets repeated.

Your task

Show the name and price of the products that have sold at least once and cost more than the average price of all products.

Checks

5 checks

Columns are name and price
Every listed product costs more than the average
Every listed product has sold at least once
Each product appears once
No matching product is missing

Tables in this lesson

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  city TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER REFERENCES customers(id),
  total       INTEGER NOT NULL,
  placed_on   TEXT    NOT NULL
);

INSERT INTO customers (id, name, city) VALUES
  (1, 'Layan',  'Riyadh'),
  (2, 'Khalid', 'Jeddah'),
  (3, 'Reem',   'Dammam'),
  (4, 'Fahd',   'Riyadh');

INSERT INTO orders (customer_id, total, placed_on) VALUES
  (1, 120, '2026-09-01'),
  (1,  80, '2026-09-12'),
  (2, 300, '2026-09-03'),
  (3,  45, '2026-09-20'),
  (2,  60, '2026-09-25');

INSERT INTO customers (id, name, city) VALUES (5, 'Sara', 'Jeddah');

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT    NOT NULL,
  price INTEGER NOT NULL
);

CREATE TABLE order_items (
  id         INTEGER PRIMARY KEY,
  order_id   INTEGER REFERENCES orders(id),
  product_id INTEGER REFERENCES products(id),
  qty        INTEGER NOT NULL
);

INSERT INTO products (id, name, price) VALUES
  (1, 'Notebook',    15),
  (2, 'Pen',          5),
  (3, 'Bag',         90),
  (4, 'Calculator',  60),
  (5, 'Lamp',       100);

INSERT INTO order_items (order_id, product_id, qty) VALUES
  (1, 3, 1), (1, 1, 2),
  (2, 4, 1), (2, 2, 4),
  (3, 3, 2), (3, 4, 2),
  (4, 1, 3),
  (5, 1, 2), (5, 2, 6);

Subqueries

Explanation

Your task

Checks

Hints