这个版块的练习主要是SELECT的一些查询
表结构如下：
Show the winners with first name John
SELECT winner FROM nobel
WHERE winner LIKE 'JOHN%'
注意这里的like，用来查找“John”，还有题目里要求的“John”实际上是first name, 不能查找'%JOHN%'，这个意思是所有名字包含JOHN而不单单就指first name。
Show 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)
这里要注意的是where后的条件是用or连接的而非and,因为or这里取的是并集而不是交集。此外还要注意“”这里面不能有空格，会影响结果。
Show the year, subject, and name of winners for 1980 excluding Chemistry and Medicine
【解法一】这里涉及not in 的用法 （*表示选中所有列）
SELECT * FROM nobel
WHERE yr=1980
AND subject NOT IN ('Chemistry','Medicine')
【解法二】用的是不等号！=
SELECT * FROM nobel
WHERE yr=1980
AND subject!='Chemistry'
AND subject!='Medicine'
Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes
You can't put a single quote in a quote string directly. You can use two single quotes within a quoted string.
SELECT *FROM nobel
WHERE winner ='EUGENE O''NEILL'
用两个单引号代替，注意这里不是一个双引号“，而是两个单引号
Knights in order
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,winner ASC
这题是注意DESC ASC排序，以及ORDER BY中间用逗号连接
The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1.
Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
SELECT winner, subject FROM nobel
WHERE yr=1984
ORDER BY
CASE WHEN subject IN ('Chemistry','Physics')
THEN 1 ELSE 0
END ASC,subject,winner
subject in()条件是一知个Boolean表达式，1是true 0是false. 因此falses会显示在道trues的前面。意思就是如果subject不在('Physics','Chemistry')里面，结果就是false，应该显示在true的前面。所以Physics 和权 Chemistry会显示在最后
CASE WHEN一般用来分类，这题蛮精妙的
