SQL/select_from_nobel.sql at master · ccamila/SQL · 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
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
--https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial--

--

(1) Change the query shown so that it displays Nobel prizes for 1950.

--

SELECT yr, subject, winner FROM nobel
  WHERE yr = 1950

--

(2) Show who won the 1962 prize for Literature.

--

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

--

(3) Show the year and subject that won 'Albert Einstein' his prize.

--

SELECT yr, subject FROM nobel
WHERE winner = 'Albert Einstein'

--

(4) Give the name of the 'Peace' winners since the year 2000, including 2000.

--

SELECT winner FROM nobel
WHERE subject = 'Peace' AND yr >= 2000

--

(5) Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

--

SELECT yr, subject, winner FROM nobel
WHERE subject = 'Literature' AND yr >= 1980 AND yr <= 1989

--

(6) Show all details of the presidential winners.

--

SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter', 'Barack Obama' )

--

(7) Show the winners with first name John

--

SELECT winner FROM nobel
WHERE winner LIKE 'John_%'

--

(8) SEw the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 1984.

--

SELECT yr, subject, winner FROM nobel
WHERE (subject = 'Physics' AND yr = 1980) OR (subject= 'Chemistry' AND yr= 1984)

--

(9)

--

SELECT yr,subject, winner FROM nobel
WHERE yr IN (1980) AND NOT subject IN ('Chemistry', 'Medicine')

--
     Show year, subject, and name of people who won a 'Medicine' prize in an early
(10) year (before 1910, not including 1910) together with winners of a 'Literature' prize in
     a later year (after 2004, including 2004)
--

SELECT yr, subject, winner FROM nobel
WHERE (subject = 'Medicine' AND yr < 1910) OR (subject = 'Literature' AND yr >= 2004)

--

(11) Find all details of the prize won by PETER GRÜNBERG

--

SELECT winner, yr, subject FROM nobel
WHERE winner LIKE 'Peter Gr%nberg'


--

(12) Find all details of the prize won by EUGENE 'O'NEILL

--

SELECT yr, subject, winner FROM nobel
WHERE winner LIKE 'Eugene O%Neill'

--

(13) List the winners, year and subject where the winner starts with Sir.
     Show the the most recent first, then by name order.

--

SELECT winner, yr, subject FROM nobel
WHERE winner LIKE 'Sir_%'
ORDER BY yr DESC

--

(14)

--