Suppose you have created tables as follows Tables:Company(ci…

Suppose you have created tables 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* Please write PL/SQL program for the following program 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 id 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.

The provided problem requires the creation of a PL/SQL program that consists of a procedure and a function. The program is focused on retrieving and displaying relevant information from the given tables – Company, Employee, and Manages. Furthermore, the program is expected to handle certain scenarios such as printing the names of employees working at a specific company and determining the manager’s ID based on an employee’s name.

To tackle Problem 1, we need to create a PL/SQL procedure that takes a company name as input and prints out the names of employees working at that company. Let’s call this procedure “get_employees”. Here is the code for the procedure:

“`sql
CREATE OR REPLACE PROCEDURE get_employees(p_company_name IN VARCHAR2) IS
BEGIN
FOR emp IN (SELECT ename
FROM Employee e
JOIN Company c ON e.cid = c.cid
WHERE c.cname = p_company_name) LOOP
DBMS_OUTPUT.PUT_LINE(emp.ename);
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE(‘No employees found for company ‘ || p_company_name);
END;
“`

In this procedure, we are using a FOR loop to iterate through the result set of employees who are associated with the provided company name. The join condition between Employee and Company tables is established using the common attribute “cid”. If there are no employees found for the given company name, the program will handle the exception and print an appropriate message.

To test the procedure, simply call it with a company name as input. For example:

“`sql
BEGIN
get_employees(‘ACME Corporation’);
END;
“`

This will print out the names of employees working at the ‘ACME Corporation’.

Moving on to Problem 2, we need to create a PL/SQL function called “get_manager” that returns the manager’s ID given the name of an employee. If an employee does not have a manager, the function will return a null value. Here is the code for the function:

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

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

In this function, we are using a SELECT statement to retrieve the manager’s ID using a join between Manages and Employee tables. The join condition is based on the common attribute “eid”. If no data is found, indicating that the employee does not have a manager, the function will handle the exception and return a null value.

To test the function, we can write an anonymous PL/SQL block that calls the function and prints out the results. For example:

“`sql
DECLARE
v_employee_name VARCHAR2(100) := ‘John Smith’;
v_manager_id NUMBER;
BEGIN
v_manager_id := get_manager(v_employee_name);
DBMS_OUTPUT.PUT_LINE(‘Manager ID for employee ‘ || v_employee_name || ‘: ‘ || v_manager_id);
END;
“`

This will display the manager’s ID for the employee with the name ‘John Smith’.

In summary, the provided PL/SQL program consists of a procedure and a function that can print the names of employees working at a specific company and retrieve the manager’s ID based on an employee’s name, respectively. The program is designed to handle scenarios where no employees are found for a given company or an employee does not have a manager.