我們繼續練習簡單的單一表格SQL查詢。
這個教程是有關諾貝爾獎得獎者的:
nobel(yr, subject, winner)
yr: 年份
subject: 獎項
winner: 得獎者
1、更改查詢以顯示1950年諾貝爾獎的獎項資料。
SELECT yr, subject, winner FROM nobel WHERE yr = 19502、顯示誰贏得了1962年文學獎(Literature)。
SELECT winner FROM nobel WHERE yr = 1962 AND subject = 'Literature'
select yr,subject from nobel where winner = 'Albert Einstein'4、顯示2000年及以後的和平獎(‘Peace’)得獎者。
select winner from nobel where yr>=2000 and subject='Peace'5、顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年,主題,獲獎者)。
select * from nobel where yr between 1980 and 1989 and subject = 'Literature'6、
顯示總統獲勝者的所有細節:
SELECT * FROM nobel WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson', 'Jimmy Carter')
7、顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前,姓氏(Last name)在後)
select winner from nobel where winner like 'John%'
select * from nobel where (yr=1980 and subject='physics') or (yr=1984 and subject='chemistry')
select * from nobel where subject not in('Chemistry','Medicine') and yr=1980
select * from nobel where (yr<1910 and subject='Medicine') or (yr>=2004 and subject='Literature')11、Find all details of the prize won by PETER GRüNBERG
select * from nobel where winner = 'Peter Grünberg'
select * from nobel where winner = 'Eugene O\'Neill'13、
騎士列隊 Knights in order
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。
select winner,yr,subject from nobel where winner like 'Sir%' order by yr desc,winner asc14、
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 ('Physics','Chemistry'),subject asc,winner asc