Codecademy: World Populations II Challenge Project (SQL)

worldpop2.sqlite

SELECT COUNT(*) AS 'No. Of Countries Sampled in Africa'
FROM countries 
WHERE continent = 'Africa';
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;
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;
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;
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';
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';
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;