Sql-server – Can’t retrieve data of countries and regions

sql server

Goal:
Improve my sql ability by doing some coding and reviewing existing code.

Problem:
I have spent lots of hour to find a solution based of this question
"3c. Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions. "

I tried finding different solution but unfortunately I can't solve this problem 🙁

The link can be found "http://sqlzoo.net/1a.htm"

Again, the purpose is to improve my ability in SQL!

// Fullmetalboy

Best Answer

Since you're trying to get into the SQL way of thinking, I'll try to break this down to explain how I got to the conclusion.

To find the answer behind this search, I tried to examine the data to see what I had to work with.

SELECT * FROM bbc

I found that what we really had to deal with was Population, Name (country name) and Region. The question stated that we need to find countries larger than others in the same region. So, I picked a random region and examined the data there:

SELECT * FROM bbc
WHERE region = 'South Asia'

Then I sorted the data by population so that I could try to understand what we're going to compare.

SELECT * FROM bbc
WHERE region = 'South Asia'
ORDER BY population DESC

If you're following from that page, you can see that India is dramatically larger than the next sized country, Pakistan.

Next, I (misunderstanding the question), found all countries that were three times the size of any other country in that region. However, I realized that question was really "three times the size of the smallest". So, I came up with

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population > 
        (SELECT MIN(b.population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.Region)
ORDER BY population DESC

There I'm finding the population where the size is greater than three times the minimum of that region. The correlated subquery (go research those) will limit the comparison to only select the populations in that region.

Finally, I realized that we're supposed to find the one that was three times larger than all other countries in the region (not just three times larger than ANY country in the region), so I changed my query to:

SELECT * FROM bbc
WHERE region = 'South Asia'
    AND population > 
        (SELECT MAX(population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.region 
           AND b.name <> bbc.name)
ORDER BY population DESC

That changed the "MIN" to "MAX" and excluded countries from the subquery that are the same country as the one we're comparing. So, when we're comparing India in the outer query, we're ignoring India in the inner query--thus finding the largest country that's Not India.

Finally, remove the "RegioN" from the outer where clause and only pull the two columns that they want (since they're picky):

SELECT name, region FROM bbc
WHERE population > 
        (SELECT MAX(population) * 3 
         FROM bbc AS b
         WHERE b.region = bbc.region 
           AND b.name <> bbc.name)
ORDER BY population DESC
Related Question