SQLZOO是一個適合新手練習SQL語句的網站，以下是我自己在做練習時的答案，供大家參考。

0 SELECT基礎

SELECT basic/zh

1. 顯示德國Germany的人口

SELECT population FROM world
WHERE name = Germany;

2. 查詢面積為5000000以上平方公里的國家，對每個國家顯示她的名字和人均國內生產總值(gdp/population)

SELECT name, gdp/population FROM world
WHERE area > 5000000;

3. 顯示「Ireland 愛爾蘭」、「Iceland 冰島」、「Denmark 丹麥」的國家名稱和人口

SELECT name, population FROM world
WHERE name IN (Ireland, Iceland, Denmark);

4. 顯示面積為200000及250000之間的國家名稱和該國面積

SELECT name, area FROM world
WHERE area BETWEEN 200000 AND 250000;

1. SELECT names/zh

1. 找出以Y開頭的國家

SELECT name FROM world
WHERE name LIKE Y%;

2. 找出以Y結尾的國家

SELECT name FROM world
WHERE name LIKE %Y;

3. 找出所有國家，其名字包括字母x

SELECT name FROM world
WHERE name LIKE %X%;

4. 找出所有國家，其名字以land結尾

SELECT name FROM world
WHERE name LIKE %land;

5. 找出所有國家，其名字以C開頭，以ia結尾

SELECT name FROM world
WHERE name LIKE C%ia;

6. 找出所有國家，其名字包括字母oo

SELECT name FROM world
WHERE name LIKE %oo%;

7. 找出所有國家，其名字包括三個或以上的a

SELECT name FROM world
WHERE name LIKE %a%a%a%;

8. 找出所有國家，其名字以t作第二個字母

SELECT name FROM world
WHERE name LIKE _t%
ORDER BY name;

9. 找出所有國家，其名字都有兩個字母o，被另外兩個字母相隔著

SELECT name FROM world
WHERE name LIKE %o__o%;
-- 兩個字母o之間是兩個下劃線

10. 找出所有國家，其名字都是4個字母的

SELECT name FROM world
WHERE name LIKE ____;
-- 單引號中間是4個下劃線

11. 顯示所有國家名字，其首都和國家名字是相同的

SELECT name
FROM world
WHERE name = capital;

12. 顯示所有國家名字，其首都是國家名字加上「 City」

SELECT name
FROM world
WHERE capital = CONCAT(name, City);

13. 找出所有首都和其國家名字，首都中要包括其國家名字

SELECT capital, name
FROM world
WHERE capital LIKE CONCAT(%, name, %);

14. 找出所有首都和其國家名字，首都中要包括其國家名字，但是首都和國家名字不能重複

SELECT name, capital
FROM world
WHERE capital LIKE CONCAT(%, name, %)
AND capital <> name;

15. 顯示國家名字和其延伸詞，如果首都是國家名字的延伸

SELECT name,REPLACE(capital, name, )
FROM world
WHERE capital LIKE CONCAT(%, name, %)
AND capital <> name;

SELECT Quiz/zh

1. C
2. E
3. E
4. C
5. C
6. C
7. C

2. SELECT from world

1. 觀察運行一個簡單的SQL命令的結果

SELECT name, continent, population
FROM world;

2. 顯示具有至少2億人口的國家名稱

SELECT name
FROM world
WHERE population>200000000;

3. 找出有至少2億人口的國家名稱，及人均國內生產總值

SELECT name, gdp/population
FROM world
WHERE population>200000000;

4. 顯示『South America』南美洲大陸的國家名字和以百萬為單位的人口數

SELECT name, population/1000000
FROM world
WHERE continent = South America;

5. 顯示法國、德國、義大利（France，Germany，Italy）的國家名稱和人口

SELECT name, population
FROM world
WHERE name in (France, Germany, Italy);

6. 顯示名稱中包含單詞「United」的國家

SELECT name
FROM world
WHERE name LIKE %United%;

7. 成為大國的兩種方式：面積大於三百萬平方公里，或者人口大於2.5億。請顯示大國的名稱、人口和麪積

SELECT name, population, area
FROM world
WHERE area > 3000000 OR population > 250000000;

8. 顯示以人口或者面積為大國的國家名稱、人口和麪積，但不能兩者同時滿足

SELECT name, population, area
FROM world
WHERE (area > 3000000 and population < 250000000)
OR (area < 3000000 AND population > 250000000)；

9. 顯示南美洲的國家名稱、人口、GDP，人口以百萬為單位，GDP以十億為單位，均保留兩位小數

SELECT name, ROUND(population/1000000, 2), ROUND(gdp/1000000000, 2)
FROM world
WHERE continent = South America;

10. 顯示至少有一萬億國內生產總值的國家名稱和人均GDP，數值四捨五入到最接近的$1000

SELECT name, ROUND(gdp/population, -3)
FROM world
WHERE gdp > 1000000000000;

11. 對於名稱以N開頭的國家，如果其大陸是「Oceania「，將其替換為」Australasia「，顯示國家名稱、大陸

SELECT name, CASE WHEN continent=Oceania THEN Australasia ELSE continent END
FROM world
WHERE name LIKE N%;

12. 對於名稱以A或B開頭的國家，如果其大陸是「Europe」或者「Aisa」，將其替換為「Eurasia」；如果其大陸是「North America」、」South America「、「Caribbean」，將其替換為「America」。顯示國家名稱、大陸

SELECT name,
CASE WHEN continent IN (Europe, Asia) THEN Eurasia
WHEN continent IN (North America, South America, Caribbean) THEN America
ELSE continent END
FROM world
WHERE name LIKE A% OR name LIKE B%;

13. 對於大陸為「Oceania」的國家，將其替換為「Australasia」；對於大陸為「Eurasia」或者「Turkey」的國家，將其替換為「Europe/Asia」；對於大陸為「Caribbean」且名稱以B開頭的國家，將其替換為「North America」；對於其他大陸為「Caribbean」的國家，將其替換為「South America」。按照名稱順序排序。

SELECT name,
continent,
CASE WHEN continent = Oceania THEN Australasia
WHEN continent IN (Eurasia,Turkey) THEN Europe/Asia
WHEN continent = Caribbean AND name LIKE B% THEN North America
WHEN continent = Caribbean AND name NOT LIKE B% THEN South America
ELSE continent END
FROM world
ORDER BY name;

BBC QUIZ/zh

1. E
2. D
3. B
4. D
5. B
6. D
7. C

3. SELECT from Nobel Tutorial/zh

1. 查詢1950年諾貝爾獎的獎項資料

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950;

2. 顯示誰贏得了1962年文學獎

SELECT winner
FROM nobel
WHERE yr = 1962 AND subject = Literature;

3. 顯示「愛因斯坦」的獲獎年份和獎項

SELECT yr, subject
FROM nobel
WHERE winner = Albert Einstein;

4. 顯示2000年及以後的和平獎得獎者

SELECT winner
FROM nobel
WHERE yr >= 2000 AND subject = Peace;

5. 顯示1980年至1989年（包含首尾）的文學獎（Literature）獲獎者所有細節（年、主題、獲獎者）

SELECT *
FROM nobel
WHERE yr BETWEEN 1980 AND 1989 AND subject = Literature;

6. 顯示總統獲勝者（西奧多·羅斯福、伍德羅·威爾遜、吉米·卡特）的所有細節

SELECT * FROM nobel
WHERE winner IN (Theodore Roosevelt, Woodrow Wilson, Jimmy Carter);

7. 顯示名字為John的得獎者

SELECT winner
FROM nobel
WHERE winner LIKE John%;

8. 顯示1980年物理學獎獲獎者、1984年化學獎獲獎者的獲獎年份、獎項和獲獎者名字

SELECT *
FROM nobel
WHERE (yr = 1980 AND subject = physics)
OR (yr = 1984 AND subject = chemistry);

9. 查看1980年獲獎的詳細信息，但不包括化學獎和醫學獎

SELECT *
FROM nobel
WHERE yr = 1980
AND subject NOT IN (Chemistry, Medicine)

10. 顯示早期的醫學家得獎者(1910年之前，不包括1910年)，及近年文學獎得獎者(2004年以後，包括2004年)

SELECT *
FROM nobel
WHERE yr < 1910 AND subject = Medicine
OR yr >= 2004 AND subject = Literature;

11. 顯示獲獎者PETER GRüNBERG的詳細獲獎資料

SELECT *
FROM nobel
WHERE winner = PETER GRüNBERG;

12. 查找EUGENE O』NEILL得獎的所有細節

SELECT *
FROM nobel
WHERE winner = EUGENE ONEILL;

13. 列出爵士（名字以Sir開頭）的獲獎者、年份、獎項。先顯示最新獲獎者，然後同年再按名稱順序排列

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

14. 列出1984年的獲獎者名字和獎項，按獎項和獲獎者名字排序，化學獎和物理學獎放在最後

SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY subject IN (Physics, Chemistry), subject, winner;
-- subject IN (Physics, Chemistry)可以被當作一個值為0或1的數字

Nobel QUIZ

1. E
2. C
3. B
4. C
5. C
6. C
7. D

4. 子查詢

Using nested SELECT/zh

1. 列出與巴西在同一個洲份的國家名稱

SELECT name
FROM world
WHERE continent = (SELECT continent FROM world WHERE name = Brazil);

2. 列出與巴西、墨西哥相同洲份的每個國家的名稱和洲份

SELECT name, continent
FROM world
WHERE continent IN
(SELECT continent
FROM world WHERE name=Brazil
OR name=Mexico);

3. 顯示中國人口是英國人口的多少倍

SELECT population / (SELECT population FROM world
WHERE name=United Kingdom)
FROM world
WHERE name = China;

4. 找出哪些國家的人口高於歐洲每一國的人口

SELECT name FROM world
WHERE population > ALL (SELECT population FROM world
WHERE continent=Europe);

SELECT within SELECT Tutorial/zh

1. 列出人口高於俄羅斯的國家名稱

SELECT name
FROM world
WHERE population >
(SELECT population FROM world
WHERE name=Russia);

2. 列出歐洲人均GDP高於英國的國家名稱

SELECT name
FROM world
WHERE gdp/population >
(SELECT gdp/population FROM world
WHERE name = United Kingdom)
AND continent = Europe;

3. 在阿根廷和澳大利亞所在的洲份中，列出國家名稱和洲份，按國家名稱排序

SELECT name, continent
FROM world
WHERE continent IN (SELECT continent FROM world
WHERE name = Argentina OR name = Australia)
ORDER BY name;

4. 列出人口比加拿大多但比波蘭少的國家名稱和人口

SELECT name, population
FROM world
WHERE population >
(SELECT population FROM world
WHERE name = Canada)
AND population <
(SELECT population FROM world
WHERE name = Poland);

5. 顯示歐洲的國家名稱和人口，人口以德國人口的百分比顯示，保留整數部分

SELECT name,
CONCAT(ROUND(population/(SELECT population FROM world
WHERE name = Germany)*100, 0), %)
FROM world
WHERE continent = Europe;

6. 列出GDP比歐洲所有國家都要高的國家名稱

SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp FROM world
WHERE continent = Europe AND gdp > 0);

7. 在每一個洲中找出面積最大的國家，列出洲份、國家名稱、面積

SELECT continent, name, area
FROM world x
WHERE area >= ALL
(SELECT area
FROM world y
WHERE y.continent=x.continent AND area>0);

8. 列出洲份名稱和每個洲份中國家名稱按字母順序排首位的國家名

SELECT continent, name
FROM world x
WHERE name <= ALL(SELECT name
FROM world y
where x.continent = y.continent);

9. 找出洲份，其中全部國家都有不大於25000000的人口，在這些洲份中列出國家名稱、洲份和人口

SELECT name, continent, population
FROM world
WHERE continent IN (SELECT DISTINCT continent FROM world x
WHERE 25000000 >= (SELECT MAX(population) FROM world y
WHERE x.continent = y.continent));

10. 有些國家的人口是同洲份的所有其他國家的3倍或以上。列出這些國家的名稱和洲份

SELECT name, continent
FROM world x
WHERE x.population / 3 >= ALL(SELECT population FROM world y
WHERE y.continent = x.continent AND y.name <> x.name);

The nobel table can be used to practice more subquery./zh

1. 找出與紅十字國際委員會同年得獎的文學獎得獎者和年份

SELECT winner, yr
FROM nobel
WHERE yr IN (SELECT yr FROM nobel
WHERE winner = International Committee of the Red Cross) AND subject = Literature;

2. 找出與益川敏英同年獲得物理學獎的日本人的名字

SELECT winner
FROM nobel
WHERE yr = (SELECT yr FROM nobel
WHERE winner = Toshihide Maskawa)
AND subject = Physics
AND winner <> Toshihide Maskawa;

3. 首次頒發的經濟學獎的得獎者是誰

SELECT winner
FROM nobel
WHERE yr = (SELECT MIN(yr) FROM nobel y
WHERE subject = Economics)
AND subject = Economics;

4. 哪幾年頒發了物理學獎，但沒有頒發化學獎

SELECT DISTINCT yr
FROM nobel
WHERE yr IN (SELECT yr FROM nobel
WHERE subject = Physics)
AND yr NOT IN (SELECT yr FROM nobel
WHERE subject = Chemistry);

5. 列出的獎人數多於12人的年份、獎項和得獎者

SELECT * FROM nobel
WHERE yr IN (SELECT yr FROM nobel
GROUP BY yr
HAVING COUNT(*) > 12);

6. 列出獲獎次數多於1次的獲獎者名字、獲獎年份、獎項，先按名字再按年份排序

SELECT winner, yr, subject
FROM nobel
WHERE winner IN (SELECT winner FROM nobel
GROUP BY winner
HAVING COUNT(*) > 1)
ORDER BY winner, yr;

Nested SELECT Quiz/zh

1. C
2. B
3. A
4. D
5. B
6. B
7. B

5. 羣組函數

SUM and COUNT/zh

1. 顯示世界的總人口

SELECT SUM(population)
FROM world;

2. 列出所有洲份，每個只有一次

SELECT DISTINCT continent
FROM world;

3. 找出非洲的GDP總和

SELECT SUM(gdp)
FROM world
WHERE continent = Africa;

4. 有多少個國傢俱有至少一百萬平方公里的面積

SELECT count(name)
FROM world
WHERE area >= 1000000;

5. 法國、德國、西班牙的總人口是多少

SELECT SUM(population)
FROM world
WHERE name IN (France, Germany, Spain);

6. 對於每一個洲份，顯示洲份和國家的數量

SELECT continent, COUNT(*)
FROM world
GROUP BY continent;

7. 對於每一個洲份，顯示洲份和至少有1000萬人口的國家數目

SELECT continent, COUNT(*)
FROM world
WHERE population >= 10000000
GROUP BY continent;

8. 列出有至少一億人口的洲份

SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000;

The nobel table can be used to practice more SUM and COUNT functions./zh

1. 找出一共頒發了多少個諾貝爾獎

SELECT COUNT(*)
FROM nobel;

2. 列出每個獎項，只列一次

SELECT subject
FROM nobel
GROUP BY subject;

3. 找出物理學獎的頒獎總次數

SELECT COUNT(*)
FROM nobel
WHERE subject = Physics;

4. 對每一個獎項，列出頒獎數目

SELECT subject, COUNT(*)
FROM nobel
GROUP BY subject;

5. 對每一個獎項，列出首次頒發的年份

SELECT DISTINCT subject, yr
FROM nobel x
WHERE yr <= ALL(SELECT yr FROM nobel y
WHERE x.subject = y.subject);

6. 對每一個獎項，列出2000年頒發的數目

SELECT subject, COUNT(*)
FROM nobel
WHERE yr = 2000
GROUP BY subject;

7. 對每一個獎項，列出有多少個不同的得獎者

SELECT subject, COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject;

8. 對每一個獎項，列出有多少年曾頒過獎

SELECT subject, COUNT(DISTINCT yr)
FROM nobel
GROUP BY subject;

9. 列出哪年曾同時有三個物理學獎獲得者

SELECT yr
FROM nobel
WHERE subject = Physics
GROUP BY yr
HAVING COUNT(winner) = 3;

10. 列出誰得獎多於一次

SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(*) > 1;

11. 列出誰獲得多於一個獎項

SELECT DISTINCT x.winner
FROM nobel x, nobel y
WHERE x.winner = y.winner AND x.subject <> y.subject;

12. 哪年哪獎項，是同一獎項頒發給三個人，只列出2000年及以後

SELECT yr, subject
FROM nobel
WHERE yr >= 2000
GROUP BY yr, subject
HAVING COUNT(*) = 3;

SUM and COUNT Quiz

1. C
2. A
3. D
4. E
5. B
6. E
7. D
8. D

6. 合併兩個表格

The JOIN operation/zh

1. 列出代表德國隊進球的賽事編號和球員名

SELECT matchid, player
FROM goal
WHERE teamid = GER;

2. 顯示賽事1012的id，stadium，team1，team2

SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012;

3. 顯示每一個德國進球的球員名、隊伍名、場館和日期

SELECT goal.player, goal.teamid, game.stadium, game.mdate
FROM game JOIN goal ON game.id=goal.matchid
WHERE goal.teamid = GER;

4. 列出球員名字叫Mario且有進球的隊伍1、隊伍2、球員名

SELECT game.team1, game.team2, goal.player
FROM game JOIN goal ON game.id = goal.matchid
WHERE goal.player LIKE Mario%;

5. 列出每場球賽中首十分鐘有進球的球員、隊伍名、教練、進球時間

SELECT goal.player, goal.teamid,
eteam.coach, goal.gtime
FROM goal JOIN eteam ON goal.teamid = eteam.id
WHERE gtime<=10

6. 列出』Fernando Santos』作為隊伍1的教練的賽事日期和隊伍名

SELECT game.mdate, eteam.teamname
FROM game JOIN eteam ON game.team1 = eteam.id
WHERE eteam.coach = Fernando Santos;

7. 列出場館』National Stadium, Warsaw』的進球球員

SELECT goal.player
FROM goal JOIN game ON goal.matchid = game.id
WHERE game.stadium = National Stadium, Warsaw;

8. 列出全部賽事中射入德國球門的球員名字

SELECT DISTINCT goal.player
FROM goal JOIN game ON goal.matchid = game.id
WHERE (teamid <> GER AND team1 = GER)
OR (teamid <> GER AND team2 = GER);

9. 列出隊伍名稱和該隊進球總數

SELECT eteam.teamname, COUNT(*)
FROM eteam JOIN goal ON eteam.id=goal.teamid
GROUP BY eteam.teamname;

10. 列出場館名和在該場館的進球數字

SELECT game.stadium, COUNT(*)
FROM game JOIN goal ON game.id=goal.matchid
GROUP BY game.stadium;

11. 每一場波蘭有參與的賽事中，列出賽事編號、日期和進球數字

SELECT goal.matchid, game.mdate, count(*)
FROM game JOIN goal ON goal.matchid = game.id
WHERE team1 = POL OR team2 = POL
GROUP BY goal.matchid, game.mdate;

12. 每一場德國有參與的賽事中，列出賽事編號、日期和德國的進球數字

SELECT goal.matchid, game.mdate, COUNT(*)
FROM game JOIN goal ON goal.matchid = game.id
WHERE (team1 = GER OR team2 = GER) AND goal.teamid = GER
GROUP BY goal.matchid, game.mdate;

13. 列出每場比賽中的比賽日期、隊伍1、隊伍1進球數、隊伍2、隊伍2進球數，並按照比賽日期、隊伍1、隊伍2排序

SELECT mdate,
team1,
SUM(CASE WHEN teamid=team1
THEN 1 ELSE 0 END) score1,
team2,
SUM(CASE WHEN teamid=team2
THEN 1 ELSE 0 END) score2
FROM game JOIN goal ON matchid = id
GROUP BY mdate, team1, team2
ORDER BY mdate, team1, team2;

Music Tutorial/zh

1. 找出歌曲『Alison』的碟名和歌手

SELECT title, artist
FROM album JOIN track ON (album.asin=track.album)
WHERE song = Alison;

2. 哪一歌手錄了歌曲『Exodus』

SELECT artist
FROM album JOIN track ON (album.asin=track.album)
WHERE song = Exodus;

3. 顯示專輯『Blur』的每一首歌名

SELECT song
FROM album JOIN track ON (album.asin=track.album)
WHERE title = Blur;

4. 顯示每一張專輯的名稱和歌曲的數量

SELECT title, COUNT(*)
FROM album JOIN track ON (album.asin=track.album)
GROUP BY title;

5. 顯示專輯中的歌名有『Heart』的歌曲數量

SELECT title, COUNT(*)
FROM album JOIN track ON (album.asin=track.album)
WHERE song LIKE %Heart%
GROUP BY title;

6. 主題歌曲是歌名和專輯名稱相同的歌曲，找出主題歌曲

SELECT song
FROM album JOIN track ON (album.asin=track.album)
WHERE song = title;

7. 同名大碟是指專輯名和歌手名相同，找出同名大碟

SELECT title
FROM album
WHERE title = artist;

8. 找出歌曲收錄在兩張以上專輯中的情況，列出歌曲名和收錄次數

SELECT song, COUNT(DISTINCT asin)
FROM album JOIN track ON (album.asin=track.album)
GROUP BY song
HAVING COUNT(DISTINCT asin) > 2;

9. 好賣大碟是指大碟中每首歌曲的價格少於5角的大碟。找出好賣大碟，列出大碟名稱，售價和歌曲數量

SELECT title, price, COUNT(song)
FROM album JOIN track ON (album.asin=track.album)
GROUP BY title, price
HAVING price / COUNT(song) < 0.5;

10. 按歌曲數量由多到少列出每一大碟的碟名和歌曲數量

SELECT title, COUNT(song)
FROM album JOIN track ON (album.asin=track.album)
GROUP BY title
ORDER BY COUNT(song) DESC;

JOIN Quiz

1. D
2. C
3. A
4. A
5. B
6. C
7. B

7. 更多的合併操作

More JOIN operations/zh

1. 列出1962年首映的電影id和title

SELECT id, title
FROM movie
WHERE yr = 1962;

2. 列齣電影「Citizen Kane」的首映年份

SELECT yr
FROM movie
WHERE title = Citizen Kane;

3. 列出全部Star Trek系列的電影(此係列電影都以Star Trek為電影名稱的開頭)，包括id, title和yr，按年份順序排列

SELECT id, title, yr
FROM movie
WHERE title LIKE Star Trek%
ORDER BY yr;

4. id是11768，11955，21191的電影名稱是什麼

SELECT title
FROM movie
WHERE id IN (11768, 11955, 21191);

5. 女演員『Glenn Close』的編號id是什麼

SELECT id
FROM actor
WHERE name = Glenn Close;

6. 電影『Casablanca』的編號id是什麼

SELECT id
FROM movie
WHERE title = Casablanca;

7. 列齣電影『Casablanca』的演員名單，使用上一題得到的movieid

SELECT name
FROM actor JOIN casting ON id = actorid
WHERE movieid = 11768;

8. 顯示電影『Alien』的演員清單

SELECT name
FROM actor JOIN casting ON id = actorid
WHERE movieid = (SELECT id FROM movie
WHERE title = Alien);

9. 列出演員』Harrison Ford』曾出演的電影名稱

-- 方法1
SELECT title
FROM movie
WHERE id IN (SELECT movieid
FROM casting
WHERE actorid = (SELECT id
FROM actor
WHERE name = Harrison Ford));
-- 方法2
SELECT title
FROM movie JOIN casting ON id = movieid
WHERE actorid = (SELECT id
FROM actor
WHERE name = Harrison Ford);

10. 列出演員』Harrison Ford』曾出演的電影名稱，但是他不是第一主角

SELECT title
FROM movie JOIN casting ON id = movieid
WHERE actorid = (SELECT id
FROM actor
WHERE name = Harrison Ford)
AND ord <> 1;

11. 列出1962年首映的電影及它的第一主角

SELECT movie.title, actor.name
FROM casting JOIN movie ON casting.movieid = movie.id
JOIN actor ON casting.actorid = actor.id
WHERE yr = 1962 AND ord = 1;

12. 『John Travolta』最忙是哪一年？顯示年份和該年的電影數目

-- 方法1
SELECT yr,COUNT(title)
FROM movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name=John Travolta
GROUP BY yr
HAVING COUNT(title) = (SELECT MAX(c)
FROM (SELECT yr,COUNT(title) AS c
FROM movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name=John Travolta
GROUP BY yr) AS t);
-- 方法2
SELECT yr, count(title)
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE name = John Travolta
GROUP BY yr
ORDER BY count(title) DESC
LIMIT 0,1;

13. 列出演員』Julie Andrews』曾參與的電影名稱及其第一主角

SELECT title, name
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE movie.id IN (SELECT movieid FROM casting
WHERE actorid = (SELECT id FROM actor
WHERE name = Julie Andrews))
AND ord = 1;

14. 按字母順序列出哪些演員曾作30次及以上第一主角

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;

15. 列出1978年首映的電影名稱及角色數目，按此數目由多到少排列

SELECT title, COUNT(*)
FROM movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE yr = 1978
GROUP BY title
ORDER BY COUNT(*) DESC, movieid;

16. 列出曾與演員』Art Garfunkel』合作過的演員姓名

SELECT name
FROM movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE movieid IN (SELECT movieid
FROM casting JOIN actor ON actor.id = actorid
WHERE name = Art Garfunkel)
AND name <> Art Garfunkel;

JOIN Quiz 2

1. C
2. E
3. C
4. B
5. D
6. C
7. B

8. NULL值

Using NULL

1. 列出係為NULL的教師名稱

SELECT name
FROM teacher
WHERE dept IS NULL;

2. 注意：INNER JOIN會去除沒有系的教師和沒有教師的系

SELECT teacher.name, dept.name
FROM teacher INNER JOIN dept ON (teacher.dept=dept.id);

3. 使用不同的JOIN方式顯示所有的教師名稱

SELECT teacher.name, dept.name
FROM teacher LEFT JOIN dept ON (teacher.dept=dept.id);

4. 使用不同的JOIN方式顯示所有的系名稱

SELECT teacher.name, dept.name
FROM teacher RIGHT JOIN dept ON (teacher.dept=dept.id);

5. 顯示教師名稱及其電話號碼，如果沒有手機號碼顯示『07986 444 2266』

SELECT name, COALESCE(mobile, 07986 444 2266)
FROM teacher;

6. 顯示所有的教師名稱及其系名稱，如果沒有系則顯示『None』

SELECT teacher.name, COALESCE(dept.name, None)
FROM teacher LEFT JOIN dept ON teacher.dept = dept.id;

7. 顯示教師的數量和手機號碼的數量

SELECT COUNT(name), COUNT(mobile)
FROM teacher;

8. 顯示所有的系的名稱和每個系的教師的數量

SELECT dept.name, COUNT(teacher.id)
FROM teacher RIGHT JOIN dept ON teacher.dept = dept.id
GROUP BY dept.name;

9. 如果dept為1或2，顯示『Sci』，否則顯示『Art』

SELECT name,
CASE WHEN dept = 1 OR dept = 2
THEN Sci ELSE Art END
FROM teacher;

10. 如果dept為1或2，顯示『Sci』；如果dept為3，顯示『Art』；否則顯示『None』

SELECT name,
CASE WHEN dept = 1 OR dept = 2 THEN Sci
WHEN dept = 3 THEN Art
ELSE None END
FROM teacher;

Scottish Parliament/zh

1. 找出不屬於任何政黨的議會成員名字

SELECT name
FROM msp
WHERE party IS NULL;

2. 列出每個黨及其領導人

SELECT name, leader
FROM party;

3. 列出有領導人的黨及其領導人

SELECT name, leader
FROM party
WHERE leader IS NOT NULL;

4. 列出政黨名單，當中至少有一名黨員在議會內

SELECT party.name
FROM party JOIN msp ON party.code = msp.party
WHERE msp.name IS NOT NULL
GROUP BY party.name;

5. 列出完整的議會成員名單及其所屬政黨名稱，按msp.name排序

SELECT msp.name, party.name
FROM msp LEFT JOIN party ON party.code = msp.party
ORDER BY msp.name;

6. 列出議會中每一政黨的黨員人數

SELECT party.name, COUNT(msp.name)
FROM msp JOIN party ON party.code = msp.party
GROUP BY party.name;

7. 列出議會中每一政黨的黨員人數，包括沒有黨員在議會中的政黨

SELECT party.name, COUNT(msp.name)
FROM msp RIGHT JOIN party ON party.code = msp.party
GROUP BY party.name;

8+ Numeric Examples

NSS Tutorial

1. 回答問題1、在』Edinburgh Napier University』、學習』(8) Computer Science』的人中強烈同意的數量

SELECT A_STRONGLY_AGREE
FROM nss
WHERE question=Q01
AND institution=Edinburgh Napier University
AND subject=(8) Computer Science；

2. 問題15中分數至少為100的學校和專業

SELECT institution, subject
FROM nss
WHERE question=Q15 AND score >= 100;

3. 問題15中專業為』(8) Computer Science』，分數小於50的學校和分數

SELECT institution, score
FROM nss
WHERE question=Q15
AND subject=(8) Computer Science
AND score < 50;

4. 問題22中，專業為』(8) Computer Science』和』(H) Creative Arts and Design』的總人數

SELECT subject, SUM(response)
FROM nss
WHERE question=Q22
AND (subject=(8) Computer Science OR subject= (H) Creative Arts and Design)
GROUP BY subject;

5. 問題22中，專業為』(8) Computer Science』和』(H) Creative Arts and Design』，且強烈同意的總人數

SELECT subject,SUM(A_STRONGLY_AGREE/100*response)
FROM nss
WHERE question=Q22
AND (subject=(8) Computer Science OR subject= (H) Creative Arts and Design)
GROUP BY subject;

6. 問題22中，專業為』(8) Computer Science』和』(H) Creative Arts and Design』，且強烈同意的人數的百分比

-- A_STRONGLY_AGREE是強烈同意人數佔全體人數的百分比，這裡要求同一個subject中強烈同意人數的百分比 */
SELECT subject, ROUND(SUM(A_STRONGLY_AGREE/100*response)/SUM(response)*100,0)
FROM nss
WHERE question=Q22
AND (subject=(8) Computer Science OR subject= (H) Creative Arts and Design)
GROUP BY subject;

7. 問題22中每一個帶』Manchester』的學校的平均分

SELECT institution,ROUND(SUM(score*response) / SUM(response),0)
FROM nss
WHERE question=Q22 AND institution LIKE %Manchester%
GROUP BY institution

8. 問題01中每一個帶』Manchester』的學校的樣本總量和計算機科學專業的學生數量

SELECT institution,
SUM(sample),
SUM(CASE WHEN subject=(8) Computer Science THEN sample ELSE 0 END)
FROM nss
WHERE question=Q01 AND institution LIKE %Manchester%
GROUP BY institution;

Using Null Quiz

1. E
2. C
3. E
4. B
5. A
6. A

9. 自我合併

Self join

1. 一共有多少個站

SELECT COUNT(id)
FROM stops;

2. 找出』Craiglockhart』站的id

SELECT id
FROM stops
WHERE name = Craiglockhart;

3. 找出『LRT』公司的4路車的站臺id和名稱

SELECT id, name
FROM stops JOIN route ON id = stop
WHERE num = 4 AND company = LRT;

4. 找出經過London Road (149)站或者Craiglockhart (53)站的公司，公交車線路，經過次數為兩次

SELECT company, num, COUNT(*)
FROM route
WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*) = 2;

5. 找出從Craiglockhart(53)站到London Road(149)站的路線

SELECT a.company, a.num, a.stop, b.stop
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=53 AND b.stop = 149;

6. 找出從Craiglockhart(53)站到London Road(149)站的路線，以站臺名為查詢條件

SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name = Craiglockhart
AND stopb.name = London Road;

7. 找出從Haymarket(115)站到Leith(137)站的路線，以站臺名為查詢條件

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name=Haymarket
AND stopb.name = Leith;

8. 找出從Craiglockhart站到Tollcross站的路線，以站臺名為查詢條件

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name=Craiglockhart
AND stopb.name = Tollcross;

9. 找出公司為『LRT』，可以乘坐一輛公共汽車從Craiglockhart站到達的站臺名

SELECT DISTINCT stopb.name, a.company, a.num
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
JOIN stops stopa ON (a.stop=stopa.id)
JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name=Craiglockhart
AND a.company = LRT;

10. 找到乘坐兩趟公共汽車從Craiglockhart到Lochend的路線

SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company
FROM (SELECT start1.num, start1.company, stop1.stop
FROM route AS start1 JOIN route AS stop1
ON start1.num = stop1.num AND start1.company = stop1.company AND start1.stop != stop1.stop
WHERE start1.stop = (SELECT id FROM stops WHERE name = Craiglockhart)) AS bus1
-- 找出從Craiglockhart出發坐一輛巴士可以到達的線路和站臺
JOIN (SELECT start2.num, start2.company, start2.stop
FROM route AS start2 JOIN route AS stop2
ON start2.num = stop2.num AND start2.company = stop2.company AND start2.stop != stop2.stop
WHERE stop2.stop = (SELECT id FROM stops WHERE name = Lochend)) AS bus2
-- 找出坐一輛巴士可以到達Lochend的線路及站臺
ON bus1.stop = bus2.stop
-- 找出bus1的到達站和bus2的出發站相同的路線
JOIN stops ON bus1.stop = stops.id;

Self join Quiz

1. C
2. E
3. D

推薦閱讀：

相關文章