I have generated comprehensive reports detailing employee information, performance metrics, and project specifics, facilitating in-depth analysis of our employee database. These reports are customized to extract specific data tailored to various requirements and insights needed for strategic decision-making.
Created a database named employee, then inserted values from data_science_team.csv proj_table.csv and emp_record_table.csv into the employee database from the given resources.
Created an ER diagram for the given employee database.
Wrote a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, and DEPARTMENT from the employee record table, and made a list of employees and details of their department.
Wrote a query to fetch EMP_ID, FIRST_NAME, LAST_NAME, GENDER, DEPARTMENT, and EMP_RATING if the EMP_RATING was: * less than two * greater than four * between two and four
Wrote a query to concatenate the FIRST_NAME and the LAST_NAME of employees in the Finance department from the employee table and then gave the resultant column alias as NAME.
Wrote a query to list only those employees who have someone reporting to them. Also, showed the number of reporters (including the President).
Wrote a query to list down all the employees from the healthcare and finance departments using union.
Wrote a query to list down employee details such as EMP_ID, FIRST_NAME, LAST_NAME, ROLE, DEPARTMENT, and EMP_RATING grouped by dept. Also included the respective employee rating along with the max emp rating for the department.
Wrote a query to calculate the minimum and the maximum salary of the employees in each role.
Wrote a query to assign ranks to each employee based on their experience.
Wrote a query to create a view that displays employees in various countries whose salary is more than six thousand.
Wrote a nested query to find employees with experience of more than ten years.
Wrote a query to create a stored procedure to retrieve the details of the employees whose experience is more than three years.
Wrote 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'
Created 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.
Wrote a query to calculate the bonus for all the employees, based on their ratings and salaries (Using the formula: 5% of salary * employee rating).
Wrote a query to calculate the average salary distribution based on the continent and country.