作业-试图计算两个城市之间的多个航班停靠点? [英] homework - trying to calculate multiple flight stops between two cities?

问题描述

这是使用Oracle DB 11g的数据库类

This is for a database class, using Oracle DB 11g

Tables:

• Flight (flt_no, from_city, to_city, flt_distance, 
  flt_departs, flt_arrives, flt_price)

• Aircraft (craft_id, craft_name, cruising_range)

• Employee (emp_id, emp_name, emp_salary)

• Certified (emp_id, craft_id)

查询:一位客户希望从麦迪逊到纽约旅行时，航班的飞行次数不超过两次.如果客户想在下午6点之前到达纽约，请列出从麦迪逊出发的选择.

QUERY: A customer wants to travel from Madison to New York with no more than two changes of flight. List the choice of departures from Madison if the customer wants to arrive at New York by 6pm.

对此非常感谢.我真的不知道从哪里开始.

ANY Help is appreciate for this. I really don't even know where to start on this.

编辑 到目前为止，这是我想出的.请让我知道我是否至少在正确的轨道上.

EDIT This is what I've come up with so far. Please let me know if I'm on the right track at least please.

SELECT F.flt_no
  FROM Flight F
 WHERE F.from_city = 'Madison'
   AND F.to_city = 'New York'
   AND DATEPART(hh, F.flt_arrives) <= 18
 UNION
SELECT F.flt_no
  FROM Flight F
 WHERE (F.from_city = 'Madison'
        AND F.to_city IN (SELECT from_city
                            FROM Flight F
                           WHERE F.to_city = 'New York')
       )
    OR
       (F.to_city = 'New York'
        AND F.from_city IN (SELECT to_city
                              FROM Flight F
                             WHERE F.from_city = 'Madison')
        AND DATEPART(hh, F.flt_arrives) <= 18
       )

推荐答案

请考虑以下三种情况:

1. 直飞.
2. 飞行1站.
3. 有两个站点的航班.

现在开始逐步处理案件.

Now start stepping through the cases.

对于1.寻找开始和停止的航班，您知道在哪里，何时知道，对吧?

For 1. Look for flight that start and stop, you know where and you know when by, right?

对于2.考虑一下这两个航班.他们将在哪里开始和结束，这两个航班有何共同点.每次飞行的时间标准是什么?

For 2. Think about those two flights. Where will they start and end, what will be in common with the two flight. What's the time criteria for each flight?

对于3.请考虑这3个细分以及每个细分的特征，包括相关性

For 3. Think about those 3 segments and the characteristics of each, including dependencies

然后，如果要显示所有选择，您最终希望将所有这些都结合起来.

Then you finally want the union of all these if all choice are to be shown.

此功能:选择to_char(DATE,'HH24') may help.

这篇关于作业-试图计算两个城市之间的多个航班停靠点?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！