[SQL]对SQLZOO学习中，有趣的题目记录！

SQL学习

一.

https://sqlzoo.net/wiki/SELECT_from_Nobel_Tutorial/zh

Show the 1984 winners and subject ordered by subject and winner name; but list Chemistry and Physics last.

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

知识点：
1.The expression subject IN (‘Chemistry’,‘Physics’) can be used as a value - it will be 0 or 1.
2.asc 正序，desc 逆序。多个排序条件，根据优先级依次排列。

二.
2.

https://sqlzoo.net/wiki/SELECT_basics/zh

查詢顯示面積為 5,000,000 以上平方公里的國家,該國家的人口密度(population/area)。人口密度並不是 WORLD 表格中的欄,但我們可用公式(population/area)計算出來。

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

知识点：
1.对于不存在的列，可以通过计算获得值，并输出。

三.
9.

https://sqlzoo.net/wiki/SQLZOO:SELECT_from_WORLD_Tutorial/zh

除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。使用 ROUND 函數來顯示的數值到小數點後兩位。

對於南美顯示以百萬計人口，以十億計2位小數GDP。
百萬和十億
除以為1000000（6個零）是以百萬計。除以1000000000（9個零）是以十億計。

select name,Round(population/1000000,2),ROUND(gdp/1000000000,2)
from world 
where continent = 'South America'

知识点：
1.round(value,num) 表示 value 对num进行科学计数法。
具体的：⑴当num>0,表示保存num个小数点。
⑵ 当num<0,则表示对|num|的位进行四舍五入

四.
13.

https://sqlzoo.net/wiki/SQLZOO:SELECT_from_WORLD_Tutorial/zh

Put the continents right…

• Oceania becomes Australasia
• Countries in Eurasia and Turkey go to Europe/Asia
• Caribbean islands starting with ‘B’ go to North America,other Caribbean islands go to South America

Show the name, the original continent and the new continent of all countries.

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

知识点：CASE是一个事件的开关，在事件中可以进行判断替换操作，以END结尾。

五.
5.

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh

Germany德國（人口8000萬），在Europe歐洲國家的人口最多。Austria奧地利（人口850萬）擁有德國總人口的11％。

顯示歐洲的國家名稱name和每個國家的人口population。以德國的人口的百分比作人口顯示。

小數位數
百分號 %
您可以使用函數 CONCAT 增加的百分比符號。

select name,CONCAT(ROUND(population * 100/
                 (select population from world where name = 'Germany'),0),'%')
from world
where continent = 'Europe' 

知识点：
CONCAT： 用来拼串
concat(s1,s2,s3,…) 输出自动拼接。

六.
10.

https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial/zh

有些國家的人口是同洲份的所有其他國的3倍或以上。列出 國家名字name 和 洲份 continent。

select name,continent from world x
where population/3 > 
All(select population from world y
    where x.continent = y.continent 
    and population > 0 and x.name != y.name)

注意：
判断数字时，要在判断条件中加>0 的判断，因为有的数字可能未输入，通过这个条件就可以将NULL的数据过滤掉。

七.
6.

https://sqlzoo.net/wiki/SUM_and_COUNT/zh

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

select continent,count(name)
from world 
group by continent 

知识点：
这题没啥知识点，哈哈，就是group by 好久没用，提示自己别犯蠢。

八.
8.

https://sqlzoo.net/wiki/SUM_and_COUNT/zh

列出有至少100百萬(1億)(100,000,000)人口的洲份。

select continent
from world
group by continent 
having sum(population) >100000000 

知识点：having 对函数值进行二次过滤。

九.
9.

https://sqlzoo.net/wiki/The_JOIN_operation/zh

List every match with the goals scored by each team as shown. This will use “CASE WHEN” which has not been explained in any previous exercises.
mdate team1 score1 team2 score2
1 July 2012 ESP 4 ITA 0
10 June 2012 ESP 1 ITA 1
10 June 2012 IRL 1 CRO 3
…
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.

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, matchid, team1,team2

知识点：
group by 后跟多个条件不会使用！赶紧去学！

https://www.cnblogs.com/87060524test/p/10402784.html

在MYSQL中使用GROUP BY对表中的数据进行分组时，
GROUP BY X意思是将所有具有相同X字段值的记录放到一个分组里，
GROUP BY X, Y意思是将所有具有相同X字段值和Y字段值的记录放到一个分组里。
自己总是模糊记不清：
出现在select后面的字段 要么是是聚合函数中的,要么就是group by 中的.！！

十.
12.

https://sqlzoo.net/wiki/More_JOIN_operations/zh

尊·特拉華達’John Travolta’最忙是哪一年? 顯示年份和該年的電影數目。

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
order by COUNT(title) desc limit 1

知识点：
Count函数可以作为order by 的条件。

十一
10.
Find the routes involving two buses that can go from Craiglockhart to Lochend.
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.

SELECT m.num,m.company,name,n.num,n.company
//Craiglockhart出发能到达的站
FROM (SELECT a.num,a.company,b.stop FROM route a JOIN route b ON a.company=b.company AND a.num=b.num AND a.stop!=b.stop WHERE a.stop=(SELECT id FROM stops WHERE name='Craiglockhart')) AS m
//Lochend出发能到达的站
JOIN (SELECT d.num,d.company,c.stop FROM route c JOIN route d ON c.company=d.company AND c.num=d.num AND c.stop!=d.stop WHERE d.stop=(SELECT id FROM stops WHERE name='Lochend')) AS n
//经过一次转乘
ON  m.stop=n.stop
JOIN stops 
ON m.stop=stops.id
//不是同一辆车直达
WHERE m.num!=n.num
ORDER BY m.num,stops.name,n.num;