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. Find the names of pilots certified for some Boeing aircraft.

b. Find the aids of all aircraft that can be used on routes from Los Angeles to Chicago.

c. Identify the routes that can be piloted by every pilot who makes more than $100,000.

d. Print the enames of pilots who can operate planes with cruisingrange greater than 3,000 miles, but are not certified on any Boeing aircraft.

Explanation / Answer

Following SQL queries follow Oracle SQL standard

a) SELECT DISTINCT ename FROM Employees NATURAL JOIN Certified NATURAL JOIN Aircraft WHERE aname LIKE '%Boeing% ;

b) SELECT aid FROM Aircraft, Flights WHERE Flights.to = 'Chicago' AND Flights.from = 'Los Angeles' AND cruisingrange > distance;

c) SELECT to, Flights.from FROM Employess NATURAL JOIN Certified NATURAL JOIN Aircraft, Flights WHERE salary > 100000 AND cruisingrange > distance;

d) SELECT ename FROM Employess NATURAL JOIN Certified NATURAL JOIN Aircraft WHERE cruisingrange > 3000 AND ename NOT IN (SELECT DISTINCT ename FROM Employees NATURAL JOIN Certified NATURAL JOIN Aircraft WHERE aname LIKE '%Boeing%)

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

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