sql beginner - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

sql beginner

Author

Topic

thumsup9
Starting Member

7 Posts

Posted - 2005-03-30 : 11:04:15

Im working on sample exercises and im stuck with the following Question:

1.Find the region for which all countries have a population of 0.

I have a table cia with the following columns NAME, REGION, AREA ,POPULATION, GDP

(note: countries means column NAME)

I have the solution but not the query:

REGION
Antarctic Region
Antarctic Region
Antarctic Region
Antarctic Region
Antarctic Region
Number of records: 5

Thanks for help,

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-30 : 11:56:29

Select region
from cia
where POPULATION = 0

Jim
Users <> Logic

maquaro
Starting Member

6 Posts

Posted - 2005-03-30 : 12:04:06

If you have a specific question about SQL, we would be happy to help, but not give the query. Read the manual or ask your course instructor for additional help, and get back to us for additional help.

A good question would be: How do I test which countries have a population of 0?

A response would be: Consider using a WHERE statement.

Maquaro

maquaro
Starting Member

6 Posts

Posted - 2005-03-30 : 12:04:57

I stand corrected

TimS
Posting Yak Master

198 Posts

Posted - 2005-03-30 : 12:37:30

Select region, SUM(POPULATION) as ttl
from cia
GROUP BY region

Note: Lookup Having clause for the rest of answer

Tim S

thumsup9
Starting Member

7 Posts

Posted - 2005-03-30 : 12:43:42

Thanks JIML that was what I did earlier,but that gives all the Regions where population=0, but im looking for a Region in which all the countries have a population=0 ..

example: Region=Europe,Countries under it can be GBR,FRANCE,GERMANY etc..similarly Region=Africa and some countries under it..soI need to pull Europe if GBR,FRANCE,GERMANY population=0

HTH

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-30 : 13:25:20

Select Region
from cia
Group by Region
Having Sum(Population) = 0

Jim
Users <> Logic

thumsup9
Starting Member

7 Posts

Posted - 2005-03-30 : 13:50:03

Exactly JIML,that gets me very close to the solution.your code gives me

REGION
Antarctic Region
Number of records: 1

whereas the solution is

REGION
Antarctic Region
Antarctic Region
Antarctic Region
Antarctic Region
Antarctic Region
Number of records: 5

note: Nested block might help this,but i cudnt get there.

Thanks.

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-03-30 : 14:14:56

Select Region
from cia
where Region In

(Select Region
from cia
Group by Region
Having Sum(Population) = 0)

Jim
Users <> Logic

thumsup9
Starting Member

7 Posts

Posted - 2005-03-31 : 10:13:34

Well Done JIML--That gets the correct result.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources