SQL assignment question 1
Problem #5
List each airline carrier with a count of the number of flights. If the airline hasn’t flown any flights, show a 0 for its count. Sort the results so the highest number of flights is on the top. Make sure you show all the carriers on here.
Put your SQL in the appropriate location in the SQL template.
Paste your screen shot(s) showing your results below.
A successful result could have the following format (obviously, your data will reflect that in the airlines database.):
|
Carrier Name |
Number of Flights |
|
Flappy Bird Airlines |
76 |
|
Moon Shot Rockets |
25 |
|
Zappo’s Crop Dusting |
0 |
Problem #6
|
Count the number of flights flown by each plane type (manufacturer and model together in a single column); sort your results from most to fewest. | |
|
Put your SQL in the appropriate location in the SQL template. | |
|
Include in your results a new field (ours below is called “Plane Type;” you may name yours whatever you wish.) The new field concatenates the aircraft manufacturer and the aircraft model number into a single field with a dash between them. For example, if the manufacturer is “Boeing,” and the model number is “787 Dreamliner,” the new field would be “Boeing – 787 Dreamliner” . | |
|
If a particular plane type has 0 flights, you may or may not display it, as you like. | |
|
Paste your screen shot(s) showing your results below. | |
|
A successful result could have the following format (obviously, your data will reflect that in the airlines database.): | |
|
Plane Type |
Number of Flights |
|
Boring – Rocketship-123 |
99 |
|
AirScooter – Putt Putt XJ 314 |
25 |
|
Wright Bros – Canvas Delite 987 |
3 |
Problem #8
|
List the airport code of origin and airport code of destination, the departure and arrival time, the airline name, and plane manufacturer and model for all flights that will occur in the future. (The manufacturer and model may be in two separate fields; they do not need to be concatenated.) |
|
Put your SQL in the appropriate location in the SQL template. |
|
This query should be written so that whenever it is run, it will *always* return only those flights which will occur in the future. Sort your results so that the flights that will occur soonest in the future will be at the top, and the flights furthest out in the future will be at the bottom. |
|
Paste your SQL and your screen shot showing your results below. Your SQL should contain suitable comments. You should also include some documentation on your screen shot of when you ran the assignment, so we can see when the computer thought “today” was. |
Problem #9
|
Your boss tells you that you will need to replace the seat coverings on all seats on all aircraft (from all carriers) which were purchased between Jan 1, 2010 and Dec 31, 2015. The fabric initially used in the manufacture of those seat coverings has issues with its fire retardant properties. Provide an SQL query which lists the Plane Type, and the total number of seats for all of the aircraft which meet the criteria. Using the results of your query, how many seat coverings will you need to order? (You can answer this question by manually adding up your SQL results; for example, the query below would imply you needed to order 50 + 30 + 2 = 82 seat covers.) | |
|
A successful query will: | |
|
Concatenate your aircraft model number and manufacturer to one column | |
|
Present the total number of seats in decimal format, to two decimal places (for compatability with legacy systems). | |
|
List the aircraft with highest seat count first. | |
|
A successful result could have the following format (obviously, your data will reflect that in the airlines database.): | |
|
Plane Type |
Number of seats |
|
Boring – Rocketship-123 |
50.00 |
|
AirScooter – Putt Putt XJ 314 |
30.00 |
|
Wright Bros – Canvas Delite 987 |
2.00 |
