Aggregating Data using SQL

/*Identify African countries in sample*/
SELECT COUNT(*) AS 'No. Of Countries Sampled in Africa'
FROM countries 
WHERE continent = 'Africa';

/*Identify total 2005 population of all countries in Oceania to 2 decimal places*/
SELECT ROUND(SUM(population), 2) AS 'Population of Oceania in 2005 (mil)'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania' AND population_years.year = 2005;

/*Identify 2003 average population of a country in South America to 2 decimal places*/
SELECT ROUND(AVG(population), 2) AS 'Average Population of South American Country in 2003 (mil)'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = "South America" AND population_years.year = 2003;

/*Identify the country with smallest population in 2007*/
SELECT name AS 'Country with Smallest Population in 2007'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007
ORDER BY population ASC
LIMIT 1;

/*Calculate average population of Poland between 2000 and 2010*/
SELECT ROUND(AVG(population), 2) AS 'Average Population of Poland between 2000 and 2010 inclusive (mil)'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE name = 'Poland';

/*Count number of countries with 'The' in the name eg 'The Maldives'*/
SELECT COUNT(*) AS 'Number of Countries with word "The" in the name (excludes "the" pattern in name)'
FROM countries
WHERE name LIKE 'The %' OR name LIKE '% The %' OR name LIKE '%The';

/* Display total population of each continent in 2010 */
SELECT continent AS 'Continent', ROUND(SUM(population), 2) AS 'Total Population in 2010 (mil)'
FROM countries 
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY continent;