# 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
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'
)
)
;


I'm a beginner at SQL (this is my second day working with the language), but what I came up with feels really verbose and unnecessarily bulky to me, and I've had to copy-and-paste several times, which I dislike. Does anybody have any tips in general for shortening it or making it look neater in general?

I'm working with sqlite3, and here are the relevant tables that I'm working with:

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)
);

• I assume you've never heard of a join? – ChaosPandion Jul 10 '12 at 21:39
• A small tip: If you have to use DISTINCT you are probably doing it wrong. Not always, but in most cases. – Ariel Jul 10 '12 at 21:39
• @ChaosPandion -- not really, no. I'm googling it right now, but I'm not sure if I'm really getting how to use it. – Michael0x2a Jul 10 '12 at 21:50
• The question seems to only ask for a fare; why are you involving flights at all? And what is the value for round_trip_required that would mean false? Also, I don't see a way to connect a flight to a fare besides the from_airport and to_airport. – Mr. Jefferson Jul 10 '12 at 21:51
• @Mr. Jefferson: Good point -- the answer returned related data about flights, so I thought I'd do the same. I also don't see an easy way to connect a flight to a fare (although I'll check again). – Michael0x2a Jul 10 '12 at 21:58

## 2 Answers

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. :-)

Can you please let me know how the 2 tables - flight and fare are connected?

Every flight has its fare which it charges while flying from one city to another. So, actually the primary key of fare table i.e. fare_id should act as a foreign key in flight table. If it is, let me know. Then I can provide you the right query.

If that is not required then you can use the MIN() function to get the query as below:

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


Here, you need to provide the codes for Boston and Baltimore. I have put them as it is as I don't have their codes.

Let me know if it works.