Module 1: Getting Started
The Course Schema and Seed Data
The small online shop and company org chart used in every lesson, why each table is shaped the way it is, and the script that loads it.
Every example in this course runs against the same small dataset. You will know it well by the end, and that is the point. When you already know that customer 10 has no orders, you can predict what a LEFT JOIN should return before you run it.
The data is tiny on purpose. Eleven products and fourteen orders are easy to hold in your head. The ideas scale, the tables do not need to.
The shape of the data#
Two small worlds live in one database. A shop (customers, products, orders, order lines) and a company (departments and employees).
Read the lines as sentences. One customer places zero or many orders. One order contains one or many items. One product appears in zero or many order lines. One employee can manage many employees, which makes employees a self-referencing table.
Why it looks the way it does#
Each oddity in this schema is there to set up a later lesson.
order_itemshas a composite primary key(order_id, product_id). A product appears at most once per order, and the quantity column carries the count. This is the standard shape of a many-to-many junction table.order_items.unit_priceduplicatesproducts.unit_price. That is deliberate. Prices change, and an old invoice must keep the price the customer actually paid. A snapshot is history, not redundancy. Module 5 returns to this.customers.phoneis nullable. Three customers have no phone. You will need that forNULLhandling.- Customer 10 (Jon Smith) has no orders. Customer 4 has only a cancelled order. These two rows make joins and anti-joins interesting.
- Product 11 (Cable Organizer) has never been ordered, and product 6 (HD Webcam) has zero stock.
- The
Legaldepartment has no employees. Counting employees per department with an inner join silently drops it. - Two engineers earn exactly 120000. Ties are where
RANKandROW_NUMBERpart ways. manager_idpoints back atemployees. Meera Iyer has no manager (NULL), which makes her the root of the tree for recursive queries.
Load it#
Save the script below as seed.sql. It drops and recreates everything, so you can run it again whenever you want a clean slate.
From your host shell with Docker:
docker cp seed.sql sqlcourse:/seed.sql
docker exec -it sqlcourse psql -U postgres -d shop -f /seed.sql
Or from inside psql connected to shop:
\i /path/to/seed.sql
-- Course schema: a small online shop plus a company org chart.
-- Target: PostgreSQL 14+ (16 recommended)
DROP TABLE IF EXISTS order_items, orders, products, customers, employees, departments CASCADE;
CREATE TABLE departments (
department_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(50) NOT NULL UNIQUE
);
CREATE TABLE employees (
employee_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
department_id INTEGER NOT NULL REFERENCES departments (department_id),
manager_id INTEGER REFERENCES employees (employee_id),
salary NUMERIC(10,2) NOT NULL CHECK (salary > 0),
hire_date DATE NOT NULL
);
CREATE TABLE customers (
customer_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
full_name VARCHAR(100) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
phone VARCHAR(20),
city VARCHAR(80) NOT NULL,
country VARCHAR(60) NOT NULL,
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE products (
product_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR(100) NOT NULL,
category VARCHAR(50) NOT NULL,
unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
stock_qty INTEGER NOT NULL DEFAULT 0 CHECK (stock_qty >= 0)
);
CREATE TABLE orders (
order_id INTEGER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers (customer_id),
order_date DATE NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending','paid','shipped','delivered','cancelled'))
);
CREATE TABLE order_items (
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,
PRIMARY KEY (order_id, product_id)
);
INSERT INTO departments (name) VALUES
('Engineering'), ('Sales'), ('Support'), ('Finance'), ('Legal');
INSERT INTO employees (full_name, department_id, manager_id, salary, hire_date) VALUES
('Meera Iyer', 1, NULL, 185000, '2015-04-12'),
('Arjun Rao', 1, 1, 140000, '2017-09-01'),
('Sofia Martins', 1, 2, 120000, '2019-02-18'),
('Kenji Watanabe', 1, 2, 120000, '2020-06-30'),
('Priya Nair', 2, 1, 130000, '2018-01-15'),
('Daniel Okafor', 2, 5, 90000, '2021-03-22'),
('Lena Fischer', 2, 5, 95000, '2022-08-09'),
('Omar Haddad', 3, 1, 100000, '2018-11-05'),
('Grace Thompson', 3, 8, 70000, '2023-01-16'),
('Rahul Verma', 4, 1, 110000, '2019-07-01');
INSERT INTO customers (full_name, email, phone, city, country, created_at) VALUES
('Aisha Khan', 'aisha@example.com', '+91-98450-11122', 'Mumbai', 'India', '2024-01-10 09:15:00'),
('Ben Carter', 'ben@example.com', '+1-512-555-0142', 'Austin', 'USA', '2024-01-22 14:40:00'),
('Chloe Dubois', 'chloe@example.com', NULL, 'Lyon', 'France', '2024-02-03 11:05:00'),
('Diego Alvarez', 'diego@example.com', '+34-91-555-0199', 'Madrid', 'Spain', '2024-02-14 16:20:00'),
('Elena Petrova', 'elena@example.com', NULL, 'Berlin', 'Germany', '2024-03-01 08:30:00'),
('Farhan Ali', 'farhan@example.com', '+91-98860-33344', 'Bengaluru', 'India', '2024-03-12 19:45:00'),
('Grace Lin', 'grace@example.com', '+1-206-555-0177', 'Seattle', 'USA', '2024-03-30 13:10:00'),
('Hiro Tanaka', 'hiro@example.com', '+81-6-5555-0123', 'Osaka', 'Japan', '2024-04-08 10:00:00'),
('Isla Murphy', 'isla@example.com', NULL, 'Dublin', 'Ireland', '2024-04-20 17:25:00'),
('Jon Smith', 'jon@example.com', '+1-512-555-0188', 'Austin', 'USA', '2024-05-05 12:00:00');
INSERT INTO products (name, category, unit_price, stock_qty) VALUES
('Mechanical Keyboard', 'Accessories', 89.00, 40),
('Wireless Mouse', 'Accessories', 29.50, 120),
('USB-C Hub', 'Accessories', 45.00, 75),
('27-inch Monitor', 'Displays', 289.00, 18),
('Laptop Stand', 'Accessories', 34.00, 60),
('HD Webcam', 'Video', 59.00, 0),
('Noise-Cancelling Headphones', 'Audio', 199.00, 25),
('Desk Lamp', 'Office', 24.00, 90),
('Ergonomic Chair', 'Furniture', 349.00, 8),
('Standing Desk', 'Furniture', 499.00, 5),
('Cable Organizer', 'Office', 9.00, 200);
INSERT INTO orders (customer_id, order_date, status) VALUES
(1, '2024-02-01', 'delivered'),
(1, '2024-03-15', 'delivered'),
(2, '2024-02-10', 'delivered'),
(3, '2024-02-20', 'shipped'),
(4, '2024-03-02', 'cancelled'),
(5, '2024-03-18', 'delivered'),
(2, '2024-04-05', 'delivered'),
(6, '2024-04-11', 'paid'),
(7, '2024-04-25', 'delivered'),
(1, '2024-05-02', 'shipped'),
(8, '2024-05-10', 'delivered'),
(6, '2024-05-21', 'pending'),
(3, '2024-06-03', 'delivered'),
(9, '2024-06-14', 'pending');
INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
(1, 1, 1, 89.00), (1, 2, 2, 29.50),
(2, 4, 1, 289.00), (2, 3, 1, 45.00),
(3, 7, 1, 199.00),
(4, 5, 2, 34.00), (4, 8, 1, 24.00),
(5, 9, 1, 349.00),
(6, 2, 1, 29.50), (6, 3, 2, 45.00), (6, 8, 2, 24.00),
(7, 1, 1, 89.00), (7, 5, 1, 34.00),
(8, 10, 1, 499.00),
(9, 4, 2, 289.00), (9, 2, 1, 29.50),
(10, 7, 1, 199.00), (10, 3, 1, 45.00),
(11, 6, 1, 59.00), (11, 8, 1, 24.00),
(12, 9, 1, 349.00), (12, 5, 1, 34.00),
(13, 2, 3, 29.50), (13, 1, 1, 89.00),
(14, 8, 2, 24.00), (14, 5, 1, 34.00);
A note on identity columns#
The tables use GENERATED ALWAYS AS IDENTITY, which is the modern standard way to auto-number a primary key. The seed never inserts IDs by hand. It lets the database assign 1, 2, 3 in order, which is why later inserts can refer to customer_id = 7 and know who that is.
If you ever insert explicit IDs into an identity column, the counter does not move, and your next automatic insert collides with an existing row. Module 7 shows the fix. For now, trust the script.
Verify the load#
Run this and compare with the expected counts.
SELECT 'departments' AS tbl, COUNT(*) FROM departments
UNION ALL SELECT 'employees', COUNT(*) FROM employees
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;
tbl | count
-------------+-------
departments | 5
employees | 10
customers | 10
products | 11
orders | 14
order_items | 26
If any number is off, rerun the script from the top. Partial loads happen when an earlier statement failed and you kept pasting.
Common mistakes#
Running the script twice without the DROP line. You get relation "departments" already exists and a half-built schema. The script starts with DROP TABLE IF EXISTS ... CASCADE to prevent exactly this.
Editing the seed data casually. Later lessons print real output computed from these exact rows. Change a price and your results will not match the lesson. Make your experiments in a separate copy.
Skimming the schema. Spend five minutes with \d orders and \d order_items in psql. Knowing the column names saves more time than any trick in this course.
Practice#
- Load the script and run the verification query. Your counts should match the table above.
- Run
\d order_itemsand identify the primary key, the two foreign keys and theCHECKconstraint. - Without running anything, predict how many rows
SELECT * FROM customers CROSS JOIN productsreturns. Then run it. (Answer: 110.) - Pick any three tables and write one sentence per relationship in the form "one X has many Y".