CODEWITHSUNDEEP
sql
kreativred
kreativred

Reputation: 63

Find the countries with a GDP greater than a group of countries

I'm attempting question 5 of SQLZoo's SELECT within SELECT tutorial

Which countries have a GDP greater than any country in Europe? [Give the name only.]

This is my solution, which is incorrect but I don't understand why.

SELECT name  
  FROM world  
 WHERE gdp > ALL (SELECT gdp FROM world WHERE continent ='Europe')

What am I doing incorrectly?

Upvotes: 6

Views: 16815

Answers (12)

AGHOGHO OMAMOHWO
AGHOGHO OMAMOHWO

Reputation: 1

SELECT name
    FROM world
    WHERE gdp > ALL(SELECT gdp
                   FROM world 
                   WHERE gdp and continent='Europe')

Upvotes: -1

S_V
S_V

Reputation: 1

Get the country with max GDP in Europe via the inner query and then use outer query to get countries having more GDP than the result obtainer from inner query.

select name
  from world
 where gdp>
(
select max(gdp)
  from world
 where continent='Europe'
)

Upvotes: -1

susam das
susam das

Reputation: 1

SELECT name FROM world WHERE GDP > ALL(SELECT GDP FROM world WHERE continent='Europe' and GDP>0 )

Null value problem

Upvotes: -1

Shahzeb Ali
Shahzeb Ali

Reputation: 1

Try this:

select name 
from world
where gdp >= all(
    select gdp
    from world
    where 
    continent = 'Europe' and gdp is not null)
and continent <>'Europe'

Upvotes: 0

Saad Alkhaleeli
Saad Alkhaleeli

Reputation: 1

SELECT name FROM world
WHERE gdp >(SELECT MAX(gdp) FROM world WHERE continent = 'Europe' OR gdp > NULL);

You only need to use the OR statement as some countries come with gdp = NULL and that's what make your code error.

Upvotes: 0

Vineet Sajwan
Vineet Sajwan

Reputation: 196

select name, gdp 
from world x 
where gdp > all (select gdp from world y where continent = "Europe" and gdp > 0)

you were missing a NULL value check because in case of null value unknown result will be fetched, see ref: http://dev.mysql.com/doc/refman/5.5/en/all-subqueries.html

world table might have null values for some European countries when you tested your query therefore it is good to put a check to avoid null values.

Upvotes: 11

Priyanshu Khandelwal
Priyanshu Khandelwal

Reputation: 1

Try this: We can select maximum out of all and then apply the greater than condition

select name from world where gdp > (select max(gdp) from world where continent = 'Europe')

Upvotes: 0

Pratyush
Pratyush

Reputation: 1

Since GDP has a null value so answer will be null if you compare any other value with null.

SELECT name FROM world
WHERE
GDP > ALL(SELECT COALESCE(GDP,0) FROM world WHERE continent = 'Europe');

In above if you pass COALESCE(GDP,0), that means you are passing GDP column and if any value is Null in GDP, then COALESCE function will return '0' (user provided value).

Passing 0 as a standard value for comparison as 0 is the minimum value.

Upvotes: 0

Javier Mar&#237;n
Javier Mar&#237;n

Reputation: 1

Try this:

SELECT name   FROM world  WHERE  gdp>= ALL(select gdp from world where continent='europe' and gdp>=0) and continent !='europe'

Upvotes: -1

Nikki Arroyo
Nikki Arroyo

Reputation: 1

I was going through the questions consecutively and for some reason Germany was in my mind from the previous question. So I had this in my query and got a correct answer. LOL Try it. I am using Chrome

select name from world where gdp > ALL( select gdp from world where name='Germany' and population<>0)

Upvotes: 0

made_in_india
made_in_india

Reputation: 2279

select name from world 
 where gdp > ALL(select gdp from world 
 where continent = 'Europe' 
   and gdp is not null)

Upvotes: 1

Lioness818
Lioness818

Reputation: 61

SELECT name 
  FROM world 
 WHERE gdp > (SELECT MAX(gdp) FROM world WHERE continent = 'Europe')

Using the MAX aggregate function returns the highest GDP from the sub-query which is what you want to compare with the outer query.

Upvotes: 6

Related Questions