Help With Oracle PL/SQL Homework
I'm stuck on three Oracle PL/SQL problems involving cursors and loops. For a pro, the entire project should take less than one hour. The questions I need answered are pasted below. I need the PL/SQL code to perform the listed requirements. I have a Word document with the database diagram for the problems.
5. The employees' salary is to be classified as following:
Class A: <= 2,000
Class B: > 2,000 and <= 15,000
Class C: > 15,000
a. Using a cursor and LOOP construct, write a PL/SQL program to display the full name ( first name followed by middle name and last name), salary, and class of salary for each employee who works in the department 2. The output must be ordered alphabetically by the employee's last name, and displayed in the following format:
Bijoy Bordoloi Salary: 17850 (Class C)
b. Rewrite the solution to question 5 to process a query for any department. Use a substitution variable. Display the department number and department name on one output line, and then list all employees in the department with the format given in question 5.
6. Using the salary classification listed in question 5, find and display the department with the highest average salary and the salary class. Display the output using the following format. Hint: As an in-class exercise you should already have in your notes the SQL query to find the 'highest average salary'.
Department Name: <insert name here>
Average Total Salary: 99999 (Class <insert class here>)
7. Using the cursor FOR loop, write a program to display all the employees with a title of 'M.D.' that have more than one specialty. The relationship between the employee and employeeSpecialty tables is one-to-many.