Subqueries and CTEs
Scalar Subquery
Compare to an Average
A scalar subquery returns one value that the outer query can use.
Program
Play the query to keep scores above the overall average.
scalar_subquery.sql
CREATE TABLE scores (name TEXT, points INTEGER);
INSERT INTO scores VALUES ('Ada', 9), ('Lin', 12), ('Mia', 6);
SELECT name, points FROM scores WHERE points > (SELECT AVG(points) FROM scores) ORDER BY points DESC;
subquery
A subquery is a query nested inside another query.
scalar
`AVG(points)` returns one value here.
outer query
The outer query compares each row to the subquery value.