1.Start SQL*Plus 2.Write and execute SQL for the following queries. Save each su

Question

1.Start SQL*Plus

2.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)

3.For each flight with the aircraft capacity of at least 100 seats, list the flight number, the aircraft description, the departure airport and the arrival airport.

4.From the itinerary information, find the flight numbers and the dates for the flights that depart from Coventry.

5.List the names of the airports in alphabetical order which can accommodate landing aircraft of type “ATP”.

6.Find the prices of all tickets issued on 11/08/2004.

7.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).

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

Explanation / Answer

3. select FlightNo,ADescription,FromAirport,ToAirport from Aircraft a,Flight where f.AircraftType=a.AircraftType and a.NoSeats=100;

Above query retrieves the aircraft information having the seat capacity of 100.

4. select FlightNo,FlightDate from Itinerary i,Flight f,Airport a where i.FlightNo=f.FlightNo and f.FromAirport=a.Airport and a.AName='Coventry';

Above query retrieves the flight information that are starting from city coventry.

5. select a.AName from Airport a, Aircraft ac,Flight f where f.AircraftType=ac.AircraftType and f.ToAirport=a.Airport and ac.AircraftType='ATP' order by a.AName asc;

Above query gives the list of airport names in alphabetical order which can land the ATP type of crafts.

6. select TicketNo,t.Price from Ticket t,Itinerary i,Fares f,Tariff t where t.TicketNo=i.TicketNo and i.FareType=f.FareType and f.Faretype=t.FareType and to_char(TicketDate,'dd/mm/yyyy')='11/08/2004';

Above query retrieves the Ticket information for the given date.

As per chegg policy I am able to answer only 4 sub parts . Please understand and post the remaining as new post.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

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