13,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;
首先显示什么,winner,yr,subject
select winner,yr,subject from nobel
再就是什么条件获奖者名字加sir winner like ‘sir%’
从最近年份向下,按名字顺字母顺序排列 order by yr desc,winner
14.Chemistry and Physics last
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 subject IN ('Chemistry','Physics'), subject , winner ;
其实这个难点就是list Chemistry and Physics last.,科目Chemistry and Physics 排列在下面/后面,反过来说不是这两门会在这两门的上面
从题目知道,科目Chemistry’,'Physics’,令subject IN (‘Chemistry’,‘Physics’)=1,;不是这两门,令subject IN (‘Chemistry’,‘Physics’)=0,就可以得到顺序列order by subject IN (‘Chemistry’,‘Physics’)