SQL for people who already know spreadsheets

Spreadsheet vs Database Table structure

Check your understanding of the differences between a spreadsheet and a database table.

A table's fields define the structure; each record is a row that supplies one value per field.

Check your understanding of records and fields.

CREATE TABLE employees (
    id INTEGER,
    name TEXT,
    hire_date DATE
);

Actual type names and limits vary slightly by database (MySQL, PostgreSQL, SQLite). This table uses standard SQL supported by most.

Check your understanding of SQL data types.

The database checks every value against the column's type before inserting. A mismatch triggers an error, enforcing the contract.

Check your understanding of the fixed-structure contract.

• Primary Key — Every table needs one; use it for the column that represents the row's identity (e.g., user_id, order_id).
• Unique Constraint — Columns that must be unique but are not the row's identifier (e.g., email, passport number).

Check your understanding of primary keys and uniqueness.

1. 1

   Start with SELECT

   Type the keyword SELECT. It tells the database you want to retrieve data.

   Don't forget the capitalisation – SQL is case-insensitive but it's standard to write keywords in uppercase.

   You see `SELECT` at the start of your query.

2. 2

   List the columns you want

   After SELECT, write the column names separated by commas. For example: product_name, price.

   A trailing comma after the last column name will cause an error. Double-check there's no comma after the final column.

   Your query looks like `SELECT product_name, price` (no comma after price).

3. 3

   Add the FROM clause

   Type FROM followed by the table name: FROM products.

   The table name must match exactly (case-sensitive in some databases).

   Your query is now `SELECT product_name, price FROM products;`

4. 4

   (Optional) Rename a column with AS

   To give the output column a different name, add AS after the column: price AS cost.

   The alias cannot be used in a WHERE clause (which runs before SELECT).

   Your query: `SELECT product_name, price AS cost FROM products;`

5. 5

   Execute the query

   Run the query in your SQL tool or the sandbox. You should see the columns you selected, with any aliases applied.

   If you get an error, check for missing commas, misspelled column names, or a missing FROM clause.

   The result shows exactly the columns you asked for, in the order you listed them.

• I have specified the correct table name after FROM.A wrong table name causes an immediate error.
• Column names are spelled exactly as they appear in the table.Misspelled columns cause an error or return unexpected data.
• Commas separate column names; no trailing comma after the last one.A trailing comma is a syntax error in most SQL dialects.
• If I used an alias (AS), I remember it only affects the output, not filtering.Using the alias in WHERE will fail because WHERE runs before SELECT.
• I have ended the statement with a semicolon (;).Many SQL tools require a semicolon to mark the end of the statement.
• I know that FROM is evaluated first, then SELECT.This explains why aliases can't be used earlier and helps debug errors.

Keep this checklist open while you write your first queries.

1. 1

   Start with SELECT

   Type SELECT followed by a space.

   Don't forget the space — `SELECTname` is a syntax error.

   Your cursor is one space after SELECT.

2. 2

   List the first column

   Type name.

   Column names are case-insensitive in most databases, but stick to the exact case used in the table definition to avoid confusion.

   The word `name` appears after SELECT.

3. 3

   Add a comma

   Type a comma ,.

   A trailing comma after the last column is a syntax error in SQL. Don't add one here yet.

   There is a comma after `name`.

4. 4

   List the second column

   Type price.

   Make sure you spelled the column name correctly. `prices` (with an 's') might not exist.

   You have `SELECT name, price`.

5. 5

   Add the FROM clause

   Type a space, then FROM products.

   The table name must match exactly. `product` (singular) might be a different table or not exist.

   Your query reads: `SELECT name, price FROM products`.

6. 6

   Run the query

   Execute the statement. In most tools, you press Ctrl+Enter or click Run.

   If you get an error like "table not found", double-check the table name. If "column not found", check spelling and that the column exists in the table.

   You see a result set with two columns: `name` and `price`.

• SELECT is the first word of the querySQL statements always start with a keyword; SELECT is the one for retrieving data.
• Column names are separated by commasA missing comma merges two column names into one, causing a syntax error.
• No comma after the last columnA trailing comma is a syntax error in SQL.
• FROM names the table (and it exists)Without a valid table, the query fails with "table not found".
• Column names are spelled exactly as in the tableTypos cause "column not found" errors.
• The query ends with a semicolon (optional but recommended)In many SQL tools, a semicolon marks the end of a statement and helps with multiple queries.

Keep this list open while you write your first few queries. Tick each item before you run the query.

1. 1

   Start the SELECT clause

   Type SELECT followed by the column or expression you want to rename. Example: SELECT emp_salary_amt

   Do not use commas inside the column expression; the alias comes after the whole expression.

   You have a valid column name or expression after SELECT.

2. 2

   Add the AS keyword

   After the column, type a space then AS. Example: SELECT emp_salary_amt AS

   Omitting `AS` is allowed in most databases (you can write just `emp_salary_amt Salary`), but always including `AS` makes your intention clear and avoids bugs.

   You see `AS` in your query.

3. 3

   Write the alias name

   Type the alias you want. If it contains spaces or special characters, enclose it in double quotes. Otherwise you can write it plain. Example: Salary or "Total Pay"

   Reserved words (like `ORDER`, `GROUP`, `DATE`) can be used as aliases only when quoted. Better to choose a non‑reserved name.

   The alias matches the column header you expect in the output.

4. 4

   Repeat for more columns (optional)

   Add a comma, then repeat steps 1–3 for the next column. Example: SELECT emp_name AS Name, emp_salary_amt AS Salary

   Each column gets its own `AS`; you cannot alias a whole list.

   All columns you want renamed have `AS` and an alias.

5. 5

   Add the FROM clause

   After all selected columns, type FROM table_name as usual. Example: SELECT emp_name AS Name, emp_salary_amt AS Salary FROM employees;

   Do not put a comma before `FROM`. A common mistake: `SELECT a, b, AS alias FROM` is wrong.

   The query ends with a semicolon and the table name is correct.

6. 6

   Run and verify

   Execute the query. The result set should show the alias as the column header instead of the original name.

   If you see the original column name, you may have omitted `AS` or placed it incorrectly. Check the exact position.

   The output columns match your aliases exactly.

• Each column that needs a rename has an `AS` keyword directly after it.Prevents accidentally giving an alias to the wrong column or forgetting to rename.
• Alias names containing spaces or special characters are enclosed in double quotes.Without quotes, the database splits the alias into multiple tokens and throws an error.
• The alias is not a SQL reserved word (unless quoted).Using an unquoted reserved word can trigger syntax errors or unexpected behaviour.
• Commas separate column definitions, not placed before `FROM`.A misplaced comma breaks the SELECT statement and produces an error.
• The alias appears exactly as intended in the output column headers.Confirms the alias worked; if the original name shows, fix the syntax.

Keep this open while writing queries. Tick each item before running.

Check your understanding of column aliases.

Logical order of execution for a SQL query. Clauses are evaluated top to bottom.

• 

  YouTube

  SQL Order of Execution (Visually Explained) | Understanding How Queries Run

  This short animation walks through the same order of execution visually, reinforcing the concept with a live query example.

  Data with Baraa·4:04·en

  Open

Check your understanding of SQL's logical order of execution.

1. 1

   Start with SELECT and FROM

   SELECT first_name, last_name, salary
   FROM employees;

   This returns all rows. You'll narrow it down.

   If you forget the comma between columns, SQL will throw an error.

   Run the query and see all rows. Count them.

2. 2

   Add a simple WHERE condition

   SELECT first_name, last_name, salary
   FROM employees
   WHERE salary > 50000;

   The > operator works exactly like in a spreadsheet.

   Don't use `salary > 50000` with an equals sign – that's a different operator (`>=`).

   Only rows with salary > 50000 appear. Verify the highest salary is included.

3. 3

   Combine conditions with AND and OR

   SELECT first_name, last_name, salary, department
   FROM employees
   WHERE department = 'Sales' AND salary > 50000;

   Both conditions must be true. Use parentheses to group OR conditions: WHERE (department = 'Sales' OR department = 'Marketing') AND salary > 50000.

   `AND` has higher precedence than `OR`. `WHERE a OR b AND c` means `a OR (b AND c)`, not `(a OR b) AND c`. Always use parentheses when mixing them.

   Check that the result includes only rows that match both parts of the condition.

4. 4

   Use IN and BETWEEN for cleaner filters

   SELECT first_name, last_name, salary
   FROM employees
   WHERE department IN ('Sales', 'Marketing')
     AND salary BETWEEN 40000 AND 60000;

   IN is a shorthand for multiple ORs. BETWEEN is inclusive of both ends.

   `BETWEEN` includes the boundary values. If you want to exclude one, use `>=` and `<` instead.

   The result should include employees in Sales or Marketing with salary 40000, 60000, and everything in between.

5. 5

   Handle NULL values correctly

   SELECT first_name, last_name, salary
   FROM employees
   WHERE salary IS NOT NULL;

   NULL means the value is missing. Comparisons like = NULL or <> NULL always evaluate to unknown (not true), so the row is dropped. Use IS NULL or IS NOT NULL.

   Never write `WHERE salary = NULL`. It will never match any row, even if there are NULLs.

   The query should return rows where salary is not NULL. If you run `WHERE salary IS NULL`, you should see only rows with missing salary.

• WHERE clause is placed after FROM, before ORDER BYSQL syntax requires this order; putting it elsewhere causes a syntax error.
• Comparison operators are used correctly (=, <>, <, >, <=, >=)Using the wrong operator (e.g., => instead of >=) is a common mistake.
• Parentheses are used when mixing AND and ORAND has higher precedence than OR; without parentheses, the logic may not be what you intend.
• IN is used instead of multiple ORs for the same columnIN is shorter and less error-prone; it also works with subqueries later.
• BETWEEN includes both endpointsIf you need an exclusive range, use >= and < instead.
• NULL comparisons use IS NULL / IS NOT NULL, not = NULL= NULL always returns unknown (false in WHERE), so rows with NULL are never returned.

Keep this open while writing your queries. Tick each item after you've checked your code.

Test your understanding of filtering with WHERE.

NULL comparisons yield UNKNOWN; only IS NULL converts it to TRUE

Check your understanding of NULL and three-valued logic with these questions.

1. 1

   Equal to (=)

   Write WHERE color = 'Red'. The single quotes are for text values, of column name. The query returns every row where color is exactly 'Red'. Case matters: 'red' not match unless the database is case-insensitive.

   Forgetting the quotes on a text value causes a syntax error. Numbers don't need quotes: `WHERE price = 10` works.

   Run the query and scan the results for rows that shouldn't be there.

2. 2

   Not equal to (<>)

   Write WHERE color <> 'Red'. This returns rows where color is anything except 'Red'. Some databases also accept !=; stick with <> for portability.

   Rows where color is NULL are NOT returned. NULL is not equal to anything, including itself, so `<>` skips it.

   Check a row you know is NULL in that column — it should be missing from the results.

3. 3

   Less than (<) and greater than (>)

   Write WHERE price < 50 for products under 50. WHERE price > 100 for products over 100. Works on numbers, dates, and even text alphabetically (WHERE name > 'M' starts after M).

   Don't mix types — `WHERE price > 'ten'` causes a database error or unexpected conversion.

   Verify the boundary: does price = 50 appear? (It shouldn't unless you use <=.)

4. 4

   Less than or equal (<=), greater than or equal (>=) — the inclusive cousins

   Use WHERE price <= 50 to include the 50 boundary. Same pattern for >=. These are the exact mapping of spreadsheet 'filter by value between'.

   A common bug: `WHERE price > '10'` treats 10 as text, comparing lexicographically. The string '9' is greater than '10', so you get wrong results. Always use numeric literals for numeric columns.

   Add a row with price exactly 50 and confirm it shows up.

5. 5

   The NULL trap: IS NULL

   To find rows where color is missing (no value), write WHERE color IS NULL. Do NOT write WHERE color = NULL — that always returns no rows because NULL = NULL yields NULL, not TRUE.

   The temptation is to use `= NULL` because it's intuitive. It never works. Use `IS NULL` and `IS NOT NULL` exclusively.

   Write both `= NULL` and `IS NULL` on the same table. The first returns nothing; the second returns the missing rows.

6. 6

   Combining comparisons with AND/OR

   Write WHERE price > 10 AND price < 100 for products between 10 and 100 exclusive. Use OR for alternatives: WHERE color = 'Red' OR color = 'Blue'.

   AND binds tighter than OR. `WHERE color = 'Red' OR color = 'Blue' AND price > 50` gives rows that are Red (any price) or Blue over 50. Use parentheses: `WHERE (color = 'Red' OR color = 'Blue') AND price > 50`.

   Run the query without parentheses, then add them. Compare counts — they differ.

• Every text value in the WHERE clause is wrapped in single quotesMissing quotes cause syntax errors or treat the word as a column name.
• Numbers in comparisons are written without quotesQuoted numbers become strings and comparison changes to alphabetical, breaking numeric logic.
• Null checks use IS NULL or IS NOT NULL, not = NULL= NULL always returns no rows; IS NULL is the only correct syntax.
• Not-equal comparisons use <> not != for portability!= works in most databases, but <> is standard SQL and guaranteed to work everywhere.
• AND/OR expressions that mix conditions have parenthesesWithout parentheses, the default precedence (AND before OR) often produces unintended results.
• The inclusive operators <= and >= are used where the boundary value should be includedUsing < instead of <= with a boundary value silently excludes it, changing the result set.
• The query returns expected rows for a known test case like price = 50 with <= 50Quick sanity check catches off-by-one errors before relying on the query.

Keep this open alongside your SQL tool. Tick each item after you write the query, before you run it.

• 

  YouTube

  SQL Comparison Operators | Oracle SQL fundamentals

  Watch this to see the same SQL comparison operators demonstrated on a live Oracle database with real result sets — the walkthrough makes the NULL trap visually clear.

  DBA Genesis·5:53·from 0:30·en

  Open

1. 1

   Filter with AND

   Write: SELECT * FROM orders WHERE status = 'Shipped' AND amount > 100; SQL returns only rows where both conditions hold. Check the count: it's smaller than either condition alone.

   If you use `=` on a NULL column, the condition is false (NULL is not equal to anything).

   The result should have fewer rows than the table.

2. 2

   Add an OR condition

   Write: SELECT * FROM orders WHERE status = 'Shipped' OR priority = 'High'; Now rows with either condition appear – result is wider.

   The result count should be larger than step 1.

3. 3

   Combine AND and OR correctly

   You want orders that are either Shipped or Pending AND have amount > 50. Write: SELECT * FROM orders WHERE (status = 'Shipped' OR status = 'Pending') AND amount > 50; Without parentheses SQL would interpret as status = 'Shipped' OR (status = 'Pending' AND amount > 50) – wrong set.

   Missing parentheses is the most common bug here. Always add them around OR groups when combined with AND.

   Compare with the unparenthesised version; the results differ.

4. 4

   Use NOT to exclude

   Write: SELECT * FROM orders WHERE NOT status = 'Cancelled'; This is the same as status <> 'Cancelled'. NOT is often clearer, especially with complex conditions.

   `NOT` applies only to the immediate condition. To negate a group, use `NOT (condition1 OR condition2)`.

   Verify no 'Cancelled' rows appear.

5. 5

   Build a combined query

   Now combine everything: orders from Departments 'Sales' or 'Marketing', amount over 100, not from year 2021. SELECT * FROM orders WHERE (department = 'Sales' OR department = 'Marketing') AND amount > 100 AND NOT year = 2021;

   If you omit the parentheses around the OR, you'll get Sales rows from any amount/year.

   Run a simplified query to test each piece: `SELECT * FROM orders WHERE department = 'Sales';` then add conditions one at a time.

• Parentheses around every OR group when combined with ANDPrevents unintended `AND` before `OR` precedence from changing the logic.
• NOT is applied to a single condition, not a list; use parentheses for groups`NOT condition1 OR condition2` is not the same as `NOT (condition1 OR condition2)`.
• Test each condition alone firstConfirms each sub-condition returns expected rows before combining.
• Check for NULL handling – use IS NULL / IS NOT NULL instead of `= NULL`NULL comparisons with `=` or `<>` always yield unknown, removing rows.
• Verify the logic with a small known subset of dataCatching errors early: manually trace a few rows against the WHERE clause.

Keep this list next to you while writing complex filters.

Test your understanding of combining conditions with logical operators.

1. 1

   Start with SELECT and FROM

   Write SELECT first_name, last_name, salary, department FROM employees.

   Don't forget the comma between columns.

   You see the column names in your results panel.

2. 2

   Add the IN filter

   Add WHERE department IN ('Engineering', 'Support') after the FROM clause.

   Use single quotes around text values. Commas between items, no trailing comma.

   Run the query — you should see only Engineering and Support employees.

3. 3

   Add the BETWEEN filter

   Extend the WHERE clause with AND salary BETWEEN 45000 AND 75000.

   BETWEEN is inclusive. If you want to exclude 45000 or 75000, use `>=` and `<=` instead.

   The result set now shows only employees in those departments with salary from 45000 to 75000 inclusive.

4. 4

   Check for NULLs

   If any employee has a NULL salary or NULL department, they are excluded. Verify by adding OR salary IS NULL temporarily to see if any rows appear.

   NULL in an IN list (`IN ('Sales', NULL)`) does NOT match NULL rows. Use `IS NULL` separately.

   Your final query returns only the rows you expect. Count them against a manual check.

• IN list values are inside parentheses, separated by commasMissing parentheses or commas cause syntax errors.
• BETWEEN uses low AND high (inclusive)Swapping the order returns zero rows.
• BETWEEN works with dates as strings in 'YYYY-MM-DD' formatDate format must match the column's stored format.
• NOT IN and NOT BETWEEN invert the conditionUse them to exclude a list or range.
• NULL in an IN list does not match NULL rowsYou must add `OR column IS NULL` to include NULLs.
• Column type matches the values in IN or BETWEENMixing text and numbers can cause implicit conversion issues.

Keep this open while writing your queries.

Check your understanding of IN and BETWEEN.

1. 1

   Write the basic SELECT with WHERE

   Start with SELECT name, email FROM customers WHERE email LIKE.

   Forgetting the `WHERE` clause returns all rows.

   The query runs without errors.

2. 2

   Add the pattern with %

   Complete the clause: WHERE email LIKE '%@gmail.com'. The % before @ means any characters can appear before the domain.

   Leaving off the `%` makes it an exact match – `'@gmail.com'` would only match rows where the email is literally `@gmail.com`.

   Run the query and verify it returns customers with gmail addresses.

3. 3

   Test with a single-character wildcard _

   Suppose you want names that are exactly 4 letters and start with 'J'. Use WHERE name LIKE 'J___' (J followed by three underscores). Each _ stands for one character.

   Using `%` instead of `_` would match names of any length, not exactly 4 characters.

   Check that 'John' matches but 'Jonathan' does not.

4. 4

   Combine wildcards for complex patterns

   To find names containing 'son' anywhere, use WHERE name LIKE '%son%'. This matches 'Johnson', 'Anderson', 'sonya'.

   Patterns are case-sensitive in some databases. If you get no results, try `WHERE LOWER(name) LIKE '%son%'`.

   Verify that 'son' appears in the middle of the returned names.

5. 5

   Handle special characters (optional)

   If you need to match a literal % or _, escape them. In SQLite and PostgreSQL, use LIKE '100\%' ESCAPE '\'. MySQL uses LIKE '100%' by default – no escape needed because % at the end is literal if not followed by anything? Actually, MySQL treats % as wildcard always; use LIKE '100%' with ESCAPE if needed. For this lesson, assume you rarely need it.

   Forgetting to escape can cause unexpected matches.

   Test with a row containing `100%` to confirm only that row matches.

• Pattern uses % for any sequence, _ for exactly one characterUsing the wrong wildcard gives wrong results – % is too broad, _ is too narrow.
• No trailing spaces in the pattern stringA space after the last character changes the match; 'Smith ' is not the same as 'Smith'.
• Case sensitivity accounted for – use LOWER() if unsureDatabases differ; forcing lowercase prevents silent failures.
• Tested with a small sample firstA pattern that looks right might match too many or too few rows; quick test catches it.

Keep this list open while you write your LIKE queries.

Test your understanding of LIKE patterns.

1. 1

   Write the SELECT statement

   SELECT product_id, name, price FROM products

   Don't forget the comma between columns.

   Run the query. It should return rows in some order (probably insertion order, but not guaranteed).

2. 2

   Add ORDER BY with one column

   SELECT product_id, name, price FROM products ORDER BY price

   If you omit `ASC`, it's still ascending. That's usually what you want.

   Prices are now sorted from lowest to highest.

3. 3

   Switch to descending order

   SELECT product_id, name, price FROM products ORDER BY price DESC

   `DESC` applies only to the column it follows. If you have multiple columns, each needs its own `DESC`.

   Prices are now sorted from highest to lowest.

4. 4

   Add a secondary sort column

   SELECT product_id, name, price FROM products ORDER BY price DESC, name

   The order of columns in `ORDER BY` matters. The first column is the primary sort. If two rows have the same price, they are sorted by the second column (name, ascending).

   Rows with the same price are now sorted alphabetically by name.

5. 5

   Use a positional reference

   SELECT product_id, name, price FROM products ORDER BY 2

   Positional references are fragile. If you later change the SELECT list to `SELECT name, price, product_id`, then `ORDER BY 2` now sorts by price instead of name.

   Rows are sorted alphabetically by name (the second column in the SELECT list).

• The query has an ORDER BY clauseWithout it, the order is undefined and may change between runs.
• Primary sort column is first in the ORDER BY listThe database sorts by the first column first, then by subsequent columns for ties.
• Each column that should be descending has DESCDESC applies only to the column it immediately follows; other columns default to ascending.
• Positional references match the SELECT list orderIf you change the SELECT list, ORDER BY 2 may sort by a different column.
• You are not relying on insertion order for any business logicInsertion order is not preserved; only ORDER BY guarantees a specific order.

Keep this checklist open when you write ORDER BY queries.

Test your understanding of ORDER BY.

1. 1

   Write the base SELECT

   Start with SELECT last_name, salary FROM employees; and run it. You'll see rows in no particular order.

   Don't add ORDER BY yet — see the unsorted output first so you can compare.

   You see all rows, but the order looks random.

2. 2

   Add ORDER BY on the salary column

   Append ORDER BY salary to the query. The full query: SELECT last_name, salary FROM employees ORDER BY salary;

   Put ORDER BY after WHERE, not before. If you have a WHERE clause, ORDER BY must come after it.

   Now the lowest salary appears first.

3. 3

   Reverse the order with DESC

   Change to ORDER BY salary DESC and run again.

   Don't forget the space between the column name and DESC. `salaryDESC` is an error.

   The highest salary appears first.

4. 4

   Verify the sort

   Check the first row and the last row. Are they what you expect? If you have ties (same salary), the order among those ties is undefined unless you add a second column — that's for the next lesson.

   If you see the same order as before, double-check you added DESC.

   First row has the highest salary, last row the lowest.

• ORDER BY appears after WHERE (if any) and before LIMIT.Wrong order causes a syntax error or unexpected results.
• Column name is spelled exactly as in the table (case-sensitive in some databases).A typo gives an 'unknown column' error.
• I used DESC when I want descending order; otherwise ascending is fine.Default ASC is often what you want, but forgetting DESC gives the opposite order.
• I ran the query and checked the first and last rows match my expectation.Catches off-by-one logic or wrong column reference.

Keep this open while you write your queries.

Check your understanding of single-column sort.

1. 1

   Start your SELECT

   Write SELECT column1, column2, ... FROM table as usual.

   Make sure you include all columns you want to see in the result.

   The query runs and returns rows (even if unsorted).

2. 2

   Add ORDER BY

   Type ORDER BY after the FROM clause.

   Don't put a comma before ORDER BY; it's a separate clause.

   No syntax error.

3. 3

   Choose the primary sort column

   Write the first column name (or its position) right after ORDER BY. This is the primary sort.

   If you use a column position, count from 1 in the SELECT list.

   The column exists in the table or SELECT list.

4. 4

   Set direction for the primary column

   Add ASC (ascending, default) or DESC (descending) after the column name.

   If you omit, ASC is assumed. Be explicit if clarity matters.

   The direction keyword is spelled correctly.

5. 5

   Add a comma and the next column

   Type a comma, then the second column name (or position). This is the secondary sort.

   A missing comma will cause a syntax error.

   The comma is there, and the column name is valid.

6. 6

   Set direction for the secondary column

   Add ASC or DESC for the second column. It can be different from the first.

   DESC does NOT carry over from the previous column; each column needs its own direction.

   The direction is what you intended.

7. 7

   Add more columns (optional)

   Repeat steps 5–6 for any additional sort columns.

   Each extra column adds a level of sorting; don't overcomplicate.

   The ORDER BY list has all the columns you need.

8. 8

   Run the query

   Execute the query and examine the result.

   If the order looks wrong, check the column order in ORDER BY – the first column is the most significant.

   Rows are sorted as expected: primary column groups together, secondary column sorts within those groups.

• Primary sort column is first in ORDER BY.The first column determines the main grouping; subsequent columns only matter when the first column has ties.
• Each column has its own ASC or DESC keyword.DESC does not carry over – forgetting to add DESC to the second column will sort it ascending by default.
• Columns are separated by commas.A missing comma causes a syntax error or unexpected sort.
• Column names or positions are used consistently.Positions refer to the SELECT list order; mixing names and positions is allowed but can be confusing.
• The order of rows within groups of equal primary values follows the secondary sort.This ensures predictable ordering within each group.
• ASC is the default – you can omit it, but including it makes your intent clear.Explicit ASC avoids ambiguity when reading the query later.
• Test with a small dataset to verify the sort order.A quick check prevents misinterpretation of the result.

Keep this list open while writing your queries.

• 

  YouTube

  SQL ORDER BY With Multiple Columns

  Watch this short video to see a live demonstration of multi-column sorting in SQL, reinforcing the order-of-columns concept.

  Jamie King·3:50·en

  Open

Test your understanding of multi-column sorting.

SELECT product, category, price
FROM products
ORDER BY ;

SELECT product, category, price
FROM products
ORDER BY price DESC, category;

• Column name — Shared scripts, reports, and any query where clarity matters more than keystrokes
• Positional reference — Quick ad-hoc queries where you're the only person who will see the SQL

Check your understanding of NULL ordering and positional references.

The pipeline a GROUP BY query runs through, and the row count at each stage.

1. 1

   Name the group column and the value column first

   Decide which column becomes the group and which becomes the value. Here the group is region and the value is amount. In spreadsheet terms you just chose the Rows field and the Values field. Write both down before touching the keyboard.

   Guessing the group column produces a query that runs and quietly answers the wrong question — no error message will save you.

   You can say it out loud: "one output row per region, and inside each output row the sum of amount."

2. 2

   Run the aggregate by itself

   Run SELECT SUM(amount) FROM sales; — it returns 915. You are proving the aggregate works before you split it into groups.

   One number comes back: 915, the total of all eight amounts.

3. 3

   Add the group column and GROUP BY

   Put region into SELECT and name it in GROUP BY:

   SELECT region, SUM(amount) FROM sales GROUP BY region;

   Eight rows collapse into three: North 400, South 280, East 235.

   Drop GROUP BY and the database errors: column region must appear in the GROUP BY clause or be used in an aggregate function. That is the non-aggregated column rule refusing to guess which region's value to show.

   The number of returned rows equals the number of distinct regions — three. And 400 + 280 + 235 = 915, matching step 2.

4. 4

   Add the other aggregates to the same query

   Replace SUM(amount) with the summaries you want, one aggregate per column:

   SELECT region, COUNT(*) AS orders, AVG(amount) AS avg_amount, MIN(amount) AS smallest, MAX(amount) AS largest FROM sales GROUP BY region;

   For North: 3 orders, average 133.3, smallest 80, largest 200.

   Keep every aggregate inside SELECT. A WHERE clause may not use an aggregate — filtering groups is a separate step that comes later in this course.

   COUNT(*) matches the raw rows: North 3, South 3, East 2.

5. 5

   Filter rows with WHERE, placed before GROUP BY

   The boss changes the question: totals by region, orders of 100 or more only. Write the filter before GROUP BY:

   SELECT region, SUM(amount) FROM sales WHERE amount >= 100 GROUP BY region;

   Now North 320, South 150, East 175.

   WHERE after GROUP BY is a syntax error; an aggregate inside WHERE is rejected too, because WHERE runs before any grouping exists.

   The totals now add to 645, not 915 — four rows passed the filter and the rest never formed a group.

6. 6

   Sort the groups with ORDER BY, after GROUP BY

   Finish the answer by sorting on the aggregate. Give it an alias and use it:

   SELECT region, SUM(amount) AS total FROM sales WHERE amount >= 100 GROUP BY region ORDER BY total DESC;

   Returns North 320, East 175, South 150.

   ORDER BY runs after the groups are built, so it may reference the alias total — but the same alias would be illegal in WHERE. Position in the sentence is position in the pipeline.

   The first row holds the highest total: 320 before 175 before 150, not an alphabetical region order.

7. 7

   Verify one group by hand

   Pick a group — East. Its raw amounts are 175 and 60. With the filter amount >= 100, only 175 survives, which is what the step-5 query shows. Re-run the query without the filter and confirm East shows 235, both raw rows summed.

   If your hand sum disagrees with the query, the filter or the grouping is wrong. Fix step 5 before building on it.

   The query result and your independent sum agree.

• Group column and value column are named before any SQL is typed.Prevents a query that runs but answers the wrong question — the failure with no error.
• Every non-aggregated column in SELECT also appears in GROUP BY.Gets rid of the must-appear-in-GROUP-BY error and keeps each output row unambiguous.
• WHERE sits between FROM and GROUP BY.WHERE filters raw rows; any other position is a syntax error.
• No aggregate appears inside WHERE.WHERE runs before any aggregate exists, so the database rejects it.
• Every summary you want is written as an aggregate in SELECT.A bare column in SELECT would have to be added to GROUP BY and would change the groups.
• ORDER BY is the last clause and sorts by the aggregate alias.Only after grouping can the database order by a summary value.
• The result has one row per group, not one per raw row.Extra rows mean a column is missing from GROUP BY.
• One group's total matches a hand sum of its raw rows.Catches a wrong filter or wrong group before the result ships.

Keep this next to your editor and tick each item only after you have verified it, not because you intend to.

Three checks after the walkthrough — you can answer these without a database.

Keep this table open when you write your first aggregate queries. The most common mistake is assuming `AVG` treats NULLs as zero.

Practice the difference between the five aggregate functions.

1. 1

   Decide the grouping columns

   Each group is defined by the unique combination of values in the columns you name. Here, we want one row per category, so the grouping column is category.

   Picking too many columns creates too many groups — you might get a row for every combination instead of the summary you intended.

   Write down the columns that will appear in the SELECT list outside aggregates; they must be here.

2. 2

   Choose the aggregate functions

   We need total sales per category, so we use SUM(amount) as the total. We'll also add COUNT(*) for number of orders in each category.

   Don't forget `COUNT(*)` counts rows, `COUNT(column)` counts non-null values in that column. They are not the same.

3. 3

   Write the SELECT clause

   Write SELECT category, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM orders

   If you include a column like `order_id` without an aggregate, you must also add it to GROUP BY — but that would make each row unique, defeating the purpose.

   Verify that every column in SELECT that is not wrapped in an aggregate (MIN, MAX, SUM, AVG, COUNT) is in the list you wrote in step 1.

4. 4

   Add the GROUP BY clause

   Write GROUP BY category after the FROM clause. This tells SQLite to collapse rows with the same category into one row per category.

   The GROUP BY clause must come after WHERE but before ORDER BY or LIMIT. Putting it in the wrong order causes a syntax error.

   Run the query: `SELECT category, SUM(amount) AS total_sales, COUNT(*) AS order_count FROM orders GROUP BY category;` — you should see one row per category.

5. 5

   Filter groups with HAVING (optional)

   If you want only categories with total sales over $1000, add HAVING total_sales > 1000 after the GROUP BY. HAVING is like WHERE for groups — it filters after aggregation.

   You cannot use HAVING before GROUP BY, and you cannot use WHERE to filter an aggregate — WHERE runs before grouping, so `WHERE SUM(amount) > 1000` is invalid.

   Add `HAVING total_sales > 1000` and run again. Only groups meeting the condition appear.

6. 6

   Order the results

   Add ORDER BY total_sales DESC to show the highest-selling category first.

   You can ORDER BY an alias (like `total_sales`) because ORDER BY is evaluated after SELECT. But this does not work in all databases — SQLite allows it.

   Run the final query and confirm the output is sorted descending by total sales.

• Every non‑aggregated column in SELECT is listed in GROUP BYPrevents the 'not a GROUP BY expression' error or a wrong result from an arbitrary row.
• Aggregate functions are applied to the correct columnSUM(price) vs SUM(quantity) produce very different numbers; double-check the column name.
• GROUP BY clause is placed after WHERE and before ORDER BY / LIMITWrong order causes a syntax error (SQLite) or changes the result set.
• HAVING references an aggregate or a grouped column, not a raw column from the tableHAVING filters after grouping; using a raw column that is not in GROUP BY is non‑standard and may give unexpected results.
• ORDER BY uses an alias or an aggregate expression (not a raw column) if neededRaw columns in ORDER BY after GROUP BY are allowed only if they are in GROUP BY; using an alias is clearer.

Keep this checklist open when writing your first few GROUP BY queries.

Test your understanding of the GROUP BY clause.

SELECT product, SUM(amount) FROM orders GROUP BY category;

Why a non-aggregated column must be in GROUP BY.

Check yourself.

• WHERE — Excluding irrelevant rows early, before any grouping happens
• HAVING — Keeping only groups that satisfy a condition on their summary values

Check your understanding of HAVING vs. WHERE.