SQLZOO 3 Select From Nobel

select from nobel

1.Change the query shown so that it displays Nobel prizes for 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 winnerfrom 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

显示名字以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 (subject='Physics' and yr='1980')
or (subject='Chemistry'and yr='1984')

9.Show the year, subject, and name of winners for 1980 excluding chemistry and medicine

显示1980年获奖者的年份、学科和名字(化学和医学除外)

select * from nobel
where yr='1980' and (subject<>'Chemistry' and subject<>'Medicine')

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

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.

找到 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 asc

order by 默认排序方式是升序，即asc

14.Show the 1984 winners and subject ordered by subject and winner name; but list chemistry and physics last.

显示1984年的获奖者和主题，按主题和获奖者名称排序;但是把化学和物理排在最后。

select winner,subject subject from nobel
where yr='1984' 
order by subject in ('Chemistry','Physics') , subject,winner

先排序化学和物理主题的，再排序其他主题，就能把化学和物理排在最后