sql zoo错题难题集

知识点：模糊查询

LIKE 

• 多字符 %
• 单字符 -

**知识点 ：函数concat()COALESCE() **

concat() 连接字符串
eg

concat(name,'city')

COALESCE() 替换NULL值

SELECT COALESCE(NULL, 0);

null替换成0

7.找出所有首都和其國家名字,而首都要有國家名字。

SELECT 	capital
		,name
FROM world
WHERE capital LIKE concat('%','name','%')

不能capital = concat(’%’,‘name’,’%’)，因为模糊查询用LIKE

知识点：CASE WHEN 嵌套

题目：
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='Oceania' THEN  'Australasia'
				WHEN continent in ('Eurasia', 'Turkey') THEN 'Europe/Asia'
				WHEN continent='Caribbean' THEN 
												CASE WHEN name like 'B%' THEN 'North America'
													 ELSE 'South America'
												END
				ELSE continent
			END
FROM world

被嵌套的CASE WHEN 根据条件返回北美 南美

CASE WHEN name like 'B%' THEN 'North America'
	 ELSE 'South America'
END

知识点：子查询

9.找出洲份，當中全部國家都有少於或等於 25000000 人口. 在這些洲份中，列出國家名字name，continent 洲份和population人口。

select 	name
		,continent
		,population 
from world 
where continent in( SELECT continent 
					from world 
					group by continent 
					having max(population)< 25000000 )

子查询筛选出最大的population< 25m的洲

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

select 	name 
		,continent from world a  
where population > all(select population*3 
						from world b 
						where a.continent = continent and a.name <> b.name )

同洲不同国a.continent = continent and a.name <> b.name
3倍以上 population> all(select population*3

知识点：表连接

• INNER JOIN：如果表中有至少一个匹配，则返回行
• LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
• RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
• FULL JOIN：只要其中一个表中存在匹配，则返回行
• SELF JOIN：用于将表连接到自己，就好像该表是两个表一样，临时重命名了SQL语句中的至少一个表
• CARTESIAN JOIN：从两个或多个连接表返回记录集的笛卡儿积

题目：
The JOIN operation/zh
13.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,team1,team2,matchid
order by mdate, matchid, team1, team2

题目给的范例是join

SELECT mdate,
  team1,
  CASE WHEN teamid=team1 THEN 1 ELSE 0 END score1
  FROM game JOIN goal ON matchid = id`

比较结果集后发现少了这两行
2012-06-24T00:00:00 ENG 0 ITA 0
2012-06-27T00:00:00 POR 0 ESP 0

因为用join内连接没连上的数据 null不显示，改为left join ，显示左表，结果就正确了

知识点：自连接self join

题目
Self join最后一题
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.

Hint
Self-join twice to find buses that visit Craiglockhart and Lochend, then join those on matching stops.

SELECT 	a.num
		,a.company
		,b.num
		,b.company
FROM route a JOIN route c ON
  (a.company=c.company AND a.num=c.num)
  JOIN stops stopa ON (a.stop=stopa.id)  
JOIN route d ON
  (c.stop = d.stop)
JOIN route b ON
  (d.company=b.company AND d.num=b.num)
JOIN stops stopb ON 
  (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name ='Lochend'

还差the name of the stop for the transfer（中间的换乘站），即c和d表连接的那站(c.stop = d.stop)

因为要显示换乘站名称， 而不是编号，所以再连一个stops表：JOIN stops stopc ON (c.stop=stopc.id)
再SELECT stopc.name 显示换乘站

SELECT	 a.num
		,a.company
		,stopc.name
		,b.num
		,b.company
FROM route a JOIN route c ON
  (a.company=c.company AND a.num=c.num)
  JOIN stops stopa ON (a.stop=stopa.id)  
JOIN route d ON
  (c.stop = d.stop)
JOIN stops stopc ON (c.stop=stopc.id)
JOIN route b ON
  (d.company=b.company AND d.num=b.num)
JOIN stops stopb ON 
  (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name ='Lochend'