SQLZOO刷题记录3——SELECT from Nobel Tutorial

 This tutorial is concerned with a table of Nobel prize winners:（本节关注诺贝尔奖获奖者信息）

nobel(yr, subject, winner)

 

题目12:

Apostrophe

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.

解题12：

SELECT * FROM nobel 
WHERE winner = 'EUGENE O''NEILL'

 注意：不能直接在引号字符串中加入单引号，可以在引号字符串中使用两个单引号。

题目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.

解题14：

SELECT winner, subject 
FROM nobel
WHERE yr=1984
ORDER BY subject IN ('Physics','Chemistry'), subject, winner

注意：这段代码的巧妙之处在于subject IN ('Chemistry','Physics') 语句的使用，由于返回指只有0和1，可以对该值进行排序，把放在最后显示的内容值放在IN里面。