关于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 (yrsubjectwinner) 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获奖的信息。

1
2
3
4
5
6
7
8
9
10
11
12
13
字符串中出现单引号时,要在该单引号前加一个单引号,或者\,或者使用双引号来引用单引号

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开头。年份按照最近排序,然后按姓名顺序排列。

1
2
3
4
5
6
7
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年获奖者和科目,按科目和获奖者姓名顺序排列,但化学和物理排在最后。

1
2
3
4
5
6
7
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')即可实现。