sqlzoo刷题-day1

【目标：完成SELECT for world/nobel】原始网页从昨天就刷不进去，今天发现网址发生变化了。。。【明天做子查询的部分】

---

SELECT_from_Nobel某些题目记录

• T5:展示1980-1989（包含首尾）的Literature获奖者的所有细节。

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

包含首尾使用between and，两个条件可以用括号隔开。

• T11: Find all details of the prize won by PETER GRÜNBERG

Non-ASCII characters

The u in his name has an umlaut. You may find this link useful https://en.wikipedia.org/wiki/%C3%9C#Keyboarding

SELECT * FROM nobel
WHERE winner LIKE 'Peter Gr_nberg'

• T12: 字符出现单引号，可用两个单引号表示'

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

• T13: 按year和winner排序，其中year要求逆序

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

ORDER BY排序多列用","隔开，逆序使用"DESC"

• T14: 按subject和winner进行排序，要求chemistry和physics在最后

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

使用IN对subject符合条件的置为1，不符合的置为0，它优先排序保证符合条件的排在后面，再按照正常字母排序

• quiz6：选择展示颁发医学奖但是没有颁发和平奖或文学奖的年份

SELECT DISTINCT yr
  FROM nobel
 WHERE subject='Medicine' 
   AND yr NOT IN(SELECT yr FROM nobel 
                  WHERE subject='Literature')
   AND yr NOT IN (SELECT yr FROM nobel
                   WHERE subject='Peace')

--将where语句改成下面的也可以查询
 WHERE subject='Medicine' 
   AND yr NOT IN(SELECT yr FROM nobel 
                  WHERE subject IN ('Literature', 'Peace'))