Specify in OQL the queries in the exercises of Chapter 7 and 8 that apply to the COMPANY database.

Question

What will be an ideal response?

Answer 1

(a) Retrieve the names of employees in department 5 who work more than 10 hours per
week on the 'ProductX' project.
select e.name.fname
from e in employee
where e.works_for .dnumber = 5 and
( 'Product X' in
(select h.work_on.pname
from h in e.work
where h.hours > 10));

(b) List the name of employees who have a dependent with the same first name as themselves.
select k.suporter.fname
from k in dependents
where k.name = k.suporter.fname

(c) List the name of employees who are all directly supervised by 'Franklin Wong'.
select e.fname
from e in employee
where e.supervisor.name.fname = 'Franklin' and e.supervisor.name.lname = 'Wong';

(d) For each project, list the project name and the total hours per week (by all employees)
spent on that project.
select projname, tot_hours : sum (select p.h.hours from p in partition)
from h in hours_worked
group by projname: w.work_on.pname;

(e) Retrieve the names of all employees who work on every project.
select e.name.fname
from e in employee
where for all g in (select p.pnumber
from p in projects)
: g.pnumber in (select e.work.work_on.pnumber);

(f) Retrieve the names of all employees who do not work on any project.
select e.name.fname
from e in employee
where for all g in (select p.pnumber
from p in projects)
: g.pnumber not in (select e.work.work_on.pnumber);

(g) For each department, retrieve the department name, and the average salary of employees
working in that department.
select deptname, avgsal : avg (select p.e.salary from p in partition)
from e in employee
group by deptname: e.works_for.dname;

(h) Retrieve the average salary of all female employees.
avg (select e.salarey
from e in employee
where e.sex = 'M');