2. [3 marks] Create a procedure to allow an employee in the shipping department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the current status, date, and comments as each stage of the order process are finished. Use an anonymous block to test your procedure.

2. [3 marks] Create a procedure to allow an employee in the shipping department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the current status, date, and comments as each stage of the order process are finished. Use an anonymous block to test your procedure.

Oracle 12c: SQL

3rd Edition

ISBN:9781305251038

Author:Joan Casteel

Publisher:Joan Casteel

Chapter6: Additional Database Objects

Section: Chapter Questions

Problem 1HOA

See similar textbooks

Similar questions

Create a procedure to allow an employee in the shipping department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the current status, date, and comments as each stage of the order process are finished. Use an anonymous block to test your procedure.
Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation.
2. Create a trigger that will maintain the correct value in the membership balance in theMEMBERSHIP table when videos are returned late. The trigger should execute as anFOR/AFTER trigger when the due date or return date attributes are updated in theDETAILRENTAL table. The trigger should satisfy the following conditions. a) Calculate the value of the late fee prior to the update that triggered this execution ofthe trigger. The value of the late fee is the days late times the daily late fee. If theprevious value of the late fee was null, then treat it as zero (0).b) Calculate the value of the late fee after the update that triggered this execution of thetrigger. If the value of the late fee is now null, then treat it as zero (0).c) Subtract the prior value of the late fee from the current value of the late fee todetermine the change in late fee for this video rental.d) If the amount calculated in part c is not zero (0), then update the membership balanceby the amount calculated for the…
Task 2 - Assume that you are working as a database administrator for XYZ Company. You are asked write the code(Text) and result(Screenshot of result) with discussion of Tablespace Management as given below, a. iv. Insert values until it now longer has space for new rows.
The SERVICE_REQUEST table uses the CHAR data type for the DESCRIPTION and STATUS fields. Is there an alternate data type that could be used to store the values in these fields? Justify your reason for choosing an alternate data type or for leaving the data type as CHAR.
The StayWell maintenance team wants to minimize the number of planned visits to properties. The team wants to learn all the properties that have different planned service dates. You need to return all available data in the SERVICE_REQUEST table, where requests are made for the same property with a different NEXT_SERVICE_DATE. Task Return a list of all properties where service requests are made with different NEXT_SERVICE_DATE values.
Task 5: The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation. Task: Create a TRIGGER to run on the USERS table before any UPDATE operation. (SQL Database Test): Create a TRIGGER before any UPDATE operations on the USERS table
The Developers team also wants you to ensure that emails are converted to lowercase after an update operation. Currently, new insertions are guaranteed to have lowercase emails, but there is no such guarantee for legacy emails. Therefore, the team wants to ensure that emails are being retained in a lowercase irrespective of any changes in the database. Create a new TRIGGER called email_update for the USERS table that runs before an UPDATE operation. Task Create a TRIGGER to run on the USERS table before any UPDATE operation.
Question 8 [5 Marks] Write a statement to create a trigger. Print a message to the employee when anyone tries to add data to the Employee_Salary table. Create a statement to verify that the trigger is fired successfully.
The InstantRide User Satisfaction team are looking forward to creating discounts for the users. However, the team suspects that there could be duplicate users in the system with different emails. Check for the users with their names and surnames for potential duplicates. Therefore, you need to JOIN the USERS table with USERS table and compare for equality of USER_FIRST_NAME and USER_LAST_NAME and difference in USER_ID fields.
Warehouse Database Diagrams Tables System Tables FileTables dbo.invoicearchive dbo.invoicelineitems dbo.invoicesarchive E dbo.invoicesTerms1 dbo.invoicesTerms2 E dbo.invoicesTerms3 E dbo.vendors
PL/SQL Problems Q1. Create a function to calculate a shopper’s total spending, excluding shipping and tax amount, with Brewbean’s site in a particular year. Exception handling is needed. Use an anonymous block to call the function and output the result. Q2.Create a procedure to allow an employee in the shipping department to update an order status to add shipping information. The BB_BASKETSTATUS table lists events for each order so that a shopper can see the current status, date, and comments as each stage of the order process are finished. Use an anonymous block to test your procedure. Q3.Create a function to insert a new product into an existing order, include the product id, unit price, quantity. The output of the function is the message to notify the calling program whether the update succeeded or not. Use an anonymous block to call the function and output the result. Q4.Create a function to determine the total pledge amount for a project. Use the function in an SQL statement to…