Question: #2: SQL (20 points) Use the Access database SQLAssignmentb.accdb to complete the following exercises using SQL (this file is linked to the Blackboard assignment). You

#2: SQL (20 points) Use the Access database SQLAssignmentb.accdb to complete the following exercises using SQL (this file is linked to the Blackboard assignment). You are required to submit the SQL you used in solving each of the exercises. To submit your SQL cut and paste the SQL from Access into Microsoft Word and submit to Blackboard. Create a table (Passenger) based on the specifications below: Field Name Type/Length PassID text 5 fixed (Primary Key) PassLName text 25 variable PassFName text 20 variable PassAge numeric Maint_DT date Add the 5 records below using the INSERT command. For the Maint_DT attribute use the following Access function Date(). PassID PassLName PassFName PassAge 00001 Morris Lucy 60 00002 Smith Trudy 51 00003 Collins Harry 22 00004 Dean Mark 37 00005 Hunter Alan 15 Select all passengers sorted from youngest to oldest. Select PassID, PassLName, and Maint_DT for all records. Complete numbers 5 9 using the Ticket table found in SQLAssignmentb.accdb. Select all ticket records listing the ticket number, passenger ID, and price for all flights arriving in PVD. Select all ticket records listing ticket number, passenger ID, and Departing Airport with a price of more than $350.00. Select all ticket records listing ticket number, passengerID, and Departing Airport with a price greater than $300.00 and an Arrival Airport of ORD. Create a derived attribute to determine a 30% discount for all flights to PVD (the calculation for the discount is price * .30). List the ticket number, passenger ID, Departing Airport, Depart Date, and Discount Price. Find the average price for all flights arriving in ATL. Use a JOIN to list ticket number, passenger last name, Departing Airport, depart date, from the passenger and ticket tables with an Arriving Airport of ORD in descending order by price

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related General Management Questions!