1. Write a PL/SQL block to ask the user to enter the students marks and determine the GRADE (using IF statements) according to the following criteria. Your output should display the following message for your input score:

Student score is : 99 (99 is whatever is your input value)

The grade is: X (X is whatever Oracle displays)

>90 A

>80 AND

>70 AND

>60 AND

2. The employees salary is to be classified as following:

Class A:

Class B: > 2,000 and

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 employees last name, and displayed in the following format:

Bijoy Bordoloi Salary: 17850 (Class C)

b. Rewrite the solution to question 2a 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 5a.

3. Using the salary classification listed in question 2, 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 or homework exercise you should already have in your notes the SQL query to find the highest average salary.

Department Name:

Average Total Salary: 99999 (Class )

4. Using the cursor FOR loop construct, 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.

5. Write a program to find the NoteID and PhysicianComment column information for all notes recorded for a specific patient. The PatientID column value should be entered interactively.