–Among the clients which only use a single company, find the different passengers who have flown more often than others.
–Result set: passenger name, number of trips, and company name.
selectp.name, cnt, c.name from
innerjoin Company c on c.ID_comp = x.ID_comp
innerjoin Passenger p on p.ID_psg = x.ID_psg
(select max(cnt) from (select pt.ID_psg, t.ID_comp, COUNT(ID_comp) cnt from Trip t inner join Pass_in_trip pt on pt.trip_no = t.trip_no where pt.ID_psg in (select ID_psg from (select distinct pt.ID_psg, t.ID_comp from Trip t inner join Pass_in_trip pt on pt.trip_no = t.trip_no) x group by ID_psg having COUNT(ID_comp) = 1) group by pt.ID_psg, t.ID_comp) x )
selectp.name, cnt, c.name
selecttop 1 with ties pt.id_psg, count(*) as cnt, min( id_comp ) as id_comp from pass_in_trip pt inner join trip t on t.trip_no = pt.trip_no group by pt.id_psg having count( distinct id_comp ) = 1 order by count(*) desc
innerjoin passenger p on t.id_psg = p.ID_psg
innerjoin Company c on c.ID_comp = t.ID_comp