SQLzoo练习题回顾

这篇文章回顾我在SQLzoo（http://zh.sqlzoo.net/wiki/）上练习时觉得有困难的练习题以及从练习中get到的新知识点。

1. 选出人口多或者面积大的国家，但不能包括同时满足这两个条件的国家
   提示要用操作符XOR，此操作符满足条件1或条件2，但是不同时满足条件1和条件2。
   因此本题的答案是:
   SELECT name, population, area
   FROM world
   WHERE area>3000000 XOR population >250000000;

2. 近似函数ROUND,FLOOR,CEIL https://sqlzoo.net/wiki/ROUND
   ROUND(f,p) 用来取近似值，p表示保留小数点后几位数字，如果p是负数，则表示近似到10的几次方，负数也可以理解为保留到小数点前几位数。
   FLOOR(f) 向下取整数
   CEIL(f) 向上取整数

3. Find all details of the prize won by EUGENE O’NEILL
   找出EUGENE O’NEILL所获奖的信息

名字中的单引号与语句中的单引号冲突，那么怎么表示名字中字母O后面的单引号呢？
在题目中已经给我们提示了！
Escaping single quotes
You can’t put a single quote in a quote string directly. You can use two single quotes within a quoted string.
答案：
SELECT *
FROM nobel
WHERE winner = ‘EUGENE O’‘NEILL’;

用两个单引号代替，注意这里不是一个双引号“，而是两个单引号 ‘’哦～

4. 显示1984年的诺贝尔获奖者和所获奖的学科，按照学科和获奖者姓名来排序，化学和物理排在最后。
   Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.
   此题中 The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.

答案：
SELECT winner, subject
FROM nobel
WHERE yr=1984
ORDER BY subject IN (‘Physics’,‘Chemistry’),subject,winner;

5. 比其同大洲的其他国家人口多三倍的国家
   Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.
   答案：
   SELECT name, continent
   FROM world x
   WHERE population/3 > ALL(SELECT population FROM world y WHERE x.continent=y.continent AND x.name!=y.name)

6. 找出该洲所有国家人口均少于25000000的大洲
   Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.
   答案：
   SELECT name,continent,population
   FROM world x
   WHERE 25000000>=ALL(SELECT population FROM world y WHERE x.continent=y.continent)

7. 列出每个大洲按照字母排序的第一个国家
   List each continent and the name of the country that comes first alphabetically.
   这道题可难为坏了我！但是没想到答案这么简单。一个min 函数轻轻松松搞定字母排序的问题。
   答案：
   SELECT continent,min(name)
   FROM world
   GROUP BY continent

JOIN

8. 至少当过30次主角的演员
   Obtain a list, in alphabetical order, of actors who’ve had at least 30 starring roles.
   答案
   SELECT name
   FROM movie JOIN casting ON movie.id=movieid
   JOIN actor ON actorid=actor.id
   WHERE ord=1
   GROUP BY name
   HAVING COUNT(movieid)>=30
   ORDER BY name

9. For every match involving ‘POL’, show the matchid, date and the number of goals scored.

答案：
SELECT matchid,mdate,count(teamid)
FROM game JOIN goal ON matchid = id
WHERE (team1 = ‘POL’ OR team2 = ‘POL’)
GROUP BY matchid,mdate

这道题刚开始我不知道GROUP BY 后面应该跟一组数据的。我只写了group by matchid，报错。

SELF JOIN

10. 找出从Craiglockhart 到 Lochend的换乘路线、和换乘站。
    Find the routes involving two buses that can go from Craiglockhart to Lochend.
    Show the bus no. and company for the first bus, the name of the stop for the transfer,
    and the bus no. and company for the second bus.

不得不承认这道题还是有点难度的

SELECT DISTINCT R1.num,R1.company, S2.name, R4.num,R4.company
FROM route R1 JOIN route R2 ON (R1.num=R2.num AND R1.company=R2.company)
JOIN route R3 ON R2.stop=R3.stop
JOIN route R4 ON (R3.num=R4.num AND R3.company=R4.company)
JOIN stops S1 ON S1.id=R1.stop
JOIN stops S2 ON S2.id=R2.stop
JOIN stops S3 ON S3.id=R4.stop
WHERE S1.name=‘Craiglockhart’ AND S3.name = ‘Lochend’

当然也可以不用JOIN 语句
SELECT R1.num,R1.company, S2.name, R4.num,R4.company
FROM route R1, route R2, route R3, route R4,stops S1, stops S2, stops S3, stops S4
WHERE S1.name=‘Craiglockhart’ AND S1.id=R1.stop
AND R1.num=R2.num AND R1.company=R2.company
AND R2.stop=R4.stop AND S2.id=R2.stop
AND S3.id=R3.stop AND S4.id=R4.stop
AND S3.name=‘Lochend’ AND R3.num=R4.num
AND R3.company=R4.company