Question: i need help with 2 sql queries asap. ------ setup drop table Trip_Detail; drop table Flight_Reservation; drop table passenger; drop table schedule; drop table flight;

i need help with 2 sql queries asap.

------ setup

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;

The two sql that i want is below

1 Return the shortest flight from BWI to LAX on Novemeber 12 2017, including flight number, departure time, arrival time, and duration of flight. Hint: trunc(departure_time) returns departure date, use sub query.

2.Compute connection time between leg 1 and leg 2 of outbound and inbound trip made by Jeff from BWI to PVG. Please return both connection time and the connecting airport code.

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:

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,...

Q:

SQL for these questions can be found at bottom. I need the answers to work in Oracle 12c 1. Show the average number of crimes reported by an officer. 2. Show the total number of crimes by status. 3....

Q:

This SQL script is not executing for some reason please help IF OBJECT_ID( 'TCourseStudents' ) IS NOT NULL DROP TABLE TCourseStudents IF OBJECT_ID( 'TCourses' ) IS NOT NULL DROP TABLE TCourses IF...

Q:

Database SQL JustLee Books Database DROP TABLE CUSTOMERS CASCADE CONSTRAINTS; DROP TABLE ORDERS CASCADE CONSTRAINTS; DROP TABLE PUBLISHER CASCADE CONSTRAINTS; DROP TABLE AUTHOR CASCADE CONSTRAINTS;...

Q:

Database SQL JustLee Books Database DROP TABLE CUSTOMERS CASCADE CONSTRAINTS; DROP TABLE ORDERS CASCADE CONSTRAINTS; DROP TABLE PUBLISHER CASCADE CONSTRAINTS; DROP TABLE AUTHOR CASCADE CONSTRAINTS;...

Q:

4. A list of all amounts owed is needed. Create a list showing each criminal name, charge ID, total amount owed (fine amount plus court fee), amount paid, amount owed, and payment due date. If...

Q:

a single dml SQL files with all queries clearly named and separated (put comments in your sql file); use the attached ddl and dml to create the db on which to test your queries. ddl: DROP TABLE...

Q:

Project 3 Details: This project is based on eight tables (AIRPORT, FLIGHT, DEPARTURES, PASSENGER, RESERVATION, EQUIP_TYPE, PILOTS, and TICKET) that contain data about the Belle Airlines. Download and...

Q:

need the mysql code for the queries First Database. 1. Initialize your database with the following script: -- initialize CREATE DATABASE airtraffic; USE airtraffic CREATE TABLE plane ID INTEGER, Type...

Q:

On 1 July 2022, Jack Ltd acquired some corporate bonds issued by McCoy Ltd. They had a 'face value of $2 million and offered a coupon rate of 10 % paid annually ($100,000 per year), paid on 30 June...

Q:

Consider the curve defined by x cos (y) + 4y = 4. Write the equation of the tangent line to the curve at the point (0, ).

Q:

Exercise 4.7 In this exercise we examine how latencies of individual components of the datapath affect the clock cycle time of the entire datapath, and how these components are utilized by...

Q:

Please make it fast 3 6 1 .

Q:

How many Tables Will Base HCMSs typically have? Why?

Q:

What is the process of normalization?

Q:

What is Notation in Data Modeling, and what is the most common Notation Type used?