How to express this query..

[php_novice2007]

Hi all,

 

I'm doing a question from my text book and I'm having trouble with the following question:

 

Consider a table schema R(State, City, Popluation, GDP), express the query for "List all cities whose GDP is greater than the average of all State GDPs with population greater than 1 million" Make appropriate assumptions wherever necessary.

 

So far I've coem up with:

 

Select r1.City from R r1 where

r1.GDP >

(select r.State, avg(r.GDP) from R r

where r.Populatioin > 1000000

group by r.State)

 

I know this is wrong since my inner query should be returning a table of state and their average GDPs the the '>' operator is only for a single row...

 

Thanks for any help~!

[Illusion]

Select city  from R where gdp>(select avg(gdp) from R where population>100000);

[php_novice2007]

would select avg(gdp) from R where population>1000000 only give one number? I was thinking that all the cities might have popluation that is < 1000000 when you add them up and find out the popluation for the state if the sum is > 1000000 then get the average of teh GDP?

 

[Illusion]

try this

 

Select city  from R where gdp>(select avg(gdp) from R where state=ANY(select if(sum(population)>100000, state,'xx') from R group by state));

 

 

there might be a better way but this what i got..........

 

why don't u go for some normalization so that it coule be very easy when u need to retrive the data like this...............

 

[php_novice2007]

gosh thats complicated :S

 

Yeah this is just a question I'm trying to do from a text book so I don't have to real database or anything Guess they just want to chuck in a really hard question...