2 SQL Project: 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...

Use Oracle SQL to do the attached assignment


2 SQL Project: 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. 2. 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 Houston or Phoenix. 3 3. Using an “IN”, list pilot names, zip 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 seat capacity less than 280, or aircraft that have a miles per gallon greater than 4.0 miles per gallon and fuel capacity less than 2000. 5. Using PATTERN MATCHING (the LIKE operation), select all information for airports in Los Angeles. 4 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 descending. Make sure to name the attributes as shown in the example output. You can learn round () function by yourself and try to apply it here so your “Miles Flown Per Dollar” column is round to two decimal places. However, you won’t lose points if you don’t use round function. 5 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 name and departure date of his first flight. Order by pilot name. Hint: you will need pilots and departures tables. 6 11. 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. 12. List the number of flights originating from each airport as NUMBER_OF_FLIGHTS. Hint: you will need to use count function. 7 13. 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 originate from an airport that is a hub for American Airlines. 14. Display the flight number, departure date and equipment type for all equipment that is manufactured by Concorde. Order by departure date and flight number. Need to use “like” keyword in your query. 8 15. Using a SUB QUERY, display the IDs and names of pilots who are not currently scheduled for a departure. Hint: you will use “not in” keyword. 16. Using “IS NULL” and an OUTER JOIN, display the IDs and names of pilots who are not currently scheduled for a departure. 17. Display passenger name and seat number, as "Seat Number", for flight 101, departing on July 15, 2017 order by “Seat Number” 9 18. List flight number, departure date and number of passengers as "Number of Passengers" for departures that have more than 5 passengers. 19. Select flight number, origination and destination for all reservations booked by Andy Anderson, Order results by flight number. 10 20. 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.
May 05, 2021
SOLUTION.PDF

Get Answer To This Question

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here