SQL 速查表

-- basic query: select specific columns
SELECT id, name, email
FROM users
WHERE age >= 18 AND status = 'active'
ORDER BY name ASC, created_at DESC
LIMIT 10 OFFSET 0;

-- select all columns (avoid in production)
SELECT * FROM products;

-- column aliases with AS
SELECT name AS product_name, price * 1.1 AS price_with_tax
FROM products;

-- unique values only
SELECT DISTINCT country FROM users;
SELECT DISTINCT city, country FROM users;  -- unique combos

-- table aliases (essential for joins)
SELECT u.name, o.total
FROM users AS u
JOIN orders AS o ON u.id = o.user_id;

-- column alias (AS is optional)
SELECT name product_name, COUNT(*) count
FROM products
GROUP BY name;

SELECT * FROM products
WHERE price BETWEEN 10 AND 100        -- inclusive range
  AND category IN ('tech', 'home')    -- match any value
  AND stock IS NOT NULL               -- exclude NULLs
  AND discount IS NULL;               -- only NULLs

-- NOT IN with NULL caveat: returns nothing if subquery has NULL!
SELECT * FROM users
WHERE id NOT IN (SELECT user_id FROM orders);  -- risky if NULLs

-- safer: NOT EXISTS
SELECT * FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- LIKE: basic pattern matching
SELECT * FROM users WHERE name LIKE 'A%';     -- starts with A
SELECT * FROM users WHERE name LIKE '%son';    -- ends with son
SELECT * FROM users WHERE name LIKE '%a%';     -- contains a
SELECT * FROM users WHERE name LIKE '_a%';     -- second char is a

-- ILIKE (PostgreSQL): case-insensitive
SELECT * FROM users WHERE name ILIKE 'a%';

-- SIMILAR TO (PostgreSQL): regex-like
SELECT * FROM users WHERE name SIMILAR TO '[AB]%';

-- full regex (PostgreSQL)
SELECT * FROM users WHERE name ~ '^A[a-z]+$';

-- conditional logic in queries
SELECT
  name,
  price,
  CASE
    WHEN price < 10 THEN 'cheap'
    WHEN price < 50 THEN 'moderate'
    WHEN price < 100 THEN 'expensive'
    ELSE 'luxury'
  END AS price_category
FROM products;

-- CASE in aggregation (pivot table)
SELECT
  category,
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
  SUM(CASE WHEN status = 'inactive' THEN 1 ELSE 0 END) AS inactive_count
FROM products
GROUP BY category;

-- only matching rows from both tables
SELECT u.name, o.total, o.created_at
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.total > 100
ORDER BY o.total DESC;

-- multiple joins
SELECT u.name, o.total, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- USING (when column names match)
SELECT * FROM users
JOIN profiles ON users.id = profiles.user_id;

-- all users, with their orders (NULL if no orders)
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
ORDER BY u.name;

-- find users with NO orders (anti-join pattern)
SELECT u.name
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- count orders per user (including zero)
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name;

-- RIGHT JOIN: all rows from right table
SELECT u.name, o.total
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;
-- returns all orders, even orphaned ones (user_id = NULL)

-- FULL OUTER JOIN: all rows from both tables
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- returns all users AND all orders, matching where possible

-- Note: RIGHT JOIN is rarely used (just swap tables and use LEFT)
-- FULL OUTER JOIN is useful for finding mismatches between tables
SELECT u.name, o.total
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id
WHERE u.id IS NULL OR o.user_id IS NULL;

-- CROSS JOIN: Cartesian product (every combination)
SELECT s.size, c.color
FROM sizes s
CROSS JOIN colors c;
-- produces all size+color combinations

-- Self join: join a table to itself
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- self join for hierarchical data
SELECT c.name AS child, p.name AS parent
FROM categories c
JOIN categories p ON c.parent_id = p.id;

-- self join to find duplicates
SELECT a.id, a.email, b.id AS dup_id
FROM users a
JOIN users b ON a.email = b.email AND a.id < b.id;

-- NATURAL JOIN: joins on all matching column names
-- (rarely recommended — implicit, fragile)
SELECT * FROM users NATURAL JOIN profiles;
-- joins on any column that exists in BOTH tables

-- Summary of join types:
-- INNER JOIN : matching rows only
-- LEFT JOIN  : all left + matching right
-- RIGHT JOIN : all right + matching left
-- FULL JOIN  : all from both sides
-- CROSS JOIN : Cartesian product
-- SELF JOIN  : table joined to itself

-- LATERAL JOIN (PostgreSQL): subquery can reference outer query
SELECT u.name, recent.*
FROM users u,
LATERAL (
  SELECT * FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 3
) recent;

-- aggregate per group
SELECT category, COUNT(*) AS cnt, AVG(price) AS avg_price
FROM products
GROUP BY category
HAVING COUNT(*) > 5
ORDER BY cnt DESC;

-- HAVING filters groups (after aggregation)
-- WHERE filters rows (before aggregation)
SELECT dept, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'       -- filter rows first
GROUP BY dept
HAVING AVG(salary) > 50000;   -- then filter groups

SELECT
  COUNT(*) AS total_rows,           -- counts all rows
  COUNT(email) AS emails_filled,    -- counts non-NULL emails
  COUNT(DISTINCT country) AS countries,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order,
  MIN(amount) AS smallest_order,
  MAX(amount) AS largest_order,
  -- string aggregation (PostgreSQL)
  STRING_AGG(name, ', ') AS all_names,
  -- boolean aggregation
  BOOL_OR(is_active) AS any_active,
  BOOL_AND(is_active) AS all_active
FROM orders;

-- multi-level grouping
SELECT
  EXTRACT(YEAR FROM created_at) AS yr,
  EXTRACT(MONTH FROM created_at) AS mon,
  category,
  COUNT(*) AS cnt,
  SUM(total) AS revenue
FROM orders
GROUP BY yr, mon, category
ORDER BY yr DESC, mon DESC, cnt DESC;

-- GROUP BY with ROLLUP (subtotals + grand total)
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category WITH ROLLUP;
-- last row has NULL category = grand total

-- GROUPING SETS (PostgreSQL): specify multiple groupings
SELECT category, status, COUNT(*)
FROM products
GROUP BY GROUPING SETS ((category, status), (category), ());

-- WHERE: filters rows BEFORE grouping
-- Cannot use aggregates
SELECT category, COUNT(*) AS cnt
FROM products
WHERE price > 10          -- OK: filter on raw column
GROUP BY category;

-- HAVING: filters groups AFTER grouping
-- Can use aggregates
SELECT category, COUNT(*) AS cnt
FROM products
GROUP BY category
HAVING COUNT(*) > 5       -- OK: filter on aggregate
   AND AVG(price) > 20;   -- OK: multiple aggregate filters

-- combining both
SELECT category, COUNT(*) AS cnt
FROM products
WHERE price > 10          -- filter rows
GROUP BY category         -- group
HAVING COUNT(*) > 5;      -- filter groups

-- group by date parts
SELECT
  DATE(created_at) AS order_date,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY DATE(created_at)
ORDER BY order_date DESC;

-- group by hour
SELECT
  EXTRACT(HOUR FROM created_at) AS hr,
  COUNT(*) AS cnt
FROM orders
WHERE created_at >= CURRENT_DATE
GROUP BY hr
ORDER BY hr;

-- monthly revenue trend
SELECT
  TO_CHAR(created_at, 'YYYY-MM') AS month,
  SUM(total) AS revenue,
  COUNT(*) AS orders,
  AVG(total) AS avg_order
FROM orders
WHERE created_at >= '2024-01-01'
GROUP BY month
ORDER BY month;

-- scalar subquery (returns single value)
SELECT name, age
FROM users
WHERE age > (SELECT AVG(age) FROM users);

-- column subquery (returns one column, multiple rows)
SELECT name
FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 100);

-- subquery in SELECT
SELECT
  u.name,
  (SELECT COUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count
FROM users u;

-- correlated: subquery references outer query
SELECT u.name
FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id
    AND o.total > 1000
);

-- NOT EXISTS: users without any orders
SELECT u.name
FROM users u
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id
);

-- correlated subquery in SELECT (runs per row)
SELECT
  u.name,
  (SELECT MAX(o.total)
   FROM orders o
   WHERE o.user_id = u.id) AS max_order
FROM users u;

-- CTE: named temporary result set
WITH active_users AS (
  SELECT id, name FROM users WHERE status = 'active'
),
user_orders AS (
  SELECT user_id, COUNT(*) AS cnt, SUM(total) AS revenue
  FROM orders
  GROUP BY user_id
)
SELECT au.name, uo.cnt, uo.revenue
FROM active_users au
LEFT JOIN user_orders uo ON au.id = uo.user_id
ORDER BY uo.revenue DESC NULLS LAST;

-- CTEs improve readability for complex queries
-- They are NOT materialized (just syntactic sugar) in most DBs
-- (PostgreSQL 12+ can materialize with MATERIALIZED keyword)

-- hierarchical data: org chart
WITH RECURSIVE org_tree AS (
  -- base case: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id IS NULL

  UNION ALL

  -- recursive case: direct reports
  SELECT e.id, e.name, e.manager_id, ot.level + 1
  FROM employees e
  JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT level, name FROM org_tree ORDER BY level, name;

-- generate a series of dates
WITH RECURSIVE dates AS (
  SELECT DATE '2024-01-01' AS d
  UNION ALL
  SELECT d + 1 FROM dates WHERE d < '2024-01-31'
)
SELECT d FROM dates;

-- factorial
WITH RECURSIVE fact(n, result) AS (
  SELECT 1, 1
  UNION ALL
  SELECT n + 1, result * (n + 1) FROM fact WHERE n < 10
)
SELECT * FROM fact;

-- ANY: greater than ANY of the values (= at least one)
SELECT * FROM products
WHERE price > ANY (
  SELECT price FROM products WHERE category = 'tech'
);
-- true if price exceeds at least one tech product's price

-- ALL: greater than ALL values (= every one)
SELECT * FROM products
WHERE price > ALL (
  SELECT price FROM products WHERE category = 'tech'
);
-- true if price exceeds every tech product's price

-- = ANY is equivalent to IN
SELECT * FROM users
WHERE id = ANY (SELECT user_id FROM orders);

-- <> ALL is equivalent to NOT IN (but NULL-safe)
SELECT * FROM users
WHERE id <> ALL (SELECT user_id FROM orders WHERE total < 0);

SELECT
  name,
  salary,
  dept,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK() OVER (ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

-- top 3 earners per department
SELECT * FROM (
  SELECT
    name,
    dept,
    salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn
  FROM employees
) ranked
WHERE rn <= 3;

-- compare each row to previous/next
SELECT
  date,
  revenue,
  LAG(revenue) OVER (ORDER BY date) AS prev_day,
  LEAD(revenue) OVER (ORDER BY date) AS next_day,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_change,
  ROUND(
    (revenue - LAG(revenue) OVER (ORDER BY date)) * 100.0
    / NULLIF(LAG(revenue) OVER (ORDER BY date), 0),
    2
  ) AS pct_change
FROM daily_sales
ORDER BY date;

-- LAG with offset and default
SELECT
  date,
  revenue,
  LAG(revenue, 7) OVER (ORDER BY date) AS revenue_7_days_ago
FROM daily_sales;

-- cumulative sum (running total)
SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS running_total,
  SUM(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS last_7_days_sum,
  AVG(revenue) OVER (ORDER BY date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS moving_avg_7d
FROM daily_sales
ORDER BY date;

-- window frame options:
-- ROWS BETWEEN n PRECEDING AND n FOLLOWING  -- physical rows
-- RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW -- logical range
-- ROWS UNBOUNDED PRECEDING = from start to current
-- ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING = all rows

-- divide into quartiles
SELECT
  name,
  salary,
  NTILE(4) OVER (ORDER BY salary DESC) AS quartile,
  PERCENT_RANK() OVER (ORDER BY salary) AS pct_rank,
  CUME_DIST() OVER (ORDER BY salary) AS cumulative_dist
FROM employees;

-- first/last value in a partition
SELECT
  dept,
  name,
  salary,
  FIRST_VALUE(name) OVER (PARTITION BY dept ORDER BY salary DESC) AS top_earner,
  LAST_VALUE(name) OVER (
    PARTITION BY dept ORDER BY salary DESC
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) AS lowest_earner
FROM employees;

-- aggregates over windows (no row collapse!)
SELECT
  name,
  dept,
  salary,
  -- compare to department average
  AVG(salary) OVER (PARTITION BY dept) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY dept) AS diff_from_avg,
  -- percentage of department total
  salary * 100.0 / SUM(salary) OVER (PARTITION BY dept) AS pct_of_dept,
  -- count per department
  COUNT(*) OVER (PARTITION BY dept) AS dept_size
FROM employees
ORDER BY dept, salary DESC;

-- key advantage: aggregates without GROUP BY
-- every row is preserved, with the aggregate value attached

CREATE TABLE users (
  id          SERIAL PRIMARY KEY,          -- auto-increment (PostgreSQL)
  -- MySQL: id INT AUTO_INCREMENT PRIMARY KEY
  username    VARCHAR(50) UNIQUE NOT NULL,
  email       VARCHAR(255) UNIQUE NOT NULL,
  password    VARCHAR(255) NOT NULL,
  age         INT CHECK (age >= 0 AND age <= 150),
  salary      DECIMAL(10, 2) DEFAULT 0.00, -- precision, scale
  bio         TEXT,                        -- unlimited length
  avatar      BYTEA,                       -- binary (PostgreSQL)
  metadata    JSONB,                       -- JSON (PostgreSQL)
  status      VARCHAR(20) DEFAULT 'active',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- common types:
-- INT, BIGINT, SMALLINT, DECIMAL(p,s), NUMERIC
-- VARCHAR(n), CHAR(n), TEXT
-- DATE, TIME, TIMESTAMP, INTERVAL
-- BOOLEAN, UUID, JSON/JSONB, BYTEA/BLOB

CREATE TABLE orders (
  id          SERIAL PRIMARY KEY,
  user_id     INT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  total       DECIMAL(10,2) NOT NULL CHECK (total >= 0),
  status      VARCHAR(20) NOT NULL DEFAULT 'pending',
  created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- table-level constraints
  CONSTRAINT valid_status CHECK (status IN ('pending','paid','shipped','cancelled')),
  CONSTRAINT unique_user_order UNIQUE (user_id, created_at)
);

-- foreign key actions:
-- ON DELETE CASCADE  : delete child rows when parent deleted
-- ON DELETE SET NULL : set FK to NULL (column must be nullable)
-- ON DELETE RESTRICT : prevent parent deletion (default)
-- ON UPDATE CASCADE  : update FK when parent PK changes

-- add constraint to existing table
ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id);

-- add column
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users ADD COLUMN verified BOOLEAN DEFAULT false;

-- drop column
ALTER TABLE users DROP COLUMN avatar;

-- rename column/table
ALTER TABLE users RENAME COLUMN username TO login;
ALTER TABLE users RENAME TO accounts;

-- change column type
ALTER TABLE users ALTER COLUMN age TYPE BIGINT;
-- MySQL: ALTER TABLE users MODIFY COLUMN age BIGINT;

-- add/drop constraints
ALTER TABLE users ADD CONSTRAINT email_unique UNIQUE (email);
ALTER TABLE users DROP CONSTRAINT email_unique;

-- set default
ALTER TABLE users ALTER COLUMN status SET DEFAULT 'active';

-- DROP: permanently remove table (structure + data)
DROP TABLE IF EXISTS old_logs CASCADE;
-- CASCADE drops dependent objects (views, FKs)

-- TRUNCATE: remove all data, keep structure (faster than DELETE)
TRUNCATE TABLE logs;
TRUNCATE TABLE logs RESTART IDENTITY;  -- reset SERIAL counter
TRUNCATE TABLE orders, order_items CASCADE;  -- multiple tables

-- CREATE INDEX for query performance
CREATE INDEX idx_users_email ON users(email);
CREATE UNIQUE INDEX idx_users_username ON users(username);
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at);
CREATE INDEX idx_products_name ON products USING gin(to_tsvector('english', name));

-- partial index (PostgreSQL)
CREATE INDEX idx_active_users ON users(last_login)
WHERE status = 'active';

-- DROP INDEX
DROP INDEX IF EXISTS idx_users_email;

-- VIEW: stored query (virtual table, runs on access)
CREATE VIEW active_users AS
SELECT id, name, email FROM users WHERE status = 'active';

SELECT * FROM active_users WHERE name LIKE 'A%';

-- updatable view (simple views can be INSERTed/UPDATEd)
CREATE VIEW user_summary AS
SELECT id, name, email, age FROM users;

-- MATERIALIZED VIEW: stored result (must refresh)
CREATE MATERIALIZED VIEW monthly_stats AS
SELECT
  DATE_TRUNC('month', created_at) AS month,
  COUNT(*) AS orders,
  SUM(total) AS revenue
FROM orders
GROUP BY month;

REFRESH MATERIALIZED VIEW monthly_stats;
REFRESH MATERIALIZED VIEW CONCURRENTLY monthly_stats; -- no lock

-- drop
DROP VIEW IF EXISTS active_users;
DROP MATERIALIZED VIEW IF EXISTS monthly_stats;

-- single row
INSERT INTO users (name, email, age)
VALUES ('Alice', '[email protected]', 30);

-- multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', '[email protected]'),
  ('Carol', '[email protected]'),
  ('Dave', '[email protected]');

-- INSERT ... SELECT (copy data between tables)
INSERT INTO archive_users (name, email, deleted_at)
SELECT name, email, NOW()
FROM users
WHERE status = 'deleted';

-- INSERT with RETURNING (PostgreSQL)
INSERT INTO users (name, email)
VALUES ('Eve', '[email protected]')
RETURNING id, created_at;  -- returns the generated id

-- DEFAULT values
INSERT INTO users DEFAULT VALUES;

-- basic update
UPDATE users
SET age = 31, status = 'verified', updated_at = NOW()
WHERE id = 1;

-- update based on another table
UPDATE products p
SET price = p.price * 1.1
FROM categories c
WHERE p.category_id = c.id AND c.name = 'electronics';

-- update with subquery
UPDATE users
SET status = 'premium'
WHERE id IN (
  SELECT user_id FROM orders
  GROUP BY user_id HAVING SUM(total) > 1000
);

-- UPDATE with RETURNING (PostgreSQL)
UPDATE users SET status = 'inactive'
WHERE last_login < '2023-01-01'
RETURNING id, name;

-- WARNING: UPDATE without WHERE affects ALL rows!

-- delete specific rows
DELETE FROM users WHERE status = 'inactive';

-- delete with subquery
DELETE FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE status = 'deleted'
);

-- delete with RETURNING (PostgreSQL)
DELETE FROM users
WHERE last_login < '2020-01-01'
RETURNING id, name;

-- delete all rows (slow, logged, can be rolled back)
DELETE FROM logs;

-- TRUNCATE (fast, minimal logging, resets identity)
TRUNCATE TABLE logs;
TRUNCATE TABLE logs RESTART IDENTITY CASCADE;

-- soft delete pattern (preferred for audit)
UPDATE users SET deleted_at = NOW() WHERE id = 1;
SELECT * FROM users WHERE deleted_at IS NULL; -- active users

-- PostgreSQL: ON CONFLICT (upsert)
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]')
ON CONFLICT (id)
DO UPDATE SET email = EXCLUDED.email, updated_at = NOW()
RETURNING *;

-- DO NOTHING on conflict
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]')
ON CONFLICT (id) DO NOTHING;

-- MySQL: ON DUPLICATE KEY UPDATE
INSERT INTO users (id, name, email)
VALUES (1, 'Alice', '[email protected]')
ON DUPLICATE KEY UPDATE email = VALUES(email);

-- SQLite: ON CONFLICT
INSERT INTO users (id, name)
VALUES (1, 'Alice')
ON CONFLICT(id) DO UPDATE SET name = excluded.name;

-- MERGE: conditional insert/update/delete in one statement
-- (SQL Server, Oracle, PostgreSQL 15+)
MERGE INTO products AS target
USING (VALUES
  (1, 'Widget', 9.99),
  (2, 'Gadget', 19.99),
  (3, 'Gizmo', 29.99)
) AS source (id, name, price)
ON target.id = source.id
WHEN MATCHED THEN
  UPDATE SET name = source.name, price = source.price
WHEN NOT MATCHED THEN
  INSERT (id, name, price) VALUES (source.id, source.name, source.price)
WHEN MATCHED AND source.price < 0 THEN
  DELETE;

-- useful for:
-- - syncing data from external sources
-- - bulk upsert with conditional logic
-- - ETL operations

-- basic transaction
BEGIN;
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- both updates succeed or both fail (atomicity)

-- rollback on error
BEGIN;
  INSERT INTO orders (user_id, total) VALUES (1, 50.00);
  -- oops, something went wrong
  ROLLBACK;
-- the insert is undone

-- transaction with savepoints
BEGIN;
  INSERT INTO logs (msg) VALUES ('step 1');
  SAVEPOINT my_savepoint;
  INSERT INTO logs (msg) VALUES ('step 2');
  ROLLBACK TO my_savepoint;  -- undo step 2, keep step 1
  INSERT INTO logs (msg) VALUES ('step 3');
COMMIT;  -- commits step 1 and step 3

-- set isolation level for transaction
BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED;
  -- can see committed data from other transactions
  SELECT balance FROM accounts WHERE id = 1;
COMMIT;

-- isolation levels (from weakest to strongest):
-- READ UNCOMMITTED: can read uncommitted (dirty) data
-- READ COMMITTED: only committed data (PostgreSQL default)
-- REPEATABLE READ: same query returns same results within txn
-- SERIALIZABLE: transactions appear to run sequentially

-- PostgreSQL: set per transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

-- MySQL: set per session
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- check current level
SHOW TRANSACTION ISOLATION LEVEL;

-- pessimistic locking: lock rows for update
BEGIN;
  SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
  -- row is locked; other transactions must wait
  UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;  -- lock released

-- NOWAIT: don't wait if locked, error immediately
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;

-- SKIP LOCKED: skip locked rows (useful for job queues)
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
FOR UPDATE SKIP LOCKED
LIMIT 10;

-- SHARE LOCK: allow reads but prevent updates
SELECT * FROM products WHERE id = 1 FOR SHARE;

-- deadlock example:
-- Transaction A:
BEGIN;
  UPDATE accounts SET balance = balance - 50 WHERE id = 1; -- locks row 1
  UPDATE accounts SET balance = balance + 50 WHERE id = 2; -- waits for row 2

-- Transaction B (concurrent):
BEGIN;
  UPDATE accounts SET balance = balance - 30 WHERE id = 2; -- locks row 2
  UPDATE accounts SET balance = balance + 30 WHERE id = 1; -- waits for row 1
-- DEADLOCK! Database detects and kills one transaction

-- prevention: always lock in consistent order
-- Transaction A and B both lock id=1 first, then id=2

-- PostgreSQL: error codes for handling
-- 40P01: deadlock_detected
-- 40001: serialization_failure
-- 40P02: transaction_integrity_constraint_violation

-- retry pattern (pseudocode):
-- for attempt in range(3):
--     try:
--         BEGIN; ... COMMIT; break
--     except deadlock:
--         ROLLBACK; continue

-- ACID guarantees for transactions:

-- A: Atomicity (all or nothing)
BEGIN;
  INSERT INTO orders (id, total) VALUES (1, 100);
  INSERT INTO order_items (order_id, product_id) VALUES (1, 5);
  -- if either fails, both are rolled back
COMMIT;

-- C: Consistency (valid state to valid state)
-- constraints are checked at commit
ALTER TABLE accounts ADD CONSTRAINT balance_non_negative
  CHECK (balance >= 0);
-- a transaction that would make balance negative fails

-- I: Isolation (concurrent transactions don't interfere)
-- controlled by isolation level (see previous section)

-- D: Durability (committed data survives crashes)
-- achieved via WAL (Write-Ahead Logging) + fsync
-- synchronous_commit = on (default) ensures durability

-- B-tree index (default): equality and range queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at);

-- composite index (order matters!)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);
-- helps: WHERE user_id = 1
-- helps: WHERE user_id = 1 AND status = 'paid'
-- does NOT help: WHERE status = 'paid' (leftmost prefix rule)

-- partial index: smaller, faster for common filters
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'active';

-- expression index
CREATE INDEX idx_lower_email ON users(LOWER(email));
SELECT * FROM users WHERE LOWER(email) = '[email protected]';

-- unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- EXPLAIN: see if index is used
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- PostgreSQL function
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id INT)
RETURNS TABLE(order_id INT, total DECIMAL) AS $$
BEGIN
  RETURN QUERY
  SELECT id, total FROM orders WHERE user_id = p_user_id;
END;
$$ LANGUAGE plpgsql;

-- call function
SELECT * FROM get_user_orders(1);

-- PostgreSQL procedure (can manage transactions, PostgreSQL 11+)
CREATE PROCEDURE transfer_money(
  from_id INT, to_id INT, amount DECIMAL
) LANGUAGE plpgsql AS $$
BEGIN
  UPDATE accounts SET balance = balance - amount WHERE id = from_id;
  UPDATE accounts SET balance = balance + amount WHERE id = to_id;
  COMMIT;
END;
$$;

CALL transfer_money(1, 2, 100.00);

-- MySQL stored procedure
DELIMITER //
CREATE PROCEDURE GetActiveUsers()
BEGIN
  SELECT * FROM users WHERE status = 'active';
END //
DELIMITER ;
CALL GetActiveUsers();

-- PostgreSQL trigger: audit log on update
CREATE OR REPLACE FUNCTION audit_user_change()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit (user_id, old_name, new_name, changed_at)
  VALUES (OLD.id, OLD.name, NEW.name, NOW());
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_user_audit
AFTER UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_change();

-- trigger timing: BEFORE / AFTER / INSTEAD OF
-- trigger events: INSERT / UPDATE / DELETE / TRUNCATE
-- granularity: FOR EACH ROW / FOR EACH STATEMENT

-- MySQL trigger
CREATE TRIGGER before_user_insert
BEFORE INSERT ON users
FOR EACH ROW
SET NEW.created_at = NOW();

-- drop trigger
DROP TRIGGER IF EXISTS trg_user_audit ON users;

-- JSONB columns (PostgreSQL)
CREATE TABLE events (
  id SERIAL PRIMARY KEY,
  data JSONB NOT NULL
);

INSERT INTO events (data) VALUES
  ('{"type": "click", "user": {"id": 1, "name": "Alice"}, "tags": ["web", "mobile"]}');

-- extract fields (-> for JSON, ->> for text)
SELECT data->'type' AS type,           -- "click" (JSON)
       data->'user'->>'name' AS name,  -- Alice (text)
       data->'tags'->0 AS first_tag    -- "web"
FROM events;

-- filter by JSON field
SELECT * FROM events WHERE data->>'type' = 'click';
SELECT * FROM events WHERE data @> '{"type": "click"}';  -- containment

-- GIN index for JSON queries
CREATE INDEX idx_events_data ON events USING gin(data);
SELECT * FROM events WHERE data @> '{"user": {"id": 1}}';

-- modify JSON
UPDATE events SET data = jsonb_set(data, '{user,name}', '"Bob"');

-- PostgreSQL full-text search
CREATE TABLE articles (
  id SERIAL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT
);

-- create a full-text search index
CREATE INDEX idx_articles_search ON articles
USING gin(to_tsvector('english', title || ' ' || body));

-- search with ranking
SELECT
  title,
  ts_rank(to_tsvector('english', body), query) AS rank,
  ts_headline('english', body, query) AS snippet
FROM articles, to_tsquery('english', 'database & performance') query
WHERE to_tsvector('english', title || ' ' || body) @@ query
ORDER BY rank DESC
LIMIT 10;

-- simplified with generated column
ALTER TABLE articles ADD COLUMN search_vector tsvector
  GENERATED ALWAYS AS (to_tsvector('english', title || ' ' || body)) STORED;
CREATE INDEX idx_articles_search_vec ON articles USING gin(search_vector);

SELECT * FROM articles WHERE search_vector @@ to_tsquery('database');

-- EXPLAIN: show query plan without running
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';

-- EXPLAIN ANALYZE: run the query and show actual timing
EXPLAIN ANALYZE SELECT * FROM users WHERE email = '[email protected]';

-- EXPLAIN ANALYZE with buffers (I/O stats)
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users
JOIN orders ON users.id = orders.user_id;

-- key things to look for in the plan:
-- Seq Scan: full table scan (bad for large tables — add index)
-- Index Scan: using an index (good)
-- Bitmap Index Scan: index + heap lookup (good for many rows)
-- Hash Join: builds hash table (good for large joins)
-- Nested Loop: good for small result sets
-- Sort: explicit sort (consider index to avoid)
-- cost: estimated cost (first row, all rows)
-- rows: estimated vs actual rows (big mismatch = stale stats)

-- 1. Sargability: avoid functions on indexed columns
-- BAD: function prevents index usage
SELECT * FROM orders WHERE DATE(created_at) = '2024-01-01';
-- GOOD: range query uses index
SELECT * FROM orders
WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02';

-- 2. Avoid SELECT * (more I/O, prevents covering indexes)
-- BAD
SELECT * FROM users WHERE status = 'active';
-- GOOD
SELECT id, name, email FROM users WHERE status = 'active';

-- 3. Use LIMIT with ORDER BY for pagination
-- BAD: loads all rows
SELECT * FROM products ORDER BY id;
-- GOOD: keyset pagination
SELECT * FROM products WHERE id > 1000 ORDER BY id LIMIT 20;

-- 4. Batch large updates
-- BAD: one giant transaction
DELETE FROM logs WHERE date < '2023-01-01';
-- GOOD: batch in chunks
DELETE FROM logs WHERE date < '2023-01-01' AND id <= 10000;
DELETE FROM logs WHERE date < '2023-01-01' AND id <= 20000;

-- UNION: combine results, remove duplicates
SELECT name FROM customers
UNION
SELECT name FROM suppliers;
-- UNION ALL: faster, keeps duplicates
SELECT name FROM customers
UNION ALL
SELECT name FROM suppliers;

-- INTERSECT: rows in BOTH results
SELECT product_id FROM sales_2023
INTERSECT
SELECT product_id FROM sales_2024;

-- EXCEPT (MINUS in Oracle): rows in first but not second
SELECT product_id FROM all_products
EXCEPT
SELECT product_id FROM discontinued_products;

-- rules:
-- - same number of columns
-- - compatible types
-- - column names come from first query
-- - UNION is often faster than OR conditions

-- PostgreSQL: VACUUM to reclaim space
VACUUM ANALYZE users;  -- update stats, reclaim dead rows
VACUUM FULL users;     -- rewrites table (locks, but reclaims all space)

-- PostgreSQL: ANALYZE to update statistics
ANALYZE users;  -- helps query planner make better decisions

-- MySQL: OPTIMIZE TABLE
OPTIMIZE TABLE users;

-- Common indexing rules across databases:
-- 1. Index foreign keys (not automatic in all DBs)
-- 2. Index columns used in WHERE, JOIN, ORDER BY, GROUP BY
-- 3. Composite indexes: high selectivity column first
-- 4. Don't over-index (slows writes, uses disk)
-- 5. Drop unused indexes (check with pg_stat_user_indexes)

-- Connection pooling (application level):
-- - Use PgBouncer (PostgreSQL) or ProxySQL (MySQL)
-- - Reuse connections instead of reconnecting per request
-- - Set appropriate pool size (not too high!)

-- NULL is not zero or empty string — it's "unknown"
SELECT NULL = NULL;   -- NULL (not true!)
SELECT NULL IS NULL;  -- true
SELECT NULL <> 1;     -- NULL (unknown)

-- COALESCE: first non-NULL value
SELECT COALESCE(nickname, first_name, 'Anonymous') FROM users;

-- NULLIF: return NULL if two values are equal
SELECT NULLIF(score, 0) FROM tests;  -- NULL instead of 0
-- useful for avoiding division by zero:
SELECT total / NULLIF(count, 0) FROM stats;

-- aggregate functions ignore NULL
SELECT AVG(score) FROM tests;  -- avg of non-NULL scores only
SELECT COUNT(score) FROM tests;  -- count of non-NULL
SELECT COUNT(*) FROM tests;  -- count of all rows

-- use LEFT JOIN + COUNT carefully
SELECT u.name, COUNT(o.id) AS orders  -- COUNT(o.id) = 0 for no orders
FROM users u LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id;

-- Recursive CTE: a CTE that references itself
-- Three parts: anchor, UNION ALL, recursive member
WITH RECURSIVE countdown(n) AS (
    -- Anchor: starting point
    SELECT 1 AS n
    UNION ALL
    -- Recursive member: references the CTE itself
    SELECT n + 1 FROM countdown WHERE n < 10
)
SELECT n FROM countdown;
-- Result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

-- PostgreSQL uses WITH RECURSIVE
-- SQL Server/MySQL 8+: WITH (RECURSIVE optional in MySQL)
-- SQLite: WITH RECURSIVE

-- Employee hierarchy with manager relationships
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    manager_id INT REFERENCES employees(id)
);

-- Find all direct and indirect reports of employee 1 (CEO)
WITH RECURSIVE org_chain AS (
    -- Anchor: the starting employee
    SELECT id, name, manager_id, 0 AS depth, CAST(name AS VARCHAR(500)) AS path
    FROM employees WHERE id = 1
    UNION ALL
    -- Recursive: find employees whose manager is in the chain
    SELECT e.id, e.name, e.manager_id, oc.depth + 1,
           CAST(oc.path || ' > ' || e.name AS VARCHAR(500))
    FROM employees e
    JOIN org_chain oc ON e.manager_id = oc.id
)
SELECT id, name, depth, path FROM org_chain
ORDER BY depth, name;

-- depth shows hierarchy level, path shows the management chain

-- Generate a series of numbers
WITH RECURSIVE numbers(n) AS (
    SELECT 1
    UNION ALL
    SELECT n + 1 FROM numbers WHERE n < 100
)
SELECT n FROM numbers;

-- Generate a date range (all days in January 2024)
WITH RECURSIVE dates(d) AS (
    SELECT DATE '2024-01-01'
    UNION ALL
    SELECT d + INTERVAL '1 day' FROM dates
    WHERE d < DATE '2024-01-31'
)
SELECT d FROM dates;

-- Generate a time series for gap-filling in reports
-- (ensures every date appears even if no data exists)
SELECT d.date, COALESCE(SUM(o.amount), 0) AS daily_total
FROM dates d
LEFT JOIN orders o ON o.order_date = d.date
GROUP BY d.date ORDER BY d.date;

-- Find all paths in a directed graph
CREATE TABLE edges (src VARCHAR(10), dst VARCHAR(10));

-- Find all reachable nodes from 'A' with the path taken
WITH RECURSIVE paths AS (
    -- Anchor: start from node A
    SELECT src, dst, CAST(src || '->' || dst AS VARCHAR(1000)) AS path,
           1 AS hops
    FROM edges WHERE src = 'A'
    UNION ALL
    -- Recursive: extend the path
    SELECT p.src, e.dst,
           CAST(p.path || '->' || e.dst AS VARCHAR(1000)),
           p.hops + 1
    FROM paths p
    JOIN edges e ON p.dst = e.src
    WHERE p.hops < 10  -- prevent infinite loops in cyclic graphs
      AND p.path NOT LIKE '%' || e.dst || '%'
)
SELECT DISTINCT path, hops FROM paths ORDER BY hops;

-- Cycle prevention: check the path doesn't already contain the node

-- Compute factorial using recursive CTE
WITH RECURSIVE factorial(n, fact) AS (
    -- Anchor: 0! = 1
    SELECT 0, 1
    UNION ALL
    -- Recursive: n! = n * (n-1)!
    SELECT n + 1, fact * (n + 1) FROM factorial WHERE n < 10
)
SELECT n, fact FROM factorial;

-- Running accumulation: cumulative sum
WITH RECURSIVE running_total AS (
    SELECT id, amount, amount AS cumulative
    FROM transactions WHERE id = 1
    UNION ALL
    SELECT t.id, t.amount, rt.cumulative + t.amount
    FROM transactions t
    JOIN running_total rt ON t.id = rt.id + 1
)
SELECT * FROM running_total ORDER BY id;

-- Note: window functions (SUM OVER) are usually better for this

-- Convert rows to columns (cross-tabulation)
-- Source: sales data with rows per quarter
CREATE TABLE sales (quarter VARCHAR(10), region VARCHAR(50), amount DECIMAL(10,2));

-- SQL Server PIVOT syntax:
SELECT region, [Q1], [Q2], [Q3], [Q4]
FROM (
    SELECT quarter, region, amount FROM sales
) AS src
PIVOT (
    SUM(amount) FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

-- Result:
-- region  | Q1    | Q2    | Q3    | Q4
-- North   | 1000  | 1500  | 1200  | 1800
-- South   | 800   | 1100  | 900   | 1300

-- Works in ALL databases (MySQL, PostgreSQL, SQLite, etc.)
SELECT
    region,
    SUM(CASE WHEN quarter = 'Q1' THEN amount ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN amount ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN amount ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN amount ELSE 0 END) AS Q4,
    SUM(amount) AS total
FROM sales
GROUP BY region
ORDER BY region;

-- PostgreSQL-specific: crosstab() from tablefunc extension
-- SELECT * FROM crosstab('SELECT region, quarter, amount FROM sales ORDER BY 1,2')
-- AS ct(region VARCHAR, Q1 DECIMAL, Q2 DECIMAL, Q3 DECIMAL, Q4 DECIMAL);

-- When pivot columns are unknown at write time, use dynamic SQL
-- SQL Server example:
DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX);

-- Build the column list dynamically
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(quarter)
    FROM sales FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Build and execute the pivot query
SET @query = 'SELECT region, ' + @cols + '
FROM (SELECT quarter, region, amount FROM sales) x
PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p';

EXEC sp_executesql @query;

-- WARNING: dynamic SQL is vulnerable to SQL injection
-- — always use QUOTENAME() to sanitize column names

-- Convert columns back to rows
-- Source: wide table with Q1-Q4 columns
-- Target: narrow table with quarter/amount rows

-- SQL Server UNPIVOT:
SELECT region, quarter, amount
FROM quarterly_sales
UNPIVOT (
    amount FOR quarter IN (Q1, Q2, Q3, Q4)
) AS unpvt;

-- Universal UNION ALL approach (all databases):
SELECT region, 'Q1' AS quarter, Q1 AS amount FROM quarterly_sales
UNION ALL
SELECT region, 'Q2' AS quarter, Q2 AS amount FROM quarterly_sales
UNION ALL
SELECT region, 'Q3' AS quarter, Q3 AS amount FROM quarterly_sales
UNION ALL
SELECT region, 'Q4' AS quarter, Q4 AS amount FROM quarterly_sales;

-- Result: one row per region/quarter combination

-- Monthly sales pivot with year-over-year comparison
SELECT
    product_name,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 1  THEN amount ELSE 0 END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 2  THEN amount ELSE 0 END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 3  THEN amount ELSE 0 END) AS mar,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 4  THEN amount ELSE 0 END) AS apr,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 5  THEN amount ELSE 0 END) AS may,
    SUM(CASE WHEN EXTRACT(MONTH FROM order_date) = 6  THEN amount ELSE 0 END) AS jun,
    SUM(CASE WHEN EXTRACT(YEAR  FROM order_date) = 2024 THEN amount ELSE 0 END) AS total_2024,
    SUM(CASE WHEN EXTRACT(YEAR  FROM order_date) = 2023 THEN amount ELSE 0 END) AS total_2023,
    SUM(CASE WHEN EXTRACT(YEAR  FROM order_date) = 2024 THEN amount ELSE 0 END) -
    SUM(CASE WHEN EXTRACT(YEAR  FROM order_date) = 2023 THEN amount ELSE 0 END) AS yoy_change
FROM orders o
JOIN products p ON o.product_id = p.id
WHERE order_date BETWEEN '2023-01-01' AND '2024-12-31'
GROUP BY product_name
HAVING SUM(amount) > 0
ORDER BY total_2024 DESC;

-- LIKE: simple pattern matching (slow on large tables)
SELECT * FROM articles WHERE title LIKE '%database%';
SELECT * FROM articles WHERE title LIKE 'data%';  -- prefix match (can use index)

-- Full-Text Search: indexed, fast, supports ranking
-- MySQL:
CREATE FULLTEXT INDEX ft_title_body ON articles(title, body);
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('database performance' IN NATURAL LANGUAGE MODE);

-- With relevance score:
SELECT id, title,
       MATCH(title, body) AGAINST('database performance') AS relevance
FROM articles
WHERE MATCH(title, body) AGAINST('database performance')
ORDER BY relevance DESC;

-- LIKE '%word%' scans every row (no index usage)
-- Full-text search uses an inverted index — much faster

-- PostgreSQL uses tsvector (document) and tsquery (search)
-- Create a search-optimized column
ALTER TABLE articles ADD COLUMN search_vector tsvector;
CREATE INDEX idx_search ON articles USING GIN(search_vector);

-- Populate the search vector (tokenize + normalize)
UPDATE articles SET search_vector =
    to_tsvector('english', coalesce(title,'') || ' ' || coalesce(body,''));

-- Search with tsquery
SELECT id, title,
       ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('english', 'database & performance') query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- ts_rank_cd gives coverage density ranking
-- plainto_tsquery: plain text → tsquery (user-friendly)
-- phraseto_tsquery: exact phrase matching

-- MySQL boolean full-text search
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+database +performance' IN BOOLEAN MODE);
-- + = must contain, - = must NOT contain, * = wildcard
-- "" = exact phrase, () = grouping, ~ = fuzzy (negation)

-- Examples:
-- '+MySQL -Oracle'      → must have MySQL, must NOT have Oracle
-- '"full text"'         → exact phrase "full text"
-- 'data*'               → words starting with "data" (database, dataset)
-- '+database >performance' → database required, performance boosts rank

-- PostgreSQL boolean operators in tsquery:
SELECT * FROM articles
WHERE search_vector @@ to_tsquery('english', 'database & !oracle');
-- & = AND, | = OR, ! = NOT, <-> = follows (phrase)

-- PostgreSQL: highlight matching terms
SELECT id, title,
       ts_headline('english', body, query, 'MaxWords=35, MinWords=15') AS snippet
FROM articles, to_tsquery('english', 'database') query
WHERE search_vector @@ query;

-- ts_headline returns a text snippet with matches highlighted
-- (wrapped in <b> tags by default, customizable)

-- MySQL: no built-in highlighting, but you can use SUBSTRING:
SELECT id,
       SUBSTRING(body, GREATEST(1, LOCATE('database', body) - 50), 100) AS snippet
FROM articles
WHERE MATCH(body) AGAINST('database' IN NATURAL LANGUAGE MODE);

-- SQL Server CONTAINSTABLE with ranking:
-- SELECT a.id, a.title, KEY_TBL.RANK
-- FROM articles a
-- INNER JOIN CONTAINSTABLE(articles, body, 'database') AS KEY_TBL
-- ON a.id = KEY_TBL.[KEY]
-- ORDER BY KEY_TBL.RANK DESC;

-- PostgreSQL pg_trgm extension: fuzzy/typo-tolerant search
CREATE EXTENSION pg_trgm;

-- similarity() returns 0-1 based on trigram overlap
SELECT name, similarity(name, 'database') AS sim
FROM products
WHERE name % 'database'  -- % operator: trigram similarity threshold
ORDER BY sim DESC;

-- Set similarity threshold (0-1)
SET pg_trgm.similarity_threshold = 0.3;

-- word_similarity: partial word matching
SELECT name, word_similarity('database', name) AS sim
FROM products WHERE name <% 'database';

-- Create a GIN index for fast fuzzy search
CREATE INDEX idx_products_trgm ON products USING GIN(name gin_trgm_ops);

-- Great for: autocomplete, typo correction, "did you mean?" features

-- A trigger fires automatically on INSERT/UPDATE/DELETE
-- MySQL syntax:
DELIMITER //
CREATE TRIGGER audit_log
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_table (table_name, action, row_id, changed_at)
    VALUES ('employees', 'INSERT', NEW.id, NOW());
END //
DELIMITER ;

-- BEFORE triggers can modify the NEW values:
CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    IF NEW.email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email';
    END IF;
END //

-- PostgreSQL uses CREATE FUNCTION + CREATE TRIGGER:
-- CREATE TRIGGER audit AFTER INSERT ON employees
-- FOR EACH ROW EXECUTE FUNCTION audit_func();

-- Track all changes to a critical table
CREATE TABLE employee_audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    action VARCHAR(10),       -- INSERT, UPDATE, DELETE
    employee_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    old_salary DECIMAL(10,2),
    new_salary DECIMAL(10,2),
    changed_by VARCHAR(50) DEFAULT CURRENT_USER,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_audit_insert
AFTER INSERT ON employees
FOR EACH ROW
INSERT INTO employee_audit (action, employee_id, new_name, new_salary)
VALUES ('INSERT', NEW.id, NEW.name, NEW.salary);

CREATE TRIGGER trg_audit_update
AFTER UPDATE ON employees
FOR EACH ROW
INSERT INTO employee_audit (action, employee_id, old_name, new_name, old_salary, new_salary)
VALUES ('UPDATE', NEW.id, OLD.name, NEW.name, OLD.salary, NEW.salary);

CREATE TRIGGER trg_audit_delete
AFTER DELETE ON employees
FOR EACH ROW
INSERT INTO employee_audit (action, employee_id, old_name, old_salary)
VALUES ('DELETE', OLD.id, OLD.name, OLD.salary);

-- Auto-update a derived column when source data changes
CREATE TABLE products (
    id INT PRIMARY KEY,
    price DECIMAL(10,2),
    discount_percent DECIMAL(5,2),
    final_price DECIMAL(10,2)  -- computed: price * (1 - discount/100)
);

-- BEFORE INSERT: compute final_price
CREATE TRIGGER calc_final_price_insert
BEFORE INSERT ON products
FOR EACH ROW
SET NEW.final_price = NEW.price * (1 - NEW.discount_percent / 100);

-- BEFORE UPDATE: recompute if price or discount changes
CREATE TRIGGER calc_final_price_update
BEFORE UPDATE ON products
FOR EACH ROW
SET NEW.final_price = NEW.price * (1 - NEW.discount_percent / 100);

-- Alternative: use GENERATED columns (MySQL 5.7+, PostgreSQL 12+)
-- final_price DECIMAL(10,2) GENERATED ALWAYS AS
--     (price * (1 - discount_percent / 100)) STORED

-- Prevent deletion of critical records
CREATE TRIGGER prevent_delete_admin
BEFORE DELETE ON users
FOR EACH ROW
BEGIN
    IF OLD.role = 'admin' THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete admin users';
    END IF;
END //

-- Soft delete instead of hard delete
CREATE TRIGGER soft_delete
BEFORE DELETE ON articles
FOR EACH ROW
BEGIN
    -- Prevent actual deletion, mark as deleted instead
    INSERT INTO articles (id, title, body, deleted_at)
    VALUES (OLD.id, OLD.title, OLD.body, NOW())
    ON DUPLICATE KEY UPDATE deleted_at = NOW();
    -- Still need to prevent the DELETE — use SIGNAL or a flag
END //

-- PostgreSQL: raise exception in trigger function
-- IF OLD.role = 'admin' THEN
--     RAISE EXCEPTION 'Cannot delete admin users';
-- END IF;

-- View existing triggers
-- MySQL:
SHOW TRIGGERS;
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'mydb';

-- PostgreSQL:
SELECT tgname, tgrelid::regclass, tgtype FROM pg_trigger;

-- SQL Server:
SELECT name, type_desc FROM sys.triggers WHERE parent_id = OBJECT_ID('employees');

-- Drop a trigger
DROP TRIGGER IF EXISTS audit_log;  -- MySQL
DROP TRIGGER IF EXISTS audit_log ON employees;  -- PostgreSQL

-- Disable/enable triggers (PostgreSQL)
ALTER TABLE employees DISABLE TRIGGER ALL;
ALTER TABLE employees ENABLE TRIGGER ALL;

-- Disable for bulk operations (SQL Server)
DISABLE TRIGGER trg_audit ON employees;
-- ... bulk operations ...
ENABLE TRIGGER trg_audit ON employees;

-- MySQL: a function that returns one value
CREATE FUNCTION calculate_discount(
    price DECIMAL(10,2),
    customer_tier VARCHAR(20)
) RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE discount_rate DECIMAL(5,2);
    SET discount_rate = CASE customer_tier
        WHEN 'gold'   THEN 0.20
        WHEN 'silver' THEN 0.10
        WHEN 'bronze' THEN 0.05
        ELSE 0.00
    END;
    RETURN price * (1 - discount_rate);
END //

-- Usage in queries:
SELECT name, price, calculate_discount(price, tier) AS final_price
FROM orders;

-- DETERMINISTIC: same inputs always give same output (cacheable)
-- READS SQL DATA: function reads but doesn't modify tables

-- SQL Server: Inline table-valued function (fast, like a view)
CREATE FUNCTION fn_OrdersByCustomer(@cust_id INT)
RETURNS TABLE
AS
RETURN (
    SELECT o.id, o.order_date, o.total
    FROM orders o
    WHERE o.customer_id = @cust_id
);
-- Usage: SELECT * FROM fn_OrdersByCustomer(42);

-- PostgreSQL: function returning a table
CREATE OR REPLACE FUNCTION get_orders_by_customer(cust_id INT)
RETURNS TABLE(order_id INT, order_date DATE, total DECIMAL) AS $$
    SELECT id, order_date, total
    FROM orders
    WHERE customer_id = cust_id;
$$ LANGUAGE SQL;

-- Usage:
SELECT * FROM get_orders_by_customer(42);

-- Multi-statement TVF (SQL Server) — slower, materializes result
-- CREATE FUNCTION fn_ComplexReport(@date DATE)
-- RETURNS @result TABLE (...)
-- AS BEGIN ... INSERT INTO @result ... RETURN END

-- Create a function to split full name into parts
CREATE FUNCTION get_first_name(full_name VARCHAR(200))
RETURNS VARCHAR(100)
DETERMINISTIC
BEGIN
    DECLARE space_pos INT;
    SET space_pos = LOCATE(' ', full_name);
    IF space_pos > 0 THEN
        RETURN SUBSTRING(full_name, 1, space_pos - 1);
    ELSE
        RETURN full_name;
    END IF;
END //

-- Function to generate slug from a title
CREATE FUNCTION make_slug(title VARCHAR(500))
RETURNS VARCHAR(500)
DETERMINISTIC
BEGIN
    DECLARE slug VARCHAR(500);
    SET slug = LOWER(title);
    SET slug = REPLACE(slug, ' ', '-');
    SET slug = REGEXP_REPLACE(slug, '[^a-z0-9-]', '');
    RETURN slug;
END //

-- Usage:
SELECT get_first_name('John Doe Smith') AS first_name;  -- John
SELECT make_slug('Hello World! 2024') AS slug;           -- hello-world-2024

-- PostgreSQL: custom aggregate function
-- Step 1: Define a state transition function
CREATE OR REPLACE FUNCTION geom_mean_state(state numeric[], val numeric)
RETURNS numeric[] AS $$
BEGIN
    IF val IS NULL THEN RETURN state; END IF;
    IF state IS NULL THEN
        RETURN ARRAY[val];
    ELSE
        RETURN state || val;
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Step 2: Define the final function
CREATE OR REPLACE FUNCTION geom_mean_final(vals numeric[])
RETURNS numeric AS $$
BEGIN
    IF vals IS NULL OR array_length(vals, 1) IS NULL THEN
        RETURN NULL;
    END IF;
    RETURN exp(avg(ln(v)) FROM unnest(vals) AS v);
END;
$$ LANGUAGE plpgsql;

-- Step 3: Create the aggregate
CREATE AGGREGATE geometric_mean(numeric) (
    SFUNC = geom_mean_state,
    STYPE = numeric[],
    FINALFUNC = geom_mean_final,
    INITCOND = '{}'
);

-- Usage:
SELECT category, geometric_mean(price) FROM products GROUP BY category;

-- FUNCTIONS: return values, usable in queries, no side effects
CREATE FUNCTION get_full_name(fname VARCHAR, lname VARCHAR)
RETURNS VARCHAR(200)
DETERMINISTIC
RETURN CONCAT(fname, ' ', lname);

-- Can be used in SELECT:
SELECT get_full_name(first, last) FROM users;  -- OK
SELECT * FROM users WHERE get_full_name(first, last) LIKE 'J%';  -- OK

-- STORED PROCEDURES: can modify data, use transactions, return result sets
CREATE PROCEDURE transfer_funds(
    IN from_acct INT, IN to_acct INT, IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    START TRANSACTION;
    UPDATE accounts SET balance = balance - amount WHERE id = from_acct;
    UPDATE accounts SET balance = balance + amount WHERE id = to_acct;
    INSERT INTO transfers (from_id, to_id, amount) VALUES (from_acct, to_acct, amount);
    COMMIT;
END //

-- Call procedure (can't use in SELECT):
CALL transfer_funds(1, 2, 100.00);

-- 1NF: each column contains atomic (indivisible) values
--       no repeating groups, each row is unique

-- VIOLATION: comma-separated values in one column
CREATE TABLE bad_orders (
    id INT,
    customer_name VARCHAR(100),
    products VARCHAR(500)  -- "laptop, mouse, keyboard" — BAD!
);

-- 1NF COMPLIANT: separate row per product
CREATE TABLE orders (
    order_id INT,
    customer_name VARCHAR(100),
    product_name VARCHAR(100),  -- one product per row
    PRIMARY KEY (order_id, product_name)  -- composite key for uniqueness
);

-- Better: normalize further with separate tables
CREATE TABLE orders (order_id INT PRIMARY KEY, customer_name VARCHAR(100));
CREATE TABLE order_items (
    order_id INT REFERENCES orders(order_id),
    product_name VARCHAR(100),
    quantity INT,
    PRIMARY KEY (order_id, product_name)
);

-- 2NF: 1NF + no partial dependencies (non-key attrs depend on FULL key)
-- Problem: order_items has composite key (order_id, product_id)
-- but product_name depends only on product_id (partial dependency)

-- VIOLATION of 2NF:
CREATE TABLE bad_order_items (
    order_id INT,
    product_id INT,
    product_name VARCHAR(100),  -- depends on product_id only!
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 2NF COMPLIANT: move product_name to products table
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(100)
);
CREATE TABLE order_items (
    order_id INT,
    product_id INT REFERENCES products(product_id),
    quantity INT,
    PRIMARY KEY (order_id, product_id)
);

-- 3NF: 2NF + no transitive dependencies
-- (non-key attrs don't depend on other non-key attrs)
-- Problem: orders table has customer_name that depends on customer_id
-- Solution: separate customers table (see above)

-- Denormalization: intentionally adding redundancy for performance
-- Normalized (3NF): requires JOINs to get full order info
SELECT o.order_id, c.name, p.product_name, oi.quantity, p.price
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.order_id = oi.order_id
JOIN products p ON oi.product_id = p.id;

-- Denormalized: store redundant data for read speed
CREATE TABLE order_summary (
    order_id INT PRIMARY KEY,
    customer_id INT,
    customer_name VARCHAR(100),    -- redundant (also in customers)
    customer_email VARCHAR(200),   -- redundant
    total_amount DECIMAL(10,2),    -- pre-calculated
    item_count INT,                -- pre-calculated
    order_date TIMESTAMP
);

-- Trade-off: faster reads, slower writes, risk of inconsistency
-- Use for: reporting tables, read-heavy dashboards, data warehouses

-- Materialized views are a managed form of denormalization:
CREATE MATERIALIZED VIEW sales_summary AS
SELECT date, region, SUM(amount) AS total FROM sales GROUP BY date, region;
REFRESH MATERIALIZED VIEW sales_summary;

CREATE TABLE departments (
    dept_id INT PRIMARY KEY AUTO_INCREMENT,
    dept_name VARCHAR(100) NOT NULL UNIQUE,
    budget DECIMAL(12,2) CHECK (budget >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    emp_id INT PRIMARY KEY AUTO_INCREMENT,
    emp_name VARCHAR(100) NOT NULL,
    email VARCHAR(200) UNIQUE,
    dept_id INT,
    salary DECIMAL(10,2) CHECK (salary > 0 AND salary < 1000000),
    hire_date DATE NOT NULL,
    manager_id INT REFERENCES employees(emp_id),  -- self-reference
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
        ON DELETE SET NULL      -- don't delete dept if employees exist
        ON UPDATE CASCADE,      -- update FK if dept_id changes
    INDEX idx_dept (dept_id),
    INDEX idx_email (email)
);

-- Constraint types:
-- PRIMARY KEY: unique + not null (clustered index)
-- UNIQUE: no duplicates (allows NULLs)
-- NOT NULL: required field
-- CHECK: custom condition
-- FOREIGN KEY: referential integrity
-- DEFAULT: value when not specified

-- B-tree index (default): good for =, <, >, BETWEEN, ORDER BY
CREATE INDEX idx_last_name ON employees(last_name);

-- Composite index: order matters! (leftmost prefix rule)
CREATE INDEX idx_dept_salary ON employees(dept_id, salary);
-- Usable for: WHERE dept_id = 5
-- Usable for: WHERE dept_id = 5 AND salary > 50000
-- NOT usable for: WHERE salary > 50000 (skips dept_id)

-- Covering index: includes all columns a query needs
CREATE INDEX idx_covering ON orders(customer_id, order_date)
    INCLUDE (total_amount, status);
-- Query can be satisfied from index alone (no table lookup)

-- Partial/partial index: index only matching rows
CREATE INDEX idx_active_users ON users(last_login)
    WHERE active = true;  -- PostgreSQL

-- Don't over-index: every index slows writes
-- Index columns used in: WHERE, JOIN, ORDER BY, GROUP BY
-- Drop unused indexes: SELECT * FROM pg_stat_user_indexes;

-- EXPLAIN shows the query execution plan
EXPLAIN SELECT * FROM orders WHERE customer_id = 42;

-- EXPLAIN ANALYZE actually runs the query (PostgreSQL)
EXPLAIN ANALYZE SELECT * FROM orders WHERE customer_id = 42;

-- PostgreSQL output:
-- Index Scan using idx_customer on orders  (cost=0.29..8.31 rows=1 width=74)
--   Index Cond: (customer_id = 42)
--   Execution Time: 0.042 ms

-- Key columns to check:
-- - type: scan method (const > eq_ref > ref > range > index > ALL)
-- - rows: estimated rows examined (lower is better)
-- - key: which index is used (NULL = no index, bad!)
-- - Extra: "Using filesort" or "Using temporary" = warning signs

-- MySQL: EXPLAIN FORMAT=JSON for detailed output
-- SQL Server: SET SHOWPLAN_TEXT ON; or Actual Execution Plan in SSMS

-- 1. Missing index → full table scan
-- Bad: SELECT * FROM orders WHERE customer_id = 42; (no index)
-- Fix: CREATE INDEX idx_customer ON orders(customer_id);

-- 2. Index not used due to function on column
-- Bad: WHERE YEAR(order_date) = 2024  (function prevents index use)
-- Good: WHERE order_date >= '2024-01-01' AND order_date < '2025-01-01'

-- 3. SELECT * instead of specific columns
-- Bad: SELECT * FROM large_table WHERE id = 1;
-- Good: SELECT id, name, email FROM large_table WHERE id = 1;

-- 4. OR conditions preventing index use
-- Bad: WHERE dept = 'A' OR dept = 'B' OR dept = 'C'
-- Good: WHERE dept IN ('A', 'B', 'C')

-- 5. LIKE with leading wildcard
-- Bad: WHERE name LIKE '%son'  (can't use index)
-- OK:  WHERE name LIKE 'John%'  (can use index)

-- 6. Implicit type conversion
-- Bad: WHERE string_column = 123  (converts to string, skips index)
-- Good: WHERE string_column = '123'

-- Join order matters: smallest table first (optimizer usually handles this)
-- Ensure join columns are indexed (usually foreign keys)
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_order_items_order ON order_items(order_id);

-- Use INNER JOIN when you don't need unmatched rows (faster than OUTER)
SELECT c.name, COUNT(o.order_id) AS order_count
FROM customers c
INNER JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;

-- Avoid joining unnecessary tables — fetch details lazily if needed
-- Bad: join 5 tables when you only need 2 columns
-- Good: split into simpler queries or use a covering index

-- EXISTS vs IN for subqueries
-- EXISTS is often faster for large subquery results:
SELECT name FROM customers c
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.id
);
-- IN is better for small lists:
SELECT * FROM orders WHERE customer_id IN (1, 2, 3);

-- Bad: OFFSET pagination (slow for large offsets)
SELECT * FROM orders ORDER BY created_at DESC LIMIT 10 OFFSET 10000;
-- Must scan and discard 10000 rows — gets slower as you page deeper

-- Good: Keyset (cursor) pagination using WHERE
SELECT * FROM orders
WHERE created_at < '2024-01-15 10:30:00'  -- last seen value
ORDER BY created_at DESC
LIMIT 10;
-- Uses index efficiently — constant time regardless of page depth

-- For composite keyset pagination (handles ties):
SELECT * FROM orders
WHERE (created_at, id) < ('2024-01-15 10:30:00', 12345)
ORDER BY created_at DESC, id DESC
LIMIT 10;

-- Count total (expensive on large tables — avoid if possible)
-- Instead of COUNT(*), use an approximate count:
SELECT reltuples::bigint FROM pg_class WHERE relname = 'orders';

-- 1. Replace subqueries with JOINs when possible
-- Slow: SELECT * FROM orders WHERE customer_id IN
--       (SELECT id FROM customers WHERE active = true);
-- Fast: SELECT o.* FROM orders o
--       JOIN customers c ON o.customer_id = c.id WHERE c.active = true;

-- 2. Use UNION ALL instead of UNION (avoids dedup sort)
SELECT 'A' UNION ALL SELECT 'B';  -- fast
SELECT 'A' UNION SELECT 'B';       -- sorts to remove duplicates

-- 3. Batch operations instead of row-by-row
-- Slow: 1000 individual INSERTs
-- Fast: INSERT INTO t VALUES (1,'a'), (2,'b'), (3,'c'), ...;

-- 4. Use CTEs for readability, but know they may not optimize well
-- (PostgreSQL 12+ inlines CTEs; older versions materialize them)

-- 5. Avoid SELECT DISTINCT when you can use GROUP BY or EXISTS
-- 6. Use prepared statements for repeated queries (plan caching)
PREPARE get_user AS SELECT * FROM users WHERE id = $1;
EXECUTE get_user(42);

-- 7. Analyze tables for up-to-date statistics
ANALYZE orders;  -- PostgreSQL: updates planner statistics

-- SQL (Relational): structured, consistent, queryable
-- Best for: financial systems, e-commerce, CRM, any ACID-requiring app
-- Examples: PostgreSQL, MySQL, SQL Server, Oracle

-- NoSQL: flexible schema, horizontal scaling, specific data models
-- Document: MongoDB, CouchDB — JSON-like documents, flexible schema
-- Key-Value: Redis, DynamoDB — fast lookups, caching, sessions
-- Column-Family: Cassandra, HBase — wide-column, time-series, write-heavy
-- Graph: Neo4j, ArangoDB — relationships, social networks, recommendations

-- Decision factors:
-- 1. Data structure: fixed → SQL, evolving/varied → NoSQL
-- 2. Consistency: strict ACID → SQL, eventual consistency OK → NoSQL
-- 3. Scale: vertical (bigger server) → SQL, horizontal (more servers) → NoSQL
-- 4. Queries: complex JOINs → SQL, simple lookups → NoSQL
-- 5. Team expertise: SQL is universal, NoSQL varies

-- Many modern databases blur the line:
-- PostgreSQL: JSON columns, full-text search, pub/sub
-- MongoDB: transactions (multi-document ACID since 4.0)

-- In SQL, you'd normalize this into 3 tables:
-- customers, orders, order_items

-- In a document store (MongoDB), you might embed everything:
-- (pseudo-code, not SQL)
// db.orders.insertOne({
//   _id: 1,
//   customer: { name: "Alice", email: "[email protected]" },
//   items: [
//     { product: "Laptop", price: 999, qty: 1 },
//     { product: "Mouse", price: 25, qty: 2 }
//   ],
//   total: 1049,
//   status: "shipped",
//   created_at: ISODate("2024-01-15")
// })

-- SQL equivalent with JSON column (PostgreSQL):
CREATE TABLE orders (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL  -- stores the entire document
);
INSERT INTO orders (data) VALUES ('{
    "customer": {"name": "Alice", "email": "[email protected]"},
    "items": [{"product": "Laptop", "price": 999, "qty": 1}],
    "total": 999
}');

-- Query JSON data:
SELECT data->'customer'->>'name' AS name FROM orders
WHERE data->'total' > 500;

-- Redis is an in-memory key-value store — not SQL
-- Common patterns (Redis commands, not SQL):

-- Caching: store expensive query results
// SET user:42:profile '{"name":"Alice","age":30}' EX 3600
// GET user:42:profile  -- returns cached data, expires in 1 hour

-- Session storage: fast, ephemeral
// SET session:abc123 '{"user_id":42}' EX 1800  -- 30 min TTL

-- Counters & leaderboards:
// INCR page:home:views           -- atomic counter
// ZADD leaderboard 1500 "alice"  -- sorted set for rankings
// ZREVRANGE leaderboard 0 9      -- top 10 players

-- SQL equivalent for caching (materialized/precomputed):
CREATE TABLE cache (
    cache_key VARCHAR(200) PRIMARY KEY,
    cache_value TEXT,
    expires_at TIMESTAMP,
    INDEX idx_expires (expires_at)
);
-- Periodically: DELETE FROM cache WHERE expires_at < NOW();

-- When to use Redis vs SQL cache:
-- Redis: sub-millisecond reads, data structures (sets, sorted sets)
-- SQL: when you need ACID or already have a database connection

-- Modern applications often use MULTIPLE database types:
-- Each data store handles what it does best

-- Typical architecture:
-- 1. PostgreSQL: core transactional data (users, orders, payments)
--    ACID guarantees, complex queries, foreign keys
CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR UNIQUE);
CREATE TABLE orders (id SERIAL PRIMARY KEY, user_id INT REFERENCES users(id));

-- 2. Redis: session storage, caching, rate limiting
--    Fast in-memory access, auto-expiring keys

-- 3. Elasticsearch: full-text search, log analytics
--    Inverted index, faceted search, aggregations

-- 4. S3/Object storage: files, images, backups
--    Cheap, unlimited, HTTP-accessible

-- 5. TimescaleDB/InfluxDB: time-series metrics
--    Optimized for timestamped data, downsampling

-- Challenge: data consistency across stores
-- Solution: event-driven architecture (CDC, message queues)
--   1. Write to PostgreSQL (source of truth)
--   2. Publish event to Kafka
--   3. Consumers update Redis cache, Elasticsearch index, etc.
--   4. eventual consistency — reads may be slightly stale

-- ACID (SQL databases):
-- Atomicity: all operations in a transaction succeed or fail together
-- Consistency: data always satisfies constraints (FK, CHECK, etc.)
-- Isolation: concurrent transactions don't interfere
-- Durability: committed data survives crashes

-- SQL transaction example:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
-- If either fails, ROLLBACK undoes both
COMMIT;  -- both updates are permanent

-- BASE (NoSQL databases):
-- Basically Available: system responds (may be stale)
-- Soft state: state changes without input (eventual consistency)
-- Eventually consistent: data converges over time

-- NoSQL trade-off: higher availability & partition tolerance
-- at the cost of immediate consistency

-- CAP Theorem: in a network partition, choose:
-- - CP (consistency): refuse writes (some NoSQL: HBase, MongoDB)
-- - AP (availability): accept writes, reconcile later (Cassandra, DynamoDB)

-- SQL databases are typically CA (consistent + available, no partition tolerance
-- in single-node setups; distributed SQL like CockroachDB adds P)

WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 80000
), by_dept AS (
  SELECT department, COUNT(*) AS cnt FROM high_earners GROUP BY department
)
SELECT * FROM by_dept ORDER BY cnt DESC;

WITH RECURSIVE org_chart AS (
  -- Anchor: top-level managers
  SELECT id, name, manager_id, 1 AS level
  FROM employees WHERE manager_id IS NULL
  UNION ALL
  -- Recursive: subordinates
  SELECT e.id, e.name, e.manager_id, oc.level + 1
  FROM employees e
  JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart;

WITH RECURSIVE fib(n, a, b) AS (
  SELECT 1, 0, 1
  UNION ALL
  SELECT n + 1, b, a + b FROM fib WHERE n < 10
)
SELECT n, a FROM fib;
-- Result: 0, 1, 1, 2, 3, 5, 8, 13, 21, 34

WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, CAST(name AS VARCHAR(1000)) AS path
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.path || ' > ' || c.name
  FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT id, path FROM category_tree ORDER BY path;

-- CTE: more readable, can be referenced multiple times
WITH active_users AS (SELECT * FROM users WHERE active = 1)
SELECT * FROM active_users WHERE age > 18
UNION ALL
SELECT * FROM active_users WHERE age <= 18;
-- Subquery: inline, cannot be reused
SELECT * FROM (SELECT * FROM users WHERE active = 1) au WHERE au.age > 18;

CREATE INDEX idx_email ON users(email);
CREATE INDEX idx_name_age ON users(last_name, first_name, age);
-- Composite index: useful for
-- WHERE last_name = 'Smith' AND first_name = 'John'
-- WHERE last_name = 'Smith' (leftmost prefix)

CREATE INDEX idx_active_users ON users(last_login) WHERE active = 1;
-- Only indexes active users, saving space
-- Useful when queries always filter on active = 1

-- PostgreSQL: INCLUDE clause
CREATE INDEX idx_users_covering ON users(last_name) INCLUDE (first_name, email);
-- The query is "covered" if all columns are in the index:
SELECT first_name, email FROM users WHERE last_name = 'Smith';
-- No table lookup needed (index-only scan)

-- B-Tree: default, good for equality and range
CREATE INDEX idx_btree ON users(email);
-- Hash: equality only (PostgreSQL)
CREATE INDEX idx_hash ON users(email) USING HASH;
-- GIN: full-text search, arrays, JSON
CREATE INDEX idx_gin ON docs USING GIN (tsv);
-- GiST: geometric, range types
CREATE INDEX idx_gist ON places USING GIST (location);

-- Check index usage (PostgreSQL)
SELECT * FROM pg_stat_user_indexes;
-- Find unused indexes
SELECT relname, indexrelname FROM pg_stat_user_indexes WHERE idx_scan = 0;
-- Rebuild fragmented index
REINDEX INDEX idx_users_email;
-- Analyze for query planner
ANALYZE users;

BEGIN TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- Or: ROLLBACK to undo

BEGIN;
INSERT INTO orders VALUES (1);
SAVEPOINT sp1;
INSERT INTO orders VALUES (2);
-- Oops, rollback to savepoint
ROLLBACK TO sp1;
-- Only order 1 is inserted
INSERT INTO orders VALUES (3);
COMMIT;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- Levels (increasing isolation):
-- READ UNCOMMITTED: dirty reads
-- READ COMMITTED: no dirty reads (default)
-- REPEATABLE READ: no non-repeatable reads
-- SERIALIZABLE: full isolation

-- Transaction 1
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 1;
UPDATE accounts SET balance = 0 WHERE id = 2;  -- Waits
-- Transaction 2
BEGIN;
UPDATE accounts SET balance = 0 WHERE id = 2;
UPDATE accounts SET balance = 0 WHERE id = 1;  -- Waits
-- Deadlock! Database aborts one transaction

-- Add version column
ALTER TABLE products ADD COLUMN version INT DEFAULT 0;
-- Update with version check
UPDATE products SET price = 100, version = version + 1
WHERE id = 1 AND version = 5;
-- If 0 rows affected, someone else updated first

CREATE TABLE events (id SERIAL, data JSONB);
INSERT INTO events (data) VALUES ('{"user": "alice", "action": "login"}');
SELECT data->>'user' AS user_name FROM events;
SELECT * FROM events WHERE data->>'action' = 'login';

SELECT data->'address'->'city' AS city FROM users;
SELECT * FROM users WHERE data @> '{"role": "admin"}';
SELECT jsonb_object_keys(data) FROM users;
-- Update JSON
UPDATE users SET data = jsonb_set(data, '{last_login}', '"2024-01-01"');

SELECT department,
  json_agg(json_build_object('name', name, 'salary', salary)) AS employees
FROM employees
GROUP BY department;
-- Result: {"department": "Eng", "employees": [{"name": "Alice", "salary": 90000}, ...]}

CREATE TABLE config (id INT, settings JSON);
INSERT INTO config VALUES (1, '{"theme": "dark", "lang": "en"}');
SELECT settings->>'$.theme' FROM config;
SELECT * FROM config WHERE JSON_EXTRACT(settings, '$.lang') = 'en';
-- Update
UPDATE config SET settings = JSON_SET(settings, '$.theme', 'light');

-- PostgreSQL GIN index on JSONB
CREATE INDEX idx_events_data ON events USING GIN (data);
-- Index specific path
CREATE INDEX idx_events_user ON events ((data->>'user'));
-- MySQL functional index
CREATE INDEX idx_theme ON config ((CAST(settings->>'$.theme' AS CHAR(50))));

EXPLAIN ANALYZE
SELECT * FROM users WHERE email = '[email protected]';
-- Shows: scan type, cost, rows, actual time
-- Seq Scan: full table scan (slow)
-- Index Scan: uses index (fast)
-- Bitmap Heap Scan: index + table lookup

-- BAD: SELECT * fetches all columns
SELECT * FROM users;
-- GOOD: select only needed columns
SELECT id, name FROM users;
-- BAD: function on indexed column
WHERE YEAR(created_at) = 2024
-- GOOD: sargable
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'

-- Use INNER JOIN for required relationships
SELECT u.name, o.total FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Index join columns
CREATE INDEX idx_orders_user_id ON orders(user_id);
-- For large joins, ensure both columns are indexed

-- BAD: OFFSET scans all skipped rows
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 10000;
-- GOOD: keyset pagination
SELECT * FROM users WHERE id > 10000 ORDER BY id LIMIT 10;
-- Stable pagination with cursor
SELECT * FROM users WHERE (created_at, id) > ('2024-01-01', 100) ORDER BY created_at, id LIMIT 10;

CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id, SUM(quantity) AS total, AVG(price) AS avg_price
FROM sales GROUP BY product_id;
-- Refresh periodically
REFRESH MATERIALIZED VIEW sales_summary;
-- Create index on materialized view
CREATE INDEX ON sales_summary (total);

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- Find pairs in same department
SELECT a.name, b.name FROM employees a, employees b
WHERE a.department = b.department AND a.id < b.id;

-- Cartesian product: every combination
SELECT s.size, c.color FROM sizes s CROSS JOIN colors c;
-- Generate all size/color combinations
-- Useful for generating test data or matrices

SELECT u.name, o.order_id
FROM users u
FULL OUTER JOIN orders o ON u.id = o.user_id;
-- Returns all users and all orders
-- NULLs where there is no match

-- Users who have never ordered
SELECT u.* FROM users u
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Alternative: LEFT JOIN ... WHERE IS NULL
SELECT u.* FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.id IS NULL;

-- Users who have at least one order
SELECT u.* FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o WHERE o.user_id = u.id
);
-- Alternative: IN
SELECT * FROM users WHERE id IN (SELECT user_id FROM orders);

-- NULL = NULL is NULL (not true!)
SELECT * FROM users WHERE phone = NULL;  -- Returns nothing
SELECT * FROM users WHERE phone IS NULL;  -- Correct
SELECT * FROM users WHERE phone IS NOT NULL;
-- NULL in arithmetic yields NULL
SELECT 5 + NULL;  -- NULL

-- BAD: string concatenation
query = "SELECT * FROM users WHERE name = '" + input + "'"
-- GOOD: parameterized queries
SELECT * FROM users WHERE name = ?;
-- PostgreSQL: $1
-- MySQL: ?
-- Always use parameters, never concatenate

-- BAD: non-aggregated column not in GROUP BY
SELECT department, name, COUNT(*) FROM employees GROUP BY department;
-- Error: which name to show?
-- GOOD: aggregate or include in GROUP BY
SELECT department, COUNT(*) FROM employees GROUP BY department;
SELECT department, MAX(name) FROM employees GROUP BY department;

-- Floating point precision issues
SELECT 0.1 + 0.2;  -- 0.30000000000000004
-- Use DECIMAL for money
CREATE TABLE accounts (balance DECIMAL(10, 2));
SELECT 0.10 + 0.20;  -- 0.30 (exact)

-- BAD: comparing string to number
SELECT * FROM users WHERE phone = 1234567890;
-- May cause full table scan due to type conversion
-- GOOD: compare same types
SELECT * FROM users WHERE phone = '1234567890';
-- Or cast explicitly
SELECT * FROM users WHERE CAST(phone AS BIGINT) = 1234567890;