Our Sample Database

Most examples in this course use the same small online-store database. Create it once and every example will run exactly as shown.

The tables

The script

Copy this whole script into your SQL tool and run it:

CREATE TABLE customers (
  id        INTEGER PRIMARY KEY,
  name      TEXT NOT NULL,
  email     TEXT NOT NULL UNIQUE,
  city      TEXT,
  joined_on DATE NOT NULL
);

CREATE TABLE products (
  id       INTEGER PRIMARY KEY,
  name     TEXT NOT NULL,
  category TEXT NOT NULL,
  price    NUMERIC NOT NULL CHECK (price > 0)
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  ordered_on  DATE NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'
);

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

CREATE TABLE employees (
  id         INTEGER PRIMARY KEY,
  name       TEXT NOT NULL,
  department TEXT NOT NULL,
  salary     INTEGER NOT NULL,
  manager_id INTEGER REFERENCES employees(id)
);

INSERT INTO customers (id, name, email, city, joined_on) VALUES
  (1, 'Ada Lovelace',    'ada@example.com',    'London',   '2024-01-15'),
  (2, 'Linus Torvalds',  'linus@example.com',  'Helsinki', '2024-03-02'),
  (3, 'Grace Hopper',    'grace@example.com',  'New York', '2024-03-20'),
  (4, 'Alan Turing',     'alan@example.com',   'London',   '2024-06-11'),
  (5, 'Margaret Hamilton', 'margaret@example.com', 'Boston', '2024-09-05'),
  (6, 'Tim Berners-Lee', 'tim@example.com',    NULL,       '2025-01-08');

INSERT INTO products (id, name, category, price) VALUES
  (1, 'Mechanical Keyboard', 'Accessories', 89.00),
  (2, 'USB-C Hub',           'Accessories', 35.50),
  (3, '27-inch Monitor',     'Displays',    249.99),
  (4, 'Laptop Stand',        'Accessories', 42.00),
  (5, 'Noise-Cancelling Headphones', 'Audio', 199.00),
  (6, 'Webcam',              'Video',       59.90);

INSERT INTO orders (id, customer_id, ordered_on, status) VALUES
  (1, 1, '2025-01-05', 'delivered'),
  (2, 1, '2025-02-14', 'delivered'),
  (3, 2, '2025-02-20', 'shipped'),
  (4, 3, '2025-03-01', 'delivered'),
  (5, 3, '2025-03-18', 'cancelled'),
  (6, 4, '2025-04-02', 'pending'),
  (7, 5, '2025-04-10', 'shipped'),
  (8, 1, '2025-04-22', 'pending');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
  (1, 1, 1, 89.00),
  (1, 2, 2, 35.50),
  (2, 3, 1, 249.99),
  (3, 5, 1, 199.00),
  (3, 2, 1, 35.50),
  (4, 3, 2, 249.99),
  (4, 4, 2, 42.00),
  (5, 6, 1, 59.90),
  (6, 1, 1, 89.00),
  (7, 5, 1, 199.00),
  (7, 4, 1, 42.00),
  (8, 6, 3, 59.90);

INSERT INTO employees (id, name, department, salary, manager_id) VALUES
  (1, 'Sofia', 'Engineering', 120000, NULL),
  (2, 'Omar',  'Engineering',  95000, 1),
  (3, 'Priya', 'Engineering',  98000, 1),
  (4, 'Chen',  'Sales',        70000, 1),
  (5, 'Lucia', 'Sales',        64000, 4),
  (6, 'Kofi',  'Support',      52000, 1);

Don't worry about understanding every line yet — creating tables and inserting data have their own lessons. For now, notice:

• Each table has a primary key (id, or the pair order_id, product_id for order_items).
• REFERENCES marks foreign keys: orders.customer_id points at customers.id.
• Tim Berners-Lee's city is NULL — unknown — which will matter in the NULL lesson.
• Customer 6 has no orders, and product 6 (the webcam) appears only in a cancelled and a pending order — useful for joins later.

Check it worked

SELECT COUNT(*) AS customers FROM customers;

+-----------+
| customers |
+-----------+
|         6 |
+-----------+

If you make a mistake while experimenting, just delete the database file (or run the script in a fresh one) and start again.