Use the Dependent table in the Company database to answer the following questions: 1. List the...

Transcribed Image Text:

Use the Dependent table in the Company database to answer the following questions: 1. List the Employee SSN (ESSN) and number of dependents for each employee. 2. List the Employee SSN (ESSN) and number of dependents for each employee but only list employees who have at least two dependents. 3. List the Employee SSN (ESSN), Sex, and number of dependents of that sex for each employee. Sort your list by Employee SSN. Use the Employee table in the Company database to answer the following questions: 4. What is the number of employees, number of unique salary values, total salary, and average salary for all employees? Use appropriate column headings for all calculated values. 5. What is the number of employees, number of unique salary values, total salary, and average salary broken down by Department? Only show departments that have at least two employees. Company Database Data Employee SSN 888665555 James E 333445555 Franklin T Wong FNAME M LNAME BDATE ADDRESS Sex SALARY SUPERSSN DNUMBER Borg 10-NOV-92 450 Stone, HoUston, TX M 55000 08-DEC-70 987654321 Jennifer S Wallace 20-JUN-86 987987987 George W Bush 29-MAR-84 638 Voss, Houston, TX 40000 888665555 291 Berry, Bellaire, TX 980 Dallas, Houston, TX 09 JAN-80 731 Fondren, Houston, TX M 3321 Castle, Spring. TX 43000 888665555 4. 25000 987654321 4. 123456789 999887777 666884444 Ramesh K Narayan 15-SEP-77 John Smith 30000 333445555 Alicia J Zelaya 19-JUL-83 25000 987654321 4 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 38000 333445555 453453453 Joyce A English 31-JUL-87 25000 333445555 5. Department Dept Locations DNUMBER DLOCATION 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston DNUMBER DNAME MGRSSN 333445555 22-MAY-2003 MGRSTARTDATE 5 Research 4 Administration 987654321 01-JAN-2006 1 Headquarters 888665555 19 JUN-96 Project PNUMBER PNAME PLOCATION DNUMBER Bellaire Sugarland Houston 10 Computerization Stafford 20 Reorganization Houston Stafford 1 ProductX 2 ProjectY 3 Productz Works On SSN 123456789 123456789 PNUMBER HOURS 32.5 7.5 2 30 Newbenefits 666884444 3 40 453453453 20 453453453 2 20 Dependent DEPENDENT NAME Sex 333445555 2 10 ESSN 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 Abner 123456789 Michael 123456789 Alice 123456789 Elzabeth BDATE RELATIONSHIP 333445555 10 05-APR-2001 Daughter M 25-OCT-2014 Son 03-MAY-1983 Spouse M 28-FEB-1987 Spouse M 01-JAN-2013 Son F 31-DEC-2013 Daughter F 05-MAY-1992 Spouse 333445555 333445555 999887777 999887777 10 10 20 10 F 30 10 30 10 987987987 987987987 987654321 10 35 30 30 20 20 987654321 15 Null 888665555 20 Use the Dependent table in the Company database to answer the following questions: 1. List the Employee SSN (ESSN) and number of dependents for each employee. 2. List the Employee SSN (ESSN) and number of dependents for each employee but only list employees who have at least two dependents. 3. List the Employee SSN (ESSN), Sex, and number of dependents of that sex for each employee. Sort your list by Employee SSN. Use the Employee table in the Company database to answer the following questions: 4. What is the number of employees, number of unique salary values, total salary, and average salary for all employees? Use appropriate column headings for all calculated values. 5. What is the number of employees, number of unique salary values, total salary, and average salary broken down by Department? Only show departments that have at least two employees. Company Database Data Employee SSN 888665555 James E 333445555 Franklin T Wong FNAME M LNAME BDATE ADDRESS Sex SALARY SUPERSSN DNUMBER Borg 10-NOV-92 450 Stone, HoUston, TX M 55000 08-DEC-70 987654321 Jennifer S Wallace 20-JUN-86 987987987 George W Bush 29-MAR-84 638 Voss, Houston, TX 40000 888665555 291 Berry, Bellaire, TX 980 Dallas, Houston, TX 09 JAN-80 731 Fondren, Houston, TX M 3321 Castle, Spring. TX 43000 888665555 4. 25000 987654321 4. 123456789 999887777 666884444 Ramesh K Narayan 15-SEP-77 John Smith 30000 333445555 Alicia J Zelaya 19-JUL-83 25000 987654321 4 975 Fire Oak, Humble, TX 5631 Rice, Houston, TX 38000 333445555 453453453 Joyce A English 31-JUL-87 25000 333445555 5. Department Dept Locations DNUMBER DLOCATION 1 Houston 4 Stafford 5 Bellaire 5 Sugarland 5 Houston DNUMBER DNAME MGRSSN 333445555 22-MAY-2003 MGRSTARTDATE 5 Research 4 Administration 987654321 01-JAN-2006 1 Headquarters 888665555 19 JUN-96 Project PNUMBER PNAME PLOCATION DNUMBER Bellaire Sugarland Houston 10 Computerization Stafford 20 Reorganization Houston Stafford 1 ProductX 2 ProjectY 3 Productz Works On SSN 123456789 123456789 PNUMBER HOURS 32.5 7.5 2 30 Newbenefits 666884444 3 40 453453453 20 453453453 2 20 Dependent DEPENDENT NAME Sex 333445555 2 10 ESSN 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 Abner 123456789 Michael 123456789 Alice 123456789 Elzabeth BDATE RELATIONSHIP 333445555 10 05-APR-2001 Daughter M 25-OCT-2014 Son 03-MAY-1983 Spouse M 28-FEB-1987 Spouse M 01-JAN-2013 Son F 31-DEC-2013 Daughter F 05-MAY-1992 Spouse 333445555 333445555 999887777 999887777 10 10 20 10 F 30 10 30 10 987987987 987987987 987654321 10 35 30 30 20 20 987654321 15 Null 888665555 20

Answer rating: 100% (QA)

SELECT COUNT AS Number of Employees COUNTDISTINCT SALARY AS Unique SalarySUMSALARY AS Total SalaryAV... View the full answer