关于sqlzoo中的子查询的练习题~

00. 前言

练习地址：https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial

使用的为sqlzoo的MySQL库。

表名：world

字段名：name、continent、area、population、gdp

01. Bigger than Russia

List each country name where the population is larger than that of ‘Russia’.

列出人口大于“俄罗斯”的国家名称。

1
2
3
select name
from world
where population>(select population from world where name='Russia')

02.Richer than UK

Show the countries in Europe with a per capita GDP greater than ‘United Kingdom’.

列出人均GDP大于“英国”的欧洲国家。

1
2
3
4
select name
from world
where continent='Europe' 
and gdp/population>(select gdp/population from world where name='United Kingdom')

03.Neighbours of Argentina and Australia

List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

列出包括阿根廷或澳大利亚在内的各大洲的国家名称和大洲。按国家名称顺序排列。

1
2
3
4
select name,continent
from world
where continent in (select continent from world where name in ('Argentina','Australia'))
order by name

04.Between United Kingdom and Germany

Which country has a population that is more than United Kingdom but less than Germany? Show the name and the population.

哪个国家的人口比英国多但比德国少?显示名字和人口。

1
2
3
4
select name,population
from world
where population>(select population from world where name='United Kingdom')
and population<(select population from world where name='Germany')

05.Percentages of Germany

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

The format should be Name, Percentage for example:

德国(人口8000万)是欧洲人口最多的国家，奥地利(人口850万)占德国人口的11%。
显示欧洲各国的名称和人口。显示其人口占德国人口的百分比。
格式应为名称，百分比，例如:

1
2
3
4
5
6
7
8
9
10
11
12
13
方法一：
select 
name,
concat(round(population*100/(select population from world where name='Germany'),0),'%') percentage
from world
where continent='Europe'

方法二：
select 
name,
concat(cast(round(population/(select population from world where name='Germany'),2)*100 as signed),'%') percentage
from world
where continent='Europe'

06.Bigger than every country in Europe

Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)]

哪些国家的国内生产总值比欧洲所有国家都高?[只说出名字。(有些国家的gdp值可能为NULL)]

1
2
3
4
select name
from world
where gdp is not null
and gdp>(select max(gdp) from world where continent='Europe')

07.Largest in each continent

Find the largest country (by area) in each continent, show the continent, the name and the area:

找出每个大洲中最大的国家(按面积)，显示大洲、国名和面积:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
方法一：
select continent,name,area 
from world x
where area>=all(select area from world y where y.continent=x.continent and area>0)

方法二：
select continent,name,area
from world
where area in (select max(area) from world group by continent)

方法三：
select continent,name,area
from world
where (continent,area) in (select continent,max(area) from world group by continent)

08.First country of each continent (alphabetically)

List each continent and the name of the country that comes first alphabetically.

列出每个大洲以及按字母顺序排在第一个的国家的名字。

1
2
3
select continent,name 
from world x
where name=(select min(name) from world y where x.continent=y.continent)

09.Difficult Questions That Utilize Techniques Not Covered In Prior Sections

Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

找出所有国家人口都小于等于25000000的各大洲，然后找出与这些大洲相关联的国家名称，显示名称、大洲和人口。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
方法一：
select name,continent,population
from world x
where (select max(population) from world y where y.continent=x.continent)<=25000000
（思路：当一个大洲的最大人口都小于等于25000000时，
表示这个大洲的所有国家的人口数都会小于等于2500000。）

方法二：
select name,continent,population
from world
where continent in (select continent from world group by continent
having max(population)<=25000000)

方法三：
select name,continent,population
from world
where continent not in (select distinct continent from world where population>25000000)
(这里用取反是因为，当一个大洲有一个国家人口数小于等于25000000时，
不代表这个大洲的所有国家都满足这个条件。
所以直接先筛选出满足所有国家人口数都大于25000000的大洲，再取反，即可。)

10.Three time bigger

Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.

获取同一大洲，该国家人口大于等于其他所有国家3倍以上的国家名称和大洲名称。

1
2
3
4
5
6
select name,continent
from world x
where population>=all(
select population*3 
from world y 
where y.continent=x.continent and y.name!=x.name and population>0)