CS422 HW2

Please upload your solutions to CSNS. Note that file uploading will be disabled automatically after 11:59PM of the due date, so please turn in your work on time.

flights( flight_number:integer, from:string, to:string, distance:integer, departs:time, arrives:time, price:integer )
aircraft( id:integer, name:string, cruising_range:integer )
pilots( id:integer, name:string, salary:integer )
certified( pilot_id:integer, aircraft_id:integer )

1. (24pt) Use SQL statements to create these tables in a PostgreSQL database, and populate the tables with some test data so each table has at least two records. Make sure that you use appropriate data types and data integrity constraints for the tables. For this exercise, please submit an SQL script hw2-create.sql.

2. (56pt) Use the tables you created in the previous exercise to complete the following queries. For this exercise, please submit an SQL script hw2-queries.sql.

(a) Find the names of the aircrafts such that all pilots certified to operate them earn more than $80,000.

(b) For each pilot who is certified for more than three aircraft, find the maximum cruising range of the aircrafts for which she or he is certified.

(c) Find the names of the pilots whose monthly salary is less than the price of the cheapest route from Los Angeles to Honolulu.

(d) For each aircraft with cruising range over 1000 miles, list the name of the aircraft and the average salary of all the pilots certified for the aircraft.

(f) Find the names of all the aircrafts that can be used on the routes from Los Angeles to Chicago.

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

(h) Print the names of the pilots who can operate planes with cruising range greater than 3000 miles but are not certified for any Boeing aircraft.

(i). A customer wants to travel from Madison to New York tomorrow 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 6pm tomorrow.

(j) Compute the difference between the average salary of the pilots who are certified for Boeing 747 and the average salary of all the pilots who are certified for Airbus 300.

(k) Print the name and the salary of the highest paid pilot who is certified for Boeing 747.

(l) Print the names of the pilots who are certified only on aircrafts with cruising range longer than 1000 miles.

(m) Print the names of the pilots who are certified on at least two aircrafts with cruising range longer than 1000 miles.

(n) Print the names of the pilots who are certified only on aircrafts with cruising range longer than 1000 miles, but on at least two such aircrafts.