Q1. In the given airline database schema: (Do any nine)
Flights(flno, from, to, distance, departs)
Aircraft(aid, aname, range)
Certified(eid, aid)
Employees(eid, ename, salary)
By definition, pilots are such employees who are certified on at least one aircraft. The aircraft can be used for any flight provided it has adequate range. Pilots can pilot any flight given they are certified on an aircraft with adequate range.
Problems:
a) Find out the eid’s of pilots who are certified on some Boeing.
b) Find out the names of pilots who are certified on some Boeing.
c) Find out the aid’s of aircraft which can fly non-stop from LA to NY. Suppose that you don’t already know the distance.
d) Find out the flno of flights which can be piloted by every pilot whose salary is over $100,000.
e) Solve the above problem without using the division operator.
f) Find out the names of pilots who can operate planes with a range more than 3,000 miles, however are not certified on any Boeing.
g) Find out eid of employee(s) with the maximum salary.
h) Find out the eid of employee(s) with the second maximum salary.
i) Find out the eid of employee(s) certified on the most aircraft.
j) Find out the eid’s of employees certified on precisely three aircraft.