nobel
yrsubjectwinner
1960 Chemistry Willard F. Libby
1960 Literature Saint-John Perse
1960 Medicine Sir Frank Macfarlane Burnet
1960 Medicine Peter Madawar
...

yr: 年份
subject: 獎項
winner: 得獎者

1.更改查詢以顯示1950年諾貝爾獎的獎項資料。

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

 

2.顯示誰贏得了1962年文學獎(Literature)。

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

 

3.顯示“愛因斯坦”('Albert Einstein') 的獲獎年份和獎項。

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 subject='Literature' and yr between 1980 and 1989

 

6.顯示總統獲勝者的所有細節:

  • 西奧多•羅斯福 Theodore Roosevelt
  • 伍德羅•威爾遜 Woodrow Wilson
  • 吉米•卡特 Jimmy Carter
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%'

 

8.顯示1980年物理學(physics)獲獎者,及1984年化學獎(chemistry)獲得者。

select *
from nobel
where yr=1984 and subject='Chemistry'
union
select *
from nobel
where yr=1980 and subject='Physics'

 

9.查看1980年獲獎者,但不包括化學獎(Chemistry)和醫學獎(Medicine)。

select *
from nobel
where yr=1980 and subject not in ('Chemistry','Medicine')

 

10.顯示早期的醫學獎(Medicine)得獎者(1910之前,不包括1910),及近年文學獎(Literature)得獎者(2004年以後,包括2004年)。

select *
from nobel
where subject='Literature' and yr>=2004
union
select *
from nobel
where subject='Medicine' and yr<1910

 

11.Find all details of the prize won by PETER GRÜNBERG

select * from nobel
where winner='PETER GRÜNBERG'

 

12.查找尤金•奧尼爾EUGENE O'NEILL得獎的所有細節 Find all details of the prize won by EUGENE O'NEILL

select * from nobel
where winner='EUGENE O''NEILL'

 

13.列出爵士的獲獎者、年份、獎頁(爵士的名字以Sir開始)。先顯示最新獲獎者,然後同年再按名稱順序排列。

select winner,yr,subject
from nobel
where winner like 'Sir%'
order by yr desc

 

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.

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

 

题目来源:SQLZOO

 

 

posted on 2021-01-28 15:05  Sempron2800+  阅读(57)  评论(0编辑  收藏  举报