Suppose you have created tables in assignment 1 as follows Tables: Company(cid, cname, city, state) /* city is the company’s location, cname is 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*/ Please write PL/SQL program for the following problems. 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. 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.

In order to solve the problems outlined in the question, we will need to create a PL/SQL procedure and a PL/SQL function. The procedure will be responsible for printing out names of employees working at a given company, while the function will return the manager’s mid given the name of an employee.

For problem 1, we will create a PL/SQL procedure that takes a company name as input and prints out the names of employees working at that company. The procedure will need to query the Employee table based on the provided company name and retrieve the names of employees who work at that company. Here is an example implementation of the procedure:

“`sql
CREATE OR REPLACE PROCEDURE print_employees_at_company (
p_company_name IN Company.cname%TYPE
)
IS
BEGIN
FOR employee IN (
SELECT ename
FROM Employee e
WHERE e.cid = (
SELECT cid
FROM Company c
WHERE c.cname = p_company_name
)
)
LOOP
DBMS_OUTPUT.PUT_LINE(employee.ename);
END LOOP;
END;
“`

To test the procedure, you can use a company name from your Company table. For example:

“`sql
BEGIN
print_employees_at_company(‘ABC Company’);
END;
“`

This will display a list of names of employees working at the ‘ABC Company’.

For problem 2, we will create a PL/SQL function called get_manager that takes an employee name as input and returns the manger’s mid. If an employee does not have a manager, the function will return a null value. The function will need to query the Manages table based on the provided employee name and retrieve the manager’s mid. Here is an example implementation of the function:

“`sql
CREATE OR REPLACE FUNCTION get_manager (
p_employee_name IN Employee.ename%TYPE
)
RETURN Manages.mid%TYPE
IS
v_manager_id Manages.mid%TYPE;
BEGIN
SELECT m.mid INTO v_manager_id
FROM Manages m
WHERE m.eid = (
SELECT e.eid
FROM Employee e
WHERE e.ename = p_employee_name
);

RETURN v_manager_id;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;
“`

To test the function, you can call it in an anonymous PL/SQL block and print out the results. For example:

“`sql
DECLARE
v_employee_name Employee.ename%TYPE := ‘John Doe’;
v_manager_id Manages.mid%TYPE;
BEGIN
v_manager_id := get_manager(v_employee_name);
IF v_manager_id IS NULL THEN
DBMS_OUTPUT.PUT_LINE(‘Employee has no manager’);
ELSE
DBMS_OUTPUT.PUT_LINE(‘Manager ID: ‘ || v_manager_id);
END IF;
END;
“`

This will print out the manager’s ID for the employee with the name ‘John Doe’. If the employee does not have a manager, it will display ‘Employee has no manager’.