ALMMÄÄCHD - SQL-QUERY

StudentJojo

Hi,

ich hab ne SQL Anfrage zu stellen, und weiss hinten und vorne ned, wie ich das lösen soll:

"Der Flug mit der Flugnummer US80 führt über fünf Intervalle vom SEATTLE/TACOMA
INTERNATIONAL Airport zum PHILADELPHIA INTERNATIONAL Airport. Die Gesamtkosten
betragen $1922 für alle fünf Intervalle in der Economy-Class. Angenommen ein Student
hat nur noch $1200 in seiner Tasche und will diese Strecke zurücklegen. Formulieren Sie eine
Anfrage, welche die ersten n Intervalle zusammen mit ihren Zielflughäfen und den kumulierten
Flugpreisen bis zu diesen Flughäfen ausgibt, die der Student mit seinen $1200 buchen kann. Ab
welchem Flughafen muss er trampen?
Hinweis: Es ist eine Subquery nötig mit Self-Join über der Relation flight_intervals."

Meine Tabellen schauen so aus:

CREATE TABLE aircraft_type (
	aircraft_code		varchar2(3) 	PRIMARY KEY,
	aircraft_description	varchar2(64),
	manufacturer		varchar2(22),
	engines			integer,
	wide_body		varchar2(3) 	CONSTRAINT wide_body_ck CHECK (wide_body IN ('YES', 'NO')),
	wing_span		number,
	length			number,
	weight			integer,
	pay_load		integer,
	cruising_speed		integer,
	range_miles		integer)
CREATE TABLE aircraft (
	aircraft_id		integer 	PRIMARY KEY,
	aircraft_code		varchar2(3) 	CONSTRAINT aircraft_aircraft_code_fk REFERENCES aircraft_type,
	state			varchar2(20))
CREATE TABLE airline (
	airline_code		varchar2(3)	PRIMARY KEY,
	airline_name		varchar2(64))
CREATE TABLE airport (
	airport_code		INTEGER		PRIMARY KEY,
	airport_name		varchar2(40),
	airport_location	varchar2(36),
	country_name		varchar2(25))
CREATE TABLE flight (
	flight_number		varchar2(8) 	PRIMARY KEY,
	airline_code		varchar2(3)	CONSTRAINT fligt_airline_code_fk REFERENCES airline,
	aircraft_id		integer 	CONSTRAINT flight_aircraft_id_fk REFERENCES aircraft)
CREATE TABLE flight_intervals (
	flight_id		integer 	PRIMARY KEY,
	flight_number		varchar2(8)	CONSTRAINT fi_flight_number_fk REFERENCES flight,
	from_airport		INTEGER		CONSTRAINT fi_from_airport_fk REFERENCES airport(airport_code),
	to_airport		INTEGER		CONSTRAINT fi_to_airport_fk REFERENCES airport(airport_code),
	departure_time          date,
	arrival_time            date)
CREATE TABLE passenger (
	passenger_id		integer 	PRIMARY KEY,
	name			varchar2(30),
	address			varchar2(80))
CREATE TABLE booking_class (
	booking_class_id	varchar2(1) 	PRIMARY KEY,
	booking_class_description varchar2(40))
CREATE TABLE booking (
	passenger_id		integer 	CONSTRAINT booking_passenger_id_fk REFERENCES passenger,
	flight_id		integer 	CONSTRAINT booking_flight_id_fk REFERENCES flight_intervals,
	booking_class_id	varchar2(1) 	CONSTRAINT booking_booking_class_id_fk REFERENCES booking_class,
	PRIMARY KEY (passenger_id, flight_id))
CREATE TABLE fare (
	flight_id		integer 	CONSTRAINT fare_flight_id_fk REFERENCES flight_intervals,
	booking_class_id	varchar2(1)	CONSTRAINT fare_booking_class_id_fk REFERENCES booking_class,
	price			integer,
	PRIMARY KEY (flight_id, booking_class_id))
CREATE TABLE capacity (
	aircraft_id		integer 	CONSTRAINT capacity_aircraft_id_fk REFERENCES aircraft,
	booking_class_id	varchar2(1) 	CONSTRAINT capacity_booking_class_id_fk REFERENCES booking_class,
	capacity		integer,
	PRIMARY KEY (aircraft_id, booking_class_id))
	storage ( initial 195k next 5k pctincrease 0);

Ich kam soweit, dass ich mir die Flugabschnitte und deren Preis anzeigen lassen konnte:

SELECT fi.from_airport_name, fi.to_airport_name, f.price
FROM 
(SELECT fi.flight_id, af.airport_name from_airport_name, at.airport_name to_airport_name
	FROM flight_intervals fi, airport af, airport at
	WHERE fi.from_airport = af.airport_code
		AND fi.to_airport = at.airport_code
		AND fi.flight_number = 'US80') fi,
	fare f, booking_class b
WHERE 	b.booking_class_description = 'ECONOMY CLASS'
	AND f.booking_class_id = b.booking_class_id
	AND f.flight_id = fi.flight_id
;

Vielleicht hat ja jemand Zeit und Lust da bisschen dran rumzuknobeln, wär ich sehr dankbar für.

Grüße,

Jojo

Lustig

Was bekommst du wenn du noch ORDER BY departure_time HAVING(SUM(f.price) < 1200) anhängst?

StudentJojo

grad gar nix, weil der db server down is.
ich probiers heude abend mal.

StudentJojo

also wenn ich das dran häng, dann kommd ne fehlermeldung.

aber wenn ich drüber nachdenk, dann kann das eigentlich eh ned das ergebnis geben...

StudentJojo

also ich mo gestehn, ich krichs ned hin...

StudentJojo

Hab die Lösung gekricht:

select a.airport_name, fi.flight_id, tmp.cum_price
from flight_intervals fi, airport a, 
  (select sum(fa.price) as cum_price, fi1.flight_id
  from fare fa, flight_intervals fi1, flight_intervals fi2
  where fi1.departure_time >= fi2.departure_time and
    fa.flight_id = fi2.flight_id and
    UPPER(fi1.flight_number) like 'US80' and
    UPPER(fi2.flight_number) like 'US80' and
    UPPER(fa.booking_class_id) like 'E'
  group by fi1.flight_id,fi1.departure_time
  order by fi1.departure_time) tmp
where fi.flight_id = tmp.flight_id and
  a.airport_code = fi.to_airport and
  tmp.cum_price < 1200;