Codecademy export

population_queries.sqlite

SELECT COUNT(*)
FROM countries
WHERE continent ='Africa';

SELECT ROUND(SUM(population_years.population), 1) AS 'total population of Oceania'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2005
AND continent = 'Oceania';

SELECT
ROUND(AVG(population_years.population), 1) AS 'average population of countries in South America'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2003
AND continent = 'South America';

SELECT
name,
MIN(population_years.population) AS 'country with smallest population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007;

SELECT
ROUND(AVG(population_years.population), 1) AS 'average population of Poland'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE name = 'Poland';

SELECT
COUNT(*) AS 'countries with "The" in their name'
FROM countries
WHERE name LIKE '%The%';

SELECT
continent,
ROUND(SUM(population_years.population), 1) AS 'total population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
GROUP BY continent;