A query inside a query

Some questions are asked in two steps: "what is the average price?" and "which ones cost more than that?". Instead of copying the first answer by hand, you write that query in brackets inside the second. The inner query runs first and its answer is put in its place.

1SELECT title, price
2FROM books
3WHERE price > (SELECT AVG(price) FROM books);

The inner query returns a single value (one row, one column), which is why it can be compared with >. You cannot write the aggregate straight into WHERE price > AVG(price); a subquery is the way to do it.

When the inner query returns a list, you use IN: WHERE author_id IN (SELECT id FROM authors WHERE country = 'TR'). First the numbers of the authors in Turkey are found, then the books carrying one of those numbers are picked.

Tasks