Re: SQL Question

Uzmati29 <uzmati29_at_aol.com> wrote in message news:20000314204301.02594.00002006_at_ng-fq1.aol.com...
> Hi,
>
> I am a college student taking a class in database design and I have been
 having
> some trouble with a query that my instructor assigned. I am using SQL +
 3.3
> with oracle.
>
> The Query is:
>
>
> What pilots are certified to fly all the types of aircraft that John Baum
 is
> certified to fly?
>
> Here is the basic table structure:
>
> Employee(E_ID, SSN, LName, FName, MI, Address, DOH, Salary, Base,
 Category,
> Year_of_Birth)
>
> Pilot_Attrib( E_ID, Can_Fly)
>
>

DOES THIS WORK? I haven't tried running it. Logically John Baum is certified to fly planes that John Baum is certified to fly but this might not be what your tutor wanted. You can always tag another restriction on the end to stop it bringing back his row. Someone else might come up with something less ugly.

select e1.e_id, e1.fname, e1.lname
from employee e1
where
not exists
(
  (
    select p.can_fly
    from pilot_attrib p
    where
      p.e_id = (select e2.e_id from employee e2 where upper(e2.fname) = 'JOHN' and upper(e2.lname) = 'BAUM')
  )
  minus
  (
    select p2.can_fly
    from pilot_attrib p2
    where p2.e_id = e1.eid
  )
)

>
> I have been unable to figure out how to work this query. I think that I
 might
> have to join the Pilot_Attrib table with itself, but I cannot figure out
 how to
> eliminate those pilots that are certified with some but not all of the
 same
> planes as John Baum.
>
>
> thanks
Received on Wed Mar 15 2000 - 00:00:00 CET