The Project | About Us | Contribute | Donations | License

HOME

The moment has come to turn theory into something tangible. In this lesson you'll get to know GreenStore inside out, the fictional organic products online shop that will be the setting for every example and exercise in the remaining eleven modules. You'll see its business model, its full entity-relationship diagram, the column-by-column description of its nine tables and —most importantly— the ready-to-copy-and-run SQL script that creates the tables and loads the data. By the end you'll have the database running on your machine and you'll know what's inside it, so that from module 2 onwards you can concentrate on learning SQL instead of on understanding the context.

This lesson is not a DDL tutorial: you'll see the script and understand what each block does, but the full syntax of CREATE TABLE, the types of constraint and migrations are studied in module 5.

The business: what GreenStore is and how it operates
Entity-relationship diagram
The nine tables, one by one
Design decisions worth knowing about
The table creation script
The data loading script
How to load the database
Verification queries
Common Mistakes and Tips
Exercises
Conclusion

The business: what GreenStore is and how it operates

GreenStore is an organic products online shop headquartered in Valencia, founded at the end of 2024 and trading since the beginning of 2025.

What it sells. A catalogue of around twenty product lines spread across six categories: organic food, natural cosmetics, sustainable home, drinks, personal hygiene and dietary supplements. It buys from five suppliers in Spain, Portugal, France and Germany.

Who it sells to. Individuals in Spain, Portugal and France. Many customers arrive on another customer's recommendation, something the company records for its referral programme.

How it operates.

Two sales channels. Orders that come in through the web have no sales rep assigned; those that come in by phone are handled by a sales rep on the team. This distinction is the reason orders.employee_id can be NULL, and it will be the star of the LEFT JOIN and null-value lessons.
A team of eight people with a hierarchy: general management, two area managers, sales reps, customer support, warehouse and data analysis.
Order life cycle: pending → paid → shipped → delivered, with the possibility of cancelled at any point.
Four payment methods: card, transfer, PayPal and cash on delivery.
Variable shipping costs depending on destination, with free shipping above a certain amount.
Customer reviews rating from 1 to 5 the products they've bought.
Returns tied to an order, with a reason and a refunded amount.

Questions management asks every month (and which you'll know how to answer by the end of the course): which products sell the most? which country leaves the best margin? which customers haven't bought in months? which sales rep closes the most orders? which products are piling up bad reviews? how much do returns cost us?

Entity-relationship diagram

erDiagram
    CATEGORIES   ||--o{ PRODUCTS    : "classifies"
    SUPPLIERS    ||--o{ PRODUCTS    : "supplies"
    CUSTOMERS    ||--o{ ORDERS      : "places"
    EMPLOYEES    ||--o{ ORDERS      : "handles"
    ORDERS       ||--o{ ORDER_LINES : "contains"
    PRODUCTS     ||--o{ ORDER_LINES : "appears in"
    PRODUCTS     ||--o{ REVIEWS     : "receives"
    CUSTOMERS    ||--o{ REVIEWS     : "writes"
    ORDERS       ||--o{ RETURNS     : "leads to"
    CUSTOMERS    ||--o{ CUSTOMERS   : "refers"
    EMPLOYEES    ||--o{ EMPLOYEES   : "manages"

    CATEGORIES {
        int id PK
        varchar name UK
        text description
    }
    SUPPLIERS {
        int id PK
        varchar name
        varchar country
        varchar email
        boolean active
    }
    PRODUCTS {
        int id PK
        varchar name
        int category_id FK
        int supplier_id FK
        numeric price
        numeric cost
        int stock
        boolean active
        date added_date
    }
    CUSTOMERS {
        int id PK
        varchar name
        varchar last_name
        varchar email UK
        varchar city
        varchar country
        date signup_date
        int referred_by_id FK
    }
    EMPLOYEES {
        int id PK
        varchar name
        varchar last_name
        varchar job_title
        int manager_id FK
        numeric salary
        date hire_date
        varchar city
    }
    ORDERS {
        int id PK
        int customer_id FK
        int employee_id FK
        date order_date
        varchar status
        varchar payment_method
        numeric shipping_cost
    }
    ORDER_LINES {
        int id PK
        int order_id FK
        int product_id FK
        int quantity
        numeric unit_price
        numeric discount
    }
    REVIEWS {
        int id PK
        int product_id FK
        int customer_id FK
        smallint rating
        text comment
        date date
    }
    RETURNS {
        int id PK
        int order_id FK
        varchar reason
        date date
        numeric amount
    }

You'll recognise everything from the previous lesson in it: nine 1:N relationships, one N:M resolved with the bridge table order_lines, and two reflexive relationships (customers.referred_by_id and employees.manager_id).

The nine tables, one by one

3.1. `categories` — catalogue classification

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK. Category identifier
`name`	`VARCHAR(60)`	No	Display name. `UNIQUE`: there can't be two identical categories
`description`	`TEXT`	Yes	Descriptive text for the category page

6 rows. Values of name: Food, Natural cosmetics, Sustainable home, Drinks, Personal hygiene, Supplements.

3.2. `suppliers` — who supplies the products

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`name`	`VARCHAR(120)`	No	The supplier's registered name
`country`	`VARCHAR(60)`	No	Country of origin: Spain, Portugal, France, Germany
`email`	`VARCHAR(120)`	Yes	Commercial contact
`active`	`BOOLEAN`	No	`FALSE` if we no longer buy from them. Defaults to `TRUE`

5 rows. Supplier 5 (EcoNordic Supplies) is inactive but still has products in the catalogue: useful for practising filters and JOINs.

3.3. `products` — the catalogue

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`name`	`VARCHAR(150)`	No	Commercial name with format/weight
`category_id`	`INTEGER`	Yes	FK → `categories.id`. `ON DELETE RESTRICT`
`supplier_id`	`INTEGER`	Yes	FK → `suppliers.id`. `ON DELETE RESTRICT`
`price`	`NUMERIC(10,2)`	No	Retail selling price, in euros
`cost`	`NUMERIC(10,2)`	Yes	Purchase cost. `price - cost` is the gross margin
`stock`	`INTEGER`	No	Units available. Defaults to 0
`active`	`BOOLEAN`	No	`FALSE` if discontinued (soft delete)
`added_date`	`DATE`	No	Date it joined the catalogue

20 rows. Prices from €1.95 to €22.00. Points you'll need in later modules:

Product 13 (Soy wax candles) has stock 0.
Product 20 (Spirulina capsules) has active = FALSE.
Products 13, 19 and 20 have never been sold: they don't appear in order_lines. They're essential for the LEFT JOIN (03-03) and null-value (04-03) lessons.

3.4. `customers` — who buys

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`name`	`VARCHAR(60)`	No	First name
`last_name`	`VARCHAR(90)`	No	Surname(s)
`email`	`VARCHAR(120)`	No	`UNIQUE`. The customer's natural key
`city`	`VARCHAR(80)`	Yes	City of residence
`country`	`VARCHAR(60)`	No	Spain, Portugal or France
`signup_date`	`DATE`	No	Registration with the shop
`referred_by_id`	`INTEGER`	Yes	FK → `customers.id` (reflexive). `NULL` = arrived on their own. `ON DELETE SET NULL`

15 rows. 11 from Spain, 2 from Portugal, 2 from France. Eight customers have a referred_by_id. Customers 13, 14 and 15 have never placed an order: they're needed for the LEFT JOIN and NOT EXISTS lessons.

3.5. `employees` — the team

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`name`	`VARCHAR(60)`	No	First name
`last_name`	`VARCHAR(90)`	No	Surname(s)
`job_title`	`VARCHAR(80)`	No	Role
`manager_id`	`INTEGER`	Yes	FK → `employees.id` (reflexive). `NULL` only for general management. `ON DELETE SET NULL`
`salary`	`NUMERIC(10,2)`	Yes	Gross annual salary in euros
`hire_date`	`DATE`	No	Date they joined
`city`	`VARCHAR(80)`	Yes	City they work in

8 rows with this hierarchy:

Rosa Alcázar Vives (1) — General manager
├── Andrés Company Talens (2) — Sales manager
│   ├── Óscar Peris Blasco (4) — Sales rep
│   ├── Laia Puig Sanchis (5) — Sales rep
│   └── Marc Estévez Roig (6) — Customer support
├── Beatriz Nadal Ripoll (3) — Logistics manager
│   └── Irene Salvador Mira (7) — Warehouse operator
└── Daniel Vercher Lluch (8) — Data analyst

Only employees 4, 5 and 6 have orders assigned. The other five never appear in orders.

3.6. `orders` — the header of each purchase

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`customer_id`	`INTEGER`	No	FK → `customers.id`. `ON DELETE RESTRICT`
`employee_id`	`INTEGER`	Yes	FK → `employees.id`. `NULL` = web order with no sales rep. `ON DELETE SET NULL`
`order_date`	`DATE`	No	Date it was placed
`status`	`VARCHAR(20)`	No	`pending`, `paid`, `shipped`, `delivered`, `cancelled`
`payment_method`	`VARCHAR(20)`	No	`card`, `transfer`, `paypal`, `cash_on_delivery`
`shipping_cost`	`NUMERIC(10,2)`	No	Shipping charged. `0.00` when delivery was free

20 rows spread between March 2025 and February 2026. Distribution by status: 14 delivered, 2 shipped, 2 paid, 1 pending, 1 cancelled. Ten orders have no employee assigned (employee_id IS NULL).

Both status and payment_method are protected by a CHECK constraint that rejects values outside the domain.

3.7. `order_lines` — the detail of each purchase

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`order_id`	`INTEGER`	No	FK → `orders.id`. `ON DELETE CASCADE`
`product_id`	`INTEGER`	No	FK → `products.id`. `ON DELETE RESTRICT`
`quantity`	`INTEGER`	No	Units. Always > 0
`unit_price`	`NUMERIC(10,2)`	No	The price at the moment of the sale, not the current one
`discount`	`NUMERIC(4,2)`	No	A fraction between 0.00 and 1.00 (`0.10` = 10 %). Defaults to `0.00`

47 rows. It's the bridge table of the N:M relationship between orders and products, and the most queried table of the course: a line's amount is calculated as quantity * unit_price * (1 - discount).

3.8. `reviews` — customer opinions

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`product_id`	`INTEGER`	No	FK → `products.id`. `ON DELETE CASCADE`
`customer_id`	`INTEGER`	No	FK → `customers.id`. `ON DELETE CASCADE`
`rating`	`SMALLINT`	No	From 1 to 5, protected by a `CHECK`
`comment`	`TEXT`	Yes	Free text of the review
`date`	`DATE`	No	Publication date

12 rows. They all belong to customers who bought that product, and their date is always later than the order's. Most products have no review at all, which makes the table ideal for practising LEFT JOIN and HAVING.

3.9. `returns` — refunds

Column	Type	Null	Description
`id`	`INTEGER` identity	No	PK
`order_id`	`INTEGER`	No	FK → `orders.id`. `ON DELETE CASCADE`
`reason`	`VARCHAR(200)`	No	Reason for the return
`date`	`DATE`	No	Date it was processed
`amount`	`NUMERIC(10,2)`	No	Amount refunded in euros

3 rows, tied to orders 6, 10 and 13.

Design decisions worth knowing about

Some of the schema's choices aren't obvious and you'll see them come up again and again in the exercises:

Decision	Reason
Object names are lowercase ASCII (`order_lines`, `reviews`)	Identifiers have to be portable and quote-free. The data does carry accents (Castellón, Lucía Martínez Soler); the object names don't
`unit_price` duplicates information from `products.price`	It's deliberate denormalisation: it stores the historical price. Two old lines (orders 1 and 2) have a lower price than the current one, precisely so you can check it
`discount` is a fraction (0.10), not a percentage (10)	It avoids multiplying and dividing by 100 in every query: the amount is `quantity * unit_price * (1 - discount)`
There's no `total` column in `orders`	It's calculated by adding up the lines. Storing it would be a precomputed aggregate that would have to be kept in sync (module 10)
Every amount is `NUMERIC(10,2)`	Exact money, never floating point (lesson 01-04)
Every date is a `DATE`	They're calendar dates, not instants with a time zone
Every PK is a surrogate `id`	Uniformity and stability (lesson 01-05)
There are deliberate gaps in the data	Customers with no orders, products never sold, orders with no employee, products with no reviews: the course needs them

The table creation script

Create a file called greenstore.sql and copy into it the two blocks from this section and the next, in order.

The first block drops the tables if they already exist (so you can rerun the script as many times as you like) and creates them in dependency order: a table can't reference another one that doesn't exist yet.

-- =====================================================================
-- GreenStore - SQL course database
-- PostgreSQL 16
-- Block 1: schema creation
-- =====================================================================

-- Drop first, in reverse dependency order.
-- CASCADE also removes the constraints that point at these tables.
DROP TABLE IF EXISTS returns     CASCADE;
DROP TABLE IF EXISTS reviews     CASCADE;
DROP TABLE IF EXISTS order_lines CASCADE;
DROP TABLE IF EXISTS orders      CASCADE;
DROP TABLE IF EXISTS employees   CASCADE;
DROP TABLE IF EXISTS customers   CASCADE;
DROP TABLE IF EXISTS products    CASCADE;
DROP TABLE IF EXISTS suppliers   CASCADE;
DROP TABLE IF EXISTS categories  CASCADE;

-- ---------------------------------------------------------------------
-- 1. Tables with no dependencies
-- ---------------------------------------------------------------------
CREATE TABLE categories (
    id          INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name        VARCHAR(60)  NOT NULL UNIQUE,
    description TEXT
);

CREATE TABLE suppliers (
    id      INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name    VARCHAR(120) NOT NULL,
    country VARCHAR(60)  NOT NULL,
    email   VARCHAR(120),
    active  BOOLEAN      NOT NULL DEFAULT TRUE
);

-- ---------------------------------------------------------------------
-- 2. Tables that depend on the previous ones
-- ---------------------------------------------------------------------
CREATE TABLE products (
    id          INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name        VARCHAR(150)  NOT NULL,
    category_id INTEGER       REFERENCES categories(id) ON DELETE RESTRICT,
    supplier_id INTEGER       REFERENCES suppliers(id)  ON DELETE RESTRICT,
    price       NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    cost        NUMERIC(10,2) CHECK (cost >= 0),
    stock       INTEGER       NOT NULL DEFAULT 0 CHECK (stock >= 0),
    active      BOOLEAN       NOT NULL DEFAULT TRUE,
    added_date  DATE          NOT NULL DEFAULT CURRENT_DATE
);

-- Reflexive relationship: a customer may have been referred by another customer
CREATE TABLE customers (
    id             INTEGER      GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name           VARCHAR(60)  NOT NULL,
    last_name      VARCHAR(90)  NOT NULL,
    email          VARCHAR(120) NOT NULL UNIQUE,
    city           VARCHAR(80),
    country        VARCHAR(60)  NOT NULL,
    signup_date    DATE         NOT NULL DEFAULT CURRENT_DATE,
    referred_by_id INTEGER      REFERENCES customers(id) ON DELETE SET NULL
);

-- Reflexive relationship: organisational hierarchy
CREATE TABLE employees (
    id         INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    name       VARCHAR(60)   NOT NULL,
    last_name  VARCHAR(90)   NOT NULL,
    job_title  VARCHAR(80)   NOT NULL,
    manager_id INTEGER       REFERENCES employees(id) ON DELETE SET NULL,
    salary     NUMERIC(10,2) CHECK (salary >= 0),
    hire_date  DATE          NOT NULL,
    city       VARCHAR(80)
);

CREATE TABLE orders (
    id             INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    customer_id    INTEGER       NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    employee_id    INTEGER                REFERENCES employees(id) ON DELETE SET NULL,
    order_date     DATE          NOT NULL,
    status         VARCHAR(20)   NOT NULL
                   CHECK (status IN ('pending','paid','shipped','delivered','cancelled')),
    payment_method VARCHAR(20)   NOT NULL
                   CHECK (payment_method IN ('card','transfer','paypal','cash_on_delivery')),
    shipping_cost  NUMERIC(10,2) NOT NULL DEFAULT 0 CHECK (shipping_cost >= 0)
);

-- ---------------------------------------------------------------------
-- 3. Bridge table of the N:M relationship between orders and products
-- ---------------------------------------------------------------------
CREATE TABLE order_lines (
    id         INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id   INTEGER       NOT NULL REFERENCES orders(id)   ON DELETE CASCADE,
    product_id INTEGER       NOT NULL REFERENCES products(id) ON DELETE RESTRICT,
    quantity   INTEGER       NOT NULL CHECK (quantity > 0),
    unit_price NUMERIC(10,2) NOT NULL CHECK (unit_price >= 0),
    discount   NUMERIC(4,2)  NOT NULL DEFAULT 0
               CHECK (discount >= 0 AND discount <= 1)
);

-- ---------------------------------------------------------------------
-- 4. Satellite tables
-- ---------------------------------------------------------------------
CREATE TABLE reviews (
    id          INTEGER  GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    product_id  INTEGER  NOT NULL REFERENCES products(id)  ON DELETE CASCADE,
    customer_id INTEGER  NOT NULL REFERENCES customers(id) ON DELETE CASCADE,
    rating      SMALLINT NOT NULL CHECK (rating BETWEEN 1 AND 5),
    comment     TEXT,
    date        DATE     NOT NULL
);

CREATE TABLE returns (
    id       INTEGER       GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    order_id INTEGER       NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    reason   VARCHAR(200)  NOT NULL,
    date     DATE          NOT NULL,
    amount   NUMERIC(10,2) NOT NULL CHECK (amount >= 0)
);

What each part does, in two minutes

Without going into the syntax (module 5), these are the elements you'll see recurring:

Element	What it means
`GENERATED BY DEFAULT AS IDENTITY`	The `id` is generated only if you don't supply one. It's the modern standard form of an auto-increment
`PRIMARY KEY`	Primary key: unique and not null
`NOT NULL`	The column is mandatory
`UNIQUE`	There can't be two rows with the same value (`categories.name`, `customers.email`)
`REFERENCES table(id)`	Foreign key: the value must exist in the referenced table
`ON DELETE RESTRICT / CASCADE / SET NULL`	What to do if the parent is deleted (lesson 01-05)
`CHECK (...)`	Domain constraint: rejects values outside the allowed range
`DEFAULT value`	The value used if you don't supply one

Notice two details about the ordering:

Tables are created in dependency order. products references categories and suppliers, so those two come first. The dropping (DROP) is done in reverse order.
Reflexive tables reference themselves inside their own definition (customers.referred_by_id REFERENCES customers(id)). PostgreSQL allows this without any trouble.

The data loading script

Add this second block to the end of the same greenstore.sql file.

-- =====================================================================
-- Block 2: data loading
-- =====================================================================

-- ---------------------------------------------------------------------
-- categories (6)
-- ---------------------------------------------------------------------
INSERT INTO categories (id, name, description) VALUES
(1, 'Food',              'Organic dry food and preserves'),
(2, 'Natural cosmetics', 'Cosmetics with natural ingredients and no parabens'),
(3, 'Sustainable home',  'Cleaning and household goods in reusable materials'),
(4, 'Drinks',            'Organic teas, juices and fermented drinks'),
(5, 'Personal hygiene',  'Daily hygiene with minimal or compostable packaging'),
(6, 'Supplements',       'Plant-based dietary supplements');

-- ---------------------------------------------------------------------
-- suppliers (5) - number 5 is inactive but still has products
-- ---------------------------------------------------------------------
INSERT INTO suppliers (id, name, country, email, active) VALUES
(1, 'Huerta del Turia',   'Spain',    '[email protected]',   TRUE),
(2, 'BioSierra Ibérica',  'Spain',    '[email protected]',      TRUE),
(3, 'Verde Atlántico',    'Portugal', '[email protected]', TRUE),
(4, 'Maison Nature',      'France',   '[email protected]',     TRUE),
(5, 'EcoNordic Supplies', 'Germany',  '[email protected]',          FALSE);

-- ---------------------------------------------------------------------
-- products (20)
--   13 -> stock 0 ; 20 -> active FALSE ; 13, 19 and 20 never sold
-- ---------------------------------------------------------------------
INSERT INTO products (id, name, category_id, supplier_id, price, cost, stock, active, added_date) VALUES
( 1, 'Extra virgin olive oil 500 ml',          1, 1, 12.50,  7.80, 120, TRUE,  '2025-01-15'),
( 2, 'Organic brown rice 1 kg',                1, 1,  3.90,  2.10, 200, TRUE,  '2025-01-15'),
( 3, 'Raw orange blossom honey 500 g',         1, 2,  9.75,  5.40,  80, TRUE,  '2025-01-15'),
( 4, 'Spelt pasta 500 g',                      1, 2,  2.80,  1.35, 150, TRUE,  '2025-01-20'),
( 5, 'Organic crushed tomato 400 g',           1, 1,  1.95,  0.90, 300, TRUE,  '2025-01-20'),
( 6, 'Aloe vera face cream 50 ml',             2, 4, 18.90,  9.50,  60, TRUE,  '2025-01-20'),
( 7, 'Rosemary solid shampoo 80 g',            2, 4,  8.40,  3.60,  95, TRUE,  '2025-02-01'),
( 8, 'Almond body oil 200 ml',                 2, 3, 14.25,  7.10,  45, TRUE,  '2025-02-01'),
( 9, 'Calendula lip balm 15 ml',               2, 4,  4.60,  1.80, 130, TRUE,  '2025-02-01'),
(10, 'Concentrated eco laundry detergent 1 L', 3, 5, 11.20,  6.00,  70, TRUE,  '2025-02-10'),
(11, 'Loofah scrubber (pack of 3)',            3, 3,  5.50,  2.20, 110, TRUE,  '2025-02-10'),
(12, 'Reusable cotton bags (pack of 5)',       3, 3,  9.90,  4.30,  85, TRUE,  '2025-02-10'),
(13, 'Soy wax candles (pack of 2)',            3, 5, 13.75,  6.90,   0, TRUE,  '2025-03-01'),
(14, 'Organic chamomile tea 20 bags',          4, 2,  3.25,  1.40, 180, TRUE,  '2025-02-20'),
(15, 'Ceremonial matcha green tea 30 g',       4, 3, 22.00, 12.50,  40, TRUE,  '2025-02-20'),
(16, 'Ginger kombucha 750 ml',                 4, 1,  4.95,  2.30,  60, TRUE,  '2025-03-15'),
(17, 'Cold-pressed orange juice 1 L',          4, 1,  5.40,  2.60,  90, TRUE,  '2025-03-15'),
(18, 'Bamboo toothbrush',                      5, 5,  3.50,  1.20, 240, TRUE,  '2025-04-01'),
(19, 'Natural stick deodorant 50 g',           5, 4,  7.80,  3.30,  75, TRUE,  '2025-05-10'),
(20, 'Spirulina capsules 120 units',           6, 5, 16.40,  8.70,  55, FALSE, '2025-06-01');

-- ---------------------------------------------------------------------
-- customers (15) - numbers 13, 14 and 15 have never placed an order
-- ---------------------------------------------------------------------
INSERT INTO customers (id, name, last_name, email, city, country, signup_date, referred_by_id) VALUES
( 1, 'Lucía',   'Martínez Soler',  '[email protected]',  'Valencia',  'Spain',    '2025-01-10', NULL),
( 2, 'Carlos',  'Ferrer Ibáñez',   '[email protected]',   'Valencia',  'Spain',    '2025-01-22', 1),
( 3, 'Marta',   'Sanchis Gil',     '[email protected]',   'Castellón', 'Spain',    '2025-02-03', 1),
( 4, 'Javier',  'Ortega Ruiz',     '[email protected]',   'Madrid',    'Spain',    '2025-02-14', NULL),
( 5, 'Ana',     'Belmonte Roca',   '[email protected]',    'Barcelona', 'Spain',    '2025-02-27', 2),
( 6, 'Pau',     'Llorens Vidal',   '[email protected]',     'Valencia',  'Spain',    '2025-03-09', NULL),
( 7, 'Sofia',   'Moreira Costa',   '[email protected]',    'Lisbon',    'Portugal', '2025-03-21', NULL),
( 8, 'Tiago',   'Almeida Nunes',   '[email protected]',    'Porto',     'Portugal', '2025-04-04', 7),
( 9, 'Camille', 'Dubois',          '[email protected]',   'Lyon',      'France',   '2025-04-18', NULL),
(10, 'Julien',  'Moreau',          '[email protected]',    'Paris',     'France',   '2025-05-02', 9),
(11, 'Elena',   'Navarro Puig',    '[email protected]',   'Alicante',  'Spain',    '2025-05-16', 6),
(12, 'Diego',   'Ramos Herrera',   '[email protected]',     'Seville',   'Spain',    '2025-06-01', NULL),
(13, 'Núria',   'Bosch Ferrer',    '[email protected]',     'Barcelona', 'Spain',    '2025-06-20', 5),
(14, 'Hugo',    'Iglesias Pardo',  '[email protected]',   'Zaragoza',  'Spain',    '2025-09-12', NULL),
(15, 'Inés',    'Carrasco Vega',   '[email protected]',   'Valencia',  'Spain',    '2026-01-08', 1);

-- ---------------------------------------------------------------------
-- employees (8) - hierarchy via manager_id ; only 4, 5 and 6 handle orders
-- ---------------------------------------------------------------------
INSERT INTO employees (id, name, last_name, job_title, manager_id, salary, hire_date, city) VALUES
(1, 'Rosa',    'Alcázar Vives',   'General manager',       NULL, 62000.00, '2024-09-01', 'Valencia'),
(2, 'Andrés',  'Company Talens',  'Sales manager',            1, 41000.00, '2024-10-15', 'Valencia'),
(3, 'Beatriz', 'Nadal Ripoll',    'Logistics manager',        1, 39500.00, '2024-11-02', 'Valencia'),
(4, 'Óscar',   'Peris Blasco',    'Sales rep',                2, 28500.00, '2025-01-13', 'Valencia'),
(5, 'Laia',    'Puig Sanchis',    'Sales rep',                2, 27800.00, '2025-02-17', 'Castellón'),
(6, 'Marc',    'Estévez Roig',    'Customer support',         2, 24500.00, '2025-03-24', 'Valencia'),
(7, 'Irene',   'Salvador Mira',   'Warehouse operator',       3, 22000.00, '2025-04-07', 'Valencia'),
(8, 'Daniel',  'Vercher Lluch',   'Data analyst',             1, 35000.00, '2025-06-16', 'Valencia');

-- ---------------------------------------------------------------------
-- orders (20) - 10 with no employee assigned (web orders)
-- ---------------------------------------------------------------------
INSERT INTO orders (id, customer_id, employee_id, order_date, status, payment_method, shipping_cost) VALUES
( 1,  1, NULL, '2025-03-04', 'delivered', 'card',              4.95),
( 2,  2,    4, '2025-03-12', 'delivered', 'transfer',          0.00),
( 3,  3, NULL, '2025-04-02', 'delivered', 'card',              4.95),
( 4,  4,    5, '2025-04-19', 'delivered', 'paypal',            4.95),
( 5,  1, NULL, '2025-05-07', 'delivered', 'card',              0.00),
( 6,  5,    4, '2025-05-23', 'cancelled', 'card',              4.95),
( 7,  6, NULL, '2025-06-11', 'delivered', 'cash_on_delivery',  6.50),
( 8,  7,    5, '2025-06-28', 'delivered', 'card',              9.90),
( 9,  8, NULL, '2025-07-15', 'delivered', 'paypal',            9.90),
(10,  9,    4, '2025-08-03', 'delivered', 'card',             12.50),
(11,  2, NULL, '2025-09-09', 'delivered', 'card',              0.00),
(12, 10,    5, '2025-10-01', 'delivered', 'transfer',         12.50),
(13, 11, NULL, '2025-10-22', 'delivered', 'card',              4.95),
(14, 12,    6, '2025-11-14', 'delivered', 'paypal',            4.95),
(15,  1, NULL, '2025-12-02', 'delivered', 'card',              0.00),
(16,  4,    4, '2025-12-19', 'shipped',   'card',              4.95),
(17,  7, NULL, '2026-01-13', 'shipped',   'paypal',            9.90),
(18,  5,    5, '2026-01-27', 'paid',      'transfer',          4.95),
(19,  6, NULL, '2026-02-09', 'paid',      'card',              4.95),
(20,  9,    6, '2026-02-21', 'pending',   'cash_on_delivery', 12.50);

-- ---------------------------------------------------------------------
-- order_lines (47)
--   Lines 1 and 4 carry the HISTORICAL price, from before the April 2025
--   price rise: that's why it doesn't match products.price
-- ---------------------------------------------------------------------
INSERT INTO order_lines (id, order_id, product_id, quantity, unit_price, discount) VALUES
( 1,  1,  1, 2, 11.95, 0.00),
( 2,  1,  2, 3,  3.90, 0.00),
( 3,  1, 14, 2,  3.25, 0.00),
( 4,  2,  6, 1, 17.50, 0.00),
( 5,  2,  9, 2,  4.60, 0.00),
( 6,  3,  5, 6,  1.95, 0.10),
( 7,  3,  4, 4,  2.80, 0.00),
( 8,  3,  2, 2,  3.90, 0.00),
( 9,  4, 15, 1, 22.00, 0.00),
(10,  4,  3, 1,  9.75, 0.00),
(11,  5, 10, 1, 11.20, 0.00),
(12,  5, 11, 2,  5.50, 0.00),
(13,  5, 12, 1,  9.90, 0.00),
(14,  6,  1, 1, 12.50, 0.00),
(15,  6,  8, 1, 14.25, 0.00),
(16,  7, 16, 4,  4.95, 0.00),
(17,  7, 17, 2,  5.40, 0.00),
(18,  8,  1, 3, 12.50, 0.05),
(19,  8,  3, 2,  9.75, 0.00),
(20,  8, 14, 3,  3.25, 0.00),
(21,  9,  7, 2,  8.40, 0.00),
(22,  9,  9, 3,  4.60, 0.00),
(23,  9, 18, 4,  3.50, 0.00),
(24, 10,  6, 2, 18.90, 0.10),
(25, 10,  8, 1, 14.25, 0.00),
(26, 11,  2, 5,  3.90, 0.00),
(27, 11,  5, 8,  1.95, 0.15),
(28, 12, 15, 2, 22.00, 0.00),
(29, 12, 14, 4,  3.25, 0.00),
(30, 12, 16, 2,  4.95, 0.00),
(31, 13, 12, 2,  9.90, 0.00),
(32, 13, 18, 3,  3.50, 0.00),
(33, 14,  1, 1, 12.50, 0.00),
(34, 14,  4, 3,  2.80, 0.00),
(35, 14, 17, 2,  5.40, 0.00),
(36, 15,  3, 2,  9.75, 0.00),
(37, 15,  7, 1,  8.40, 0.00),
(38, 15, 11, 1,  5.50, 0.00),
(39, 16, 10, 2, 11.20, 0.05),
(40, 16, 12, 1,  9.90, 0.00),
(41, 17,  1, 2, 12.50, 0.00),
(42, 17, 15, 1, 22.00, 0.00),
(43, 18,  6, 1, 18.90, 0.00),
(44, 18,  9, 2,  4.60, 0.00),
(45, 19, 16, 6,  4.95, 0.10),
(46, 20,  2, 4,  3.90, 0.00),
(47, 20, 18, 2,  3.50, 0.00);

-- ---------------------------------------------------------------------
-- reviews (12) - always from customers who bought that product
-- ---------------------------------------------------------------------
INSERT INTO reviews (id, product_id, customer_id, rating, comment, date) VALUES
( 1,  1,  1, 5, 'Excellent oil, intense flavour and beautiful packaging.',    '2025-03-15'),
( 2,  2,  1, 4, 'Good rice, though it takes a little longer than usual.',     '2025-03-16'),
( 3,  6,  2, 5, 'The cream leaves the skin very soft. I will buy it again.',  '2025-03-25'),
( 4,  5,  3, 3, 'Fine for the price, nothing spectacular.',                   '2025-04-12'),
( 5, 15,  4, 5, 'Genuinely ceremonial-grade matcha, flawless colour.',        '2025-05-02'),
( 6, 16,  6, 2, 'Far too much ginger for my taste, barely drinkable.',        '2025-06-20'),
( 7,  1,  7, 5, 'I buy it every month, unbeatable value for money.',          '2025-07-08'),
( 8, 18,  8, 4, 'Does the job perfectly, though the bristles are hard.',      '2025-07-26'),
( 9,  6,  9, 4, 'Very good hydration, delivery to France took a while.',      '2025-08-14'),
(10,  2,  2, 5, 'Loose grains and a clean taste, better than supermarkets.',  '2025-09-19'),
(11, 12, 11, 3, 'Sturdy bags but smaller than I was expecting.',              '2025-11-03'),
(12, 10,  4, 4, 'Goes a very long way, one litre lasts for months.',          '2026-01-10');

-- ---------------------------------------------------------------------
-- returns (3)
-- ---------------------------------------------------------------------
INSERT INTO returns (id, order_id, reason, date, amount) VALUES
(1,  6, 'Order cancelled by the customer before shipping', '2025-05-25', 26.75),
(2, 10, 'Product damaged in transit',                      '2025-08-11', 34.02),
(3, 13, 'The format does not match what was expected',     '2025-10-30', 19.80);

-- ---------------------------------------------------------------------
-- Sync the identity sequences with the ids already inserted, so that
-- future INSERTs without an id don't clash with the keys already used.
-- ---------------------------------------------------------------------
SELECT setval(pg_get_serial_sequence('categories',  'id'), (SELECT MAX(id) FROM categories));
SELECT setval(pg_get_serial_sequence('suppliers',   'id'), (SELECT MAX(id) FROM suppliers));
SELECT setval(pg_get_serial_sequence('products',    'id'), (SELECT MAX(id) FROM products));
SELECT setval(pg_get_serial_sequence('customers',   'id'), (SELECT MAX(id) FROM customers));
SELECT setval(pg_get_serial_sequence('employees',   'id'), (SELECT MAX(id) FROM employees));
SELECT setval(pg_get_serial_sequence('orders',      'id'), (SELECT MAX(id) FROM orders));
SELECT setval(pg_get_serial_sequence('order_lines', 'id'), (SELECT MAX(id) FROM order_lines));
SELECT setval(pg_get_serial_sequence('reviews',     'id'), (SELECT MAX(id) FROM reviews));
SELECT setval(pg_get_serial_sequence('returns',     'id'), (SELECT MAX(id) FROM returns));

Explanation of the loading script, block by block

Block	What it loads	The detail that matters
`categories`	6 categories	No dependencies: they go first
`suppliers`	5 suppliers	Number 5 (EcoNordic) has `active = FALSE` but keeps its products
`products`	20 products	Number 13 with stock 0, number 20 discontinued; 13, 19 and 20 will never be sold
`customers`	15 customers	Eight with a `referred_by_id`; 13, 14 and 15 with no orders
`employees`	8 employees	Number 1 has `manager_id NULL`; only 4, 5 and 6 appear in `orders`
`orders`	20 orders	March 2025 – February 2026; 10 with `employee_id NULL`
`order_lines`	47 lines	Two with a historical price different from the current one
`reviews`	12 reviews	The date is always later than that of the corresponding order
`returns`	3 returns	Tied to orders 6, 10 and 13
`setval(...)`	—	Adjusts the sequences after inserting explicit ids

That last block deserves an explanation. Since we've inserted the id values by hand, each table's internal counter is still at 1. If tomorrow you inserted a customer without specifying an id, PostgreSQL would try to assign 1 and fail with a duplicate key. setval moves the counter forward to the highest value already used. It's a routine detail when loading initial data, and you'll see it again in module 5.

A note about multi-row INSERTs: each statement inserts many rows with a single instruction, separating the tuples with commas. It's far faster than one INSERT per row, and the full syntax is studied in lesson 05-02.

How to load the database

If you haven't created it yet, go back over lesson 01-02. With the greenstore database already in place, there are two ways to run the script.

From the command line (recommended)

psql -h localhost -U sql_course -d greenstore -f greenstore.sql

Expected output (abridged):

DROP TABLE
...
CREATE TABLE
CREATE TABLE
...
INSERT 0 6
INSERT 0 5
INSERT 0 20
INSERT 0 15
INSERT 0 8
INSERT 0 20
INSERT 0 47
INSERT 0 12
INSERT 0 3
 setval
--------
      6
...

Each INSERT 0 N tells you how many rows that statement inserted. If you see the numbers 6, 5, 20, 15, 8, 20, 47, 12 and 3 in that order, the load went fine.

From inside `psql`

psql -h localhost -U sql_course -d greenstore

greenstore=> \i greenstore.sql

With Docker, if the file is on your machine and PostgreSQL is in the container, you can copy it inside or pipe it straight in:
docker exec -i pg-course psql -U postgres -d greenstore < greenstore.sql

If something fails

Error	Cause and fix
`permission denied for schema public`	You're missing `GRANT ALL ON SCHEMA public TO sql_course;` run as superuser
`database "greenstore" does not exist`	Create it first (lesson 01-02)
Odd characters instead of accented letters	The database isn't in UTF-8. Recreate it with `ENCODING 'UTF8'`
`relation "categories" already exists`	You're running only block 2. Run the whole file: the initial `DROP TABLE IF EXISTS` sorts it out

The script is idempotent: you can run it as many times as you like and it will always leave the database in the same state. If in some module you experiment with UPDATE or DELETE and want to go back to the starting point, just run it again.

Verification queries

Check that everything is where it should be.

8.1. The nine tables exist

greenstore=> \dt

You should see all nine: categories, customers, employees, order_lines, orders, products, returns, reviews, suppliers.

8.2. Number of rows per table

SELECT 'categories'  AS table_name, COUNT(*) AS row_count FROM categories
UNION ALL SELECT 'suppliers',   COUNT(*) FROM suppliers
UNION ALL SELECT 'products',    COUNT(*) FROM products
UNION ALL SELECT 'customers',   COUNT(*) FROM customers
UNION ALL SELECT 'employees',   COUNT(*) FROM employees
UNION ALL SELECT 'orders',      COUNT(*) FROM orders
UNION ALL SELECT 'order_lines', COUNT(*) FROM order_lines
UNION ALL SELECT 'reviews',     COUNT(*) FROM reviews
UNION ALL SELECT 'returns',     COUNT(*) FROM returns;

Expected result:

table_name	row_count
categories	6
suppliers	5
products	20
customers	15
employees	8
orders	20
order_lines	47
reviews	12
returns	3

(Don't worry about the syntax of UNION ALL or COUNT: they're studied in modules 3 and 4. Here it's just a verification tool.)

8.3. The deliberate "gaps" are where they should be

SELECT
  (SELECT COUNT(*) FROM customers WHERE id NOT IN (SELECT customer_id FROM orders))       AS customers_without_orders,
  (SELECT COUNT(*) FROM products  WHERE id NOT IN (SELECT product_id FROM order_lines))   AS products_never_sold,
  (SELECT COUNT(*) FROM orders    WHERE employee_id IS NULL)                              AS orders_without_employee,
  (SELECT COUNT(*) FROM customers WHERE referred_by_id IS NULL)                           AS customers_not_referred,
  (SELECT COUNT(*) FROM employees WHERE manager_id IS NULL)                               AS employees_without_manager;

customers_without_orders	products_never_sold	orders_without_employee	customers_not_referred	employees_without_manager
3	3	10	7	1

If these five numbers match, your database is exactly the one every following lesson will use.

8.4. A glance at the data

SELECT id, name, price, stock, active FROM products WHERE id <= 5;

id	name	price	stock	active
1	Extra virgin olive oil 500 ml	12.50	120	true
2	Organic brown rice 1 kg	3.90	200	true
3	Raw orange blossom honey 500 g	9.75	80	true
4	Spelt pasta 500 g	2.80	150	true
5	Organic crushed tomato 400 g	1.95	300	true

SELECT status, COUNT(*) AS order_count FROM orders GROUP BY status ORDER BY order_count DESC;

status	order_count
delivered	14
shipped	2
paid	2
cancelled	1
pending	1

Common Mistakes and Tips

Running the blocks out of order. Tables have to be created before their dependants and the data loaded in the same order. Always run the whole file.
Copying the script only halfway. An INSERT cut in half leaves the database inconsistent. Copy block by block and check the row counters.
Changing the data and not being able to go back. The script is idempotent: rerun it and you're back to the initial state. Keep it somewhere you can find it.
Being surprised that unit_price doesn't match price. In lines 1 and 4 that's intentional: they're historical prices from before the price rise.
Reading discount as a percentage. It's a fraction: 0.10 means 10 %. A line's amount is quantity * unit_price * (1 - discount).
Expecting reviews on every product. Only 9 of the 20 products have reviews, and that's deliberate.
Forgetting the final setval. Without it, the first INSERT with no explicit id will fail with a duplicate key.
Tip: keep the diagram to hand. Come back to this lesson whenever you're unsure which table holds which column; it'll save you a lot of column does not exist.
Tip: use \d table before each exercise. It's faster than searching through the text.
Tip: make a backup. pg_dump -U sql_course -d greenstore -f backup.sql lets you restore in seconds if you break something.

Exercises

Exercise 1

Load the database on your machine and verify the installation by answering these four questions with the commands and queries from section 8:

Do the nine tables exist?
How many rows does each one have?
What columns, types and foreign keys does order_lines have?
How many orders have no employee assigned?

Exercise 2

Without running anything, and using only the diagram and the table descriptions, say which tables and which columns you'd need to answer each business question. Don't write the query: state the path between tables.

What is the average rating of the product "Extra virgin olive oil 500 ml"?
Which sales rep handled order number 12 and who is their manager?
How much did order 8 bill, shipping costs included?
Which country is the supplier of the most expensive product in the catalogue from?
Which customers were referred by Lucía Martínez Soler?

Exercise 3

Predict what will happen with each of these operations on the freshly loaded database. Then run them and check your prediction (remember you can reload the script to return to the initial state).

-- a)
INSERT INTO categories (name, description) VALUES ('Drinks', 'Duplicate');

-- b)
INSERT INTO reviews (product_id, customer_id, rating, comment, date)
VALUES (1, 14, 7, 'Great', '2026-03-01');

-- c)
INSERT INTO order_lines (order_id, product_id, quantity, unit_price, discount)
VALUES (20, 13, 1, 13.75, 0.00);

-- d)
DELETE FROM orders WHERE id = 5;
SELECT COUNT(*) FROM order_lines;

Solutions

Solution 1

greenstore=> \dt

Nine tables listed. For the row counts, the UNION ALL query from section 8.2, which must return 6, 5, 20, 15, 8, 20, 47, 12 and 3.

For the structure of order_lines:

greenstore=> \d order_lines

It shows the six columns (id, order_id, product_id, quantity, unit_price, discount), their types, the PK on id and the two foreign keys: order_id → orders(id) ON DELETE CASCADE and product_id → products(id) ON DELETE RESTRICT.

And for the orders with no employee:

SELECT COUNT(*) FROM orders WHERE employee_id IS NULL;

count
10

Solution 2

#	Question	Path between tables	Key columns
1	Average rating of a product	`products` → `reviews`	`products.name`, `reviews.product_id`, `reviews.rating`
2	Sales rep of order 12 and their manager	`orders` → `employees` → `employees` (self join)	`orders.employee_id`, `employees.id`, `employees.manager_id`
3	Revenue of order 8	`orders` → `order_lines`	`order_lines.quantity`, `unit_price`, `discount`, plus `orders.shipping_cost`
4	Country of the supplier of the most expensive product	`products` → `suppliers`	`products.price`, `products.supplier_id`, `suppliers.country`
5	Customers referred by Lucía	`customers` → `customers` (self join)	`customers.id`, `customers.referred_by_id`, `customers.name`

Notice that two of the five questions require joining a table to itself: that's what we'll meet as SELF JOIN in lesson 03-06, and it's the direct consequence of the schema's reflexive relationships.

Solution 3

a) It fails because of the UNIQUE constraint on categories.name:

ERROR:  duplicate key value violates unique constraint "categories_name_key"
DETAIL:  Key (name)=(Drinks) already exists.

It's the natural-key protection we mentioned in lesson 01-05: even though the PK is id, the UNIQUE prevents the name from being duplicated.

b) It fails because of the CHECK constraint on the rating:

ERROR:  new row for relation "reviews" violates check constraint "reviews_rating_check"
DETAIL:  Failing row contains (13, 1, 14, 7, Great, 2026-03-01).

The domain of rating is 1-5, and the SMALLINT type on its own doesn't guarantee that: the CHECK is needed. Notice as well that customer 14 has never bought that product; the database does not prevent that, because no constraint requires it. It's a good reminder that constraints only protect what you declare explicitly.

c) It works. Product 13 (Soy wax candles) exists and so does order 20, so the FK is satisfied. order_lines would now have 48 rows and product 13 would stop being "never sold".

INSERT 0 1

Careful: this breaks one of the deliberate gaps in the dataset. If you run it, reload the script before continuing with module 3, or some results in the LEFT JOIN lessons won't match. Note as well that the database hasn't checked whether there's stock (product 13 has 0 units): that rule is business logic and isn't declared as a constraint.

d) It works, and it deletes in cascade. Order 5 has three lines (ids 11, 12 and 13), and order_lines.order_id is declared ON DELETE CASCADE:

DELETE 1

count
44

A single statement has removed four rows across two tables. It's exactly the behaviour we anticipated in lesson 01-05 and the reason CASCADE should be reserved for genuine composition relationships. Reload the script to get the original state back.

Conclusion

With this lesson you close module 1 and, above all, you now have the ground prepared:

You know GreenStore as a business: what it sells, to whom, through which channels and with what team.
You can read its entity-relationship diagram: nine tables, nine 1:N relationships, one N:M resolved with order_lines and two reflexive relationships in customers and employees.
You have the column-by-column description of the nine tables, with their types, keys and meanings.
You've run the full script and verified the counts: 6 categories, 5 suppliers, 20 products, 15 customers, 8 employees, 20 orders, 47 lines, 12 reviews and 3 returns.
You understand the deliberate gaps in the dataset —3 customers with no orders, 3 products never sold, 10 orders with no employee, 11 products with no reviews— and why the LEFT JOIN, NULL and aggregation lessons need them.
You know the script is idempotent: you can always go back to the initial state by rerunning it.

You've completed module 1. By now you understand what SQL is and where it fits, you have PostgreSQL 16 running, you've got a grip on the language's writing rules, you know how data is structured and typed, you understand why tables relate the way they do and you have loaded the database that will accompany you all the way to the final project. In module 2, Basic SQL Queries, you'll finally start interrogating GreenStore: the SELECT statement and how to choose columns, aliases and calculated columns, filtering with WHERE, removing duplicates with DISTINCT, sorting with ORDER BY and limiting results with LIMIT. The first real query is one lesson away.

The Course Database: GreenStore

Contents

The business: what GreenStore is and how it operates

Entity-relationship diagram

The nine tables, one by one

3.1. categories — catalogue classification

3.2. suppliers — who supplies the products

3.3. products — the catalogue

3.4. customers — who buys

3.5. employees — the team

3.6. orders — the header of each purchase

3.7. order_lines — the detail of each purchase

3.8. reviews — customer opinions

3.9. returns — refunds

Design decisions worth knowing about

The table creation script

What each part does, in two minutes

The data loading script

Explanation of the loading script, block by block

How to load the database

From the command line (recommended)

From inside psql

If something fails

Verification queries

8.1. The nine tables exist

8.2. Number of rows per table

8.3. The deliberate "gaps" are where they should be

8.4. A glance at the data

Common Mistakes and Tips

Exercises

Exercise 1

Exercise 2

Exercise 3

Solutions

Conclusion

SQL Course

Module 1: Introduction to SQL

Module 2: Basic SQL queries

Module 3: Working with multiple tables

Module 4: Advanced data filtering

Module 5: Data manipulation

Module 6: Advanced SQL functions

Module 7: Subqueries and nested queries

Module 8: Indexes and performance tuning

Module 9: Transactions and concurrency

Module 10: Advanced topics

Module 11: SQL in practice

Module 12: Final project