在學習SQL語法及完成書本上練習之後，想要多做一些關於SQL的練習題加深自己對語法的理解，提升自己的SQL水平。許多人利用SQLZOO進行練習，本文記錄自己在SQLZOO練習中遇到的問題、對問題的的分析及應用SQL查詢是需要注意的問題：

  • 符號問題：英文符號還是中文符號
  • 拼寫問題：注意SQL語言中的關鍵詞以及待查詢問題的拼寫問題
  • 表頭含義：理解表頭含義有助於分析
  • 表間聯繫：
  • JOIN及MORE JOIN後的表格結構，注意其邏輯聯繫。

SQLZOO為在線練習，主要包括以下幾部分內容：

  • SELECT basics ：WHERE、LIKE、IN、AND、ROUND、LENGTH、LEFT、ORDER BY
  • SELECT in SELECT
  • SUM and Count
  • JOIN
  • MORE JOIN
  • USING NULL
  • SELF JOIN

註：本人能力有限，下面問題分析及答案有錯誤之處還請各位大神指出！

  1. SELECT from WORLD Tutorial 6#：Show the countries which have a name that includes the word United。

分析：本題需要查找name中還有「United」的國家，初做本題時還沒有學習LIKE的用法，不過好在第13題題幹中有關於LIKE的簡單介紹，得知LIKE的用法，『United%』為以United開頭，『%United』為以United結尾的字元串，這道題怎麼查找含有『United』的國家，你會了麼？

SELECT name FROM world WHERE name LIKE %United%;

2. SELECT from WORLD Tutorial 8#：Exclusive OR (XOR). Show the countries that are big by area or big by population but not both. Show name, population and area.

分析：XOR在題目前面的描述是寫的很清楚：One or the other (but not both)，兩者只能取其一

SELECT name,population,area FROM world
WHERE area/1000000 >=3 XOR population/1000000>=250;

3. SELECT from WORLD Tutorial 9#:Show thenameandpopulationin millions and the GDP in billions for the countries of thecontinentSouth America. Use the ROUND function to show the values to two decimal places.

分析：ROUND(column,decimals)將數值欄位舍入為指定的小數位數，其中dicimals為規定要返回的小數位數。

ROUND(column,-3):第二位參數為負數，此時ROUND函數的意思是將column小數點左側abs(-3)位四捨五入至千位，小數點左側三位則為0。如ROUND(348,-2)計算出的結果為300。

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

4. SELECT from WORLD Tutorial 12#：Show the name and the capital where the first letters of each match. Dont include countries where the name and the capital are the same word.

分析：LEFT(str,num)：從左側開始返回num個字元。LEFT(text,2)返回值為te

SELECT name, capital FROM world
WHERE LEFT(name,1) = LEFT(capital,1) AND name <> capital;

5. SELECT from Nobel Tutorial 11#:Find all details of the prize won by PETER GRüNBERG

分析：ü為非ASCII字元，無法直接輸入，為了查找滿足要求的數據，這裡使用LIKE避免輸入ü

SELECT * FROM nobel
WHERE winner LIKE Peter GR%NBERG;

6. SELECT from Nobel Tutorial 12#:Find all details of the prize won by EUGENE ONEILL

分析：待查找的字元串中有單引號『，再次添加單引號變為『EUGENE ONEILL』，運行報錯：You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near NEILL at line 2，因為編譯器將『EUGENE O』視為一個字元串，而NEILL則認為缺少單引號『。

SELECT * FROM nobel
WHERE winner = EUGENE ONEILL;

7. SELECT within SELECT Tutorial 5#：Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

分析：題目中查找歐洲各國人口佔德國人口的百分比。SQL中無法直接將小數輸出為百分數，小數為數值型，%為字元型，因此需要利用CONCAT實現字元鏈接。百分比求取利用子查詢，求取出德國人口數量，利用除法，再乘以100，利用前面介紹的ROUND（）函數，加上%則求出歐洲各國人口佔德國人口百分比。

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

8. SELECT within SELECT Tutorial 6#：Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

分析：題目中要找出GDP超過歐洲任一國家GDP的國家，可以先找出歐洲GDP最多的國家A，然後在找出比A的GDP多的國家即為所需查找的內容。注意：如果不添加ALL,WHERE返回的是多個值，將會報錯Subquery returns more than 1 row。

歐洲GDP最多的國家為：

SELECT MAX(GDP) FROM world WHERE continent = Europe AND GDP IS NOT NULL

將此語句作為WHERE語句中判斷條件，

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

還可以寫為：

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

9. SELECT within SELECT Tutorial 7#：Find the largest country (by area) in each continent, show the continent, the name and the area.

分析：選擇各個continent面積最大的國家，該過程中需要不斷的判斷各個國家是否處於同一continent，從而求出各continent面積最大國家，判斷過程是將FROM中的world設置別名，實現在WHERE中的判斷。

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

10.SELECT within SELECT Tutorial 8#: List each continent and the name of the country that comes first alphabetically.

分析：查找每個continent中名字排在第一位的國家。題目重點為判斷如何選擇出同一continent內的國家。可以通過分別設置別名，對於子查詢設定WHERE判斷條件，另w1.continent = w2.continent相等，子查詢返回的是同一continent所有國家的名字，為多行數據，可以利用ALL實現對任一任一國家名字的比較，進而選擇出排名第一位的國家。

SELECT continent,name FROM world AS w1
WHERE name <=ALL(SELECT name
FROM world AS w2 WHERE w1.continent = w2.continent );

11. SELECT within SELECT Tutorial 10#: Some countries have populations more than three times that of any of their neighbours (in the same continent). Give the countries and continents.

分析：此題大體思路延續前面兩道題，依舊利用設置別名實現WHERE語句中的判斷。不同之處在於此題目中要求計算人口為本continent其他國家人口數量的三倍以上的國家A，因此在計算其他人口之和時需要去除國家A的人口，因此判斷條件需要添加w1.name<>w2.name.

SELECT name, continent FROM world AS w1
WHERE population > ALL
(SELECT 3*population FROM world AS w2
WHERE w1.continent=w2.continent AND w1.name <> w2.name);

12. The JOIN OPERATION 8#：show the name of all players who scored a goal against Germany.

分析：分析：題目中要求列出對陣德國時所有進球的球員，由題目得知在game表德國必須屬於參賽球隊，即德國為team1或者team2，而在goal表中則teamid<>德國，從而篩選出符合條件的球員。用於表格連接需使用JOIN...ON，其中ON是不可或缺的。

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

或者

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

13. The JOIN OPERATION 13#：Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1. Sort your result by mdate, matchid, team1 and team2.

分析：此題求各場比賽的比賽日期以及各球隊進球數據，goal表中給出的是比賽id以及進球球隊、球員及進球時間，因此為求出進球總數目，需要各場將各球隊進球數據相加求和，即用到聚合函數SUM，為求SUM需要用到CASE WHEN:WHEN teamid = team1，THEN 1,ELSE 0 END。

使用聚合函數後，需使用GROUP BY。使用GROUP BY時需注意，SELECT中的欄位均需包含在GROUP BY語句中，即此題目中 GROUP BY mdate，team1，team2三個欄位。GROUP BY X，Y多個欄位意思是將所有具有相同X和Y欄位的值放置到一個分組內。因此本題 GROUP BY mdate,team1,team2 意思是將日期相同，team1及team2隊伍相同分組，也就意味分組為同一場比賽。FROM中為什麼用LEFT JOIN還請給為大神給與解釋。

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 LEFT JOIN goal ON matchid=id
GROUP BY mdate,team1,team2

14. MORE JOIN OPERATION 8#：List the films in which Harrison Ford has appeared.

分析：通過actor表可以查出Harrison的id，casting表中同時具有actorid及movieid，因此將casting與movie聯結，並將actor獲取的id作為WHERE中的判斷條件。

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

自己所做的答案為:

SELECT DISTINCT title FROM movie JOIN casting ON id = movieid
WHERE id = ANY(SELECT movieid FROM actor JOIN casting ON actorid = id
WHERE actorid = (SELECT id FROM actor WHERE name = Harrison Ford));

雖然結果也是對的，但是出現嵌套再嵌套，忽視了casting中actorid與actor表的id的關係。同樣的錯誤出現在JOIN章節中第10題：查找1962年的電影及主演演員。第10題中需要MORE JOIN，在WHERE判斷條件中沒有考慮JOIN後表格的聯繫，再次利用子查詢進行查找，徒增閱讀的複雜性。

15. SELF JOIN OPERATION 10#：Find the routes involving two buses that can go from Craiglockhart to Sighthill.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.

分析：題中給出提示-Self-join twice to find buses that visit Craiglockhart and Sighthill, then join those on matching stops.是否可以利用A---B---C，其中A，B，C分別為起始站、換乘站、終點站。A---B間為route ab，B---C 間為route bc，A與route ab 連接，ab與B連接，B與bc連接，bc與C連接。通過WHERE判斷條件A= Craiglockhart，C= Sighthill，而route bc<> route ab進行判斷？

你問我問什麼不放上代碼？因為我麼寫對，還在思考中？或許上面思路不對？好吧，大神來拯救我把！

推薦閱讀：

相關文章