Answer to Question #268394 in Databases | SQL | Oracle | MS Access for tumi

Question

Question 2                                                                                                                                                         [15]

Write a PL/SQL block to determine whether salaries for members of the staff need to be adjusted or not. Depending on the work load (number of subjects), lectures need to get an increase. Management has decided that whoever teaches 2 or more subjects should get a 15% increase on their salary. Prompt for staff identification number and display this data accordingly. Refer to the sample output

Enter value for staff_number: 513500

Mr/Mrs OLIVIER teaches 2 subjects

ATTENTION: This lecturer is eligible for an increase

The current salary is:          R78000 - The new salary is:          R89700

 

Enter value for staff_number: 502501

Mr/Mrs MORGAN teaches 1 subjects

ATTENTION: This lecturer is not eligible for an increase

Answer 1

SOLUTION FOR THE ABOVE QUESTION

Question 2                                                                                                                                                         [15]
Write a PL/SQL block to determine whether salaries for members of the staff need to be adjusted or not.
 Depending on the work load (number of subjects), lectures need to get an increase. 
Management has decided that whoever teaches 2 or more subjects should get a 15% increase on their salary. 
Prompt for staff identification number and display this data accordingly. Refer to the sample output


In this question we will assume we have a table named Members with name,id_number, subjects columns


ANSWER.
PRINT  'Enter value for staff_number: '
identification_number = getRequestString("id_number")
SELECT name,id_number, subjects FROM Members
WHERE id_number = identification_number 
IF (subjects>=2)
   PRINT name +' teaches ' + CAST(subjects AS VARCHAR) + ' subjects ' 
   PRINT 'ATTENTION: This lecturer is eligible for an increase'
ELSE
   PRINT name +' teaches ' + CAST(subjects AS VARCHAR) + ' subjects ' 
   PRINT 'ATTENTION: This lecturer is not eligible for an increase'


END;