CSE3330 Project #2

Query formulation
Tuesday October 25, before midnight
Worth 8% of the final grade


Implement in SQL and evaluate in phpMyAdmin the following queries:

  1. For each flight, print the flight number, the departure and the arrival airport.
  2. Give all the flight numbers and departure/arrival times of all flights that depart from DFW and arrive at LAX airport on 11/05/11.
  3. For each airline, print the airline name and the number of its flights departing from DFW on 11/05/11.
  4. For each airport and for each airline, print the airport name, the airline name, and the number of flights of this airline departing from that airport, and the average cost of these flights.
  5. Print all flight numbers from DFW to LAX that have at least one available seat and cost less than $500 in economy class.
  6. For each flight departing from DFW and arriving at LAX that has at least one available seat, print the flight number and the number of available seats.
You may use names other than DFW, LAX, etc. Write your queries in a file queries.sql and evaluate them using mysql on Omega. You may use phpMyAdmin to run your queries one-by-one before you write them in the file queries.sql and evaluate them using mysql on Omega. You should add more data into your database so that each one of the above queries prints at least two rows. You need to submit your queries.sql file, the exported schema and data (as the one you submitted for project #1, but updated/corrected), and a printout of the results when you evaluate your queries against your data. To get a printout of the results in mysql, use the command tee output.txt and then do source queries.sql to execute the queries.

You must use the following form to submit the three files (queries.sql, your-netid.sql, and output.txt) electronically (one file at a time). You may submit each file as many times as you like, but only the most recently submitted file will be retained and evaluated.

Submit Project #2:

Last modified: 10/13/11 by Leonidas Fegaras