问题描述
- 求三个很简单的 Oracle 语句（只是因为英文题目，看着有点晕而已）
-
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
解决方案
翻译可以给你，但SQL就帮不了哈哈。我跪~
解决方案二：
百度翻译可以帮到你 出门右拐就到了
解决方案三：
8、9、10需要用到视图available_flights，这个视图是7里创建的，如果创建了这个视图，你直接从这个视图里取数据就行了
8的条件是find the cheapest direct flights from YEG to YYZ scheduled on December 22, 2015 with an available seat.
解决方案四：
该表是来自分配1规范和表和列的名称应给予的语义，除了小的差异，这是明确的表的定义，插入语句或查询。
创建数据库
使用提供的SQL语句，创建Oracle上表你的用户名下，一些数据填充它们。这里是一个小的初始数据，让你开始。
（90标记）查询
写出下列查询SQL运行在SQL * Plus（或SQL Developer）在数据库中创建。你会写一个SQL语句为每个查询。你的SQL查询1-2题不能使用任何聚集，分组，或筑巢。
1、发现谁有一个航班的乘客到目的地机场诞生的电子邮件地址。
2、在十二月2015的航班上，找到乘客的姓名和电子邮件地址。提示：查看甲骨文中的日期函数。
3，找到在未来30天内的航班，但没有人已经预订了这些航班。对于每一次这样的飞行，flightno和dep_date列表。提示：再次检查甲骨文中的日期函数。
4、找到最大数量的机场。对于每一个这样的机场，报告机场的代码和预定人数。
5、找到最多的3个机场，数量最多的预定人数和出发点。对于每一个这样的机场，报告机场的代码和总人数的预定人数和离开。提示：你会发现等级或row_number Oracle中的有用的功能。
6，找到最便宜的票价从埃德蒙顿到Los。为最便宜的票价，列出航班号，出发时间，票价类型和价格。
7、创建一个视图称为available_flights具有以下属性：flightno，dep_date，SRC，DST，dep_time，arr_time，票价，座位，价格。视图包含所有航班SRC和DST机场之间，有一个座位；对于每一次这样的飞行，观有航班信息包括航班号，出发日期，来源和目的地机场，出发时间，预计到达时间，座位每张票价类型下的座位数量和价格。如果票价类型的预订数量低于所分配的限额，则可在航班上提供一种票价类型。估计到达时间是到达目的地的到达时间（即它是基于目标时区）。治疗dep_date，dep_time，视图中的arr_time日期类型，不要将它们转换为char（转换可以发生在查询，可以使用视图）。提示：你可能会发现外部连接有用的。
8、使用视图available_flights，找到最便宜的直飞航班从诞生到多伦多定于2015年12月22日与座位。对于每一个这样的飞行，列出航班号，票价类型，可用的座位数量和座位价格。
9、创建一个视图称为DST，SRC柱，dep_date，flightno1，flightno2，停留good_connections，和价格。视图包含所有对机场SRC和DST，到达机场flightno1和离开机场之间的航班flightno2是相同的flightno1和flightno2，两个航班之间的连接时间至少90min和最多5小时。对于航班的每一对，视图包括航班号，SRC和DST的机场代码，第一次飞行的dep_date，的时间和价格的总和。承担的观点available_flights可用，可以用在这里。
10、找到最便宜的机票从5强诞生松懈定于2015年12月22日出发，至多一个连接（即最多两航班）和一些座位。对于每一次这样的飞行（S），列表的第一flightno，第二flightno（如果适用），停留时间（如果适用）和价格。假设的观点available_flights和good_connections都可用，可以用在这里。