SELECT and WHERE

What you will be able to do

• Write SELECT with column lists and aliases
• Filter with WHERE including NULL-safe patterns
• ORDER BY and LIMIT for sampling

Why this matters for data work

Every dashboard metric starts as which rows, which columns. Bad filters silently drop revenue; forgetting NULL drops a different set of rows than you think.

Concepts

• SELECT col AS alias — rename in the result.
• WHERE — row filter before aggregation.
• AND / OR / IN / BETWEEN / LIKE.
• IS NULL / IS NOT NULL — never = NULL.
• ORDER BY … LIMIT n — sample and top-N.

Practice on your machine

export PGPASSWORD=dcubes PGHOST=127.0.0.1 PGPORT=15432 PGUSER=dcubes PGDATABASE=retail
psql << 'SQL'
SELECT order_id, store_id, status, line_total
FROM orders
WHERE status = 'completed'
  AND line_total >= 20
ORDER BY line_total DESC
LIMIT 10;

SELECT count(*) AS n_null_status
FROM orders
WHERE status IS NULL;

SELECT DISTINCT payment_method
FROM orders
ORDER BY 1;
SQL

What you should see

• Up to 10 completed high-value lines.
• n_null_status = 0 on this seed.
• Three payment methods: card, cash, wallet.

Common mistakes

• WHERE status = NULL — always false; use IS NULL.
• Selecting * in jobs — fine for explore; pin columns in production SQL.
• Filtering dates as text — next lesson covers types.

Next

Types and casts