Codecademy export

population_queries.sqlite

SELECT COUNT(*)
FROM countries;
--214


SELECT countries.continent,
 SUM(population_years.population) AS 'Total population in 2005'
FROM countries
JOIN population_years
 ON countries.id = population_years.country_id
WHERE continent = 'Oceania'AND year = '2005';
--32.66417 milions


SELECT countries.continent,
 ROUND(AVG(population_years.population),2) AS 'Average population in 2003'
FROM countries
JOIN population_years
 ON countries.id = population_years.country_id
WHERE continent = 'South America'AND year = '2003';
--25.89 milion


SELECT countries.continent,
 MIN(population_years.population) AS 
 'Country with the mallest population in 2007'
FROM countries
JOIN population_years
 ON countries.id = population_years.country_id
WHERE year = '2007';
-- 0.00216 milion


SELECT countries.name,
 ROUND(AVG(population_years.population),2) AS 
 'Average population in Poland between 2000 and 2010'
FROM countries
JOIN population_years
 ON countries.id = population_years.country_id
WHERE name = 'Poland' AND year BETWEEN 2000 AND 2010;
--38.56 milion

SELECT COUNT(name) AS 'Countries with the word “The” in their name'
FROM countries
WHERE name LIKE '%The%';
--4


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