Codecademy export

population_queries.sqlite

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

SELECT SUM(population_years.population)
 AS 'Total population of Oceania in 2005'
FROM population_years
JOIN countries
 ON population_years.country_id = countries.id
  WHERE continent = 'Oceania' and year = 2005;

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

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

SELECT AVG(population_years.population)
 AS 'Avg. Population in Poland'
FROM population_years
JOIN countries
 ON population_years.country_id = countries.id
WHERE name = "Poland";

SELECT COUNT(*)
FROM countries
WHERE name LIKE "%The%";

SELECT countries.continent, 
 ROUND(SUM(population_years.population), 2) 
 AS 'Total population per continent in 2003'
FROM population_years
JOIN countries
 ON population_years.country_id = countries.id
WHERE year = 2010
GROUP BY 1
ORDER BY 2 DESC;