SQL Cheat Sheet — Every Command on One Page

-- Select specific columns
SELECT customer_id, name, city FROM customers;

-- Select all columns
SELECT * FROM orders;

-- Select with alias
SELECT customer_id AS id, total_amount AS revenue FROM orders;

-- Select distinct values
SELECT DISTINCT city FROM customers;

-- Basic comparison
SELECT * FROM orders WHERE amount > 1000;

-- Multiple conditions
SELECT * FROM orders WHERE city = 'Mumbai' AND status = 'delivered';

-- OR condition
SELECT * FROM orders WHERE city = 'Mumbai' OR city = 'Delhi';

-- IN operator
SELECT * FROM orders WHERE city IN ('Mumbai', 'Delhi', 'Bangalore');

-- NOT IN
SELECT * FROM orders WHERE status NOT IN ('cancelled', 'returned');

-- BETWEEN
SELECT * FROM orders WHERE order_date BETWEEN '2026-01-01' AND '2026-03-31';

-- LIKE pattern matching
SELECT * FROM customers WHERE email LIKE '%@gmail.com';
SELECT * FROM customers WHERE name LIKE 'A%';  -- starts with A

-- IS NULL / IS NOT NULL
SELECT * FROM orders WHERE delivery_time IS NULL;
SELECT * FROM customers WHERE phone IS NOT NULL;

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

-- LEFT JOIN: all rows from left table + matches from right
SELECT c.name, o.order_id, o.amount
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id;

-- RIGHT JOIN: all rows from right table + matches from left
SELECT c.name, o.order_id
FROM customers c
RIGHT JOIN orders o ON c.customer_id = o.customer_id;

-- FULL OUTER JOIN: all rows from both tables
SELECT c.name, o.order_id
FROM customers c
FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

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

-- Multiple joins
SELECT o.order_id, c.name, p.product_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
JOIN products p ON o.product_id = p.product_id;

SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.customer_id IS NULL;
-- Finds customers who never placed an order

-- Count rows
SELECT COUNT(*) FROM orders;
SELECT COUNT(DISTINCT customer_id) FROM orders;

-- Sum and average
SELECT SUM(amount) AS total_revenue FROM orders;
SELECT AVG(amount) AS avg_order_value FROM orders;

-- Min and max
SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;

-- Basic grouping
SELECT city, COUNT(*) AS order_count
FROM orders
GROUP BY city;

-- Multiple aggregates
SELECT city,
  COUNT(*) AS order_count,
  SUM(amount) AS total_revenue,
  AVG(amount) AS avg_order_value
FROM orders
GROUP BY city;

-- Multiple columns in GROUP BY
SELECT city, status, COUNT(*) AS count
FROM orders
GROUP BY city, status;

-- HAVING: filter after grouping
SELECT city, SUM(amount) AS revenue
FROM orders
GROUP BY city
HAVING SUM(amount) > 100000;

-- Correct: WHERE before, HAVING after
SELECT city, SUM(amount) AS revenue
FROM orders
WHERE order_date >= '2026-01-01'  -- filter rows first
GROUP BY city
HAVING SUM(amount) > 50000;  -- filter groups after

-- ROW_NUMBER: unique sequential number
SELECT customer_id, total_orders,
  ROW_NUMBER() OVER (ORDER BY total_orders DESC) AS row_num
FROM customer_summary;

-- RANK: ties get same rank, then skips
SELECT customer_id, total_orders,
  RANK() OVER (ORDER BY total_orders DESC) AS rank_num
FROM customer_summary;

-- DENSE_RANK: ties get same rank, no skipping
SELECT customer_id, total_orders,
  DENSE_RANK() OVER (ORDER BY total_orders DESC) AS dense_rank_num
FROM customer_summary;

-- PARTITION BY: rank within groups
SELECT order_id, city, amount,
  RANK() OVER (PARTITION BY city ORDER BY amount DESC) AS rank_in_city
FROM orders;

-- LAG: previous row's value
SELECT month, revenue,
  LAG(revenue, 1) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_sales;

-- LEAD: next row's value
SELECT month, revenue,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month_revenue
FROM monthly_sales;

-- Calculate month-over-month growth
SELECT month, revenue,
  ROUND((revenue - LAG(revenue) OVER (ORDER BY month)) / LAG(revenue) OVER (ORDER BY month) * 100, 2) AS growth_pct
FROM monthly_sales;

-- Running total
SELECT order_date, daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date) AS cumulative_revenue
FROM daily_sales;

-- 7-day rolling average
SELECT order_date, daily_revenue,
  AVG(daily_revenue) OVER (ORDER BY order_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7day_avg
FROM daily_sales;

-- Subquery in WHERE
SELECT * FROM orders
WHERE customer_id IN (
  SELECT customer_id FROM customers WHERE city = 'Mumbai'
);

-- Subquery in FROM (derived table)
SELECT city, avg_amount
FROM (
  SELECT city, AVG(amount) AS avg_amount
  FROM orders
  GROUP BY city
) city_avg
WHERE avg_amount > 500;

-- Correlated subquery
SELECT customer_id, name,
  (SELECT COUNT(*) FROM orders WHERE orders.customer_id = customers.customer_id) AS order_count
FROM customers;

-- Single CTE
WITH high_value_customers AS (
  SELECT customer_id, SUM(amount) AS lifetime_value
  FROM orders
  GROUP BY customer_id
  HAVING SUM(amount) > 10000
)
SELECT c.name, hvc.lifetime_value
FROM high_value_customers hvc
JOIN customers c ON hvc.customer_id = c.customer_id;

-- Multiple CTEs
WITH monthly_sales AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders
  GROUP BY 1
),
growth AS (
  SELECT month, revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_revenue
  FROM monthly_sales
)
SELECT month, revenue,
  ROUND((revenue - prev_revenue) / prev_revenue * 100, 2) AS growth_pct
FROM growth;