Database Design and SQL The following relations keep track of airline flight inf

Question

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. 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.

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.

Explanation / Answer

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. 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.
SELECT flno, from, departs
FROM Flights
WHERE CURRTIME(arrive) == "0600"

UNION

SELECT F1.flno, F1.from, f1.departs
FROM Flights F1, Flights F2
WHERE F1.from = "Madison"
AND F2.to = "New York"
AND F1.arrive < F2.depart
AND CURRTIME(F2.arrive) == "0600"

b. Compute the difference between the average salary of a pilot and the average salary of all employees (including pilots).
SELECT avg(salary) FROM Employee WHERE eid IN
(SELECT eid FROM Certified)
-
SELECT avg(salary) FROM Employee

c. Print the name and salary of every nonpilot whose salary is more than the average salary for pilots.
SELECT ename, salary FROM Employee WHERE
eid NOT IN (SELECT eid FROM Certified)
AND salary > (SELECT avg(salary) FROM Employee WHERE eid IN (SELECT eid FROM Certified))

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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