The Demo Database

Every example in this course uses the same small database for an imaginary online shop. Knowing its tables and how they connect will make the rest of the course much easier to follow — come back to this page whenever you need a reminder.

The tables

How the tables connect

customers 1 ──< orders 1 ──< order_items >── 1 products

departments 1 ──< employees
employees (manager) 1 ──< employees

• A customer can place many orders; each order belongs to one customer (orders.customer_id).
• An order contains many order items; each item refers to one product (order_items.order_id, order_items.product_id).
• A department has many employees (employees.department_id).
• An employee's manager is another employee (employees.manager_id).

customers

SELECT * FROM customers;

Notice the gaps: Farah has no city and Julia has no email. Missing values are stored as NULL (lesson 11).

products

SELECT * FROM products;

orders

SELECT * FROM orders;

An order's status is one of pending, shipped, delivered or cancelled.

order_items

SELECT * FROM order_items LIMIT 6;

The unit_price is copied from the product when the order is placed, so later price changes don't rewrite history. This table has a composite primary key: the pair (order_id, product_id).

employees and departments

SELECT id, first_name, last_name, department_id, job_title, salary, manager_id
FROM employees;

SELECT * FROM departments;

The chief executive has no department and no manager, and the Research department has no employees yet — details that matter when we reach joins.

The table definitions

This is how the orders table was created. You'll learn to write statements like this in lessons 40–43.

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  order_date DATE NOT NULL,
  status TEXT NOT NULL CHECK (status IN ('pending', 'shipped', 'delivered', 'cancelled'))
);

Exploring the schema yourself

Every database stores a description of its own tables. In SQLite, it's the sqlite_schema table; most other systems use the standard information_schema views (lesson 52).