SQL Cheatsheet

-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT name, email FROM users;

-- Alias columns
SELECT name AS full_name,
       email AS contact
FROM users;

-- Distinct values (remove duplicates)
SELECT DISTINCT city FROM users;

-- Limit results
SELECT * FROM users LIMIT 10;

-- Skip rows (pagination)
SELECT * FROM users LIMIT 10 OFFSET 20;

-- Calculated columns
SELECT name,
       price * quantity AS total
FROM orders;

-- WHERE clause
SELECT * FROM users WHERE age > 25;
SELECT * FROM users WHERE city = 'NYC';

-- Multiple conditions
SELECT * FROM users
WHERE age > 25 AND city = 'NYC';

SELECT * FROM users
WHERE city = 'NYC' OR city = 'LA';

-- IN operator (match any in list)
SELECT * FROM users
WHERE city IN ('NYC', 'LA', 'Chicago');

-- BETWEEN (inclusive range)
SELECT * FROM orders
WHERE total BETWEEN 100 AND 500;

-- LIKE (pattern matching)
SELECT * FROM users WHERE name LIKE 'A%';    -- Starts with A
SELECT * FROM users WHERE email LIKE '%@gmail.com'; -- Ends with

-- NULL checks
SELECT * FROM users WHERE phone IS NULL;
SELECT * FROM users WHERE phone IS NOT NULL;

-- ORDER BY
SELECT * FROM users ORDER BY name ASC;
SELECT * FROM users ORDER BY age DESC, name ASC;

-- INNER JOIN (only matching rows)
SELECT users.name, orders.total
FROM users
INNER JOIN orders ON users.id = orders.user_id;

-- LEFT JOIN (all from left + matching from right)
SELECT users.name, orders.total
FROM users
LEFT JOIN orders ON users.id = orders.user_id;

-- RIGHT JOIN (all from right + matching from left)
SELECT users.name, orders.total
FROM users
RIGHT JOIN orders ON users.id = orders.user_id;

-- FULL OUTER JOIN (all rows from both)
SELECT users.name, orders.total
FROM users
FULL OUTER JOIN orders ON users.id = orders.user_id;

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

-- Multiple JOINs
SELECT u.name, o.total, p.name AS product
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) FROM users;
SELECT COUNT(DISTINCT city) FROM users;
SELECT SUM(total) FROM orders;
SELECT AVG(age) FROM users;
SELECT MIN(price), MAX(price) FROM products;

-- GROUP BY
SELECT city, COUNT(*) AS user_count
FROM users
GROUP BY city;

SELECT category, AVG(price) AS avg_price
FROM products
GROUP BY category;

-- HAVING (filter groups, not rows)
SELECT city, COUNT(*) AS cnt
FROM users
GROUP BY city
HAVING COUNT(*) > 10;

-- GROUP BY with ORDER BY
SELECT category, SUM(total) AS revenue
FROM orders
GROUP BY category
ORDER BY revenue DESC;

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

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

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

-- CTE (Common Table Expression)
WITH high_spenders AS (
  SELECT user_id, SUM(total) AS lifetime
  FROM orders
  GROUP BY user_id
  HAVING SUM(total) > 5000
)
SELECT u.name, hs.lifetime
FROM users u
JOIN high_spenders hs ON u.id = hs.user_id;

-- Multiple CTEs
WITH monthly AS (
  SELECT DATE_TRUNC('month', date) AS month,
         SUM(total) AS revenue
  FROM orders GROUP BY 1
),
growth AS (
  SELECT month, revenue,
         LAG(revenue) OVER (ORDER BY month) AS prev
  FROM monthly
)
SELECT * FROM growth;

-- INSERT single row
INSERT INTO users (name, email, age)
VALUES ('Alice', 'alice@example.com', 30);

-- INSERT multiple rows
INSERT INTO users (name, email, age) VALUES
  ('Bob', 'bob@example.com', 25),
  ('Carol', 'carol@example.com', 28);

-- INSERT from SELECT
INSERT INTO archive_users
SELECT * FROM users WHERE is_active = false;

-- UPDATE rows
UPDATE users
SET age = 31
WHERE name = 'Alice';

-- UPDATE multiple columns
UPDATE products
SET price = price * 1.1,
    updated_at = NOW()
WHERE category = 'electronics';

-- DELETE rows
DELETE FROM users WHERE id = 5;

-- DELETE with subquery
DELETE FROM orders
WHERE user_id IN (
  SELECT id FROM users WHERE is_banned = true
);

-- CREATE TABLE
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE NOT NULL,
  age INTEGER,
  created_at TIMESTAMP DEFAULT NOW()
);

-- ALTER TABLE
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;

-- DROP TABLE
DROP TABLE IF EXISTS old_users;

-- TRUNCATE (delete all rows, keep structure)
TRUNCATE TABLE temp_data;

-- Common data types
-- INTEGER, BIGINT, SERIAL (auto-increment)
-- VARCHAR(n), TEXT, CHAR(n)
-- BOOLEAN
-- DATE, TIMESTAMP, TIME
-- DECIMAL(p,s), NUMERIC, FLOAT
-- JSON, JSONB (PostgreSQL)

-- Create index
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_date ON orders(created_at DESC);

-- Unique index
CREATE UNIQUE INDEX idx_users_username
ON users(username);

-- Composite index
CREATE INDEX idx_orders_user_date
ON orders(user_id, created_at);

-- Drop index
DROP INDEX idx_users_email;

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

-- Add constraint to existing table
ALTER TABLE products
ADD CONSTRAINT positive_price CHECK (price > 0);

-- Foreign key actions
-- ON DELETE CASCADE  (delete child rows)
-- ON DELETE SET NULL (set FK to null)
-- ON DELETE RESTRICT (prevent deletion)

-- ROW_NUMBER (assign sequential numbers)
SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank
FROM employees;

-- RANK and DENSE_RANK
SELECT name, department, salary,
  RANK() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS dept_rank
FROM employees;

-- Running total
SELECT date, amount,
  SUM(amount) OVER (
    ORDER BY date
    ROWS UNBOUNDED PRECEDING
  ) AS running_total
FROM transactions;

-- LAG and LEAD (access previous/next rows)
SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue) OVER (ORDER BY month) AS growth
FROM monthly_sales;

-- NTILE (divide into buckets)
SELECT name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

-- CASE expression (conditional logic)
SELECT name,
  CASE
    WHEN age < 18 THEN 'Minor'
    WHEN age < 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

-- COALESCE (first non-null value)
SELECT COALESCE(nickname, name, 'Anonymous') AS display
FROM users;

-- String functions
SELECT UPPER(name), LOWER(email),
       LENGTH(name), TRIM(name),
       CONCAT(first, ' ', last) AS full_name
FROM users;

-- Date functions
SELECT NOW(), CURRENT_DATE,
       DATE_TRUNC('month', created_at),
       EXTRACT(YEAR FROM created_at),
       created_at + INTERVAL '30 days'
FROM orders;

-- UNION (combine result sets)
SELECT name, 'customer' AS type FROM customers
UNION ALL
SELECT name, 'employee' AS type FROM employees;

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