SQL commands to master

SELECT + FROM

SELECT
  order_id,
  customer_id,
  amount
FROM orders;

Choose exactly which columns you want from which tables; every query starts here.

WHERE

SELECT
  order_id,
  customer_id,
  amount
FROM orders
WHERE status = 'Completed'
  AND amount > 2000;

Filters rows by conditions using > , < , AND , OR, IN, BETWEEN, LIKE

JOIN (INNER + LEFT)

-- Customers with at least one order (INNER)
SELECT c.customer_id, c.name
FROM customers c
JOIN orders o
  ON c.customer_id = o.customer_id;

-- Customers who never ordered (LEFT + IS NULL)
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o
  ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

INNER for "only matched"; LEFT for "all from left, even if no match", with NULL logic.

GROUP BY

SELECT
  customer_id,
  COUNT(*) AS order_count,
  SUM(amount) AS total_spend
FROM orders
GROUP BY customer_id;

Rolls many rows into one row per group (per customer, per month, per product).

HAVING

SELECT
  customer-id,
  COUNT(*) AS order-count
FROM orders
GROUP BY customer_id
HAVING > 10;

Filters groups after aggregation; use it when you need conditions on COUNT / SUM etc.

Aggregates (COUNT, SUM, AVG, MIN, MAX)

SELECT
  COUNT(*) AS total_orders,

  SUM(amount) AS total-revenue,
  AVG(amount) AS avg_order-value,
  MIN(amount) AS min_order_value,
  MAX(amount) AS max_order_value
FROM orders;

Core KPI tools; almost every analytics query uses at least one aggregate.

Window functions (OVER)

SELECT
  account-id,
  txn-date,
  amount,
  SUM(amount) OVER (
    PARTITION BY account-id
    ORDER BY txn_date
   AS running_balance
FROM transactions;

Lets you compute running totals, ranks, and "value vs group average" without collapsing rows.

WITH (CTE) + subqueries

WITH dept-avg AS (
  SELECT
    department,
    AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
)
SELECT
  e.id,
  e.name,
  e.department,
  e.salary
FROM employees e
JOIN dept_avg d
  ON e.department = d.department
WHERE e.salary > d.avg_salary;

CTE/subquery breaks complex logic into steps; used everywhere in reporting and interview questions.