CREATE TABLE city (city_code VARCHAR, city_name VARCHAR, state_code VARCHAR, country_name VARCHAR, time_zone_code VARCHAR)
CREATE TABLE month (month_number INT, month_name TEXT)
CREATE TABLE equipment_sequence (aircraft_code_sequence VARCHAR, aircraft_code VARCHAR)
CREATE TABLE compartment_class (compartment VARCHAR, class_type VARCHAR)
CREATE TABLE state (state_code TEXT, state_name TEXT, country_name TEXT)
CREATE TABLE date_day (month_number INT, day_number INT, year INT, day_name VARCHAR)
CREATE TABLE aircraft (aircraft_code VARCHAR, aircraft_description VARCHAR, manufacturer VARCHAR, basic_type VARCHAR, engines INT, propulsion VARCHAR, wide_body VARCHAR, wing_span INT, length INT, weight INT, capacity INT, pay_load INT, cruising_speed INT, range_miles INT, pressurized VARCHAR)
CREATE TABLE airline (airline_code VARCHAR, airline_name TEXT, note TEXT)
CREATE TABLE fare (fare_id INT, from_airport VARCHAR, to_airport VARCHAR, fare_basis_code TEXT, fare_airline TEXT, restriction_code TEXT, one_direction_cost INT, round_trip_cost INT, round_trip_required VARCHAR)
CREATE TABLE flight_fare (flight_id INT, fare_id INT)
CREATE TABLE code_description (code VARCHAR, description TEXT)
CREATE TABLE flight_stop (flight_id INT, stop_number INT, stop_days TEXT, stop_airport TEXT, arrival_time INT, arrival_airline TEXT, arrival_flight_number INT, departure_time INT, departure_airline TEXT, departure_flight_number INT, stop_time INT)
CREATE TABLE dual_carrier (main_airline VARCHAR, low_flight_number INT, high_flight_number INT, dual_airline VARCHAR, service_name TEXT)
CREATE TABLE days (days_code VARCHAR, day_name VARCHAR)
CREATE TABLE class_of_service (booking_class VARCHAR, rank INT, class_description TEXT)
CREATE TABLE airport (airport_code VARCHAR, airport_name TEXT, airport_location TEXT, state_code VARCHAR, country_name VARCHAR, time_zone_code VARCHAR, minimum_connect_time INT)
CREATE TABLE restriction (restriction_code TEXT, advance_purchase INT, stopovers TEXT, saturday_stay_required TEXT, minimum_stay INT, maximum_stay INT, application TEXT, no_discounts TEXT)
CREATE TABLE flight_leg (flight_id INT, leg_number INT, leg_flight INT)
CREATE TABLE time_zone (time_zone_code TEXT, time_zone_name TEXT, hours_from_gmt INT)
CREATE TABLE fare_basis (fare_basis_code TEXT, booking_class TEXT, class_type TEXT, premium TEXT, economy TEXT, discounted TEXT, night TEXT, season TEXT, basis_days TEXT)
CREATE TABLE airport_service (city_code VARCHAR, airport_code VARCHAR, miles_distant INT, direction VARCHAR, minutes_distant INT)
CREATE TABLE ground_service (city_code TEXT, airport_code TEXT, transport_type TEXT, ground_fare INT)
CREATE TABLE food_service (meal_code TEXT, meal_number INT, compartment TEXT, meal_description VARCHAR)
CREATE TABLE time_interval (period TEXT, begin_time INT, end_time INT)
CREATE TABLE flight (aircraft_code_sequence TEXT, airline_code VARCHAR, airline_flight TEXT, arrival_time INT, connections INT, departure_time INT, dual_carrier TEXT, flight_days TEXT, flight_id INT, flight_number INT, from_airport VARCHAR, meal_code TEXT, stops INT, time_elapsed INT, to_airport VARCHAR)

cheapest one way fare from INDIANAPOLIS to ORLANDO on 12 27
SELECT DISTINCT fare_id FROM fare WHERE ((fare_basis_code IN (SELECT FARE_BASISalias1.fare_basis_code FROM fare_basis AS FARE_BASISalias1 WHERE FARE_BASISalias1.basis_days IN (SELECT DAYSalias2.days_code FROM days AS DAYSalias2 WHERE DAYSalias2.day_name IN (SELECT DATE_DAYalias2.day_name FROM date_day AS DATE_DAYalias2 WHERE DATE_DAYalias2.day_number = 27 AND DATE_DAYalias2.month_number = 12 AND DATE_DAYalias2.year = 1991))) AND fare_id IN (SELECT FLIGHT_FAREalias1.fare_id FROM flight_fare AS FLIGHT_FAREalias1 WHERE FLIGHT_FAREalias1.flight_id IN (SELECT FLIGHTalias1.flight_id FROM flight AS FLIGHTalias1 WHERE ((FLIGHTalias1.flight_days IN (SELECT DAYSalias3.days_code FROM days AS DAYSalias3 WHERE DAYSalias3.day_name IN (SELECT DATE_DAYalias3.day_name FROM date_day AS DATE_DAYalias3 WHERE DATE_DAYalias3.day_number = 27 AND DATE_DAYalias3.month_number = 12 AND DATE_DAYalias3.year = 1991)) AND FLIGHTalias1.to_airport IN (SELECT AIRPORT_SERVICEalias3.airport_code FROM airport_service AS AIRPORT_SERVICEalias3 WHERE AIRPORT_SERVICEalias3.city_code IN (SELECT CITYalias3.city_code FROM city AS CITYalias3 WHERE CITYalias3.city_name = 'ORLANDO'))) AND FLIGHTalias1.from_airport IN (SELECT AIRPORT_SERVICEalias2.airport_code FROM airport_service AS AIRPORT_SERVICEalias2 WHERE AIRPORT_SERVICEalias2.city_code IN (SELECT CITYalias2.city_code FROM city AS CITYalias2 WHERE CITYalias2.city_name = 'INDIANAPOLIS')))))) AND one_direction_cost = (SELECT MIN(FAREalias1.one_direction_cost) FROM fare AS FAREalias1 WHERE (FAREalias1.fare_basis_code IN (SELECT FARE_BASISalias0.fare_basis_code FROM fare_basis AS FARE_BASISalias0 WHERE FARE_BASISalias0.basis_days IN (SELECT DAYSalias0.days_code FROM days AS DAYSalias0 WHERE DAYSalias0.day_name IN (SELECT DATE_DAYalias0.day_name FROM date_day AS DATE_DAYalias0 WHERE DATE_DAYalias0.day_number = 27 AND DATE_DAYalias0.month_number = 12 AND DATE_DAYalias0.year = 1991))) AND FAREalias1.fare_id IN (SELECT FLIGHT_FARE_ID FROM flight_fare AS FLIGHT_FARE WHERE FLIGHT_FLIGHT_ID IN (SELECT FLIGHTalias0.flight_id FROM flight AS FLIGHTalias0 WHERE ((FLIGHTalias0.flight_days IN (SELECT DAYSalias1.days_code FROM days AS DAYSalias1 WHERE DAYSalias1.day_name IN (SELECT DATE_DAYalias1.day_name FROM date_day AS DATE_DAYalias1 WHERE DATE_DAYalias1.day_number = 27 AND DATE_DAYalias1.month_number = 12 AND DATE_DAYalias1.year = 1991)) AND FLIGHTalias0.to_airport IN (SELECT AIRPORT_SERVICEalias1.airport_code FROM airport_service AS AIRPORT_SERVICEalias1 WHERE AIRPORT_SERVICEalias1.city_code IN (SELECT CITYalias1.city_code FROM city AS CITYalias1 WHERE CITYalias1.city_name = 'ORLANDO'))) AND FLIGHTalias0.from_airport IN (SELECT AIRPORT_SERVICEalias0.airport_code FROM airport_service AS AIRPORT_SERVICEalias0 WHERE AIRPORT_SERVICEalias0.city_code IN (SELECT CITYalias0.city_code FROM city AS CITYalias0 WHERE CITYalias0.city_name = 'INDIANAPOLIS')))))) AND FAREalias1.round_trip_required = 'NO') AND round_trip_required = 'NO')
WITH "_u_11" AS (SELECT "DATE_DAYalias0"."day_name" FROM "date_day" AS "DATE_DAYalias0" WHERE "DATE_DAYalias0"."day_number" = 27 AND "DATE_DAYalias0"."month_number" = 12 AND "DATE_DAYalias0"."year" = 1991 GROUP BY "day_name"), "_u_12" AS (SELECT "DAYSalias0"."days_code" FROM "days" AS "DAYSalias0" LEFT JOIN "_u_11" AS "_u_11" ON "DAYSalias0"."day_name" = "_u_11"."" WHERE NOT "_u_11"."" IS NULL GROUP BY "days_code"), "_u_13" AS (SELECT "FARE_BASISalias0"."fare_basis_code" FROM "fare_basis" AS "FARE_BASISalias0" LEFT JOIN "_u_12" AS "_u_12" ON "FARE_BASISalias0"."basis_days" = "_u_12"."" WHERE NOT "_u_12"."" IS NULL GROUP BY "fare_basis_code"), "_u_14" AS (SELECT "DATE_DAYalias1"."day_name" FROM "date_day" AS "DATE_DAYalias1" WHERE "DATE_DAYalias1"."day_number" = 27 AND "DATE_DAYalias1"."month_number" = 12 AND "DATE_DAYalias1"."year" = 1991 GROUP BY "day_name"), "_u_15" AS (SELECT "DAYSalias1"."days_code" FROM "days" AS "DAYSalias1" LEFT JOIN "_u_14" AS "_u_14" ON "DAYSalias1"."day_name" = "_u_14"."" WHERE NOT "_u_14"."" IS NULL GROUP BY "days_code"), "_u_16" AS (SELECT "CITYalias1"."city_code" FROM "city" AS "CITYalias1" WHERE "CITYalias1"."city_name" = 'ORLANDO' GROUP BY "city_code"), "_u_17" AS (SELECT "AIRPORT_SERVICEalias1"."airport_code" FROM "airport_service" AS "AIRPORT_SERVICEalias1" LEFT JOIN "_u_16" AS "_u_16" ON "AIRPORT_SERVICEalias1"."city_code" = "_u_16"."" WHERE NOT "_u_16"."" IS NULL GROUP BY "airport_code"), "_u_18" AS (SELECT "CITYalias0"."city_code" FROM "city" AS "CITYalias0" WHERE "CITYalias0"."city_name" = 'INDIANAPOLIS' GROUP BY "city_code"), "_u_19" AS (SELECT "AIRPORT_SERVICEalias0"."airport_code" FROM "airport_service" AS "AIRPORT_SERVICEalias0" LEFT JOIN "_u_18" AS "_u_18" ON "AIRPORT_SERVICEalias0"."city_code" = "_u_18"."" WHERE NOT "_u_18"."" IS NULL GROUP BY "airport_code"), "_u_20" AS (SELECT "FLIGHTalias0"."flight_id" FROM "flight" AS "FLIGHTalias0" LEFT JOIN "_u_15" AS "_u_15" ON "FLIGHTalias0"."flight_days" = "_u_15"."" LEFT JOIN "_u_17" AS "_u_17" ON "FLIGHTalias0"."to_airport" = "_u_17"."" LEFT JOIN "_u_19" AS "_u_19" ON "FLIGHTalias0"."from_airport" = "_u_19"."" WHERE NOT "_u_15"."" IS NULL AND NOT "_u_17"."" IS NULL AND NOT "_u_19"."" IS NULL GROUP BY "flight_id"), "_u_3" AS (SELECT "DATE_DAYalias3"."day_name" FROM "date_day" AS "DATE_DAYalias3" WHERE "DATE_DAYalias3"."day_number" = 27 AND "DATE_DAYalias3"."month_number" = 12 AND "DATE_DAYalias3"."year" = 1991 GROUP BY "day_name"), "_u_4" AS (SELECT "DAYSalias3"."days_code" FROM "days" AS "DAYSalias3" LEFT JOIN "_u_3" AS "_u_3" ON "DAYSalias3"."day_name" = "_u_3"."" WHERE NOT "_u_3"."" IS NULL GROUP BY "days_code"), "_u_5" AS (SELECT "CITYalias3"."city_code" FROM "city" AS "CITYalias3" WHERE "CITYalias3"."city_name" = 'ORLANDO' GROUP BY "city_code"), "_u_6" AS (SELECT "AIRPORT_SERVICEalias3"."airport_code" FROM "airport_service" AS "AIRPORT_SERVICEalias3" LEFT JOIN "_u_5" AS "_u_5" ON "AIRPORT_SERVICEalias3"."city_code" = "_u_5"."" WHERE NOT "_u_5"."" IS NULL GROUP BY "airport_code"), "_u_7" AS (SELECT "CITYalias2"."city_code" FROM "city" AS "CITYalias2" WHERE "CITYalias2"."city_name" = 'INDIANAPOLIS' GROUP BY "city_code"), "_u_8" AS (SELECT "AIRPORT_SERVICEalias2"."airport_code" FROM "airport_service" AS "AIRPORT_SERVICEalias2" LEFT JOIN "_u_7" AS "_u_7" ON "AIRPORT_SERVICEalias2"."city_code" = "_u_7"."" WHERE NOT "_u_7"."" IS NULL GROUP BY "airport_code"), "_u_9" AS (SELECT "FLIGHTalias1"."flight_id" FROM "flight" AS "FLIGHTalias1" LEFT JOIN "_u_4" AS "_u_4" ON "FLIGHTalias1"."flight_days" = "_u_4"."" LEFT JOIN "_u_6" AS "_u_6" ON "FLIGHTalias1"."to_airport" = "_u_6"."" LEFT JOIN "_u_8" AS "_u_8" ON "FLIGHTalias1"."from_airport" = "_u_8"."" WHERE NOT "_u_4"."" IS NULL AND NOT "_u_6"."" IS NULL AND NOT "_u_8"."" IS NULL GROUP BY "flight_id"), "_u_10" AS (SELECT "FLIGHT_FAREalias1"."fare_id" FROM "flight_fare" AS "FLIGHT_FAREalias1" LEFT JOIN "_u_9" AS "_u_9" ON "FLIGHT_FAREalias1"."flight_id" = "_u_9"."" WHERE NOT "_u_9"."" IS NULL GROUP BY "fare_id"), "_u_0" AS (SELECT "DATE_DAYalias2"."day_name" FROM "date_day" AS "DATE_DAYalias2" WHERE "DATE_DAYalias2"."day_number" = 27 AND "DATE_DAYalias2"."month_number" = 12 AND "DATE_DAYalias2"."year" = 1991 GROUP BY "day_name"), "_u_1" AS (SELECT "DAYSalias2"."days_code" FROM "days" AS "DAYSalias2" LEFT JOIN "_u_0" AS "_u_0" ON "DAYSalias2"."day_name" = "_u_0"."" WHERE NOT "_u_0"."" IS NULL GROUP BY "days_code"), "_u_2" AS (SELECT "FARE_BASISalias1"."fare_basis_code" FROM "fare_basis" AS "FARE_BASISalias1" LEFT JOIN "_u_1" AS "_u_1" ON "FARE_BASISalias1"."basis_days" = "_u_1"."" WHERE NOT "_u_1"."" IS NULL GROUP BY "fare_basis_code") SELECT DISTINCT "fare_id" FROM "fare" LEFT JOIN "_u_10" AS "_u_10" ON "_u_10"."" = "fare_id" LEFT JOIN "_u_2" AS "_u_2" ON "_u_2"."" = "fare_basis_code" WHERE "one_direction_cost" = (SELECT MIN("FAREalias1"."one_direction_cost") FROM "fare" AS "FAREalias1" LEFT JOIN "_u_13" AS "_u_13" ON "FAREalias1"."fare_basis_code" = "_u_13"."" WHERE "FAREalias1"."fare_id" IN (SELECT "FLIGHT_FARE_ID" FROM "flight_fare" AS "FLIGHT_FARE" LEFT JOIN "_u_20" AS "_u_20" ON "FLIGHT_FLIGHT_ID" = "_u_20"."" WHERE NOT "_u_20"."" IS NULL) AND "FAREalias1"."round_trip_required" = 'NO' AND NOT "_u_13"."" IS NULL) AND "round_trip_required" = 'NO' AND NOT "_u_10"."" IS NULL AND NOT "_u_2"."" IS NULL