Fork me on GitHub

sqlzoo练习6-子查询

select in select部分的小测quiz5个不同的字段信息

习题

  1. Select the code that shows the name, region and population of the smallest country in each region

每个地区人口最少的国家

1
2
3
4
5
select region, name, population
from bbc x
where population <= ALL(select population
from bbc y y.region=x.region
and population > 0);

相当于是把同一个地区中的人数进行对比,选择最少的那个

  1. Select the code that shows the countries belonging to regions with all populations over 50000

地区中每个国家的人数都超过了50000的地区region

1
2
3
4
5
6
select name, region, population
from bbc
where 50000 < ALL(select population
from bbc y
where x.region=y.region
and y.population>0); -- 保证y中的populaton大于0
  1. Select the code that shows the countries with a less than a third of the population of the countries around it

同一个地区中某个国家的人口小于其他国家的3分之一

1
2
3
4
5
6
select name, region
from bbc x
where population < ALL(select population / 3
from bbc y
where x.region=y.region
and x.name != y.name) -- 进行比较的两个国家名字不同
  1. 根据代码选择答案

人口比英国多

和英国在同一个地区

1
2
3
4
5
6
7
select name from bbc
where population > (select population
from bbc
where name='United Kingdom')
and region in (select region
from bbc
where name='United Kingdom');
  1. Select the code that would show the countries with a greater GDP than any country in Africa (some countries may have NULL gdp values).

比非洲所有的国家的gdp都要大;考虑有些国家没有gdp

1
2
select name from bbc
where gdp > (select max(gdp) from bbc where region='Africa'); -- 只需要比最大的gdp都要大即可
  1. Select the code that shows the countries with population smaller than Russia but bigger than Denmark

人口比俄罗斯小,比Denmark大

1
2
3
select name from bbc
where population < (select population from bbc where name = 'Russia')
and population > (select population from bbc where name = 'Denmark');
  1. 根据代码选答案
  • 比欧洲所以国家的人口都要多
  • 地区在南亚
1
2
3
4
5
select name from bbc
where population > all(select max(population) -- 大于最大的人口即可
from bbc
where region='Europe')
and region='South Asia';

本文标题:sqlzoo练习6-子查询

发布时间:2020年01月15日 - 17:01

原始链接:http://www.renpeter.cn/2020/01/15/sqlzoo%E7%BB%83%E4%B9%A06.html

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

Coffee or Tea