SQL Tutorials - 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 2005 Forums

Transact-SQL (2005)

SQL Tutorials

Author

Topic

Donovan
Starting Member

8 Posts

Posted - 2007-02-11 : 14:32:05

Don't know what I'm doing wrong here. These seem to be pretty easy but I'm stumped.

Table looks like this.
bbc(name, region, area, population, gdp)

Question is:
List the name and region of countries in the regions containing 'India', 'Iran'.

So far I have this but it is saying I'm wrong.

SELECT name, region FROM bbc
WHERE region IN(SELECT region 
		FROM bbc 
		WHERE region = 'India' OR 'Iran')

It says I should return 51 records.

The next question is:

Show the European countries with a per capita GDP greater than 'United Kingdom'.

So far I have this:

SELECT name FROM bbc WHERE region = 'European' AND population/gdp > (SELECT population/gdp FROM bbc WHERE name = 'United Kingdom')

But is says I should return 7 rows.

So far I am not returning any rows in either query.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 14:52:06

In you first query, it doesn't make sense that the name of the region would be 'India' or 'Iran'. More than likely, you have a Country table with a region name, code, or ID in it.

In the second query, shouldn't the calculation be GDP/population, not population/GDP?

You should really ask your teacher for help with your homework. That's what they get paid for.

CODO ERGO SUM

Donovan
Starting Member

8 Posts

Posted - 2007-02-11 : 15:03:44

Made some changes.
Yeah they were stupid mistakes.

Thanks for the smart ass answer, but I'm not in school.

SELECT name, region FROM bbc
WHERE name IN(SELECT name
		FROM bbc 
		WHERE name = 'India' OR 'Iran')


SELECT name FROM bbc 
WHERE region = 'European'
AND gdp/population > (SELECT gdp/population
		      FROM bbc 
		      WHERE name = 'United Kingdom')

Still not returning expected results.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-11 : 15:11:21

also, is your region really called 'european' or is it called 'europe'?

-ec

EDIT:
a link to the table DDL that includes the INSERT statements would help. Or are you using the standard country.sql that I found here: http://www-users.cs.umn.edu/~mckoskey/CSCI5708_databases/mysql/country.sql

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2007-02-11 : 15:12:41

are you using SQL Server or some other database? MySql perhaps? the reason I ask, is that your OR syntax is a little wrong for SQL Server.

-ec

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-02-11 : 15:14:09

OK, I won't bother you with anymore smartass help.

CODO ERGO SUM

Donovan
Starting Member

8 Posts

Posted - 2007-02-11 : 15:27:51

quote:
Originally posted by eyechart

also, is your region really called 'european' or is it called 'europe'?

That was it. Still working on the other one. This is a tutorial I found at http://sqlzoo.net

Donovan
Starting Member

8 Posts

Posted - 2007-02-11 : 16:44:37

quote:
Originally posted by eyechart
EDIT:
a link to the table DDL that includes the INSERT statements would help. Or are you using the standard country.sql that I found here: http://www-users.cs.umn.edu/~mckoskey/CSCI5708_databases/mysql/country.sql

That looks like the table I am using. This is all done online from the http://sqlzoo.net tutorial site.

Donovan
Starting Member

8 Posts

Posted - 2007-02-11 : 16:50:14

This whole section is about nested queries.

Which country has a population that is more than Canada but less than Algeria?

I don't think I can use BETWEEN here.

Something like:

SELECT name FROM bbc 
WHERE population >((SELECT population 
			FROM bbc 
			WHERE name = 'Canada')
			AND < (SELECT population 
			FROM bbc 
			WHERE name = 'Algeria'))

Took a shot at it but doesn't seem to be correct.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources