Setting Up a Sample Database

Reading SQL is fine, but running it against real data is how the concepts actually stick. This series reuses one consistent sample schema throughout — a small e-commerce database — so the tables you create here will keep showing up in later examples on joins, aggregation, indexing, and more.

The schema

The sample database models a simple online store with four tables:

customers — people who place orders
products — items available for purchase
orders — one row per order placed by a customer
order_items — the individual line items (product + quantity) inside each order

orders references customers, and order_items references both orders and products — a classic one-to-many, many-to-many shape that shows up constantly in real applications.

Creating the tables

schema.sql — table definitions

SQL

CREATE TABLE customers (
    customer_id  SERIAL PRIMARY KEY,
    full_name    VARCHAR(100) NOT NULL,
    email        VARCHAR(255) UNIQUE NOT NULL,
    created_at   TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE products (
    product_id   SERIAL PRIMARY KEY,
    name         VARCHAR(150) NOT NULL,
    price        NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
    stock        INTEGER NOT NULL DEFAULT 0
);

CREATE TABLE orders (
    order_id     SERIAL PRIMARY KEY,
    customer_id  INTEGER NOT NULL REFERENCES customers(customer_id),
    status       VARCHAR(20) NOT NULL DEFAULT 'pending',
    ordered_at   TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
    order_item_id SERIAL PRIMARY KEY,
    order_id      INTEGER NOT NULL REFERENCES orders(order_id),
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    unit_price    NUMERIC(10, 2) NOT NULL
);

Inserting sample data

seed.sql — sample rows

SQL

INSERT INTO customers (full_name, email) VALUES
    ('Ava Thompson', 'ava@example.com'),
    ('Liam Chen',     'liam@example.com'),
    ('Maria Garcia',  'maria@example.com');

INSERT INTO products (name, price, stock) VALUES
    ('Wireless Mouse',     24.99, 150),
    ('Mechanical Keyboard', 89.50,  75),
    ('USB-C Hub',           39.00, 200),
    ('27" Monitor',        249.99,  30);

INSERT INTO orders (customer_id, status) VALUES
    (1, 'completed'),
    (2, 'pending'),
    (1, 'completed');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (1, 1, 2, 24.99),
    (1, 3, 1, 39.00),
    (2, 2, 1, 89.50),
    (3, 4, 1, 249.99);

Running the script

Save both blocks above into a single file, for example sample-db.sql, then load it with psql:

Bash

psql -U postgres -d dbname -f sample-db.sql

Or, from inside an interactive psql session, use the \i meta-command covered on the previous page:

SQL

\i sample-db.sql

Tip

Actually run this on your own machine before moving on. Later pages will ask you to join these tables, aggregate order totals, and add indexes — following along with real rows in front of you makes those examples click far faster than reading alone.