select * from bbc
--1 获得和巴西'Brazil'同属一个地区的所有国家
select name from bbc a
where region=
(select region from bbc b where name = 'Brazil')
--2 人口多余俄国的国家列名称
select name from bbc a
where population>
(select population from bbc where name = 'Russia')
--3 给出'India','Iran'所在地区的所有国家的信息
select * from bbc a
where region in
(select region from bbc b where name='India'or name='Iran')
--4 给出人均GDP 超过‘United Kingdom’的欧洲国家
select name from bbc a
where region='Europe' and
gdp/population >
(select gdp/population from bbc b where name='United Kingdom')
group by name
--5 人口比加拿大多 但少于Algeria 的国家的信息
select * from bbc a
where population>
(select population from bbc b where name='Canada' )
and population < (select population from bbc b where name='Algeria' )
--6 gdp比任何欧洲国家都多的国家
select name from bbc a
where gdp >(select max(gdp) from bbc b where region='Europe')
group by name
--7 每个地区人口最大的国家
select name from bbc a
where population=
(select max(population)from bbc b where a.region=b.region)
--8 给出地区中所有国家的人口总数为0 的地区
select region from bbc a
where (select max(population)from bbc b where a.name=b.name)=0
--9 有些国家人口数比他的周边国家要多余三倍 列出这些国家和地区
select name, region from bbc a
where population>3*(select max(population) 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)