SQLZOO:SELECT from Nobel Tutorial/zh
诺贝尔奖获得者信息表格:
yr: 年份
subject: 奖项
winner: 得奖者
开始查询表格(nobel)
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 (yr between 1980 AND 1989)
AND subject = 'Literature'
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 (subject = 'physics' AND yr = 1980)
OR (subject = 'chemistry' AND yr = 1984)
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 (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
(查找尤金•奥尼尔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, winner
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.
(显示获奖年份为1984年的获奖者及奖项,按照奖项和获奖者排序,并且化学奖和物理学奖排在最后)
SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Chemistry','Physics'), subject, winner
使用subject IN语句进行判断,如果subject满足条件,返回1,若不满足,则返回0。满足条件返回值是1,进行顺序排序时,在最后显示。