Codecademy export

population_queries.sqlite

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

SELECT SUM(population_years.population) as oceania_population_2005
FROM countries
JOIN population_years ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania' AND population_years.year = 2005;

SELECT AVG(population_years.population) as latin_america_avg_2003
FROM countries
JOIN population_years ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = 2003;

SELECT MIN(population_years.population) as smallest_population_country,
  countries.name
FROM countries
JOIN population_years ON countries.id = population_years.country_id
WHERE population_years.year = 2007;

SELECT AVG(population_years.population) as poland_avg
FROM countries
JOIN population_years ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';

SELECT COUNT(*) as countries_with_the
FROM countries
WHERE name LIKE '%The';

SELECT SUM(population_years.population) as total_population_2010,
  population_years.year,
  countries.continent
FROM countries
JOIN population_years ON countries.id = population_years.country_id
WHERE year = 2010
GROUP BY 3
ORDER BY 1 DESC;