Question

The following relations keep track of airline flight information:

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)

Note that the Employee relation describes pilots and other kinds of employees as well; every pilot is certified for some aircraft, and only pilots are certified to fly. Write each of the following queries in SQL.

1. Create a view (called EMP_MID_SAL) that has all employee tuples in table Employee where the salary is between $40,000 and $50,000.

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

Answer 1

1. CREATE VIEW EMP_MID_SAL AS SELECT * FROM Employee WHERE SALARY > 40000 and SALARY<50000

2. SELECT MIN(PRICE), MAX(PRICE), COUNT(*) FROM Flight WHERE from = 'NY' and (to = 'CA' or to = 'LA')

NOTE:Both San Jose and Los Angeles are given the same code CA in question, so considering Los Angeles code as LA, i have written the above query. Change it according to the correct question.