rockcairn logoRockCairn.com rockcairn logo
PROMPT
create or replace VIEW avg_cost_per_mile (Airlines, Avg_Cost_per_Mile)
AS
select f.airline_name, sum(f.cost)/sum(m.mileage)
from Flight_segments f, Mileages m
where (f.dep_airport = m.dep_airport and f.arr_airport = m.arr_airport) or
(f.dep_airport = m.arr_airport and f.arr_airport = m.dep_airport)
group by f.airline_name;
COMMIT;

PROMPT
create or replace VIEW flight_arrivals (Arrive_Time, Arriving_From,
Fight_Number, Airlines, Departed_Time, Arrival_Date)
AS
select f.arr_time, f.dep_airport, d.flight_num, d.airline_name,
f.dep_time, d.dep_date
from Departures d, Flight_Segments f
where d.airline_name = f.airline_name and d.flight_num = f.flight_num and
d.flight_seg = f.flight_seg and f.arr_airport = 'DEN';
COMMIT;

PROMPT
create or replace VIEW Arrivals_view (Arr_Date, Arriving_From, Arrive_Time, Departed_Time,
Airline, Flight_Num, Arrivals)
AS
select d.dep_date, f.dep_airport, f.arr_time, F.dep_time,
d.airline_name, d.flight_num, f.arr_airport
from Departures d, Flight_Segments f
where d.airline_name = f.airline_name and d.flight_num = f.flight_num and
d.flight_seg = f.flight_seg order by d.dep_date;
COMMIT;

PROMPT
create or replace VIEW Departures_view (Dep_Date, Arriving_In, Departing_Time, Arrive_Time,
Airline, Flight_Num, Departures)
AS
select d.dep_date, f.arr_airport, F.dep_time, f.arr_time,
d.airline_name, d.flight_num, f.dep_airport
from Departures d, Flight_Segments f
where d.airline_name = f.airline_name and d.flight_num = f.flight_num and
d.flight_seg = f.flight_seg order by d.dep_date;
COMMIT;

PROMPT
create or replace PROCEDURE cancel_flight (date_in IN varchar, airline_name_in
IN varchar, flight_num_in IN number, dep_airport_in IN char)
AS
flt_cnt number(6) := 0;
seg_cnt number(6) := 0;
seg_id number(6) := 0;
BEGIN
-- Determine if this segment exists
select count(*) into seg_cnt
from Flight_segments
where airline_name = airline_name_in and
flight_num = flight_num_in and
dep_airport = dep_airport_in;

-- Determine if flight exists
select count(*) into flt_cnt
from Departures
where dep_date = date_in and
airline_name = airline_name_in and
flight_num = flight_num_in;

if flt_cnt = 0
then
raise_application_error(-20500, 'Flight Not Scheduled');

elsif seg_cnt = 0
then
raise_application_error(-20500, 'Flight Segment does not exist');

else
select flight_seg into seg_id
from Flight_segments
where airline_name = airline_name_in and
flight_num = flight_num_in and
dep_airport = dep_airport_in;
delete from Departures
where dep_date = date_in and
airline_name = airline_name_in and
flight_num = flight_num_in and
flight_seg = seg_id;
end if;
END;
/
COMMIT;