SELECT with WHERE — Querying Data

This is where your database starts feeling like a database: retrieving a filtered, projected view of stored rows. SELECT combines two independent concerns that are worth separating in your implementation — projection (which columns to show) and filtering (which rows to show).

Projection: choosing columns

SELECT * means "every column, in schema order." SELECT name, age means "only these columns, in the order listed" — which may differ from schema order, and may repeat or omit columns. Resolve column names to indices once, up front:

def resolve_columns(schema, select_list):
    if select_list == ["*"]:
        return list(range(len(schema.columns)))
    indices = []
    for name in select_list:
        idx = find_column_index(schema, name)
        if idx is None:
            raise Error(f"ERR unknown column: {name}")
        indices.append(idx)
    return indices

Doing this resolution before scanning any rows means the "unknown column" error fires immediately, rather than partway through printing results.

Filtering: the WHERE predicate

A WHERE clause is a boolean expression evaluated once per row. For this lesson, keep the grammar to a single condition or an AND-chain of conditions:

WHERE age > 25
WHERE age > 25 AND name = 'Bob'

Represent a parsed condition as (column, operator, value), and evaluate it against a row by looking up that column's value and comparing:

def eval_condition(row, schema, col, op, value):
    actual = row[find_column_index(schema, col)]
    if op == "=":  return actual == value
    if op == "!=": return actual != value
    if op == "<":  return actual <  value
    if op == ">":  return actual >  value
    if op == "<=": return actual <= value
    if op == ">=": return actual >= value

An AND-chain is just: evaluate every condition, keep the row only if all of them are true. This is a textbook table scan — for every row in the table, evaluate the predicate, and if it passes, project the selected columns and add it to the result set. You'll optimize this scan later with indices, but correctness comes first.

Output format

Header row first (the selected column names, |-joined), then one line per matching row (|-joined values), reusing whatever row-formatting helper you built for .dump:

SELECT name, age FROM users WHERE age > 25
→ name|age
→ Alice|30
→ Bob|28

If no rows match, you still print the header — an empty result set is not an error, and the header line tells the caller the query executed successfully with zero matches.

Edge cases

• SELECT * still needs schema-order columns even if the table was populated via INSERT ... VALUES with a different literal order in the SQL text (it wasn't — VALUES is positional — but don't accidentally scramble column order in your projection code).
• Comparing a TEXT value with = should do exact string comparison; comparing NUMBER columns with </> should compare numerically, not lexicographically ("9" < "10" as strings is false, but 9 < 10 as numbers is true) — keep values typed, don't stringify everything.
• Unknown column in either the SELECT list or the WHERE clause should produce ERR unknown column: <name> — check both places.

This projection/filter/scan structure is the same shape a real query executor uses; later lessons swap the "scan every row" step for an index lookup without touching projection logic at all.