Quick Ref.

Nobel Prizes: Aggregate functions

This tutorial concerns aggregate functions such as COUNT, SUM and AVG.

Exercises

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

1a. Show the total number of prizes awarded.
nobel(yr, subject, winner)

Results
1b. List each subject - just once
nobel(yr, subject, winner)

Results
1c. Show the total number of prizes awarded for Physics
nobel(yr, subject, winner)

Results

Using GROUP BY and HAVING.

2a. For each subject show the subject and the number of prizes.
nobel(yr, subject, winner)

Results
2b. For each subject show the first year that the prize was awarded.
nobel(yr, subject, winner)

Results
2c. For each subject show the number of prizes awarded in the year 2000.
nobel(yr, subject, winner)

Results

Look into aggregates with DISTINCT.

3a. Show the number of different winners for each subject.
nobel(yr, subject, winner)

Results
3b. For each subject show how many years have had prizes awarded.
nobel(yr, subject, winner)

Results

Use HAVING.

4a. Show the years in which three prizes were given for Physics.
nobel(yr, subject, winner)

Results
4b. Show winners who have won more than once.
nobel(yr, subject, winner)

Results
4c. Show winners who have won more than one subject.
nobel(yr, subject, winner)

Results

GROUP BY yr, subject

5a. Show the year and subject where 3 prizes were given. Show only years 2000 onwards.
nobel(yr, subject, winner)

Results