Reference no: EM13870163

Suppose you have created tables in assignment 1 as follows

Tables:
Company(cid, cname, city, state) /* city is the company's location, cname is company name */
Employee(eid, ename, street, city, state, salary, cid) /* city is where the employee lives*/
Manages(eid, mid) /* mid is manager's ID*/

Sol:

drop table manages;
drop table employee;
drop table company;

create table company(
cid integer,
cname varchar(30),
city varchar(30),
state varchar(20),
primary key (cid));

create table employee(
eid integer,
ename varchar(30),
street varchar(30),
city varchar(30),
state varchar(20),
salary number,
cid integer,
primary key (eid),
foreign key (cid) references company(cid));

create table manages(eid integer,
mid integer,
primary key(eid,mid),
foreign key(eid) references employee(eid),
foreign key(mid) references employee(eid));

--- assume the following rows been inserted
insert into company values(1, 'First Bank Corp', 'baltimore', 'MD');
insert into company values(2, 'Verizon', 'DC', 'DC');

insert into employee values(1, 'alice','123 street', 'baltimore', 'MD',55000,1);
insert into employee values(2, 'bob','23 street', 'DC', 'DC',80000,1);
insert into employee values(3,'jeff','33 street', 'DC','DC',60000,2);
insert into employee values(4,'susan','33 street', 'DC','DC',80000,2);
insert into employee values(5,'eric','1 street', 'DC', 'DC',90000,2);
insert into employee values(6,'andy','2 street', 'baltimore', 'MD',70000,2);

insert into manages values(2,1);
insert into manages values(3,4);
insert into manages values(4,5);
insert into manages values(6,4);

Please write PL/SQL program for the following problems.

Problem 1: Please create a PL/SQL procedure that given a company name, print out names of employees working at that company. Test your procedure with a company name you have in your company table.

Problem 2: Please create a PL/SQL function get_manager that returns the manager's mid given the name of an employee. If an employee does not have a manger, return a null value. Please write an anonymous PL/SQL program to call this function with some employee name as input, and print out the results. You can assume that each employee has at most one manager.