⚡ SQL Cheat Sheet

CREATE TABLE users (
  id INT PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT CHECK (age >= 0),
  dept_id INT REFERENCES departments(id),
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users DROP COLUMN phone;
ALTER TABLE users RENAME COLUMN name TO full_name;
ALTER TABLE users ALTER COLUMN age SET NOT NULL;
ALTER TABLE users ADD CONSTRAINT uq_email UNIQUE(email);

CREATE INDEX idx_email ON users(email);
CREATE UNIQUE INDEX idx_uq ON users(email);
CREATE INDEX idx_composite ON orders(customer_id, order_date DESC);
DROP INDEX idx_email;

DROP TABLE IF EXISTS temp_data;     -- Remove table + data
DROP TABLE orders CASCADE;          -- Drop with dependents
TRUNCATE TABLE staging_data;        -- Delete all rows fast (no rollback)
DELETE FROM logs WHERE dt < '2023-01-01'; -- Row-by-row delete

CREATE VIEW active_users AS
SELECT * FROM users WHERE status = 'active';

CREATE OR REPLACE VIEW monthly_revenue AS
SELECT DATE_TRUNC('month', order_date) AS month,
       SUM(amount) AS revenue
FROM orders GROUP BY 1;

DROP VIEW IF EXISTS active_users;

CREATE TEMPORARY TABLE temp_results AS
SELECT * FROM orders WHERE status = 'pending';

-- Session-scoped: auto-dropped when session ends
-- Useful for intermediate results in multi-step queries

INSERT INTO users (name, email) VALUES ('Alice', 'a@co.com');

-- Insert multiple rows
INSERT INTO users (name, email) VALUES
  ('Bob', 'b@co.com'),
  ('Carol', 'c@co.com');

-- Insert from SELECT
INSERT INTO archive SELECT * FROM orders WHERE year = 2022;

UPDATE employees SET salary = salary * 1.10
WHERE dept = 'Engineering';

-- Update with JOIN
UPDATE orders o
SET o.status = 'cancelled'
FROM customers c
WHERE o.customer_id = c.id AND c.is_blocked = TRUE;

DELETE FROM logs WHERE created_at < '2023-01-01';

-- Delete with subquery
DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers WHERE status = 'inactive'
);

MERGE INTO target t
USING source s ON t.id = s.id
WHEN MATCHED THEN
  UPDATE SET t.name = s.name, t.updated_at = NOW()
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name);

WHERE salary > 80000                 -- Comparison
WHERE dept IN ('Eng', 'Sales')       -- List match
WHERE salary BETWEEN 50000 AND 90000 -- Range
WHERE name LIKE 'A%'                 -- Starts with A
WHERE name ILIKE '%smith%'           -- Case-insensitive (Postgres/SF)
WHERE email IS NOT NULL              -- NULL check
WHERE NOT (age < 18)                 -- Negation

SELECT * FROM employees
ORDER BY salary DESC, name ASC
LIMIT 10 OFFSET 20;     -- Skip 20, take 10

-- FETCH FIRST (SQL standard)
ORDER BY salary DESC
FETCH FIRST 10 ROWS ONLY;

SELECT DISTINCT dept FROM employees;

SELECT name,
  CASE
    WHEN salary > 100000 THEN 'Senior'
    WHEN salary > 60000  THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

SELECT dept, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
GROUP BY dept
HAVING COUNT(*) >= 5
ORDER BY avg_sal DESC;

-- GROUP BY with ROLLUP (subtotals)
SELECT dept, role, SUM(salary)
FROM employees
GROUP BY ROLLUP(dept, role);

-- INNER JOIN: only matching rows
SELECT o.id, c.name
FROM orders o
JOIN customers c ON o.customer_id = c.id;

-- LEFT JOIN: all left rows, NULLs for no match
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

-- RIGHT JOIN: all right rows
SELECT * FROM orders o
RIGHT JOIN products p ON o.product_id = p.id;

-- FULL OUTER JOIN: all rows from both
SELECT * FROM table_a a
FULL OUTER JOIN table_b b ON a.id = b.id;

-- CROSS JOIN: Cartesian product
SELECT * FROM sizes CROSS JOIN colors;

-- Find employee and their manager
SELECT e.name AS employee,
       m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

-- SEMI JOIN: rows in A that have a match in B
SELECT * FROM orders o
WHERE EXISTS (SELECT 1 FROM returns r WHERE r.order_id = o.id);

-- ANTI JOIN: rows in A with NO match in B
SELECT * FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

-- Employees earning more than their department avg
SELECT name, salary, dept
FROM employees e
WHERE salary > (
  SELECT AVG(salary) FROM employees
  WHERE dept = e.dept  -- references outer query
);

SELECT * FROM customers
WHERE id IN (SELECT customer_id FROM orders WHERE year = 2024);

-- NOT IN: be careful with NULLs!
SELECT * FROM products
WHERE id NOT IN (SELECT product_id FROM order_items
                 WHERE product_id IS NOT NULL);

SELECT name, dept, salary,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn,
  RANK()       OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS drnk
FROM employees;
-- Ties: ROW_NUMBER=arbitrary, RANK=gaps, DENSE_RANK=no gaps

SELECT sale_date, amount,
  LAG(amount, 1)  OVER (ORDER BY sale_date) AS prev_day,
  LEAD(amount, 1) OVER (ORDER BY sale_date) AS next_day,
  amount - LAG(amount) OVER (ORDER BY sale_date) AS day_change
FROM daily_sales;

SELECT sale_date, amount,
  SUM(amount) OVER (
    ORDER BY sale_date ROWS UNBOUNDED PRECEDING
  ) AS running_total,
  AVG(amount) OVER (
    ORDER BY sale_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ) AS moving_avg_7d
FROM daily_sales;

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;

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 cume_dist
FROM employees;

WITH active_users AS (
  SELECT * FROM users WHERE status = 'active'
),
dept_counts AS (
  SELECT dept, COUNT(*) AS cnt
  FROM active_users GROUP BY dept
)
SELECT * FROM dept_counts WHERE cnt > 10;

-- Org hierarchy: find all reports under a manager
WITH RECURSIVE org_tree AS (
  SELECT id, name, manager_id, 1 AS depth
  FROM employees WHERE id = 1   -- root manager
  UNION ALL
  SELECT e.id, e.name, e.manager_id, t.depth + 1
  FROM employees e
  JOIN org_tree t ON e.manager_id = t.id
)
SELECT * FROM org_tree;

-- UNION ALL: combine, keep duplicates (faster)
SELECT email FROM customers
UNION ALL
SELECT email FROM leads;

-- UNION: combine, remove duplicates
-- INTERSECT: rows in both
-- EXCEPT: rows in first but not second

SELECT
  UPPER('hello'),             -- 'HELLO'
  LOWER('HELLO'),             -- 'hello'
  TRIM('  hi  '),             -- 'hi'
  LENGTH('hello'),            -- 5
  SUBSTRING('hello' FROM 2 FOR 3), -- 'ell'
  LEFT('hello', 3),           -- 'hel'
  RIGHT('hello', 2),          -- 'lo'
  REPLACE('hello', 'l', 'r'), -- 'herro'
  CONCAT(first_name, ' ', last_name);

WHERE name LIKE 'J%'            -- Starts with J
WHERE name LIKE '%son'          -- Ends with son
WHERE name LIKE '_a%'           -- 2nd char is a
WHERE email LIKE '%@gmail.com'

-- SIMILAR TO / REGEXP (Postgres)
WHERE phone ~ '^\d{3}-\d{3}-\d{4}$'

-- Split string to array (Postgres / Snowflake)
SELECT SPLIT_PART('a,b,c', ',', 2); -- 'b'

-- String aggregation
SELECT dept, STRING_AGG(name, ', ') AS members
FROM employees GROUP BY dept;

SELECT
  CURRENT_DATE,          -- 2024-07-11
  CURRENT_TIMESTAMP,     -- 2024-07-11 10:30:00
  NOW(),                 -- Same as CURRENT_TIMESTAMP
  CURRENT_DATE - INTERVAL '7 days';

SELECT
  EXTRACT(YEAR FROM order_date)  AS yr,
  EXTRACT(MONTH FROM order_date) AS mo,
  EXTRACT(DOW FROM order_date)   AS day_of_week,
  DATE_TRUNC('month', order_date) AS month_start,
  DATE_TRUNC('week', order_date)  AS week_start;

SELECT
  order_date + INTERVAL '30 days'    AS due_date,
  DATEDIFF('day', start_dt, end_dt)  AS days_between,
  AGE(NOW(), hire_date)              AS tenure,
  DATE_PART('year', AGE(NOW(), dob)) AS age_years;

-- Postgres
SELECT TO_CHAR(NOW(), 'YYYY-MM-DD HH24:MI:SS');
SELECT TO_DATE('2024-07-11', 'YYYY-MM-DD');

-- MySQL
SELECT DATE_FORMAT(NOW(), '%Y-%m-%d');

SELECT
  COALESCE(phone, email, 'N/A') AS contact,  -- First non-null
  NULLIF(status, 'unknown')     AS clean_status, -- Returns NULL if equal
  IFNULL(discount, 0)           AS safe_discount; -- MySQL shorthand

-- NULL = NULL is NOT true!
WHERE col IS NULL          -- Correct NULL check
WHERE col IS NOT NULL      -- Correct not-null check

-- NULL-safe equality (varies by DB)
WHERE col IS NOT DISTINCT FROM other_col  -- Postgres
WHERE col <=> other_col                   -- MySQL

BEGIN TRANSACTION;

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

-- If all good:
COMMIT;
-- If something fails:
ROLLBACK;

SAVEPOINT sp1;     -- Create savepoint
ROLLBACK TO sp1;   -- Partial rollback

PRIMARY KEY (id)                -- Unique + NOT NULL
UNIQUE (email)                  -- No duplicates
NOT NULL                        -- Cannot be NULL
CHECK (salary > 0)              -- Value validation
FOREIGN KEY (dept_id) REFERENCES departments(id)
  ON DELETE CASCADE
  ON UPDATE SET NULL;

EXPLAIN ANALYZE
SELECT * FROM orders
WHERE customer_id = 42
ORDER BY order_date DESC;

-- Look for: Seq Scan (bad), Index Scan (good),
-- Hash Join vs Nested Loop, Sort cost

-- B-Tree (default): equality, range, ORDER BY
CREATE INDEX idx_date ON orders(order_date);

-- Composite: order matters! Left-to-right
CREATE INDEX idx_comp ON orders(status, order_date);

-- Covering index: all columns in query
CREATE INDEX idx_cover ON orders(customer_id)
  INCLUDE (amount, status);