Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
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

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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