-
sqlzoo–select basics,select from world
-
sqlzoo–select from nobel ,select in select
-
sqlzoo–sum and count
-
sqlzoo–the join operation
-
sqlzoo–More JOIN operations
-
sqlzoo–using null
-
sqlzoo–self join
sqlzooself_join_9">sqlzoo–self join
已有字段:
table:stops | table:route |
---|---|
id | num |
name | company |
pos | |
stop | |
-
数据库中有多少个站点
sql">select count(id) from stops;
-
找出车站‘craiglockhart’的id
sql">select id from stops where name = 'Craiglockhart';
-
列出巴士公司’LRT‘4号线的站点编号和站名
sql">select id,name from route join stops on (id = stop) where company = 'LRT' and num = 4;
-
查询途经London Road (149) 或 Craiglockhart (53)的巴士路线号码,有两条路线会经过这个站点两次,使用having语句列出这两条路线。
sql">SELECT company, num, COUNT(*) FROM route WHERE stop=149 OR stop=53 GROUP BY company,num HAVING count(*) = 2;
-
使用自连接来显示能够从Craiglockhart到 London Road直达的路线
sql">SELECT a.company, a.num, a.stop, b.stop FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num) WHERE a.stop=53 and b.stop = 149;
-
使用两个stops表来进行自连接,来显示Craiglockhart到 London Road的服务资料。
sql">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';
-
列出连接115和137公司名称和路线号码,不要重复
sql">select company,num from route where num in (select a.num from route as a,route as b where a.stop != b.stop and (a.company = b.company and a.num=b.num) and a.stop='115' and b.stop='137') group by num,company;
-
列出连接车站stops’Craiglockhart’ 到 ‘Tollcross’ 的公司名和路线号码
sql">SELECT b.company, b.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 stopb.name = 'Tollcross';
-
不重复列出由 ‘Craiglockhart’ 乘一站到达的站点,包括其本身,列出站名,公司名以及路线号码
sql">select stopb.name,a.company,a.num from route as a join route as b on (a.company=b.company and a.num=b.num) join stops as stopa on (stopa.id=a.stop) join stops as stopb on (stopb.id=b.stop) where stopa.name='Craiglockhart';
-
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.sql">SELECT DISTINCT bus1.num, bus1.company, name, bus2.num, bus2.company FROM(SELECT start1.num, start1.company, stop1.stop FROM route AS start1 JOIN route AS stop1 ON start1.num = stop1.num AND start1.company = stop1.company AND start1.stop != stop1.stop WHERE start1.stop = (SELECT id FROM stops WHERE name = 'Craiglockhart')) AS bus1 JOIN (SELECT start2.num, start2.company, start2.stop FROM route AS start2 JOIN route AS stop2 ON start2.num = stop2.num AND start2.company = stop2.company and start2.stop != stop2.stop WHERE stop2.stop = (SELECT id FROM stops WHERE name = 'Sighthill')) AS bus2 ON bus1.stop = bus2.stop JOIN stops ON bus1.stop = stops.id;
self join quiz
-
显示从Craiglockhart 到Haymarket的可能路线
sql">SELECT DISTINCT a.name, b.name FROM stops a JOIN route z ON a.id=z.stop JOIN route y ON y.num = z.num JOIN stops b ON y.stop=b.id WHERE a.name='Craiglockhart' AND b.name ='Haymarket';
-
在路线2A上并且能够一站到达haymarket的站点
sql">SELECT S2.id, S2.name, R2.company, R2.num FROM stops S1, stops S2, route R1, route R2 WHERE S1.name='Haymarket' AND S1.id=R1.stop AND R1.company=R2.company AND R1.num=R2.num AND R2.stop=S2.id AND R2.num='2A';
-
到tollcross的可能服务路线
sql">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='Tollcross';