SELECT from Nobel Tutorial - SQLZOO
nobel
Nobel Laureates
This tutorial is concerned with a table of Nobel prize winners:
nobel(yr, subject, winner)
Using the SELECT
statement.
条件筛选可用括号:Chemistry and Physics from different years
8.
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)
“除了”:Exclude Chemists and Medics
9.
Show the year, subject, and name of winners for 1980 excluding chemistry and medicine
SELECT * FROM nobel WHERE yr=1980 AND subject NOT IN ('Medicine','Chemistry')
当时想用EXCEPT,但显然EXCEPT太麻烦了!!
另外,*可以代表all,要善用。
字符串内有单引号时:Apostrophe
12.
Find all details of the prize won by EUGENE O'NEILL
Escaping single quotes
SELECT * FROM nobel WHERE winner='EUGENE O''NEILL'
按顺序展示,ORDER BY语句:Knights of the realm
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
SELECT * 得到的是year,subject,winner的列顺序,但题目要求的是winner,year,subject的顺序。
Chemistry and Physics last
14.
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
subject in()条件是一个Boolean表达式。值为false的会显示在true的前面。意思就是如果subject不在(‘Physics’,‘Chemistry’)里面,值为false,会显示在值为true(即subject在化学和物理里面)的前面。所以Physics 和 Chemistry会显示在最后。