SQLZOO 练习笔记(SQL 笔试题)

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
…… …… ……
  1. 选出 1984 年的得主和科目,按科目、得主排序,但 ChemistryPhysics 科目需要放在最后(第 14 题):
SELECT winner, subject
  FROM nobel
 WHERE yr = 1984
 ORDER BY subject IN ('Physics', 'Chemistry'), subject, winner;

subject IN ('Physics', 'Chemistry') 的结果可以作为数值 01 参与排序。

  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
…… …… …… …… ……
  1. 哪些国家的 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 导致的结果无记录。

  1. 选出每个大陆面积最大的国家(第 7 题):
SELECT continent, name, area
  FROM world AS W1
 WHERE area >= ALL (SELECT area 
                      FROM world AS W2
                     WHERE W1.continent = W2.continent);
  1. 选出每个大陆按字母顺序排名第一的国家(第 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);
  1. 选出各个国家人口数均不大于 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
…… …… ……
  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);
  1. 演员 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'));
  1. 和演员 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
…… …… …… ……
  1. 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';
  1. 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 会出现重复数据。

推荐阅读更多精彩内容