sqlzoo:3
顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。
select yr,subject,winner from nobel where (subject='physics' and yr=1980) or(subject='chemistry'and yr=1984)
查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。
select yr,subject,winner from nobel where yr=1980 and subject not in('Chemistry','Medicine')
顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
select yr,subject,winner from nobel where (yr <1910 and subject='Medicine') or (yr>=2004 and subject='Literature')
查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL
select * from nobel where winner='EUGENE O''NEILL'
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。
注意降序升序
select winner,yr,subject from nobel where winner like'Sir%' order by yr desc ,winner asc
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.
重点在于表达主语IN('Chemistry','Physics')可以用作值 - 它将是0或1。
select winner, subject from nobel where yr=1984 order by subject in ('Physics','Chemistry'),subject asc,winner asc