Can anyone help me with the code?

Copper Contributor

Write a query using stored functions in the project table to check whether the job profile assigned to each employee in the data science team matches the organization’s set standard.

 

The standard being:

For an employee with experience less than or equal to 2 years assign 'JUNIOR DATA SCIENTIST',

For an employee with the experience of 2 to 5 years assign 'ASSOCIATE DATA SCIENTIST',

For an employee with the experience of 5 to 10 years assign 'SENIOR DATA SCIENTIST',

For an employee with the experience of 10 to 12 years assign 'LEAD DATA SCIENTIST',

For an employee with the experience of 12 to 16 years assign 'MANAGER'.

2 Replies

Hi @Jayaprakash116,

 

I dont know if it will work but below is my Code 

DELIMITER &&
CREATE FUNCTION EMPLOYEE_ROLE_MATCH(
EXP INT
)
RETURNS VARCHAR(50)
DETERMINISTIC
BEGIN
DECLARE STANDARD_ROLE VARCHAR(50);
IF EXP < 2 THEN SET STANDARD_ROLE ='JUNIOR DATA SCIENTIST';
ELSEIF ( EXP >=2 and EXP <5) THEN SET STANDARD_ROLE ='ASSOCIATE DATA SCIENTIST';
ELSEIF ( EXP >=5 and EXP <10) THEN SET STANDARD_ROLE ='SENIOR DATA SCIENTIST';
ELSEIF ( EXP >=10 and EXP <12) THEN SET STANDARD_ROLE ='LEAD DATA SCIENTIST';
ELSEIF ( EXP >=12 and EXP <16) THEN SET STANDARD_ROLE ='MANAGER';
END IF;
RETURN (STANDARD_ROLE);
END &&

 

select EMP.EMP_ID,DS.ROLE,EMP.EXP,EMPLOYEE_ROLE_MATCH(EMP.EXP),
from emp_record_table EMP
JOIN data_science_team DS
ON EMP.EMP_ID=DS.EMP_ID
;

 

if it works please like the answer

@VBA_ENTHU1988 

Code is fine just a slight change, instead of using Varchar(50) use Varchar(255) in the entire program.