Difference between revisions of "SELECT from Nobel Tutorial/zh"

From SQLZOO
Jump to navigation Jump to search
Line 103: Line 103:
  
 
<div class='qu'>
 
<div class='qu'>
顯示1980年至1989年(包含首尾)的文學獎獲獎者所有細節(年,主題,獲獎者)。
+
顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節(年,主題,獲獎者)。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 116: Line 116:
  
 
<div class='qu'>
 
<div class='qu'>
6) 顯示總統獲勝者的所有細節:
+
顯示總統獲勝者的所有細節:
 
*西奧多•羅斯福 Theodore Roosevelt
 
*西奧多•羅斯福 Theodore Roosevelt
 
*伍德羅•威爾遜 Woodrow Wilson
 
*伍德羅•威爾遜 Woodrow Wilson
Line 138: Line 138:
  
 
<div class='qu'>
 
<div class='qu'>
Show the winners with first name John
+
顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前,姓氏(Last name)在後)
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 149: Line 149:
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper>Show the Physics winners for 1980 together with the Chemistry winners for 1984.</p>
+
<p class=imper>顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。</p>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 162: Line 162:
  
 
<div class='qu'>
 
<div class='qu'>
<p class=imper>Show the winners for 1980 excluding the Chemistry and Medicine</p>
+
<p class=imper>查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。</p>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 175: Line 175:
  
 
<div class='qu'>
 
<div class='qu'>
Show who won a 'Medicine' prize in an early year (before 1910, not including 1910) together with winners of a 'Literature' prize in a later year (after 2004, including 2004)
+
顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 206: Line 206:
  
 
<div class='qu'>
 
<div class='qu'>
 +
查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節
 
Find all details of the prize won by EUGENE O'NEILL
 
Find all details of the prize won by EUGENE O'NEILL
<div class=hint title='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.</div>
+
<div class=hint title='跳脫字符:單引號'>你不能把一個單引號直接的放在字符串中。但您可連續使用兩個單引號在字符串中當作一個單引號。</div>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>
Line 219: Line 220:
  
 
<div class='qu'><span class='param respectorder'></span>
 
<div class='qu'><span class='param respectorder'></span>
Knights in order
+
騎士列隊 Knights in order
<p class=imper>List the winners, year and subject where the winner starts with '''Sir'''. Show the the most recent first, then by name order.</p>
+
<p class=imper>列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。</p>
 
<source lang='sql' class='def'>
 
<source lang='sql' class='def'>
 
</source>
 
</source>

Revision as of 01:56, 8 January 2016

Language:Project:Language policy [[:{{#invoke:String|sub|SELECT from Nobel Tutorial/zh
 |1
 |Expression error: Unrecognized punctuation character "{".
}}|English]]
nobel
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

The u in his name has an umlaut. You may find this link useful https://en.wikipedia.org/wiki/%C3%9C#Keyboarding
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