Tables and Select

Create and Insert

Building a Table

A SQL table starts with named columns. INSERT adds rows, and SELECT reads the table back.

Program

Play the script to watch an empty table become two rows.

create_insert.sql
Replay: real traced execution (multi-file project)
CREATE TABLE users (id INTEGER, name TEXT);
INSERT INTO users VALUES (1, 'Ada');
INSERT INTO users VALUES (2, 'Lin');
SELECT * FROM users ORDER BY id;

  1. tables ← 1 row

    1CREATE TABLE users (id INTEGER, name TEXT);2INSERT INTO users VALUES (1, 'Ada');
    values this step1 rowtables

  2. users ← 1 row

    1CREATE TABLE users (id INTEGER, name TEXT);2INSERT INTO users VALUES (1, 'Ada');3INSERT INTO users VALUES (2, 'Lin');
    values this step1 rowusers

  3. users ← 2 rows

    2INSERT INTO users VALUES (1, 'Ada');3INSERT INTO users VALUES (2, 'Lin');4SELECT * FROM users ORDER BY id;
    values this step2 rowsusers

  4. result ← 2 rows

    3INSERT INTO users VALUES (2, 'Lin');4SELECT * FROM users ORDER BY id;
    values this step2 rowsresult

Follow the Rows

  1. CREATE TABLE users makes two columns: id and name.
  2. The first INSERT adds (1, 'Ada').
  3. The second INSERT adds (2, 'Lin').
  4. SELECT * FROM users ORDER BY id reads both rows with 1 before 2. | id | name | | --- | --- | | 1 | Ada | | 2 | Lin |
CREATE TABLE `CREATE TABLE` defines the columns a table can store.
INSERT `INSERT INTO ... VALUES` appends a row.
SELECT `SELECT *` returns every column from the table.

Exercise: create_insert.sql

Reproduce the two-row users table with Ada and Lin, then add one more user row and predict where it appears when ordered by id.