A Query Inside a Query in Java

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.

String query = "";
query += "SELECT name, price ";
query += "FROM products ";
query += "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: build query, the products that cost more than the average price, with the average worked out by a subquery. Then build lampQuery, the names of the customers who ordered the lamp (product id 3).

Assignment
Follow the Coding Tutorial to build query, then lampQuery, one piece per step with +=; SQL runs lampQuery from the inside out.

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 go the customer_id of those orders, then the names from customers, each time with IN ( … ). Every opening bracket needs its closing ), and each piece ends with a space before the closing quote.