Flight (flno: int, from: varchar(20), to: varchar(20), distance: real, departs:

Question

Flight (flno: int, from: varchar(20), to: varchar(20), distance: real, departs: time, arrives: time, price: real)

Aircraft (aid: int, aname: varchar(20), cruisingrange: real)

Certified (eid: int, aid: int)

Employee (eid: int, ename: varchar(20), salary: real)

SQL commands for:

For each pilot who is certified for more than three aircrafts, find the eid and the maximum cruisingrange of the aircraft for which she or he is certified.

Find the names of pilots whose salary is less than the price of the cheapest flight from Los Angeles, CA to Valdosta, GA.

Find the names of pilots certified for some Boeing aircraft. (Hint: consider substring pattern matching)

Find the aids of all aircraft that can be used on ALL the routes from Los Angeles, CA to Valdosta, GA.

Find the cheapest price, the highest price and the count of the flights that fly from New York City, NY to either San Jose, CA or Los Angeles, CA.

Explanation / Answer

1. Select c.eid,max(a.cruisingrange) from Certified C,Aircraft A where c.aid = a.aid group by c.eid having count(*) > 3;

2. Select distinct e.ename from Employees e where e.salary < (Select min(f.price) from Flights f where f.from = 'Los Angeles,CA' and to = 'Valdosta,GA');

3.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%';

4.Select a.aid from Aircraft a where A.cruisingrange > (Select min(f.distance) from flights f where f.from = 'Los Angeles, CA' and to = 'Valdosta, GA';

5. Select min(price),max(price),count(*) from Flight where from = 'New York City' and to = 'San Jose,CA' or to = 'Los Angeles,CA';

Related Questions

Navigate

• Browse (All)
• Browse F
• Subjects

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