DBS501 Assignment 2 Solution

Your code must encompass ALL Test cases given here.
1. Write the code for the Procedure called modify_sal that will search table Employees and for a given Department Id perform the following:
By using Cursor For Loop you will scan each employee’s salary and figure out is it smaller than the Average amount earned in that department (ignore the Commission) and then if YES adjust his/her salary to that average amount. Count # of employees who received salary increase. Do NOT perform unnecessary calculations in the loop. Also, be sure that you locked all rows before performing your Update -- you do NOT want to wait for just one row in the middle of your salary modification.
You also need to take care of the wrong input (Department Id is invalid) and also if that Department is empty (NO employees there). Then UNDO your change.

Here are the possible outputs:
SQL> @a2-1;

Procedure created.
No errors.

SQL> EXECUTE modify_sal(99);
This Department Id is invalid: 99

PL/SQL procedure successfully completed.

SQL> EXECUTE modify_sal(190);
This Department is EMPTY: 190

PL/SQL procedure successfully completed.

SQL> EXECUTE modify_sal(10);
No salary was modified in Department: 10

PL/SQL procedure successfully completed.

SQL> EXECUTE modify_sal(110);
Employee William Gietz just got an increase of $1850
Total # of employees who received salary increase is: 1

PL/SQL procedure successfully completed.

SQL> EXECUTE modify_sal(60);
Employee David Austin just got an increase of $960
Employee Valli Pataballa just got an increase of $960
Employee Diana Lorentz just got an increase of $1560
Total # of employees who received salary increase is: 3

PL/SQL procedure successfully completed.

SQL> Rollback; Rollback complete.

2. Write the code for the Function called Total_Cost that will for a provided Student Id return Total Cost for ALL enrolled courses. Test your Function for a Valid and Invalid input by using BIND variables. You need to take care of an Invalid Id (returned value will be -1) and also if student is enrolled in NO courses (meaning the returned value will be 0). Show all 3 tests with Bind variables as well.

Here are the outputs:



3) A) Write the Package Specification called My_pack for both OBJECTS created here. B) Then write the Package Body specification and compile without warnings.
C) Test your Package by providing input as for Question 2)

4) Now OVERLOAD your Package with TWO NEW variations of Function Total_Cost:

A) First will accept TWO Input parameters: First Name and Last Name, returns Total Cost
B) Second will accept ONE Input parameter: Zip Code (here you need a For Cursor Loop) it returns Total Cost for ALL students living in the provided ZIP area
C) Test your OVERLOADED Function in both forms from --- by Full Name and by Zip input by providing proper parameter values through BIND variables. Show your testing.

Here are the outputs:

If you enter VERONA GRANT then



If you enter YVONNE WINNICKI then
COST
0


If you enter PETER PAN then
COST
-1


If you enter 07044 then
COST
1195


If you enter 11209 then
COST
7070



If you enter 11111 then
COST
-1