UC知道几个SELECT语句题目 请教大家
来源：百度知道 编辑：UC知道 时间：2024/05/24 05:02:41
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)
foreign keys和primary keys的关系对应如图
题目是:
1.找出座位数在100以上的航班(Aircraft, Noseats), 请列出其航班号(Flight, FlightNo), 飞机型号描述(Aircraft, Adescription), 起飞机场(Flight, FromAirport)及目的地机场(Flight, ToAirport)
2.从行程信息(Itinerary)中找出目的地机场(Flight,ToAirport)为COV的航班, 列出其航班号(Itinerary, FlightNo),起飞日期(Itinerary, FlightDate)
3.按照字母顺序排列, 找出可以起降ATP(Aircraft, AircraftType)型号飞机的机场(Airport)
4.列出2004年8月11日(11/8/2004 FlightDate)所有机票的票价(Ticket)
5.列出在凌晨1点(01:00 am Flight, DepT
1.找出座位数在100以上的航班(Aircraft, Noseats), 请列出其航班号(Flight, FlightNo), 飞机型号描述(Aircraft, Adescription), 起飞机场(Flight, FromAirport)及目的地机场(Flight,ToAirport)
答：select flightono,aircrafttype,fromairport,toairport from flight where AircraftType in (select aircrafttype from aircraft where noseats>100)
2.从行程信息(Itinerary)中找出目的地机场(Flight,ToAirport)为COV的航班, 列出其航班号(Itinerary, FlightNo),起飞日期(Itinerary, FlightDate)
答：select flightno,flightdate from itinerary where flightno in (select flightno from Flight where toairport='COV')
3.按照字母顺序排列, 找出可以起降ATP(Aircraft, AircraftType)型号飞机的机场(Airport)
答：select airport from airport where airport in (select fromairport from flight where fromairport='ATP') or airport in (select toairport from flight where toairport='ATP') order by airport asc
4.列出2004年8月11日(11/8/2004 FlightDate)所有机票的票价(Ticket)
答：select price from tariff where faretype in (select faretype from itinerary where flightdate bet