SQLZOO Self join 答案

ZOO Self join 链接

1.數據庫中有多少個站stops。

SELECT SUM(CASE WHEN id IS NOT NULL THEN 1 ELSE 0 END) FROM stops;

2.找出車站 ‘Craiglockhart’ 的 id

SELECT ID FROM stops WHERE name = 'Craiglockhart';

3.列出巴士公司’LRT’的’4’號巴士線的站編號id 和 站名name

SELECT id, name FROM stops s JOIN route r on s.id = r.stop
WHERE company = 'LRT' AND num = '4';

4.以下查詢列出途經 London Road (149) 或 Craiglockhart (53)的巴士線號碼。注意有兩條路線會經過這兩個站兩次。 加入 HAVING 語句來限制只列出這兩條路線。

SELECT company, num, COUNT(num) FROM route 
WHERE stop = 149 OR stop = 53
GROUP BY company, num
HAVING COUNT(num) = 2