SQL语句练习（二）

表名：bbc

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

• name
• region
• area
• population
• gdp

1. 给出人口多于Russia（俄国）的国家名称

SELECT name FROM bbc
  WHERE population>
     (SELECT population FROM bbc
      WHERE name='Russia')

 

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比任何欧洲国家都多的国家（只要显示国家名称）．

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

 

6.给出每个地区人口最大的国家：

SELECT region, name, population FROM bbc x
  WHERE population >= ALL
    (SELECT population FROM bbc y
        WHERE y.region=x.region)

 

或者：

SELECT name FROM bbc x WHERE population = (SELECT max(population) FROM bbc y WHERE x.region = y.region)

 

或者：

SELECT name FROM bbc WHERE population IN (SELECT max(population) FROM bbc GROUP BY region)

 

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

 

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

 

或者：

SELECT region FROM bbc GROUP BY region HAVING sum(population) = 0

 

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

 

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)

 

或者

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)