Specify the following queries on the database schema shown in Figure 5.5, using

Question

Specify the following queries on the database schema shown in Figure 5.5, using
the relational operators discussed in this chapter. Also show the result of each
query as it would apply to the database state of Figure 5.6.

a. Retrieve the names of all employees in department 5 who work more than 10
hours per week on the 'ProductX' project.
b. List the names of all employees who have a dependent with the same first
name as themselves.
c. Find the names of all employees who are directly supervised by 'Franklin
Wong'.
d. For each project, list the project name and the total hours per week (by all
employees) spent on that project.
e. Retrieve the names of all employees who work on every project.
f. Retrieve the names of all employees who do not work on any project.
g. For each department, retrieve the department name and the average salary of
all employees working in that department.
h. Retrieve the average salary of all female employees.
i. Find the names and addresses of all employees who work on at least one
project located in Houston but whose department has no location in Houston.
j. List the last names of all department managers who have no dependents

Explanation / Answer

Dear..    a.

 select E.FNAME,E.MINIT,E.LNAME
 from   employee E, works_on W, projects P
 where E.DNO = 5 and
         E.ssn = W.essn and W.pno = P.pnumber and
         P.pname = 'ProductX' and W.hours > 10;
b.

 select E.FNAME,E.MINIT,E.LNAME
 from   employee E, dependent D
 where E.ssn = D.essn and E.fname = D.dependent_name;

c.

 select E.fname,E.minit,E.lname
 from   employee E, employee E1
 where E.superssn = E1.ssn
 and    E1.fname = 'Franklin' and E1.lname = 'Wong';

d.

 select projName,TotHours : sum(select P.wo.Hours from p in partition)
  from Wo in WORKS_ON_PROJECT
 group by projName : Wo.project.Name;

e.

 select E.FNAME,E.MINIT,E.LNAME
 from   employee E
 where not exists( select P.pnumber 
                 from   projects P
                     where not exists( select W1.essn 
                                        from   works_on W1
                                        where W1.pno = P.pnumber and W1.essn = E.ssn));
f.

 select E.FNAME,E.MINIT,E.LNAME
   from   employee 
   where not exists (
             select essn 
             from   works_on 
             where ssn = essn);
g.



    select PNAME, SUM(Hours)

    from project, works_on
    where pnumber = pno
    group by pname;

h.

     avg( select E.Salary
         from E in EMPLOYEES
         where E.Sex = F) ;
i.
    select E.FNAME,E.MINIT,E.LNAME, E.ADDRESS
    from   employee E, projects P, works_on W
    where E.ssn = W.essn and P.pnumber = W.pno and P.plocation = 'Houston' and not exists (
                          select D.dnumber 
                          from   dept_locations D
                          where E.dno = D.dnumber and D.dlocation = 'Houston');
j.
    select E.LNAME
    from   employee E, department D 
    where E.ssn = D.mgrssn and not exists( select essn 
                                           from   dependent 
                                           where D.mgrssn = essn);

a.

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

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