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;