关于sqlzoo中的聚合函数的练习题,包含sum、count、max、distinct和order by~

00. 前言

练习地址:https://sqlzoo.net/wiki/SUM_and_COUNT

使用的为sqlzoo的MySQL库。

表名:world

字段名:name、continent、population、gdp

01.Total world population

Show the total population of the world.

显示世界人口总数。

1
2
select sum(population) 世界总人口
from world

02.List of continents

List all the continents - just once each.

列出所有大洲-每个大洲只列出一个。

1
2
3
select continent
from world
group by continent

03.GDP of Africa

Give the total GDP of Africa.

给出非洲的GDP总量。

1
2
3
select sum(gdp) 非洲gdp总和
from world
where continent='Africa'

04.Count the big countries

How many countries have an area of at least 1000000.

有多少国家的面积至少有1000000。

1
2
3
select count(name)
from world
where area>=1000000

05.Baltic states population

What is the total population of (‘Estonia’, ‘Latvia’, ‘Lithuania’).

‘Estonia’, ‘Latvia’, ‘Lithuania’的总人口为多少。

1
2
3
select sum(population) 三个国家总人口数
from world
where name in ('Estonia', 'Latvia', 'Lithuania')

06.Counting the countries of each continent

For each continent show the continent and number of countries.

对于每个大洲显示大洲和国家数目。

1
2
3
select continent,count(name)
from world
group by continent

07.Counting big countries in each continent

For each continent show the continent and number of countries with populations of at least 10 million.

对于每个大洲,显示人口至少1000万的大洲和国家数目。

1
2
3
4
select continent,count(name) 国家数量
from world
where population>=10000000
group by continent

08.Counting big continents

List the continents that have a total population of at least 100 million.

列出总人口至少一亿的各大洲。

1
2
3
4
select continent
from world
group by continent
having sum(population)>=100000000