Forum Discussion
Can anyone help me with the code?
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
Code is fine just a slight change, instead of using Varchar(50) use Varchar(255) in the entire program.
