SELECT with WHERE — Querying Data

SELECT is the most complex SQL statement. It reads data, optionally filters it, and returns results in a specific column order.

Anatomy of SELECT

SELECT name, age FROM users WHERE age > 25

Three parts:

1. Projection (name, age): which columns to output
2. Source (FROM users): which table to read
3. Filter (WHERE age > 25): which rows to include

Projection

SELECT * returns all columns. SELECT name, age returns only those two, in that order. This is called projection — you're projecting the full row onto a subset of columns.

Full row: [1, 'Alice', 30]  (columns: id, name, age)
SELECT name, age → ['Alice', 30]
SELECT age, name → [30, 'Alice']  (order matters!)

WHERE Clause Evaluation

For each row, evaluate the WHERE expression:

Row: [1, 'Alice', 30]
WHERE age > 25  →  30 > 25  →  true  →  include this row

Supported operators:

The AND Operator

Multiple conditions can be combined:

SELECT * FROM users WHERE age > 20 AND age < 40

Both conditions must be true. We'll add OR and parentheses in a later lesson.

Output Format

Results are pipe-separated with a header row:

name|age
Alice|30
Charlie|35

If no rows match, output the header only (an empty result set is not an error).

How SQLite Evaluates Queries

SQLite compiles SELECT into bytecode that runs on a virtual machine (VDBE). Each instruction does one small thing: open a cursor, advance to the next row, compare a column, jump if false, output a column. It's like a tiny assembly language for databases. We won't go that far, but understanding this architecture helps.

Your Task

Implement SELECT with column selection, WHERE filtering with comparison operators, and AND for combining conditions.