SQLZOO:SUM and COUNT/zh&The nobel table can be used to practice more SUM and COUNT functions./zh 笔记

SUM and COUNT/zh

name:国家名称
continent:洲
area:面积
population:人口
gdp:国内生产总值

开始查询world表

1、展示世界的总人口。

SELECT SUM(population)
FROM world

2、列出所有的洲, 每个只有一次。

SELECT DISTINCT continent
FROM world

3、找出非洲(Africa)的GDP总和。

SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa'

4、有多少个国家具有至少百万(1000000)的面积。

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

5、(‘France’,‘Germany’,‘Spain’)（“法国”，“德国”，“西班牙”）的总人口是多少？

SELECT SUM(population)
FROM world
WHERE name IN ('France', 'Germany', 'Spain')

6、对于每一个洲，显示洲和国家的数量。

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

7、对于每一个洲，显示洲和至少有1000万人(10,000,000)口国家的数量。

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

8、列出有至少100百万(1亿)(100,000,000)人口的洲。

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

---

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

yr: 年份
subject: 奖项
winner: 得奖者

开始查询nobel表

1、找出总共颁发了多少个奖。

SELECT COUNT(winner) FROM nobel

2、列出每一个奖项(subject), 只列一次

SELECT DISTINCT subject
FROM nobel

3、找出物理奖的总颁发次数。

SELECT COUNT(winner)
FROM nobel
WHERE subject = 'Physics'

4、对每一个奖项(Subject),列出颁奖数目。

SELECT subject, COUNT(winner)
FROM nobel
GROUP BY subject

5、对每一个奖项(Subject),列出首次颁发的年份。

SELECT subject, MIN(yr)
FROM nobel
GROUP BY subject

6、对每一个奖项(Subject),列出2000年颁发的数目。

SELECT subject, COUNT(winner)
FROM nobel
WHERE yr = 2000
GROUP BY subject

7、对每一个奖项(Subject),列出有多少个不同的得奖者。

SELECT subject, COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject

8、对每一个奖项(Subject),列出有多少年曾颁发过。

SELECT subject, COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject

9、列出哪年有3个物理奖Physics得奖者。

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

10、列出谁得奖多于一次。

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(winner) > 1

11、列出谁获得多于一个奖项(Subject)

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1

12、哪年哪奖项，是同一奖项(subject)颁发给3个人。只列出2000年及之后的资料。

SELECT yr, subject
FROM nobel
WHERE yr >= 2000
GROUP BY yr, subject
HAVING COUNT(winner) = 3