Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created March 24, 2020 17:36
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save codecademydev/5c84b0207861568e2778842c5dbd406f to your computer and use it in GitHub Desktop.
Codecademy export
/*Project Goals
You’ll work with a dataset of world population by country data from recent years. You’ll write queries to retrieve interesting data and answer a set of specific questions.*/
--How many entries in the countries table are from Africa?
SELECT COUNT(*)
FROM countries
WHERE continent = 'Africa';
--What was the total population of the continent of Oceania in 2005?
SELECT countries.continent,
SUM(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2005
GROUP BY countries.continent;
--What is the average population of countries in South America in 2003?
SELECT countries.continent,AVG(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2003
GROUP BY 1;
--What country had the smallest population in 2007?
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?
SELECT countries.name,AVG(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland';
--How many countries have the word “The” in their name?
SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';
--What was the total population of each continent in 2010?
SELECT countries.continent,SUM(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2010
GROUP BY 1;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment