Start SQL*Plus, Write and execute SQL for the following queries. Save each succe

Question

Start SQL*Plus, Write and execute SQL for the following queries. Save each successful commands in file. Note that the commands use the following tables:

Airport(Airport, AName, CheckIN, Resvtns, FlightInfo)

Route(RouteNo, RDescription)

Fares(FareType, Fdescription, Conditions)

Tariff(RouteNo , FareType, Price)

Aircraft(AircraftType, ADescription, NoSeats)

Flight(FlightNo, FromAirport, ToAirport, DepTime, ArrTime, Service, AircraftType,                                                                                                               RouteNo)

Passenger(Pid, Name, Address, TelNo)

Ticket(TicketNo, TicketDate, PID)

Itinerary(TicketNo, FlightNo, LegNo, FlightDate, FareType)

q1?List the code of those airports that do not have any flights with scheduled departure time between 1:00 am (0100) and 7:30am (0730).

q2?List the names of the passengers with at least two tickets which cost over £180 each.

Explanation / Answer

If you have any doubts, please give me comment...

-- q1?List the code of those airports that do not have any flights with scheduled departure time between 1:00 am (0100) and 7:30am (0730).

SELECT Airport, AName

FROM Airport

WHERE Airport NOT IN(

SELECT FromAirport

FROM Flight

WHERE DepTime BETWEEN 0100 AND 0730;

);

-- q2?List the names of the passengers with at least two tickets which cost over £180 each.

SELECT Pid, PName

FROM Passenger P, Ticket T, Itinerary I, Flight F, Tariff TF

WHERE P.Pid = T.PID AND T.TicketNo = I.TicketNo AND F.FlightNo = I.FlightNo AND F.RouteNo = TF.RouteNo AND I.FareType = TF.FareType AND TF.Price>=180

GROUP BY Pid, Price

HAVING COUNT(*)>2;

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.