Subqueries

A query in parentheses can stand in for a value or a list. "Products above the average price" needs the average first: WHERE price > (SELECT AVG(price) FROM products). A subquery that returns one column can feed IN: WHERE id IN (SELECT product_id FROM orders) finds products that were ever ordered, and NOT IN the ones that never were.

Many subqueries can be rewritten as joins and vice versa; use whichever reads more naturally. Subqueries in the FROM clause act as temporary tables.

SELECT name FROM products WHERE id NOT IN (SELECT product_id FROM orders);

Your task

Show the name and price of every product priced above the average product price.

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