Here are the questions that need to be answered: Implement the following stored
ID: 3553538 • Letter: H
Question
Here are the questions that need to be answered:
Implement the following stored procedures in PL/SQL (40 points each):
o A. Create for a company X the following report (shown below for Wal-Mart
Stores):
Company Name: Wal-Mart Stores
Located in: Bentonville
Total Employees: 5
Total Managers: 3
Average Salary Employees: 111800
Average Salary Managers: 75000
Test and include in the assignment the report for Chanel and BP (before
modifying salary values in the second store procedure).
o B. Give all employees that works in a company located in city X a Y percent
raise if they are managers and Z percent raise if they are not managers. X,
Y, and Z will be the three parameters for the stored procedure. Test your
procedure, and show that it worked correctly (using SQL statements before
and after update).
Here are the create table statements:
drop table employee;
drop table works;
drop table company;
drop table manages;
-- Create tables
create table employee
(employee_name varchar2(35),
street varchar2(30),
city varchar2(15),
primary key (employee_name));
create table works
(employee_name varchar2(35),
company_name varchar2(20),
salary number(7),
primary key (employee_name));
create table company
(company_name varchar2(20),
city varchar2(15),
primary key (company_name));
create table manages
(employee_name varchar2(35),
manager_name varchar2(35),
primary key (employee_name));
-- Populate tables
insert into employee
values('William Gates III', 'University St', 'Redmond');
insert into employee
values('Warren Buffett', 'Alaskan Way', 'Omaha');
insert into employee
values('Lakshmi Mittal', '1st Ave', 'London');
insert into employee
values('Carlos Slim Helu', '1st Ave', 'London');
insert into employee
values('Prince Alwaleed Bin Talal Alsaud', 'Roy St', 'Riyadh');
insert into employee
values('Ingvar Kamprad', 'University Ave', 'Oslo');
insert into employee
values('Paul Allen', 'University St', 'Redmond');
insert into employee
values('Karl Albrecht', 'Westlake Ave', 'London');
insert into employee
values('Lawrence Ellison', '20717 International Blvd.', 'Silicon Valley');
insert into employee
values('S Robson Walton', 'Sixth Avenue', 'Bentonville');
insert into employee
values('Jim Walton', 'Sixth Avenue', 'Bentonville');
insert into employee
values('John Walton', 'Sixth Avenue', 'Bentonville');
insert into employee
values('Alice Walton', 'Sixth Avenue', 'Bentonville');
insert into employee
values('Helen Walton', 'Sixth Avenue', 'Bentonville');
insert into employee
values('Kenneth Thomson', '20717 International Blvd.', 'Silicon Valley');
insert into employee
values('Liliane Bettencourt', '18 Avenue de Suffren', 'Paris');
insert into employee
values('Bernard Arnault', '18 Avenue de Suffren', 'Paris');
insert into employee
values('Michael Dell', '4533 South', 'Austin');
insert into employee
values('Sheldon Adelson', '4533 South', 'Austin');
insert into employee
values('Theo Albrecht', 'Westlake Ave', 'London');
insert into works
values('William Gates III', 'Microsoft', 1400000);
insert into works
values('Warren Buffett', 'Exxon Mobil', 2300000);
insert into works
values('Lakshmi Mittal', 'General Electric', 600000);
insert into works
values('Carlos Slim Helu', 'General Electric', 400000);
insert into works
values('Prince Alwaleed Bin Talal Alsaud', 'ChevronTexaco', 50000);
insert into works
values('Ingvar Kamprad', 'BP', 80000);
insert into works
values('Paul Allen', 'Microsoft', 300000);
insert into works
values('Karl Albrecht', 'BP', 100000);
insert into works
values('Lawrence Ellison', 'Oracle', 98000);
insert into works
values('S Robson Walton', 'Wal-Mart Stores', 65000);
insert into works
values('Jim Walton', 'Wal-Mart Stores', 40000);
insert into works
values('John Walton', 'Wal-Mart Stores', 120000);
insert into works
values('Alice Walton', 'Wal-Mart Stores', 34000);
insert into works
values('Helen Walton', 'Wal-Mart Stores', 300000);
insert into works
values('Kenneth Thomson', 'Oracle', 120000);
insert into works
values('Liliane Bettencourt', 'Chanel', 5000000);
insert into works
values('Bernard Arnault', 'Chanel', 130000);
insert into works
values('Michael Dell', 'Dell', 900000);
insert into works
values('Sheldon Adelson', 'Dell', 80000);
insert into works
values('Theo Albrecht', 'BP', 56000);
insert into company
values('Microsoft', 'Redmond');
insert into company
values('Exxon Mobil', 'Omaha');
insert into company
values('General Electric', 'London');
insert into company
values('ChevronTexaco', 'Riyadh');
insert into company
values('BP', 'London');
insert into company
values('Oracle', 'Silicon Valley');
insert into company
values('Wal-Mart Stores', 'Bentonville');
insert into company
values('Chanel', 'Paris');
insert into company
values('Dell', 'Austin');
insert into manages
values('Paul Allen', 'William Gates III');
insert into manages
values('Carlos Slim Helu', 'Lakshmi Mittal');
insert into manages
values('Karl Albrecht', 'Ingvar Kamprad');
insert into manages
values('Theo Albrecht', 'Ingvar Kamprad');
insert into manages
values('Kenneth Thomson', 'Lawrence Ellison');
insert into manages
values('Jim Walton', 'S Robson Walton');
insert into manages
values('John Walton', 'Jim Walton');
insert into manages
values('Alice Walton', 'John Walton');
insert into manages
values('Helen Walton', 'Jim Walton');
insert into manages
values('Bernard Arnault', 'Liliane Bettencourt');
insert into manages
values('Sheldon Adelson', 'Michael Dell');
commit;
Any anwers will be greatly appreciated.
Explanation / Answer
CREATE OR REPLACE PROCEDURE print_report
(in_company IN VARCHAR2)
IS
v_company WORKS.COMPANY_NAME%TYPE;
v_city EMPLOYEE.CITY%TYPE;
v_total_emp NUMBER;
v_avg_sal_emp WORKS.SALARY%TYPE;
v_total_man NUMBER;
v_avg_sal_man WORKS.SALARY%TYPE;
BEGIN
select C.COMPANY_NAME, C.CITY, count(*), AVG(W.SALARY)
INTO v_company, v_city, v_total_emp, v_avg_sal_emp
from works w, employee e, company c
where E.EMPLOYEE_NAME = W.EMPLOYEE_NAME
and W.COMPANY_NAME = C.COMPANY_NAME
and W.COMPANY_NAME = in_company
group by C.COMPANY_NAME, C.CITY;
select count(*), AVG(W.SALARY)
INTO v_total_man, v_avg_sal_man
from works w, employee e, company c
where E.EMPLOYEE_NAME = W.EMPLOYEE_NAME
and W.COMPANY_NAME = C.COMPANY_NAME
and W.COMPANY_NAME = in_company
and E.EMPLOYEE_NAME IN (SELECT distinct M.MANAGER_NAME FROM manages m, works w WHERE W.COMPANY_NAME = in_company AND M.MANAGER_NAME = W.EMPLOYEE_NAME)
group by C.COMPANY_NAME, C.CITY;
DBMS_OUTPUT.PUT_LINE('Company Name: ' || v_company);
DBMS_OUTPUT.PUT_LINE('Located in: ' || v_city);
DBMS_OUTPUT.PUT_LINE('Total Employees: ' || to_char(v_total_emp));
DBMS_OUTPUT.PUT_LINE('Total Managers: ' || to_char(v_total_man));
DBMS_OUTPUT.PUT_LINE('Average Salary Employees: ' || to_char(v_avg_sal_emp));
DBMS_OUTPUT.PUT_LINE('Average Salary Managers: ' || to_char(v_avg_sal_man));
END;
/
CREATE OR REPLACE PROCEDURE give_raise
(in_city IN VARCHAR2, in_percent_emp IN NUMBER, in_percent_manager IN NUMBER)
IS
BEGIN
UPDATE works w
SET W.SALARY = W.SALARY + (W.SALARY * in_percent_emp)
WHERE W.EMPLOYEE_NAME IN (select W.EMPLOYEE_NAME
from company c, works w, manages m
where C.COMPANY_NAME = W.COMPANY_NAME
and W.EMPLOYEE_NAME = M.EMPLOYEE_NAME
and C.CITY = in_city);
UPDATE works w
SET W.SALARY = W.SALARY + (W.SALARY * in_percent_manager)
WHERE W.EMPLOYEE_NAME IN (SELECT distinct M.MANAGER_NAME FROM manages m, works w WHERE W.COMPANY_NAME = in_city AND M.MANAGER_NAME = W.EMPLOYEE_NAME);
COMMIT;
END;
/
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.