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) SQL Statement to insert and update tables

-- 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 Gatlin', '2nd Ave', 'Birmingham'); insert into employee values('Ingvar Kamprad', 'University Ave', 'Oslo'); insert into employee values('Paul Allen', 'First St', 'Seattle'); insert into employee values('Karl Albrecht', 'Westlake Ave', 'London'); insert into employee values('Lawrence Ellison', '20717 International Blvd.', 'Silicon Valley'); insert into employee values('Mark Zuckerberg', 'First Avenue', 'Silicon Valley'); insert into employee values('Sheryl Sandberg', 'Second Avenue', 'Silicon Valley'); insert into employee values('Dave Wehner', 'Third Avenue', 'Silicon Valley'); insert into employee values('Mike Schroepfer', 'Fourth Avenue', 'Silicon Valley'); insert into employee values('Chris Cox', 'Sixth Avenue', 'Silicon Valley'); insert into employee values('Kenneth Thomson', '20717 International Blvd.', 'Silicon Valley'); insert into employee values('Liliane Bettencourt', '21 Rue de Miroir', 'Lyon'); 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', 230000); insert into works values('Lakshmi Mittal', 'General Electric', 60000); insert into works values('Carlos Slim Helu', 'General Electric', 400000); insert into works values('Prince Gatlin', 'General Electric', 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('Mark Zuckerberg', 'Facebook', 650000); insert into works values('Sheryl Sandberg', 'Facebook', 450000); insert into works values('Dave Wehner', 'Facebook', 120000); insert into works values('Mike Schroepfer', 'Facebook', 94000); insert into works values('Chris Cox', 'Facebook', 300000); insert into works values('Kenneth Thomson', 'Oracle', 120000); insert into works values('Liliane Bettencourt', 'Chanel', 500000); 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('BP', 'London'); insert into company values('Oracle', 'Silicon Valley'); insert into company values('Facebook', 'Silicon Valley'); 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('Sheryl Sandberg', 'Mark Zuckerberg'); insert into manages values('Dave Wehner', 'Mark Zuckerberg'); insert into manages values('Mike Schroepfer', 'Sheryl Sandberg'); insert into manages values('Chris Cox', 'Sheryl Sandberg'); insert into manages values('Bernard Arnault', 'Liliane Bettencourt'); insert into manages values('Sheldon Adelson', 'Michael Dell');

commit;