Link to the page - https://sqlzoo.net/wiki/The_nobel_table_can_be_used_to_practice_more_SUM_and_COUNT_functions.
Show the total number of prizes awarded.
SELECT COUNT(winner) FROM nobel| COUNT(winner) |
|---|
| 895 |
select DISTINCT subject from nobel
order by subject| subject |
|---|
| Chemistry |
| Economics |
| Literature |
| Medicine |
| Peace |
| Physics |
Show the total number of prizes awarded for Physics.
select COUNT(winner) from nobel
where subject='physics'| COUNT(winner) |
|---|
| 201 |
For each subject show the subject and the number of prizes.
select subject, count(winner) from nobel
group by subject| subject | count(winner) |
|---|---|
| Chemistry | 172 |
| Economics | 76 |
| Literature | 112 |
| Medicine | 209 |
| Peace | 125 |
| Physics | 201 |
For each subject show the first year that the prize was awarded.
select subject, MIN(yr) from nobel
group by subject| subject | first awarded |
|---|---|
| Chemistry | 1901 |
| Economics | 1969 |
| Literature | 1901 |
| Medicine | 1901 |
| Peace | 1901 |
| Physics | 1901 |
For each subject show the number of prizes awarded in the year 2000.
where yr = 2000
group by subject
| subject | winners in 20.. |
|---|---|
| Chemistry | 3 |
| Economics | 2 |
| Literature | 1 |
| Medicine | 3 |
| Peace | 1 |
| Physics | 3 |
Show the number of different winners for each subject.
select subject, COUNT(DISTINCT winner) from nobel
group by subject| subject | COUNT(DISTINC.. |
|---|---|
| Chemistry | 171 |
| Economics | 76 |
| Literature | 112 |
| Medicine | 209 |
| Peace | 123 |
| Physics | 200 |
For each subject show how many years have had prizes awarded.
select subject, COUNT(DISTINCT yr) as "times awarded" from nobel
group by subject| subject | times awarded |
|---|---|
| Chemistry | 107 |
| Economics | 47 |
| Literature | 108 |
| Medicine | 106 |
| Peace | 92 |
| Physics | 109 |
Show the years in which three prizes were given for Physics.
select yr from nobel
where subject = 'physics'
GROUP BY yr
HAVING COUNT(winner)=3| yr |
|---|
| 1903 |
| 1956 |
| 1958 |
| 1963 |
| 1964 |
| ... |
Show winners who have won more than once.
select winner from nobel
GROUP BY winner
HAVING COUNT(winner) > 1| winner |
|---|
| Frederick Sanger |
| International Committee of the Red Cross |
| John Bardeen |
| Linus Pauling |
| Marie Curie |
Show winners who have won more than one subject.
select winner from nobel
group by winner
having count(DISTINCT subject) > 1| winner |
|---|
| Linus Pauling |
| Marie Curie |
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(subject) = 3| yr | subject |
|---|---|
| 2000 | Chemistry |
| 2000 | Medicine |
| 2000 | Physics |
| 2001 | Chemistry |
| 2001 | Economics |
| 2001 | Medicine |
| 2001 | Physics |
| ... | ... |