SQL语句练习（二）

wawa

表名：bbc
该表有５个字段（columns），又称为属性（attributes） .

[li]name [/li][li]region [/li][li]area [/li][li]population [/li][li]gdp [/li]

1. 给出人口多于Russia（俄国）的国家名称
[pre]SELECT name FROM bbc
  WHERE population>
     (SELECT population FROM bbc
      WHERE name='Russia')

[/pre]

2. 给出'India'（印度）, 'Iran'（伊朗）所在地区的所有国家的所有信息．

select * from bbc where region in(select region from bbc where name in ('India','Iran'))

3. 给出人均GDP超过'United Kingdom'（英国）的欧洲国家．

select name from bbc where gdp/population>(select gdp/population from bbc where name='United Kingdom')and region='Europe'

4.哪个国家的人口比Canada（加拿大）多但少于Algeria（阿尔及利亚）？

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

5.给出GDP比任何欧洲国家都多的国家（只要显示国家名称）．
[pre]select name from bbc where gdp>all(select gdp from bbc where region='Europe')
[/pre]

6.给出每个地区人口最大的国家：
[pre]SELECT region, name, population FROM bbc x
  WHERE population >= ALL
    (SELECT population FROM bbc y
        WHERE y.region=x.region)

[/pre]

或者：
[pre]SELECT name FROM bbc x WHERE population = (SELECT max(population) FROM bbc y WHERE x.region = y.region)

[/pre]

或者：
[pre]SELECT name FROM bbc WHERE population IN (SELECT max(population) FROM bbc GROUP BY region)

[/pre]

7.给出地区中所有国家的人口总数为０的地区．

[pre]SELECT region FROM bbc WHERE 0 = ALL(SELECT sum(population) FROM bbc GROUP BY region)

[/pre]

或者：
[pre]SELECT region FROM bbc GROUP BY region HAVING sum(population) = 0

[/pre]

8.有些国家的人口数比她的周边国家（周边国家指在同一地区的国家）要多三倍，列出这些国家和地区．

[pre]select name,region
from bbc a where population > all (select population*3 from bbc b where a.region=b.region and a.name!=b.name)

[/pre]

或者
[pre]select name,region
from bbc a where population > all (select population*3 from bbc b where a.region=b.region and a.name<>b.name)

[/pre]