Formulate the following queries in SQL (the answers to these queries in relational algebra, tuple relational calculus, and domain relational calculus were given in the previous section).

Question

The following tables form part of a database held in a Relational Database Management System:

Employee (empNo, eName, salary, position)
Aircraft (aircraftNo, aName, aModel, flyingRange)
Flight (flightNo, from, to, flightDistance, departTime, arriveTime)
Certified (empNo, aircraftNo)

where Employee contains details of all employees (pilots and non-pilots)
and empNo is the key.
AirCraft contains details of aircraft and aircraftNo is the key.
and Certified contains details of the staff who are certified to fly an
aircraft, and empNo/aircraftNo form the key.

(1) List all Boeing aircraft.
(2) List all Boeing 737 aircraft.
(3) List the employee numbers of pilots certified for Boeing aircraft.
(4) List the names of pilots certified for Boeing aircraft.
(5) List the aircraft that can fly nonstop from Glasgow to New York (flyingRange > flightDistance).
(6) List the employee numbers of employees who have the highest salary.
(7) List the employee numbers of employees who have the second highest salary.
(8) List the employee numbers of employees who are certified for exactly three aircraft.

Answer 1

(1) SELECT *
FROM Aircraft
WHERE aName = ‘Boeing’;
(2) SELECT *
FROM Aircraft
WHERE (aName = ‘Boeing’) AND (aModel = ‘737’);
(3) SELECT empNo
FROM Aircraft a, Certified c
WHERE (a.aircraftNo = c.aircraftNo) AND (a.aName = ‘Boeing’);
(4) SELECT eName
FROM Aircraft a, Certified c, Employee e
WHERE (a.aircraftNo = c.aircraftNo) AND (e.empNo = c.empNo) AND
(a.aName = ‘Boeing’);
(5) SELECT a.aircraftNo
FROM Aircraft a, Flight f
WHERE (from = ‘Glasgow’ ? to = ‘New York’) ? (flyingRange > flightDistance);
(6) SELECT empNo
FROM Employee e1
WHERE e1.salary = (SELECT MAX(e2.salary)
FROM Employee e2);
(7) SELECT empNo
FROM Employee e1
WHERE e1.salary = (SELECT MAX(e2.salary)
FROM Employee e2
WHERE e2.salary <> (SELECT MAX(e3.salary)
FROM Employee e3);
(8) SELECT c1.empNo
FROM Certified c1, Certified c2, Certified c3
WHERE (c1.empNo = c2.empNo) AND (c2.empNo = c3.empNo) AND
(c1aircraftNo <> c2.aircraftNo) AND (c2.aircraftNo <> c3.aircraftNo) AND
(c3.aircraftNo <> c1.aircraftNo)
EXCEPT
SELECT c4.empNo
FROM Certified c4, Certified c5, Certified c6, Certified c7
WHERE (c4.empNo = c5.empNo) AND (c5.empNo = c6.empNo) AND
(c6.empNo = c7.empNo) AND (c4.aircraftNo <> c5.aircraftNo) AND
(c4.aircraftNo <> c6.aircraftNo) AND (c4.aircraftNo <> c7.aircraftNo) AND
(c5.aircraftNo <> c6.aircraftNo) AND (c5.aircraftNo <> c7.aircraftNo) AND
(c6.aircraftNo <> c7.aircraftNo)