A Query Inside a Query

Sometimes a query needs a value you don't know yet, like the average price. Instead of looking it up and typing it in, put a whole query in round brackets where the value goes. This subquery runs first, and its answer takes its place.

var query = "SELECT name, price " +
    "FROM products " +
    "WHERE price = (SELECT MIN(price) FROM products) ";

Here the subquery answers 2, the lowest price, so the query finds the pen. A subquery can also give a list: WHERE id IN (SELECT …) keeps the rows whose id is in that list, and the query inside can have its own subquery. Read such a query from the inside out.

The check runs show(db, query); and show(db, lampQuery);, which must print:

name | price
Lamp | 29
Teapot | 22
Gift card | 50
(3 rows)
name
Ben Ortiz
Dan Weber
(2 rows)

Your task: write query, the products that cost more than the average price, with the average worked out by a subquery. Then write lampQuery, the names of the customers who ordered the lamp (product id 3).

Assignment
Follow the Coding Tutorial to write query, then build lampQuery from the outside in, one piece of the string per step.

Hint
AVG(price) gives the average price. For lampQuery, the innermost query finds the order ids in order_items whose product_id is 3; around it, the customer_id of those orders; around that, the names from customers, each time with IN ( … ). Every piece of a string ends with +, except the last one, which ends the statement with ;.