Consider the following database schema, where the keys are underlined:

(a) Use both the relational algebra and SQL to answer the following query:

Find all possible trips from LA to NYC, which consist of two connecting flights.

(Flights connect if flight 1 arrives at the airport from where flight 2 leaves and

the arrival time of flight 1 is less than the departure time of flight 2. You can

use < to compare the times.)

(b) Use relational algebra only to answer the following query:

Find the travel agents who issued a ticket for flight originating in LA.

(c) Use SQL for the following query:

Find the travel agents who sold more than 5 tickets.

(d) Use SQL (only) to

Find the travel agents who sold the most number of tickets (among all the

agents).

(a) SQL:




SELECT *


FROM Flight F1, Flight F2


WHERE F1.To = F2.From AND F1.ArrivalDateTime < F2.ArrivalDateTime


AND F1.From = 'LA' AND F2.To = 'NYC'

Algebra:

(b)

(c)




SELECT TravelAgent


FROM Ticket T


WHERE 5 < ( SELECT COUNT(*)


FROM Ticket T1


WHERE T1.TravelAgent = T.TravelAgent)

(d)




SELECT T.TravelAgent


FROM Ticket T


WHERE ( SELECT COUNT(*)


FROM Ticket T1


WHERE T1.TravelAgent = T.TravelAgent)


=


( SELECT Max(*)


FROM Ticket T2


GROUPBY T2.TravelAgent)