Question

After creating the table below and inserting the values please write sql commands

d. Count the different MARITALSTATUS when ATTRITION is YES in the dataset. Arrange the count in descending order (Hint: Use GROUP BY statement)

e. For each JOBROLE when the ATTRITION is No, find the average MONTHLYINCOME for only those employees having DAILYRATE greater than or equal to 110 (Hint: Use GROUP BY and HAVING statements together)

CREATE TABLE EMPLOYEEATTRITION (
AGE INT,
ATTRITION VARCAHR2(20),
BUSINESSTRAVEL VARCAHR2(20),
DAILYRATE INT,
DEPARTMENT VARCAHR2(20),
DISTANCEFROMHOME VARCAHR2(20),
EDUCATION INT,
EDUCATIONFIELD VARCAHR2(20),
EMPLOYEECOUNT INT,
EMPLOYEENUMBER INT,
ENVIRONMENTSATISFACTION INT,
GENDER VARCAHR2(20),
HOURLYRATE INT,
JOBINVOLVEMENT INT,
JOBLEVEL INT,
JOBROLE VARCHAR2(20),
JOBSATISFACTION INT,
MARITALSTATUS VARCAHR2(20),
MONTHLYINCOME INT,
MONTHLYRATE INT,
NUMCOMPANIESWORKED INT,
OVER18 VARCHAR2(20),
OVERTIME VARCHAR2(20),
PERCENTSALARYHIKE INT,
PERFORMANCERATING INT,
RELATIONSHIPSATISFACTION INT,
STANDARDHOURS INT,
STOCKOPTIONLEVEL INT,
TOTALWORKINGYEARS INT,
TRAININGTIMESLASTYEAR INT,
WORKLIFEBALANCE INT,
YEARSATCOMPANY INT,
YEARSINCURRENTROLE INT,
YEARSSINCELASTPROMOTION INT,
YEARSWITHCURRMANAGER INT,
PRIMARY KEY (AGE)
);

INSERT INTO EMPLOYEEATTRITION ( AGE, ATTRITION, BUSINESSTRAVEL, DAILYRATE, DEPARTMENT, DISTANCEFROMHOME, EDUCATION, EDUCATIONFIELD, EMPLOYEECOUNT, EMPLOYEENUMBER, ENVIRONMENTSATISFACTION, GENDER, HOURLYRATE, JOBINVOLVEMENT, JOBLEVEL, JOBROLE, JOBSATISFACTION, MARITALSTATUS, MONTHLYINCOME, MONTHLYRATE, NUMCOMPANIESWORKED, OVER18, OVERTIME, PERCENTSALARYHIKE, PERFORMANCERATING, RELATIONSHIPSATISFACTION, STANDARDHOURS, STOCKOPTIONLEVEL, TOTALWORKINGYEARS, TRAININGTIMESLASTYEAR, WORKLIFEBALANCE, YEARSATCOMPANY, YEARSINCURRENTROLE, YEARSSINCELASTPROMOTION, YEARSWITHCURRMANAGER ) VALUES ( 38, 'No', 'Travel_Rarely', 702, 'Sales', '1', 4, 'Life Sciences', 1, 230, 1, 'Female', 59, 2, 2, 'Sales Executive', 4, 'Single', 8686, 12930, 4, 'Y', 'No', 22, 4, 3, 80, 0, 12, 2, 4, 8, 3, 0, 7 );

INSERT INTO EMPLOYEEATTRITION ( AGE, ATTRITION, BUSINESSTRAVEL, DAILYRATE, DEPARTMENT, DISTANCEFROMHOME, EDUCATION, EDUCATIONFIELD, EMPLOYEECOUNT, EMPLOYEENUMBER, ENVIRONMENTSATISFACTION, GENDER, HOURLYRATE, JOBINVOLVEMENT, JOBLEVEL, JOBROLE, JOBSATISFACTION, MARITALSTATUS, MONTHLYINCOME, MONTHLYRATE, NUMCOMPANIESWORKED, OVER18, OVERTIME, PERCENTSALARYHIKE, PERFORMANCERATING, RELATIONSHIPSATISFACTION, STANDARDHOURS, STOCKOPTIONLEVEL, TOTALWORKINGYEARS, TRAININGTIMESLASTYEAR, WORKLIFEBALANCE, YEARSATCOMPANY, YEARSINCURRENTROLE, YEARSSINCELASTPROMOTION, YEARSWITHCURRMANAGER ) VALUES ( 32, 'No', 'Travel_Rarely', 120, 'Research & Development', '6', 5, 'Life Sciences', 1, 231, 3, 'Male', 43, 3, 1, 'Research Scientist', 3, 'Single', 3038, 12430, 3, 'Y', 'No', 20, 4, 1, 80, 0, 8, 2, 3, 5, 4, 1, 4 );

INSERT INTO EMPLOYEEATTRITION ( AGE, ATTRITION, BUSINESSTRAVEL, DAILYRATE, DEPARTMENT, DISTANCEFROMHOME, EDUCATION, EDUCATIONFIELD, EMPLOYEECOUNT, EMPLOYEENUMBER, ENVIRONMENTSATISFACTION, GENDER, HOURLYRATE, JOBINVOLVEMENT, JOBLEVEL, JOBROLE, JOBSATISFACTION, MARITALSTATUS, MONTHLYINCOME, MONTHLYRATE, NUMCOMPANIESWORKED, OVER18, OVERTIME, PERCENTSALARYHIKE, PERFORMANCERATING, RELATIONSHIPSATISFACTION, STANDARDHOURS, STOCKOPTIONLEVEL, TOTALWORKINGYEARS, TRAININGTIMESLASTYEAR, WORKLIFEBALANCE, YEARSATCOMPANY, YEARSINCURRENTROLE, YEARSSINCELASTPROMOTION, YEARSWITHCURRMANAGER ) VALUES ( 27, 'No', 'Travel_Rarely', 1157, 'Research & Development', '17', 3, 'Technical Degree', 1, 233, 3, 'Male', 51, 3, 1, 'Research Scientist', 2, 'Married', 3058, 13364, 0, 'Y', 'Yes', 16, 3, 4, 80, 1, 6, 3, 2, 5, 2, 1, 1 );

INSERT INTO EMPLOYEEATTRITION ( AGE, ATTRITION, BUSINESSTRAVEL, DAILYRATE, DEPARTMENT, DISTANCEFROMHOME, EDUCATION, EDUCATIONFIELD, EMPLOYEECOUNT, EMPLOYEENUMBER, ENVIRONMENTSATISFACTION, GENDER, HOURLYRATE, JOBINVOLVEMENT, JOBLEVEL, JOBROLE, JOBSATISFACTION, MARITALSTATUS, MONTHLYINCOME, MONTHLYRATE, NUMCOMPANIESWORKED, OVER18, OVERTIME, PERCENTSALARYHIKE, PERFORMANCERATING, RELATIONSHIPSATISFACTION, STANDARDHOURS, STOCKOPTIONLEVEL, TOTALWORKINGYEARS, TRAININGTIMESLASTYEAR, WORKLIFEBALANCE, YEARSATCOMPANY, YEARSINCURRENTROLE, YEARSSINCELASTPROMOTION, YEARSWITHCURRMANAGER ) VALUES ( 19, 'Yes', 'Travel_Frequently', 602, 'Sales', '1', 1, 'Technical Degree', 1, 235, 3, 'Female', 100, 1, 1, 'Sales Representative', 1, 'Single', 2325, 20989, 0, 'Y', 'No', 21, 4, 1, 80, 0, 1, 5, 4, 0, 0, 0, 0 );

Answer 1

Dear Student ,

As per the requirement submitted above , kindly find the below solution.

This demonstration is using oracle Live SQL.

Question d:

SQL Query :

SELECT count(MARITALSTATUS) from EMPLOYEEATTRITION where ATTRITION like'Yes'
group by MARITALSTATUS;

Screen in Oracle Live SQL :

SQL Worksheet 56 57 SELECT count (MARITALSTATUS) from EMPLOYEEATTRITION where ATTRITION like'Yes" group by MARITALSTATUS 58 59 60 61 62 COUNT (MARITALSTATUS) 1 Download Csv

*********************************

Question e:

SQL Query :

SELECT JOBROLE,DAILYRATE,avg(MONTHLYINCOME) from EMPLOYEEATTRITION where ATTRITION like'No'
group by JOBROLE,DAILYRATE
having DAILYRATE>=110;

Screen in Oracle Live SQL :

SQL Worksheet 60 SELECT JOBROLE, DAILYRATE, avg (MONTHLYINCOME) from EMPLOYEEATTRITION where ATTRITION like'No 61 group by JOBROLE,DAILYRATE having DAILYRATE> 110 ; 62 63 64 65 66 67 AVG (MONTHLYINCOME) JOBROLE DAILYRATE Sales Executive 702 8686 Research Scientist 3038 128 Research Scientist 1157 3058 Download CSV 3 rows selected

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.