Lesson · 12 min

Subqueries

A query inside a query

A subquery is a SELECT inside another query, written in parentheses. The database runs the inner query first, and uses its result in the outer query.

A single value

When the subquery gives one value, you can use it in a comparison.

SELECT title, year
FROM books
WHERE year > (SELECT AVG(year) FROM books);

You do not need to know the average in advance. If the data changes, the query still works.

A list of values

When the subquery gives one column with many rows, use IN or NOT IN.

SELECT title
FROM books
WHERE genre IN (SELECT genre FROM books WHERE price > 20);

IN is true when the value is in the list. NOT IN is true when it is not.

How to build a subquery

Write the inner query alone, and run it to see its result.
Put it in parentheses.
Write the outer query around it.

Your turn

Write one query for each task, one after the other. Each task asks for a subquery, and the checker looks for it in your code.

Tasks

4 to do

Task 1, not checked yet. Select the title and price of the books that cost more than the average price. Use a subquery with AVG inside the WHERE clause.
Task 2, not checked yet. Select the title of the newest book. Use a subquery with MAX(year).
Task 3, not checked yet. Select the title of the books by authors that have more than one book in the table. Use IN with a subquery that groups by author.
Task 4, not checked yet. Select the title of the books in genres where no book costs more than 20. Use NOT IN with a subquery.

Task 1 of 4

Select the title and price of the books that cost more than the average price. Use a subquery with AVG inside the WHERE clause.

All tasks