[go: up one dir, main page]
Scribd
Upload
103 views4 pages

CEP 1 Employee Performance Mapping Problem Statement

The document describes a problem scenario for ScienceQtech, a startup in the data science field, where the HR department needs to generate reports on employee performance and projects to help with employee appraisals. It provides details on the employee and manager tables that need to be queried to return information on employee details, department averages, salary ranges by role, and bonuses calculated based on rating and salary. The tasks involve importing data, creating the database, running various queries to return the requested employee and department information, and calculating statistics.

Uploaded by

Dania Alhrbi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
103 views4 pages

CEP 1 Employee Performance Mapping Problem Statement

The document describes a problem scenario for ScienceQtech, a startup in the data science field, where the HR department needs to generate reports on employee performance and projects to help with employee appraisals. It provides details on the employee and manager tables that need to be queried to return information on employee details, department averages, salary ranges by role, and bonuses calculated based on rating and salary. The tasks involve importing data, creating the database, running various queries to return the requested employee and department information, and calculating statistics.

Uploaded by

Dania Alhrbi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 4

MySQL

Course-End Project Problem Statement


ScienceQtech Employee Performance Mapping

Problem scenario:

ScienceQtech is a startup that works in the Data Science field. ScienceQtech has

worked on fraud detection, market basket, self-driving cars, supply chain,

algorithmic early detection of lung, customer sentiment, and drug discovery field.

With the annual appraisal cycle around the corner, the HR department has asked

you (Junior Database Administrator) to generate reports on employee details, their

performance, and on the project that the employees have undertaken, to analyze

the employee database and extract specific data based on different requirements.

Objective:

To facilitate a better understanding, managers have provided ratings for each

employee which will help the HR department to finalize the employee performance

mapping. As a DBA, you should find the maximum salary of the employees and

ensure that all jobs are meeting the organization's profile standard. You should also

determine whether or not employees need a promotion, and calculate bonuses to

find extra cost for expenses. This will raise the overall performance of the

organization by ensuring that all required employees receive training.


Dataset description:

Employee Table: contains the information of all the employees.


● EMP_ID – ID of the employee
● FIRST_NAME – First name of the employee
● LAST_NAME – Last name of the employee
● GENDER – Gender of the employee
● ROLE – Post of the employee
● DEPT – Field of the employee
● EXP – Years of experience the employee has
● COUNTRY – Country in which the employee is presently living
● CONTINENT – Continent in which the country is
● SALARY – Salary of the employee
● EMP_RATING – Performance rating of the employee
● MANAGER_ID – The manager under which the employee is assigned

The task to be performed:

First Steps:
1. Create a database: create a database called "CEP1"
2. Then import the file: "employee.csv", into it
3. Draw an ER Diagram that visualizes the relationships between the tables in
the CEP1 database

Employee Information:
3. List employees’ details, including their departments.
4. Select and filter employees by experience based on specific ranges:
o Less than 2
o Between 2 and 4 (inclusive)
o Greater than 4
5. Add a new column “FULL_NAME” that combines “FIRST_NAME” and
“LAST_NAME” (separated by a space).

Managerial and Departmental Queries:


6. List managers’ details, including how many employees report to them.
7. List employees who belong to either the “HEALTHCARE” or “FINANCE”
departments. (However: use UNION instead of OR)
8. List departments with their average rating, and number of employees in
them.

Salary and Location Analysis:


9. Salary range by role: Find the minimum and maximum salaries for each role
in the employee table.
10. Create a view for employees whom salary is above $6,000 and are from Aisa.

Advanced Queries:
11. Use a nested query to list employees who have above average experience.
12. Create an index on FIRST_NAME to speed up the database queries.
13. Calculate employee bonuses: Based on their rating and salary, calculate a
bonus for each employee using the formula: 5% of salary multiplied by
employee rating.

Statistics and Aggregation:


14. Average salary by continent and country: Calculate the average salary
distribution for each country and continent based on the data in the
employee record table.

You might also like