1. Using implicit cursor
The BB_SHOPPER table in the Brewbean's database contains a column named PROMO that specifies promotions to send to shoppers. This column needs to be cleared after the promotion has been sent. First, open the assignment04-03.txt file in the Chapter04 folder in a text editor (such as Notepad). Run the UPDATE and COMMIT statements at the top of this file (not the anonymous block at the end). Modify the anonymous block so that it displays the number of rows updated onscreen. Run the block.
2. Handling Exceptions with User-Defined Errors
Sometimes Brewbean's customers mistakenly leave an item out of a basket that's already been checked out, so they create a new basket containing the missing items. However, they request that the baskets be combined so that they aren't charged extra shipping. An application page has been developed that enables employees to change the basket ID of items in the BB_BASKETITEM table to another existing basket's ID to combine the baskets. A block has been constructed to support this page (see the assignment04-07.sql file in the Chapter04 folder). However, an exception handler needs to be added to trap the situation of an invalid basket ID being entered for the original basket. In this case, the UPDATE affects no rows but doesn't raise an Oracle error. The handler should display the message "Invalid original basket ID" onscreen. Use an initialized variable named lv_old_num with a value of 30 and another named lv_new_num with a value of 4 to provide values to the block. First, verify that no item rows with the basket ID 30 exist in the BB_BASKETITEM table.
3. Assignment 4-8: Processing and Updating a Group of Rows
To help track employee information, a new EMPLOYEE table was added to the Brewbean's database. Review the data in this table. A PL/SQL block is needed to calculate annual raises and update employee salary amounts in the table. Create a block that addresses all the requirements in the following list. All salaries in the EMPLOYEE table are recorded as monthly amounts. Tip: Display the calculated salaries for verification before including the update action.
- Calculate 6% annual raises for all employees except the president.
- If a 6% raise totals more than $2,000, cap the raise at $2,000.
- Update the salary for each employee in the table.
- For each employee number, display the current annual salary, raise, and proposed new annual salary.
- Finally, following the details for each employee, show the total cost of all employees' salary increases for Brewbean's.
Attachment:- Assignment - SQL.rar