Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
Specify the following queries on the database in Figure 5.6 in SQL. (a) Retrieve the names of employee in department 4 who work more than 15 hours per week o the 'Newbenefits' project. (b) Find the names of Franklin Wong's supervisor. (c) For each project, list the project name and the total hours per week (by all employees) spent c that project.

Specify the following queries on the database in Figure 5.6 in SQL. (a) Retrieve the names of employee in department 4 who work more than 15 hours per week o the 'Newbenefits' project. (b) Find the names of Franklin Wong's supervisor. (c) For each project, list the project name and the total hours per week (by all employees) spent c that project.

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

DATABSE 

Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno John Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Franklin Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 |333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 James E Borg 888665555 | 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT_LOCATIONS Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-06-19 Bellaire Sugarland Houston WORKS_ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 1 32.5 ProductX 1 Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 3 40.0 ProductZ 3 Houston 453453453 1 20.0 Computerization 10 Stafford 4 453453453 2 20.0 Reorganization 20 Houston 1 333445555 10.0 Newbenefits 30 Stafford 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 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore 1983-10-25 Son 987987987 10 35.0 333445555 Joy F 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse
Transcribed Image Text:Figure 5.6 One possible database state for the COMPANY relational database schema. EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno John Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 Franklin Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 Alicia Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4 Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 |333445555 5 Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5 Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4 James E Borg 888665555 | 1937-11-10 450 Stone, Houston, TX M 55000 NULL DEPARTMENT DEPT_LOCATIONS Dname Dnumber Mgr_ssn Mgr_start_date Dnumber Dlocation Research 333445555 1988-05-22 1 Houston Administration 4 987654321 1995-01-01 4 Stafford Headquarters 1 888665555 1981-06-19 Bellaire Sugarland Houston WORKS_ON PROJECT Essn Pno Hours Pname Pnumber Plocation Dnum 123456789 1 32.5 ProductX 1 Bellaire 123456789 2 7.5 ProductY 2 Sugarland 666884444 3 40.0 ProductZ 3 Houston 453453453 1 20.0 Computerization 10 Stafford 4 453453453 2 20.0 Reorganization 20 Houston 1 333445555 10.0 Newbenefits 30 Stafford 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 1986-04-05 Daughter 999887777 10 10.0 333445555 Theodore 1983-10-25 Son 987987987 10 35.0 333445555 Joy F 1958-05-03 Spouse 987987987 30 5.0 987654321 Abner 1942-02-28 Spouse 987654321 30 20.0 123456789 Michael M 1988-01-04 Son 987654321 20 15.0 123456789 Alice F 1988-12-30 Daughter 888665555 20 NULL 123456789 Elizabeth F 1967-05-05 Spouse
Specify the following queries on the database in Figure 5.6 in SQL. (a) Retrieve the names of employee in department 4 who work more than 15 hours per week on the 'Newbenefits' project. (b) Find the names of Franklin Wong's supervisor. (c) For each project, list the project name and the total hours per week (by all employees) spent on that project. (d) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. (e) Retrieve the names of all employees whose supervisor's supervisor has '888665555’ for SSN.
Transcribed Image Text:Specify the following queries on the database in Figure 5.6 in SQL. (a) Retrieve the names of employee in department 4 who work more than 15 hours per week on the 'Newbenefits' project. (b) Find the names of Franklin Wong's supervisor. (c) For each project, list the project name and the total hours per week (by all employees) spent on that project. (d) Retrieve the names of all employees who work in the department that has the employee with the highest salary among all employees. (e) Retrieve the names of all employees whose supervisor's supervisor has '888665555’ for SSN.
Expert Solution
trending now

Trending now

This is a popular solution!

steps

Step by step

Solved in 3 steps

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Knowledge Booster
Complex Datatypes
Learn more about
Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS