SQL, because the data is not in a spreadsheet

A spreadsheet is useful when the data is already sitting in front of you. Often it is not.

Policy, claims, customer and transaction data may sit in databases containing far more rows than you would sensibly copy into Excel. SQL is the language used to ask those databases for the rows and columns you need.

For an actuarial analyst, that might mean retrieving policies written during a period, calculating premium by class, finding claims above a threshold or building an extract that will then be analysed in Excel, R or Python.

The level this module covers

This is not a database-engineering course. You do not need to learn how to design databases, manage servers or optimise complicated queries.

These three lessons focus on the SQL that is most useful when working with actuarial data:

• selecting and filtering data
• grouping and summarising rows
• joining tables together
• understanding how joins can change your totals
• checking duplicates and missing values
• validating an extract before using it

By the end, you should be able to read a straightforward SQL query, write a small one yourself and, importantly, recognise some of the ways a query can return a plausible-looking wrong answer.

Start with a table

A database contains tables made up of rows and named columns.

For example, a policies table might contain one row per policy:

A basic SQL query has three useful parts:

select policy_id, class, premium
from policies
where premium > 1000

SELECT says which columns you want.

FROM says which table they come from.

WHERE says which rows to keep.

So this query returns the policy ID, class and premium for policies with premium above 1,000.

SQL keywords are not usually case-sensitive, so SELECT and select mean the same thing. The examples in this course use lower case simply for consistency.

Summarising rows

Often you do not want every individual policy back. You want a summary such as total premium by class.

Three functions appear repeatedly:

count(*)
sum(premium)
avg(premium)

count(*) counts rows, sum() adds values and avg() calculates their average.

To calculate them separately for each class, use GROUP BY:

select class,
       count(*) as policy_count,
       sum(premium) as total_premium
from policies
group by class

Instead of returning one row per policy, the query now returns one row per class.

The names after AS are aliases. They give calculated columns useful names such as policy_count and total_premium.

Your turn: premium by class

count(*) counts the rows that reached each group. In this table there is one row per policy, so the row count is also the policy count.

That distinction matters whenever you use somebody else's data. Before interpreting a count, ask what one row of the table actually represents.

WHERE and HAVING

WHERE filters individual rows before they are grouped.

For example:

where start_year = 2025

keeps only 2025 policies before any totals are calculated.

Sometimes you want to filter the summary instead.

Suppose you have grouped policies by class and only want classes containing at least three policies. That condition depends on count(*), which only exists after the rows have been grouped.

SQL uses HAVING for that:

select class,
       count(*) as policy_count
from policies
group by class
having count(*) >= 3

The useful distinction is:

WHERE filters rows; HAVING filters groups.

Your turn: filter the groups

Ordering the result

SQL does not promise that rows will come back in a particular order unless you ask for one.

ORDER BY sorts the result:

order by total_premium desc

DESC means largest to smallest.

ASC means smallest to largest and is the default.

For example:

select class,
       sum(premium) as total_premium
from policies
group by class
order by total_premium desc

returns the class with the largest total premium first.

If the order matters to the person using the result, make it explicit in the query rather than relying on how the database happened to return the rows.

Make the query easy to review

A few habits make SQL easier for somebody else to understand.

Name the columns you need.

While exploring a table, SELECT * can be convenient:

select *
from policies

But for a query that will be saved or reused, explicitly naming the columns makes the expected output clearer:

select policy_id, class, premium
from policies

Name calculated columns.

Instead of leaving a result called sum(premium), give it an alias:

sum(premium) as total_premium

The same applies to counts:

count(*) as policy_count

Check the total.

If you calculate premium by class, the class totals should add back to the total premium over the same rows:

select sum(premium) as total_premium
from policies

If your grouped result adds to a different number, something has changed between the source rows and the summary and you should understand why before using it.

The same checking principle appeared in the Excel, Python and R lessons: summarising data should not make unexplained amounts appear or disappear.

The next two SQL lessons make that habit more important. Joins, duplicated rows and missing values can all change a result without necessarily producing an error.

The next lesson joins tables together and shows why a query can be perfectly valid SQL while still producing the wrong arithmetic.