SQL學習三部曲

sqlzoo.net

最後用三道練習題來鞏固下自己的學習成果。

SQL ZOO練習題（其中穿插基礎知識）：

1.Show the countries which have a name that includes the word United

SELECT name FROM world

where name like %United%

查找名字里包含『United』的，可以用 % 來完成，%United%表示不管前後是什麼，有多少字元，只要包含United就可以，%United表示以United結尾，United%以United開頭。如果僅指代一個字元用 _ 。

2.Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.

select name, ROUND(gdp/population,-3) FROM world

WHERE gdp>1000000000000

其中ROUND函數可以求精確到哪一位的小數，如果要精確到百或千，那麼就是前面加個負號。

3.Show all details of the presidential winners:

• Theodore Roosevelt
• Woodrow Wilson
• Jimmy Carter
• Barack Obama

SELECT distinct yr, subject, winner FROM nobel

where winner in (Theodore Roosevelt,Woodrow Wilson,Jimmy Carter,Barack Obama)

其中加一個distinct 就不會顯示重複的結果了。

4.Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

select name,concat(round( population/ (select population from world where name=Germany)*100 ,0),%) population from world where continent=Europe

ROUND函數上面講過，這裡最主要的是CONCAT函數，這個函數是用來連接字元串的。

5.List each continent and the name of the country that comes first alphabetically.

SELECT continent,name from world a where name <=ALL(select name from world b where a.continent=b.continent)order by name

其中any,all的用法：

any 可以與=、>、>=、<、<=、<>結合起來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的任何一個數據。

all可以與=、>、>=、<、<=、<>結合是來使用，分別表示等於、大於、大於等於、小於、小於等於、不等於其中的其中的所有數據。

嵌套中的選擇，得分別對錶命名x和y。

6.Show teamname and the total number of goals scored.

SELECT teamname, COUNT(id) FROM eteam JOIN goal ON eteam.id = goal.teamid

GROUP BY teamname;

count( )函數返回指定列的值得數目。

JOIN可以用在兩個或者更多的表中

可以使用的不同的 SQL JOIN 類型：

• INNER JOIN：如果表中有至少一個匹配，則返回行
• LEFT JOIN：即使右表中沒有匹配，也從左表返回所有的行
• RIGHT JOIN：即使左表中沒有匹配，也從右表返回所有的行
• FULL JOIN：只要其中一個表中存在匹配，則返回行

7.Use the COALESCE function and a LEFT JOIN to print the teacher name and department name. Use the string None where there is no department.

COALESCE 是補全函數

原答案：SELECT name, COALESCE(dept, None) FROM teacher;

我的答案：SELECT teacher.name, COALESCE(dept, None)

FROM teacher LEFT JOIN dept on (teacher.dept=dept.id)

最後三個練習題：

1.查詢一張數據表（tb），基本欄位：日期（date），訂單（order）

要求用SQL實現：

周次（week），訂單總和，日均訂單，極大值訂單，極小值訂單

select count(date)/7 from tb

select sum(order) from tb

select sum(order)/count(date) from tb

select MAX(order) from tb

select MIX(order) from tb

2.使用SQL實現以下數據錶行轉列及總分，平均分（數據表：table）

輸出結果：

姓名：name，語文：language，數學：mathematics，外語：foreign

select name, sum(case when subject=Language then score end) as Language,

sum(case when subject=Language then score end)/3 as Language,

sum(case when subject=mathematics then score end) as mathematics,

sum(case when subject=mathematics then score end)/3 as mathematics,

sum(case when subject=foreign then score end) as foreign,

sum(case when subject=foreign then score end)/3 as foreign

from table

group by name

3.查詢數據表（table），基本欄位如下：

省，市，區，店鋪名稱，訂單數

要求按照「區"排序，需要看到每一個業務區內的訂單排名前三名是什麼門店及訂單數

省： province ，市：city ， 區：area ， 名稱：name ， 訂單數：orders

SELECT x.name, x.orders, FROM table AS x

WHERE x.orders = ( SELECT TOP 3 y.orders FROM table AS y

WHERE x.area = y.area order by orders DESC)

如有錯誤，希望你們能夠進行指正，讓我們共同進步！

推薦閱讀：