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...

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

Submit New Assignment

Copy and Paste Your Assignment Here