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)
页:
[1]
