102 — Filtering and Sorting

Beginner

Learn to find exactly the data you need with WHERE clauses, comparison operators, pattern matching, and NULL handling. Then organize your results with ORDER BY and paginate with LIMIT and OFFSET.

Learning Objectives

Filter rows with WHERE and comparison operators

Combine conditions with AND and OR

Match values from a list with IN

Filter ranges with BETWEEN

Search text patterns with LIKE and wildcards

Handle NULL values correctly with IS NULL / IS NOT NULL

Sort results with ORDER BY in ascending and descending order

Paginate results with LIMIT and OFFSET

Step 1

WHERE clause basics — equality

The WHERE clause filters rows based on a condition. Only rows that satisfy the condition appear in the results. Start with the simplest filter: exact equality.

Commands to Run

sqlite3 ecommerce.db

.headers on

.mode column

SELECT name, price, stock_quantity FROM products WHERE category_id = 1;

SELECT first_name, last_name, city FROM customers WHERE state = 'CA';

What This Does

WHERE acts like a gatekeeper — it evaluates each row against your condition and only lets matching rows through. The `=` operator checks for exact equality. Notice that text values must be wrapped in single quotes ('CA'), while numbers don't need quotes (1). This is because SQL distinguishes between string literals and numeric literals.

Expected Outcome

The first query returns only products in category 1. The second returns only customers whose state is 'CA'. Rows that don't match the condition are excluded entirely.

Pro Tips

1
SQL uses single quotes for text values: 'CA', 'shipped', 'Electronics'
2
Double quotes are for identifiers (column/table names), not values
3
WHERE is evaluated before SELECT — the database filters first, then picks columns

Common Mistakes to Avoid

⚠️Using double quotes for string values — SQL expects single quotes: WHERE state = 'CA' not WHERE state = "CA"
⚠️Forgetting quotes around text values — WHERE state = CA will look for a column named CA

Was this step helpful?