Thursday, September 13, 2007

SELECT within SELECT

1a. List each country name where the population is larger than 'Russia'.
SELECT name FROM bbc WHERE population> (SELECT population FROM bbc WHERE name='Russia')

1b. List the name and region of countries in the regions containing 'India', 'Iran'.
select name ,region from bbc where region in (SELECT region from bbc where name in ('India','Iran'))

1c. Show the European countries with a per capita GDP greater than 'United Kingdom'.
select name from bbc where region = (select region from bbc where name = 'United Kingdom') and gdp/population > (Select gdp/population from bbc where name = 'United Kingdom')

1d. Which country has a population that is more than Canada but less than Algeria?
select name from bbc where population > (Select population from bbc where name = 'canada') and population <(select population from bbc where name = 'algeria')

2a. Which countries have a GDP greater than any European country? [Give the name only.]
select name from bbc where gdp> (select max(gdp) from bbc where region = 'europe')

3a. Find the largest country in each region:
SELECT region, name, population FROM bbc x
WHERE population >= ALL
(SELECT population FROM bbc y
WHERE y.region=x.region
AND population>0)

No comments: