SQL: Filtering, Sorting & NULL

Chapter roadmap

Return exactly the rows you need using predicates, ranges, pattern matching, NULL rules, ordering, and limited results. Work through the concepts in order, type every example yourself, and keep a short debugging log that records predictions, errors, fixes, and what each fix taught you.

Learning objectives

Write WHERE predicates
Combine conditions correctly
Handle NULL explicitly
Sort and limit results predictably

02.1

WHERE and comparison predicates

WHERE keeps rows whose predicate evaluates to true. Use =, <>, <, <=, >, and >= for comparisons. Text and date literals use quotes, while numbers normally do not. Filters should reflect the question in plain language before being translated into SQL.

Before running the example, trace each expression and write down the expected state or result. Then run it, compare the actual result, and change one input or rule to confirm that you understand which part controls the behavior.

Example

SELECT title, price
FROM books
WHERE price <= 20.00;

Result

title                 | price
The Long Way Home     | 18.50
Small Systems         | 16.00

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

02.2

AND, OR, IN, and BETWEEN

AND requires every condition, while OR allows alternatives. SQL evaluates AND before OR, so parentheses should make mixed logic explicit. IN matches a set of listed values; BETWEEN includes both endpoints and is useful for numeric and date ranges.

Example

SELECT order_id, status, total
FROM orders
WHERE status IN ('paid', 'shipped')
  AND total BETWEEN 50 AND 200;

Result

order_id | status  | total
4501     | paid    | 72.40
4510     | shipped | 188.00

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

02.3

NULL and three-valued logic

NULL means missing or unknown, not zero or an empty string. Comparisons with NULL do not produce true, so use IS NULL and IS NOT NULL. COALESCE can substitute the first non-NULL value when presenting a result.

Example

SELECT full_name, COALESCE(phone, 'No phone') AS phone_display
FROM contacts
WHERE email IS NOT NULL;

Result

full_name   | phone_display
Mara Singh  | No phone
Jon Bell    | 555-0138

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

02.4

LIKE, ORDER BY, and limiting

LIKE matches text patterns: % represents any sequence and _ represents one character. ORDER BY may use multiple columns with ASC or DESC. A limited query should include an order so the selected top rows are deterministic.

Example

SELECT full_name, points
FROM members
WHERE full_name LIKE 'A%'
ORDER BY points DESC, full_name ASC
FETCH FIRST 5 ROWS ONLY;

Result

full_name    | points
Amara Okafor | 920
Ana Ruiz     | 845

Check your understanding

Explain the example in plain language, identify one boundary or failure case, and revise it so the output changes in a predictable way.

Chapter assignments

Complete these in order. Later assignments assume that earlier skills are working. Do not only test the sample values; design tests that challenge boundaries, missing values, incorrect types, empty collections, and other likely failures.

A2.1
Write filters for a library dataset using comparisons, ranges, IN, and mixed AND/OR logic with parentheses.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A2.2
Find incomplete customer records using NULL tests and create display columns with COALESCE.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A2.3
Create pattern-matching queries for names, codes, and email domains, including one-character wildcards.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.
A2.4
Produce deterministic top-five and bottom-five reports using multi-column sorting and a row limit.
Submit readable source code, a brief design note, and evidence from at least three tests including one boundary or invalid case.

Chapter project

Data Quality Audit

Data Quality Audit — Analyze a messy contacts table. Write separate queries for missing required data, suspicious ranges, inconsistent categories, duplicates, and invalid-looking patterns. Finish with a prioritized cleanup report explaining what each query detects.

Required process

Write a short specification listing inputs, outputs, rules, and failure cases.
Break the work into small functions, queries, modules, or classes appropriate to the language.
Build the smallest working version before adding optional features.
Test normal, boundary, empty, and invalid cases and record the results.
Refactor names and duplication, then write a concise user guide.

Key terms

predicate
filter
operator precedence
range
pattern
wildcard
NULL
three-valued logic
deterministic