SQL之SELECT from Nobel Tutorial

经过一段时间练习，第二部分习题终于可以很好的做出来啦。
题目源自 https://sqlzoo.net。这个网站是一个很好的练习SQL学习网站。可以配合书《SQL必知必会》一起使用。
1.Change the query shown so that it displays Nobel prizes for 1950.

SELECT yr, subject, winner
  FROM nobel
 WHERE yr = 1950

很简单的过滤数据，数据根据WHERE子句中指定的搜索条件yr = 1950进行过滤。

2.Show who won the 1962 prize for Literature.

SELECT winner
  FROM nobel
 WHERE yr = 1962
   AND subject = 'Literature'

这一题属于高级数据过滤，要通过不止一个列进行过滤，可以使用AND操作符给WHERE子句附加条件。
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 yr>=2000
       AND subject='Peace' 

WHERE子句操作符>=表示大于等于也可以使用!<表示不小于。
5.Show all details (yr, subject, winner) of the Literature prize winners for 1980 to 1989 inclusive.

SELECT yr,subject,winner
FROM nobel
WHERE subject='Literature'
AND yr BETWEEN 1980 AND 1989

WHERE子句操作符中BETWEEN表示为指定的两个值之间，但是BETWEEN语法和其他操作符稍有不同，因为他需要两个值，即范围的开始值和结束值。这两个值必须用AND关键字分隔。另外，BETWEEN匹配的范围中包括指定的开始值和结束值。
6.Show all details of the presidential winners:Theodore RooseveltWoodrow WilsonJimmy CarterBarack Obama

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%'

用通配符进行过滤，为在搜索子句中使用通配符，必须使用LIKE操作符。在搜索串中，%表示任何字符出现任意次数。
8.Show the year, subject, and name of Physics winners for 1980 together with the Chemistry winners for 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

SELECT * FROM nobel
 WHERE yr=1980
       AND subject NOT IN ('Chemistry','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)

SELECT * 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ÜNBERGNon-ASCII characters

SELECT * FROM nobel
WHERE winner='PETER GRÜNBERG'

12.Find all details of the prize won by EUGENE O’NEILLEscaping single quotes

SELECT yr, subject, winner
FROM nobel
WHERE winner = 'EUGENE O''NEILL'

这里面借鉴的网络答案，将 EUGENE O’NEILLE改成EUGENE O’'NEILL。
13.Knights in orderList 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 

排序检索数据，ORDER BY子句取一个或多个列的名字，据此对输出进行排序。默认升序（从A到Z），为了进行降序排列，须使用指定DESC关键词。
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.

SELECT winner,subject
  FROM nobel
 WHERE yr=1984
 ORDER BY subject IN ('Physics','Chemistry'),subject,winner