Consider the following relational database for Grand Travel Airlines.
Grand Travel Airlines has to keep track of its flight and airplane history. A flight is uniquely identified by the combination of a flight number and a date. Every passenger who has flown on Grand Travel has a unique passenger number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet. Each airplane has a unique serial number. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.

\begin{array} { | l | l | l | l | } \hline \text { PLOTNUM } & \text { PILOTNAME } & \text { BIRTHDATE } & \text { HIREDATE } \\\hline\end{array}

PILOT Relation

\begin{array} { | l | l | l | l | l | l|} \hline \text { FLIGHTNUM } & \text { DATE } & \text { DEPTIME } & \text { ARRTIME } & \text { PLINNUM } & \text { PLANENUM }\\\hline\end{array}

FLIGHT Relation

\begin{array} { | l | l | l | l | } \hline \text { PASSNUM } & \text { PASSNAME } & \text { ADDRESS } & \text { PHONE } \\\hline\end{array}

PASSENGER Relation

\begin{array} { | l | l | l | l | l | } \hline\text { FLIGHTNUM } & \text { DATE } & \text { PASSNUM } & \text { FARE } & \text { RESVDATE } \\\hline\end{array}

RESERVATION Relation

\begin{array} { | l | l | l | l | l | } \hline \text { PLANENUM } & \text { MODEL } & \text { CAPACITY } & \text { YEARBULL } & \text { MANUF }\\\hline\end{array}

AIRPLANE Relation
Write SQL SELECT commands to answer the following queries.
a. Find the records for the airplanes manufactured by Boeing.
b. How many reservations are there for flight 278 on February 21, 2004?
c. List the flights on March 7, 2004 that are scheduled to depart between 10 and 11AM or that are scheduled to arrive after 3PM on that date.
d. How many Boeing 737s does Grand Travel have?
e. How many of each model of Boeing aircraft does Grand Travel have?
f. List the names and dates of hire of the pilots who flew Airbus A320 aircraft in March, 2004.
g. List the names, addresses, and telephone numbers of the passengers who have reservations on Flight 562 on January 15, 2004.
h. What was the total fare paid for each flight scheduled to depart between 9 and 10AM on December 23, 2003? Only include those flights for which the total fare was at least $5,000.
i. List the smallest (in terms of passenger capacity) Boeing 737s.
j. List the Airbus A310s that are larger (in terms of passenger capacity) than the smallest Boeing 737s.

The SQL SELECT ORDER BY Operator ____

Multiple Choice

Correct Answer:VerifiedShow Answer

Correct Answer:
Verified