SELECT from Nobel Tutorial/zh
yr | subject | winner | ||
---|---|---|---|---|
1960 | Chemistry | Willard F. Libby | ||
1960 | Literature | Saint-John Perse | ||
1960 | Medicine | Sir Frank Macfarlane Burnet | ||
1960 | Medicine | Peter Madawar | ||
... |
yr: 年份
subject: 獎項
winner: 得獎者
nobel
諾貝爾獎得獎者
我們繼續練習簡單的單一表格SQL查詢。
這個教程是有關諾貝爾獎得獎者的:
nobel(yr, subject, winner)
yr: 年份
subject: 獎項
winner: 得獎者
練習
使用SELECT
語句。
更改查詢以顯示1950年諾貝爾獎的獎項資料。
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1960
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950
顯示誰贏得了1962年文學獎(Literature)。
SELECT winner
FROM nobel
WHERE yr = 1960
AND subject = 'Physics'
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein'
顯示2000年及以後的和平獎(‘Peace’)得獎者。
SELECT winner
FROM nobel
WHERE subject = 'Peace'
AND yr >= 2000
顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年,主題,獲獎者)。
SELECT yr,subject,winner
FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989
顯示總統獲勝者的所有細節:
- 西奧多•羅斯福 Theodore Roosevelt
- 伍德羅•威爾遜 Woodrow Wilson
- 吉米•卡特 Jimmy Carter
SELECT * FROM nobel
WHERE yr = 1970
AND subject IN ('Cookery',
'Chemistry',
'Literature')
SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jed Bartlet',
'Jimmy Carter')
顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前,姓氏(Last name)在後)
SELECT winner FROM nobel
WHERE winner LIKE 'John %'
顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。
SELECT *
FROM nobel
WHERE (subject='physics' AND yr=1980) OR
(subject='chemistry' AND yr=1984)
查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。
SELECT *
FROM nobel
WHERE yr=1980 AND
subject NOT IN ('Chemistry','Medicine')
顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
SELECT *
FROM nobel
WHERE (subject='Medicine' and yr <1910) OR
(subject='Literature' AND yr>=2004)
Harder Questions
Find all details of the prize won by PETER GRÜNBERG
SELECT *
FROM nobel
WHERE winner in ('Peter Grünberg')
查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL
SELECT *
FROM nobel
WHERE winner in ('Eugene O''Neill')
騎士列隊 Knights in order
列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner
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, subject IN ('Physics','Chemistry')
FROM nobel
WHERE yr=1984
ORDER BY subject,winner
select winner, subject
from nobel
where yr=1984
order by subject in ('Physics','Chemistry'),subject,winner