Codecademy export

population_queries.sqlite

/* Get overview */
SELECT * FROM countries LIMIT 5;
SELECT * FROM population_years LIMIT 5;

/* How many entries in the countries table are from Africa? */
SELECT COUNT(*) AS 'Number of entries from Africa' FROM countries WHERE continent LIKE '%Africa%';

/* 
What was the total population of the continent of Oceania in 2005? */
SELECT SUM(population) AS 'Total population of the Oceania in 2005' FROM countries INNER JOIN population_years ON countries.id = population_years.country_id WHERE continent LIKE '%Oceania%' and year = 2005;

/* 
What is the average population of countries in South America in 2003? */ 
SELECT AVG(population) AS 'Average population of South America in 2003' FROM countries INNER JOIN population_years ON countries.id = population_years.id WHERE continent LIKE '%South America%' AND year = 2003;

/* What country had the smallest population in 2007? */
SELECT name, MIN(population) AS 'Smallest population in 2007' FROM countries INNER JOIN population_years ON countries.id = population_years.country_id WHERE year = 2007;

/* 
What is the average population of Poland during the time period covered by this dataset? */
SELECT AVG(population) AS 'Average population of Poland' FROM countries INNER JOIN population_years ON countries.id = population_years.country_id WHERE name LIKE '%Poland%';

/* How many countries have the word “The” in their name? */
SELECT COUNT(name) AS 'Count' FROM countries WHERE name LIKE '%The%';

/* What was the total population of each continent in 2010? */
SELECT continent AS 'Continent', SUM(population) AS 'Total' FROM countries INNER JOIN population_years ON countries.id = population_years.country_id WHERE year = 2010 GROUP BY continent ORDER BY continent;