Given the following relational database schema: FLIGHT = (FlightN, FromCity, ToCity, Date, DepartureTime, ArrivalTime ) //...

Transcribed Image Text:

Given the following relational database schema: FLIGHT = (FlightN, FromCity, ToCity, Date, DepartureTime, ArrivalTime ) // You may use <, >, !=, or= between any two dates or between any two times. Also, you may assume the attribute Date = arrival date= departure date and that ToCity and FromCity are in the same time zone. • TICKET = (TicketN, FlightN, Cost, Completed) // Since Ticket# and Flight# together are the key, this means a single ticket may include several flights. Completed may assume the values 'Yes' or NULL, Null means the flight hasn't been completed. • PASSENGER = (Name, TicketN) // This table includes every passenger and the ticket numbers he or she purchased. Assume the name of the passenger is unique. Express the following queries as SQL statements using a minimum number of tables and operations: Retrieve the names of every passenger and total amount paid for the tickets he/she purchased. SELECT P.Name, countT.cost) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name; SELECT P.Name, count(*) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name; SELECT P.Name, Sum(T.cost) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name; Given the following relational database schema: FLIGHT = (FlightN, FromCity, ToCity, Date, DepartureTime, ArrivalTime ) // You may use <, >, !=, or= between any two dates or between any two times. Also, you may assume the attribute Date = arrival date= departure date and that ToCity and FromCity are in the same time zone. • TICKET = (TicketN, FlightN, Cost, Completed) // Since Ticket# and Flight# together are the key, this means a single ticket may include several flights. Completed may assume the values 'Yes' or NULL, Null means the flight hasn't been completed. • PASSENGER = (Name, TicketN) // This table includes every passenger and the ticket numbers he or she purchased. Assume the name of the passenger is unique. Express the following queries as SQL statements using a minimum number of tables and operations: Retrieve the names of every passenger and total amount paid for the tickets he/she purchased. SELECT P.Name, countT.cost) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name; SELECT P.Name, count(*) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name; SELECT P.Name, Sum(T.cost) FROM PASSENGER P, TICKET T WHERE P.TicketN= T.TicketN GROUP BY P.Name;

Answer rating: 100% (QA)

The correct option is c SQL SELECT PName SUM T Cost AS TotalAmount Paid FROM PASSENGER P JOIN TICKET ... View the full answer