Specify the following queries on the COMPANY relational database schema (d) For

Question

Specify the following queries on the COMPANY relational database schema

(d) For each project, list the names of projects the have total spent hours (spent by all employees) less than the average spent hours of all projects.

(f) Find the names and addresses of employees who work on at least one project located in Houston but whose department has no location in Houston.

(g) List the last names of department managers who have no dependents.

EMPLOYEE Fname Minit Lname Ssn Bdate Address SexSalary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr ssn Mgr _start_date DEPT LOCATIONS Dnumber Dlocation PROJECT PnamePnumber Plocation Dnum WORKS ON Essn Pno Hours DEPENDENT Essn Dependent_name Sex Bdate Relationship

Explanation / Answer

d) select pname,sum(hours) from project,works_on where pnumber=pno group by pname having sum(hours)< avg(hours);

The above query retrieve the project name and hours spent on that project for which project hours are less than the average hours spent on all projects.

f) select fname,lname,address from employee where exists ( select * from works_on,project where ssn=essn and pno=pnumber and plocation='Houston') and not exists ( select * from dept_location where dno=dnumber and dlocation='Houston');

The above query retrieves the employee information of which works for the project located in houston but his department is not in houston.

g) select lname from employee where exists ( select * from department where ssn=mgrssn) and not exists ( select * from dependent where ssn=essn);

The abve query gives the managers information of those who have no dependents.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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