CODEWITHSUNDEEP
mysqlsql
user119020
user119020

Reputation: 423

tricky sql query for the given table

given a table similar to the one below

+-------------+-----------+---------+------------+--------------+
| name        | continent | area    | population | gdp          |
| Afghanistan | Asia      | 652230  | 25500100   | 20343000000  |
| Albania     | Europe    | 28748   | 2831741    | 12960000000  |
| Algeria     | Africa    | 2381741 | 37100000   | 188681000000 |
| Andorra     | Europe    | 468     | 78115      | 3712000000   |
| Angola      | Africa    | 1246700 | 20609294   | 100990000000 |
+-------------+-----------+---------+------------+--------------+

What is the query needed to get the following result.

Some countries have populations more than three times that of any of their neighbors (in the same continent). Give the countries and continents.

EDIT

This is what i have tried so far.

SELECT name, continent FROM 
world x
WHERE x.name = ALL(SELECT name FROM world y where y.continent = x.continent and (x.population/y.population) > 3)

The above query does not return any records :(

And to add this is not home work. I am trying to brush up SQL for back end developer interview.(have not used SQL for a while instead used object relational mapping frameworks)

Upvotes: 0

Views: 554

Answers (2)

Nitin
Nitin

Reputation: 290

Try This one:-

SELECT name, continent FROM world where name not in(
   SELECT name FROM world a where name =
     ALL(SELECT name FROM world b where a.continent =b.continent and a.population>b.population*3)
)

Upvotes: 0

Rajendra Kalepu
Rajendra Kalepu

Reputation: 97

Try This query

SELECT name, continent
FROM countries x
WHERE x.population > ALL (SELECT population * 3 FROM countries y WHERE x.continent = y.continent AND x.name <> y.name);

Upvotes: 0

Related Questions