Question

After creating the table and inserting the information below, please

Write SQL commands that find the following: a. Count the total number of records in the table.b. How many unique JOBROLE are there in the dataset. Order them by alphabetical order from A to Z. c. Find EMPLOYEENUMBER, EDUCATIONFIELD, JOBROLE for all the employees whose AGE is greater than 50 and ATTRITION is YES

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 SQL Live.

Question 1:

SQL Query :

select count(*) from EMPLOYEEATTRITION;

Screen in Oracle SQL Live :

SOL Worksheet 49 50 select count (*) from EMPLOYEEATTRITION; 51 53 54 COUNT () Download CSv st un un n

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

Question 2:

SQL Query :

select distinct(JOBROLE) from EMPLOYEEATTRITION order by JOBROLE asc;

Screen in Oracle SQL Live :

SQL Worksheet 52 select distinct (J0BROLE) from EMPLOYEEATTRITION order by JOBROLE asc; 53 54 55 56 J0BROLE Research Scientist Sales Executive Sales Representative Download CSV 3 rows selected.

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

Question 3:

SQL Query :

select EMPLOYEENUMBER, EDUCATIONFIELD, JOBROLE from EMPLOYEEATTRITION where AGE > 50 and ATTRITION='YES';

Screen in Oracle SQL Live :

SQL Worksheet EMPLOYEEATTRITION where AGE 50 and ATTRITION YES' ; 55 select EMPLOYEENUMBER, EDUCATIONFIELD, JOBROLE from 56 57 58 59 no data found

Here no employee with age >50;

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