[Pl-SQL] 2. Create a PL/SQL block that computes the commission amount for a give

Question

[Pl-SQL]
2. Create a PL/SQL block that computes the commission amount for a given employee based on the employee’s salary.
a.Use the DEFINE command to provide the employee ID. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.
DEFINE p_empno = 100
b.If the employee’s salary is less than $5,000, display the bonus amount for the employee as 10% of the salary.
c.If the employee’s salary is between $5,000 and $10,000, display the bonus amount for the employee as 15% of the salary.
d. If the employee’s salary exceeds $10,000, display the bonus amount for the employee as 20% of the salary.
e. If the employee’s salary is NULL, display the bonus amount for the employee as 0.
f. Test the PL/SQL block for each case using the following test cases, and check each bonus amount.
Note: Include SET VERIFY OFF in your solution.

Explanation / Answer

DECLARE
v_empno employees.employee_id%TYPE :=&p_empno;
v_sal employees.salary%TYPE;
v_bonus_per NUMBER(7,2);
v-bonus NUMBER(7,2);
BEGIN
SELECT salary
INTO v_sal
FROM employees
WHERE employee_id = v_empno;
IF v_sal < 5000 THEN
v_bonus_per: .10;
ELSIF v_sal BETWEEN 5000 and 10000 THEN
v_bonus_per := .15;
ELSIF v_sal > 10000 THEN
v_bonus_per := .20;
ELSE
v_bonus_per : = 0;
END IF;
v_bonus := v_sal * v_bonus_per;
DBMS_OUTPUT.PUT_LINE('The bonus for the employee with employee_id'|| v_empno || ' and salary ' || v_sal|| ' is ' || v_bonus);
END;
/

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.