World Country Profile: Aggregate functions（世界国家概况:综合功能）

题目7：Counting big countries in each continent

For eachcontinentshow thecontinentand number of countries with populations of at least 10 million.

（对于每个大洲，显示大洲和人口数量超过10000万的国家数量）

解题7:

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

题目8：Counting big continents

List the continents thathavea total population of at least 100 million.

（列出大洲，大洲的总人口数至少是100000万）

解题8：

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

注意：对比题目7和题目8，WHERE,GROUP BY 与 GROUP BY , HAVING 的关系。前者在分组之前进行筛选，后者在分组后进行筛选。

测试 SUM and COUNT Quiz

3. Select the list of core SQL aggregate functions（sql中的聚集函数）

AVG(), COUNT(), MAX(), MIN(), SUM()

4. Select the result that would be obtained from the following code:

SELECT region, SUM(area)FROM bbc WHERE SUM(area) > 15000000 GROUP BY region

5. Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark'

解题5：SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')

注意：SQL 中等于 后面是某一个特定值，而IN 后面可以是一个结果集。如果结果集只有一个值时“=”等价于“IN” ，如果结果集有多个值时，IN 等价于 多个 “=”或。

col = 'A' 等价于 col IN ('A')

col = 'A' or col ='B' or col ='C' 等价于 IN('A','B','C')

6. Select the statement that shows the medium population density of each region

查找每个区域的人口密度，注意此处的人口密度是针对区域来说，也就是区域内的指标总和。

SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region

7.Select the statement that shows the name and population density of the country with the largest population

查找人口数量最多的国家和人口密度

SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)

The nobel table can be used to practice more SUM and COUNT functions.

题目9：Show the years in which three prizes were given for Physics.

nobel(yr, subject, winner)

解题9：

SELECT yr FROM nobel WHERE subject = 'Physics' GROUP BY yr HAVING COUNT(winner) = 3

注意：根据题意的理解，确定WHERE ,GROUP BY 和 HAVING 中各自对应什么条件。

题目12：GROUP BY yr, subject

Show the year and subject where 3 prizes were given. Show only years 2000 onwards.

显示颁发3个奖项的年份和主题。只显示2000年及以后。

nobel(yr, subject, winner)

解题12：

SELECT yr, subjectFROM nobel WHERE yr >= 2000 GROUP BY yr, subjectHAVING COUNT(subject) = 3