Skip to content

Instantly share code, notes, and snippets.

@codecademydev
Created September 25, 2020 17:13
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/c735fdde00b75d9dee75e2da3581d641 to your computer and use it in GitHub Desktop.
Save codecademydev/c735fdde00b75d9dee75e2da3581d641 to your computer and use it in GitHub Desktop.
Codecademy export
SELECT COUNT(*)
FROM countries
WHERE continent = 'Africa'
;
-- 56
SELECT countries.continent,
COUNT(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania'
AND population_years.year = '2005'
;
-- Oceania 17
SELECT AVG(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = 'South America'
AND year = '2003'
;
-- 25.8906514285714
SELECT countries.name, MIN(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
;
-- Niue 0.002
SELECT countries.name, AVG(population_years.population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.name = 'Poland'
;
--Poland 38.5606790909091
SELECT countries.name, count(*)
FROM countries
WHERE name LIKE '%The%'
GROUP BY 1;
--Bahamas, The 1
-- Gambia, The 1
-- Netherlands 1
-- Netherlands Antilles 1
-- OR WE CAN USE SIMPLE VERSION
-- SELECT count(*)
-- FROM countries
-- WHERE name LIKE '%The%';
-- 4
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
;
-- Africa 1015.47846
-- Asia 4133.09148
-- Europe 723.06044
-- North America 539.79456
-- Oceania 34.95696
-- South America 396.58235
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment