Assumptions/Notes About Data Entities and Relationships:
The database is divided into 14 tables: some directly partitioning data and others describing it.
The employees table is one to one related to salaries, work experience, education, department roles and employee review. Salaries table is many to one related to performanceandsalary table. Every PerformanceRating from performanceandsalary has many PercentSalaryHike numerical values in the Salaries table. The performanceandsalary table is one to one related to performancerating. The Employee Review table is one to one related to jobinvolvement, jobsatisfaction, environmentsatisfaction, worklifebalance and relationshipsatisfaction.
The column Over18 has been removed because whether an employee is aged over 18 or not can be determined directly using the Age column. Also, the column EmployeeCount has been omitted from the database because the row-wise count of employee will be 1 (the entry in all rows of this column).
Assumptions/Notes About Data Set
1. All static columns pertaining to an employee belong to employees table
2. Attrition implies that the particular person is no more employed
3. Any person rejoining the company would have a different EmployeeNumber and his/his details would be listed as any new employee’s would, i.e., YearsAtCompany and YearsWithCurrManager will be calculated with respect to this position and this EmployeeNumber. NumCompaniesWorked will also include this company.
4. TrainingTimesLastYear are the number of trainings the employee has undertaken with this company in the last year
5. All salary details are present in Salaries table (including StockOptionLevel, which is part of salary for some employees)
6. All employees are paid monthly salaries. The hourly rate, daily rate and monthly rate are the rates that were paid to contractors that are not on the company payroll but recruited on the same day. The contract employees are not listed here.
7. All job-related experience, including job level and role in the current company, can be varying and is hence considered as part of Work Experience table
8. Every department has specific roles in it, which do not overlap with any other department
9. For employees with rating = 3 salary hike is < 20% while those with rating = 4 get a hike of ≥ 20%
10. Employees have been asked to rate their experiences at work and in personal life which are considered as Employee Review.
SQL Queries
Question: The company has decided to focus on cost cutting to improve their bottom line in a difficult economy. Should the company focus on Business Travel? Why or Why not?
Notes/Comments About SQL Query and Results (Include # of Rows in Result):
1. We consider just the currently employed people for this analysis because cost cutting can only be done on the currently employed
2. Assuming that the budget for every employee’s travel is the same ($100)
3. Assuming that the employees travelling frequently travel once a month and employees travelling rarely travel once every quarter
4. Assuming that only frequent and rare travelers like traveling for business purpose and non-travelers don’t like to
5. The output of the query contains 3 rows, one for each category of business travelers. We are populating the total number of employees, percentage of these employees that are highly satisfied and percentage of these highly satisfied employees that have high performance.
6. We find that frequent travelers have highest satisfaction and non- travelers have highest performance
7. For the sake of simplicity if we consider that every employee spends
$100 on every trip then the frequent travelers will be spending 208 x 3 x 100 = $62,400 in a quarter while the rare travelers spend 887 x 100
= $88,700 in a quarter. That means, we spend $151,100 per quarter on travel.
8. If frequent travelers are made to travel just once a quarter, their satisfaction level may drop but there may not be a lot of difference in their performance because that is directly related to the percent of salary hike they will receive. Additionally, travel expenditure may drop to (887 + 208) x 100 = $109,500 per quarter, saving 151100 – 109500
= $41,600 per quarter
9. On the flip side, the satisfaction level of non-travelers will increase, if they
10. So, we suggest that any employee traveling for business purpose can do so only once per quarter and yes, business travel is an important factor that the company must focus on for cost cutting
Select business travel categories, count of employees from work experience table where employees belong to Attrition = ’No‘ joined with select list of percentage of employees from work experience and employee review table for those who have job satisfaction = 4 and have Attrition = ’No‘ grouped by business travel categoories joined with select list of percentage of employees from work experience, employee review and salaries tables for those who have job satisfaction = 4, Attrition = ’No‘ and PercentSalaryHike >= 20 (signifying performance = 4 which is high) grouped by the business travel categories
Screen Shot of SQL Query and Results:
select p.BusinessTravel, `Count Employees`, `Percent Satisfied employees`,
`Percent High Performance Satisfied employees` from
(select BusinessTravel, count(EmployeeNumber) as `Count Employees` from `work experience`
where EmployeeNumber in (select EmployeeNumber from employees where Attrition = 'No')
group by BusinessTravel) as p join
(select x.BusinessTravel, round((`Satisfied employees`/`Total Satisfied employees`)*100, 2) as `Percent Satisfied employees`
from
(select BusinessTravel, count(we.EmployeeNumber) as `Satisfied employees` from `work experience` as we
join `employee review` as er
on we.EmployeeNumber = er.EmployeeNumber
where we.EmployeeNumber in (select EmployeeNumber from employees where Attrition = 'No') and JobSatisfaction = 4
group by BusinessTravel) as x
join
(select BusinessTravel, count(we.EmployeeNumber) as `Total Satisfied employees`
from `work experience` as we join `employee review` as er
on we.EmployeeNumber = er.EmployeeNumber
where we.EmployeeNumber in (select EmployeeNumber from employees where Attrition = 'No')
group by BusinessTravel) as y
on x.BusinessTravel = y.BusinessTravel) as q on p.BusinessTravel = q.BusinessTravel
join
(select x.BusinessTravel, round((`High Performance Satisfied employees`/`Satisfied employees`)*100, 2) as `Percent High Performance Satisfied employees`
from
(select BusinessTravel, count(we.EmployeeNumber) as `High Performance Satisfied employees`
from `work experience` as we join `employee review` as er
on we.EmployeeNumber = er.EmployeeNumber join salaries as s
on we.EmployeeNumber = s.EmployeeNumber
where we.EmployeeNumber in (select EmployeeNumber from employees where Attrition = 'No') and JobSatisfaction = 4 and s.PercentSalaryHike >= 20
group by BusinessTravel) as x join
(select BusinessTravel, count(we.EmployeeNumber) as `Satisfied employees` from `work experience` as we
join `employee review` as er
on we.EmployeeNumber = er.EmployeeNumber join salaries as s
on we.EmployeeNumber = s.EmployeeNumber
where we.EmployeeNumber in (select EmployeeNumber from employees where Attrition = 'No') and JobSatisfaction = 4
group by BusinessTravel) as y
on x.BusinessTravel = y.BusinessTravel) as r on r.BusinessTravel = p.BusinessTravel;
Question: Which department's employee is the most likely to have the longest commute between home and work?
Notes/Comments About SQL Query and Results (Include # of Rows in Result):
1. The result set contains 2 rows
2. Employees of the departments ‘Research & Development’ and ‘Sales’ are most likely to have longest commute between home and work
Translation: select department name from department roles table for all the job roles that are in the work experience table whose employee number column is in empoloyees table and distance from home is maximum
Question: A new employee with a Technical Degree wants to work in Sales. Do you believe the company might be able to give her a chance to work in Sales? Why or Why not?
Notes/Comments About SQL Query and Results (Include # of Rows in Result):
1. Assuming that the new employee is fully capable to working in the sales team. What we want to verify is if there have been female employees with technical degrees who have worked in Sales in the past. If such employees indeed exist, this particular employee will be able to work in Sales.
2. The following query tells us the names of departments in which there have been female employees with technical degrees. It contains 3 rows, one of them being Sales.
3. Yes, the company will be able to give the new employee a chance to work in Sales.
Translation: select Department name from department roles table where job role in work experience table where employee number in employees table where education field is technical degree and gender is female
CS 340 Milestone One Guidelines and Rubric Overview: For this assignment, you will implement the fundamental operations of create, read, update,
Retail Transaction Programming Project Project Requirements: Develop a program to emulate a purchase transaction at a retail store. This
7COM1028 Secure Systems Programming Referral Coursework: Secure
Create a GUI program that:Accepts the following from a user:Item NameItem QuantityItem PriceAllows the user to create a file to store the sales receip
CS 340 Final Project Guidelines and Rubric Overview The final project will encompass developing a web service using a software stack and impleme
