Advanced SQL

-- CTE: a named subquery defined before the main query
-- Syntax: WITH name AS (subquery) SELECT ... FROM name

-- Simple CTE — improves readability
WITH high_earners AS (
    SELECT id, name, salary, department
    FROM   employees
    WHERE  salary > 80000
)
SELECT department, COUNT(*) AS count, AVG(salary) AS avg
FROM   high_earners
GROUP  BY department;

-- Multiple CTEs — chain them together
WITH
    dept_stats AS (
        SELECT   department_id,
                 AVG(salary) AS avg_sal,
                 COUNT(*)    AS headcount
        FROM     employees
        GROUP BY department_id
    ),
    large_depts AS (
        SELECT * FROM dept_stats WHERE headcount > 10
    )
SELECT d.name, l.avg_sal, l.headcount
FROM   large_depts l
JOIN   departments d ON d.id = l.department_id
ORDER  BY l.avg_sal DESC;

-- Recursive CTE — traverse hierarchies (org charts, trees)
WITH RECURSIVE org_chart AS (
    -- Base case: the root (CEO has no manager)
    SELECT id, name, manager_id, 0 AS depth
    FROM   employees
    WHERE  manager_id IS NULL

    UNION ALL

    -- Recursive case: employees whose manager is already in the CTE
    SELECT e.id, e.name, e.manager_id, oc.depth + 1
    FROM   employees e
    JOIN   org_chart oc ON e.manager_id = oc.id
)
SELECT REPEAT('  ', depth) || name AS hierarchy, depth
FROM   org_chart
ORDER  BY depth, name;

-- CREATE TABLE with constraints
CREATE TABLE orders (
    id          BIGSERIAL PRIMARY KEY,         -- auto-incrementing PK
    customer_id INTEGER   NOT NULL
                REFERENCES customers(id)       -- foreign key
                ON DELETE CASCADE,             -- delete orders if customer deleted
    status      VARCHAR(20) NOT NULL
                DEFAULT 'pending'
                CHECK (status IN ('pending','confirmed','shipped','cancelled')),
    total       NUMERIC(12, 2) CHECK (total >= 0),
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Indexes — dramatically speed up queries on large tables
CREATE INDEX idx_orders_customer ON orders (customer_id);
CREATE INDEX idx_orders_status   ON orders (status);
-- Composite index for queries filtering on both
CREATE INDEX idx_orders_cust_status ON orders (customer_id, status);
-- Partial index — only index a subset of rows
CREATE INDEX idx_pending_orders ON orders (created_at)
    WHERE status = 'pending';

-- Altering a table
ALTER TABLE orders ADD COLUMN notes TEXT;
ALTER TABLE orders ALTER COLUMN total SET NOT NULL;
ALTER TABLE orders DROP COLUMN notes;
ALTER TABLE orders ADD CONSTRAINT check_total CHECK (total >= 0);

-- Views — saved queries that look like tables
CREATE VIEW active_orders AS
    SELECT o.*, c.name AS customer_name
    FROM   orders o JOIN customers c ON o.customer_id = c.id
    WHERE  o.status NOT IN ('cancelled');

-- Transaction: a sequence of operations that succeeds or fails atomically
BEGIN;

UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;

-- If anything fails, ROLLBACK undoes everything
-- If everything succeeds, COMMIT makes changes permanent
COMMIT;

-- ROLLBACK example
BEGIN;
DELETE FROM orders WHERE customer_id = 42;
-- Oh wait, we did not want to delete all of them
ROLLBACK;  -- nothing was deleted

-- Savepoints — partial rollback within a transaction
BEGIN;
INSERT INTO orders (customer_id, total) VALUES (1, 500);
SAVEPOINT order_inserted;
INSERT INTO order_items (order_id, product_id) VALUES (1, 99);  -- fails
ROLLBACK TO SAVEPOINT order_inserted;  -- undo only the items insert
INSERT INTO order_items (order_id, product_id) VALUES (1, 100); -- try again
COMMIT;

-- ACID properties:
-- Atomicity:   all or nothing — partial failures roll back completely
-- Consistency: data satisfies all constraints before and after
-- Isolation:   concurrent transactions do not interfere
-- Durability:  committed transactions survive system crashes (WAL)