The Ultimate SQL Cheatsheet

-- ── CREATE TABLE ──
CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    age         INTEGER CHECK (age >= 0),
    role        VARCHAR(20) DEFAULT 'user',
    salary      DECIMAL(10, 2),
    is_active   BOOLEAN DEFAULT TRUE,
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- ── INSERT ──
INSERT INTO users (name, email, age, role, salary) VALUES
    ('Alice', 'alice@example.com', 30, 'admin', 85000.00),
    ('Bob', 'bob@example.com', 25, 'user', 65000.00),
    ('Charlie', 'charlie@example.com', 35, 'manager', 95000.00);

INSERT INTO users (name, email) VALUES
    ('Diana', 'diana@example.com')
RETURNING id, name;

-- ── SELECT ──
SELECT * FROM users;
SELECT name, email, role FROM users;
SELECT DISTINCT role FROM users;

-- WHERE conditions
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE role = 'admin' AND is_active = TRUE;
SELECT * FROM users WHERE role IN ('admin', 'manager');
SELECT * FROM users WHERE name LIKE 'A%';
SELECT * FROM users WHERE email IS NOT NULL;
SELECT * FROM users WHERE age BETWEEN 20 AND 35;

-- ── UPDATE ──
UPDATE users SET salary = salary * 1.10 WHERE role = 'manager';
UPDATE users SET is_active = FALSE WHERE id = 2;

-- ── DELETE ──
DELETE FROM users WHERE id = 3;
DELETE FROM users WHERE is_active = FALSE;

-- ── Sample Tables ──
-- users (id, name, department_id)
-- departments (id, name)
-- orders (id, user_id, total, status)
-- order_items (id, order_id, product_name, quantity)

-- ── INNER JOIN (only matching rows) ──
SELECT u.name, d.name AS department
FROM users u
INNER JOIN departments d ON u.department_id = d.id;

-- ── LEFT JOIN (all from left, matching from right) ──
SELECT u.name, d.name AS department
FROM users u
LEFT JOIN departments d ON u.department_id = d.id;

-- ── RIGHT JOIN (all from right, matching from left) ──
SELECT d.name AS department, COUNT(u.id) AS member_count
FROM departments d
RIGHT JOIN users u ON d.id = u.department_id
GROUP BY d.name;

-- ── FULL OUTER JOIN (all rows from both) ──
SELECT u.name, d.name AS department
FROM users u
FULL OUTER JOIN departments d ON u.department_id = d.id;

-- ── SELF JOIN ──
SELECT e1.name AS employee, e2.name AS manager
FROM users e1
LEFT JOIN users e2 ON e1.manager_id = e2.id;

-- ── Multiple JOINs ──
SELECT u.name, o.id AS order_id, oi.product_name, oi.quantity
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN order_items oi ON o.id = oi.order_id
WHERE o.status = 'completed'
ORDER BY o.id DESC;

-- ── Subqueries ──
-- WHERE clause
SELECT * FROM users
WHERE salary > (SELECT AVG(salary) FROM users);

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

-- FROM clause (derived table)
SELECT department_id, avg_salary
FROM (
    SELECT department_id, AVG(salary) AS avg_salary
    FROM users
    GROUP BY department_id
) AS dept_avg
WHERE avg_salary > 70000;

-- EXISTS
SELECT d.name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM users u WHERE u.department_id = d.id
);

-- ── Window Functions ──
-- Syntax: FUNCTION() OVER (PARTITION BY ... ORDER BY ...)

-- ── ROW_NUMBER, RANK, DENSE_RANK ──
SELECT
    name,
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary DESC) AS row_num,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rank,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dense_rank
FROM users;

-- ROW_NUMBER: 1, 2, 3, 4 (always sequential)
-- RANK:       1, 2, 2, 4 (gaps for ties)
-- DENSE_RANK: 1, 2, 2, 3 (no gaps)

-- ── Running Totals ──
SELECT
    order_date,
    total,
    SUM(total) OVER (
        ORDER BY order_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders;

-- ── Moving Averages ──
SELECT
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS moving_avg_7day
FROM daily_revenue;

-- ── LAG / LEAD ──
SELECT
    date,
    revenue,
    LAG(revenue, 1) OVER (ORDER BY date) AS prev_day,
    LEAD(revenue, 1) OVER (ORDER BY date) AS next_day,
    revenue - LAG(revenue, 1) OVER (ORDER BY date) AS daily_change
FROM daily_revenue;

-- ── FIRST_VALUE / LAST_VALUE ──
SELECT
    department_id,
    name,
    salary,
    FIRST_VALUE(name) OVER (
        PARTITION BY department_id ORDER BY salary DESC
    ) AS highest_earner
FROM users;

-- ── Percentage of Total ──
SELECT
    name,
    salary,
    ROUND(
        salary * 100.0 / SUM(salary) OVER (), 2
    ) AS pct_of_total
FROM users
ORDER BY salary DESC;

-- ── GROUP BY with Aggregates ──
SELECT
    department_id,
    COUNT(*) AS total_users,
    COUNT(*) FILTER (WHERE role = 'admin') AS admin_count,
    AVG(salary) AS avg_salary,
    MIN(salary) AS min_salary,
    MAX(salary) AS max_salary,
    SUM(salary) AS total_salary
FROM users
GROUP BY department_id
HAVING COUNT(*) >= 3
ORDER BY avg_salary DESC;

-- ── GROUPING SETS ──
SELECT
    department_id,
    role,
    COUNT(*),
    AVG(salary)
FROM users
GROUP BY GROUPING SETS (
    (department_id, role),
    (department_id),
    (role),
    ()
);

-- ── Create Indexes ──
-- Single column
CREATE INDEX idx_users_email ON users (email);

-- Composite (multi-column)
CREATE INDEX idx_users_role_active ON users (role, is_active);

-- Unique index
CREATE UNIQUE INDEX idx_users_email ON users (email);

-- Partial index
CREATE INDEX idx_users_active ON users (name) WHERE is_active = TRUE;

-- Covering index (includes extra columns)
CREATE INDEX idx_orders_user ON orders (user_id) INCLUDE (total, status);

-- Expression index
CREATE INDEX idx_users_lower_email ON users (LOWER(email));

-- ── Analyze Query Performance ──
EXPLAIN ANALYZE
SELECT * FROM users WHERE email = 'alice@example.com';

-- ── Check Index Usage ──
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;

-- ── Find Missing Indexes ──
SELECT schemaname, tablename, attname, n_distinct, correlation
FROM pg_stats
WHERE schemaname = 'public'
ORDER BY correlation ASC;