SQL Project ScienceQtech Employee Performance Mapping...
SQL Project ScienceQtech Employee Performance Mapping...
SQL Project ScienceQtech Employee Performance Mapping...
Date 12-09-2023
PB BA - SQL
By Swati Sangal
Q1. 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.
----------------------------------------------------------------------------------------------------------------
Q5. 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.
where dept="Finance"
Q6.Write a query to list only those employees who have someone reporting to them. Also, show the
number of reporters (including the President).
on et1.emp_id=et2.manager_id
where dept="healthcare"
union
where dept="finance"
Q8 .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.
from emp_record_table
order by dept
Q9. Write a query to calculate the minimum and the maximum salary of the employees in each role.
Take data from the employee record table.
group by role
Q10. Write a query to assign ranks to each employee based on their experience. Take data from the
employee record table.
WHERE SALARY>6000;
where exp>10) as B
Q13 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 &&
BEGIN
END &&
CALL get_experience_details()
Q14. 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.
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',
from emp_record_table
Q15 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.
where first_name="Eric"
Q16 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).