1) Find pilots by eid who are certified to fly all aircraft the airline owns wit

Question

1) Find pilots by eid who are certified to fly all aircraft the airline owns with the exception of those on the "specialized" list, i.e. SAAB 340, Tupolev 154, the Airbus A320, Airbus A319, and the Schwitzer 2-33. (This means that they definitely can fly all the aircraft with the exception those in the "specialized" list, but does not rule out that they may also be able to fly aircraft on the "specialized" list as well). You are permitted, in cases where a query is a subquery, to refer to that query as Qx. The source query has to be clearly labeled as Qx. Do NOT use knowledge of the contents of the tables in your answers. a) Find the distinct aircraft aids of aircraft that the airline owns with the exceiption of those on the specialized list. (This query should result in 11 rows indicating that there are 11 aircraft out of the total of 16 aircraft that are not on the specialized list). b) Find the distinct eids of all pilots. (This query should result in 12 pilot eids)

Explanation / Answer

Select eid from Employees inner join Certified on Employees.eid = Certified.eid inner join Aircraft on Certified.aid = Aircraft.aid where Aircraft.aname NOT IN('SAAB 340','Tupolev 154','Airbus A320','Airbus A319','Schwitzer 2-33');

Select distinct aid from Aircraft where aname NOT IN ('SAAB 340','Tupolev 154','Airbus A320','Airbus A319','Schwitzer 2-33');

Select distinct eid from Employees inner join Certified on Employees.eid = Certified.eid;

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.