World Populations II Challenge Project (SQL)

py0056517676 · August 19, 2020, 6:59pm

Here is my solution:
I know is not perfect, but I got all the answers:

select *
from countries;

select *
from population_years;

select count(*)
from countries
where continent = ‘Africa’;

SELECT continent, year, sum(population)
FROM countries
JOIN population_years
WHERE countries.id = population_years.country_id
GROUP by continent,
year = 2005;

SELECT continent, year, avg(population)
FROM countries
JOIN population_years
WHERE countries.id = population_years.country_id
Group by continent,
year = 2003;

SELECT name, year, min(population)
FROM countries
JOIN population_years
WHERE countries.id = population_years.country_id
Group by year = 2007;

SELECT name, avg(population)
FROM countries
JOIN population_years
WHERE countries.id = population_years.country_id
Group by name = ‘Poland’;

SELECT *
FROM countries
WHERE name like ‘%The%’;

SELECT continent, year, sum(population)
FROM countries
JOIN population_years
WHERE countries.id = population_years.country_id
Group by continent,
year = 2010;

css0478751288 · August 20, 2020, 12:19pm

Hello! Here are my solutions too

github.com

omflisa/Codecademy-Exercises/blob/master/World Populations SQL Practice II: Multiple Tables

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


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


SELECT countries.continent, population_years.year, AVG(population_years.population) AS 'Average Population'
FROM population_years
JOIN countries
  ON countries.id = population_years.country_id
WHERE countries.continent = 'South America' AND population_years.year = '2003'; 


SELECT countries.name, population_years.year, MIN(population_years.population) AS 'Total Population'

This file has been truncated. show original

projektnosound · August 22, 2020, 8:08pm

That was a really fun challenge. Thank you! Here are my results.

gist.github.com

https://gist.github.com/ProjectNosound/7c570a7d15aa20e817edf68f90b17be7

gistfile1.txt

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

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

This file has been truncated. show original

css0759291550 · August 24, 2020, 3:41pm

gist.github.com

https://gist.github.com/creahmiePrince/9ccd521a47bb06e0d0ac83e578bf16dd

gistfile1.txt

--What was the total population of the continent of Oceania in 2005?
SELECT continent, round (sum (population_years.population),2) AS 'Total Population' FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = 'Oceania' AND year = '2005';

--How many entries in the countries table are from Africa?
SELECT count() FROM countries
WHERE continent = 'Africa';

This file has been truncated. show original

ttpojh · August 25, 2020, 12:58am

this is my solution

any feedback welcome!

jtrawick · August 25, 2020, 5:24am

Below are my solutions to the World Populations II practice.

github.com

jtrawick/Codecademy_SQL/blob/master/world_populations_SQL_practice_ii.sqlite

-- **Below are my solutions to the Codecademy World Populations SQL Practice II
-- **Commented-out problem descriptions will precede solutions
-- **Additional comments by me will use ** as an identifier for easier reading


-- **Begin problem/solution set


-- **Problems 1 and 2 are informational only and require no solution
-- **The descriptions nonetheless listed below are retained in an attempt to provide additional clarity


-- 1. In order to complete this project, you should have completed the Learn SQL Manipulation, Queries, Aggregate Functions, and Multiple Tables lessons in our Learn SQL course.


-- 2. In this project, you’ll answer questions using a database of world population by country.
-- There are two tables:

-- countries
-- id	INTEGER	Primary Key

This file has been truncated. show original

beta4292341513 · August 26, 2020, 1:37am

Here are my answers! Hopefully you find them helpful.

gist.github.com

https://gist.github.com/codecademydev/70ab8c1c816bb3f33a4011521458a5c0

population_queries.sqlite

SELECT COUNT(*) AS 'Africa Entries'
FROM countries
WHERE continent = 'Africa';

SELECT SUM(population) AS 'Oceania Total Population in 2005'
FROM population_years
JOIN countries
ON population_years.id = countries.id
WHERE continent = 'Oceania' AND year = '2005';

This file has been truncated. show original

arraysolver16164 · August 27, 2020, 6:48am

gist.github.com

https://gist.github.com/codecademydev/874e8b41b8ebf6cd29d9c99d19096eb8

population_queries.sqlite

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

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

SELECT id, continent
FROM countries

This file has been truncated. show original

michielvleer06107572 · August 27, 2020, 11:59am

gist.github.com

https://gist.github.com/codecademydev/59561837dd81ef662c67a7d3b5130bae

population_queries.sqlite

-- 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, round(sum(population_years.population),0) as 'total population', population_years.year
from countries

This file has been truncated. show original

rubyace0430 · August 27, 2020, 11:38pm

So, here is my solution to this project.
Please review and let me know if there is anything to be changed. TIA!

gist.github.com

https://gist.github.com/codecademydev/b4e546b278270195aad5f1dfd564ff56

population_queries.sqlite

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

SELECT SUM(population)
FROM countries
INNER JOIN population_years
on countries.id=population_years.country_id
where continent='Oceania'
  and year=2005;

This file has been truncated. show original

jessicajohnson079355 · August 29, 2020, 1:54pm

Here is my code. Fun project!

gist.github.com

https://gist.github.com/codecademydev/28daeedd6e8013008c16dfbadacfa00f

population_queries.sqlite

SELECT COUNT (*) FROM countries
WHERE continent = 'Africa';
-- 56

SELECT SUM(population) FROM population_years
JOIN countries
ON countries.id = population_years.country_id
WHERE year = 2005
AND continent = 'Oceania';
--32.66417 million

This file has been truncated. show original

diazbrittany · August 30, 2020, 6:34am

gist.github.com

https://gist.github.com/diazbrittany/8ae27972af2e7f98714592a6ceffe5d9

Codecademy World Populations SQL Project II.sqlite

SELECT COUNT (*)
FROM countries
WHERE continent = 'Africa';
--Answer: 56

SELECT countries.continent, ROUND(SUM (population_years.population), 0) AS total_population, population_years.year
FROM countries
JOIN population_years 
	ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania'

This file has been truncated. show original

I did the first Populations project earlier today, and this time I decided to experiment a little more and fine tune the queries.

byteninja83424 · August 30, 2020, 9:56pm

This is my code:

gist.github.com

https://gist.github.com/codecademydev/9212d1365abee1d9c781d8f50fc61d73

population_queries.sqlite

--3 How many entries from Africa
select count(*) as 'Africa entries' from countries where continent = 'Africa';

--4 Total population Oceania 2005
select sum(population) as 'Total population Oceania 2005 in mil' from population_years join countries on countries.id=population_years.country_id where continent = 'Oceania' and year = 2005;

--5 Average population South America 2003
select avg(population) as 'Average population South America 2003' from population_years join countries on countries.id=population_years.country_id where continent = 'South America' and year = 2003;

--6 Smallest population 2007

This file has been truncated. show original

kennyx0r · August 30, 2020, 11:50pm

Hi everyone, here I share my solution:

github.com

martingavioli/cc-worldpopulation-II/blob/master/world_population_II_queries.sql

-- Countries from Africa

SELECT count(*)
FROM countries
WHERE continent = 'Africa';

-- Population of oceania in 2005

SELECT countries.continent,
	population_years.year,
	sum(population_years.population) AS 'Total population'
FROM countries
JOIN population_years
	ON population_years.country_id = countries.id
WHERE countries.continent = 'Oceania'
AND population_years.year = 2005;

-- Avg population in South America in 2003

SELECT countries.continent,

This file has been truncated. show original

I have noticed that most of you dont call the columns with the table name at the beggining, as “countries.continent” and just calling “continent” seems to work fine.
On wich cases should I add the table name as well?

Any feedback is very welcome, thanks!

mattchisholm77657044 · August 31, 2020, 1:27am

Here are my solutions! Did anyone else have trouble getting the Code Academy sql to load in DB Browser? I was asked for an encryption key.

gist.github.com

https://gist.github.com/codecademydev/278d382e3519fdde7cc2d67f27000cb1

population_queries.sqlite

select continent, count (*)
from countries 
where continent='Africa';

select continent, sum(population), year
from countries
join population_years
on countries.id=population_years.country_id
where continent='Oceania'
and year=2005;

This file has been truncated. show original

system9974833568 · August 31, 2020, 3:25am

Hi! i’d like to know how can i set up on GitHub on debian 9 system.
thanks for your answer

jboyle777 · September 1, 2020, 2:14am

gist.github.com

https://gist.github.com/codecademydev/a26c1d204386edf39fc998f475f1192b

population_queries.sqlite

--How many entries in the countries table are from Africa?
SELECT *
FROM countries
WHERE continent = 'Africa';
--56

--What was the total population of the continent of Oceania in 2005?
SELECT SUM(population)
FROM population_years
LEFT JOIN countries

This file has been truncated. show original

Fairly straight forward solutions I think, nothing crazy.

williamsonson0815400 · September 1, 2020, 10:35am

Wow incredible
Seo-Yun

nicodebonis · September 2, 2020, 1:22am

Hey! Here are my answers.

I think I got over excited with the WITH clause, and I think it might not be the cleanest/clearest way to code and redact.

Any advice on how to make it better (or, at least, more clear)?

gist.github.com

https://gist.github.com/codecademydev/b4320c4dfa7202e384b83e1b2dffcfa8

population_queries.sqlite

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

WITH ocean AS (SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE countries.continent = 'Oceania'
)
SELECT ROUND(SUM(population), 2) AS 'Población total Oceania 2005' FROM ocean
WHERE year = 2005;

This file has been truncated. show original

lightlyskewed · September 2, 2020, 9:14pm

Love the variety we see here. I tried this with and without the ‘WITH/ AS’ operator and kept it in the end. Not sure if it’s efficient code or not.

gist.github.com

https://gist.github.com/codecademydev/c22c4a6ba70dfbf6b6ad56f9580296c7

population_queries.sqlite

SELECT COUNT(*) AS 'African Countries'
FROM countries WHERE
continent = 'Africa';

WITH pop_countries AS (
  SELECT * FROM population_years
  JOIN countries on population_years.country_id = countries.id
)
SELECT ROUND(SUM(population),2) AS 'Oceania Pop. 2005 (Millions)' 
FROM pop_countries

This file has been truncated. show original