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
Replay: real traced execution (multi-file project)
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;

  1. scores ← 0 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 9), ('Lin', 12), ('Mia', 6);
    values this step0 rowsscores

  2. scores ← 3 rows

    1CREATE TABLE scores (name TEXT, points INTEGER);2INSERT INTO scores VALUES ('Ada', 9), ('Lin', 12), ('Mia', 6);3SELECT name, points FROM scores WHERE points > (SELECT AVG(points) FROM scores) ORDER BY points DESC;
    values this step3 rowsscores

  3. result ← 1 row

    2INSERT INTO scores VALUES ('Ada', 9), ('Lin', 12), ('Mia', 6);3SELECT name, points FROM scores WHERE points > (SELECT AVG(points) FROM scores) ORDER BY points DESC;
    values this step1 rowresult

Follow the Average

  1. The scores are Ada 9, Lin 12, and Mia 6.
  2. The scalar subquery computes the average points: 9.
  3. The outer query checks each row with points > 9.
  4. Ada is equal to the average, so she is not kept.
  5. Only Lin has more than 9, so the final row is Lin 12. | name | points | compared to average 9 | result | | --- | --- | --- | --- | | Ada | 9 | equal | filtered out | | Lin | 12 | greater | kept | | Mia | 6 | lower | filtered out |
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.

Exercise: scalar_subquery.sql

Reproduce the final row Lin 12, then predict whether a score equal to the average would pass the filter.