Database Design and SQL The following relations keep track o

Database Design and SQL

The following relations keep track of airline flight information:

Flights (flno: integer, from : string, to: string, distance: integer, departs: time, arrive: time, price: integer)
Aircraft (aid: integer, aname : string, cruisingrange: integer)
Certified (eid: integer, aid: integer)
Employees (eid: integer, ename : string, salary: integer)

The Employees relation describe pilots and other kinds of employees as well. Every pilot is certified for some aircraft and only pilots are certified to fly.

Based on the schemas, formulate the following SQL queries:

a. Find the names of aircraft such that all pilots certified to operate them earn more than 80,000.

b. For each pilot who is certified for more than three aircraft, find the eid and the maximum crusingrange of the aircraft that he/she is certified for.

c. Find the names of pilots whose salary is less than the price of the cheapest route from Los Angeles to Honolulu.

d. For all aircraft with cruisingrange over 1,000 miles, find the name of the aircraft as well as the average salary of all pilots certified for this aircraft.

Solution

a. select a.aname from Aircraft a where a.aid in(select c.aid from Certified c,Employees e where c.eid=e.eid and not exists(select * from Employees e1 where e1.eid=e.eid and e1.salary<80000));

b. select c.eid, max(a.cruisingrange) from Certified c, Aircraft a where c.aid=a.aid group by c.eid having count(*)>3;

c. select e.ename from Employees e where e.salary<(select min(f.price) from Flights f where f.from=\'los angeles\' and f.to=\'honolulu\');

d. select a.aname as name,avg(e.salary) as avgsal from Aircraft a,Certified c,Employees e where a.aid=c.aid and c.eid=e.eid and a.cruisingrange>1000 group by a.aname;