The nobel table can be used to practice more SUM and COUNT functions 习题答案

做SQLZOO时，发现The nobel table can be used to practice more SUM and COUNT functions（SUM and COUNT 有个选项可以进到这节） 这节没有没有人做出答案来。我就摸索了一下，把答案传上来。

（1）需要注意的是，where和group by……having是可以同时使用的，但是用法不同。where用于过滤分组前，是对原数据进行过滤，having用于过滤分组后，对分好的组进行过滤。因此只要分好是分组前操作还是分组后操作，这些题都比较好完成.
（2）where和group by……having+函数 是对各分组内进行函数运算

1. Show the total number of prizes awarded.

SELECT COUNT(subject) FROM nobel

2. List each subject - just once

SELECT DISTINCT subject FROM nobe

3.Show the total number of prizes awarded for Physics.

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

4.For each subject show the subject and the number of prizes.

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

5.For each subject show the first year that the prize was awarded.

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

6.For each subject show the number of prizes awarded in the year 2000.

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

7.Show the number of different winners for each subject.

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

8.For each subject show how many years have had prizes awarded.

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

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

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

10.Show winners who have won more than once.

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

11.Show winners who have won more than one subject.

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

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

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