Week5 - Usage of Subqueries to Solve Queries / 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 all the details of metro_train which belongs to the route 'North South line'. Display the records in ascending order based on position.

select * from metro_train where route_id in (select id from route where route_name = 'North South line') order by position;

2. Write a query to display the person names whose entry or exit station is 'Marina Bay MRT Station' on '22-Dec-2017'.Display the records in ascending order based on person name.

select person_name from travel_card where id in (select travel_card_id from travel_payment where (entry_station_id= (select id from station where name = 'Marina Bay MRT Station') or exit_station_id = (select id from station where name = 'Marina Bay MRT Station')) and (entry_time like '22-DEC-17%' or exit_time like '22-DEC-17%')) order by person_name

3. Write a query to display the route_name from route table with the route_id matches with the following metro_train ids. metro_train id's are 1001,1002,1003,1004 and 1005. Display the records in ascending order based on route_name.

select route_name from route where id in (select route_id from metro_train where id between 1001 and 1005) order by route_name

4. Write a query to display the train details which are all belongs to the route 'North South line' and 'Circle line'.Display the record in ascending order based on position.

select * from metro_train where route_id in (select id from route where route_name='North South line' or route_name='Circle line') order by position

5. Write a query to display person_name and contact_number from travel_card table of the persons whose destination is 'Expo MRT Station' with the payment amount more than 25 on 23-Dec-2017. Display the records in ascending order based on person_name.

select person_name,contact_number from travel_card where id = (select travel_card_id from travel_payment where exit_station_id in (select id from station where name = 'Expo MRT Station') and amount>25 and exit_time like '23-DEC-17%')