Tables and Select

Select Columns

Reading Only What You Need

SELECT can return a subset of columns. This keeps query results focused.

Program

Play the script to load books, then select only title and price.

select_columns.sql
Replay: real traced execution (multi-file project)
CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);
INSERT INTO books VALUES (1, 'SQL', 30);
INSERT INTO books VALUES (2, 'R', 25);
SELECT title, price FROM books ORDER BY id;

  1. tables ← 1 row

    1CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);2INSERT INTO books VALUES (1, 'SQL', 30);
    values this step1 rowtables

  2. books ← 1 row

    1CREATE TABLE books (id INTEGER, title TEXT, price INTEGER);2INSERT INTO books VALUES (1, 'SQL', 30);3INSERT INTO books VALUES (2, 'R', 25);
    values this step1 rowbooks

  3. books ← 2 rows

    2INSERT INTO books VALUES (1, 'SQL', 30);3INSERT INTO books VALUES (2, 'R', 25);4SELECT title, price FROM books ORDER BY id;
    values this step2 rowsbooks

  4. result ← 2 rows

    3INSERT INTO books VALUES (2, 'R', 25);4SELECT title, price FROM books ORDER BY id;
    values this step2 rowsresult

Follow the Selected Columns

  1. books stores id, title, and price.
  2. The table gets (1, 'SQL', 30) and (2, 'R', 25).
  3. The query asks for only title and price.
  4. ORDER BY id keeps SQL before R. | title | price | | --- | --- | | SQL | 30 | | R | 25 |
projection Selecting columns is called projection.
ORDER BY `ORDER BY id` makes row order deterministic.
result set A query returns a table-shaped result.

Exercise: select_columns.sql

Reproduce the title-and-price result for SQL and R, then select id too and predict the extra column before running it.