Subqueries
A subquery is a query inside brackets, used inside another query. The inner query runs first and its result is used by the outer one.
A single value
When the subquery returns one value, you can compare against it:
SELECT name, joined
FROM customers
WHERE joined = (SELECT MIN(joined) FROM customers); -- the first customer
This is better than typing the value in yourself: if the data changes, the query still gives the right answer.
A list of values
With IN, a subquery can return a whole column:
SELECT name FROM products
WHERE id IN (SELECT product_id FROM orders WHERE quantity >= 10);
And NOT IN finds the opposite. (Be careful: if the list contains a NULL, NOT IN matches nothing; LEFT JOIN ... IS NULL from the last lesson avoids that trap.)
EXISTS
EXISTS (subquery) is true when the subquery returns at least one row. It is often the clearest way to say "has any":
SELECT name FROM customers AS c
WHERE EXISTS (SELECT 1 FROM orders AS o WHERE o.customer_id = c.id);
Your task
Show the name and price of products that cost more than the average price of all products, most expensive first. Work out the average with a subquery.