Introduction:

The 35 queries that constitute the SQL Experience will collectively provide you with a good introduction to SQL.  Some are quite easy while others are a bit more challenging.   Queries in phases 1 & 2 are worth 4 points each.   There is an optional extra credit opportunity to earn an additional 10 points (1 pt/query). Thus a total of 110 points can be earned on the SQL Project. 

The SQL queries you will be working on are based on eight tables that make up a database for the Airline reservation system. The database is stored on the same Oracle database we have been using and is accessible via an internet connection. The server is running Oracle Standard Edition One 11.2.0.2.  You will be required to download and install SQL Developer and then connect to the SQL Experience database.

Using SQL Developer to Create, Edit and Execute SQL Queries

Queries are due at 11:59 p.m. on the due date. You will submit your queries at https://research.gmgrimes.com/SQLProject.

There are 3 steps to create and save a query:

  1. Draft an SQL query that you believe will result in the data needed.
    1. It is highly recommended you do this using Notepad (Windows) or TextEdit (Mac)
    2. Do not write your queries in Word or they will not work!
  2. Test/debug in SQL Developer
  3. Copy and paste your queries into the SQL Project submission site at https://research.gmgrimes.com/SQLProject

Step 1: Draft SQL query that results in the data needed

Step 2: Test/debug in SQL Developer

Copy/paste your query from notepad into SQL Developer and try running it. Did you get the results you expected?

If you make changes to the query in SQL developer, remember to copy/paste the modified query back into your notepad file.

Step 3: Copy and paste your answers into the SQL Project submission system.  

SQL Project Phase 1

(15 Queries, 60 points):  Due April 11th at 11:59 pm

The output for each query should include the column headings shown below the statement of the query itself.  Thus you may find yourself using column aliases frequently.

  1. List all the people in the passenger table, including their name, itinerary number, fare, and confirmation number. Order by name and fare.
  • Using an “OR” operator, list pilot name, state, zip code, and flight pay for pilots who make more than $2,500 per flight and live in either the state TX or AZ. Order by pay in descending order.
  •  Using an “IN”, list pilot names, zip and flight pay for pilots who make more than $2,500 per flight and live in either the state TX or AZ. Order by pay in descending order.
  •  Using a SET OPERATOR, list pilot names, zip and flight pay for pilots who make more than $2,500 per flight and live in either the state TX or AZ. Order by pay in descending order.
  • Using an “AND” and an “OR”, list all information (Equipment Number, Equipment Type, Seat Capacity, Fuel Capacity, and Miles per Gallon) on aircraft that have a seat capacity greater than 300, or aircraft that have a miles per gallon greater than 3.5 miles per gallon and fuel capacity less than 2500. Order by EQ_EQUIP_No.
  • Using a SET OPERATION, list all information (Equipment Number, Equipment Type, Seat Capacity, Fuel Capacity, and Miles per Gallon) on aircraft that have a seat capacity greater than 300, or aircraft that have a miles per gallon greater than 3.5 miles per gallon and fuel capacity less than 2500. Order by EQ_EQUIP_No.
  • Using PATTERN MATCHING, select all information for airports in TX.
  • Using a HAVING statement, produce a unique list of pilot Id’s as “Pilot ID” of pilots who piloted more than 20 departures.  Order by pilot id ascending.
  • List all flights showing flight number, flight fare, flight distance, and the miles flown per dollar (distance/fare) as “Miles Flown Per Dollar” that have miles per dollar greater than $5.50, and sort by miles flown per dollar descending.
  1.  Display airport location and number of departing flights as “Number of departing Flights”.
  1.  List the maximum pay, minimum pay and average flight pay by state for pilots.  Make sure to name the attributes as shown in the example output.
  1.  Display pilot name and departure date of his first flight. Order by pilot name.
  1. For each unique equipment type, List the equipment types and maximum miles that can be flown as “Maximum Distance Flown”.  Order by maximum distance descending.
  1. List the number of flights originating from each airport as NUMBER_OF_FLIGHTS.
  1. List the equipment type and max distance possible on a full tank of fuel for each plane with a maximum distance greater than 8600. Order by maximum distance, from lowest to highest.

SQL Project Phase 2

(10 Queries, 40 points):  Due April 25th by 11:59pm.

The output for each query should include the column headings shown below the statement of the query itself.  Thus you will find yourself using column aliases frequently.   You must follow the instructions outlined in the Instructions for submitting SQL Experience assignments section of this document to prepare you files for submission.

  1.  Using an “OR” statement and a “Cartesian product / WHERE” join, display flight number, origination and destination for flights that originate from an airport that does not have a hub airline or flights that originate from an airport that is a hub for American Airlines. Order by origination airport.
  • Using an SET OPERATOR statement and a “JOIN ON” join, display flight number, origination and departure for flights that originate from an airport that does not have a hub airline or flights that originate from an airport that is a hub for American Airlines. Order by origination airport.
  • Display the flight number, departure date and equipment type for all equipment that is manufactured by Concorde. Order by departure date and flight number.
  • Using a SET OPERATOR, display the IDs and names of pilots who are not currently scheduled for a departure.
  • Using a SUB QUERY, display the IDs and names of pilots who are not currently scheduled for a departure.
  • Using “IS NULL” and an OUTER JOIN, display the IDs and names of pilots who are not currently scheduled for a departure.
  • Display passenger name and seat number, as “Seat Number”, for flight 101, departing on July 15, 2017
  • List flight number, departure date and number of passengers as “Number of Passengers” for departures that have more than 5 passengers.
  • Select flight number, origination and destination for all reservations booked by Andy Anderson, Order results by flight number.
  1. Display departing airport code as “Departs From”, arriving airport code as “Arrives at”, and minimum fair as “Minimum Fair”, for flights that have minimum fare for flights between these two airports.

SQL Project Phase 3 (Bonus)

(10 Question, 10 points):  Due April 25 by 11:59pm.

  1.  Display the age and name for the pilot who was the oldest when HIRED.
  •  Using a SET OPERATOR, display the pilots and the number of miles flown as “Miles Flown”, include pilots who have not yet flown (for those pilots, display “0” for miles flown”).
  • Parse Pilot Names into First Name / Middle Initial, and Last Name.
  • List the pilots that are paid above the average for their state and the state average pay.
  • Display the name of the pilot, pay and age of the pilot who has the maximum pay of pilots under the age of 50 (Hint, you must calculate age using pil_birthdate and sysdate).
  • Using a SUB QUERY, display the flight number, originating airport, destination airport, departure time as “Departure Time”, and arrival time as “Arrival Time” for flights not departing on May 17, 2017.
  • Using a SET OPERATION, display the flight number, originating airport, destination airport, departure time as “Departure Time”, and arrival time as “Arrival Time” for flights not departing on May 17, 2017.

<Same output as previous query>

  • Using a SUB QUERY, list pilot names for pilots who have no scheduled departures in May 2017.
  • Using a SET OPERATION, list pilot names for pilots who have no scheduled departures in May 2017.
  1. Find the number of passengers that have the same last name. Display the number of passengers with each last name, ordered by number of passengers per last name in descending order.

ERD


Tables and Associated Data

SQL> describe ticket

Name             Null     Type       

—————- ——– ———–

TIC_ITINERARY_NO NOT NULL NUMBER     

TIC_FLIGHT_NO    NOT NULL NUMBER     

TIC_FLIGHT_DATE  NOT NULL DATE       

TIC_SEAT                  VARCHAR2(3)

SQL> select * from TICKET;

                       TIC_ITINERARY_NO                           TIC_FLIGHT_NO TIC_FLIGHT_DATE TIC

————————————— ————————————— ————— —

                                      1                                      15 01-APR-17       10D

                                      1                                     329 01-APR-17       12D

                                      2                                      15 01-APR-17       10E

                                      2                                     329 10-APR-17       12E

                                      3                                     101 15-JUL-17       3D

                                      3                                     104 23-JUL-17       4D

                                      4                                     101 15-JUL-17       3C

                                      4                                     104 23-JUL-17       4C

                                      5                                     101 15-JUL-17       3B

                                      5                                     104 20-JUL-17       4B

                                      6                                     101 18-APR-17       10A

                                      6                                     104 23-JUL-17       8B

                                      7                                     101 18-APR-17       10B

                                      7                                     104 30-JUL-17       8C

                                      8                                     101 18-APR-17       10C

                                      8                                     104 23-JUL-17       8D

                                      9                                     606 27-APR-17       12B

                                      9                                     691 08-JUL-17       12A

                                     10                                     606 20-APR-17       12C

                                     11                                     606 17-MAY-17       12B

                                     11                                     691 19-MAY-17       13B

                                     12                                     102 18-APR-17       10B

                                     12                                     103 19-APR-17       7B

                                     13                                     102 18-APR-17       7C

                                     13                                     103 23-APR-17       3C

 25 rows selected

SQL> describe passenger

Name             Null     Type        

—————- ——– ————

PAS_NAME                  VARCHAR2(20)

PAS_ITINERARY_NO NOT NULL NUMBER      

PAS_FARE                  NUMBER      

PAS_CONFIRM_NO            NUMBER      

SQL> select * from PASSENGER;

PAS_NAME                                    PAS_ITINERARY_NO   PAS_FARE                          PAS_CONFIRM_NO

——————– ————————————— ———- —————————————

Ole Olson                                                  1        410                                       1

Lena Olson                                                 2         98                                       1

Pete Peterson                                              3        315                                       2

Hazel Peterson                                             4        315                                       2

David Peterson                                             5        315                                       2

Swen Swenson                                               6        345                                       3

Olga Swenson                                               7        315                                       3

Pete Swenson                                               8        409                                       3

Andy Anderson                                              9        436                                       4

Gloria Anderson                                           10        436                                       4

Torgie Torgenson                                          11      578.5                                       5

Andy Anderson                                             12        560                                       6

Gloria Anderson                                           13        312                                       6

 13 rows selected

SQL> describe reservation

Name            Null     Type        

————— ——– ————

RES_CONFIRM_NO  NOT NULL NUMBER      

RES_DATE                 DATE        

RES_NAME                 VARCHAR2(20)

RES_PHONE                VARCHAR2(10)

RES_FLIGHT_NO            NUMBER      

RES_FLIGHT_DATE          DATE        

SQL> select * from RESERVATION;

                         RES_CONFIRM_NO RES_DATE  RES_NAME             RES_PHONE                            RES_FLIGHT_NO RES_FLIGHT_DATE

————————————— ——— ——————– ———- ————————————— —————

                                      1 01-APR-17 Ole Olson            2186942221                                      15 01-APR-17     

                                      2 01-APR-17 Pete Peterson        2186943000                                     101 15-JUL-17     

                                      3 01-APR-17 Swen Swenson         2186948822                                     101 18-APR-17     

                                      4 11-APR-17 Andy Anderson        6025233510                                     606 21-APR-17     

                                      5 17-APR-17 Torgie Torgenson     2082223333                                     606 17-MAY-17     

                                      6 17-APR-17 Andy Anderson        6025233510                                     102 18-APR-17     

 6 rows selected

SQL> describe departures

Name          Null     Type       

————- ——– ———–

DEP_FLIGHT_NO NOT NULL NUMBER     

DEP_DEP_DATE  NOT NULL DATE       

DEP_PILOT_ID           VARCHAR2(3)

DEP_EQUIP_NO           NUMBER     

SQL> select * from DEPARTURES;

                          DEP_FLIGHT_NO DEP_DEP_DATE DEP                            DEP_EQUIP_NO

————————————— ———— — —————————————

                                     15 01-APR-17    WRP                                    1026

                                     15 28-JUN-17    WRP                                    1026

                                     15 20-AUG-17    WRP                                    1026

                                     40 28-JUN-17    SL                                     1081

                                     40 29-JUN-17    SL                                     1081

                                     40 20-AUG-17    SL                                     1081

                                     40 21-AUG-17    SL                                     1081

                                     40 21-SEP-17    SL                                     1081

                                     40 22-SEP-17    SL                                     1081

                                     40 23-SEP-17    SL                                     1081

                                     40 29-SEP-17    SL                                     1081

                                     60 19-APR-17    ELL                                    1194

                                     60 04-JUN-17    ELL                                    1194

                                     60 05-JUN-17    ELL                                    1194

                                     60 08-JUN-17    ELL                                    1194

                                     60 10-JUN-17    ELL                                    1194

                                     60 20-JUN-17    ELL                                    1194

                                     60 21-JUN-17    ELL                                    1194

                                     60 29-JUL-17    ELL                                    1194

                                     60 24-AUG-17    ELL                                    1194

                                     60 25-SEP-17    ELL                                    1194

                                    101 18-APR-17    KCH                                    1253

                                    101 15-JUL-17    KCH                                    1253

                                    101 29-SEP-17    KCH                                    1253

                                    102 02-APR-17    KCH                                    1253

                                    102 18-APR-17    KCH                                    1253

                                    102 21-APR-17    KCH                                    1253

                                    102 25-APR-17    KCH                                    1253

                                    102 01-MAY-17    KCH                                    1253

                                    102 18-MAY-17    KCH                                    1253

                                    102 21-JUN-17    KCH                                    1253

                                    102 24-AUG-17    KCH                                    1253

                                    102 25-AUG-17    KCH                                    1253

                                    103 02-APR-17    KCH                                    1253

                                    103 19-APR-17    KCH                                    1253

                                    103 21-APR-17    KCH                                    1253

                                    103 22-APR-17    KCH                                    1253

                                    103 23-APR-17    KCH                                    1253

                                    103 29-APR-17    KCH                                    1253

                                    103 05-MAY-17    KCH                                    1253

                                    103 18-MAY-17    KCH                                    1253

                                    103 21-JUN-17    KCH                                    1253

                                    103 26-AUG-17    KCH                                    1253

                                    103 28-AUG-17    KCH                                    1253

                                    103 29-AUG-17    KCH                                    1253

                                    104 13-MAY-17    KCH                                    1253

                                    104 25-MAY-17    KCH                                    1253

                                    104 02-JUN-17    KCH                                    1253

                                    104 20-JUL-17    KCH                                    1253

                                    104 23-JUL-17    KCH                                    1253

                                    104 30-JUL-17    KCH                                    1253

                                    104 26-SEP-17    KCH                                    1253

                                    329 01-APR-17    WRP                                    1026

                                    329 10-APR-17    WRP                                    1026

                                    329 20-JUN-17    WRP                                    1026

                                    329 23-AUG-17    WRP                                    1026

                                    329 24-AUG-17    WRP                                    1026

                                    329 25-AUG-17    WRP                                    1026

                                    329 27-AUG-17    WRP                                    1026

                                    329 28-AUG-17    WRP                                    1026

                                    329 22-SEP-17    WRP                                    1026

                                    400 20-AUG-17    SL                                     1081

                                    400 21-AUG-17    SL                                     1081

                                    400 23-AUG-17    SL                                     1081

                                    400 28-AUG-17    SL                                     1081

                                    400 21-SEP-17    SL                                     1081

                                    400 23-SEP-17    SL                                     1081

                                    400 25-SEP-17    SL                                     1081

                                    500 02-JUN-17    RS                                     1368

                                    500 18-JUN-17    RS                                     1368

                                    500 01-SEP-17    RS                                     1368

                                    501 05-JUN-17    RS                                     1368

                                    501 07-JUN-17    RS                                     1368

                                    501 22-JUN-17    RS                                     1368

                                    501 29-JUN-17    RS                                     1368

                                    501 08-SEP-17    RS                                     1368

                                    501 09-SEP-17    RS                                     1368

                                    503 28-APR-17    ELG                                    1489

                                    503 29-APR-17    ELG                                    1489

                                    503 23-JUL-17    ELG                                    1489

                                    503 27-JUL-17    ELG                                    1489

                                    503 29-JUL-17    ELG                                    1489

                                    503 30-JUL-17    ELG                                    1489

                                    518 29-APR-17    CG                                     1345

                                    518 30-APR-17    CG                                     1345

                                    518 30-MAY-17    CG                                     1345

                                    518 28-JUN-17    CG                                     1345

                                    518 29-JUN-17    CG                                     1345

                                    518 29-SEP-17    CG                                     1345

                                    521 21-APR-17    CG                                     1085

                                    521 19-JUL-17    CG                                     1085

                                    521 20-JUL-17    CG                                     1085

                                    600 17-APR-17    WRP                                    1347

                                    600 25-APR-17    WRP                                    1347

                                    600 21-JUN-17    WRP                                    1347

                                    600 16-JUL-17    WRP                                    1347

                                    600 26-AUG-17    WRP                                    1347

                                    600 25-SEP-17    WRP                                    1347

                                    604 22-APR-17    CG                                     1345

                                    604 24-APR-17    CG                                     1345

                                    604 30-APR-17    CG                                     1345

                                    604 25-AUG-17    CG                                     1345

                                    604 22-SEP-17    CG                                     1345

                                    606 20-APR-17    SL                                     1081

                                    606 21-APR-17    SL                                     1081

                                    606 25-APR-17    SL                                     1081

                                    606 27-APR-17    SL                                     1081

                                    606 17-MAY-17    SL                                     1081

                                    606 21-JUN-17    SL                                     1081

                                    606 15-JUL-17    SL                                     1081

                                    691 24-APR-17    WRP                                    1347

                                    691 29-APR-17    WRP                                    1347

                                    691 13-MAY-17    WRP                                    1347

                                    691 19-MAY-17    WRP                                    1347

                                    691 02-JUN-17    WRP                                    1347

                                    691 28-JUN-17    WRP                                    1347

                                    691 19-JUL-17    WRP                                    1347

                                    691 20-JUL-17    WRP                                    1347

                                    691 23-JUL-17    WRP                                    1347

                                    691 22-SEP-17    WRP                                    1347

                                    723 22-APR-17    CG                                     1085

                                    723 23-APR-17    CG                                     1085

                                    723 24-APR-17    CG                                     1085

                                    723 25-APR-17    CG                                     1085

                                    723 20-JUL-17    CG                                     1085

                                    723 21-JUL-17    CG                                     1085

                                    723 26-JUL-17    CG                                     1085

                                    723 29-JUL-17    CG                                     1085

                                   1260 18-APR-17    SL                                     1081

                                   1260 19-APR-17    SL                                     1081

                                   1260 22-APR-17    SL                                     1081

                                   1260 29-APR-17    SL                                     1081

                                   1260 27-JUN-17    SL                                     1081

                                   1260 23-JUL-17    SL                                     1081

                                   1260 28-AUG-17    SL                                     1081

                                   1260 29-AUG-17    SL                                     1081

                                   1260 01-SEP-17    SL                                     1081

                                   1260 28-SEP-17    SL                                     1081

                                   1260 29-SEP-17    SL                                     1081

                                     15 08-APR-17    WRP                                    1026

                                     15 08-JUL-17    WRP                                    1026

                                     15 08-AUG-17    WRP                                    1026

                                     15 08-SEP-17    WRP                                    1026

                                     40 08-APR-17    SL                                     1081

                                     40 08-JUL-17    SL                                     1081

                                     40 08-AUG-17    SL                                     1081

                                     40 08-SEP-17    SL                                     1081

                                     59 08-APR-17    ELL                                    1194

                                     59 08-JUL-17    ELL                                    1194

                                     59 08-AUG-17    ELL                                    1194

                                     59 08-SEP-17    ELL                                    1194

                                     60 08-APR-17    ELL                                    1194

                                     60 08-JUL-17    ELL                                    1194

                                     60 08-AUG-17    ELL                                    1194

                                     60 08-SEP-17    ELL                                    1194

                                    101 08-APR-17    KCH                                    1253

                                    101 08-JUL-17    KCH                                    1253

                                    101 08-AUG-17    KCH                                    1253

                                    101 08-SEP-17    KCH                                    1253

                                    102 08-APR-17    KCH                                    1253

                                    102 08-JUL-17    KCH                                    1253

                                    102 08-AUG-17    KCH                                    1253

                                    102 08-SEP-17    KCH                                    1253

                                    103 08-APR-17    KCH                                    1253

                                    103 08-JUL-17    KCH                                    1253

                                    103 08-AUG-17    KCH                                    1253

                                    103 08-SEP-17    KCH                                    1253

                                    104 08-APR-17    KCH                                    1253

                                    104 08-JUL-17    KCH                                    1253

                                    104 08-AUG-17    KCH                                    1253

                                    104 08-SEP-17    KCH                                    1253

                                    329 08-APR-17    WRP                                    1026

                                    329 08-JUL-17    WRP                                    1026

                                    329 08-AUG-17    WRP                                    1026

                                    329 08-SEP-17    WRP                                    1026

                                    400 08-APR-17    SL                                     1081

                                    400 08-JUL-17    SL                                     1081

                                    400 08-AUG-17    SL                                     1081

                                    400 08-SEP-17    SL                                     1081

                                    500 08-APR-17    RS                                     1368

                                    500 08-JUL-17    RS                                     1368

                                    500 08-AUG-17    RS                                     1368

                                    500 08-SEP-17    RS                                     1368

                                    501 08-APR-17    RS                                     1368

                                    501 08-JUL-17    RS                                     1368

                                    501 08-AUG-17    RS                                     1368

                                    502 08-APR-17    ELG                                    1489

                                    502 08-JUL-17    ELG                                    1489

                                    502 08-AUG-17    ELG                                    1489

                                    502 08-SEP-17    ELG                                    1489

                                    503 08-APR-17    ELG                                    1489

                                    503 08-JUL-17    ELG                                    1489

                                    503 08-AUG-17    ELG                                    1489

                                    503 08-SEP-17    ELG                                    1489

                                    518 08-APR-17    CG                                     1345

                                    518 08-JUL-17    CG                                     1345

                                    518 08-AUG-17    CG                                     1345

                                    518 08-SEP-17    CG                                     1345

                                    521 08-APR-17    CG                                     1085

                                    521 08-JUL-17    CG                                     1085

                                    521 08-AUG-17    CG                                     1085

                                    521 08-SEP-17    CG                                     1085

                                    600 08-APR-17    WRP                                    1347

                                    600 08-JUL-17    WRP                                    1347

                                    600 08-AUG-17    WRP                                    1347

                                    600 08-SEP-17    WRP                                    1347

                                    604 08-APR-17    CG                                     1345

                                    604 08-JUL-17    CG                                     1345

                                    604 08-AUG-17    CG                                     1345

                                    604 08-SEP-17    CG                                     1345

                                    606 08-APR-17    SL                                     1081

                                    606 08-JUL-17    SL                                     1081

                                    606 08-AUG-17    SL                                     1081

                                    606 08-SEP-17    SL                                     1081

                                    691 08-APR-17    WRP                                    1347

                                    691 08-JUL-17    WRP                                    1347

                                    691 08-AUG-17    WRP                                    1347

                                    691 08-SEP-17    WRP                                    1347

                                    723 08-APR-17    CG                                     1085

                                    723 08-JUL-17    CG                                     1085

                                    723 08-AUG-17    CG                                     1085

                                    723 08-SEP-17    CG                                     1085

                                   1260 08-APR-17    SL                                     1081

                                   1260 08-JUL-17    SL                                     1081

                                   1260 08-AUG-17    SL                                     1081

                                   1260 08-SEP-17    SL                                     1081

 226 rows selected

SQL> describe equip_type

Name             Null     Type        

—————- ——– ————

EQ_EQUIP_NO      NOT NULL NUMBER      

EQ_EQUIP_TYPE             VARCHAR2(10)

EQ_SEAT_CAPACITY          NUMBER      

EQ_FUEL_CAPACITY          NUMBER      

EQ_MILES_PER_GAL          NUMBER(5,2) 

SQL> select * from EQUIP_TYPE;

                            EQ_EQUIP_NO EQ_EQUIP_T                        EQ_SEAT_CAPACITY                        EQ_FUEL_CAPACITY EQ_MILES_PER_GAL

————————————— ———- ————————————— ————————————— —————-

                                   1026 BOE 727                                        188                                    1882              3.4

                                   1194 DC 7                                           282                                    2340              3.8

                                   1080 BOE 727                                        188                                    1882              3.4

                                   1368 DC 7                                           282                                    2340              3.8

                                   1081 BOE 727                                        188                                    1882              3.4

                                   1345 BOE 737                                        270                                    2150              4.1

                                   1253 BOE 747                                        480                                    2800              3.6

                                   1489 CONCORDE                                       350                                    2750              3.1

                                   1347 BOE 737                                        270                                    2150              4.1

                                   1082 BOE 727                                        188                                    1882              3.4

                                   1346 BOE 737                                        270                                    2150              4.1

                                   1083 BOE 727                                        188                                    1882              3.4

                                   1084 BOE 727                                        188                                    1882              3.4

                                   1085 BOE 727                                        188                                    1882              3.4

 14 rows selected

SQL> describe pilots

Name           Null     Type        

————– ——– ————

PIL_PILOT_ID   NOT NULL VARCHAR2(3) 

PIL_PILOTNAME           VARCHAR2(30)

PIL_SSN                 VARCHAR2(9) 

PIL_STREET              VARCHAR2(20)

PIL_CITY                VARCHAR2(12)

PIL_STATE               VARCHAR2(2) 

PIL_ZIP                 VARCHAR2(5) 

PIL_FLIGHT_PAY          NUMBER(4)   

PIL_BRTHDATE            DATE        

PIL_HIREDATE            DATE        

SQL> select * from pilots;

PIL PIL_PILOTNAME                  PIL_SSN   PIL_STREET           PIL_CITY     PI PIL_Z PIL_FLIGHT_PAY PIL_BRTHDATE PIL_HIREDATE

— —————————— ——— ——————– ———— — —– ————– ———— ————

WRP Pasewark, William B.           523451784 64 RHODA AVENUE      PHOENIX      AZ 84326           2500 02-JUL-58    23-JUL-93  

ELL Leiss, Ernst L.                234783222 91 BEVERLY HILL RD   FLAGSTAFF    AZ 85120           2640 12-SEP-53    21-JAN-90  

SSM Moore, Sharon S.               452127055 44 THORNHILL TRAIL   LOS ANGELES  CA 92300           3000 03-SEP-52    20-DEC-89  

RS  Scamell, Richard               452094673 1500 EAST RIVERSIDE  CUPERTINO    CA 94212            560 13-FEB-13    20-MAY-95  

SL  Long, Stuart                   148434500 3722 GEORGETOWN      DALLAS       TX 75090           3000 12-OCT-50    03-JUN-91   

CG  Gladchuk, Chet                 210453278 519 TALL OAKS DRIVE  HOUSTON      TX 77024           3150 03-JUN-51    28-FEB-93  

KCH Harris, Kenneth C.             211463278 519 GREEN OAKS DRIVE HOUSTON      TX 77004           3150 03-JUN-70    28-MAY-93  

VIT Tabor, Victor T.               234651322 891 BARONSHIRE       FLAGSTAFF    AZ 85120           2640 12-SEP-73    21-JAN-98  

ELG Green, Edward L.               234781000 9211 CANYON HILL RD  FLAGSTAFF    AZ 85120           2640 12-SEP-63    21-JAN-89  

 9 rows selected

SQL> describe flight

Name          Null     Type       

————- ——– ———–

FL_FLIGHT_NO  NOT NULL NUMBER(6)  

FL_ORIG                VARCHAR2(3)

FL_DEST                VARCHAR2(3)

FL_ORIG_TIME           DATE        

FL_DEST_TIME           DATE       

FL_MEAL                CHAR(1)    

FL_FARE                NUMBER     

FL_DISTANCE            NUMBER     

FL_TIME_ZONES          NUMBER     

SQL> select * from flight;

FL_FLIGHT_NO FL_ FL_ FL_ORIG_TIME FL_DEST_TIME F    FL_FARE                             FL_DISTANCE                           FL_TIME_ZONES

———— — — ———— ———— – ———- ————————————— —————————————

         101 FLG PHX 01-JAN-18    01-JAN-18    S       48.5                                     200                                       0

         102 PHX MSP 01-JAN-18    01-JAN-18    L        156                                    1000                                       1

         103 MSP PHX 01-JAN-18    01-JAN-18    D        156                                    1000                                      -1

         104 PHX FLG 01-JAN-18    01-JAN-18    S       48.5                                     200                                       0

          15 PHX LAX 01-JAN-18    01-JAN-18    B         49                                     400                                      -1

         329 LAX PHX 01-JAN-18    01-JAN-18              49                                     400                                       1

          40 PHX LAX 01-JAN-18    01-JAN-18              49                                     400                                      -1

         400 LAX PHX 01-JAN-18    01-JAN-18              49                                     400                                       1

         600 PHX SFO 01-JAN-18    01-JAN-18    B        109                                     750                                      -1

         604 PHX SFO 01-JAN-18    01-JAN-18    B        109                                     750                                      -1

         606 PHX SFO 01-JAN-18    01-JAN-18    L        109                                     750                                      -1

         198 LAX SFO 01-JAN-18    01-JAN-18              69                                     400                                       1

         298 PHX LAX 01-JAN-18    01-JAN-18              39                                     400                                      -1

          60 PHX SFO 01-JAN-18    01-JAN-18    S        139                                     750                                      -1

          59 SFO PHX 01-JAN-18    01-JAN-18    S        149                                     750                                       1

         691 SFO PHX 01-JAN-18    01-JAN-18             109                                     750                                       1

         518 SFO PHX 01-JAN-18    01-JAN-18             109                                     750                                       1

        1260 SFO PHX 01-JAN-18    01-JAN-18    D        109                                     750                                       1

         500 IAH DFW 01-JAN-18    01-JAN-18              79                                     250                                       0

         501 DFW IAH 01-JAN-18    01-JAN-18              79                                     250                                       0

         502 IAH JFK 01-JAN-18    01-JAN-18    B        289                                    1200                                      -1

         503 JFK IAH 01-JAN-18    01-JAN-18    S        289                                    1200                                       1

         521 IAH DTW 01-JAN-18    01-JAN-18    L        218                                    1000                                       0

         723 DTW JFK 01-JAN-18    01-JAN-18    S        189                                    1000                                       1

 24 rows selected

SQL> describe airport

Name            Null     Type        

————— ——– ————

AIR_CODE        NOT NULL VARCHAR2(3) 

AIR_LOCATION             VARCHAR2(20)

AIR_ELEVATION            NUMBER      

AIR_PHONE                VARCHAR2(10)

AIR_HUB_AIRLINE          VARCHAR2(20)

SQL> select * from airport;

AIR AIR_LOCATION                                   AIR_ELEVATION AIR_PHONE  AIR_HUB_AIRLINE   

— ——————– ————————————— ———- ——————–

FLG Flagstaff, AZ                                           6920 6027741897                    

PHX Phoenix, AZ                                             1257 6025831971 Air West           

MSP Minneapolis, MN                                          862 6123782910 Northwest          

LAX Los Angeles, CA                                           37 3102731846                    

SFO San Francisco, CA                                         78 4158392371                    

IAH Houston, TX                                                5 2816642000 Continental        

DFW Dallas, TX                                               210 2146218044 American           

JFK New York, NY                                              43 2106732145 United             

DTW Detroit, MI                                               23 4158219000 American            

OIA Orlando, FL                                               20 5183240912 TWA                

MIA Miami, FL                                                 16 7175203400 Grand Cayman       

 11 rows selected

All papers are written by ENL (US, UK, AUSTRALIA) writers with vast experience in the field. We perform a quality assessment on all orders before submitting them.

Do you have an urgent order?  We have more than enough writers who will ensure that your order is delivered on time. 

We provide plagiarism reports for all our custom written papers. All papers are written from scratch.

24/7 Customer Support

Contact us anytime, any day, via any means if you need any help. You can use the Live Chat, email, or our provided phone number anytime.

We will not disclose the nature of our services or any information you provide to a third party.

Assignment Help Services
Money-Back Guarantee

Get your money back if your paper is not delivered on time or if your instructions are not followed.

We Guarantee the Best Grades
Assignment Help Services