Review and Practice

SELECT Practice

Filter and Sort

A review query should make projection, filtering, and ordering easy to inspect in one small result set.

Program

Play the script to choose the minimum score and review how the SELECT query filters rows.

min_score
select_filter_practice.sql
Replay: real traced execution (multi-file project)
CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);
INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
WITH params(min_score) AS (VALUES (80)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;

CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);
INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
WITH params(min_score) AS (VALUES (70)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;

CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);
INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
WITH params(min_score) AS (VALUES (90)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;

  1. tables ← 1 row

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
    values this step1 rowtables

  2. quiz_scores ← 4 rows

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (80)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step4 rowsquiz_scores

  3. result ← 2 rows

    2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (80)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step2 rowsresult

  1. tables ← 1 row

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
    values this step1 rowtables

  2. quiz_scores ← 4 rows

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (70)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step4 rowsquiz_scores

  3. result ← 3 rows

    2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (70)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step3 rowsresult

  1. tables ← 1 row

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);
    values this step1 rowtables

  2. quiz_scores ← 4 rows

    1CREATE TABLE quiz_scores (student TEXT, topic TEXT, score INTEGER);2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (90)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step4 rowsquiz_scores

  3. result ← 1 row

    2INSERT INTO quiz_scores VALUES ('Ada', 'joins', 92), ('Lin', 'joins', 75), ('Mira', 'ctes', 88), ('Noor', 'windows', 67);3WITH params(min_score) AS (VALUES (90)) SELECT student, topic, score FROM quiz_scores WHERE score >= (SELECT min_score FROM params) ORDER BY score DESC, student;
    values this step1 rowresult
projection The SELECT list controls which columns appear in the result.
filter The WHERE clause keeps rows whose score meets the selected threshold.
ordering ORDER BY makes the review result stable and easy to compare.