Flight SQL

This assignment is designed to familiarize Oracle SQL*Plus. You may refer to the documents in the Lecture Notes on Oracle SQL for the various commands that will be needed to answer the questions. The database design and content for this exercise are for class discussion only and they do not represent actual design for any airline database.

Please study the “make-airline-DB.sql” script file (attached in this post) which contains the commands to create the Airline Database that includes information about pilots, employee, flights, planes, and other tables including populating them as shown in the make-airline-DB.sql

Directions:

• Upload the make-airline-DB.sql to uisacad5.uis.edu server or to your local Oracle database server if you are using your own database server.
• Login to the UISACAD5 server and to the Oracle database server.
• Run the make-airline-DB.sql file in the SQL prompt using: SQL>@make-airline-DB.sql; Make sure that the make-airline-DB.sql is in the directory where you are invoking the sqlplus; otherwise you have to include the full path name where the said file is located. Example: SQL>@homenetidfolder1make-airline-DB.sql. Running the SQL script will cleanup any existing tables with same name, create and populate the relations.
• Create a spool file using: SQL>spool airline_netid.txt. Spool file name should be named this way, airline_[your UIS netid].txt. Example, if I were to do this exercise, I would name my spool file as “airline_rsalv1.txt”.
• Verify that the tables are created, using select statement on each relation.
• Verify that the tables have the necessary columns and types using describe statement.
• Verify that the tuples are inserted using select statement on each relation.
• When everything looks great, run all those operations as requested in the Questions below.
• End the spool file by typing “spool off” in the SQL prompt.
• Using SFTP from your desktop, get the spool file. Example, sftp uisacad5.uis.edu enter netid and password psftp>get airline_netid.txt. This command will transfer a text file named airline_netid.txt to your PC’s directory or folder where you invoke the sftp assuming that the spool file is in the default folder at uisacad5 server; otherwise you have to change directory or include the path names in the get command.
• Open the spool file you have created using notepad, wordpad or textpad (please do not use Microsoft Word for this because it embeds a rich format in Blackboard and become unreadable), then go to the Exam Center and answer the questions (or simply copy and paste from the spool file the respective answer to the question on Assignment No. 2 at the Exam Center). You have one (1) hour to complete it. This one hour is a matter of transferring your answers from the spool file to Blackboard.

Questions:

Please include the SQL statement and result in each of the answer to get full points. 10 points each question.

• List all the columns (not the contents) of aircraft and flight tables.
• List the maker and model_no of all planes. (Hint: Use one table.)
• List all flights originating from ORD. Include the num, origin, dest, dep-time, arr_time. (Hint: Use one table.)
• List the name, emp_no and salary of all pilots. (Hint: Use two tables.)
• List the serial_no, model_no, maker of all aircrafts. (Hint: Use two tables.)
• List all flights departing date (dep_date) on Oct 31. Include the departing date, origin, dest, departing time (dep_time) and arriving time (arr_time). (Hint: Use two tables.)
• List all names of those who book their flight and sort the output by name. Include name, origin, dest, dep_date, dep_time, arr_time. (Hint: Use two tables.)
• Provide the details of the flight of Gates such as name, origin, dest,departing date, time of departure and arrival. (Hint: Use two tables and two conditions.)
• Who can fly the B757 model? Provide the name, model_no and emp_no in your query. (Hint: Use two tables.)
• Give the total number of employees, the maximum salary, the minimum salary and the total salary of all employees. (Hint: Use aggregate functions and one table only.)