SQL basics: select, filter and check rows

Run a complete query

Each row is one order. Keep completed orders worth at least $50. The WITH block supplies the sample data, so no existing table is required.

WITH orders(order_id, customer_id, amount_usd, status) AS (
  VALUES (101, 1, 50.0, 'completed'),
         (102, 1, 30.0, 'returned'),
         (103, 2, 80.0, 'completed'),
         (104, 3, NULL, 'pending')
)
SELECT order_id, customer_id, amount_usd
FROM orders
WHERE status = 'completed' AND amount_usd >= 50
ORDER BY amount_usd DESC, order_id;

Expected: order 103 ($80), then order 101 ($50). The returned order and the pending order are excluded.

Read the query in stages

• FROM chooses the source; SELECT chooses the output expressions.
• WHERE keeps rows for which the condition is true. Unknown comparisons involving NULL do not pass.
• ORDER BY makes the result order explicit. A stable second key breaks ties.
• LIMIT restricts how many rows are returned in PostgreSQL. Without an order, the chosen subset is unspecified, not a random sample.

NULL, DISTINCT and aliases

NULL represents missing or unknown information. Use IS NULL to test it. COALESCE supplies a replacement only when the analysis justifies one; an unknown amount is not automatically zero.

SELECT DISTINCT status FROM orders ORDER BY status;

Run this statement against the sample CTE above. DISTINCT removes duplicate output combinations. It does not repair a faulty join. AS gives a result column a readable name; naming columns explicitly also makes a query easier to review.

Practise a change

Change the threshold to $60: only order 103 should remain. Then select the missing amount with amount_usd IS NULL: expect order 104. Explain why amount_usd = NULL does not select it.