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