schema_setup.sql in SQL

A complete migration: drop, create, index and seed a small store schema.

-- Store schema, safe to re-run from scratch.
PRAGMA foreign_keys = ON;

DROP VIEW IF EXISTS order_summary;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    country TEXT NOT NULL DEFAULT 'US',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE products (
    sku TEXT PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL CHECK (price >= 0)
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    status TEXT NOT NULL DEFAULT 'pending',
    total REAL NOT NULL DEFAULT 0,
    placed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE order_items (
    order_id INTEGER NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    sku TEXT NOT NULL REFERENCES products(sku),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    UNIQUE (order_id, sku)
);

CREATE INDEX IF NOT EXISTS idx_orders_customer
    ON orders (customer_id, placed_at DESC);

CREATE VIEW order_summary AS
SELECT
    o.id,
    c.name AS customer,
    o.status,
    o.total,
    COUNT(i.sku) AS lines
FROM orders AS o
JOIN customers AS c
    ON c.id = o.customer_id
LEFT JOIN order_items AS i
    ON i.order_id = o.id
GROUP BY o.id, c.name, o.status, o.total;

INSERT INTO customers (name, email, country)
VALUES
    ('Ada Lovelace', 'ada@example.com', 'GB'),
    ('Grace Hopper', 'grace@example.com', 'US');

INSERT INTO products (sku, name, price)
VALUES
    ('TS-001', 'Keyboard', 89.00),
    ('TS-002', 'Keycap set', 45.50);

How it works

1. Statements run in dependency order, parents before children.
2. IF EXISTS and IF NOT EXISTS make the whole file re-runnable.
3. A view at the end gives the application one friendly name to query.

Keywords and builtins used here

• AS
• BY
• CASCADE
• CHECK
• COUNT
• CREATE
• CURRENT_TIMESTAMP
• DEFAULT
• DELETE
• DESC
• DROP
• EXISTS
• FROM
• GROUP
• IF
• INDEX
• INSERT
• INTEGER
• INTO
• JOIN
• KEY
• LEFT
• NOT
• NULL
• ON
• PRIMARY
• REAL
• REFERENCES
• SELECT
• TABLE
• TEXT
• TIMESTAMP
• UNIQUE
• VALUES
• VIEW
• c

The run, in numbers

Lines

64

Characters to type

1576

Tokens

336

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 237 seconds.

Type this snippet

Step 1 of 2 in Encore, step 22 of 23 in Language basics.

← Previous Next →