PL/SQL Exercise 3

Practice 6
1. Run the command in the script lab06_1.sql to create a new table for storing the salaries of the employees.
CREATE TABLE top_dogs
( salary NUMBER(8,2));
2. Create a PL/SQL block that determines the top employees with respect to salaries.

a. Accept a number n from the user where n represents the number of top n earners from the EMPLOYEES table. For example, to view the top five earners, enter 5.
Note: Use the DEFINE command to provide the value for n. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.




SET SERVEROUTPUT ON
SET VERIFY OFF
--VARIABLE inp NUMBER(3)
DEFINE myvar NUMBER(3) PROMPT 'Enter Number'
SET AUTOPRINT ON
DECLARE
TYPE emp_name IS TABLE OF VARCHAR2(25);
EmpName emp_name;
inp NUMBER(3):='&myvar';
BEGIN
select concat(FIRST_NAME,LAST_NAME) BULK COLLECT INTO EmpName from employees order by salary desc;
For i in 1..inp
LOOP
DBMS_OUTPUT.PUT_LINE('List of Employees: '||EmpName(i));
END LOOP;
END;
/


b. In a loop use the iSQL*Plus substitution parameter created in step 1 and gather the salaries of the top n people from the EMPLOYEES table. There should be no duplication in the salaries. If two employees earn the same salary, the salary should be picked up only once.

SET VERIFY OFF
SET SERVEROUTPUT ON
DEFINE inpvar NUMBER(3) PROMPT 'Enter Number of Rows'
DECLARE
TYPE EmpName IS TABLE OF varchar2(25);
TYPE Emp_Salary IS TABLE OF NUMBER(8,2);
emp_name EmpName;
EmpSal Emp_Salary;
myvar NUMBER(3):='&inpvar';
BEGIN
select DISTINCT SALARY BULK COLLECT INTO EmpSal FROM EMPLOYEES order by salary desc;
For i in 1..myvar
LOOP
DBMS_OUTPUT.PUT_LINE('Employee with unique salary : '||EmpSal(i));
END LOOP;
END;
/

c. Store the salaries in the TOP_DOGS table.

create table TOP_DOGS(salary varchar2(100));
commit;

SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS
select distinct Salary from Employees order by Salary desc;
empSalary employees.salary%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO empSalary;
INSERT INTO TOP_DOGS(salary) VALUES(empSalary);
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(empSalary);
END LOOP;
CLOSE C1;
END;
/
d. Test a variety of special cases, such as n = 0 or where n is greater than the number
of employees in the EMPLOYEES table. Empty the TOP_DOGS table after each test. The output shown represents the five highest salaries in the EMPLOYEES table.


SET SERVEROUTPUT ON
DECLARE
myvar NUMBER(3):=&myvar ;
CURSOR C1 IS
select distinct Salary from Employees order by Salary desc;
empSalary employees.salary%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO empSalary;
INSERT INTO TOP_DOGS(Salary) VALUES(empSalary);
EXIT WHEN myvar=0;
DBMS_OUTPUT.PUT_LINE(empSalary);
myvar := myvar-1;
END LOOP;
CLOSE C1;

END;
/





3. Create a PL/SQL block that does the following:
a. Use the DEFINE command to provide the department ID. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.
b. In a PL/SQL block, retrieve the last name, salary, and MANAGER ID of the employees working in that department.

SET SERVEROUTPUT ON
DECLARE
myvar NUMBER(3):=&myvar ;
CURSOR C1 IS
select MANAGER_ID,LAST_NAME,SALARY from Employees where DEPARTMENT_ID=myvar;
empSalary employees.salary%type;
empMgrID EMPLOYEES.MANAGER_ID%type;
emplname EMPLOYEES.LAST_NAME%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO empMgrID,emplname,empSalary;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(empMgrID||' '||emplname||' '||empSalary);
END LOOP;
CLOSE C1;

END;
/

SET SERVEROUTPUT ON
DECLARE
CURSOR emp_cursor IS
SELECT employee_id, last_name,salary FROM employees
WHERE department_id =30;
BEGIN
FOR emp_record IN emp_cursor
LOOP
DBMS_OUTPUT.PUT_LINE( emp_record.employee_id||' ' ||emp_record.last_name||' '||emp_record.salary);
END LOOP;
END;
/



c. If the salary of the employee is less than 5000 and if the manager ID is either 101 or 124, display the message <> Due for a raise. Otherwise, display the message <> Not due for a raise.
Note: SET ECHO OFF to avoid displaying the PL/SQL code every time you execute the script.


SET ECHO OFF
SET SERVEROUTPUT ON
DECLARE
CURSOR C1 IS SELECT MANAGER_ID,LAST_NAME,SALARY FROM EMPLOYEES;
empSalary employees.salary%type;
empMgrID EMPLOYEES.MANAGER_ID%type;
emplname EMPLOYEES.LAST_NAME%type;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO empMgrID,emplname,empSalary;
IF EMPSALARY<5000 THEN
IF EMPMGRID in (101,124)
THEN
DBMS_OUTPUT.PUT_LINE(emplname||' Due for a raise');
ELSE
DBMS_OUTPUT.PUT_LINE(emplname||' Not Due for a raise');
END IF;
ELSE
DBMS_OUTPUT.PUT_LINE(emplname||' Not Due for a raise');
END IF;
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;

END;
/