Ask one table a question

Read a table before you trust an agent with it

This is the part you cannot skip. You will type every query in this section yourself. The point is not speed. It is that a query you have written by hand is a query you can read when an agent writes the next one.

Select and name columns

Start with the shape of a query: SELECT names the columns, FROM names the table, and LIMIT caps the rows returned.

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;"

AS renames a column in the output. It changes the label, not the data.

Filter, sort, and de-duplicate

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, and the number is wrong - and nothing on your screen suggests it.

Ask your coding agent

AI Prompt

I wrote this query to count orders that were not cancelled:

SELECT COUNT(*) FROM orders WHERE order_status != 'cancelled'

The number is lower than I expected. Explain why, show me the corrected query, and tell me how many rows the original version silently dropped. Do not stop at the fix - explain the rule about NULL comparisons that caused it.

Checkpoint

Run both versions and compare:

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. The naive != 'cancelled' filter silently dropped all 24 of those orders. A correct version keeps them, for example with WHERE order_status IS DISTINCT FROM 'cancelled'. The gap between the two counts is exactly 24.