Tables and Select

Where and Order

Filtering Rows

WHERE keeps rows that match a condition. ORDER BY sorts the remaining rows.

Program

Play the script to filter in-stock products and sort by price.

where_order.sql
Replay: real traced execution (multi-file project)
CREATE TABLE products (name TEXT, price INTEGER, stock INTEGER);
INSERT INTO products VALUES ('pen', 3, 10), ('bag', 25, 0), ('book', 12, 4);
SELECT name, price FROM products WHERE stock > 0 ORDER BY price;

  1. tables ← 1 row

    1CREATE TABLE products (name TEXT, price INTEGER, stock INTEGER);2INSERT INTO products VALUES ('pen', 3, 10), ('bag', 25, 0), ('book', 12, 4);
    values this step1 rowtables

  2. products ← 3 rows

    1CREATE TABLE products (name TEXT, price INTEGER, stock INTEGER);2INSERT INTO products VALUES ('pen', 3, 10), ('bag', 25, 0), ('book', 12, 4);3SELECT name, price FROM products WHERE stock > 0 ORDER BY price;
    values this step3 rowsproducts

  3. result ← 2 rows

    2INSERT INTO products VALUES ('pen', 3, 10), ('bag', 25, 0), ('book', 12, 4);3SELECT name, price FROM products WHERE stock > 0 ORDER BY price;
    values this step2 rowsresult

Follow the Filter

  1. products starts with pen, bag, and book.
  2. WHERE stock > 0 keeps rows with stock left.
  3. bag has stock 0, so it is not returned.
  4. ORDER BY price puts pen before book. | name | price | stock | result | | --- | --- | --- | --- | | pen | 3 | 10 | kept | | bag | 25 | 0 | filtered out | | book | 12 | 4 | kept |
WHERE `WHERE stock > 0` keeps only rows with available stock.
comparison `>` compares each row's value.
sort `ORDER BY price` sorts the filtered rows.

Exercise: where_order.sql

Reproduce the kept rows pen and book, then change bag stock to a positive number and predict the price order.