Codecademy export

population_queries.sqlite

/* How many entries in the countries table are from Africa?  
56 */
select count(*)
from countries
where continent like 'africa';

/* What was the total population of the continent of Oceania in 2005? 
33.0*/
select round(sum(population_years.population),0)
from countries
join population_years
  on countries.id = population_years.country_id
  where countries.continent like 'Oceania'
    and population_years.year = 2005;

/* What is the average population of countries in South America in 2003?
 26.0*/
select round(avg(population_years.population),0)
from countries
join population_years
  on countries.id = population_years.country_id
  where countries.continent like 'south america'
    and population_years.year = 2003;
 
/* What country had the smallest population in 2007? 
 Niue, 0.00216*/
select countries.name, min(population_years.population)
from countries
join population_years
  on countries.id = population_years.country_id
  where population_years.year = 2007;

/* What is the average population of Poland during the time period covered by this dataset?
 39.0*/
select round(avg(population_years.population),0)
from countries
join population_years
  on countries.id = population_years.country_id
  where countries.name like 'poland';

/* How many countries have the word “The” in their name?
4*/
select count(*)
from countries
where name like '%the%';

/* What was the total population of each continent in 2010? 
continent	population
Asia	4133.0
Africa	1015.0
Europe	723.0
North America	540.0
South America	397.0
Oceania	35.0*/
select countries.continent, round(sum(population_years.population),0) as 'population'
from countries
join population_years
  on countries.id = population_years.country_id
where population_years.year = 2010
group by countries.continent
order by 2 desc;