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
