Checkpoint: Querying Data

You've learned SELECT DISTINCT, LIMIT, aliases, LIKE, IN/BETWEEN, CASE and NULL handling — let's combine them. This checkpoint has no new syntax. Instead, you'll weave these tools together into the kind of real query you'd actually write on the job, then test yourself with a short quiz.

Learn Checkpoint: Querying Data in our free SQL course — a beginner-friendly interactive lesson with runnable queries, a practice exercise and a quick recall.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

Removes duplicate rows; compares the whole selected row.

Caps and paginates results; pair with ORDER BY.

Friendly column headings and short table nicknames.

% (many chars) and _ (one char) for pattern matching.

List membership and inclusive ranges; mind AND/OR precedence.

If/then/else logic; first matching branch wins.

IS NULL , COALESCE , NULLIF ; never use = NULL .

Every challenge and quiz question runs against this orders table. Notice the missing region and the source codes you'll translate with CASE.

(NULL) = no region recorded. source codes: web, app, phone.

This single query touches almost everything from this section. Read the numbered brief in the starter code, write your attempt, then reveal the worked solution to compare.

Key insight: we filtered on the raw region column in WHERE (the COALESCE alias isn't available there), and presented the cleaned "Region" only in SELECT. That's why the NULL-region row is excluded rather than shown as 'Unknown'.

Answer each before revealing. All questions use the orders table above.

3 — EU, US, and NULL. DISTINCT collapses the multiple EU and US rows, and all the NULLs collapse into a single NULL row.

Jonas (180), Emma (150, inclusive low), Sara (260), and Mia (310, inclusive high). 4 rows — BETWEEN includes both endpoints.

Comparing to NULL with = always yields UNKNOWN, never TRUE. Use WHERE region IS NULL — which would return Jonas's order.

'Low' — 260 fails the >= 300 branch, so the ELSE fires.

No — WHERE runs before SELECT assigns aliases. Repeat the CASE expression in WHERE, or wrap the query in a subquery/CTE and filter the outer level.

Shipped orders sorted high-to-low are Omar (540), Amélie (420), Jonas (180), Emma (150), Lucas (95). The top 2 are Omar (540) and Amélie (420) .

Practice quiz

How many rows does 'SELECT DISTINCT region FROM orders;' return for the sample table (regions EU, US, and some NULLs)?

Answer: 3. Three: EU, US, and a single NULL. DISTINCT collapses the repeated EU and US values, and all NULLs collapse into one NULL row.

Which rows match 'WHERE amount BETWEEN 150 AND 310'?

Rows from 150 up to and including 310, since BETWEEN is inclusive on both ends
Only rows strictly between 150 and 310
Only the row equal to 150
No rows, because BETWEEN needs three values

Answer: Rows from 150 up to and including 310, since BETWEEN is inclusive on both ends. BETWEEN is inclusive of both endpoints, so 150 and 310 both qualify along with everything in between.

Why does 'WHERE region = NULL' return no rows, and what should you write instead?

It works fine; nothing is wrong with it
You should write WHERE region == NULL
NULL can only be matched with LIKE
Comparing to NULL with = yields UNKNOWN, never TRUE; use WHERE region IS NULL

Answer: Comparing to NULL with = yields UNKNOWN, never TRUE; use WHERE region IS NULL. Any = comparison with NULL evaluates to UNKNOWN, so the row is never returned. Use IS NULL to test for missing values.

Can you reference a SELECT alias (e.g. a CASE aliased as 'channel') inside the WHERE clause?

Yes, aliases are available everywhere
No, because WHERE is evaluated before SELECT assigns aliases
Only if you use single quotes
Only in ORDER BY and WHERE

Answer: No, because WHERE is evaluated before SELECT assigns aliases. WHERE runs before SELECT, so its aliases don't exist yet. Repeat the expression in WHERE, or wrap the query in a subquery/CTE and filter the outer level.

In which clause can you use a SELECT alias defined with AS?

ORDER BY
WHERE
FROM
Both WHERE and FROM

Answer: ORDER BY. ORDER BY runs after SELECT, so a SELECT alias is available there. WHERE runs first, so it cannot see the alias.

What does 'COALESCE(region, 'Unknown')' return for a row whose region is NULL?

NULL
An error
'Unknown'
The first non-null value of region

Answer: 'Unknown'. COALESCE returns the first non-NULL argument, so a NULL region becomes the fallback 'Unknown'.

Which clause order is correct?

FROM ... SELECT ... WHERE ... LIMIT ... ORDER BY
SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT
SELECT ... WHERE ... FROM ... ORDER BY ... LIMIT
WHERE ... SELECT ... FROM ... LIMIT ... ORDER BY

Answer: SELECT ... FROM ... WHERE ... ORDER BY ... LIMIT. The written order is SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT — even though the engine logically processes FROM and WHERE before SELECT.

In the 'orders' table, what makes a row with a NULL region get excluded by 'region IN ('EU', 'US')'?

IN automatically includes NULLs
It causes a syntax error
IN only works with numbers
NULL IN (...) evaluates to UNKNOWN, so the row is filtered out

Answer: NULL IN (...) evaluates to UNKNOWN, so the row is filtered out. Comparing a NULL against the IN list yields UNKNOWN rather than TRUE, so the NULL-region row is excluded rather than matched.

What does 'ORDER BY amount DESC LIMIT 2' return after the rows are sorted?

The two rows with the smallest amount
The two rows with the largest amount
A random pair of rows
Every row, just reordered

Answer: The two rows with the largest amount. ORDER BY amount DESC sorts highest first, and LIMIT 2 keeps the top two — the two largest amounts.

When should you use CASE versus COALESCE?

They are interchangeable in every situation
COALESCE for multi-branch logic; CASE only for NULLs
CASE for multi-branch conditional logic; COALESCE specifically to replace NULLs with a fallback
CASE only works in WHERE; COALESCE only in SELECT

Answer: CASE for multi-branch conditional logic; COALESCE specifically to replace NULLs with a fallback. CASE handles general multi-branch conditional logic, while COALESCE is a focused shortcut for replacing NULLs with the first non-NULL fallback value.