SQLZOO-1

http://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

#14 

The expression subject IN ('Chemistry','Physics') can be used as a value - it will be 0 or 1. （ 如果subject in ('Chemistry','Physics')），value=1，如果subject  not in ('Chemistry','Physics')）value=0

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

用此方法可将 subject IN ('Chemistry','Physics') 从table中抽离出来放在最后。

SELECT winner, subject FROM nobel

WHERE yr=1984

ORDER BY subject in ('Physics','Chemistry'),subject,winner

http://sqlzoo.net/wiki/Nobel_Quiz

3. Pick the code that shows the amount of years where no Medicine awards were given

select count(distinct yr) from nobel

where subject <> 'medicine'

6.Select the code which shows the years when a Medicine award was given but no Peace or Literature award was

此类题属于sql 中套sql 类型

思路：该年份满足条件：1. 该年颁发medicine award       subject="medicine"

                                         2.  那一年没有颁发 Peace award  AND Literature award

                                      yr not in  (select yr in nobel where subject ='Literature' )  and yr not in (select yr in nobel where subject='Peace') （没有颁发和平奖和文学奖的年份）

                                       比较: yr not in (select yr in nobel where subject ='Literature' and  subject='Peace')   文学奖和和平奖没有共同颁发的年份

  select distinct yr from nobel

  where subject='medicine'

  and yr not in (select yr in nobel where subject ='Literature' )  

  and  yr not in(select yr in nobel where subject='Peace')