SQL 11 – Self Join

SQL 11 – Self Join

---

Database

---

Problems

문제 1. 데이터베이스에 있는 stops의 수를 보여주세요.

정답

SELECT COUNT(id)
 
FROM stops

 

문제 2. stop 'Craiglockhart'의 ID 값을 찾습니다. Find the id value for the stop 'Craiglockhart'

정답

select id
 
from stops
 
where name = 'Craiglockhart'

 

 

문제 3. '4' 'LRT' 서비스의 정류장 아이디와 이름을 알려주세요.

정답

select id, name
 
from stops JOIN route ON (id = stop)
 
where num = 4 AND company = 'LRT'
 
order by pos

 

문제 4. 표시된 쿼리는 런던 로드(149개) 또는 크레이그록하트(53개)를 방문하는 경로 수를 나타냅니다. 쿼리를 실행하고 이러한 중지를 연결하는 두 서비스의 카운트가 2인 것을 확인합니다. 출력을 이 두 경로로 제한하려면 HAVING 절을 추가하십시오.

정답

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

 

 

문제 5. 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.

표시된 자체 결합을 실행하고 경로를 변경하지 않고 b.stop이 크레이그록하트에서 갈 수 있는 모든 위치를 제공하는지 관찰합니다. Craiglockhart에서 London Road까지의 서비스를 표시하도록 쿼리를 변경합니다.

정답

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;

 

 

 

문제 6. 표시된 쿼리는 이전 쿼리와 유사하지만 두 개의 정지 테이블 사본을 결합하면 번호가 아닌 이름으로 정지를 참조할 수 있습니다. 'Craiglockhart'와 'London Road' 사이의 서비스가 표시되도록 쿼리를 변경합니다. 만약 당신이 이러한 장소에 지쳤다면, '톨크로스'에 대항하여 '페어마일헤드'를 시도해 보세요.

정답

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';

 

 

문제 7. 115번과 137번 정류장을 연결하는 모든 서비스 목록을 제공합니다('헤이마켓' 및 '리스')
Give a list of all the services which connect stops 115 and 137 ('Haymarket' and 'Leith')

정답

SELECT DISTINCT a.company, a.num
FROM route a JOIN route b ON (a.num =b.num AND a.company=b.company)
WHERE a.stop = 115 AND b.stop =137;

 

 

문제 8. 'Craiglockhart' 및 'Tollcross' 스톱을 연결하는 서비스 목록을 제공합니다.
Give a list of the services which connect the stops 'Craiglockhart' and 'Tollcross'

정답

SELECT a.company, a.num
 
FROM route a JOIN route b ON (a.num = b.num AND a.company = b.company)
 
             JOIN stops c on a.stop = c.id
 
             JOIN stops d on b.stop = d.id
 
WHERE c.name ='Craiglockhart' AND d.name  = 'Tollcross';

 

 

문제 9. LRT 회사가 제공하는 'Craiglockhart' 자체 버스를 포함하여 '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.

정답

select d.name,a.company,a.num
 
from route a join route b
 
on a.num =b.num and a.company = b.company
 
join stops c on (a.stop = c.id)
 
join stops d on (b.stop = d.id)
 
where c.name ='Craiglockhart';

 

 

문제 10. Craiglockhart에서 Lochend까지 가는 버스 두 대가 관련된 경로를 찾아보세요.
버스 번호와 첫 번째 버스 회사, 환승 정류장 이름을 보여주세요.
그리고 두 번째 버스는 회사도 있습니다.

정답

SELECT DISTINCT x.num, x.company, name, y.num, y.company
 
FROM (SELECT a.num, a.company, b.stop
 
      FROM route a JOIN route b ON a.num = b.num AND a.company = b.company
 
           AND a.stop != b.stop
 
      WHERE a.stop = (SELECT id
 
                      FROM stops
 
                      WHERE name ='Craiglockhart'
 
                     )
 
      ) as x
 
     JOIN (SELECT c.num, c.company, c.stop
 
           FROM route c JOIN route d ON c.num = d.num AND c.company = d.company
 
                AND c.stop != d.stop
 
           WHERE d.stop = (SELECT id
 
                           FROM stops
 
                           WHERE name = 'Lochend'
 
                          )
 
          ) as y
 
     ON x.stop = y.stop
 
     JOIN stops ON x.stop = stops.id
 
ORDER BY x.num, stops.name, y.num