Exercise Using the Company Database to execute following query. 1.For every depa

Question

Exercise

Using the Company Database to execute following query.

1.For every department located in Houston, list the name of the department, last name of the department manager and the name of the project(s) control by these departments(s).

2.Retrieve name(s) of the projects and the name of the controlling department that is located in Bellaire. Do not include projects that are controlled by Administration department.

3.List First names and project names of all employees who work on project(s) and have Smith as their last name. Also, include only those projects that are controlled by the department managed by an employee whose last name is Wong.

Final Relational Schema EMPLOYEE DEPARTMENT Dek Dname Dnumber Mgrssn Mgr start date DEPT LOCATIONS Dnumber Diccton PROJECT WORKS ON Ess Pro Hors DEPENDENT egenens omh Sex Bdate Relatenshp $8

Explanation / Answer

1.

select dept.Dname, emp.Lname, proj.Pname

from EMPLOYEE emp, DEPARTMENT dept, DEPT_LOCATIONS deptLoc, PROJECT proj

where emp.ssn = dept.Mgr_Ssn

and dept.Dnumber = deptLoc.Dnumber

and proj.Dnumber = dept.Dnumber

and deptLoc.Dlocation = 'Houston'

group by dept.Dname;

2.

select dept.Dname, proj.Pname

from DEPARTMENT dept, DEPT_LOCATIONS deptLoc, PROJECT proj

where dept.Dnumber = deptLoc.Dnumber

and proj.Dnumber = dept.Dnumber

and deptLoc.Dlocation = 'Bellaire'

and dept.Dname <> 'Administration'

group by dept.Dname;

3.

select emp.Fname, proj.Pname

from EMPLOYEE emp, PROJECT proj, WORKS_ON workOn

where emp.Ssn = workOn.Essn

and proj.Dnumber in (

select proj.Pnumber

from EMPLOYEE emp, DEPARTMENT dept, PROJECT proj

where proj.Dnumber = dept.Dnumber

and dept.Mgr_Ssn = emp.Ssn

and emp.Lname = 'Wong')

and workOn.Pno = proj.Pnumber

and emp.Lname = 'Smith'

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

---

---

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