SQLZOO 是一个在线学习和练习 SQL 的网站。
本文主要记录其中一些不常见的或难度较高的题目解法。
试题
如下是诺贝尔奖表 nobel：
| yr（年份） | subject（科目） | winner（得主） |
|---|---|---|
| 2015 | Chemistry | Aziz Sancar |
| 2015 | Chemistry | Paul L. Modrich |
| 2015 | Chemistry | Tomas Lindahl |
| 2015 | Economics | Angus Deaton |
| 2015 | Literature | Svetlana Alexievich |
| …… | …… | …… |
- 选出
1984年的得主和科目，按科目、得主排序，但Chemistry和Physics科目需要放在最后（第 14 题）：
SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN ('Physics', 'Chemistry'), subject, winner;
subject IN ('Physics', 'Chemistry')的结果可以作为数值0或1参与排序。
- 有几年没有颁发
Medicine奖（第 3 题）：
SELECT COUNT(DISTINCT yr)
FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr
FROM nobel
WHERE subject = 'Medicine');
如下是世界国家信息表 world：
| name（国家） | continent（大陆） | area（面积） | population（人口） | gdp（GDP） |
|---|---|---|---|---|
| Afghanistan | Asia | 652230 | 25500100 | 20343000000 |
| Albania | Europe | 28748 | 2831741 | 12960000000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000000 |
| Andorra | Europe | 468 | 78115 | 3712000000 |
| Angola | Africa | 1246700 | 20609294 | 100990000000 |
| …… | …… | …… | …… | …… |
- 哪些国家的 GDP 比欧洲的所有国家都高（第 6 题）：
SELECT name
FROM world
WHERE GDP > ALL (SELECT GDP
FROM world
WHERE GDP IS NOT NULL AND continent = 'Europe');
有些国家的 GDP 数值可能为
NULL，所以子查询中必须使用GDP IS NOT NULL作为查询条件之一，以避免NULL导致的结果无记录。
- 选出每个大陆面积最大的国家（第 7 题）：
SELECT continent, name, area
FROM world AS W1
WHERE area >= ALL (SELECT area
FROM world AS W2
WHERE W1.continent = W2.continent);
- 选出每个大陆按字母顺序排名第一的国家（第 8 题）：
SELECT continent, name
FROM world AS W1
WHERE name < ALL (SELECT name
FROM world AS W2
WHERE W1.continent = W2.continent
AND W1.name <> W2.name);
- 选出各个国家人口数均不大于 25000000 的大陆的所有国家（第 9 题）：
SELECT name, continent, population
FROM world AS W1
WHERE 25000000 >= ALL (SELECT population
FROM world AS W2
WHERE W1.continent = W2.continent);
------------------------ 多解分隔线 ------------------------
SELECT name, continent, population
FROM world AS W1
WHERE NOT EXISTS (SELECT *
FROM world AS W2
WHERE W1.continent = W2.continent
AND W2.population > 25000000);
如下是电影信息表 movie：
| id（主键） | title（电影名） | yr（年份） | director（导演，actor 表外键） | budget（成本） | gross（票房） |
|---|---|---|---|---|---|
| 10003 | "Crocodile" Dundee II | 1988 | 38 | 15800000 | 239606210 |
| 10004 | 'Til There Was You | 1997 | 49 | 10000000 | NULL |
| …… | …… | …… | …… | …… | …… |
如下是演员表 actor：
| id（主键） | name（演员名） |
|---|---|
| 20 | Paul Hogan |
| 50 | Jeanne Tripplehorn |
| …… | …… |
如下是电影演员关系表 casting：
| movieid（movie 表外键） | actorid（actor 表外键） | ord（排序号） |
|---|---|---|
| 10003 | 20 | 4 |
| 10004 | 50 | 1 |
| …… | …… | …… |
- 哪些年演员
John Travolta参与拍摄的电影最多（第 11 题）：
SELECT yr, COUNT(*)
FROM movie INNER JOIN casting ON id = movieid
INNER JOIN actor ON actorid = actor.id
WHERE name = 'John Travolta'
GROUP BY yr
HAVING COUNT(*) = (SELECT MAX(count)
FROM (SELECT yr, COUNT(*) AS count
FROM movie INNER JOIN casting ON id = movieid
INNER JOIN actor ON actorid = actor.id
WHERE name = 'John Travolta'
GROUP BY yr) AS T);
- 演员
Julie Andrews参与拍摄了哪些电影？选出电影名和领衔主演（第 12 题）：
SELECT title, name
FROM movie INNER JOIN casting ON id = movieid
INNER JOIN actor ON actorid = actor.id
WHERE ord = 1
AND EXISTS (SELECT *
FROM casting AS C
WHERE movie.id = C.movieid
AND C.actorid = (SELECT id
FROM actor AS A
WHERE A.name = 'Julie Andrews'));
--------------------------------- 多解分隔线 ---------------------------------
SELECT title, name
FROM movie INNER JOIN casting ON id = movieid
INNER JOIN actor ON actorid = actor.id
WHERE ord = 1
AND movieid IN (SELECT movieid
FROM casting
WHERE actorid = (SELECT id
FROM actor
WHERE name = 'Julie Andrews'));
- 和演员
Art Garfunkel共事过的演员有哪些（第 15 题）：
SELECT DISTINCT name
FROM actor AS A INNER JOIN casting AS C ON id = actorid
WHERE name <> 'Art Garfunkel'
AND EXISTS (SELECT *
FROM casting
WHERE C.movieid = movieid
AND actorid = (SELECT id
FROM actor
WHERE name = 'Art Garfunkel'));
------------------------------- 多解分隔线 -------------------------------
SELECT DISTINCT name
FROM actor INNER JOIN casting ON id = actorid
WHERE name <> 'Art Garfunkel'
AND movieid IN (SELECT movieid
FROM casting
WHERE actorid = (SELECT id
FROM actor
WHERE name = 'Art Garfunkel'));
如下是公交站台表 stops：
| id（主键） | name（站台名） |
|---|---|
| 1 | Aberlady |
| 2 | Abington |
| 3 | Amisfield Park |
| 4 | Ancrum |
| 5 | Armadale |
| …… | …… |
如下是公交路线表 route：
| num（公交编号） | company（公司） | pos（站台序号） | stop（站台号，stops 表外键） |
|---|---|---|---|
| 1 | LRT | 1 | 137 |
| 1 | LRT | 2 | 99 |
| 1 | LRT | 3 | 59 |
| 1 | LRT | 4 | 66 |
| 1 | LRT | 5 | 42 |
| …… | …… | …… | …… |
-
Craiglockhart站到London Road站直达的公交有哪些（第 6 题）：
SELECT R1.company, R1.num, S1.name, S2.name
FROM route AS R1 INNER JOIN route AS R2 ON R1.company = R2.company AND R1.num = R2.num
INNER JOIN stops AS S1 ON R1.stop = S1.id
INNER JOIN stops AS S2 ON R2.stop = S2.id
WHERE S1.name = 'Craiglockhart' AND S2.name = 'London Road';
-
Craiglockhart站到Sighthill站中转 1 次的方案有哪些（第 10 题）：
SELECT DISTINCT R1.num, R1.company, name, R4.num, R4.company
FROM route AS R1 INNER JOIN route AS R2 ON R1.num = R2.num AND R1.company = R2.company
INNER JOIN route AS R3 ON R2.stop = R3.stop
INNER JOIN route AS R4 ON R3.num = R4.num AND R3.company = R4.company
INNER JOIN stops ON R2.stop = id
WHERE R1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')
AND R4.stop = (SELECT id FROM stops WHERE name = 'Sighthill');
某些公交为环线，此处如果不用 DISTINCT 会出现重复数据。
