QUESTIONS SQL – Part 3

Question 1 : The following relations keep track of airline flight
             information:
             Flights(flno: integer, from: string, to: string,
                     distance: integer, departs: time, 
                     arrives: time, price: real)
             Aircraft(aid: integer, aname: string,
                      cruisingrange: integer)
             Certified(eid: integer, aid: integer)
             Employees(eid: integer, ename: string, salary: integer)

             Note that the Employees relation describes pilots and other
             kinds of employees as well; every pilot is certified for
             some aircraft, and only pilots are certified to fly. Write
             each of the following queries in SQL.
a) Find the names of aircraft such that all pilots certified to
   operate them have salaries more than $80,000.
Solution : SELECT DISTINCT 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) For each pilot who is certified for more than three aircraft, find
   the eid and the maximum cruisingrange of the aircraft for which 
   she or he is certified.
Solution : SELECT C.eid, MAX (A.cruisingrange)
           FROM Certified C, Aircraft A
           WHERE C.aid = A.aid
           GROUP BY C.eid
           HAVING COUNT (*) > 3
c) Find the names of pilots whose salary is less than the price of
   the cheapest route from Los Angeles to Honolulu.
SOlution : SELECT DISTINCT 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) For all aircraft with cruisingrange over 1000 miles, find the name
   of the aircraft and the average salary of all pilots certified for
   this aircraft.
Solution : SELECT Temp.name, Temp.AvgSalary
           FROM ( SELECT A.aid, A.aname AS name,
           AVG (E.salary) AS AvgSalary
           FROM Aircraft A, Certified C, Employees E
           WHERE A.aid = C.aid AND
           C.eid = E.eid AND A.cruisingrange > 1000
           GROUP BY A.aid, A.aname ) AS Temp
e) Find the names of pilots certified for some Boeing aircraft.
Solution : SELECT DISTINCT E.ename
           FROM Employees E, Certified C, Aircraft A
           WHERE E.eid = C.eid AND
           C.aid = A.aid AND
           A.aname LIKE ‘Boeing%’
f) Find the aids of all aircraft that can be used on routes from 
   Los Angeles to Chicago.
Solution : SELECT A.aid
           FROM Aircraft A
           WHERE A.cruisingrange > ( SELECT MIN (F.distance)
           FROM Flights F
           WHERE F.from = ‘Los Angeles’ AND F.to = ‘Chicago’ )
g) Identify the routes that can be piloted by every pilot who makes
   more than $100,000.
Solution : SELECT DISTINCT F.from, F.to
           FROM Flights F
           WHERE NOT EXISTS ( SELECT *
           FROM Employees E
           WHERE E.salary > 100000
           AND
           NOT EXISTS (SELECT *
           FROM Aircraft A, Certified C
           WHERE A.cruisingrange > F.distance
           AND E.eid = C.eid
           AND A.aid = C.aid) )
h) Print the enames of pilots who can operate planes with
   cruisingrange greater than 3000 miles but are not certified
   on any Boeing aircraft.
Solution : SELECT DISTINCT E.ename
           FROM Employees E
           WHERE E.eid IN ( ( SELECT C.eid
           FROM Certified C
           WHERE EXISTS ( SELECT A.aid
           FROM Aircraft A
           WHERE A.aid = C.aid
           AND A.cruisingrange > 3000 )
           AND
           NOT EXISTS ( SELECT A1.aid
           FROM Aircraft A1
           WHERE A1.aid = C.aid
           AND A1.aname LIKE ‘Boeing%’ ))
i) A customer wants to travel from Madison to New York with no more
   than two changes of flight. List the choice of departure times 
   from Madison if the customer wants to arrive in New York by 6 p.m.
Solution : SELECT F.departs
           FROM Flights F
           WHERE F.flno IN ( ( SELECT F0.flno
           FROM Flights F0
           WHERE F0.from = ‘Madison’ AND F0.to = ‘New York’
           AND F0.arrives < ‘18:00’ )
           UNION
           ( SELECT F0.flno
           FROM Flights F0, Flights F1
           WHERE F0.from = ‘Madison’ AND F0.to <> ‘New York’
           AND F0.to = F1.from AND F1.to = ‘New York’
           AND F1.departs > F0.arrives
           AND F1.arrives < ‘18:00’ )
           UNION
           ( SELECT F0.flno
           FROM Flights F0, Flights F1, Flights F2
           WHERE F0.from = ‘Madison’
           AND F0.to = F1.from
           AND F1.to = F2.from
           AND F2.to = ‘New York’
           AND F0.to <> ‘New York’
           AND F1.to <> ‘New York’
           AND F1.departs > F0.arrives
           AND F2.departs > F1.arrives
           AND F2.arrives < ‘18:00’ ))
j) Compute the difference between the average salary of a pilot and
   the average salary of all employees (including pilots).
Solution : SELECT Temp1.avg - Temp2.avg
           FROM (SELECT AVG (E.salary) AS avg
           FROM Employees E
           WHERE E.eid IN (SELECT DISTINCT C.eid
           FROM Certified C )) AS Temp1,
           (SELECT AVG (E1.salary) AS avg
           FROM Employees E1 ) AS Temp2
k) Print the name and salary of every nonpilot whose salary is more
   than the average salary for pilots.
Solution : SELECT E.ename, E.salary
           FROM Employees E
           WHERE E.eid NOT IN ( SELECT DISTINCT C.eid
           FROM Certified C )
           AND E.salary > ( SELECT AVG (E1.salary)
           FROM Employees E1
           WHERE E1.eid IN
           ( SELECT DISTINCT C1.eid
           FROM Certified C1 ) )
l) Print the names of employees who are certified only on aircrafts
   with cruising range longer than 1000 miles.
Solution : SELECT E.ename
           FROM Employees E, Certified C, Aircraft A
           WHERE C.aid = A.aid AND E.eid = C.eid
           GROUP BY E.eid, E.ename
           HAVING EVERY (A.cruisingrange > 1000)
m) Print the names of employees who are certified only on aircrafts
   with cruising range longer than 1000 miles, but on at least two
   such aircrafts.
Solution : SELECT E.ename
           FROM Employees E, Certified C, Aircraft A
           WHERE C.aid = A.aid AND E.eid = C.eid
           GROUP BY E.eid, E.ename
           HAVING EVERY (A.cruisingrange > 1000) AND COUNT (*) > 1
n) Print the names of employees who are certified only on aircrafts
   with cruising range longer than 1000 miles and who are certified
   on some Boeing aircraft.
Solution : SELECT E.ename
           FROM Employees E, Certified C, Aircraft A
           WHERE C.aid = A.aid AND E.eid = C.eid
           GROUP BY E.eid, E.ename
           HAVING EVERY (A.cruisingrange > 1000)
                        AND ANY (A.aname = ’Boeing’)

Previous Home Next
Questions on SQL – Part 2 Quesions On SQL – Part 4

     

Incoming search terms:

  • sql for airline flight information includes employees certified aircraft flights
  • find the eids of pilots certified for some boeing aircraft in algebra and sql
  • find the eids of pilots certified for some boeing aircraft sql trc and drc
  • relation algebra for flight and aircraft and certified and employee
  • salect no eid qostion

Leave a Reply