SQL Zoo 3.SELECT from Nobel Tutorial

1.Change the query shown so that it displays Nobel prizes for 1950.(显示1950年的诺贝尔奖)

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

2.Show who won the 1962 prize for literature.(谁获得了1962年的文学奖)

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

3.Show the year and subject that won 'Albert Einstein' his prize.(展示“阿尔伯特·爱因斯坦”获奖的年份和学科)

select yr,subject from nobel where winner = 'Albert Einstein'

4.Give the name of the 'peace' winners since the year 2000, including 2000.(请说出自2000年以来的“和平”获奖者的名字，包括2000年)

select winner from nobel where subject = 'peace' and yr >=2000

5.Show all details (yr, subject, winner) of the literature prize winners for 1980 to 1989 inclusive.(显示1980年至1989年(含)文学奖得主的所有详细信息(年龄、主题、获奖者))

select * from nobel where subject = 'literature' and yr between 1980 and 1989

6.Show all details of the presidential winners(显示获奖人的所有细节):

• Theodore Roosevelt
• Thomas Woodrow Wilson
• Jimmy Carter
• Barack Obama

select * from nobel where winner in ('Theodore Roosevelt','Woodrow Wilson','Jimmy Carter','Barack Obama')

 7.Show the winners with first name John(显示名字为约翰的获胜者)

select winner from nobel where winner like 'John%'

8.Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.(显示1980年物理学奖得主的年份、学科和名字，以及1984年化学奖得主)

select * from nobel where
(yr = 1980 and subject = 'physics') or
(yr = 1984 and subject = 'chemistry')

9.Show the year, subject, and name of winners for 1980 excluding chemistry and medicine.(显示1980年获奖者的年份、学科和姓名，化学和医学除外)

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

10.Show year, subject, and name of people 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)(展示年份、学科和在早期年份(1910年以前，不包括1910年)获得“医学奖”的人的名字，以及之后年份(2004年以后，包括2004年)获得“文学奖”的人的名字)

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.(找到PETER GRÜNBERG赢得的奖品的所有细节)

select * from nobel where winner = 'Peter Grünberg'

12.Find all details of the prize won by EUGENE O'NEILL.(查找尤金·奥尼尔获奖的所有细节)

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

注：名字内有单引号，需要利用两个单引号才能进行查询

13.Knights in order List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.（按顺序列出获奖者，年份和主题，获奖者以Sir开头。首先显示最近的，然后按姓名顺序显示）

 

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

14.Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.(显示1984年获奖者和主题，按主题和获奖者姓名排序;但是把化学和物理放在最后)

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

注：order by 配合in语句即可把括号中需要排序的内容放置最后