Codecademy export

population_queries.sqlite

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


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


/*What is the average population of countries in South America in 2003?*/
select avg(population)
from population_years
join countries
on population_years.country_id=countries.id
where continent = 'South America' and year='2003';
/*25.8906514285714*/


/*What country had the smallest population in 2007?
*/
select min(population)
from population_years
join countries
on population_years.country_id=countries.id
where year='2007';
/*0.00216*/

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


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


/*What was the total population of each continent in 2010?*/
select continent, sum(population)
from population_years
join countries
on population_years.country_id=countries.id
where year='2010' group by continent;
/*Africa 	1015.47846
Asia 	4133.09148
Europe 	723.06044
North America 	539.79456
Oceania 	34.95696
South America 	396.58235*/