Skip to content

Instantly share code, notes, and snippets.

@codecademydev

codecademydev/population_queries.sqlite Secret

Created February 4, 2020 18:54
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/4f1c932d7576524b6ede249f03c26329 to your computer and use it in GitHub Desktop.
Save codecademydev/4f1c932d7576524b6ede249f03c26329 to your computer and use it in GitHub Desktop.
Download ZIP
Codecademy export
Raw
population_queries.sqlite
SELECT Round(SUM(population_years.population), 2) AS 'total population',
countries.continent,
population_years.year
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE population_years.year = 2010
GROUP BY countries.continent;
SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';
SELECT ROUND(AVG(population_years.population), 2) AS 'average population',
countries.name
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.name = 'Poland'
GROUP BY countries.name;
SELECT MIN(population) as 'smallest',
countries.name
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE population_years.year = 2007;
SELECT ROUND(AVG(population_years.population), 2) AS 'average population',
countries.continent
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.continent = 'South America'
AND population_years.year = 2003
GROUP BY countries.continent;
SELECT SUM(population_years.population),
countries.continent,
population_years.year
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.continent = 'Oceania'
AND population_years.year = 2005
GROUP BY countries.continent;
SELECT COUNT(*)
FROM countries
where continent = 'Africa';
@Rajamanikam
Copy link

SELECT Round(SUM(population_years.population), 2) AS 'total population',
countries.continent,
population_years.year
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE population_years.year = 2010
GROUP BY countries.continent;

SELECT COUNT(*)
FROM countries
WHERE name LIKE '%The%';

SELECT ROUND(AVG(population_years.population), 2) AS 'average population',
countries.name
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.name = 'Poland'
GROUP BY countries.name;

SELECT MIN(population) as 'smallest',
countries.name
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE population_years.year = 2007;

SELECT ROUND(AVG(population_years.population), 2) AS 'average population',
countries.continent
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.continent = 'South America'
AND population_years.year = 2003
GROUP BY countries.continent;

SELECT SUM(population_years.population),
countries.continent,
population_years.year
FROM population_years
JOIN countries
ON population_years.country_id = countries.id
WHERE countries.continent = 'Oceania'
AND population_years.year = 2005
GROUP BY countries.continent;

SELECT COUNT(*)
FROM countries
where continent = 'Africa';

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment