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
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
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
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
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
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
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
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);
Why should I choose Homework Writings Pro as my essay writing service?
We Follow Instructions and Give Quality Papers
We are strict in following paper instructions. You are welcome to provide directions to your writer, who will follow it as a law in customizing your paper. Quality is guaranteed! Every paper is carefully checked before delivery. Our writers are professionals and always deliver the highest quality work.
Professional and Experienced Academic Writers
We have a team of professional writers with experience in academic and business writing. Many are native speakers and able to perform any task for which you need help.
Reasonable Prices and Free Unlimited Revisions
Typical student budget? No problem. Affordable rates, generous discounts - the more you order, the more you save. We reward loyalty and welcome new customers. Furthermore, if you think we missed something, please send your order for a free review. You can do this yourself by logging into your personal account or by contacting our support..
Essay Delivered On Time and 100% Money-Back-Guarantee
Your essay will arrive on time, or even before your deadline – even if you request your paper within hours. You won’t be kept waiting, so relax and work on other tasks.We also guatantee a refund in case you decide to cancel your order.
100% Original Essay and Confidentiality
Anti-plagiarism policy. The authenticity of each essay is carefully checked, resulting in truly unique works. Our collaboration is a secret kept safe with us. We only need your email address to send you a unique username and password. We never share personal customer information.
24/7 Customer Support
We recognize that people around the world use our services in different time zones, so we have a support team that is happy to help you use our service. Our writing service has a 24/7 support policy. Contact us and discover all the details that may interest you!
Try it now!
How it works?
Follow these simple steps to get your paper done
Place your order
Fill in the order form and provide all details of your assignment.
Proceed with the payment
Choose the payment system that suits you most.
Receive the final file
Once your paper is ready, we will email it to you.
Our reputation for excellence in providing professional tailor-made essay writing services to students of different academic levels is the best proof of our reliability and quality of service we offer.
When using our academic writing services, you can get help with different types of work including college essays, research articles, writing, essay writing, various academic reports, book reports and so on. Whatever your task, homeworkwritingspro.com has experienced specialists qualified enough to handle it professionally.
Admission Essays & Business Writing Help
An admission essay is an essay or other written statement by a candidate, often a potential student enrolling in a college, university, or graduate school. You can be rest assurred that through our service we will write the best admission essay for you.
Our professional editor will check your grammar to make sure it is free from errors. You can rest assured that we will do our best to provide you with a piece of dignified academic writing. Homeworkwritingpro experts can manage any assignment in any academic field.
If you think your paper could be improved, you can request a review. In this case, your paper will be checked by the writer or assigned to an editor. You can use this option as many times as you see fit. This is free because we want you to be completely satisfied with the service offered.