Oracle SQL 練習題：機場/航班/票務綜合查詢（第 8-10 題參考思路）

題目背景

給定 8 張表（機場、航班、排班、票價、旅客、機票、預訂），需完成 10 道查詢題。用戶僅請求最後三題（8、9、10）SQL，但完整邏輯鏈需前置視圖支持。

核心表結構回顧

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)

第 7 題：創建視圖 available_flights（前置依賴）

CREATE VIEW available_flights AS
SELECT
    f.flightno,
    sf.dep_date,
    f.src,
    f.dst,
    f.dep_time,
    -- 估算到達時間：出發時間 + 飛行時長，考慮目的地時區
    (sf.dep_date + f.dep_time + f.est_dur/24) AT TIME ZONE a2.tzone AS arr_time,
    ff.fare,
    (ff.limit - COUNT(b.tno)) AS seats, -- 剩餘座位
    ff.price
FROM flights f
JOIN sch_flights sf ON f.flightno = sf.flightno
JOIN flight_fares ff ON f.flightno = ff.flightno
JOIN airports a1 ON f.src = a1.acode
JOIN airports a2 ON f.dst = a2.acode
LEFT JOIN bookings b ON sf.flightno = b.flightno AND sf.dep_date = b.dep_date AND ff.fare = b.fare
GROUP BY f.flightno, sf.dep_date, f.src, f.dst, f.dep_time, f.est_dur, a2.tzone, ff.fare, ff.limit, ff.price
HAVING (ff.limit - COUNT(b.tno)) > 0; -- 僅有空座位的

第 8 題：YEG→YYZ 2015-12-22 最便宜直飛航班

SELECT flightno, fare, seats, price
FROM available_flights
WHERE src = 'YEG' AND dst = 'YYZ'
  AND dep_date = DATE '2015-12-22'
ORDER BY price ASC
FETCH FIRST 1 ROWS ONLY; -- Oracle 12c+ 語法，舊版用 ROWNUM

第 9 題：創建視圖 good_connections（中轉航班對）

CREATE VIEW good_connections AS
SELECT
    a1.src,
    a2.dst,
    a1.dep_date,
    a1.flightno AS flightno1,
    a2.flightno AS flightno2,
    (a2.dep_time - (a1.dep_time + a1.est_dur/24)) * 24 * 60 AS layover, -- 分鐘
    (a1.price + a2.price) AS price
FROM available_flights a1
JOIN available_flights a2
  ON a1.dst = a2.src                 -- 到達=下一程出發
  AND a1.dep_date = a2.dep_date      -- 同一天
  AND a2.dep_time >= (a1.dep_time + a1.est_dur/24) + 90/1440  -- 至少 90 分
  AND a2.dep_time <= (a1.dep_time + a1.est_dur/24) + 300/1440 -- 最多 5 小時
WHERE a1.src != a2.dst; -- 非同城往返

第 10 題：YEG→LAX 2015-12-22 前 5 最便宜（直飛+一轉）

-- 直飛
SELECT flightno AS f1, NULL AS f2, 0 AS layover, price
FROM available_flights
WHERE src = 'YEG' AND dst = 'LAX' AND dep_date = DATE '2015-12-22'

UNION ALL

-- 一轉
SELECT flightno1 AS f1, flightno2 AS f2, layover, price
FROM good_connections
WHERE src = 'YEG' AND dst = 'LAX' AND dep_date = DATE '2015-12-22'

ORDER BY price ASC
FETCH FIRST 5 ROWS ONLY;

關鍵技術點總結

聲明

以上 SQL 為**邏輯參考**，未在實際數據庫驗證，具體列名、類型、時區處理需根據實際 schema 調整。建議先跑通第 7 題視圖，再逐層構建。