SQLZOO 是一個在線學(xué)習(xí)和練習(xí) SQL 的網(wǎng)站。

本文主要記錄其中一些不常見的或難度較高的題目解法。

試題

如下是諾貝爾獎表 nobel：

1. 選出 1984 年的得主和科目，按科目、得主排序，但 Chemistry 和 Physics 科目需要放在最后（第 14 題）：

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

subject IN ('Physics', 'Chemistry') 的結(jié)果可以作為數(shù)值 0 或 1 參與排序。

2. 有幾年沒有頒發(fā) Medicine 獎（第 3 題）：

SELECT COUNT(DISTINCT yr)
  FROM nobel
 WHERE yr NOT IN (SELECT DISTINCT yr
                    FROM nobel
                   WHERE subject = 'Medicine');

如下是世界國家信息表 world：

1. 哪些國家的 GDP 比歐洲的所有國家都高（第 6 題）：

SELECT name
  FROM world
 WHERE GDP > ALL (SELECT GDP
                    FROM world
                   WHERE GDP IS NOT NULL AND continent = 'Europe');

有些國家的 GDP 數(shù)值可能為 NULL，所以子查詢中必須使用 GDP IS NOT NULL 作為查詢條件之一，以避免 NULL 導(dǎo)致的結(jié)果無記錄。

2. 選出每個大陸面積最大的國家（第 7 題）：

SELECT continent, name, area
  FROM world AS W1
 WHERE area >= ALL (SELECT area 
                      FROM world AS W2
                     WHERE W1.continent = W2.continent);

3. 選出每個大陸按字母順序排名第一的國家（第 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);

4. 選出各個國家人口數(shù)均不大于 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：

如下是演員表 actor：

如下是電影演員關(guān)系表 casting：

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);

2. 演員 Julie Andrews 參與拍攝了哪些電影？選出電影名和領(lǐng)銜主演（第 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'));

3. 和演員 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：

如下是公交路線表 route：

1. Craiglockhart 站到 London Road 站直達(dá)的公交有哪些（第 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';

2. Craiglockhart站到 Sighthill 站中轉(zhuǎn) 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');

某些公交為環(huán)線，此處如果不用 DISTINCT 會出現(xiàn)重復(fù)數(shù)據(jù)。