关于sqlzoo中的select_from_nobel表的练习题~

00. 前言

练习地址：https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial

使用的为sqlzoo的MySQL库。

表名：nobel

字段名：yr、subject、winner

01.Winners from 1950

Change the query shown so that it displays Nobel prizes for 1950.

更改显示的查询，以显示1950年的诺贝尔奖。

1
2
3

select *
from nobel
where yr=1950

02.1962 Literature

Show who won the 1962 prize for literature.

显示谁获得了1962年的文学奖。

1
2
3

select winner
from nobel
where yr=1962 and subject='literature'

03.Albert Einstein

Show the year and subject that won ‘Albert Einstein’ his prize.

显示Albert Einstein获奖的年份和奖项。

1
2
3

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

04.Recent Peace Prizes

Give the name of the ‘peace’ winners since the year 2000, including 2000.

请说出自2000年以来，包括2000年在内的“和平”获奖者的名字。

1
2
3

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

05.Literature in the 1980’s

Show all details (yr, subject, winner) of the literature prize winners for 1980 to 1989 inclusive.

显示1980年至1989年(含)文学奖获奖者的所有信息(年份、奖项、获奖者)。

1
2
3

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

06.Only Presidents

Show all details of the presidential winners:

Theodore Roosevelt
Woodrow Wilson
Jimmy Carter
Barack Obama

显示总统候选人的得奖信息。

1
2
3

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

07.John

Show the winners with first name John.

显示获奖者的名字为John的。

1
2
3

select winner
from nobel
where winner like 'John%'

08.Chemistry and Physics from different years

Show the year, subject, and name of physics winners for 1980 together with the chemistry winners for 1984.

显示1980年诺贝尔物理学和1984年诺贝尔化学奖得主的年份，奖项，姓名。

1
2
3

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

09.Exclude Chemists and Medics

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

显示1980年获奖者(化学和医学除外)的年份、奖项和姓名。

1
2
3

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

10.Early Medicine, Late Literature

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年)获得“文学”奖的人的姓名和获奖年份。

1
2
3

select *
from nobel
where (yr<1910 and subject='medicine') or (yr>=2004 and subject='literature')

11.Umlaut

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

查找PETER GRÜNBERG获奖的信息。

1
2
3

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

12.Apostrophe

Find all details of the prize won by EUGENE O’NEILL.

查找EUGENE O’NEILL获奖的信息。

字符串中出现单引号时，要在该单引号前加一个单引号，或者\，或者使用双引号来引用单引号

select *
from nobel
where winner='EUGENE O\'NEILL'
或者
select *
from nobel
where winner='EUGENE O''NEILL'
或者
select *
from nobel
where winner="EUGENE O'NEILL"

13.Knights of the realm

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

多个字段之间排序用英文逗号隔开，按照写的顺序依次作为排序依据。
这里就是先按yr降序排序后，yr中有相同的值，再对winner进行升序排序

14.Chemistry and Physics last

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.

表达式subject IN (‘chemistry’,’physics’)可以用作一个值，它将是0或1。
显示1984年获奖者和科目，按科目和获奖者姓名顺序排列，但化学和物理排在最后。

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

在排序中subject in ('chemistry','physics')表示将括号内的内容排在最后面，在括号外的都是0，括号内的都是1。
同理，想将这两科排在最前面，改为subject not in ('chemistry','physics')即可实现。