找出最便宜的单程机票。

Question

我正在做一个练习，给我一个问题，我必须通过使用给我的数据库编写一个SQL查询来回答它。

这就是问题所在：

从波士顿到巴尔的摩的单程航班最便宜的票价是多少？

我想出的是：

SELECT DISTINCT
    fare.one_direction_cost,
    fare.fare_id,
    flight.flight_id,
    flight.departure_time,
    flight.arrival_time,
    flight.airline_flight,
    flight.airline_code
FROM
    flight,
    fare
WHERE
    flight.flight_id IN (   
        SELECT DISTINCT flight_id
        FROM
            flight
        WHERE
            from_airport IN (
                SELECT
                    airport_code
                FROM 
                    airport_service
                WHERE
                    city_code = 'BBOS'
            ) AND
            to_airport IN (
                SELECT
                    airport_code
                FROM
                    airport_service
                WHERE
                    city_code = 'BBWI'
            )
    ) AND
    fare.round_trip_required = 'NO' AND
    fare.from_airport IN (
        SELECT
            airport_code
        FROM 
            airport_service
        WHERE
            city_code = 'BBOS'
    ) AND
    fare.to_airport IN (
        SELECT
            airport_code
        FROM
            airport_service
        WHERE
            city_code = 'BBWI'
    ) AND
    fare.one_direction_cost = (
        SELECT MIN(fare.one_direction_cost)
        FROM
            flight,
            fare
        WHERE
            flight.flight_id IN (   
                SELECT DISTINCT flight_id
                FROM
                    flight
                WHERE
                    from_airport IN (
                        SELECT
                            airport_code
                        FROM 
                            airport_service
                        WHERE
                            city_code = 'BBOS'
                    ) AND
                    to_airport IN (
                        SELECT
                            airport_code
                        FROM
                            airport_service
                        WHERE
                            city_code = 'BBWI'
                    )
            ) AND
            fare.round_trip_required = 'NO' AND
            fare.from_airport IN (
                SELECT
                    airport_code
                FROM 
                    airport_service
                WHERE
                    city_code = 'BBOS'
            ) AND
            fare.to_airport IN (
                SELECT
                    airport_code
                FROM
                    airport_service
                WHERE
                    city_code = 'BBWI'
            )
    )
;

我是SQL的初学者(这是我使用语言的第二天)，但我想出的东西对我来说实在是冗长而不必要的笨重，我不得不复制和粘贴好几次，我不喜欢。有没有人在一般情况下有什么建议来缩短它或者使它看起来更整洁呢？

我正在使用sqlite3，下面是我正在使用的相关表：

CREATE TABLE airport_service (
        city_code character(4) NOT NULL,
        airport_code character(3) NOT NULL,
        miles_distant numeric(4,1),
        direction character(3),
        minutes_distant numeric(3),
        PRIMARY KEY (city_code, airport_code)
        );

CREATE TABLE fare (
        fare_id character(8) NOT NULL,
        from_airport character(3) NOT NULL,
        to_airport character(3) NOT NULL,
        fare_basis_code character(3) NOT NULL,
        fare_airline character(2),
        restriction_code character(5),
        one_direction_cost numeric(7,2),
        round_trip_cost numeric(8,2),
        round_trip_required character(3),
        PRIMARY KEY (fare_id)
        );

CREATE TABLE flight (
        flight_id character(8) NOT NULL,
        flight_days character(12),
        from_airport character(3),
        to_airport character(3),
        departure_time numeric(4),
        arrival_time numeric(4),
        airline_flight character(20),
        airline_code character(2),
        flight_number numeric(4),
        aircraft_code_sequence character(11),
        meal_code character(4),
        stops numeric(1),
        connections numeric(1),
        dual_carrier character(3),
        time_elapsed numeric(4),
        PRIMARY KEY (flight_id)
        );

Answer 1

如果问题只是询问票价，您选择票价的第一行允许单程机票，其中从波士顿机场和到机场在巴尔的摩，按单程成本排序。

由于这是有标记的作业，我让您将其转换为SQL。:-)

Answer 2

你能告诉我这两个表- flight和fare是如何连接的吗？

每个flight都有自己的fare，它在从一个城市飞到另一个城市时收费。因此，实际上fare表的主键fare_id应该在flight表中充当外键。如果是的话，请告诉我。然后我可以给你提供正确的查询。

如果这不是必需的，那么您可以使用MIN()函数获得如下所示的查询：

SELECT MIN(one_direction_cost) 
  FROM fare 
  WHERE from_airport='Boston' 
  AND to_airport='Baltimore';

在这里，你需要提供波士顿和巴尔的摩的密码。因为我没有他们的密码，所以我已经把他们按原样写了。

如果有用的话请告诉我。