DBS501-Assignment 2 Solved

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:                                                                                

                                                                                                                        

If you enter 194 then

COST 
1195
 
 
 
 
 
 
 
 
 
If you enter 294 then

COST 
0
 
 
 
 
 
 
If you enter 494 then

COST 
-1
 
 
 
 
 

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

COST 
1195
 
 
 
 
 
 
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