SQLZOO习题详解（03）——SELECT from Nobel Tutorial

练习地址

---

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.

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.

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.

SELECT * FROM nobel
 WHERE subject = 'literature' AND yr BETWEEN 1980 AND 1989;

6.Show all details of the presidential winners:（这题好坑啊，给的名字和查询的不一样）

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.

SELECT yr, subject, winner 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(这题目指定是有点问题，排除化学奖和医学奖，但是正确答案里面有医学奖，我…)

SELECT yr, subject, winner FROM nobel
 WHERE subject NOT IN ('chemistry') 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)

SELECT yr, subject, winner FROM nobel
 WHERE (subject = 'Medicine' AND yr < 1910) OR 
       (subject = 'Literature' AND yr >= 2004);

11.Find all details of the prize won by 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.List the winners, year and subject where the winner starts with Sir. Show the the most recent first, then by name order.

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.

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

---

笔记

1.特殊字符需要用 \来转义
2.面对多个筛选条件时可以用小括号隔开，例如：

WHERE (subject = 'Medicine' AND yr < 1910) OR 
       (subject = 'Literature' AND yr >= 2004);

3.sql ORDER BY 的使用 DESC是从大到小排序，ASC是从小到大排序