Task - Stored Functions - org DB #13

akash-coded · 2022-03-17T07:45:39Z

akash-coded
Mar 17, 2022
Maintainer

Database = `org`

Dump file: org.sql

DB Schema

Calculating Income Tax

Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:
- 10% - salary <= 75000
- 20% - 75000 < salary <= 150000
- 30% - salary > 150000
Write a query that displays all the details of a worker including their computedTax.
Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

preeti-max · 2022-03-17T08:18:02Z

preeti-max
Mar 17, 2022

DELIMITER &&
CREATE FUNCTION computeTax(salary int)RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN
DECLARE incomeTax DECIMAL(10,2);
IF salary<=75000 THEN SET incomeTax=0.1*salary; 
ELSEIF salary>75000 and salary<=150000 THEN SET incomeTax=0.2*salary;
ELSE SET incomeTax=0.3*SALARY;
END IF;
RETURN incomeTax;
END &&
DELIMITER ;
SELECT * ,computeTax(salary)FROM WORKER;





DELIMITER &&
CREATE PROCEDURE CALCULATEINCOMETAX(IN SALARY INT,OUT INCOMETAX DECIMAL(10,2))
BEGIN
SET INCOMETAX=computeTax(SALARY);
END &&
DELIMITER ;
CALL CALCULATEINCOMETAX(80000,@INCOMETAX);
SELECT @INCOMETAX;

0 replies

Harshita-Joshi · 2022-03-17T08:21:56Z

Harshita-Joshi
Mar 17, 2022

1

USE org;

DELIMITER //

CREATE FUNCTION computeTax (salary INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
    BEGIN

        DECLARE taxComputed DECIMAL(10,2);

        IF salary <= 75000 THEN
            SET taxComputed = salary * 10 / 100;
        ELSEIF salary > 75000 AND salary <= 150000 THEN
            SET taxComputed = salary * 20 / 100;
        ELSEIF salary > 150000 THEN
            SET taxComputed = salary * 30 / 100;
        END IF;
            
        RETURN taxComputed;

    END //


DELIMITER ;

SELECT worker.*, computeTax(SALARY) AS computedTax FROM worker;

2

USE org;

DELIMITER //

CREATE PROCEDURE calculateIncomeTax(IN SALARY INT, OUT computedTax DECIMAL (10,2))
BEGIN

    DECLARE tax DECIMAL (10,2);
    
    SET tax = computeTax(SALARY);

    SELECT tax INTO computedTax;

END //

DELIMITER ;

CALL calculateIncomeTax(80000 , @taxAmount);
SELECT @taxAmount AS computedTax;

0 replies

Sarfinaa · 2022-03-17T08:24:51Z

Sarfinaa
Mar 17, 2022

1.)
CREATE FUNCTION computeTax(
	salary int
) 
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE tax DECIMAL(10,2);

    IF salary <= 75000 THEN
		SET tax = salary*10;
    ELSEIF (salary > 75000 AND 
			salary <= 150000) THEN
	SET tax = salary*20;
    ELSEIF salary > 150000 THEN
       SET tax = salary*30;
    END IF;
	-- return the customer level
    SET tax=tax/100;
	RETURN (tax);
END //
SELECT *,computeTax(salary) AS computed_Tax FROM worker;
2.)
CREATE PROCEDURE tax(IN salary INT,OUT computedTax DECIMAL(10,2))
BEGIN
SELECT computeTax(salary) INTO computedTax;
END //
CALL tax(60000,@tax);
SELECT @tax;

0 replies

ratnadeepikavuddagiri · 2022-03-17T08:38:03Z

ratnadeepikavuddagiri
Mar 17, 2022

delimiter //
create function computeTax(salary int) returns decimal(10,2)
deterministic
begin
	declare tax decimal(10,2);
	if salary <=7500 then
		set tax = salary*0.1;
	elseif (salary>7500 and salary<=150000) then
		set tax = salary*0.2;
	else
		set tax = salary*0.3;
	end if;
	return tax;
end//
delimiter ;
select worker_id,salary,computeTax(salary) from worker;


delimiter //
create procedure compute_tax_procedure(in salary int,out tax decimal(10,2))
begin
	set tax = computeTax(salary);
	select tax;
end//  
delimiter ;
call compute_tax_procedure(100000,@tax);

0 replies

Khyati-tripathi · 2022-03-17T08:51:33Z

Khyati-tripathi
Mar 17, 2022

--1

DELIMITER;
CREATE FUNCTION incomeTax(salary INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE incometax DECIMAL(10,2);
    IF( salary<75000) then
        SET incometax= salary*.10;
    ELSEIF (salary>75000 and salary<100000) then
    SET incometax= salary*.20;
    else
    SET incometax= salary*.30;
    end if;
                RETURN incometax;
    end //

    DELIMITER;

    select *, incomeTax(salary) as incomeTax from worker;

--2
DELIMITER //
CREATE PROCEDURE computeIncomeTax(IN salary INT, OUT incometax DECIMAL(10,2))
BEGIN
SET incometax=incomeTax(salary);
END //

DELIMITER ;

 call computeIncomeTax(150000,@result);
 select @result;

0 replies

Ashwesh09 · 2022-03-17T08:57:15Z

Ashwesh09
Mar 17, 2022

Calculating Income Tax

Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

10% - salary <= 75000

20% - 75000 < salary <= 150000

30% - salary > 150000

use org;

DELIMITER //

CREATE FUNCTION computeTax(Salary int)
RETURNS Decimal(10,2)
DETERMINISTIC
BEGIN
    DECLARE tax Decimal(10,2);
    IF Salary <= 75000 THEN
        SET tax = salary * 0.1 ;
    ELSEIF Salary >= 75000 AND Salary <= 150000 THEN
        SET tax = salary * 0.2;
    ELSE 
        SET tax = salary * 0.3;
    END IF;
    RETURN tax;
END //

Select worker_id,salary,computeTax(salary) as IncomeTax from worker;

Write a query that displays all the details of a worker including their computedTax.
2. Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

DELIMITER //

CREATE PROCEDURE calculatedIncomeTax(
    IN sal INT,
    OUT incomeTax DECIMAL(10,2)
)
BEGIN 
    SELECT computeTax(sal) INTO incomeTax;
END //

DELIMITER ;

0 replies

Batchusatyasri · 2022-03-17T08:57:27Z

Batchusatyasri
Mar 17, 2022

1.

mysql> DELIMITER &&
mysql> CREATE FUNCTION computeTax(salary int)RETURNS INT DETERMINISTIC BEGIN
    -> DECLARE incomeTax INT;
    -> IF salary<=75000 THEN SET incomeTax=0.1*salary;
    -> ELSEIF salary>75000 and salary<=150000 THEN SET incomeTax=0.2*salary;
    -> ELSE SET incomeTax=0.3*SALARY;
    -> END IF;
    -> RETURN incomeTax;
    -> END &&
Query OK, 0 rows affected (0.12 sec)

mysql> delimiter ;
mysql> select *,computeTax(salary) from worker;

2.

delimiter //
mysql> create procedure get_income_tax(in salary int,out incometax int)
    -> begin
    -> set incometax=computeTax(SALARY);
    -> end //
Query OK, 0 rows affected (0.04 sec)

mysql> delimiter ;
mysql> call get_income_tax(95000,@incometax);

0 replies

Shubhi27 · 2022-03-17T08:58:01Z

Shubhi27
Mar 17, 2022

**1**
DELIMITER $$
CREATE FUNCTION computeTax (
    salary INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
    BEGIN
        DECLARE incomeTax DECIMAL(10,2);
        IF salary <= 75000 THEN
            SET incomeTax = salary * 0.1;
        ELSEIF salary > 75000 AND salary <= 150000 THEN
            SET incomeTax = salary * 0.2;
        ELSEIF salary > 150000 THEN
            SET incomeTax = salary * 0.3;
        END IF;
            
        RETURN incomeTax;
    END $$
DELIMITER ;
SELECT *,computeTax(salary) AS income_tax FROM worker;

**2**
DELIMITER $$
CREATE PROCEDURE calculate_Tax(IN salary INT,OUT IncomeTax DECIMAL(10,2))
BEGIN
SET IncomeTax = computeTax(salary);
END $$
DELIMITER ;

CALL calculate_Tax(120000 , @tax);
SELECT @tax;

0 replies

RohitNaikade1 · 2022-03-17T09:12:33Z

RohitNaikade1
Mar 17, 2022

1

delimiter //
create function computeTax(salary int) returns decimal(10,2)
deterministic
begin
	declare tax decimal(10,2);
	if salary <=7500 then
		set tax = salary*0.1;
	elseif (salary>7500 and salary<=150000) then
		set tax = salary*0.2;
	else
		set tax = salary*0.3;
	end if;
	return tax;
end//
delimiter ;
select worker_id,salary,computeTax(salary) from worker;

delimiter //
create procedure compute_tax_procedure(in salary int,out tax decimal(10,2))
begin
	set tax = computeTax(salary);
	select tax;
end//  
delimiter ;
call compute_tax_procedure(100000,@tax);

0 replies

abhiraj2k · 2022-03-17T09:13:06Z

abhiraj2k
Mar 17, 2022

DELIMITER //
CREATE FUNCTION calcTax(
    salary INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE incomeTax DECIMAL(10,2);
    SET incomeTax = 0;

    IF (salary <= 75000) THEN
        SET incomeTax = 10*salary;
    ELSEIF (salary > 75000 and salary <= 150000) THEN
        SET incomeTax = 20*salary;
    ELSEIF (salary > 150000) THEN
        SET incomeTax = 30*salary;
    END IF;
    SET incomeTax = incomeTax/100;
    RETURN incomeTax;
END //

DELIMITER ;
SELECT *, calcTax(salary) FROM worker;

DELIMITER //
CREATE PROCEDURE calculateIncomeTax(IN salary INT,OUT incomeTax DECIMAL(10,2))
BEGIN
SET incomeTax = calcTax(salary);
END//

DELIMITER ;
CALL calculateIncomeTax(100000,@incomeTax);
SELECT @incomeTax;

0 replies

rakeshGit419 · 2022-03-17T09:15:28Z

rakeshGit419
Mar 17, 2022

USE org;
--1st
DELIMITER &&
CREATE FUNCTION `computeTax`(salary INT) RETURNS DECIMAL(10,2)
    DETERMINISTIC
BEGIN
	DECLARE incomeTax DECIMAL(10,2);
    IF salary<75000 THEN
		SET incomeTax = salary*0.1;
	ELSEIF salary>75000 AND salary<=150000 THEN
		SET incomeTax = salary*0.2;
	ELSE
		SET incomeTax = salary*0.3;
	END IF;
    RETURN (incomeTax);
END &&

DELIMITER ;

SELECT *,computeTax(salary) as computedTax from worker;


-- 2nd
DELIMITER &&
CREATE PROCEDURE calculateIncomeTax(IN salary INT,OUT incomeTax DECIMAL(10,2))
BEGIN
	SET incomeTax = compareTax(salary);
END &&
DELIMITER ;
CALL calculateIncomeTax(150000,@incomeTax);
SELECT @incomeTax;

0 replies

shreiyarandive · 2022-03-17T09:16:56Z

shreiyarandive
Mar 17, 2022

DELIMITER //
CREATE FUNCTION computeTax(salary int)RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN
DECLARE incomeTax DECIMAL(10,2);
IF salary<=75000 THEN SET incomeTax=0.1*salary; 
ELSEIF salary>75000 and salary<=150000 THEN SET incomeTax=0.2*salary;
ELSE SET incomeTax=0.3*SALARY;
END IF;
RETURN incomeTax;
END //
DELIMITER ;

select *, computeTax(salary) as computedTax from employees;




DELIMITER //
CREATE PROCEDURE CALCULATEINCOMETAX(IN SALARY INT,OUT INCOMETAX DECIMAL(10,2))
    -> BEGIN
    -> SET INCOMETAX=computeTax(SALARY);
    -> END //
DELIMITER ;

call calculateIncomeTax(75600, @INCOMETAX);
select @INCOMETAX;

0 replies

ujjwal18bit1167 · 2022-03-17T09:22:57Z

ujjwal18bit1167
Mar 17, 2022

--1
DELIMITER //
CREATE Function computeTax(salary int)
RETURNS DECIMAL(10,2)
DETERMINISTIC 

BEGIN
    DECLARE incomeTax Decimal(10,2);
    IF salary <= 75000 THEN
        SET incomeTax = 0.1 * salary;
    ELSEIF salary > 75000 and salary <= 150000 THEN
        SET incomeTax = 0.2 * salary;
    ELSE 
        SET incomeTax = 0.3 * salary;
    END IF;

    RETURN incomeTax;
END //

DELIMITER ;


select *, computeTax(salary) from worker;




--2
DELIMITER $$
CREATE Procedure calculateIncomeTax(IN salary INT , OUT incomeTax decimal(10,2))
BEGIN 
    set incomeTax = computeTax(salary);
END $$

delimiter ;
call calculateIncomeTax(25000,@INCOMETax_var);
select @incomeTax_var;

0 replies

RohitN09 · 2022-03-17T09:25:36Z

RohitN09
Mar 17, 2022

DELIMITER //

CREATE FUNCTION computeTax(
    salary INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC 
BEGIN

    DECLARE tax DECIMAL(10,2);

    IF salary <= 75000 THEN
        SET tax = 0.1*salary;
    ELSEIF salary > 75000 AND salary <= 150000 THEN
        SET tax = 0.2*salary;
    ELSE
        SET tax = 0.3*salary;
    END IF;
    RETURN tax;
END //

DELIMITER ;

select *, computeTax(salary) from worker;

DELIMITER //

CREATE PROCEDURE calculateIncomeTax(
    IN salary INT, OUT tax DECIMAL(10,2)
)
BEGIN
    SET tax = computeTax(salary);
END//

DELIMITER ;

CALL calculateIncomeTax(50000, @tax);
SELECT @tax;

0 replies

hemanth409 · 2022-03-17T09:52:01Z

hemanth409
Mar 17, 2022

1

DELIMITER &&
CREATE FUNCTION computeTax(salary INT) RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE incomeTax DECIMAL(10,2);
IF salary<75000 THEN
SET incomeTax = salary0.1;
ELSEIF salary>75000 AND salary<=150000 THEN
SET incomeTax = salary0.2;
ELSE
SET incomeTax = salary*0.3;
END IF;
RETURN (incomeTax);
END &&

DELIMITER ;

SELECT *,computeTax(salary) as computedTax from worker;

2

DELIMITER &&
CREATE PROCEDURE calculateIncomeTax(IN salary INT,OUT incomeTax DECIMAL(10,2))
BEGIN
SET incomeTax = compareTax(salary);
END &&
DELIMITER ;
CALL calculateIncomeTax(150000,@incometax);
SELECT @incometax;

0 replies

vshvandana · 2022-07-05T10:58:43Z

vshvandana
Jul 5, 2022

delimiter //
create function computetax1(salary int)
Returns decimal(10,2) 
DETERMINISTIC
BEGIN 
DECLARE incometax decimal(10,2);

IF salary <= 75000 then 
set incometax= 0.1*salary;

elseif (salary> 75000 and salary <=15000) then
set incometax= 0.2*salary;

else 
set incometax= 0.3*salary;
 
END IF;
return incometax;
END //
delimiter ;
select * , computetax1(salary) from worker ;


delimiter //
create Procedure tax(in salary int, out incometax decimal(10,2))
BEGIN
set incometax= computetax1(salary);
end //
delimiter;
call tax(900000,@incometax);
select @incometax;

0 replies

Coder-093 · 2022-07-05T11:01:37Z

Coder-093
Jul 5, 2022

--1
DELIMITER  / / 

CREATE FUNCTION computingTax(salary int) 
RETURNS decimal(10, 2)
DETERMINISTIC 

BEGIN
   DECLARE incomeTax decimal(10, 2);
   IF salary <= 75000 THEN
      SET incomeTax = (salary * 0.1);
   ELSEIF ( salary <= 150000 AND salary > 75000 ) THEN
      SET incomeTax = (salary * 0.2);
   ELSE
      SET incomeTax = (salary * 0.3);
   END IF;
   RETURN incomeTax;
END / / 

DELIMITER ;
select worker_id, first_name, salary, computingTax(salary)
from worker order by first_name;

SHOW FUNCTION STATUS where db = 'org';


--2
Delimiter &&

CREATE PROCEDURE getTax( 
  IN sal INT, OUT IncTax DECIMAL(10,2)
) 

BEGIN
  SET IncTax = computingTax(sal);    
END &&

DELIMITER ;

CALL getTax(50000, @IncTax);

select @IncTax;

SHOW CREATE PROCEDURE getTax;

SHOW PROCEDURE STATUS LIKE 'getTax';

0 replies

meghvi-raj · 2022-07-05T11:22:55Z

meghvi-raj
Jul 5, 2022

--Q1
DELIMITER &&

CREATE FUNCTION computeTax(salary int)
RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN
DECLARE incomeTax DECIMAL(10,2);
IF salary<=75000 THEN SET incomeTax=0.1*salary; 
ELSEIF salary>75000 and salary<=150000 THEN SET incomeTax=0.2*salary;
ELSE SET incomeTax=0.3*SALARY;
END IF;
RETURN incomeTax;
END &&

DELIMITER ;

select computeTax(salary) from worker;

--Q2

DELIMITER &&

CREATE PROCEDURE calculateIncomeTax(IN salary INT, OUT incomeTax DECIMAL(10,2))
BEGIN
SET incomeTax= computeTax(salary);
END && 

DELIMITER ;

CALL calculateIncomeTax(80000, @incomeTax);

SELECT @incomeTax;

0 replies

Kuntapaliwal · 2022-07-06T04:45:30Z

Kuntapaliwal
Jul 6, 2022

--1.  Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

-- 10% - salary <= 75000
-- 20% - 75000 < salary <= 150000
-- 30% - salary > 150000
-- Write a query that displays all the details of a worker including their computedTax.

DELIMITER //
create FUNCTION computeTax (salary int) RETURNS int deterministic BEGIN
declare tax int;
if salary <= 75000 then
set tax = salary *.1;
ELSEIF salary > 75000
AND salary <= 150000 then
SET tax = salary *.2;
else
SET tax = salary *.3;
END if;
return tax;
END //

DELIMITER ;

SELECT * ,computeTax(76000)FROM WORKER; 




--2. Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

delimiter //
create procedure calIncomeTax(IN salary int, out incomeTax decimal(10, 2))
begin
set incomeTax = computeTax(salary);
end //

delimiter;
call calIncomeTax(5000, @income);

select @income;

0 replies

ghost · 2022-07-06T04:52:19Z

ghost
Jul 6, 2022

--1.
DELIMITER;
CREATE FUNCTION incomeTax(salary INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE incometax DECIMAL(10,2);
    IF( salary<75000) then
        SET incometax= salary*.10;
    ELSEIF (salary>75000 and salary<100000) then
    SET incometax= salary*.20;
    else
    SET incometax= salary*.30;
    end if;
                RETURN incometax;
    end //
    DELIMITER;
    select *, incomeTax(salary) as incomeTax from worker;

--2.
DELIMITER //
CREATE PROCEDURE computeIncomeTax(IN salary INT, OUT incometax DECIMAL(10,2))
BEGIN
SET incometax=incomeTax(salary);
END //
DELIMITER ;
 call computeIncomeTax(500000,@result);
 select @result;

0 replies

anuj9604 · 2023-07-27T05:10:32Z

anuj9604
Jul 27, 2023

1. Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

. 10% - salary <= 75000

. 20% - 75000 < salary <= 150000

. 30% - salary > 150000

Write a query that displays all the details of a worker including their computedTax.

Solution

delimiter $$

create function tax(
salary int
)
returns int
deterministic
begin
	declare tax int;
	
	if (salary<=75000) then
		set tax=0.10*salary;
	elseif (salary>75000 and salary<=150000) then
		set tax=0.20*salary;
	else
		set tax=0.30*salary;
	end if;

	return (tax);
end $$

create procedure computeTax ()
begin
	select *, tax(worker.salary) as TAX
	from worker;
end $$

delimiter ;

call computeTax();

2. Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

Solution

delimiter $$

create procedure tax_of_salary (
in salary int,
out tax_ int
)
begin
	set tax_ = tax(salary);
end $$

delimiter ;

call tax_of_salary(200000, @tax_);
select @tax_;

0 replies

niktwister · 2023-07-27T06:15:14Z

niktwister
Jul 27, 2023

1.Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

10% - salary <= 75000
20% - 75000 < salary <= 150000
30% - salary > 150000
Write a query that displays all the details of a worker including their computedTax.

DELIMITER $$

CREATE FUNCTION IF NOT EXISTS computeTax(salary INT)
RETURNS DECIMAL
DETERMINISTIC
BEGIN
    
    CASE
    WHEN salary <= 75000 THEN 
        RETURN salary * 0.1;
    WHEN salary <= 150000 THEN 
        RETURN salary * 0.2;
    WHEN salary > 150000 THEN 
        RETURN salary * 0.3;
    END CASE;
    
    
END $$

DELIMITER ;

SELECT * , computeTax(salary) AS computedTax
FROM worker;

2.Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure).

DELIMITER $$
    
CREATE PROCEDURE IF NOT EXISTS incomeTax(IN salary INT,OUT tax INT)
BEGIN

    SET tax = computeTax(salary);
    
END $$

DELIMITER ;

CALL incomeTax(70000,@result);
SELECT @result;

0 replies

apoorva-sin · 2023-07-27T06:21:04Z

apoorva-sin
Jul 27, 2023

-- Discussion Forum 13 Part 1

use org;

delimiter $$

create function computeTax(
	salary int
)
returns decimal(10,2)
deterministic

begin
	declare ans decimal(10, 2);
	set ans = case when salary <= 75000 then 0.1*salary 
	when salary between 75000 and 150000 then 0.2*salary
	else 0.3 * salary
	end;

	return ans;
end $$

delimiter ;

select distinct FIRST_NAME, LAST_NAME, SALARY, 
	computeTax(salary) as INCOME_TAX, 
	JOINING_DATE, DEPARTMENT, WORKER_TITLE, AFFECTED_FROM, BONUS_AMOUNT, BONUS_DATE from worker w
	left join title t on w.WORKER_ID = t.WORKER_REF_ID
	left join bonus b on w.WORKER_ID = b.WORKER_REF_ID;
    
-- Discussion Forum 13 Part 2

drop procedure storedTax;

delimiter $$

create procedure storedTax(
	in salary decimal(10, 2),
    out ans decimal(10, 2)
)

begin
	select computeTax(salary) into ans;
end $$

delimiter ;

call storedTax(10000, @tax);
select @tax;

0 replies

prathameshyadure · 2023-07-27T06:21:07Z

prathameshyadure
Jul 27, 2023

drop function computedTax;
delimiter $$
create function computedTax(salary int)
returns int
deterministic
begin
if salary <= 75000 then
return (salary0.1);
elseif salary <= 150000 then
return (salary0.2);
else
return (salary*0.3);
end if;
end$$
delimiter ;

select *,computedTax(salary) as incomeTax from worker;

-- Q2
drop procedure computeTax;
delimiter $$
create procedure computeTax(in salary int, out incomtax int)
begin
select computedTax(salary) into incomtax;
end $$
delimiter ;

call computeTax(160000, @incomtax);
select @incomtax;

0 replies

ashumore1998 · 2023-07-27T06:21:57Z

ashumore1998
Jul 27, 2023

/* 1 Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

10% - salary <= 75000
20% - 75000 < salary <= 150000
30% - salary > 150000
Write a query that displays all the details of a worker including their computedTax.

*/

DELIMITER //
create function computeTax( sal decimal(15,3) )
returns decimal(15,3)
deterministic
begin
declare tax decimal(10,2) default 0;

if sal <=75000 then set tax = sal*0.1;
elseif (sal >75000 and sal<=150000) then set tax = sal*0.2;
else set tax = sal*0.3;
end if;
return tax;

end;
//

delimiter ;

select *,computeTax(SALARY) as Tax from worker;

--2 Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

DELIMITER //
create procedure compute_tax_proc(in sal int,out result varchar(10))
begin
set result = computeTax(sal);
end ;
//
delimiter ;

call compute_tax_proc(1234567,@tax_out);
select @tax_out as Results;

0 replies

ManasFuturense · 2023-07-27T06:22:03Z

ManasFuturense
Jul 27, 2023

-- 1. Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

-- 10% - salary <= 75000
-- 20% - 75000 < salary <= 150000
-- 30% - salary > 150000
-- Write a query that displays all the details of a worker including their computedTax.

DELIMITER $$
CREATE FUNCTION computeTax(salary int)
RETURNS FLOAT
DETERMINISTIC
BEGIN
DECLARE tax FLOAT;
IF salary <= 75000 THEN
SET tax = salary * 10 / 100;
ELSEIF salary > 75000 AND salary <= 150000 THEN
SET tax = salary * 20 / 100;
ELSE
SET tax = salary * 30 / 100;
END IF;
RETURN tax;
END $$
DELIMITER ;

SELECT worker_ID, first_name, last_name, salary, joining_date, department, computeTax(salary) AS computedTax
FROM worker;

SHOW FUNCTION STATUS WHERE db = 'org';

-- 2. Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

DELIMITER $$
CREATE PROCEDURE computeTaxProcedure(IN salary INT, OUT tax FLOAT)
BEGIN
SET tax = computeTax(salary);
END $$
DELIMITER ;

CALL computeTaxProcedure(20000, @output);
SELECT @output;

0 replies

dhanyaMeruva9 · 2023-07-27T06:39:04Z

dhanyaMeruva9
Jul 27, 2023

--1
delimiter $$
CREATE FUNCTION computeTax(salary int)RETURNS DECIMAL(10,2) DETERMINISTIC BEGIN
DECLARE incomeTax DECIMAL(10,2);
IF salary<=75000 THEN SET incomeTax=0.1salary;
ELSEIF salary>75000 and salary<=150000 THEN SET incomeTax=0.2salary;
ELSE SET incomeTax=0.3*SALARY;
END IF;
RETURN incomeTax;
END $$
DELIMITER ;
SELECT * ,computeTax(salary)FROM WORKER;

--2
delimiter $$
CREATE PROCEDURE CALCULATEINCOMETAX(IN SALARY INT,OUT INCOMETAX DECIMAL(10,2))
BEGIN
SET incometax=computeTax(salary);
END $$
delimiter ;
CALL CALCULATEINCOMETAX(100000,@incometax);
select @incometax;

0 replies

Shubham-Futurense · 2023-07-27T06:40:29Z

Shubham-Futurense
Jul 27, 2023

#1
DELIMITER $$
CREATE FUNCTION computeTax(salary INT)
RETURNS DECIMAL(9,2)
DETERMINISTIC
BEGIN
DECLARE tax DECIMAL(11,2);
IF salary<=75000 THEN
SET tax=salary0.10;
ELSEIF salary>150000 THEN
SET tax=salary0.30;
ELSE
SET tax=salary*0.20;
END IF;
RETURN tax;

END;
$$

DELIMITER ;

SELECT *, computeTax(salary) FROM worker;

#2
DELIMITER $$
create procedure tax(IN inp INT, OUT tax DECIMAL(9,2))
BEGIN
SET tax=computeTax(inp);

END
$$

DELIMITER ;

call tax(100000,@tax);
select @tax;

0 replies

var5 · 2023-07-27T19:14:48Z

var5
Jul 27, 2023

Write a stored function called computeTax that calculates income tax based on the salary for
every worker in the Worker
table as follows:
 10% - salary <= 75000
 20% - 75000 < salary <= 150000
 30% - salary > 150000
 Write a query that displays all the details of a worker including their computedTax.

DELIMITER //
CREATE FUNCTION computeTax(salary INT) RETURNS DECIMAL(10, 2)
DETERMINISTIC
BEGIN
DECLARE tax DECIMAL(10, 2);
IF salary <= 75000 THEN
SET tax = salary * 0.10;
ELSEIF salary > 75000 AND salary <= 150000 THEN
SET tax = salary * 0.20;
ELSE
SET tax = salary * 0.30;
END IF;
RETURN tax;
END //
DELIMITER;
SELECT *, computeTax(salary) AS INCOME_TAX FROM worker;

Define a stored procedure that takes a salary as input and returns the calculated income tax
amount for the input salary.
Print the computed tax for an input salary from a calling program. (Hint - Use the
computeTax stored function inside the stored procedure)

DELIMITER ||
CREATE PROCEDURE CalculateIncomeTax(IN salary INT, OUT tax DECIMAL(10,
2))
BEGIN
SET tax = computeTax(salary);
END ||
DELIMITER ;
CALL CalculateIncomeTax(80000,@tax);
SELECT @tax;

0 replies

NANDU-R-N · 2023-07-31T08:15:29Z

NANDU-R-N
Jul 31, 2023

Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

-- 10% - salary <= 75000
-- 20% - 75000 < salary <= 150000
-- 30% - salary > 150000
-- Write a query that displays all the details of a worker including their computedTax.

DELIMITER $$

CREATE FUNCTION computeTax(
salary INT
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
DECLARE tax DECIMAL(10,2);
IF salary <= 75000 THEN
SET tax = salary * .1;
ELSEIF (salary > 75000 AND salary <= 15000) THEN
SET tax = salary * .2;
ELSE
SET tax = salary *.3;
END IF;
RETURN tax;
END $$

SELECT worker_id,first_name,computeTax(salary) AS tax FROM worker;

2 Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

DELIMITER //
create procedure compute_tax_procedure(in salary int,out result varchar(10))
begin
set result = computeTax(salary);
end ;
//
delimiter ;

call compute_tax_procedure(1234567,@tax_output);
select @tax_output as Results;

0 replies

Task - Stored Functions - org DB #13

Uh oh!

Uh oh!

akash-coded Mar 17, 2022 Maintainer

Database = org

Dump file: org.sql

DB Schema

Calculating Income Tax

Replies: 41 comments

Uh oh!

Uh oh!

Uh oh!

1

2

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Calculating Income Tax

Uh oh!

1.

2.

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

1. Write a stored function called computeTax that calculates income tax based on the salary for every worker in the Worker table as follows:

. 10% - salary <= 75000

. 20% - 75000 < salary <= 150000

. 30% - salary > 150000

Write a query that displays all the details of a worker including their computedTax.

2. Define a stored procedure that takes a salary as input and returns the calculated income tax amount for the input salary. Print the computed tax for an input salary from a calling program. (Hint - Use the computeTax stored function inside the stored procedure)

Uh oh!

Uh oh!

Uh oh!

Uh oh!

Uh oh!

akash-coded
Mar 17, 2022
Maintainer

Database = `org`