Homework Help is Here – Start Your Trial Now!
Engineering
Computer Engineering
1. Find the average salary of employees who works on the projects which are controlled by department 5. 2. Find the total number of dependents of each employee who has dependents. 3. Find the total working hours of each manager.

1. Find the average salary of employees who works on the projects which are controlled by department 5. 2. Find the total number of dependents of each employee who has dependents. 3. Find the total working hours of each manager.

Computer Networking: A Top-Down Approach (7th Edition)
Computer Networking: A Top-Down Approach (7th Edition)
7th Edition
ISBN:9780133594140
Author:James Kurose, Keith Ross
Publisher:James Kurose, Keith Ross
Chapter1: Computer Networks And The Internet
Section: Chapter Questions
Problem R1RQ: What is the difference between a host and an end system? List several different types of end...
See similar textbooks
icon
Related questions
Question
Write the following queries in SQL on the relational database schema for COMPANY database given 1. Find the average salary of employees who works on the projects which are controlled by department 5. 2. Find the total number of dependents of each employee who has dependents. 3. Find the total working hours of each manager. 4. Find the name of employees who have salary less than the average salary of employees who are in department 4. 5. Find the last name of the projects on which no manager works. 6. Find the last name of the supervisors who do not have dependents. 3.
Transcribed Image Text:Write the following queries in SQL on the relational database schema for COMPANY database given 1. Find the average salary of employees who works on the projects which are controlled by department 5. 2. Find the total number of dependents of each employee who has dependents. 3. Find the total working hours of each manager. 4. Find the name of employees who have salary less than the average salary of employees who are in department 4. 5. Find the last name of the projects on which no manager works. 6. Find the last name of the supervisors who do not have dependents. 3.
EMPLOYEE Fname Minit Lname Sen Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Referential integrity constraints displayed on the COMPANY relational database schema. Essn Dependent_name Sex Bdate Relationship One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_san Dno B 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 40000 888665555 John Smith 123456789 Franklin Wong 333445555 1955-12-08 638 Voss, Houston, TX 5 999887777 1968-01-19 3321 Castle, Spring. TX 1941-06-20 291 Berry, Bellaire, TX Alicia Zelaya F 25000 987654321 4 Jennifer Wallace 987654321 43000 888665555 4 Ramesh Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 25000 333445555 25000 987654321 55000 NULL Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX Ahmad Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX 4 James Borg 888665555 1937-11-10 450 Stone, Houston, TX DEPARTMENT DEPT_LOCATIONS Dnumber Mgr_start_date 1988-05-22 Dname Dnumber Mgr_ssn Dlocation Research 333445555 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 888665555 1 1981-06-19 Bellaire Sugarland 5 Houston WORKS_ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 32.5 ProductX 1 Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 40.0 Productz 3 Houston 5 Computerization Reorganization 453453453 20.0 10 Stafford 4 453453453 20.0 20 Houston 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Bdate Relationship 999887777 30 30.0 333445555 Alice F 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 987987987 10 35.0 Spouse 333445555 Joy F 1958-05-03 987987987 30 5.0 987654321 Abner M 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth 1967-05-05 Spouse
Transcribed Image Text:EMPLOYEE Fname Minit Lname Sen Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Referential integrity constraints displayed on the COMPANY relational database schema. Essn Dependent_name Sex Bdate Relationship One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_san Dno B 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 40000 888665555 John Smith 123456789 Franklin Wong 333445555 1955-12-08 638 Voss, Houston, TX 5 999887777 1968-01-19 3321 Castle, Spring. TX 1941-06-20 291 Berry, Bellaire, TX Alicia Zelaya F 25000 987654321 4 Jennifer Wallace 987654321 43000 888665555 4 Ramesh Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 25000 333445555 25000 987654321 55000 NULL Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX Ahmad Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX 4 James Borg 888665555 1937-11-10 450 Stone, Houston, TX DEPARTMENT DEPT_LOCATIONS Dnumber Mgr_start_date 1988-05-22 Dname Dnumber Mgr_ssn Dlocation Research 333445555 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 888665555 1 1981-06-19 Bellaire Sugarland 5 Houston WORKS_ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 32.5 ProductX 1 Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 40.0 Productz 3 Houston 5 Computerization Reorganization 453453453 20.0 10 Stafford 4 453453453 20.0 20 Houston 333445555 10.0 Newbenefits 30 Stafford 4 333445555 3 10.0 333445555 10 10.0 DEPENDENT 333445555 20 10.0 Essn Dependent_name Sex Bdate Relationship 999887777 30 30.0 333445555 Alice F 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore M 1983-10-25 Son 987987987 10 35.0 Spouse 333445555 Joy F 1958-05-03 987987987 30 5.0 987654321 Abner M 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth 1967-05-05 Spouse
Expert Solution
steps

Step by step

Solved in 2 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Recommended textbooks for you
Computer Networking: A Top-Down Approach (7th Edi…
Computer Networking: A Top-Down Approach (7th Edi…
Computer Engineering
ISBN:
9780133594140
Author:
James Kurose, Keith Ross
Publisher:
PEARSON
Computer Organization and Design MIPS Edition, Fi…
Computer Organization and Design MIPS Edition, Fi…
Computer Engineering
ISBN:
9780124077263
Author:
David A. Patterson, John L. Hennessy
Publisher:
Elsevier Science
Network+ Guide to Networks (MindTap Course List)
Network+ Guide to Networks (MindTap Course List)
Computer Engineering
ISBN:
9781337569330
Author:
Jill West, Tamara Dean, Jean Andrews
Publisher:
Cengage Learning
Concepts of Database Management
Concepts of Database Management
Computer Engineering
ISBN:
9781337093422
Author:
Joy L. Starks, Philip J. Pratt, Mary Z. Last
Publisher:
Cengage Learning
Prelude to Programming
Prelude to Programming
Computer Engineering
ISBN:
9780133750423
Author:
VENIT, Stewart
Publisher:
Pearson Education
Sc Business Data Communications and Networking, T…
Sc Business Data Communications and Networking, T…
Computer Engineering
ISBN:
9781119368830
Author:
FITZGERALD
Publisher:
WILEY
SEE MORE TEXTBOOKS