Finding the cheapest one-way air flight fare

I'm doing a sort of exercise where I'm given a question, and I have to answer it by writing an SQL query using a database that I was given.

This is the question:


What is the cheapest fare for a one way flight from Boston to Baltimore?

Here's what I came up with:

```
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

If the question is only asking for fares, you select the first result row of the fares allowing one-way tickets where the from airport is in Boston and the to airport is in Baltimore, sorted ascending by one way cost.

Since this is tagged homework, I'll let you translate that to SQL. :-)

StackExchange Code Review Q#13520, answer score: 3