SQL: how to filter results outside of GROUP BY
Original question
The following relationships track airline flight information:
Aircraft (aircraft_number, aircraft_make, cruisingrange)
Certified (pilot_id, aircraft_number)
Pilot (pilot_id, username, salary)
Note that italicized attributes represent primary keys. In this scheme, each pilot is certified for some aircraft. Write each of the following queries in SQL format.
(ii) Find the names of pilots who can fly over 2,000 miles but are not certified on any Boeing aircraft.
Can someone help me figure out how to write a query for this?
My guess would be first join PILOT
before CERTIFIED
, then join
before AIRCRAFT
, then GROUP BY PILOT.pilot_id
, but also, I'm not sure how to filter pilot_id
to exclude those at least one plane with a minimum range of 2000 and no aircraft_make
Boeing?
Many thanks!
source to share
This should do:
select p.pilot_name
from pilot p
join certified c
on p.pilot_id = c.pilot_id
join aircraft a
on c.aircraft_number = a.aircraft_number
where a.cruisingrange > 2000
and p.pilot_id not in (
select c.pilot_id
from certified c
join aircraft a
on c.aircraft_number = a.aircraft_number
where a.aircraft_make = 'BOEING'
)
group by p.pilot_id
source to share