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: 获奖者

SELECT from Nobel Tutorial/zh

1
2
3
4
1.更改查詢以顯示1950年諾貝爾獎的獎項資料。
SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950
1
2
3
4
5
2.顯示誰贏得了1962年文學獎(Literature)。
SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'
1
2
3
4
3.顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。
select yr,subject
from nobel
where winner = 'Albert Einstein'
1
2
3
4
4.顯示2000年及以後的和平獎(‘Peace’)得獎者。
select winner
from nobel
where yr >= 2000 and subject = 'Peace'
1
2
3
4
5.顯示1980年至1989年(包含首尾)的文學獎(Literature)獲獎者所有細節（年，主題，獲獎者）。
select yr,subject,winner
from nobel
where yr between 1980 and 1989 and subject = 'Literature'
1
2
3
4
5
6
7
6.顯示總統獲勝者的所有細節：
西奧多•羅斯福 Theodore Roosevelt
伍德羅•威爾遜 Woodrow Wilson
吉米•卡特 Jimmy Carter

select * from nobel
where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter')
1
2
3
4
7.顯示名字為John 的得獎者。 (注意:外國人名字(First name)在前，姓氏(Last name)在後)
select winner
from nobel
where winner like 'John %'
1
2
3
8.顯示1980年物理學(physics)獲獎者，及1984年化學獎(chemistry)獲得者。
select * from nobel
where (yr = 1980 and subject = 'physics') or (yr = 1984 and subject = 'chemistry') 
1
2
3
9.除查看1980年獲獎者，但不包括化學獎(Chemistry)和醫學獎(Medicine)。
select * from nobel
where yr = 1980 and subject not in ('Chemistry','Medicine')
1
2
3
10.顯示早期的醫學獎(Medicine)得獎者（1910之前，不包括1910），及近年文學獎(Literature)得獎者（2004年以後，包括2004年）。
select * from nobel
where (yr < 1910 and subject = 'Medicine') or (yr >= 2004 and subject = 'Literature')

More difficult

1
2
3
4
11.Find all details of the prize won by PETER GRÜNBERG(直接复制就好了233)

select * from nobel
where winner = 'PETER GRÜNBERG'
1
2
3
4
12.查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL

select * from nobel
where winner = 'EUGENE O''NEILL'
1
2
3
4
5
6
13.列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者，然後同年再按名稱順序排列。

select winner,yr,subject
from nobel
where winner like 'Sir%'
order by yr desc,winner
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
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.(用Mysql引擎，用sql server貌似不行，这个操作相当于自己加一个的条件排序)

SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY (case when subject in ('Chemistry','Physics') then 1 else 0 end),subject,winner

(case when subject in ('Chemistry','Physics') then 1 else 0 end)这玩意儿就像是给每行数据一个排序索引，subject在('Chemistry','Physics')这里面的要放到后面，所以给他们的值是1，其余是0，再升序排列就可以了（默认就是升序），去瞄了一眼别人的答案，是这样的：

SELECT winner,subject
FROM nobel
WHERE yr=1984
ORDER BY subject in ('Chemistry','Physics'),subject,winner

恍然大悟，原来可以直接用（是我傻了）

Quiz

P__4X0F_F_J_I23L5_VJ7_N.png
1.選擇代碼以顯示以C開頭，並以n結束獲獎者的名字。
Answer：

1
2
SELECT winner FROM nobel
 WHERE winner LIKE 'C%' AND winner LIKE '%n'

2.選擇代碼以顯示1950年到1960年間有多少個化學獎。
Answer：

1
2
3
SELECT COUNT(subject) FROM nobel
 WHERE subject = 'Chemistry'
   AND yr BETWEEN 1950 and 1960

3.選擇代碼以顯示有多少年沒有頒發醫學獎。
Answer：

1
2
SELECT COUNT(DISTINCT yr) FROM nobel
 WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')

4.選擇你會從這個代碼獲得的結果。

1
SELECT subject, winner FROM nobel WHERE winner LIKE 'Sir%' AND yr LIKE '196%'

Answer：
WWLI1W`LD__LZD_`_8U_8@Q.png
5.選擇代碼以顯示哪一年沒有頒發物理獎，亦沒有頒發化學獎
Answer：

1
2
3
4
SELECT yr FROM nobel
 WHERE yr NOT IN(SELECT yr 
                   FROM nobel
                 WHERE subject IN ('Chemistry','Physics'))

6.選擇代碼以顯示哪一年有頒發醫學獎，但沒有頒發和平或文學獎。
Answer：

1
2
3
4
5
6
7
SELECT DISTINCT yr
  FROM nobel
 WHERE subject='Medicine' 
   AND yr NOT IN(SELECT yr FROM nobel 
                  WHERE subject='Literature')
   AND yr NOT IN (SELECT yr FROM nobel
                   WHERE subject='Peace')

7.選擇你會從這個代碼獲得的結果。

1
2
3
4
SELECT subject, COUNT(subject) 
  FROM nobel 
 WHERE yr ='1960' 
 GROUP BY subject

Answer：
ZSH3JW8LK1Z85ZW_Y_MCPH6.png

Summary