World Populations SQL Practice II question

Link to exercise: https://www.codecademy.com/paths/data-science/tracks/sql-intermediate/modules/dspath-sql-multiple-tables/lessons/multiple-tables/exercises/with
Hi everyone!

I’m doing an SQL practice. I was asked:

What was the total population of the continent of Oceania in 2005?

So I wrote:

SELECT SUM(population)

FROM population_years

JOIN countries

ON population_years.id =

countries.id

WHERE continent = 'Oceania'

AND year = 2005;

and got: 52.81393

The answer from codecademy is:

SELECT SUM(population) FROM population_years

INNER JOIN countries ON

countries.id = population_years.country_id

WHERE year = 2005

AND continent = 'Oceania';

and the outcome: 32.66417

I have compared them many times and still can’t find what I did that affects the code. Can someone help me please?:slight_smile:

1 Like

You’re using population_years.id and the Codecademy solution is using population_years.country_id. :wink:
To see the difference try:

SELECT id, country_id FROM population_years;

Happy coding!

1 Like

What does this line of code do?
countries.id = population_years.country_id

Thanks so much for posting this. I too, made this mistake.

1 Like

The JOIN is matching the two tables on the appropriate id.
The Primary Key on the countries table is id, the foreign key on population_years matches that on country_id

I can’t see the solutions. Could u say where they are please?

New here, not sure if this is the best place to post this question.

I’ve noticed that the result doesn’t change if I use INNER JOIN, LEFT JOIN or JOIN with this answer. What is the correct one to use in this case?

Hey there guys,

I’d like to know what’s the issue with this answer for this particular exercise.

Thanks in advance.

WITH full_data AS (
SELECT * FROM countries
JOIN population_years
ON countries.id = population_years.country_id
)
SELECT population_years.population, population_years.year, countries.continent
FROM full_data
WHERE countries.continent = ‘Oceania’ AND population_years.year = 2005;

Hello All,

I am having a similar problem on this question. I have reviewed my code multiple times and can’t figure out what is wrong. I know that it must be something minor as my code is very similar to some of the others that i’ve read. Just trying to figure out my mistake.

When I go to run this I get no query results.

Select count(*)
from countries
where continent = "Africa"

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

Hi, in the first query you’re missing a semicolon after your WHERE clause.
Move the semicolon up a line so it follows 2005 in the second query.

Also, you can alias tables so your query isn’t so lengthy.

FROM population_years AS p 
JOIN countries AS c ON
p.country_id = c.id
1 Like