SQLize Online / PHPize Online  /  SQLtest Online

drop table if exists pilot; create table pilot as ( select '123' as pilot_id ,'IVANOV' as name ,33 as age ,1 as rank ,'higher' as education_level UNION ALL select '234' as pilot_id ,'PETROV' as name ,21 as age ,2 as rank ,'secondary' as education_level UNION ALL select '345' as pilot_id ,'SIDOROV' as name ,47 as age ,3 as rank ,'higher' as education_level ); drop table if exists airplane; create table airplane as ( select '0-123' as plane_id ,300 as capacity ,0 as cargo_flg UNION ALL select '0-234' as plane_id ,25 as capacity ,1 as cargo_flg ); drop table if exists flight; create table flight as ( select 'S-123' as flight_id ,'2023-08-30'::date as flight_dt ,'0-123' as plane_id ,'123' as first_pilot_id ,'234' as second_pilot_id ,'Шереметьево' as destination ,200 as quantity UNION ALL select 'S-787' as flight_id ,'2023-08-01'::date as flight_dt ,'0-123' as plane_id ,'234' as first_pilot_id ,'345' as second_pilot_id ,'Кольцово' as destination ,300 as quantity UNION ALL select 'A-786' as flight_id ,'2023-06-01'::date as flight_dt ,'0-234' as plane_id ,'345' as first_pilot_id ,'234' as second_pilot_id ,'Шереметьево' as destination ,20 as quantity ); select * from pilot; select * from flight; select * from airplane; --SELECT pilot.name --FROM ( SELECT second_pilot_id FROM flight --WHERE destination = 'Шереметьево' -- AND DATE_TRUNC('month', flight_dt) = '2023-08-01' --GROUP BY second_pilot_id ) f --LEFT JOIN pilot --ON f.second_pilot_id = pilot_id -- select pilot.name -- from ( select second_pilot_id from flight -- where destination = 'Шереметьево' and date_trunc('month', flight_dt) = '2023-08' -- group by second_pilot_id) f -- left join pilot on f.second_pilot_id = pilot_id --SELECT pilot.name --FROM ( SELECT first_pilot_id FROM flight f --LEFT JOIN airplane ap --ON f.plane_id = ap.plane_id --WHERE ap.cargo_flg = 1 --GROUP BY first_pilot_id ) pp_id --LEFT JOIN pilot --ON pilot.pilot_id = pp_id.first_pilot_id --WHERE pilot.age > 45 --UNION --SELECT pilot.name --FROM ( SELECT second_pilot_id FROM flight f --LEFT JOIN airplane ap --ON f.plane_id = ap.plane_id --WHERE ap.cargo_flg = 1 --GROUP BY second_pilot_id ) pr_id --LEFT JOIN pilot --ON pilot.pilot_id = pr_id.second_pilot_id --WHERE pilot.age > 45 SELECT pilot.name from ( select first_pilot_id, count(*) passengers from flight f left join airplane ap on f.plane_id = ap.plane_id where ap.cargo_flg = 0 group by first_pilot_id ) pr_id left join pilot on pr_id.first_pilot_id = pilot.pilot_id order by pr_id.passengers desc limit 1;