ScienceQtech Employee Performance Mapping.sql

#Create a database named employee, then import data_science_team.csv proj_table.csv and emp_record_table.csv into the employee database from the given resources.
create database employee;
use employee;
#imported emp_record_table.csv , data_science_team.csv , proj_table.csv using Table Data Import Wizard option
select *from emp_record_table ;
select *from data_science_team ;
select *from proj_table ;


#Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, and DEPARTMENT from the employee record table, and make a list of employees and details of their department.
select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, dept  from emp_record_table ;

#Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPARTMENT, and EMP_RATING if the EMP_RATING is: less than two
select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, dept , emp_rating  from emp_record_table where emp_rating =2 ;

#Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPARTMENT, and EMP_RATING if the EMP_RATING is: greater than four
select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, dept , emp_rating  from emp_record_table where emp_rating >4 ;

#Write a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPARTMENT, and EMP_RATING if the EMP_RATING is: between two and four
select EMP_ID, FIRST_NAME, LAST_NAME, GENDER, dept , emp_rating  from emp_record_table where emp_rating >2 and emp_rating <4 ;

#Write a query to concatenate the FIRST_NAME and the LAST_NAME of employees in the Finance department from the employee table and then give the resultant column alias as NAME.
select concat(FIRST_NAME,'', last_name) as Name from emp_record_table where dept = 'Finance';

#Write a query to list only those employees who have someone reporting to them. Also, show the number of reporters (including the President).
select m.first_name , m.last_name , count(r.emp_id) as "Number of reporting" from emp_record_table m , emp_record_table r
where m.emp_id =r.manager_id group by m.emp_id ;

#Write a query to list down all the employees from the healthcare and finance departments using union. Take data from the employee record table.
select *from emp_record_table where dept = "finance" union
select *from emp_record_table where dept = "healthcare" ;

#Write a query to list down employee details such as EMP_ID, FIRST_NAME, LAST_NAME, ROLE, DEPARTMENT, and EMP_RATING grouped by dept. Also include the respective employee rating along with the max emp rating for the department.
select emp_id , first_name , last_name , role , dept , emp_rating , max(emp_rating) over(partition by dept) as Max_emp_rating from emp_record_table ;

#Write a query to calculate the minimum and the maximum salary of the employees in each role. Take data from the employee record table
select role , max(salary) as max_salary , min(salary) as min_salary from emp_record_table group by role ;

#Write a query to assign ranks to each employee based on their experience. Take data from the employee record table.
select first_name , last_name ,exp ,  dense_rank() over(order by exp desc) as exprience_rank from emp_record_table ;

#Write a query to create a view that displays employees in various countries whose salary is more than six thousand. Take data from the employee record table.
create   view  view_salary as select emp_id , first_name , last_name , country , salary , 
dense_rank() over(order by salary desc) as salary_rank from
emp_record_table where salary > 6000  ;
select  *from view_salary ;

#Write a nested query to find employees with experience of more than ten years. Take data from the employee record table.
select *from emp_record_table where exp = (select max(exp) from emp_record_table) ;

#Write a query to create a stored procedure to retrieve the details of the employees whose experience is more than three years. Take data from the employee record table.
delimiter //
create procedure 3_experience()
begin
select * from emp_record_table
where exp>3
order by exp;
end //
delimiter ;
call 3_experience();

#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'.

delimiter &&
create  function job_profile(exp int , role varchar(25)) 
returns varchar(20) deterministic
begin 
declare profile_check varchar(20);
if (exp<=2 and role = 'JUNIOR DATA SCIENTIST')
then set profile_check = "correct" ;
elseif (exp>2 and exp <=5 and role ='ASSOCIATE DATA SCIENTIST' )
then set profile_check = "correct" ;
elseif (exp>5 and exp<=10 and role = 'SENIOR DATA SCIENTIST')
then set profile_check = "correct" ;
elseif (exp>10 and exp<=12 and role = 'LEAD DATA SCIENTIST')
then set profile_check = "correct" ;
elseif (exp >12 and exp<=16 and role = "Manager")
then set profile_check = "correct" ;
else  set profile_check = "not correct" ;
end if ;
return(profile_check) ;
end &&

select first_name , last_name , exp , role , job_profile(exp, role) from data_science_team ;

#Create an index to improve the cost and performance of the query to find the employee whose FIRST_NAME is ‘Eric’ in the employee table after checking the execution plan.

create index index_first_name on emp_record_table(first_name) ;
select *from emp_record_table where first_name = "eric" ;

#Write a query to calculate the bonus for all the employees, based on their ratings and salaries (Use the formula: 5% of salary * employee rating).

select first_name , last_name , salary  , (0.05*salary*emp_rating) as "Bonus" from emp_record_table ;

#Write a query to calculate the average salary distribution based on the continent and country. Take data from the employee record table.

select continent , avg(salary) as "avergae salary continent" from emp_record_table group by continent ;

select country , avg(salary) as "average country salary " from emp_record_table group by country ;