本文中主要是对
sum and count进行了一个小测，熟悉SQL中聚合函数的使用
Sum and Count Quiz
image-20200115233840316
练习
- Select the statement that shows the sum of population of all countries in 'Europe'
欧洲所有国家的总人口
select sum(population)
from bbc
where region='Europe';
- Select the statement that shows the number of countries with population smaller than 150000
统计人口小于150000的国家总数
select count(name)
from bbc
where population < 150000;
- Select the list of core SQL aggregate functions
列出SQL中aggregate函数，返回的是单一结果的函数
AVG(), COUNT(), CONCAT(), FIRST(), LAST(), MAX(), MIN(), SUM()
- Select the result that would be obtained from the following code，根据代码选择结果
select region, sum(area)
from bbc
where sum(area) > 15000000 -- 错误的写法
group by region;
- area总数大于15000000；（写法错误）
- 根据地区region进行分组
原因：where无法对区域总和进行分组，需要使用having来过滤行
正确写法：
select region, sum(area)
from bbc
group by region
having sum(area) > 15000000; --使用having进行过滤分组
- Select the statement that shows the average population of 'Poland', 'Germany' and 'Denmark'
求解3个国家的平均人口数
select avg(population)
from bbc
where name in ('Poland', 'Germany', 'Denmark')
- Select the statement that shows the medium population density of each region
显示每个region的平均人口密度
select region, sum(population)/sum(area) as density
from bbc
group by region;
- 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); -- 子查询中现将最大的人口数的国家选出来
- Pick the result that would be obtained from the following code
select region, sum(area)
from bbc
group by region
having sum(area) <= 20000000;
- 先求出每个
region的人口总数 - 再把人口总数小于等于
2000000的过滤掉
image
