SQLZoo/05. SUM and COUNT from Nobel.sql at develop · Ankononenko/SQLZoo · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
/*
1. Show the total number of prizes awarded.
*/

SELECT count(winner)
FROM nobel

/*
2. List each subject - just once
*/

SELECT DISTINCT (subject)
FROM nobel

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

SELECT count(subject)
FROM nobel
WHERE subject = 'Physics'

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

SELECT subject
	,count(subject) AS num_prizes
FROM nobel
GROUP BY subject

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

SELECT DISTINCT subject
	,min(yr)
FROM nobel
ORDER BY min(yr) DESC

/*
6. 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

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

SELECT DISTINCT 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(DISTINCT winner) = 3