Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created March 21, 2020 16:16
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save codecademydev/fe91dd3682ca929bc78c7c917610f154 to your computer and use it in GitHub Desktop.
Save codecademydev/fe91dd3682ca929bc78c7c917610f154 to your computer and use it in GitHub Desktop.
Codecademy export
SELECT * FROM countries
LIMIT 5;
SELECT * FROM population_years
LIMIT 5;
--How many entries in the countries table are from Africa?--
SELECT COUNT(name) FROM countries
WHERE continent = 'Africa';
--Ans: 56
--What was the total population of the continent of Oceania in 2005?
SELECT countries.continent, ROUND(SUM(population_years.population), 2) AS 'Oceania in 2005', population_years.year FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania' AND population_years.year = 2005;
--Ans: 32.66
--What is the average population of countries in South America in 2003?
SELECT countries.continent, ROUND(AvG(population_years.population), 2) AS 'Avr in SA 2003', population_years.year FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = 2003;
--Ans: 25.89
--What country had the smallest population in 2007?
SELECT countries.name, population_years.population FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2007 AND population_years.population IS NOT NULL
ORDER BY population_years.population ASC
LIMIT 1;
--Ans: Niue, population 0.00216
--What is the average population of Poland during the time period covered by this dataset?
SELECT ROUND(AVG(population_years.population), 2), countries.name FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name LIKE '%Poland%' AND population_years.year = 2010;
--Ans: 38.46
--How many countries have the word “The” in their name?
SELECT *, COUNT(*) FROM countries
WHERE countries.name LIKE '%The%'
GROUP BY name;
SELECT COUNt(*) FROM countries
WHERE countries.name LIKE '%The%';
--Ans:4
--What was the total population of each continent in 2010?
SELECT countries.continent, ROUND(SUM(population_years.population), 2) AS 'All in 2010', population_years.year 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;
--Ans: Asia - 4133.09, Africa - 1015.48, Europe - 723.06, NortH America - 539.79, SoutH America 396.58, Oceania - 34.96
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment