World Populations II Challenge Project (SQL)

I did the project in the Codeacademy setup instead of on my computer.
I got the first two codes right, and then it was unresponsive.
I have compared my code with the solution offered by codeacademy, and they are the same.
anyone know if the codeacademy is locked to one solution or if working in that environment has limitations? thanks

I was happy to see I got all the right results, I always wonder if I should space stuff out more or if long lines are okay :slight_smile:

My solutions.
I’m new to all of this and really enjoyed working this out.

HERE IS MY SOLUTION

Adding my solution to the list here:

I admit that I found myself over-thinking the questions. D’oh! Here’s my code. :floppy_disk:

FROM countries
LIMIT 10;

SELECT *
FROM population_years
LIMIT 10;*/

/*Q3*/

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

/*Q4*/

SELECT SUM(population)
FROM population_years AS p
JOIN countries AS c ON
p.country_id = c.id
WHERE continent = "Oceania"
AND year = 2005;

/*Q5*/
SELECT AVG(population)
FROM population_years AS p
JOIN countries AS c ON
p.country_id = c.id
WHERE continent = "South America"
AND year = 2003;


/*Q6*/
SELECT MIN(population), name
FROM population_years AS p
JOIN countries AS c ON
p.country_id = c.id
WHERE year = 2007;

/*Q7*/

SELECT AVG(population)
FROM population_years AS p
JOIN countries AS c
ON p.country_id = c.id
WHERE name = "Poland";

/*Q8*/
SELECT *
FROM countries
WHERE name LIKE "%The%";

/*Also:*/
SELECT COUNT(*)
FROM countries
WHERE name LIKE "%The%";

/*Q9*/
SELECT continent, SUM(p.population)
FROM population_years AS p
JOIN countries AS c
ON
p.country_id = c.id
WHERE year = 2010
GROUP BY continent;

Below I share the answers for the “World Populations SQL Practice II”.

If you have any question or something to add, please make sure to contact me or just reply the post.

Thanks! :grinning:

Hi! Here is my solution to the World Populations II Challenge Project (SQL):

Hello everyone,

here is the link to my codecademy repository:

The files relevant to this project can be found here:

./sql-population-queries-part-ii-starting

In this subfolder you will find my solution sql file as well as the provided solution. In addition you will find the database that is been used and the results of both sql files as .txt documents.

I have one thing to mention:
The first question is, in my opinion, ambiguous. I counted respective database entries in population_years rather than in countries.

I would really like your thoughts on this one as well as maybe a statement from @annajiali.

Hi everybody, here’s my solution to the exercise:

My code ^^

-----------------------------World Populations SQL Practice II-----------------------------------------

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

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

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

select population_years.year,
population_years.population,
countries.continent
from population_years
join countries
on population_years.country_id = countries.id
where year = 2007 and population is not null order by population ASC limit 1 ;

select avg(population_years.population) as ‘Poland_avg_population’,
countries.name from population_years join countries
on population_years.country_id = countries.id
where name = ‘Poland’;

select count(*) as ‘The_name_country’ from countries where name like ‘%The%’ ;

select population_years.year,
sum(population_years.population) as ‘total_population’,
countries.continent from population_years join countries
on population_years.country_id = countries.id where year = 2010 group by continent order by sum(population_years.population) DESC;

this is my code for this project

Hi, all! This is my solution.
https://gist.github.com/3a42fad1b036bce95725caf609800717
/* How many entries in the countries table are from Africa?

56 */

select count(*)

from countries

where continent like ‘africa’;

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

33.0*/

select round(sum(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.continent like ‘Oceania’

and population_years.year = 2005;

/* What is the average population of countries in South America in 2003?

26.0*/

select round(avg(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.continent like ‘south america’

and population_years.year = 2003;

/* What country had the smallest population in 2007?

Niue, 0.00216*/

select countries.name, min(population_years.population)

from countries

join population_years

on countries.id = population_years.country_id

where population_years.year = 2007;

/* What is the average population of Poland during the time period covered by this dataset?

39.0*/

select round(avg(population_years.population),0)

from countries

join population_years

on countries.id = population_years.country_id

where countries.name like ‘poland’;

/* How many countries have the word “The” in their name?

4*/

select count(*)

from countries

where name like ‘%the%’;

/* What was the total population of each continent in 2010?

continent population

Asia 4133.0

Africa 1015.0

Europe 723.0

North America 540.0

South America 397.0

Oceania 35.0*/

select countries.continent, round(sum(population_years.population),0) as ‘population’

from countries

join population_years

on countries.id = population_years.country_id

where population_years.year = 2010

group by countries.continent

order by 2 desc;

1 Like

Here is my GitHub link https://github.com/AzawStephens/World-population-SQL-project

Here is my code, although on question 6, I think that the question should describe better if null values should be considered and in question 8, if the word has to be alone or if the word the in Netherlands, for instance should be considered as well.

Here’s mine
SELECT COUNT(*)
FROM countries
WHERE continent = ‘Africa’;

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

SELECT AVG(population)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE continent = ‘South America’
AND year = 2003;

SELECT name, population
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE year = 2007
AND population IS NOT NULL
ORDER BY population ASC
LIMIT 1;

SELECT name, ROUND(AVG(population),1)
FROM countries
JOIN population_years
ON countries.id = population_years.country_id
WHERE name = ‘Poland’;

SELECT COUNT(name)
FROM countries
WHERE name LIKE ‘% The%’;

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

Here’s my code: https://gist.github.com/codecademydev/1b61c6ec31aea66ed7973fd901221de4

Cheers!

Here’s the link to my code - https://gist.github.com/ec4d638a6725be7b32d3dc8c9a36ce4f