SQLZOO 5.SUM-and-COUNT

World Country Profile: Aggregate functions

world

name	continent	area	population	gdp
Afghanistan	Asia	652230	25500100	20343000000
Albania	Europe	28748	2831741	12960000000
Algeria	Africa	2381741	37100000	188681000000
Andorra	Europe	468	78115	3712000000
Angola	Africa	1246700	20609294	100990000000
...

1.Total world population

列出全球总人口。

SELECT SUM(population)
FROM world

Correct answer

SUM(populatio..
7118632738

2.List of continents

列出大洲名称，只列一次。

SELECT distinct continent
FROM world

Correct answer

continent
Africa
Asia
Caribbean
Eurasia
Europe
North America
Oceania
South America

3.GDP of Africa

列出非洲总GDP。

SELECT sum(GDP)
FROM world
WHERE continent ='Africa'

Correct answer

sum(GDP)
1811788000000

4.Count the big countries

SELECT count(name)
FROM world
WHERE area  >=1000000

Correct answer

count(name)
29

5.Baltic states population

列出这些国家('Estonia', 'Latvia', 'Lithuania')的总人口

SELECT SUM(population )
FROM world
WHERE name IN ( 'Estonia', 'Latvia', 'Lithuania')

Correct answer

SUM(populatio..
6251750

6.Counting the countries of each continent

列出每个大洲的国家数量。

SELECT continent,count(name)
FROM world
GROUP BY continent

Correct answer

continent	count(name)
Africa	53
Asia	47
Caribbean	11
Eurasia	2
Europe	44
North America	11
Oceania	14
South America	13

7.Counting big countries in each continent

列出每个大洲中国家人口大于10亿的国家数量。

SELECT continent,count(name)
FROM world
WHERE population > 10000000
GROUP BY continent

Correct answer

continent	count(name)
Africa	29
Asia	26
Caribbean	2
Eurasia	1
Europe	14
North America	4
Oceania	1
South America	8

8.Counting big continents

列出大洲总人口数大于100亿的大洲。

SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population)>=100000000

Correct answer

continent
Africa
Asia
Eurasia
Europe
North America
South America