Day 2 of 10 · Harbour Mutual

SELECT mastery

Ask for exactly the rows and columns you need: filter with WHERE, sort with ORDER BY, page with LIMIT and OFFSET, and compute with functions.

Theory for today: Policies, How a query is evaluated.

The lesson

Choosing columns

SELECT policy_number, start_date, annual_premium
FROM policies
LIMIT 5;

Name the columns you want, in the order you want them. Try it in the playground

Filtering rows with WHERE

WHERE keeps only the rows for which a condition is true. Combine conditions with AND and OR; compare with =, <>, <, >=, BETWEEN ... AND ... (inclusive), IN (...), and text patterns with LIKE (% matches anything).

SELECT policy_number, status, annual_premium
FROM policies
WHERE status = 'active'
  AND annual_premium BETWEEN 1000 AND 1500;

Text and dates go in single quotes; numbers do not. Try it in the playground

SELECT claim_number, cause
FROM claims
WHERE cause IN ('storm', 'fire')
  AND loss_date >= DATE '2025-11-01';

DATE '2025-11-01' is a date, not text. Try it in the playground

A missing value is null, and nothing equals null, not even null: test it with IS NULL or IS NOT NULL.

SELECT COUNT(*)
FROM policies
WHERE broker_id IS NULL;

Policies sold directly have no broker. Try it in the playground

Sorting and paging

SELECT policy_number, annual_premium
FROM policies
ORDER BY annual_premium DESC, policy_number
LIMIT 5;

DESC sorts high to low; the second column breaks ties, so the order is always the same. Try it in the playground

OFFSET skips rows: LIMIT 10 OFFSET 20 is the third page of ten. Paging only makes sense with an ORDER BY that fixes the order.

Functions

SELECT name,
       birth_date,
       EXTRACT(YEAR FROM AGE(DATE '2025-12-31', birth_date)) AS age
FROM customers
LIMIT 5;

AGE gives the time between two dates; EXTRACT(YEAR FROM ...) takes its years. AS names the column. Try it in the playground

The story's today is 31 December 2025: use DATE '2025-12-31', not CURRENT_DATE, so your answers match everyone else's.

Briefs from the business

0 of 5 done

Write SQL to answer each brief, run it, then check your answer against Harbour Mutual's. Your database is the same one as in the playground.

Starting PostgreSQL in your browser…

Brief 2.1 · Renewals in the first week of January

The renewals team wants the active policies whose cover ends in the first week of January 2026 (1 to 7 January): the policy number and end date, earliest first, and by policy number within a day.

Tables: policies

Brief 2.2 · The ten largest premiums

Which ten policy terms carry the highest annual premium? Show the policy number and premium, highest first (by policy number if two are equal).

Tables: policies

Brief 2.3 · Customers aged 80 or older

How many customers were 80 or older on 31 December 2025? Answer with a single number.

Tables: customers

Brief 2.4 · Open claims reported in December

Which claims reported in December 2025 are still open? Show the claim number, cause and reserve, the largest reserve first (by claim number if two are equal).

Tables: claims

Brief 2.5 · The second page of customers

A customer list is shown ten to a page, in alphabetical order of name (by customer number for the same name). Show page 2: customer id and name.

Tables: customers

5 briefs to go today.

Day 3: Joins and relationships