CODEWITHSUNDEEP
mysqlsql
kenc32390
kenc32390

Reputation: 33

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 that italic attributes denote primary keys. In this scheme every pilot is certified for some aircraft. Write each of the following queries in SQL Format.

(ii) Find the names of pilots who can operate planes with a range greater than 2,000 miles but are not certified on any Boeing aircraft.

Can someone help me understand how to write a query for this?

My guess would be to first join PILOT to CERTIFIED, then join to AIRCRAFT, then GROUP BY PILOT.pilot_id, but beyond that, I am not sure how to filter the pilot_id to exclude those without at least one plane with a minimum 2000 cruising range and with no aircraft of the aircraft_make 'Boeing'?

Thank you very much!

Upvotes: 3

Views: 118

Answers (1)

Georgi Raychev
Georgi Raychev

Reputation: 1334

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

Upvotes: 3

Related Questions