SqlZoo.net习题答案：Using nested SELECT.

习题地址：http://sqlzoo.net/1a.htm

 

表结构：　　bbc(name, region, area, population, gdp)

 

 

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 countries in Europe with a per capita GDP greater than 'United Kingdom'.

select name 
from bbc 
where region = 'Europe' 
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?

from bbc 
where population < (select population from bbc where name = 'Algeria') 
and population > (select population from bbc where name = 'Canada')

 

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

select name
from bbc
where GDP > all (select GDP from bbc where region = 'Europe')

 

3a.Find the largest country in each region, show the region, the name and the population:

select region, name, population
from bbc x
where population >= all (select population from bbc y where x.region = y.region and y.population is not null)

 

3b.Find each country that belongs to a region where all populations are less than 25000000. Show name, region and population.

select name, region, population
from bbc x
where 25000000 > all (select population from bbc y where x.region = y.region)

 

3c.Some countries have populations more than three times that of any of their neighbours (in the same region). Give the countries and regions.

select name, region
from bbc x
where x.population/3 > all (select y.population from bbc y where x.region = y.region and y.name <> x.name)