airports(acode, name, city, country, tzone);
flights(flightno, src, dst, dep_time, est_dur);
sch_flights(flightno, dep_date, act_dep_time, act_arr_time);
fares(fare, descr);
flight_fares(flightno, fare, limit, price, bag_allow);
passengers(email, name, country);
tickets(tno, email, paid_price);
bookings(tno, flightno, fare, dep_date, seat);
以上是表的信息，以下是问题，我只需要最后三道题的SQL语句。
The tables are derived from the Assignment 1 spec and the names of the tables and columns should give the semantics, except minor differences which are explicit in table definitions, insert statements or queries.
Creating the database
Using the SQL statements provided, create the above tables in Oracle under your user name and populate them with some data. Here is a small initial data to get you started.
(90 marks) Queries
Write down the following queries in SQL and run them in SQL*PLUS (or SQL developer) over the database created. You will be writing ONE SQL statement for every query. Your SQL queries for questions 1-2 cannot use any of aggregation, grouping, or nesting.
1、Find the email address of passengers who have had a flight booked to destination airport YEG.
2、Find the name and email address of passengers who have a flight booked out of Edmonton in December 2015. Hint: Check out the date functions in Oracle.
3、Find flights that are scheduled for a departure within the next 30 days but no one has booked those flights. For every such flight, list the flightno and the dep_date. Hint: Again check out the date functions in Oracle.
4、Find the airports with the largest number of scheduled arrivals. For each such airport, report the airport code and the number of scheduled arrivals.
5、Find top 3 airports with the largest number of scheduled arrivals and departures. For each such airport, report the airport code and the total number of scheduled arrivals and departures. Hint: You may find rank or row_number functions in Oracle useful.
6、Find the cheapest fare from Edmonton to Los Angeles. For the cheapest fare, list the flight number(s), the departure time, the fare type and the price.
7、Create a view called available_flights with the following attributes: flightno, dep_date, src, dst, dep_time, arr_time, fare, seats, price. The view includes all flights between src and dst airports that have a seat available; for every such flight, the view has the flight details including the flight number, the departure date, the source and destination airports, the departure time, the estimated arrival time, the number of seats available under each fare type and the seat price. A fare type is available on a flight if the number of bookings for that fare type is less than the allocated limit. The estimated arrival time is the arrival time at the destination (i.e. it is based on the destination time zone). Treat dep_date, dep_time, arr_time in the view as date types, and don't convert them to char (the conversion can happen inside queries that may use the view). Hint: you may find outer join useful here.
8、Using the view available_flights, find the cheapest direct flights from YEG to YYZ scheduled on December 22, 2015 with an available seat. For each such flight, list the flight number, the fare type, the number of available seats and the seat price.
9、Create a view called good_connections with columns src, dst, dep_date, flightno1, flightno2, layover, and price. The view includes all pairs of flights flightno1 and flightno2 between airports src and dst such that the arriving airport for flightno1 and the departing airport for flightno2 are the same, the connection time between the two flights is at least 90min and at most 5 hours. For each such pair of flights, the view includes the flight numbers, the airport codes for src and dst, the dep_date of the first flight, the layover time and the sum of the price. Assume the view available_flights is available and can be used here.
10、Find top 5 cheapest flights from YEG to LAX scheduled for a departure on Dec 22, 2015, with at most one connection (i.e. at most two flights) and some available seats. For each such flight(s), list the first flightno, the second flightno (if applicable), layover time (if applicable) and the price. Assume the views available_flights and good_connections are both available and can be used here.
以上是所有问题，我只需要最后三个问题的SQL代码，谢谢！！
最后三个问题即问题8、9、10
