Subqueries

Sometimes a query needs an answer from another query first. A subquery is a SELECT nested inside another, in parentheses. A classic use: find rows compared to an aggregate of the whole table, like "priced above the average":

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

The inner (SELECT AVG(price) FROM products) runs first and produces one number; the outer query then filters against it. You cannot do this with a plain WHERE price > AVG(price) because aggregates are not allowed directly in WHERE, that is exactly the gap a subquery fills.

Write above_average_price(con) returning the names of products whose price is strictly greater than the average price, ordered by name. Press Run.