World Populations II Challenge Project (SQL)

Using ‘%The%’ can output for example ‘Netherlander’ as a result of the query, the Question asked for the WORD ‘The’, the article ‘The’, for example ‘The Congo’.
Using ‘% The %’ will look for the word ‘The’, but it will not output the result ‘The Congo’, is no space before ‘The’ in ‘The Congo’.
Also the data was store as ‘Congo, The’ in the countries table, using % The%’ will output the result ‘Congo, The’, if the name of a country was stored, for example as ‘Congo, The beautiful’ ‘% The %’ will output it.

So the better query code in my opinion is

WHERE name  LIKE '% The %' OR name LIKE '% The' OR name LIKE 'The %'
1 Like

Ohhh I see ok noted.

1 Like

Hi there!

here’s my Project :wink:

Have a great day
Maria

Hello everyone! Here are my solution to this project :slight_smile: https://gist.github.com/53eb2ba9055b4e76fa8e8adab5ac2d47

My solution:

Hello everyone, please find my solution here :slight_smile:

I took some time off from this code because I feel like I wasn’t grasping the concepts well. Came back and it all works!

I completed the challenge! Here’s my code:

Thanks!

Here are my answers to the projects!

Here is my solution!

Below is my solution, I found out that WITH clause is very useful and I also surprise at your solutions guys, it looks neat.

P/S: sorry for inconvenient, don’t know why I can’t copy the link

3.How many entries in the “countries” table are from ‘Africa’
SELECT COUNT ()
FROM countries
WHERE continent = ‘Africa’;

  1. The total population of the continent of Oceania in 2005
    WITH population_Oceania_2005 AS
    (SELECT *
    FROM countries
    JOIN population_years
    ON countries.id = population_years.country_id)
    SELECT SUM(population) AS ‘Oceania_2005’
    FROM population_Oceania_2005
    WHERE continent = ‘Oceania’
    AND year = 2005;

5.The avg population of countries in SA in 2003
WITH avg_SA_2003 AS
(SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id)
SELECT AVG(population) AS ‘SA_2003’
FROM avg_SA_2003
WHERE continent = ‘South America’
AND year = 2003;

6.Country where had the smallest population in 2007
WITH country AS
(SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id)
SELECT name, MIN(population)
FROM country
WHERE year = 2007;

7.Average population of Poland
WITH Avg_Poland AS
(SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id)
SELECT AVG(population) AS ‘Poland’
FROM Avg_Poland
WHERE name = ‘Poland’;

8.How many countries have the word ‘The’ in their name
WITH list AS
(SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id)
SELECT COUNT()
FROM list
WHERE name LIKE ‘%The%’;

9.The total population of each continent in 2010
WITH continent_2010 AS
(SELECT *
FROM countries
JOIN population_years
ON countries.id = population_years.country_id)
SELECT continent, SUM (population) AS ‘population_2010’
FROM continent_2010
WHERE year = 2010
GROUP BY continent;

Hope you all doing well… Here my solution.

I will be glad of receiving any advice or suggestion.

Hello Guys Here is my SQL Solutions on this topic
Suggestions on where i could do better are welcome

My answers are below