几个SELECT语句的题目 求助.. 完整的最佳答案160分

Question

建立的Table的属性如下
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, DepTime)至凌晨5点30分(05:30 am Flight, DepTime)之间, 没有任何飞机起飞的机场(Airport)

6.列出所有买了至少两张机票(Ticket 每两张票180英镑,单位符号为 ￡ )的乘客(Passenger)

下面是具体数据 有些不对齐 谅解下
AIRPORT:
AIRPORT ANAME CHECKIN RESVTNS FLIGHTINFO
AMST Amsterdam South Hall Departures 20 mins before flight 06 022 2426 20 178299
BELF Belfast Desks 18 and 19,15 mins before flight 023 2325151 08494 22888
BRUS Brussels Sabena Check-in desks 20 mins before flight 2 511 9030 2 7207167
COV Coventry check-in desks 30 mins before flight 024 768 123
EMID East Midlands Check-in 15 mins before flight for heavy bags 0332 810552 0332 852614
DUBL Dublin Check-in 20 mins prior to departure 01 423 555
EDIN Edinburgh Gate 1 at least 10 mins before departure 031 447 1000
GLAS Glasgow Desks 60-64/Gate 8 20 mins before departure 041 204 2436
HROW Heathrow Island A/B Terminal-1 20 mins before flight 081 5895599 081 7457321
LBDR Leeds/Bradford Check-in 15 mins before flight for baggage 0532 451991
LVPL Liverpool 15 mins heavy baggages,10 mins hand baggage 051 494 0200
PARI Paris Hall 22 Terminal 1 30 mins before flight 14742 14444 14862 2280
TEES Teeside 15 mins heavy baggages,10 mins hand baggage 0642 219444
ROUTE:
ROUTENO RDESCRIPTION
3 Heathrow-Belfast
4 Heathrow-Edinburgh

FARES:
FARETYPE FDESCRIPTION CONDITIONS
BUR Business Return Business use only
SDS Standard Single

TARIFF:
ROUTENO FARETYPE PRICE
3 BUR 117
3 SDR 158

FLIGHT:
FLIGHTNO FROMAIRPORT TOAIRPORT DEPTIME ARRTIME SERVICE AIRCRAFTTYPE ROUTENO
BD80 HROW BELF 0725 0840 Breakfast 737 3
BD82 HROW BELF 0930 1045 Breakfast 737 3

AIRCRAFT:
AIRCRAFTTYPE ADESCRIPTION NOSEATS
ATP Advanced Turbo Prop 48
DC9 McDonnel Douglas Jet 120

PASSENGER:
PID NAME ADDRESS Tel No
26 J Millar Englewood Cliffs 061 343 881
28 J D Ullman 1 Microsoft Way

TICKET:
TICKETNO TICKETDATE PID
100001 01/08/04 26
100002 25/07/04 28

ITINERARY:
TICKETNO FLIGHTNO LEGNO FLIGHTDATE FARETYPE
100001 BD80 1 05/08/04 BUR
100001 BD95 2 05/08/04 BUR

Answer 1

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 between'10/8/2004'and'12/8/2004')

5.列出在凌晨1点(01:00 am Flight, DepTime)至凌晨5点30分(05:30 am Flight, DepTime)之间, 没有任何飞机起飞的机场(Airport)
答：select airport from airport where airport in (select fromairport from flight where left(right(convert(char,deptime,9),7),2))='am' and convert(char,deptime,8)>'01:00' and convert(char,deptime,8)<'05:30')

6.列出所有买了至少两张机票(Ticket 每两张票180英镑,单位符号为 ￡ )的乘客(Passenger)
答：select name from passenger where pid in (select p from (select pid as p,count(*) as c from ticket group by pid having count(*) >1) as tic)

Answer 2

多啊 才160？不答