3
\$\begingroup\$

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)
        );
\$\endgroup\$
  • 1
    \$\begingroup\$ I assume you've never heard of a join? \$\endgroup\$ – ChaosPandion Jul 10 '12 at 21:39
  • 4
    \$\begingroup\$ A small tip: If you have to use DISTINCT you are probably doing it wrong. Not always, but in most cases. \$\endgroup\$ – Ariel Jul 10 '12 at 21:39
  • \$\begingroup\$ @ChaosPandion -- not really, no. I'm googling it right now, but I'm not sure if I'm really getting how to use it. \$\endgroup\$ – Michael0x2a Jul 10 '12 at 21:50
  • \$\begingroup\$ 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. \$\endgroup\$ – Mr. Jefferson Jul 10 '12 at 21:51
  • \$\begingroup\$ @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). \$\endgroup\$ – Michael0x2a Jul 10 '12 at 21:58
3
\$\begingroup\$

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

\$\endgroup\$
0
\$\begingroup\$

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.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.