SQLZOO练习--其余
简介
sqlzoo是一个适合用于练习sql的题库网站
不要觉得页面丑而且无简体中文
如果做得完大部分题目比你听网课更有成效
因为剩余的题目有些都比较简单,因此本文会选一些有意义的来做笔记
补充:我是菜比
null的习题地址
6. 使用COALESCE和LEFT JOIN来显示teacher的name和dept的name,如果没有部门则显示'None'
原文
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 (a,b,c),coalesce会按照参数的顺序,如果当前参数为(null),则显示下一参数的值...直到最后
通过比case when更加优雅的方式来判断null
SELECT teacher.name, coalesce(dept.name,'None')
FROM teacher left join dept on (teacher.dept=dept.id)
9. 使用CASE来显示每个教师从属的部门,如果是1或2则显示'Sci',其他则显示'Art'
原文
Use CASE to show the name of each teacher followed by 'Sci' if the teacher is in dept 1 or 2 and 'Art' otherwise.
select teacher.name,
(case dept.id
when '1' then 'Sci'
when '2' then 'Sci'
else 'Art'end
) type
from teacher left join dept on (teacher.dept=dept.id)
自连接部分习题地址
4. 根据已给出的经过149站或53站的巴士线路的查询,要求列出有2条线路经过这些站的路线,他们所属的公司company,路线号num和出现次数
原文:
The query shown gives the number of routes that visit either London Road (149) or Craiglockhart (53). Run the query and notice the two services that link these stops have a count of 2. Add a HAVING clause to restrict the output to these two routes.
这题看懂题目后比较简单,无非就是要加上having条件
SELECT company, num,COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company,num
having COUNT(num) =2
注意给出的提示中group by添加了多个字段
我从来没用过多字段的group by,因此特地去查了下
https://zhidao.baidu.com/question/164072931.html
vinson_shen
推荐于2017-09-10
"group by 字段列表"
表示根据后面的字段来分zd组,如果只有1个字段,那只是根据这个字段的值来进行一次分组就可以了；若后面有多个字段，那表示根据多字段的值来进行层次分组，分组层次从左到右，即先按专第1个字段分组，然后在第1个字段值相同的记录中，再根据第2个字段的值进行分组；接着第2个字段值相同的记录中，再根据第3个字段的值进行分组......依次类属推。
别看good多其实他的理解是错的
group by的本质是将相同字段的值丢进一个组内
如果有2个group by的字段则会将他们2个都丢进同一个组
而不是像他说的那样一个个进行分组
5. 根据已给出的自连接查询语句,查询从53号站(Craiglockhart)到149号站(London Road)的线路
原文
Execute the self join shown and observe that b.stop gives all the places you can get to from Craiglockhart, without changing routes. Change the query so that it shows the services from Craiglockhart to London Road.
根据提示的话解起来非常简单
说实话我也从没用过自连接,在没看提示的情况下我没想到怎么又表示a站又表示b站,惭愧
select a.company,a.num,a.stop,b.stop
from route a join route b on (a.num=b.num)
WHERE a.stop=53 AND b.stop=149
6. 根据题目5的结果,通过JOIN连接stops表,结果显示得更人性化
原文
The query shown is similar to the previous one, however by joining two copies of the stops table we can refer to stops by name rather than by number. Change the query so that the services between 'Craiglockhart' and 'London Road' are shown. If you are tired of these places try 'Fairmilehead' against 'Tollcross'
提示给的非常多
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'
-- 结果显示更符合人类阅读
company num name name
LRT 4 Craiglockhart London Road
LRT 45 Craiglockhart London Road
这里需要注意多重join的顺序
如本题,先连接了a,b两表
再将a,b两表与stopsA表进行连接(将a的字段与stopsA关联)
再将a,b,stopsA表与stopsB表进行连接(将b的字段与stopsB关联)
9. (通过自连接)找出所有与'Craiglockhart'站在单条路线中相连接的车站(包括他自己),列出这些站点名称,经过这些路线所属公司和巴士线路号
原文
Give a distinct list of the stops which may be reached from 'Craiglockhart' by taking one bus, including 'Craiglockhart' itself, offered by the LRT company. Include the company and bus no. of the relevant services.
看懂题目后很简单,大概是这么个意思
image.png
相当于前面几个练习的合集
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'
需要注意的是题目让你使用distinct排重关键词,其实不用也不会错,因为没有站点名重复
10.如何通过换乘2辆车从C站和L站,列出第一辆的车号,所属公司,换乘站,和第二辆的车号,所属公司
原文
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.
一开始觉得有点难,但实际做起来真的有点鸡儿麻烦
照常理还是先分析
先画图,下图C表示C站,以此类推L站,T则表示中转站(transfer)
image.png
- 首先要认识到他给的2张表格站点名和线路是分离的,实际操作起来需要外连接非常麻烦,因此先简化表格,认为路线图route上已经存在站点名name
- 此时,假设要求C站和T站的共有路线的sql怎么写?
select name from route a join router b on (a.company = b.company AND a.num = b.num)
where a.name = 'C站' and b.name = 'T站'
同样的求L站和T站的也是一样的写法(换下关键词就行了,省略)
- 那如果需要三个站点都相等怎么求?
此时就不得不需要让2个中转的T站来相等(如图中灰色的T1和T2,需要在各自线路上找到交点)
因此
select name from
//a与t1关联
route a join router t1 on (a.company = t1.company AND a.num = t1.num)
//a与b都是不同的路线集合,并没有属性可以产生交集,因此使用笛卡尔积列出所有结果
join
//在其中让b与t2关联
(join router t2 on (a.company = t2.company AND a.num = t2.num))
where a.name = 'C站' and b.name = 'L站' and t1.name=t2.name
- 最后再把省略掉的站点表stops给换进去
select distinct a.num, a.company, t1.name, b.num, b.company
//rt1:route t1
from route a JOIN route rt1 on (a.company = rt1.company AND a.num = rt1.num)
join (route rt2 join route b on (b.company = rt2.company AND b.num = rt2.num))
//关联(塞入)posts
JOIN stops start ON (a.stop = start.id)
JOIN stops end ON (b.stop = end.id)
JOIN stops trans1 ON (c.stop = trans1.id)
JOIN stops trans2 ON (d.stop = trans2.id)
where
start.name = 'Craiglockhart'
and end.name = 'Lochend'
and t1.name = t2.name
结果因为顺序不对所以报错,估计修复又要等了
sof上还有中通过子查询来获取2条线路交集stop的写法,思路都是差不多的
https://stackoverflow.com/questions/24834948/self-join-tutorial-10-on-sqlzoo
SELECT DISTINCT S.num, S.company, stops.name, E.num, E.company
FROM
(SELECT a.company, a.num, b.stop
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=(SELECT id FROM stops WHERE name= 'Craiglockhart')
)S
JOIN
(SELECT a.company, a.num, b.stop
FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
WHERE a.stop=(SELECT id FROM stops WHERE name= 'Sighthill')
)E
ON (S.stop = E.stop)
JOIN stops ON(stops.id = S.stop)
这题难如何通过第三个集合求出2个没有交集的集合的交集,并且同时还伴有join子表
因此还是化繁为简,从最简单的角度切入思考
总结
我以前一直很讨厌写sql
这期练习后让我有所改观
原来这吊玩样能玩出这么多花...
