Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created February 16, 2020 20:28
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save codecademydev/26c8e616d6082a2426285bc28e812a50 to your computer and use it in GitHub Desktop.
Codecademy export
-- 3. 56 entries in the countries table from Africa.
SELECT COUNT(*) AS 'Number_of_countries'
FROM countries
WHERE continent = 'Africa';
-- 4. Total population Oceania in 2005= 32.66417
SELECT countries.continent, SUM(population_years.population) AS 'Total_population_2005'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania' AND population_years.year = 2005;
--5. Average population of countries in South America in 2003= 25.8906514285714
SELECT countries.continent, AVG(population_years.population) AS 'Average_population_2003'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = 2003;
--6. Country with smallest population in 2007= Niue
SELECT countries.name, MIN(population_years.population) AS 'MIN_population_2007'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2007;
--7. Dataset time period average population of Poland= 38.5606790909091
SELECT countries.name, AVG(population_years.population) AS 'Total_average_population'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland' AND population_years.year BETWEEN 2000 AND 2010;
--8. 3 countries have the word "The" in their name.
SELECT DISTINCT countries.name
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name LIKE '%The%';
--9. Total population of each continent in 2010.
SELECT countries.continent, SUM(population_years.population) AS 'Total_population_2010'
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE population_years.year = 2010
GROUP by countries.continent
ORDER by population_years.population DESC;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment