Work 123

PLSQL

The below requirements are on employee schema. Please setup the environment using the attached documents. Please ignore if the system as employee schema.

Problem 1

Write a program in PL/SQL to create a cursor displays the name and salary of each employee in the EMPLOYEES table whose salary is less than that specified by a passed-in parameter value. Note: Use necessary exceptions handlers to handle the errors.

Problem 2

Write a program to update salary of an employee based on department and commission percentage. If department is 40 increase salary by 10%. If department is 70 then 15%, if commission is more than .3% then 5% otherwise 10%.

Problem 3

Write a trigger to ensure that no charges can be made to EMPLOYEES table before 6 am and after 10 PM in a day.

Problem 4

Develop PL/SQL program to get list of department IDs and their corresponding details, department name, manager ID, Location ID.

Declare

/*declared a collection of %rowtype type*/

BEGIN

/*get all the department details then store into collection */

/*Retrieve and display elements in collection using index and fields in records*/

END;

Problem 5

Develop PL/SQL program to add a new department with

DEPARTMENT_ID:111

DEPARTMENT_NAME: Sales

MANAGER_ID:222

LOCATION_ID:1500

In the program,

a. Raise an exception with error number if manager ID is not valid and handle with an appropriate message.

b. Raise an exception with error number if location ID is not valid and handle with an appropriate message