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
Hello everyone, please find my solution here
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!
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’;
- 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