USA: +1(669)289-8683 
Sign in
I only need the first phase to be done . Its questions 1-12.
SQL Experience Project
Fall 2023
Introduction:
The 20 queries that constitute the SQL Experience will collectively provide an excellent introduction to SQL. Some are pretty easy, while others are a
bit more challenging. For phase 1, there will be 12 queries, each worth 10 points. For phase 2, there will be eight queries, each worth 15 points. Phase
1 will have 120 points, and Phase 2 will have 120 points. Thus, 220 points can be earned on the SQL Project. The assignment due dates are as follows:
● SQL Project Phase #1 (12 queries) is due on Nov 16
● SQL Project Phase #2 (8 queries) is due on Nov 27
Install Oracle, SQL Developer, and Import Tables
To finish this project, please install Oracle and SQL developer. Here is the playlist for installing Oracle and SQL Developer:
1. The first step is to install Oracle database 19c.
https://www.youtube.com/watch?v=Jn2YIiJGqY4&list=PL6q_mUNX4FTWmPG7xgl4mh0WBFXRJYE8E&index=1&ab_channel=jiangdatabase2020
2. The second step is to install SQL developer.
https://www.youtube.com/watch?v=GzACNMIg_24&list=PL6q_mUNX4FTWmPG7xgl4mh0WBFXRJYE8E&index=2&ab_channel=jiangdatabase2020
3. The third step is to connect the SQL developer and the Oracle database.
https://www.youtube.com/watch?v=t8RIxx-DX3w&list=PL6q_mUNX4FTWmPG7xgl4mh0WBFXRJYE8E&index=3&ab_channel=jiangdatabase2020
Please utilize the video resources above if you have trouble installing Oracle 19c and SQL Developer. On 10/26, I will host a Zoom session on
installing the SQL project software. A recording of the Zoom session will be uploaded as an additional resource to help with installation. In addition,
you can read the PDF file guides that include more detailed instructions for installing the software.
Please refer to the following guide: Instructions for downloading and installing Oracle Database 19c->Installing Oracle SQL Developer and importing
tables.
1
If you are a Mac user, an additional step is required: installing Windows. Please refer to the guide titled “Installation of Oracle Database 19c on a
Mac” to install Windows first on your Mac. If you are still unable to install Windows on your Mac, you may utilize the Melcher Hall lab computers.
Please refer to the guide “Using SQL Developer on MH272 Lab Computer” for more information.
4. The last step is to import tables or DDLs to run SQL commands. Please import tables to finish this project using the file called
“sql_project_ddl_importingtable.sql.”
https://www.youtube.com/watch?v=xMfiUHz_I2U&list=PL6q_mUNX4FTWmPG7xgl4mh0WBFXRJYE8E&index=4&ab_channel=jiangdatabase2020
5. You may also copy and paste the code from the file “sql_project_ddl_importingtable.txt” into SQL developer to execute the code, as seen in
the following image. After you paste the DDL code from the txt file called “sql_project_ddl_importingtable.txt,” you can click on the green
button with a triangle shape (circled by a red rectangle box) to execute all the DDL code. Then, the tables for this project will be imported.
2
6. To test whether you have successfully imported all the tables for this project, please type the following query into SQL developer:” select * from
passenger.” If you see a query result in the next image, you have successfully imported all the tables.
3
Using SQL Developer to Create, Edit, and Execute SQL Queries
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
Please copy and paste your query from Notepad into SQL Developer and try running it, or directly type your code into SQL Developer. Did you get
the results you expected? If not, you can make changes to the query in SQL developer.
4
In all cases, review your output to verify the accuracy of the result!
Step 3: Save the query in a .txt file. This step is crucial!
1. Copy queries from your SQL developer’s screen into a Notepad or clipboard and save the file as a .txt file. Do not save it as a Word document!
2. Each query should be under the appropriate query number.
3. When you are ready to submit, submit a .txt file of your queries to Blackboard. ONLY submit a .txt file similar to the following example:
Please follow the submission process carefully. Failure to do so will result in a 30% grade deduction. This submission process will help us grade
your submission smoothly.
SQL Project Phase 1
The output for each query should include the column headings shown below the query statement itself. The output should consist of the column
headings in each question’s screenshot. Unless specified in the question, the order of data does not matter. If the question requires you to order
your output based on the order of some columns, ensure that you are doing so.
The screenshot for each table of this project is at the end of this file.
1. List all the people in the passenger table, including their name, itinerary number, fare, and confirmation number. Order by name and fare.
5
2. Using an “OR” operator, list the pilot name, state, city, zip code, and flight pay for pilots who make over $2,500 per flight and live in Houston or
Phoenix.
3. Using an “IN,” list pilot names, state, city, zip code, and flight pay for pilots who make more than $2,500 per flight and live in either Houston or
Phoenix.
4. 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 seating capacity of less than 280 or aircraft that have miles per gallon greater than 4.0 miles per gallon and fuel capacity
less than 2000.
6
5. Using PATTERN MATCHING, select all information for airports in Los Angeles.
6. Using a HAVING statement, produce a unique list of pilot Id’s of pilots who piloted more than 20 departures—order by pilot id ascending.
7. 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 ascending. Make sure to name the attributes as shown in the
example output.
You can learn the round () function by yourself and try to apply it here so your “Miles Flown Per Dollar” column is rounded to two decimal
places. However, you won’t lose points if you don’t use the round function.
7
8. Display airport location and number of departing flights as “Number of departing Flights.”
9. 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.
10. Display pilot’s name and departure date of his first flight. Order by pilot name. Hint: you will need pilots and departures tables.
8
11. List the equipment types and maximum miles that can be flown as “Maximum Distance Flown” for each unique equipment type.
12. List the number of flights originating from each airport as NUMBER_OF_FLIGHTS. Hint: you will need to use the count function.
9
SQL Project Phase 2
The output for each query should include the column headings shown below the query statement itself. The output should consist of the column
headings in each question’s screenshot. Unless specified in the question, the order of data does not matter. If the question requires you to order
your output based on the order of some columns, ensure that you are doing so.
1. Using an “OR” statement and a “WHERE” join, display flight number, origination, and departure for flights that originate from an airport
that does not have a hub airline or flights that arise from an airport that is a hub for American Airlines. Hint: Please use “air_hub_airline is
null” in your answer.
2. Display the flight number, departure date, and equipment type for all equipment that Concorde manufactures. Order by departure date and
flight number. You need to use the “like” keyword in your query. Hint: please have the following part in your answer: “eq_equip_type like
‘%CON%’”
10
3. Using a SUB QUERY, display the IDs and names of pilots who are not currently scheduled for a departure. Hint: you will use the “not in”
keyword.
4. Using “IS NULL” and a LEFT OUTER JOIN, display the IDs and names of pilots who are not currently scheduled for a departure. Hint: please
have the following in your answer: “where departures.dep_dep_date is null”.
5. Display passenger name and seat number as “Seat Number” for flight 101, departing on July 15, 2017, and order by “Seat Number”. Hint:
You will need to select from the PASSENGER, TICKET table.
11
6. List flight number, departure date, and number of passengers as “Number of Passengers” for departures with more than five passengers.
Hint: You will need to use the count() function.
7. Select the flight number, origination, and destination for all reservations booked by Andy Anderson. Order results by flight number. Hint:
You will select from two tables (flight and reservation).
8. Display the departing airport location as “Departs From,” arriving airport location as “Arrives at,” and minimum fair as “Minimum Fair” for
flights that have a minimum fare for flights between these two airports. Hint: You will need to use the min() function and have the following
part in your answer: “ FROM FLIGHT, airport a, airport b
WHERE fl_orig = a.air_code and fl_dest = b.air_code”.
12
13
ERD
Flight Reservation Schema – Entity Relationship Diagram
PAS_ITINERARY_NO
PAS_NAME
PAS_FARE NUMBER
PAS_CONFIRM_NO NUMBER
1
Passenger
Passenger
reserves
RES_CONFIRM_NO
RES_DATE
RES_NAME
RES_PHONE
RES_FLIGHT_NO
RES_FLIGHT_DATE
Reservation
M
1
1
Is for Pass
Airport
Is for
departure
air_code
air_location
air_elevation
air_phone
air_hub_airline
1
M
TIC_ITINERARY_NO
TIC_FLIGHT_NO
TIC_FLIGHT_DATE
TIC_SEAT
Is for
departure
Ticket
M
pil_pilot_id
pil_pilotname
pil_ssn
pil_street
pil_city
pil_state
pil_zip
pil_flight_pay
pil_brthdate
pil_hiredate
1
1
1
Org Dest
DEP_FLIGHT_NO
DEP_DEP_DATE
DEP_PILOT_ID
DEP_EQUIP_NO
Departures
M
Flight
departures
M
1
Flys
M
Flight
fl_flight_no
fl_orig
fl_dest
fl_orig_time
fl_dest_time
fl_meal
fl_fare
fl_time_zones
Pilots
1
Uses
1
Equip_type
EQ_EQUIP_NO
EQ_EQUIP_TYPE
EQ_SEAT_CAPACITY
EQ_FUEL_CAPACITY
EQ_miles_per_gal
14
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;
SQL> describe passenger
15
Name
Null
Type
—————- ——– ———–PAS_NAME
VARCHAR2(20)
PAS_ITINERARY_NO NOT NULL NUMBER
PAS_FARE
NUMBER
PAS_CONFIRM_NO
NUMBER
SQL>select * from PASSENGER;
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;
16
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;
17
18
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;
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;
19
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;
20
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;
21
set echo on
set pagesize 10000
set linesize 10000
drop table ticket;
drop table passenger;
drop table reservation;
drop table departures
drop table equip_type;
drop table pilots;
drop table flight;
drop table airport;
create table airport (air_code varchar2(3) primary key, air_location varchar2(20), air_elevation number,
air_phone varchar2(10), air_hub_airline varchar2(20));
insert into airport values (‘FLG’, ‘Flagstaff, AZ’, 6920, ‘6027741897’,”);
insert into airport values (‘PHX’, ‘Phoenix, AZ’, 1257, ‘6025831971’,’Air West’);
insert into airport values (‘MSP’, ‘Minneapolis, MN’, 862, ‘6123782910’,’Northwest’);
insert into airport values (‘LAX’, ‘Los Angeles, CA’, 37, ‘3102731846’,”);
insert into airport values (‘SFO’, ‘San Francisco, CA’, 78, ‘4158392371’,”);
insert into airport values (‘IAH’, ‘Houston, TX’, 5, ‘2816642000’,’Continental’);
insert into airport values (‘DFW’, ‘Dallas, TX’, 210, ‘2146218044’,’American’);
insert into airport values (‘JFK’, ‘New York, NY’, 43, ‘2106732145’, ‘United’);
insert into airport values (‘DTW’, ‘Detroit, MI’, 23, ‘4158219000’, ‘American’);
insert into airport values (‘OIA’, ‘Orlando, FL’, 20, ‘5183240912’, ‘TWA’);
insert into airport values (‘MIA’, ‘Miami, FL’, 16, ‘7175203400’, ‘Grand Cayman’);
create table flight (fl_flight_no number(6) primary key, fl_orig varchar2(3) constraint
flight_orig_fk references airport(air_code),
fl_dest varchar2(3) constraint flight_Dest_fk references airport(air_code),
fl_orig_time DATE, fl_dest_time DATE, fl_meal char(1) constraint flight_meal_cc check
(fl_meal =’B’ or fl_Meal=’L’ or fl_meal=’D’ or fl_Meal=’S’ or fl_meal=”), fl_fare number constraint
flight_fare_cc check(fl_fare >= 30 and fl_fare = -2 and fl_time_zones
