Week5 - Display Data from Multiple Tables / 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 the person_name and the contact_number from travel_card table of every person whose destination station is on the route 'North East line' with the payment more than 25. Display the records in ascending order based on person name.

    select t.person_name, t.contact_number
    from travel_card t
    inner join travel_payment tp on t.id = tp.travel_card_id
    inner join station s on tp.exit_station_id = s.id
    where s.id in (
        select station_id 
        from station_route 
        where route_id in (
            select id 
            from route
            where route_name = 'North East line'
        )
    )
    and tp.amount>25
    order by t.person_name;

2. Write a query to display the name and locality of the stations with the metro trains scheduled to arrive on '21-Dec-2017' along the route 'Circle line'.Display the records in ascending order based on the station name.

    select s.name, s.locality
    from station s
    inner join train_schedule ts on s.id = ts.station_id
    where ts.scheduled_time like '21-DEC-17%' and s.id in (
        select station_id
        from station_route
        where route_id in (
            select id
            from route
            where route_name = 'Circle line')
        )
    order by s.name;

3. Write a query to display person name,contact number and balance of all the details, who have minimum amount in their payment card. If there is multiple record dipsplay the record in descending order by person name.

    select person_name,contact_number,balance 
    from travel_card 
    where id = (
        select travel_card_id 
        from travel_payment 
        where amount = (
            select min(amount) 
            from travel_payment)
    ) 
    order by person_name desc;

4. Write a query to display the person name,contact number,balance,entry_time and exit_time details of all the persons who is having the second highest balance in their travel cards. Display the records in ascending order based on their person_name.

    select t.person_name,t.contact_number,t.balance,p.entry_time,p.exit_time 
    from travel_card t,travel_payment p 
    where t.id = p.travel_card_id and  t.balance in (
      select max(balance) 
      from travel_card 
      where balance not in (
        select max(balance)
        from travel_card))

5. Write a query to display person name,contact number and balance of all the details, who are travelled maximum number of times. If there is multiple record dipsplay the record in descending order by person name.

    Select tc.person_name, tc.contact_number, tc.balance 
    from travel_card tc 
    join (
      select travel_card_id, count(*) as travel_count 
      from travel_payment 
      group by travel_card_id) tp
    on tc.id = tp.travel_card_id
    where tp.travel_count = (
      select max(travel_count) 
      from (
        select count(*) as travel_count 
        from travel_payment 
        group by travel_card_id)
      )
    order by tc.person_name desc;