One possible database state for the COMPANY relational database schema. EMPLOYEE

Question

One possible database state for the COMPANY relational database schema. EMPLOYEE Bdate Super ssn Dno Fname Minit Lname John FranklinT Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5 AliciaJZelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4 enniferS Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F43000 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 AhmadVJabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 9876543214 James EBorg888665555 1937-11-10 450 Stone, Houston, TXM 55000 NULL B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 445555 5 DEPARTMENT DEPT LOCATIONS Mgr ssn 333445555 987654321 888665555 Mar start date 1988-05-22 1995-01-01 1981-06-19 Research Stafford 5 5 WORKS ON PROJECT Pno Hours Pnumber Plocation Dnum 123456789 23456789 666884444 53453453 53453453 33445555 333445555 333445555 333445555 999887777 999887777 987987987 987987987 987654321 987654321 888665555 Product)x ProductY 32.5 7.5 40.0 20.0 20.0 10.0 10.0 10.0 20 10.0 30 30.0 10.0 10 35.0 5.0 30 20.0 20 15.0 Computerization10 Stafford Newbenefits 30 Stafford 10 DEPENDENT nameSBdate Relationship 333445555 333445555 333445555 Joy 987654321 Abner 23456789 Michael 23456789 Alice 23456789 Elizabeth F 1986-04-05 Daughter M 1983-10-25 Son F 1958-05-03 Spouse M 1942-02-28 Spouse M 1988-01-04 Son F 1988-12-30Daughter F 1967-05-05 Spouse 10 20 NULL CLIENT CName Linkedin 10 20 30

Explanation / Answer

Please Note: Your Post contains more than one Question. According to Chegg answering guidelines, I have answered the first 4 sub-questions. Please Re-Post for others.

1) Employees names in dept 5 who work more than 10 hrs per week on ProductX project.

select Fname, Lname from Employee where Dno=5 and where Ssn in (

select Essn from Works_On where Pno in (

select Pnumber from Project where Pname='ProductX'

)

);

2) Employees names who have dependent with same first name as themselves.

select Employee.Fname, Employee.Lname from Employee inner join

Dependent ON Employee.Ssn = Dependent.Essn where Employee.Fname=Dependent.Dependent_name;

3) Employees names that are directly supervised by Franklin Wong

select Fname, Lname from Employee where Super_ssn in (

select Ssn from Employee where Fname='Franklin' and Lname='Wong'

);

4) For each project, list project name and total hours per week in project

select Project.Pname, sum(Works_On.Hours) from Project inner join Works_On on Project.Pnumber=Works_On.Pno group by Works_On.Pno;

Related Questions

Navigate

• Browse (All)
• Browse O
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.