2

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

  • 4
    Giving you the answer isn't going to improve your abilities. If you post what you tried, and why it seemed like a good idea, someone here can probably show you more "SQL" ways of thinking. – Mike Sherrill 'Cat Recall' Jul 24 '11 at 17:34
  • 1
    To get a good answer, post the schema scripts to create tables and sample data. (Be aware that this sounds like a homework question.) – Brent Ozar Aug 1 '11 at 12:11
3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.