SQL - Oracle Version 4.1.4.21: Need someone to fix my answers so they run without any errors. Thnx.

--------------------------------

DECLARE lv_tax_num NUMBER(2,2); BEGIN CASE 'NJ' WHEN 'VA' THEN lv_tax_num := .04; WHEN 'NC' THEN lv_tax_num := .02; WHEN 'NY' THEN lv_tax_num := .06; END CASE; DBMS_OUTPUT.PUT_LINE('tax rate = '||lv_tax_num); EXCEPTION WHEN CASE_NOT_FOUND THEN DBMS_OUTPUT.PUT_LINE('No Tax'); END;

--------------------------------

DECLARE rec_shopper bb_shopper%ROWTYPE; BEGIN SELECT * INTO rec_shopper FROM bb_shopper WHERE idShopper = 99; EXCEPTION WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE('Invalid shopper ID'); END;

--------------------------------

ALTER TABLE bb_basketitem ADD CONSTRAINT bitems_qty_ck CHECK (quantity

--------------------------------

DECLARE lv_new_num NUMBER(3) :=4; lv_old_num NUMBER(3) :=30; ex_id_fk EXCEPTION; PRAGMA EXCEPTION_INIT(ex_id_fk, -02291); BEGIN UPDATE bb_basketitem SET idBasket = lv_old_num WHERE idBasket = lv_new_num; EXCEPTION WHEN ex_id_fk THENDBMS_OUTPUT.PUT_LINE('Invalid original basket ID'); END;

--------------------------------

DECLARE CURSOR cur_emp IS SELECT employee_id, salary, salary*12 AnSal, salary*12*.06 Raise FROM employees WHERE employee_id 100 FOR UPDATE NOWAIT; rec_emp cur_emp%rowtype; new_sal number(12,2); BEGIN OPEN cur_emp; LOOP FETCH cur_emp INTO rec_emp; EXIT WHEN cur_emp%NOTFOUND;

IF rec_emp.employee_id 100 THEN new_sal := rec_emp.salary*12 * 0.6; elsif rec_emp.Raise > 2000 then rec_emp.Raise:=2000; End if; UPDATE employees SET salary = new_sal WHERE CURRENT OF cur_emp; Dbms_output.put_line( 'EmpNo: ' || rec_emp.employee_id ||'Current annual Salary: ' ||rec_emp.salary ||'Salary raise: ' ||rec_emp.raise ||'Proposed New Salary: ' ||new_sal); END LOOP; CLOSE cur_emp; END;

Problem 1:- Using Exception Handling Review this code containing a CASE statement and no exception handlers. Copy and paste this block into SQL Developer and run it. An error is raised because the state of NJ isn't included in the CASE statement - recall that a CASE statement must find a matching case. DECLARE vtaxDum NUMBERI2.2): BEGIN CASE NJ' WHEN 'VA' THEN v_tax_num 04; WHEN 'NC' THEN W.tax oum:-.02; WHEN 'NY' THEN vtax DumiF.06; END CASE DBMS_OUTPUT.PUT_LUNEC'tax rate -'1 llv.taxnuml; END; To correct the problem, add a predefined EXCEPTION handler that addresses this error and displays 'No Tax' onscreen. Run the block again - review the exception Problem 2:- Handling Predefined Exceptions A block of code has been created to retrieve basic customer information. Application page was modified so that an employee can enter a customer number that could cause an error. An exception handler needs to be added to the block that displays the message "Invalid shopper ID" onscreen. Use an initialized variable named lv_shopper_num to provide shopper Id. Test the block with shopper ID 99. DECLARE rec.shopper bbshopperROWTYRE BEGIN SELECT ROM tbb. shoRpet WHERE idShopper-22; END; Problem 3 Handling Exceptions with Undefined Errors Brewbean's wants to add a check constraint on the QUANTITY column of the BB_BASKETITEM table. If a shopper enters a quantity value greater than 20 for an item, Brewbean's wants to display the message "Check Quantity" onscreen. ALTER TABLE bb basketitem ADD CONSTRAINT bitemsatvck CHECK (quantity