不忽略空值null的聚合函数_sqlzoo练习题答案聚合函数和空值
部分
1 SUM and COUNT
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(*)
FROM world
WHERE area >= 1000000
5 (‘France’,’Germany’,’Spain’)（“法国”，“德国”，“西班⽛”）的总⼈⼝是多少？
SELECT SUM(population)
FROM world
WHERE name IN ('France','Germany','Spain')
6 对于每⼀个洲份，显⽰洲份和国家的数量。
SELECT continent,COUNT(*)
FROM world
GROUP BY continent
7 对于每⼀个洲份，显⽰洲份和⾄少有1000万⼈(10,000,000)⼝国家的数⽬。
SELECT continent,COUNT(*)
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
2 The nobel table can be ud to practice more SUM and COUNT functions.
1 找出总共有多少个奖颁发了。
SELECT COUNT(*)
FROM nobel
2 列出每⼀个奖项(subject), 只列⼀次
SELECT DISTINCT subject
FROM nobel
3 找出物理奖的总颁发次数。
SELECT COUNT(subject)
FROM nobel
WHERE subject = 'Physics'
4 对每⼀个奖项(Subject)，列出颁发数⽬。
SELECT subject,COUNT(*)
FROM nobel
GROUP BY subject
5 对每⼀个奖项(Subject)，列出⾸次颁发的年份。
SELECT subject,yr
FROM nobel n1
WHERE yr <= (
SELECT MIN(yr) FROM nobel n2
WHERE n1.subject = n2.subject)
GROUP BY subject,yr
6 对每⼀个奖项(Subject)，列出2000年颁发的数⽬。
SELECT subject,COUNT(*)
FROM nobel
WHERE yr = 2000
GROUP BY subject
空虚寂寞
7 对每⼀个奖项(Subject)，列出有多少个不同的得奖者
什么奶粉比较好
SELECT subject,COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject
这个题⽬关键在于列出不同的获奖者数量，由于可能存在⼀个⼈多次获得同⼀奖项，所以需要使⽤DISTINCT 去重。
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(*) > 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
3 Using Null
1 列出学系department是NULL值的⽼师。
SELECT name
FROM teacher
WHERE dept IS NULL
2 注意INNER JOIN 不理会没有学系的⽼师及没有⽼师的学系。
SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept
ON (teacher.dept=dept.id)
3 使⽤不同的JOIN(外连接)，来列出全部⽼师。
SELECT t.name,d.name
FROM teacher t
LEFT JOIN dept d ON t.dept = d.id
LEFT JOIN 也可以写成 LEFT OUTTER JOIN 两者相同
4 使⽤不同的JOIN(外连接)，来列出全部学系。
SELECT t.name,d.name
电工实训心得体会FROM teacher t
RIGHT JOIN dept d ON t.dept = d.id
5 U COALESCE to print the mobile number. U the number '0798
6 444 2266' if there is no number given.Show teacher name and mobile number or '07986 444 2266'
SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher
6 U the COALESCE function and a LEFT JOIN to print the teachernameand department name. U the string 'None' where there is no department.
SELECT teacher.name, COALESCE(dept.name, 'None')
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id
7 使⽤COUNT来计算⽼师和流动电话数⽬。
SELECT COUNT(name),COUNT(mobile)
FROM teacher
8 使⽤COUNT 和 GROUP BY dept.name来显⽰每⼀学系的⽼师数⽬。 使⽤ RIGHT JOIN 以确保⼯程系Engineering 是在当中。
SELECT COUNT(name)
FROM teacher t
RIGHT JOIN dept d ON t.dept = d.id
GROUP BY dept,name
得出的结果没错只是和答案顺序不⼀样
9 U CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwi.
SELECT name,
CASE WHEN dept = 1 OR dept = 2 THEN 'Sci'
ELSE 'Art' END
FROM teacher
10 U CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2, show 'Art' if the teacher's dept is 3 and 'None' otherwi.
SELECT name,
CASE WHEN dept = 1 OR dept = 2 THEN 'Sci'
祖国历史WHEN dept = 3 THEN 'Art'
鲜人参泡酒ELSE 'None' END
FROM teacher;
4 Scottish Parliament
1 ⼀个成员被⼯党逐出党，现没属任何党。找出他
SELECT name
FROM msp
WHERE party IS NULL
2 列出每个党及其领导⼈。
