Consider an existing database with the following databasae definition in SQL: Ai

Question

Consider an existing database with the following databasae definition in SQL:

Airports(airportcode, city)

FlightCodes(fcode, AirlineName)

Flights(origin,destination,departuretime,arrivaltime,fcode)

AvailableFlights(fcode,date,numberOfFreeSeats,price)

AvailableFlights(reference,fcode,date,passenger,price)

Bookings

AvailableFlights is a table listing for each flight the number of available seats and the price per ticket.Bookings is a table listing the passengers that they have been booked for each flight, with the price they have paid and a unique booking reference number (an integer)

Create a view called Itenaries that lists booking references, passengers, flightcodes,date,departure and destination cities.  

Explanation / Answer

CREATE VIEW Itenaries

AS SELECT b.reference,b.passenger,b.fcode,b.date,f.departuretime,f.destination

FROM Bookings as b,Flights as f

where b.fcode=f.fcode;

(IF IT IS MEANT DEPARTURE CITY INSTEAD OF DEPARTURE TIME THEN f.departuretime WILL BE REPLACED BY f.origin)

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects