mysql - SQL: how to filter results beyond GROUP BY -

original question

the following relations keep track of airline flight information:

aircraft (aircraft_number, aircraft_make, cruisingrange)

certified (pilot_id, aircraft_number)

pilot (pilot_id, pilot_name, salary)

note italic attributes denote primary keys. in scheme every pilot certified aircraft. write each of following queries in sql format.

(ii) find names of pilots can operate planes range greater 2,000 miles not certified on boeing aircraft.

can me understand how write query this?

my guess first join pilot certified, join aircraft, group pilot.pilot_id, beyond that, not sure how filter pilot_id exclude without @ least 1 plane minimum 2000 cruising range , no aircraft of aircraft_make 'boeing'?

thank much!

this should do:

select p.pilot_name   pilot p   join certified c     on p.pilot_id = c.pilot_id   join aircraft     on c.aircraft_number = a.aircraft_number   a.cruisingrange > 2000     , p.pilot_id not in (           select c.pilot_id             certified c             join aircraft               on c.aircraft_number = a.aircraft_number             a.aircraft_make = 'boeing'         )   group p.pilot_id