sqlzoo 3.SELECT from Nobel 答案

有什么问题，欢迎评论或私聊。转载请私聊博主，谢谢。

原题链接：https://sqlzoo.net/wiki/Self_join

其他题解连接：https://blog.csdn.net/aiqiyizz/article/details/109057732

题解对应的是英文版题目。

3 SELECT from Nobel

3.1 Winners from 1950

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

3.2 1962 Literature

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

3.3 Albert Einstein

SELECT yr, subject
  FROM nobel
 WHERE winner = 'Albert Einstein'

3.4 Recent Peace Prizes

SELECT winner
  FROM nobel
 WHERE yr >= 2000 AND subject = 'Peace'

3.5 Literature in the 1980’s

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

3.6 Only Presidents

SELECT * FROM nobel
 WHERE winner IN ('Theodore Roosevelt',
                  'Woodrow Wilson',
                  'Jimmy Carter',
                  'Barack Obama')

3.7 John

SELECT winner
FROM nobel
WHERE winner LIKE 'John%'

3.8 Chemistry and Physics from different years

SELECT *
FROM nobel
WHERE (yr = 1980 AND subject = 'physics')
OR (yr = 1984 AND subject = 'chemistry')

3.9 Exclude Chemists and Medics

SELECT *
FROM nobel
WHERE yr = 1980 AND subject NOT IN ('Chemistry ', 'Medicine')

3.10 Early Medicine, Late Literature

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

3.11 Umlaut

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

3.12 Apostrophe

SELECT *
FROM nobel
WHERE winner = 'EUGENE O\'NEILL'

3.13 Knights of the realm

SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC, winner

3.14 Chemistry and Physics last

先按照第一项排序可以使得物理和化学排到末尾

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