Page Comparison

...

SELECT Course, SUM(FTE) AS TotalFTE, SUM(1) AS TotalStudents
FROM Students
GROUP BY Department

...

We have a code in our Students table so we need to relate it to its lookup, Country:

...

SELECT Students.Surname, Students.Forename, Country.CountryName
FROM Students
INNER JOIN Country ON Students.Country = Country.CountryCode

gives:

...

SELECT Students.Surname, Students.Forename, Country.CountryName
FROM Students
LEFT JOIN Country ON Students.Country = Country.CountryCode

gives:

...

SELECT Country, SUM(1) AS TotalStudents
FROM Students
GROUP BY Country

gives:

Then wrap this in brackets and use it in your FROM clause:

SELECT Country.CountryName, StuCount.TotalStudents
FROM
(
    SELECT Country, SUM(1) AS TotalStudents
    FROM Students
    GROUP BY Country
) StuCount
LEFT JOIN Country ON Students.Country = Country.CountryCode
WHERE StuCount.TotalStudents >= 100

gives:

Nesting is a powerful way to take oodles of big data, perform a sequence of steps on it, in order to squish it down into exactly the simple form you want. It's also a superfast way to get lost in onion layers of logic and make small mistakes that are obscured until a few years later when something new crops up and breaks things.

...

This is about as far as you can get with the standard SQL syntax. Despite agreements all round, database vendors have decided they know best and have their own implementation of things that are so common across the world that you sometimes can't believe it. Say you want the last character of a value (we often do, as we keep programme length in this place of many codes).

Microsoft SQL Server / MySQL

SELECT RIGHT(ProgrammeCode,1)

...