Week5 - Set Operators / Assess

The questions can come in any order, so make sure you are selecting right option for all questions.

1. Write a query to display person_name,contact_number and balance who are enterd into station 'Kallang MRT station' and 'Sengkang MRT station'. Display the common records in ascending order based on their balance.

select t.person_name,t.contact_number,t.balance
from travel_card t
join travel_payment p on p.travel_card_id = t.id
where p.entry_station_id in (
  select id from station
  where name = 'Kallang MRT station')
intersect
select t.person_name,t.contact_number,t.balance
from travel_card t
join travel_payment p on p.travel_card_id = t.id
where p.entry_station_id in (
  select id from station
  where name = 'Sengkang MRT station')
order by balance

2. Write a query to display station name,locality,station_id and route_id for which route name is 'East West line' and route name is 'Downtown line'.Display the records in ascending order based on thier station name.

select s.name,s.locality,r.station_id,r.route_id 
from station s 
join station_route r on s.id = r.station_id 
join route ro on r.route_id = ro.id 
where ro.route_name = 'East West line'
minus
select s.name,s.locality,r.station_id,r.route_id 
from station s 
join station_route r on s.id = r.station_id 
join route ro on r.route_id = ro.id 
where ro.route_name = 'Downtown line'
order by name