PL/SQL WORKSHEET
1. Input a salary, output all the employees that have salary greater than the input salary. 2. Input two salaries, output all the employees that has a salary within the input range. 3. Input two employee numbers, delete all the employees within the two employee numbers. 4. Input an employee name, add 500 to the employees commission. 5. Create a table called EMP5 based on the employee table with the following fields: ename, hiredate, sal and comm. 6. Input a salary, add all the employees that have a salary greater than the salary input to the EMP5 table. 7. Suppose that the EMP table contains an odd number of employees. Input an odd number N from the keyboard. Output the middle N employees in the EMP table, sorted by salary. Problems set by Manashe 8. Create a table called managers with 2 fields: mgrnum and position. Fill the table by selecting all the employees from the emp table who are managers. The manager number will correspond to the empno in the emp table, the position reflects the job the manager does i.e. If an emloyee is in the sales department and s/he is a manager then the persons position is Sales manager. 9. Write using a cursor to retrieve rows from the emp table who are managers. Use the same cursor to fill the managers table. Output the number of rows in the managers table. 10. Alter the managers table by adding a field called payment which reflects the total amount that the department is paying to their employees. Fill the fields by extracting and summing up the salaries from the emp table; group the salaried by department. Remember the total amount paid by the department includes the commission amount. Problems set by Prince
11. Write a script to input an employee number and output the employee name, job, loc and of the employee salary is greater than the average salary. 12. Write a script to output all employees whose name start with the letter ‘S’ and if they have a salary between the highest salary and (the highest salary – 15%). 13. Write a script to input the employee number and update the employees salary as follows: Increase by 5% if salary is less than 1000 Increase by 10% if the salary is >= 1000 and <= 2500 Increase by 15% if the salary is > 2500 Output the amount increase Problems by Dumasani 14. Write a script to output all the employees that are earning more than their managers eg Blake. 15. Write a script to find and output all the top earners in each department. If there is more than one employee that has the top salary in the department the program should output all.