作业 - 试图计算两个城市之间的多个航班停靠点?

2021-12-30 00:00:00 sql oracle11g oracle

这是一个数据库类，使用 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. 飞行一站.
  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.

这个函数:select to_char(DATE,'HH24') 可能会有帮助.

This function: select to_char(DATE,'HH24') may help.

相关文章