schema.sql

-- =============================================================
-- Section 8 — Order of SQL
-- =============================================================
-- An e-commerce dataset with customers, products, and orders.
-- It's deliberately rich so the exercises in this section can
-- force you to use every clause — FROM, WHERE, GROUP BY, HAVING,
-- SELECT, ORDER BY, LIMIT — in combinations that make you think
-- about the logical order of execution.
-- =============================================================

DROP SCHEMA IF EXISTS sqlf_order CASCADE;
CREATE SCHEMA sqlf_order;
SET search_path TO sqlf_order;

CREATE TABLE customer (
    id         INTEGER      PRIMARY KEY,
    first_name VARCHAR(50)  NOT NULL,
    last_name  VARCHAR(50)  NOT NULL,
    country    VARCHAR(50)  NOT NULL,
    signup_date DATE        NOT NULL
);

CREATE TABLE product (
    id       INTEGER        PRIMARY KEY,
    name     VARCHAR(100)   NOT NULL,
    category VARCHAR(50)    NOT NULL,
    price    NUMERIC(10, 2) NOT NULL
);

CREATE TABLE orders (
    id          INTEGER        PRIMARY KEY,
    customer_id INTEGER        REFERENCES customer(id),
    product_id  INTEGER        REFERENCES product(id),
    quantity    INTEGER        NOT NULL,
    order_date  DATE           NOT NULL,
    total       NUMERIC(10, 2) NOT NULL
);