Fall 2022
Introduction:…………. 2
Installing SQL Developer and connecting to the database……………….. 2
Using SQL Developer to Create, Edit and Execute SQL Queries……………………………. 4
SQL Project Phase 1. 8
SQL Project Phase 2………………………….. 14
SQL Project Phase 3 (Extra Credit).. Error! Bookmark not defined.
ERD…………………… 22
Tables and Associated Data…………………… 23
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. Please see the syllabus and lectures for due dates.
- SQL Project Phase #1 (15 queries)
- SQL Project Phase #2 (10 queries)
- SQL Project Phase #3 (10 queries) optional Extra Credit
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.
There is a video with some detail and instruction here: https://www.youtube.com/watch?v=WS9NxYH89_w – this was recorded a few semesters ago, but everything except the dates should be accurate, so just ignore any dates that are mentioned.
Installing SQL Developer and connecting to the database
Step 1: To begin using the database, you must first download and install SQL Developer 22.2.1 (or most current version). This software can be downloaded from: https://www.oracle.com/tools/downloads/sqldev-downloads.html.
Downloading & Installing SQL Developer
- Go to ORACLE.COM and register for a free Oracle Web account
- Go to the website above and accept OTN License agreement and select the appropriate version of Oracle SQL Developer to download.
- Login using your Oracle Web Account and download the file
- Extract the files into a directory on the hard drive (e.g. C:\Program Files\Oracle)
Step 2: You must also have Java SE Developer version 8.0 or higher installed. This software can be downloaded from the following website:
http://www.oracle.com/technetwork/java/javase/downloads/jdk8-downloads-2133151.html Windows users should download jdk-8u60-windows-i586.exe, not jdk-8u60-windows-x64.exe (64-bit version). The steps to follow include:
Downloading & Installing Java SE Development Kit
- Go to the website above and accept the license agreement and select the appropriate version to download
- Follow the instructions in the install wizard. No optional features are needed. Make note of the directory in which you install the software as you will need it later.
- Click close to exit the wizard once the software installation is complete
Step 3: Once you have successfully installed both Oracle SQL Developer andJava SE Development Kit you are ready to begin.
- launch SQL Developer
- The first time only, you will be prompted to enter the directory where Java SE Development Kit is installed
- In the upper left hand pane, right click on “Connections” and choose “New Connection”.
- For connection Name, enter SQL Experience
- For username and password, enter the following information.
- Username: Your Blackboard Userid
- Password: Your PeopleSoft ID (include leading zeroes, if they exist)
- Check the “Save Password” box
- For Hostname enter the following exactly as shown: 192.138.193.71
- Verify that the port number is 1522
- Press “Connect” to connect to the server.
| This can be anything you want |
| Your Blackboard Userid |
| Your PeopleSoft ID |
| Make sure to change this to 1522 |
| IP Address of Oracle Server |
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:
- Draft an SQL query that you believe will result in the data needed.
- It is highly recommended you do this using Notepad (Windows) or TextEdit (Mac)
- Do not write your queries in Word or they will not work!
- Test/debug in SQL Developer
- 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
Think about the question being asked and write the query in your text editor. Refer to the ERD and tables at the end of this document.
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.
- URL: https://research.gmgrimes.com/SQLProject
- Your username is your blackboard username
- Your password is initially your PSID – the first time you log in you will be required to change your password
- See the YouTube video for more details: https://youtu.be/WS9NxYH89_w on the YouTube channel: https://bit.ly/ProfessorMG
Make sure you click Save for EVERY query!
The background will turn green to indicate you answer has been saved
If you are idle for too long (10 minutes or so) the server may “time out” and not save your answers. I suggest closing your browser and logging back in to make sure your answers were saved!
SQL Project Phase 1
(15 Queries, 60 points)
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.
- 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 on the AIR_LOCATION attribute, select all information for airports in TX.
- Using an aggregate function and HAVING, 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.
- Display airport location and number of departing flights as “Number of departing Flights”.
- 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.
- Display pilot name and departure date of his first flight. Order by pilot name.
- 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.
- List the number of flights originating from each airport as NUMBER_OF_FLIGHTS.
- 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)
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.
- 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.
- 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):
- 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 pilots under the age of 55 (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.
- 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
