Assignment 4-8: Processing and Updating a Group of Rows

To help track employee information, a new EMPLOYEE table was added to the Brewbeans 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 Brewbeans.

Also the schema for Employee table -

Name Null? Type -------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NOT NULL NUMBER(2)

--Table list--

4Connections x| Reports x Connections XE_pbok Tables (Filtered) B B- B- i BB-AUDIT-LOGON BB-BASKET BB-BASKETTEM BB_BASKETSTATUS BB BLOCKTEST i E-E B- BB-PRODUCT-REQUEST B- BB-PRODUCTOPTION B- BB-PRODUCTOPTIONCATEGORY . BB-PROD-SALES BB-PRODUCT | | : BB-PRODUCTOPTIONDETAIL I - BB-PROMO | B- BB-SHIPPING BB-SHOP-SALES B-E BBSHOPPER | - BB TEST2 BB TRANS LOG