The following tables form part of a database held in a Relational Database Manag

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.

                  Flight contains details of the flights and flightNo is the key.

      and Certified contains details of the staff who are certified to fly an aircraft, and empNo/aircraftNo form the key.

Formulate the following queries in SQL

(14)List the employee numbers of employees who have the second highest salary.

(15) List the employee numbers of employees who are certified for exactly three aircraft.

Explanation / Answer

select MAX(Salary), count(employees) from Employee where Salary NOT IN (Select MAX(Salary) from employee); SELECT C1.eid FROM Certified C1, Certified C2, Certified C3 WHERE (C1.eid = C2.eid AND C2.eid = C3.eid AND C1.aid 6= C2.aid AND C2.aid 6= C3.aid AND C3.aid 6= C1.aid) EXCEPT SELECT C4.eid FROM Certified C4, Certified C5, Certified C6, Certified C7, WHERE (C4.eid = C5.eid AND C5.eid = C6.eid AND C6.eid = C7.eid AND C4.aid 6= C5.aid AND C4.aid 6= C6.aid AND C4.aid 6= C7.aid AND C5.aid 6= C6.aid AND C5.aid 6= C7.aid AND C6.aid 6= C7.aid )

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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