use the SQL92 format (ie: JOINS) to answer all questions.

• You MUST use the SQL92 format (ie: JOINS) to answer all questions.
• NO sub queries
• NO regular expressions (ie: RegEx)
• Your SQL should be written using the standard coding style:
• all keywords are to be upper case
• all user-defined names are to be lower case unless required to be capitalised (example: table and field names)
• there should be a carriage return before each major part of the SQL statements (i.e. before SELECT, FROM, WHERE and ORDER BY)

1. List all the planes that have modified their seating capacity. Column headings should be Plane Type, Maximum Seats, Modified Seating, and Difference.

The Difference column is the difference between the maximum seating and the modified seating.

Only show planes that have modified their seating and set the order in descending order by the Difference column.

 

2. List all airplane companies and the types of planes they have.

Order by company name in ascending order and then airplane type in ascending order.

Only show the company name and plane type.

 

3. List all airports that have no scheduled departure flights.

Show the airport code and the airport name with column headings of "Code" and "Name"

Sort by airport code in descending order.

 

4. List any planes that have not been designated to land at any airport.

Just show the name of the airplane type and the heading should be "Type of Plane"

 

5. I would like the seating and actual capacity of each leg instance of each flight.

The columns should be

"Flight Number", "Leg", "Date", "Full Seating", "Occupied Seats", and "Actual Flight Capacity %"

Round the "Actual Flight Capacity %" to 2 decimal places.

Use the airplanesetas column from the mid_airplane table to get the full seating number.

The occupied seats would be the difference between the "Full Seating" and the leginstanceavailablesetas

The "Actual Flight Capacity %" would be the percentage of seats actually occupied against the "Full Seating". For instance if you had a plane that had 90 seats with 30 seats occupied then you would be at 33.33% capacity.

You will see that some flights are over booked - they will have a negative "Actual Flight Capacity %".

6. List the types of plane each airline has. 

Only show the airline name and the plane type. Column headings should be "Airline" and "Type of Plane".

Order by the airline name in ascending order and then the plane type in ascending order.

Remove duplicates.

Use the least number of JOINS possible.

Answer rating: 100% (QA)

1 ... View the full answer