Ask one table a question

Read a table before you trust an agent with it

This is the part you cannot skip. A query you have written by hand is a query you can read when an agent writes the next one, so write these yourself.

Start with the shape of a query: SELECT names the columns, FROM names the table, and LIMIT caps the rows returned. AS renames a column in the output - it changes the label, not the data.

bruin query --connection duckdb-default \
  --description "preview orders with named columns" \
  --query "SELECT order_id, ordered_at AS placed_at, order_total AS amount FROM orders LIMIT 5;"

WHERE keeps only the rows that match a condition. Combine conditions with AND, OR, and NOT. ORDER BY sorts, and DISTINCT removes duplicate rows from the result.

bruin query --connection duckdb-default \
  --description "large recent orders sorted by value" \
  --query "SELECT order_id, order_total, order_status FROM orders WHERE order_total > 500 AND ordered_at >= '2024-01-01' ORDER BY order_total DESC LIMIT 10;"

A few filters read almost like English and are worth knowing: BETWEEN 100 AND 200 matches a range, IN ('USD', 'EUR') matches a list, LIKE 'A%' matches a text pattern, and IS NULL matches missing values. Use IS NULL, never = NULL, because a value can never equal a thing that is absent.

Your first silent failure

Now the shock. Count the orders that were not cancelled:

bruin query --connection duckdb-default \
  --description "naive count of orders that are not cancelled" \
  --query "SELECT COUNT(*) FROM orders WHERE order_status != 'cancelled';"

The query returns 1,069. The right answer is 1,093. Some orders have no status at all - order_status is NULL on 24 rows. In SQL, NULL != 'cancelled' is not true. It is unknown, so those 24 rows are dropped without a word. This is a silent failure: the query ran, returned a number, the number is wrong, and nothing on your screen suggests it. The rule is simple. Use IS NULL, never = NULL, and reach for IS DISTINCT FROM when you want a comparison that treats NULL as a real value.

Your task

Work through the basics in queries/01-first-look.sql. Write a SELECT that names columns with AS, a WHERE that filters and sorts with ORDER BY and caps rows with LIMIT, then reproduce the silent NULL filter. Run the naive count and confirm it returns 1,069:

bruin query --connection duckdb-default \
  --description "naive count of orders that are not cancelled" \
  --query "SELECT COUNT(*) FROM orders WHERE order_status != 'cancelled';"

Then write a corrected version that returns 1,093, and prove the gap by counting the missing statuses:

bruin query --connection duckdb-default \
  --description "orders with a missing status" \
  --query "SELECT COUNT(*) AS null_status FROM orders WHERE order_status IS NULL;"

null_status should be 24 - exactly the gap between the naive count and the correct one. A correct filter keeps those rows, for example WHERE order_status IS DISTINCT FROM 'cancelled'.

Check your understanding

• Why does != 'cancelled' return fewer rows than expected?
• What is the rule about comparing to NULL?
• How many rows did the naive filter silently drop?

Do it with your agent

Say next lesson and your agent teaches this, asks you these questions, then sets the task above. Do it by hand, then say review my work - it runs your query, checks it against a rubric, and tells you what to fix or marks the lesson done.