Question: Please write an anonymous PL/SQL program for each the following problems. You can use the attached SQL statements to create the tables. -Write an anonymous

Please write an anonymous PL/SQL program for each the following problems. You can use the attached SQL statements to create the tables.

-Write an anonymous PL/SQL program to compute the sum of 1, 4, 9, 16,25. You must use a loop.

-Use an implicit cursor to print out the code of arrival and departure airport for the first leg of outbound flight on flight reservation 1 (1 is rid). Please handle exceptions.

Note that you can only use conditions listed in the problem and cannot manually look up data.

-Use an explicit cursor to print out all flights scheduled leaving BWI on November 12 2017 including flight number, arrival city, departure time ordered by arrival city and then by departure time

Hint: you can use trunc(departure_time) to get departure date.

Sample code to create the tables.

drop table Trip_Detail;

drop table Flight_Reservation;

drop table passenger;

drop table schedule;

drop table flight;

drop table airport;

drop table airline;

create table airline

(

alcode char(2),

alname varchar(50),

primary key (alcode)

);

insert into airline values

('UA', 'United');

insert into airline values

('DL', 'Delta');

insert into airline values

('NK', 'Spirit');

create table airport

(

apcode char(3),

apname varchar(50),

city varchar(50),

state varchar(50),

country varchar(50),

primary key(apcode));

insert into airport values

('BWI', 'Baltimore Washington Airport', 'Baltimore', 'MD', 'USA');

insert into airport values

('DTW', 'Detroit Airport','Detroit', 'MI', 'USA');

insert into airport values

('LAX', 'Los Angeles Airport','Los Angeles','CA','USA');

insert into airport values

('PVG', 'Shanghai Pudong Airport','Shanghai','Shanghai','China');

insert into airport values

('EWR', 'Newark Airport','Newark','NJ','USA');

create table flight

(

fnumber varchar(20),

alcode char(2),

departure_apcode char(3),

arrival_apcode char(3),

primary key (fnumber),

foreign key(alcode) references airline,

foreign key(departure_apcode) references airport,

foreign key(arrival_apcode) references airport

);

insert into flight values

('UA 3938','UA','BWI','EWR');

insert into flight values

('UA 86','UA','EWR','PVG');

insert into flight values

('UA 87','UA','PVG','EWR');

insert into flight values

('UA 4144','UA','EWR','BWI');

insert into flight values

('DL 2429','DL','BWI','DTW');

insert into flight values

('DL 583','DL','DTW','PVG');

insert into flight values

('DL 582','DL','PVG','DTW');

insert into flight values

('DL 1906','DL','DTW','BWI');

insert into flight values

('NK 141','NK','BWI','LAX');

insert into flight values

('NK 128','NK','LAX','BWI');

insert into flight values

('UA 1623','UA','BWI','LAX');

insert into flight values

('UA 2020','UA','LAX','BWI');

create table schedule

(

sid int,

fnumber varchar(20),

departure_time timestamp with time zone,

arrival_time timestamp with time zone,

price number,

primary key(sid),

foreign key(fnumber) references flight

);

insert into schedule values

(1, 'UA 3938', timestamp '2017-9-12 06:41:00.00 -05:00',timestamp '2017-9-12 07:50:00.00 -05:00',120);

insert into schedule values

(2, 'UA 86', timestamp '2017-9-12 10:45:00.00 -05:00',timestamp '2017-9-13 13:40:00.00 +08:00',400);

insert into schedule values

(3, 'UA 87', timestamp '2017-10-12 15:40:00.00 +08:00',timestamp '2017-10-12 18:00:00.00 -05:00',300);

insert into schedule values

(4, 'UA 4144', timestamp '2017-10-12 22:00:00.00 -05:00',timestamp '2017-10-12 23:20:00.00 -05:00',200);

insert into schedule values

(5, 'DL 2429', timestamp '2017-9-12 13:00:00.00 -05:00',timestamp '2017-9-12 14:30:00.00 -05:00',140);

insert into schedule values

(6, 'DL 583', timestamp '2017-9-12 16:30:00.00 -05:00',timestamp '2017-9-13 18:50:00.00 +08:00',500);

insert into schedule values

(7, 'DL 582', timestamp '2017-10-12 11:40:00.00 +08:00',timestamp '2017-10-12 13:40:00.00 -05:00',400);

insert into schedule values

(8, 'DL 1906', timestamp '2017-10-12 15:20:00.00 -05:00',timestamp '2017-10-12 17:00:00.00 -05:00',150);

insert into schedule values

(9, 'NK 141', timestamp '2017-11-12 08:40:00.00 -05:00',timestamp '2017-11-12 11:30:00.00 -08:00',120);

insert into schedule values

(10, 'NK 128', timestamp '2017-11-16 21:30:00.00 -08:00',timestamp '2017-11-17 05:30:00.00 -05:00',120);

insert into schedule values

(11, 'UA 1623', timestamp '2017-11-12 06:30:00.00 -05:00',timestamp '2017-11-12 09:00:00.00 -08:00',150);

insert into schedule values

(12, 'UA 2020', timestamp '2017-11-16 21:30:00.00 -08:00',timestamp '2017-11-17 05:30:00.00 -05:00',150);

insert into schedule values

(13, 'UA 3938', timestamp '2017-11-12 06:41:00.00 -05:00',timestamp '2017-11-12 07:50:00.00 -05:00',120);

insert into schedule values

(14, 'UA 4144', timestamp '2017-11-12 22:00:00.00 -05:00',timestamp '2017-11-12 23:20:00.00 -05:00',200);

insert into schedule values

(15, 'DL 2429', timestamp '2017-11-12 13:00:00.00 -05:00',timestamp '2017-11-12 14:30:00.00 -05:00',140);

insert into schedule values

(16, 'NK 128', timestamp '2017-11-11 21:30:00.00 -08:00',timestamp '2017-11-12 05:30:00.00 -05:00',120);

insert into schedule values

(17, 'UA 2020', timestamp '2017-11-11 21:30:00.00 -08:00',timestamp '2017-11-12 05:30:00.00 -05:00',150);

insert into schedule values

(18, 'DL 1906', timestamp '2017-11-12 15:20:00.00 -05:00',timestamp '2017-11-12 17:00:00.00 -05:00',150);

create table passenger

(pid int,

pname varchar(50),

phone varchar(20),

primary key(pid));

insert into passenger values

(1, 'Jeff', '410-465-1928');

insert into passenger values

(2, 'Erin', '410-465-2234');

create table flight_reservation

(

rid int,

pid int,

num_passengers int,

origin_apcode char(3),

destination_apcode char(3),

departure_date date,

return_date date,

total number, --- total price, need to be computed from each flight leg price

primary key (rid),

foreign key(pid) references passenger,

foreign key (origin_apcode) references airport,

foreign key (destination_apcode) references airport

);

insert into flight_reservation values

(1, 1, 4, 'BWI','PVG',date '2017-09-12', date '2017-10-12',0);

insert into flight_reservation values

(2, 1, 1, 'BWI','LAX',date '2017-11-12', date '2017-11-16',0);

insert into flight_reservation values

(3, 2, 4, 'BWI','LAX',date '2017-11-12', date '2017-11-16',0);

create table trip_detail

(

rid int,

sid int,

flag int,

leg int,

primary key(rid,sid),

foreign key (rid) references flight_reservation,

foreign key (sid) references schedule

);

insert into trip_detail values

(1,1,1,1);

insert into trip_detail values

(1,2,1,2);

insert into trip_detail values

(1,3,2,1);

insert into trip_detail values

(1,4,2,2);

insert into trip_detail values

(2,9,1,1);

insert into trip_detail values

(2,10,2,1);

insert into trip_detail values

(3,11,1,1);

insert into trip_detail values

(3,12,2,1);

commit;

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer
Step: 1 Unlock blur-text-image
Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock
Step: 3 Unlock

Students Have Also Explored These Related Databases Questions!

Q:

Please write an anonymous PL/SQL program for each the following problems. You can use the attached SQL statements to create the tables. Use an implicit cursor to print out the code of arrival and...

Q:

Suppose you have created tables in assignment 1. Please write an anonymous PL/SQL program for each the following problems. You can use the same code in assignment 2 to create the tables. Please...

Q:

Tables Created in Assignment 1 drop table paper_review cascade constraints; drop table reviewer cascade constraints; drop table paper_author cascade constraints; drop table paper cascade constraints;...

Q:

DATABASE PROGRAMMING - WRITE THE SQL QUERIES FOR THE FOLLOWING QUESTIONS CREATE TABLE dept ( deptno NUMBER(2,0), dname VARCHAR2(14), loc VARCHAR2(13), CONSTRAINT n_pk_dept PRIMARY KEY (deptno) );...

Q:

The code below using basic loop. Please edit the codes, use cursor for loop ("For dummy_index IN cursor_name LOOP"). The output of your new codes should be the same as the original program. DECLARE...

Q:

Write short programs as requested, the outputs of the program are required to be attached in your submission. Q 1 , ( 1 5 points ) A standalone procedure, based on Employees Table. In this question,...

Q:

PL/SQL Question: XYZ Airlines Inc. keeps track of its employees in its Human Resources database. The T_PILOT table contains basic pilot information. The structure of the table is shown below with...

Q:

1 . In the following code, identify examples of these procedural constructs: variable, conditional control statement, reusable / callable program unit ( functions or methods ) , assignment statement,...

Q:

SQL / PL 1 . In the following code, identify examples of these procedural constructs: variable, conditional control statement, reusable / callable program unit ( functions or methods ) , assignment...

Q:

Need help with this coding problem Suppose you have created tables in assignment 1. Please write one SQL statement for each the following problems. You can use the attached SQL statements to create...

Q:

A and B are two partners sharing profit and loss equally. Their capital A/c stood at Rs.30,000 and Rs.25,000 respectively on 31st March, 2013. On 1st April C is admitted for 1/3rd share of profit for...

Q:

Given a simple passive RC low-pass filter, as sketched below. R To do: Prove that this circuit operates as a first-order system. Question 2: Second-order spring-mass-damper system Given: The most...

Q:

What factors should Raj consider besides price when providing his recommendations to Roger Jones?

Q:

A company is preparing completing their Cash Budget. The following data has been prepared for cash receipts and payments. January February March Cash receipts $1,061,200 $1,182,400 $1,091,700 Cash...