sql · level 2 · unit 4 · lesson 2
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
A in brackets hands its answer to the main query.
Which snacks cost more than average? You could ask for the average, write it down, then type it into a second query. But when prices change, your number goes stale.
Instead, put the first query in brackets right where the number would go. That is a subquery. SQLite works out the bracketed part first, then uses its answer as the value.
A subquery used like this must give back exactly one value: one column and one row. An aggregate such as AVG or MAX is perfect.
SELECT name, price FROM snacks
WHERE price > (SELECT AVG(price) FROM snacks)
ORDER BY price DESC;If a subquery gives back several rows, SQLite quietly uses the first, so make sure it gives one.
guess first
Whose score is the top score?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT player, game FROM scores
WHERE points = (SELECT MAX(points) FROM scores);your turn
Your turn. Compare films with the average.
Type > or < in the blank to list the films that are longer or shorter than the average film.
result
Your answer appears here as a table.
fix it
Which snacks are dearer than crisps? Fix it!
result
Your answer appears here as a table.
- Read the error. Which word is it near?
- A subquery must sit inside its own brackets, so SQLite knows where it starts and ends.
- Put ( before the second SELECT and ) after 'crisps', then press run.
nailed it
your query asked a question of its own. :D
- A subquery is a query in brackets inside another.
- It runs first, and its answer drops into place.
- Used as a value, it must give back one value.
new words in your collection
- subquery
This lesson isn’t saved. A free account remembers every lesson you finish.