in oracle sql create a procedure that will determine the 1. top 3 highest averag

Question

in oracle sql create a procedure that will determine the

1. top 3 highest average salary (employee_salary) departments (dept)

2. top 5 employees

1. example return dept, avg_dept_salary rank

tech 78333.33 1

research 62000 2

business 53500 3

2.

ex answer

employee_name rank

bob 1

dylan 2

marissa 3

bill 4

heidi 5

please create the procedure for each

dept salary employee tech 83000 bob tech 78000 dylan tech 74000 marissa research 63000 bill research 62000 heidi research 61000 nick business 54000 francis business 53000 susan support 45000 kate support 40000 hillary

Explanation / Answer

-- Create table
create table TEMP_EMP
(
dept VARCHAR2(100),
average VARCHAR2(100)
)
/
-- Create table
create table TEMP_EMP_RANK
(
emprank VARCHAR2(10)
  
)
/

-- Create table
create table EMP
(
dept VARCHAR2(20),
salary NUMBER,
employee VARCHAR2(20)
)
/

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('tech', 83000, 'bob');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('tech', 78000, 'dylan');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('tech', 74000, 'marissa');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('research', 63000, 'bill');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('research', 62000, 'heidi');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('research', 61000, 'nick');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('business', 54000, 'francis');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('business', 53000, 'susan');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('support', 45000, 'kate');

insert into emp (DEPT, SALARY, EMPLOYEE)
values ('support', 40000, 'hillary');
/
commit;

create or replace procedure avg_sal
(
vc_cursor out SYS_REFCURSOR
)

as
begin
insert into temp_emp (
select dept , Average from (select dept, avg(salary) Average from emp
group by dept order by avg(salary) desc) where rownum<4);

open vc_cursor for
select dept,Average from temp_emp;

end;

/
------------------------
/*

output
   dept   average
  
   tech   78333.3333333333
   research   62000
   business   53500

*/
create or replace procedure emp_rank
(
vc_cursor out SYS_REFCURSOR
)

as
begin
insert into TEMP_EMP_rank (

SELECT emprank FROM
(
SELECT EMPLOYEE, SALARY,
RANK() OVER (ORDER BY SALARY DESC) EMPRANK
FROM emp
)
WHERE emprank <= 5
)
;

open vc_cursor for
select emprank from TEMP_EMP_rank;

end;

output
===================
emprank
=============
1
2
3
4
5

=================

Please excute one by one queries you will get results in cusror.

Please do commit once insert statement exute.

excute create table statements first then excute procedure.

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

---

---

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