section 1

1a. Write a PL/SQL script to create a PROCEDURE that will list the employee names given the country ID.

1b. Call the procedure from an anonymous block to allow the user to key in the country ID (country_id).

Expected output:

Enter the country id: US

The employee(s) in the UNITED STATES OF AMERICA are as follows:

1. Steven King 2. Neena Kochhar 3. Lex De Haan 4. Alexander Hunold 5. Bruce Ernst 6. David Austin

...

PL/SQL procedure successfully completed.

section 2 :

2a. Write a PL/SQL script to create a FUNCTION that calculates the bonuses for employees according to their job position.

2b. Call the function from an anonymous block to allow the user to key in the employee ID (employee_id).

Below is the bonus detail for each job:

JOB TITLE

BONUS

(%)

Stock Clerk

7

Shipping Clerk

10

Purchasing Clerk

15

Programmer

20

Other Jobs

5

Expected Output:

Enter the employee ID: 105

The bonus amount to be paid for David Austin is RM 960.00

PL/SQL procedure successfully completed.

OR

Enter the employee ID: 102

The bonus amount to be paid for Lex De Haan is RM 850.00

PL/SQL procedure successfully completed