Nobel Prizes: Aggregate functions

Using SUM, COUNT, MAX, AVG, DISTINCT and ORDER BY.

表如下:

SELECT * FROM nobel;

 

 

1,Show the total number of prizes awarded.统计有多少个获奖者

SELECT COUNT(subject) FROM nobel

1b. List each subject - just once 列出每个奖项-只显示一次

SELECT DISTINCT(subject) FROM nobel

1c. Show the total number of prizes awarded for Physics显示物理学奖的总数量

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

2a. For each subject show the subject and the number of prizes.为每个学科显示不同获奖者的数量

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

2b. For each subject show the first year that the prize was awarded显示为每个学科被授予奖项的第一年

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

2c. For each subject show the number of prizes awarded in the year 2000.显示为每个学科奖项的数量在公元二○○○年。

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

3a. Show the number of different winners for each subject.显示每个学科不同获奖者的数量。

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

3b. For each subject show how many years have had prizes awarded.为每个学科显示有多少年的奖项。

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