Querying Data with SELECT

Load some sample data first:

INSERT INTO customers (email, full_name) VALUES
  ('ada@example.com', 'Ada Lovelace'),
  ('alan@example.com', 'Alan Turing'),
  ('grace@example.com', 'Grace Hopper');

INSERT INTO products (sku, name, price_cents) VALUES
  ('HOOD-1', 'Hoodie', 3900), ('MUG-3', 'Mug', 1250), ('CAP-2', 'Cap', 1800);

The basic shape

SELECT name, price_cents
FROM products
WHERE active = TRUE AND price_cents < 2000
ORDER BY price_cents DESC
LIMIT 10;

SQL is written in this order: SELECT → FROM → WHERE → GROUP BY → HAVING → ORDER BY → LIMIT.

Filtering

WHERE status = 'paid'
WHERE price_cents BETWEEN 1000 AND 5000
WHERE status IN ('paid', 'shipped')
WHERE email LIKE '%@example.com'        -- % = anything, _ = one character
WHERE lower(full_name) LIKE '%ada%'      -- ILIKE in PostgreSQL ignores case
WHERE phone IS NULL                      -- never "= NULL"
WHERE created_at >= '2026-10-01'

NULL means "unknown". Any comparison with it is unknown, so phone = NULL matches nothing. Always use IS NULL / IS NOT NULL.

Computed columns and aliases

SELECT name,
       price_cents / 100.0 AS price,
       price_cents * 1.2 / 100.0 AS price_with_tax
FROM products;

Conditional values

SELECT name,
       CASE WHEN price_cents >= 3000 THEN 'premium'
            WHEN price_cents >= 1500 THEN 'standard'
            ELSE 'budget' END AS tier
FROM products;

Distinct and pagination

SELECT DISTINCT status FROM orders;

-- page 3 with 20 per page
SELECT * FROM orders ORDER BY created_at DESC, id DESC LIMIT 20 OFFSET 40;

For large tables, keyset pagination is faster than big offsets: WHERE (created_at, id) < (:last_created, :last_id) ORDER BY created_at DESC, id DESC LIMIT 20.

Avoid SELECT *

In application code, name the columns you need. It is faster, clearer and does not break when columns are added.

Exercise

Write queries for: active products under $20 sorted by price; customers whose email is not at example.com; the 5 most recent orders; products labelled with a price tier using CASE.

Check yourself

• Why does WHERE phone = NULL return nothing?
• In what order does SQL expect WHERE, ORDER BY and LIMIT?
• Why is keyset pagination faster than large offsets?