IS 633 UMBC PLSQL Stored Procedure Project Q1.Write a PL/SQL procedure that given an order id, first check whether this order exists. If not, print an erro
IS 633 UMBC PLSQL Stored Procedure Project Q1.Write a PL/SQL procedure that given an order id, first check whether this order exists. If not, print an error message no such order. Then compute the total cost equals the sum of each item’s price * quantity in the order plus a 10% delivery fee. The procedure finally updates the g_order table to set the total column to the computed value (the initial total column has null value).
Q2.Write a PL/SQL procedure which takes a time interval x as input, and print out names of employees and IDs of deliveries whose actual delivery time is within plus or minus x of the planned delivery time. E.g., if planned delivery time is timestamp ‘2020-6-10 12:00:00.00’ and actual delivery time is timestamp ‘2020-6-10 12:10:00.00′, and x is interval ’15’ minute, then this delivery satisfies the condition.
create table G_emp
(eid int,
ename varchar(50),
primary key(eid));
insert into g_emp values(1, ‘Alice’);
insert into g_emp values(2, ‘David’);
insert into g_emp values(3, ‘Susan’);
insert into g_emp values(4, ‘Nathan’);
create table g_customer
(cid int,
cname varchar(50),
phone varchar(20),
address varchar(100),
zip varchar(10),
primary key (cid));
insert into g_customer values(1, ‘Nancy’, ‘1234567890’,’123 west springs drive, Ellicott City’, ‘21043’);
insert into g_customer values(2, ‘Ryan’, ‘2334561234’,’123 Hilltop Road’, ‘21022’);
insert into g_customer values(3, ‘Ann’,’7653451010′,’11110 Baltimore National Pike’, ‘21042’);
create table g_item
(iid int,
iname varchar(50),
unit varchar(20),
price number,
primary key(iid));
insert into g_item values(1,’egg’,’dozen’, 4.00);
insert into g_item values(2,’milk’,’gallon’, 4.50);
insert into g_item values(3, ‘ground beef’, ‘pound’, 3.50);
insert into g_item values(4,’chicken drum sticks’,’2 pound pack’,3.00);
insert into g_item values(5,’French bread’,’1 pack’,3.00);
create table g_order
(oid int,
cid int,
otime timestamp,
total number,
primary key(oid),
foreign key (cid) references g_customer);
insert into g_order values(1,1,timestamp ‘2020-6-2 11:00:00.00’, null);
insert into g_order values(2,1,timestamp ‘2020-6-3 16:00:00.00’, null);
insert into g_order values(3,2,timestamp ‘2020-6-4 20:00:00.00’, null);
insert into g_order values(4,3,timestamp ‘2020-6-4 21:00:00.00’, null);
create table g_order_line
(oid int,
iid int,
quantity number,
primary key(oid,iid),
foreign key(oid) references g_order,
foreign key(iid) references g_item);
— order 1, egg, milk, bread
insert into g_order_line values(1, 1, 2);
insert into g_order_line values(1, 2, 1);
insert into g_order_line values(1, 5, 2);
— order 2, beef and chicken
insert into g_order_line values(2, 3, 2);
insert into g_order_line values(2, 4, 2);
— order 3, milk, bread, chicken
insert into g_order_line values(3, 2, 2);
insert into g_order_line values(3, 4, 1);
insert into g_order_line values(3, 5, 2);
— order 4, milk, bread,
insert into g_order_line values(4, 2, 1);
insert into g_order_line values(4, 5, 2);
create table g_delivery
(did int,
oid int,
eid int,
planned_time timestamp,
actual_time timestamp,
primary key(did),
foreign key(oid) references g_order,
foreign key(eid) references g_emp);
— two delivery for order 1
insert into g_delivery values(1, 1, 1, timestamp ‘2020-6-3 11:00:00.00’, timestamp ‘2020-6-3 11:10:00.00’);
insert into g_delivery values(2, 1, 2, timestamp ‘2020-6-4 16:00:00.00’, timestamp ‘2020-6-4 15:50:00.00’);
— one delivery for order 2
insert into g_delivery values(3, 2, 1, timestamp ‘2020-6-4 15:00:00.00’, timestamp ‘2020-6-4 15:40:00.00’);
— two delivery for order 3
insert into g_delivery values(4, 3, 3, timestamp ‘2020-6-5 16:00:00.00’, timestamp ‘2020-6-5 16:10:00.00’);
insert into g_delivery values(5, 3, 2, timestamp ‘2020-6-6 14:00:00.00’, timestamp ‘2020-6-6 14:10:00.00’);
create table g_delivery_item
(did int,
iid int,
primary key(did, iid),
foreign key(did) references g_delivery,
foreign key(iid) references g_item);
insert into g_delivery_item values(1,1);
insert into g_delivery_item values(1,2);
insert into g_delivery_item values(2,5);
insert into g_delivery_item values(3,3);
insert into g_delivery_item values(3,4);
insert into g_delivery_item values(4,2);
insert into g_delivery_item values(4,4);
insert into g_delivery_item values(5,5);
