-- sample-schema.sql: a small shop database, as one SQL script.
-- Free sample from 99helpers.com/file-types/sql
--
-- Written to run unchanged on PostgreSQL, MySQL and SQLite.
-- It creates four tables, fills them with fictional data, changes some
-- rows inside a transaction and runs a few queries. Run it in an empty
-- or throwaway database: it drops these tables first if they exist.
--
--   PostgreSQL:  psql -d shop -v ON_ERROR_STOP=1 -f sample-schema.sql
--   MySQL:       mysql shop < sample-schema.sql
--   SQLite:      sqlite3 shop.db < sample-schema.sql

/* 1. Comments. Two hyphens start a comment that runs to the end of the
      line (MySQL needs a space after them); slash-star comments like
      this one can span several lines. */

-- 2. Start clean. Drop the view, then the tables that point at other
--    tables, then the tables they point at. IF EXISTS skips missing ones.
DROP VIEW IF EXISTS order_totals;
DROP TABLE IF EXISTS order_items;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS products;
DROP TABLE IF EXISTS customers;

-- 3. CREATE TABLE: columns, their types and constraints. Each statement
--    ends with a semicolon, which is how the client knows where one
--    statement stops and the next begins.
CREATE TABLE customers (
    id          INTEGER      PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) NOT NULL UNIQUE,
    city        VARCHAR(100),
    active      BOOLEAN      NOT NULL DEFAULT TRUE
);

CREATE TABLE products (
    id          INTEGER       PRIMARY KEY,
    sku         VARCHAR(20)   NOT NULL UNIQUE,
    title       VARCHAR(200)  NOT NULL,
    -- 4. DECIMAL stores exact amounts. Never use FLOAT for money.
    price       DECIMAL(10,2) NOT NULL CHECK (price >= 0)
);

-- 5. Foreign keys link rows across tables. (SQLite only enforces them
--    after PRAGMA foreign_keys = ON; the others always do.)
CREATE TABLE orders (
    id          INTEGER     PRIMARY KEY,
    customer_id INTEGER     NOT NULL REFERENCES customers (id),
    ordered_on  DATE        NOT NULL,
    status      VARCHAR(20) NOT NULL DEFAULT 'new'
);

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  DECIMAL(10,2) NOT NULL,
    PRIMARY KEY (order_id, product_id)
);

-- 6. An index makes lookups by this column fast on large tables.
CREATE INDEX idx_orders_customer ON orders (customer_id);

-- 7. INSERT with several rows at once. Strings use single quotes; a quote
--    inside a string is written twice, as in 'O''Example'. Dates are
--    written as 'YYYY-MM-DD', which every database reads the same way.
INSERT INTO customers (id, name, email, city) VALUES
    (1, 'Ada Example',   'ada@example.com',   'Utrecht'),
    (2, 'Ben O''Example', 'ben@example.org',   'Leeds'),
    (3, 'Cleo Example',  'cleo@example.net',  NULL);

INSERT INTO products (id, sku, title, price) VALUES
    (1, 'MUG-01',  'Coffee mug',        8.50),
    (2, 'TEE-M',   'T-shirt, size M',  19.00),
    (3, 'NOTE-A5', 'A5 notebook',       4.25);

INSERT INTO orders (id, customer_id, ordered_on, status) VALUES
    (101, 1, '2026-10-01', 'shipped'),
    (102, 2, '2026-10-03', 'new'),
    (103, 1, '2026-10-20', 'new');

INSERT INTO order_items (order_id, product_id, quantity, unit_price) VALUES
    (101, 1, 2,  8.50),
    (101, 3, 4,  4.25),
    (102, 2, 1, 19.00),
    (103, 3, 10, 4.00);

-- 8. A transaction: the statements between BEGIN and COMMIT take effect
--    together or not at all. ROLLBACK instead of COMMIT would undo both.
BEGIN;
UPDATE orders SET status = 'shipped' WHERE id = 102;
UPDATE customers SET active = FALSE WHERE id = 3;
COMMIT;

-- 9. A view is a saved query that you can select from like a table.
CREATE VIEW order_totals AS
SELECT o.id AS order_id,
       c.name AS customer,
       o.status,
       SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
GROUP BY o.id, c.name, o.status;

-- 10. Queries. Their results are what a client prints after running the
--     script. Expected: order 101 = 34.00, 102 = 19.00, 103 = 40.00.
SELECT order_id, customer, status, total
FROM order_totals
ORDER BY order_id;

-- 11. GROUP BY with HAVING: customers who spent more than 50 in total.
SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM order_totals
GROUP BY customer
HAVING SUM(total) > 50
ORDER BY spent DESC;

-- 12. A LEFT JOIN keeps customers with no orders; COUNT(o.id) is then 0.
--     LIMIT works in all three databases here; SQL Server uses TOP and
--     standard SQL has FETCH FIRST 2 ROWS ONLY.
SELECT c.name, c.active, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name, c.active
ORDER BY orders DESC, c.name
LIMIT 2;