SQLZOO刷题记录5——SUM and COUNT

最新推荐文章于 2022-07-30 20:22:46 发布

pad45723

最新推荐文章于 2022-07-30 20:22:46 发布

阅读量808

点赞数

分类专栏： SQLZOO 文章标签： sql

本文链接：https://blog.csdn.net/pad45723/article/details/119728982

版权

5 篇文章

订阅专栏

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

题目7：Counting big countries in each continent

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

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

解题7:

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

题目8：Counting big continents

List the continents that have a 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 的关系。前者在分组之前进行筛选，后者在分组后进行筛选。

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)

题目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, subject
FROM nobel 
WHERE yr >= 2000 
GROUP BY yr, subject
HAVING COUNT(subject) = 3