01 — Introduction & Setup

-- A multi-table production schema for an e-commerce system.
-- Each table has explicit constraints, proper types, and indexes.

CREATE TABLE customers (
  -- UUID primary key avoids sequential enumeration attacks and
  -- allows distributed ID generation without central coordination.
  id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email       CITEXT NOT NULL UNIQUE,           -- CITEXT = case-insensitive email lookup
  full_name   TEXT NOT NULL CHECK (full_name <> ''),
  phone       TEXT,                              -- nullable: not every customer has a phone
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now(),
  -- Exclude soft-deleted customers from the unique email constraint
  deleted_at  TIMESTAMPTZ
);

-- Partial unique index: email must be unique among non-deleted rows only.
-- This allows re-registration after soft-delete without violating uniqueness.
CREATE UNIQUE INDEX customers_email_active_uniq
  ON customers (email)
  WHERE deleted_at IS NULL;

CREATE TABLE orders (
  id           BIGSERIAL PRIMARY KEY,            -- sequential BIGINT (8 bytes); gap-free NOT guaranteed
  customer_id  UUID NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  status       TEXT NOT NULL DEFAULT 'pending'
               CHECK (status IN ('pending','paid','shipped','cancelled','refunded')),
  total        NUMERIC(12, 2) NOT NULL DEFAULT 0
               CHECK (total >= 0),               -- NUMERIC, not REAL — money needs exact arithmetic
  currency     CHAR(3) NOT NULL DEFAULT 'USD',   -- ISO 4217
  placed_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at   TIMESTAMPTZ NOT NULL DEFAULT now()
);

-- Composite index for "show me customer X's recent orders" (hot path).
-- Column order matters: equality on customer_id, then sort on placed_at DESC.
CREATE INDEX idx_orders_customer_placed
  ON orders (customer_id, placed_at DESC);

-- Partial index: only pending orders need scanning — skips 99% of rows.
CREATE INDEX idx_orders_pending
  ON orders (placed_at)
  WHERE status = 'pending';

CREATE TABLE products (
  id          BIGSERIAL PRIMARY KEY,
  sku         TEXT NOT NULL UNIQUE,              -- business identifier, stable across renames
  name        TEXT NOT NULL,
  price       NUMERIC(10, 2) NOT NULL CHECK (price > 0),
  stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE order_items (
  id           BIGSERIAL PRIMARY KEY,
  order_id     BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
  product_id   BIGINT 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),
  -- Snapshot of price at order time — price in products may change later.
  -- Without this, historical order totals would silently change.
  CHECK (quantity * unit_price <= 1000000)       -- sanity bound, prevents overflow in totals
);

-- Covering index: the join path order_items→products often only needs product_id + name.
-- A covering index lets the planner satisfy the query from the index alone (index-only scan).
CREATE INDEX idx_order_items_order_product
  ON order_items (order_id, product_id)
  INCLUDE (quantity, unit_price);

-- Auto-maintain updated_at on every row update.
CREATE OR REPLACE FUNCTION touch_updated_at()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = now();                         -- set before the row is written
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER orders_touch_updated_at
  BEFORE UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION touch_updated_at();

brew install postgresql@16          # install PostgreSQL 16
brew services start postgresql@16   # start as a background service

psql postgres                       # connect to default maintenance DB

sudo apt install postgresql postgresql-contrib   # contrib adds gen_random_uuid() etc.
sudo systemctl enable --now postgresql            # start on boot + immediately
sudo -u postgres psql                              # connect as the postgres superuser

# --name pg: container alias; -e: env vars; -p: map host 5432 → container 5432
docker run --name pg \
  -e POSTGRES_PASSWORD=secret \
  -e POSTGRES_DB=practice \
  -p 5432:5432 \
  -d postgres:16                   # -d: run detached (background)

psql -h localhost -U postgres -d practice
# password: secret

brew install sqlite                 # macOS
sudo apt install sqlite3            # Linux (Debian/Ubuntu)

sqlite3 --version                   # verify install
sqlite3 practice.db                 # create/open a DB file (created on first connect)
sqlite> .tables                     # list tables
sqlite> .quit

psql -h localhost -U postgres -d practice -c "SELECT now();"  # run one query, exit
psql -d practice -f setup.sql                                 # run a SQL file
psql -d practice < dump.sql                                   # restore from dump
psql -d practice -At -c "SELECT count(*) FROM customers"     # -A: unaligned, -t: tuples only → raw number

-- DDL: create a database. Note: CREATE DATABASE cannot run inside a
-- transaction block (BEGIN/COMMIT), so it must be its own statement.
CREATE DATABASE practice;

-- In psql, switch to the new database:
--   \c practice

-- DDL: define a table. Each column has a type and constraints.
CREATE TABLE greetings (
  id      SERIAL PRIMARY KEY,              -- SERIAL = INTEGER + sequence + default
  message TEXT NOT NULL
);

-- DML: insert a row. The SERIAL column is auto-populated by the sequence.
INSERT INTO greetings (message) VALUES ('Hello, SQL!');

-- DQL: read it back. SELECT * is fine for exploration; in production code,
-- always list explicit columns (see Chapter 02).
SELECT * FROM greetings;
--  id |   message
-- ----+------------
--   1 | Hello, SQL!

# Load the dvdrental sample database (15 tables: customer, payment, rental, film, ...)
wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip
unzip dvdrental.zip
createdb dvdrental
pg_restore -d dvdrental dvdrental.tar       # restore from the tar archive
psql -d dvdrental -c "\dt"                   # verify tables loaded

CREATE TABLE customers (
  id    SERIAL PRIMARY KEY,
  name  TEXT NOT NULL,
  city  TEXT                                  -- nullable: city may be unknown
);

CREATE TABLE orders (
  id           SERIAL PRIMARY KEY,
  customer_id  INTEGER NOT NULL REFERENCES customers(id),
  amount       NUMERIC(10, 2) NOT NULL CHECK (amount >= 0),
  ordered_on   DATE NOT NULL DEFAULT CURRENT_DATE
);

INSERT INTO customers (name, city) VALUES
  ('Alice', 'NYC'),
  ('Bob',   'LA'),
  ('Carol', NULL);                            -- Carol's city is unknown — see NULL chapters

INSERT INTO orders (customer_id, amount) VALUES
  (1, 99.50),
  (1, 12.00),
  (2, 450.00),
  (3, 7.25);

FROM / JOIN  →  WHERE  →  GROUP BY  →  HAVING  →  SELECT  →  DISTINCT  →  ORDER BY  →  LIMIT/OFFSET

-- ❌ ERROR: column "total" does not exist
-- The alias "total" is assigned in SELECT (step 5), but WHERE runs at step 2.
-- At WHERE time, the alias doesn't exist yet — the planner doesn't know what "total" is.
SELECT amount * 1.08 AS total
FROM orders
WHERE total > 100;

-- ✅ Correct: repeat the full expression in WHERE.
-- The planner may optimize this to compute it once, but you must write it out.
SELECT amount * 1.08 AS total
FROM orders
WHERE amount * 1.08 > 100;

-- ✅ Alias IS visible in ORDER BY — ORDER BY runs after SELECT, so the alias exists.
SELECT amount * 1.08 AS total
FROM orders
ORDER BY total DESC;

EXPLAIN SELECT c.name, o.id, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 50;

 Hash Join  (cost=38.05..60.15 rows=2 width=46)
   Hash Cond: (c.id = o.customer_id)
   ->  Seq Scan on customers c  (cost=0.00..22.00 rows=1200 width=42)
   ->  Hash  (cost=37.75..37.75 rows=24 width=12)
         ->  Seq Scan on orders o  (cost=0.00..37.75 rows=24 width=12)
               Filter: (amount > 50)

-- EXPLAIN ANALYZE actually executes the query and shows real timings.
-- Useful for finding where the planner's estimates diverge from reality.
EXPLAIN ANALYZE SELECT c.name, o.id, o.amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 50;

-- Approximate row count — reads from the catalog, no table scan.
-- reltuples is updated by ANALYZE/VACUUM, so it may be slightly stale.
SELECT reltuples::bigint AS approx_rows
FROM pg_class
WHERE relname = 'big_table';

SELECT
  customer_id,
  SUM(amount) AS total_spent
FROM orders
WHERE total_spent > 100
GROUP BY customer_id;