SQL Commands Guide: SELECT, WHERE, GROUP BY & ORDER BY

Start with the table grain before writing SQL Commands Guide

To find high-value product categories, filter to completed orders, group by category, calculate revenue and keep only categories above a revenue threshold.

• SELECT chooses columns, WHERE filters rows, GROUP BY changes the grain to groups, and ORDER BY controls presentation order.
• Filtering before aggregation answers a different question from filtering after aggregation with HAVING.
• NULL uses three-valued logic, so equality comparisons with NULL do not behave like ordinary values.
• A readable query uses explicit column names and aliases so its output can be interpreted without guessing.

Work through a query with row-count checks

For SQL Commands Guide, predict one visible result before each snippet runs—a row count, shape, statistic, ranking or error pattern—then compare it with the printed checkpoint.

-- SQL Commands Guide · example 1
SELECT category,
       COUNT(*) AS orders,
       SUM(amount) AS revenue
FROM sales
WHERE status = 'complete'
GROUP BY category
HAVING SUM(amount) > 100000
ORDER BY revenue DESC;

SQL Commands Guide checkpoint 1: Run the cell and inspect the printed values before changing anything.

-- SQL Commands Guide · example 2
SELECT COUNT(*) AS rows, COUNT(DISTINCT customer_id) AS customers
FROM practice_orders;

SQL Commands Guide checkpoint 2: rows | customers
30   | 12

-- SQL Commands Guide · example 3
SELECT customer_id, COUNT(*) AS n
FROM practice_orders
GROUP BY customer_id
HAVING COUNT(*) > 1
ORDER BY n DESC;

SQL Commands Guide checkpoint 3: Customers with more than one row are listed before any join or aggregation.

-- SQL Commands Guide · example 4
SELECT region, COUNT(*) AS orders, ROUND(SUM(amount),2) AS revenue
FROM practice_orders
GROUP BY region
ORDER BY revenue DESC;

SQL Commands Guide checkpoint 4: region | orders | revenue
West   | ...    | ...

-- SQL Commands Guide · example 5
WITH x AS (
  SELECT amount FROM practice_orders WHERE amount IS NOT NULL
)
SELECT ROUND(SUM(amount),2) AS detail_total,
       ROUND((SELECT SUM(amount) FROM x),2) AS check_total
FROM x;

SQL Commands Guide checkpoint 5: detail_total and check_total should match exactly.

Test duplicates, NULLs and unmatched keys

Self-check with answers

Practise with a reproducible SQLite-style dataset