Quick
Ref.
Functions
- date
- number
- string
Data Types
- date
- number
- string
| Nobel Prizes: Aggregate functionsThis 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 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
|
| |
