Please write in SQL Statement For each JOBROLE when the ATTRITION is No, find th
ID: 3585104 • Letter: P
Question
Please write in SQL Statement
For each JOBROLE when the ATTRITION is No, find the average MONTHLYINCOME having DAILYRATE is greater than or equal to 110 (Hint: Use GROUP BY and HAVING statements)
CREATE TABLE EMPLOYEEATTRITION (
AGE INT,
ATTRITION VARCHAR2(20),
BUSINESSTRAVEL VARCHAR2(20),
DAILYRATE INT,
DEPARTMENT VARCHAR2(20),
DISTANCEFROMHOME VARCHAR2(20),
EDUCATION INT,
EDUCATIONFIELD VARCHAR2(20),
EMPLOYEECOUNT INT,
EMPLOYEENUMBER INT,
ENVIRONMENTSATISFACTION INT,
GENDER VARCHAR2(20),
HOURLYRATE INT,
JOBINVOLVEMENT INT,
JOBLEVEL INT,
JOBROLE VARCHAR2(20),
JOBSATISFACTION INT,
MARITALSTATUS VARCHAR2(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
);
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
(
36,
'No',
'Travel_Frequently',
1480,
'Research & Development',
'3',
2,
'Medical',
1,
238,
4,
'Male',
30,
3,
1,
'Laboratory Technician',
2,
'Single',
2088,
15062,
4,
'Y',
'No',
12,
3,
3,
80,
0,
13,
3,
2,
8,
7,
7,
2
);
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
(
30,
'No',
'Non-Travel',
111,
'Research & Development',
'9',
3,
'Medical',
1,
239,
3,
'Male',
66,
3,
2,
'Laboratory Technician',
1,
'Divorced',
3072,
11012,
1,
'Y',
'No',
11,
3,
3,
80,
2,
12,
4,
3,
12,
9,
6,
10
);
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
(
45,
'No',
'Travel_Rarely',
1268,
'Sales',
'4',
2,
'Life Sciences',
1,
240,
3,
'Female',
30,
3,
2,
'Sales Executive',
1,
'Divorced',
5006,
6319,
4,
'Y',
'Yes',
11,
3,
1,
80,
1,
9,
3,
4,
5,
4,
0,
3
);
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
(
56,
'No',
'Travel_Rarely',
713,
'Research & Development',
'8',
3,
'Life Sciences',
1,
241,
3,
'Female',
67,
3,
1,
'Research Scientist',
1,
'Divorced',
4257,
13939,
4,
'Y',
'Yes',
18,
3,
3,
80,
1,
19,
3,
3,
2,
2,
2,
2
);
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
(
33,
'No',
'Travel_Rarely',
134,
'Research & Development',
'2',
3,
'Life Sciences',
1,
242,
3,
'Male',
90,
3,
1,
'Research Scientist',
4,
'Single',
2500,
10515,
0,
'Y',
'No',
14,
3,
1,
80,
0,
4,
2,
4,
3,
1,
0,
2
);
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_Rarely',
303,
'Research & Development',
'2',
3,
'Life Sciences',
1,
243,
2,
'Male',
47,
2,
1,
'Laboratory Technician',
4,
'Single',
1102,
9241,
1,
'Y',
'No',
22,
4,
3,
80,
0,
1,
3,
2,
1,
0,
1,
0
);
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
(
46,
'No',
'Travel_Rarely',
526,
'Sales',
'1',
2,
'Marketing',
1,
244,
2,
'Female',
92,
3,
3,
'Sales Executive',
1,
'Divorced',
10453,
2137,
1,
'Y',
'No',
25,
4,
3,
80,
3,
24,
2,
3,
24,
13,
15,
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
(
38,
'No',
'Travel_Rarely',
1380,
'Research & Development',
'9',
2,
'Life Sciences',
1,
245,
3,
'Female',
75,
3,
1,
'Laboratory Technician',
4,
'Single',
2288,
6319,
1,
'Y',
'No',
12,
3,
3,
80,
0,
2,
3,
3,
2,
2,
2,
1
);
Explanation / Answer
select
JOBROLE, avg(MONTHLYINCOME ) as average_income
from EMPLOYEEATTRITION
where ATTRITION = 'No' and (DAILYRATE >= 110)
group by JOBROLE