Question Hi King, I am stuck in a query can you help.
The question is that.
Given that the salary earned by an employee (scott.s_emp) is per month. write a PL/SQL PROCEDURE that calculates annual tax paid by each employee. List their ID,LAST NAME,FIRST NAME,ANNUAL SALARY,AND TAX AMOUNT in dollars and cents.
as example the range is
Taxable income Tax on this income
1. $0-$6000 = Nil
2. $6001-$25000 = 15c for each $1 over $6000.
3. $25001-$75000 = $2850 plus 30c for each $1 over
$25000
4. $75001- $150,000 = $17850 plus 40c for each $1over
$75,000
5. $150,000 and over = $47850 plus 45c for each $1
over $150,000
Answer Hi Jawad,
Create the test table us_employee and try the below procedure.Please change the column name according to your need.
Hope this will help.
Cheers
--Below is the procedure to calculate tax of everybody in the us_employee table
CREATE OR REPLACE procedure pro_emp_calculate_tax
As
lv_tax number(9,2):=0;
begin
for i in (select id,last_name,first_name,annual_salary
from us_employee)
loop
lv_tax:=0;
if i.annual_salary <= 6000 then
lv_tax:=0;
dbms_output.put_line(i.id||' '||i.first_name||' '||i.last_name||' '||i.annual_salary||' '||lv_Tax);
elsif i.annual_salary >= 6001 and i.annual_salary <= 25000 then
lv_tax:= (0.15*(i.annual_salary-6000));
dbms_output.put_line(i.id||' '||i.first_name||' '||i.last_name||' '||i.annual_salary||' '||lv_Tax);
elsif i.annual_salary >= 25001 and i.annual_salary <= 75000 then
lv_tax:= 2850 + (0.3*(i.annual_salary-25000));
dbms_output.put_line(i.id||' '||i.first_name||' '||i.last_name||' '||i.annual_salary||' '||lv_Tax);
elsif i.annual_salary >= 75001 and i.annual_salary <= 150000 then
lv_tax:= 17850 + (0.4*(i.annual_salary-75000));
dbms_output.put_line(i.id||' '||i.first_name||' '||i.last_name||' '||i.annual_salary||' '||lv_Tax);
elsif i.annual_salary >= 150001 then
lv_tax:= 47850 + (0.45*(i.annual_salary-150000));
dbms_output.put_line(i.id||' '||i.first_name||' '||i.last_name||' '||i.annual_salary||' '||lv_Tax);
end if;
end loop;
end pro_emp_calculate_tax;
--to test the pro_emp_calculate_tax in sqlplus, run the following commands
SQL> set serveroutput on;
SQL> exec pro_emp_calculate_tax;
1 john smith 1000 0
2 alan hunter 6000 0
3 baxter lynn 7000 150
4 steve turner 26000 3150
5 barry howarth 80000 19850
6 graham tie 155000 50100
