World Populations II Challenge Project (SQL)

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.

Here are my solutions for the World Populations II challenge:

id name continent id population year country_id
1 Bermuda North America 1 0.06306 2000 1
1 Bermuda North America 2 0.06361 2001 1
1 Bermuda North America 3 0.06418 2002 1
1 Bermuda North America 4 0.06476 2003 1
1 Bermuda North America 5 0.06534 2004 1
count (*)
56
count (continent)
17
avg (population)
25.8906514285714
id name continent id population year country_id
62 Former Serbia and Montenegro Europe 668 2007 62
200 Niue Oceania 2186 0.00216 2007 200
avg (population)
38.5606790909091
count(name)
0
continent sum(population) year
Africa 9972.25333 2010
Asia 43060.62809 2010
Europe 7991.48186 2010
North America 5643.75671000001 2010
Oceania 359.47808 2010
South America 4096.11401 2010

Hi Everyone!
Here’s my solution.

Hi everyone, I am sharing my link for the challenge. There are a few hiccups here and there like the one where i tried to group by continent for the Oceania question. Not the most efficient way to go about it as its a bit redundant :slight_smile:

https://gist.github.com/4da9f28c0133440e93fd37a92eddad4c

Hi everyone!

A link to my solutions to the questions are here

Hello Everyone,

Link to my solution for questions this project:
Github link

Hello, everyone
The trickiest code was to discover the number of countries with the word ‘The’ in its name. I approached this issue by first knowing all the countries with, at least, ‘%the%’ in its name, so that after I could filter out the ones with ‘the’ in the middle of the name, like ‘Netherlands’. It was mostly a trial and error operation. Feel free to comment if something is not optimal.

My solution is here: https://github.com/kamidbar/codecademy_solutions/blob/master/world_population_solution.sql

Hi, all my solutions were the same except number 6, which I did according to the below. This returns Former Serbia and Montenegro, which has a value of 0, but using the min function returns Niue, which has a population of 0.26M. How would you decide to use the min function? Does the min function not return null values?

select population, countries.name from population_years join countries on countries.id = population_years.country_id where year = 2007 order by population ASC limit 1;

The reason your solution didn’t work is that Serbia and Montenegro did not exist in 2007, therefore population in population_years has a null value in the year 2007.

Your code would work if you added “AND population IS NOT NULL” to your WHERE clause.

The MIN operator ignores NULL values.

For JOINs, I’m still unclear on when you have to specify which table you’re pulling from, as in “population_years.year” vs “year” by itself.

Also, for the last question, ORDER BY population does not actually put them in correct order, and I don’t know why

I tried some complicated method first, but it did not work. Here is the final.

Hi! Here is my solution, with a few “verification steps” added (coding vocabulary missing here)!

Be careful: one country’s population is missing from the database (see my answer to question 6 and 9) :wink:

If anyone finds any verification step that should be added in there, I’d love to hear about them!

Mehn i am so happy i was able to do this,it was so long i couldn’t believe it.

SELECT COUNT(*) FROM countries WHERE continent = ‘Africa’;
SELECT SUM(population_years.population) AS ‘TOTAL POP OF OCEANIA IN 2005(millions)’ FROM population_years JOIN countries ON population_years.country_id = countries.id WHERE population_years.year = ‘2005’ AND countries.continent = ‘Oceania’;

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 countries.name,MIN(population_years.population) AS 'Smallest populations’FROM countries JOIN population_years ON countries.id = population_years.country_id WHERE population_years.year = 2007 GROUP BY 1 ORDER BY 2 ASC LIMIT 2;
SELECT DISTINCT year FROM population_years ORDER BY 1 ASC;
SELECT ROUND(AVG(population_years.population), 2) AS ‘AVERAGE POP OF POLAND 2000 to 2010’ FROM population_years JOIN countries ON countries.id = population_years.country_id WHERE population_years.year BETWEEN ‘2000’ AND ‘2010’ AND countries.name = ‘Poland’;

SELECT COUNT(*) FROM countries WHERE name LIKE ‘%The%’;
SELECT countries.name, 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 1 ORDER BY 2 DESC;

My solution:

select * from countries;
select * from population_years;

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;

select avg(population)
from population_years join countries
on population_years.country_id = countries.id
where continent = ‘South America’ and year = 2003;

select min(population), country_id, continent
from population_years join countries
on population_years.country_id = countries.id
where year = 2007;

select avg(population)
from countries join population_years
on population_years.country_id = countries.id
where continent = ‘Poland’;

select count(*)
from countries
where continent like ‘%The%’;
select continent, sum(population)
from countries join population_years
on population_years.country_id = countries.id
where year = 2010
group by continent;

Hey everyone,

that is my solution: