Provide the SQL select statements for the following queries.

a$.$ The airline is being audited for tax evasion. Retrieve the total cost of aircraft services for

aircraft A$0001$ in the financial year $1$st July $2017$ to $30$th June $2018.[5$ marks$]$

b$.$ List the model ID$,$ aircraft ID$,$ total number of seats, destination country and actual flight

duration $($in hours$)$ of every flight to have departed from NEX airport $($excluding flights

that have not arrived at their destinations yet$).[5$ marks$]$

c$.$ Print the model ID$,$ length, wingspan, destination country and actual flight duration $($in

hours$)$ of the longest $($actual duration$)$ flight to ever depart from NEX airport. $[5$ marks$]$

d$.$ For each pilot, give the total amount of flying experience so far in hours $($including

experience prior to joining NEA and experience with NEA as a captain, first officer, or

additional pilot$).$ Hint: You may want to use a correlated nested query. $[5$ marks$]$

e$.$ The analytics team want to investigate the amount of pilot experience on board flights.

Produce a table with the flightID and estimated departure date of each flight, along with

the staff IDs of the captain and first officer on board, their respective previous hours of

flying experience prior to joining NEA, and each of their total hours of experience with

new endor airlines before that flight $($so the table will have a total of $8$ columns$).$ Order

the result by estimated departure date. Hint: It may be easier to complete Question d first.

$[5$ marks$]$

f$.$ The management team wish to determine if an appropriate number of hosts have been

assigned to each flight. For each departed flight display the flight number, number of

tickets sold, and number of hosts assigned. $[5$ marks$]$

g$.$ List the model ID$,$ aircraft ID$,$ haul type and number of seats for each aircraft whose model

has amongst the top $2$ greatest number of seats of all models owned by NEA. $[5$ marks$]$

h$.$ Display a table of all customer departures and arrivals from the airport with airport code

'NED'. For each row display the flight ID$,$ customer ID$,$ customer name, and the details of

the preferred contact method. The preferred method is the customer's phone number for

departures and email for arrivals. The displayed table should have exactly $4$ columns. Order

the result by flight number $($first$)$ and customer ID $($second$).[5$ marks$]$

i$.$ Display a table of all customers who have purchased tickets for at least $5$ flights, excluding

any flights that have experienced irregular events. Display the customer ID$,$ number of

tickets purchased, and any description of medical conditions that the customer claimed on

their tickets. If a customer described different medical conditions on different tickets, then

display only the description that comes first alphanumerically $($e$.$g$.$ 'acrophobia' comes

before 'hemophilia'$).[5$ marks$]$

CSE$4$DBF $(2024)$ Assessment $4$ T$3$ Page $2/3$

$\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_\_$

j$.$ For each flight that has already departed, display the flight ID$,$ actual departure date,

actual departure time $($in a separate column$),$ and the number of days that have passed

between now $($system date$)$ and the departure. The displayed table should have $4$ columns.

Display the date in the same format as $\text{'}01$st January $2019\text{'},$ and display the time in $12$ hour

format ending in $\text{'}$am$\text{'}$ or $\text{'}$pm$\text{'}$ with hours and minutes shown $($but not seconds$).[5$ marks$]$