COEN 178 Intro to Database Systems Lab 5 solution

$24.99

Original Work ?
Category: You will Instantly receive a download link for .ZIP solution file upon Payment

Description

5/5 - (3 votes)

Objectives: Learn

 

  • PLSQL Functions
  • Exceptions

In this lab, we will use AlphaCoEmp and Emp_Work tables that you have created in the earlier labs.

 

The following is the plan of what you will do today.

  • Load randomly generated values for salary and title for the tuples in AlphaCoEmp using a procedure.
  • Write a function called calcSalaryRaise() to calculate the amount of raise for an employee.
  • Create a table called EmpStats.
  • Create a procedure called saveCountsByTitle
  • Create a function called countByTitle() with an exception

 

Run the queries and capture the results in lab5_output.lst, using spool.

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Question 1 (10 pts)

 

In this exercise, we will try to assign job titles to employees randomly selected from a hardcoded set of titles, in AlphaCoEmp table, using a PLSQL procedure. Since we want to assign salaries based on titles, we will store titles and salaries in two PLSQL VArrays and randomly select an index into the arrays and use the title and its associated salary from the same index number. For example, the salary for the title stored at index 0 in the titles array will be at index 0 in the salaries array.

Note: In PLSQL VArrays, subscripts start at 1, not 0.

Examine the code in the procedure below, to assign job titles and salaries.

 

Create or Replace Procedure assignJobTitlesAndSalaries

As

type titlesList IS VARRAY(5) OF AlphaCoEmp.title%type;

type salaryList IS VARRAY(5) of AlphaCoEmp.salary%type;

v_titles titlesList;

v_salaries salaryList;

Cursor Emp_cur IS

Select * from AlphaCoEmp;

l_emprec Emp_cur%rowtype;

l_title AlphaCoEmp.title%type;

l_salary AlphaCoEmp.salary%type;

l_randomnumber INTEGER := 1;

BEGIN

/* Titles are stored in the v_titles array  */

/* Salaries for each title are stored in the v_salaries array.

The salary of v_titles[0] title is at v_salaries[0].

*/

v_titles := titlesList(‘advisor’, ‘director’, ‘assistant’, ‘manager’, ‘supervisor’);

 

v_salaries := salaryList(130000, 100000, 600000, 500000, 800000);

 

/* use a for loop to iterate through the set 

for l_emprec IN Emp_cur

LOOP

     /* We get a random number between 1-5 both inclusive */

l_randomnumber := dbms_random.value(1,5);

 

/* Get the title using the random value as the index into the

v_tiles array */

l_title := v_titles(l_randomnumber);

/* Get the salary using the same random value as the index into the v_salaries array */

l_salary := v_salaries(l_randomnumber);

 

/* Update the employee title and salary using the l_title

and l_salary */

update AlphaCoEmp

set title = l_title

where name = l_emprec.name;  

 

update AlphaCoEmp

set salary = l_salary

where name = l_emprec.name;

 

 

END LOOP;

 

commit;

END;

/

Show errors;

 

Run the code and if it compiles without errors, run the command,

 

 

  1. a) exec assignJobTitlesAndSalaries at SQL prompt.
  2. b) Run a Select * on AlphaCoEmp table and check if titles and salaries are assigned.
  3. c) Now, modify the above procedure and include one more job title and a salary for that title in the code.
  • I added an occupation named ‘trash collector’ with a salary of 1000000.
  1. d) Run the procedure.
  2. e) Execute the procedure and make sure it is working ok.

 

To ensure that it is working correctly, I made sure to adjust the varray size to 6 and increased the upper-bound of l_randomnumber from 5 to 6.

 

Question 2 (15 pts)

 

You will write a PLSQL function that calculates the salary raise based on the current salary and the percent raise. PLSQL functions return a value.

The function calcSalaryRaise() calculates raise amount as follows:

  • Takes the name and percent salary (an integer) as parameters.
  • Fetches the employee’s salary from AlphaCoEmp Table and calculates the amount of raise.

 

Create or Replace Function calcSalaryRaise( p_name in AlphaCoEmp.name%type, percentRaise IN Integer)

RETURN NUMBER

IS

l_salary AlphaCoEmp.salary%type;

l_raise AlphaCoEmp.salary%type;

l_cnt Integer;

BEGIN

— Find the current salary of p_name from AlphaCoEMP table.

Select salary into l_salary from AlphaCoEmp

where name = p_name;

— Calculate the raise amount

l_raise := l_salary * (percentRaise/100);

 

 

 

 

 

 

 

 

      /* return a value from the function */

return l_raise;

 

END;

/

Show Errors;

 

Run the function (copy and paste it at SQL prompt or run it from a script file). Once it compiles without errors, you can execute it by calling it, as shown below.

  1. a) If you want to test the function and see if it is working ok, call it as follows:

 

Select calcSalaryRaise(‘Stone’,2) from Dual;

 

Note: You can give any name that is in the AlphaCoEmp table.

 

  1. b) Call the function as part of a more useful SQL query

Select name, title, salary CURRENTSALARY, trunc(calcSalaryRaise(name,2)) NEWSALARY

from AlphaCoEmp where upper(name) = upper(‘Stone’);

 

What is the output?

NAME                          TITLE                  CURRENTSALARY  NEWSALARY

————————- ——————– ————————  —————-

Stone                           trash collector   1000000              20000

 

  1. c) If you examine the code of the function, we are comparing (string compare) the name with the parameter, p_name without checking the case. Modify the code so that both strings are compared with each other, both in upper or lowercase.

Test and make sure your function work correctly after modifications.

The function still works properly after setting both strings that we are comparing to uppercase.

 

  1. d) What happens if you call the function with a name that NOT in the AlphaEoEmp table?

 

Select calcSalaryRaise(‘BoogieMonster‘,2) from Dual;

 

CALCSALARYRAISE(‘BOOGIEMONSTER’,2)

———————————————————

 

Now let us rewrite the function with an exception.

Add the given code below the “return l_raise” statement.

EXCEPTION

   WHEN NO_DATA_FOUND THEN

      dbms_output.put_line(‘No employee with given name found!’);

       –return 0;

   WHEN others THEN

      dbms_output.put_line(‘Error!’);

 

Test your function again with a name that is not in the table.

 

CALCSALARYRAISE(‘BOOGIEMONSTER’,2)

———————————————————-

0

 

No employee with given name found!

 

 

It now returns a 0 for the value, rather than leaving the value blank and prints out “No employee with given name found!”

 

Question 3 (15 pts)

  1. a) Let us create a table called EmpStats as follows:

Create table EmpStats (title VARCHAR(20) Primary KEY,empcount INTEGER, lastModified DATE);

 

  1. b) The function (incomplete) below, counts the number of employees from AlphaCoEmp table by title, where title is passed as a parameter and returns the count.

Complete the function and run it.

 

Create or Replace Function countByTitle(p_title in AlphaCoEmp.title%type)

RETURN NUMBER IS

l_cnt Integer;

BEGIN

     /* Complete the query below */

Select into l_cnt from AlphaCoEmp

Group by

Having

return l_cnt;

END;

/

  1. c) Run the SQL commands below and show the output.

 

select countByTitle(‘director’) from Dual;

select countByTitle(‘advisor’) from Dual;

 

Output:

 

COUNTBYTITLE(‘DIRECTOR’)

—————————————

84

 

 

COUNTBYTITLE(‘ADVISOR’)

————————————-

44

 

Question 4 (15 pts)

 

If the function, countByTitle() in Q3 is working ok, we will write a procedure to store the number of employees by title in the table, EmpStats. We will call the function countByTitle() in this procedure.

 

CREATE or REPLACE procedure saveCountByTitle

AS

l_advisor_cnt integer := 0;

BEGIN

l_advisor_cnt := countByTitle(‘advisor’);

 

delete from EmpStats; — Any previously loaded data is deleted

/* inserting count of employees with title, ‘advisor’.*/

insert into EmpStats values (‘advisor’,l_advisor_cnt,SYSDATE);

END;

/

Show errors;

 

The above procedure stores the count of employees with the title, ‘advisor’.

  1. a) Complete the procedure to store the count of employees for every title you have in AlphaCoEmp table.

 

  1. b) Execute the procedure.

 

  1. c) Show the data in the EmpStats table (do a Select * ).

 

Output:

TITLE                          EMPCOUNT              LASTMODIF

——————–        —————               —————

advisor                       41                             28-OCT-19

director                      74                              28-OCT-19

assistant                    88                              28-OCT-19

manager                    88                              28-OCT-19

supervisor                  94                              28-OCT-19

trash collector         34                              28-OCT-19

 

 

Question 5 (20 pts)

 

  1. Write a PLSQL function called countBySalaryRange() that takes two parameters, called lowEnd and highEnd, both numbers. The function counts the no.of employees in AlphaCoEmp table, whose salaries fall with in the range between lowEnd and highEnd (both numbers inclusive) and returns the count.
  2. Check if your function is created without errors.
  3. Now, call the function, countBySalaryRange() with two values of your choice for the parameters and display the value returned.

Select countBySalaryRange(20000,900000) From Dual;

 

COUNTBYSALARYRANGE(20000,900000)

—————————————————–

378

  1. Show how you call countBySalaryRange() with two values of your choice for the parameters and display the value returned with an appropriate column heading.

 

Select countBySalaryRange(20000,900000) EmployeesInSalaryRange From Dual;

 

EMPLOYEESINSALARYRANGE

——————————————-

387