Create a test table called Emp2 that is similar to the Worker table Use CREATE T

Question

Create a test table called Emp2 that is similar to the Worker table Use CREATE TABLE Emp2 AS SELECT * FROM Worker).

a. Write a procedure called add_any_emp to insert values into your Emp2 table. If a salary is greater than 20000, then set that salary to 20000. Insert at least three rows into your table with your procedure. In furnishing values for the procedure, include a salary greater than 20000 for one of those rows. Display your loaded table.

b. Write a procedure called new_emp to access the Emp2 table created in a. In your procedure, insert a new worker named Madison from WI into the department that has the highest department number. Make the salary equal to the average of all other workers less 15%. Pass the name and state through the parameter list. Save the script you use to create this procedure.

c. Write a function called avg_emp that returns the average salary of workers in your Emp2 table. Do not include the lowest salary in the average. Display the average salary using the Dual table.

d. Retrieve the script used to create the procedure in Exercise 12-2 and use the function created in Exercise 12-3 in the procedure to add another worker to the table. The parameter list should include name, state, and department number.

e. Create a package called Salary that contains both the procedure, new_emp, and the function, avg_emp, but rename the function avg_emp1 for the package. Test new_emp by adding another worker to the Emp2 table and show the table after your addition. Do not put the function in the package specification (you are hiding the function in the package). Try to use avg_emp1 in a SELECT (SELECT Salary.evg_emp1 FROM Dual). Does it work?

Explanation / Answer

TABLE Worker CREATION
-----------------
1. create table worker ( "name" varchar2(50), ST varchar2(50), salary number, DEPT number)
2. insert into worker values('SHIKLA','AL', 55000, 2);
3. insert into worker values('RICHARRD','FL', 53000, 1);
4. insert into worker values('George','TX', 45000, 2);

Data selection From Worker table
---------------------------------

select * from worker

Emp2 Table Creation from worker table
-----------------------------------------

CREATE TABLE Emp2 AS SELECT * FROM Worker

emp2 table records selection
-------------------------------

SELECT * FROM Emp2

A. Write a procedure called add_any_emp
-------------------------------------------

create or replace procedure "add_any_emp" (name IN VARCHAR2,ST IN VARCHAR2,salary IN NUMBER,DEPT IN NUMBER)
is
begin
IF salary > 20000 THEN
salary := 20000
END IF
INSERT INTO WORKER VALUES(name,ST,salary,DEPT);   
end;

B. Write a procedure called new_emp to access ...
-------------------------------------------------------

create or replace procedure "add_any_emp" (name IN VARCHAR2,ST IN VARCHAR2,salary IN NUMBER,DEPT IN VARCHAR2)
is
highest_dept number;
set_salary number;
begin
IF salary > 20000 THEN
salary := 20000
END IF
select countdept INTO highest_dept FROM ( select DEPT,COUNT(*) as countdept FROM worker GROUP BY DEPT ORDER BY countdept DESC ) WHERE ROWNUM = 1;;
select (avg(salary) - (0.15 * avg(salary))) INTO set_salary from emp2
insert into worker values(name,ST,set_salary,highest_dept);   
end;

C. Write a function called avg_emp that returns....
-----------------------------------------------------------

CREATE OR REPLACE Function avg_emp
( name_in IN varchar2 )
RETURN number
IS
avg_sal number;

cursor c1 is
SELECT (avg(salary) - (0.15 * avg(salary))) FROM emp2 where salary IN ( select salary from emp2 where salary != ( select min(salary) from emp2 ) );

BEGIN

open c1;
fetch c1 into avg_sal;

if c1%notfound then
avg_sal := 9999;
end if;

close c1;

RETURN avg_sal;

EXCEPTION
WHEN OTHERS THEN
raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.