SQL Fundamentals

SELECT & Filtering

-- Basic SELECT
SELECT id, name, email FROM users WHERE is_active = true;
SELECT * FROM orders WHERE created_at >= '2024-01-01' AND total > 100;
SELECT * FROM products WHERE category IN ('electronics', 'books');
SELECT * FROM users WHERE name LIKE 'Alice%';   -- starts with Alice
SELECT * FROM users WHERE email IS NOT NULL;

-- ORDER BY, LIMIT, OFFSET
SELECT * FROM posts ORDER BY created_at DESC LIMIT 20 OFFSET 40;

-- DISTINCT
SELECT DISTINCT country FROM users;

-- CASE expression
SELECT name,
  CASE
    WHEN score >= 90 THEN 'A'
    WHEN score >= 80 THEN 'B'
    ELSE 'C'
  END AS grade
FROM students;

-- Aggregates
SELECT
  COUNT(*) AS total_users,
  COUNT(DISTINCT country) AS countries,
  AVG(age) AS avg_age,
  MAX(created_at) AS latest,
  SUM(amount) AS revenue
FROM users;

-- GROUP BY / HAVING
SELECT country, COUNT(*) AS user_count
FROM users
GROUP BY country
HAVING COUNT(*) > 100
ORDER BY user_count DESC;

JOINs

-- INNER JOIN — only matching rows
SELECT u.name, o.id, o.total
FROM users u
INNER JOIN orders o ON o.user_id = u.id;

-- LEFT JOIN — all from left, nulls from right if no match
SELECT u.name, COUNT(o.id) AS order_count
FROM users u
LEFT JOIN orders o ON o.user_id = u.id
GROUP BY u.id, u.name;

-- Multiple JOINs
SELECT u.name, o.id, p.name AS product
FROM users u
JOIN orders o ON o.user_id = u.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p ON p.id = oi.product_id
WHERE o.status = 'completed';

-- Self JOIN — hierarchy
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- Subqueries
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- EXISTS (more efficient than IN for large sets)
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 500
);

CTEs & Window Functions

-- CTE (Common Table Expression)
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(total) AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY 1
),
revenue_with_growth AS (
  SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_revenue
)
SELECT
  month,
  revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM revenue_with_growth;

-- Recursive CTE — hierarchical data
WITH RECURSIVE org_chart AS (
  SELECT id, name, manager_id, 0 AS level
  FROM employees WHERE manager_id IS NULL  -- root
  UNION ALL
  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 ORDER BY level, name;

-- Window functions
SELECT
  name,
  salary,
  department,
  RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dept_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS global_rank,
  SUM(salary) OVER (PARTITION BY department) AS dept_total,
  AVG(salary) OVER () AS company_avg,
  LAG(salary, 1) OVER (ORDER BY hired_at) AS prev_salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

DDL & DML

-- CREATE
CREATE TABLE users (
  id         SERIAL PRIMARY KEY,
  email      VARCHAR(255) UNIQUE NOT NULL,
  name       VARCHAR(100) NOT NULL,
  role       VARCHAR(20) DEFAULT 'user' CHECK (role IN ('admin', 'user')),
  created_at TIMESTAMPTZ DEFAULT NOW()
);
CREATE INDEX idx_users_email ON users (email);
CREATE INDEX idx_users_created ON users (created_at DESC);

-- ALTER
ALTER TABLE users ADD COLUMN age INT;
ALTER TABLE users DROP COLUMN age;
ALTER TABLE users ALTER COLUMN name TYPE TEXT;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE posts ADD CONSTRAINT fk_user
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

-- INSERT
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice');
INSERT INTO users (email, name)
VALUES ('a@b.com', 'Alice'), ('b@c.com', 'Bob');

-- Upsert (conflict handling)
INSERT INTO users (email, name) VALUES ('a@b.com', 'Alice')
ON CONFLICT (email) DO UPDATE SET name = EXCLUDED.name;

-- UPDATE
UPDATE users SET name = 'Bob', role = 'admin' WHERE id = 1;

-- DELETE
DELETE FROM users WHERE created_at < NOW() - INTERVAL '1 year';

-- TRUNCATE (fast delete all)
TRUNCATE TABLE sessions RESTART IDENTITY CASCADE;