Query

kullu · #1 (**permalink**) 11-17-03, 09:47

Hi, I have the following tables-
How do I answer this query?

EMPLOYEE
ssn ename dnumber

DEPARTMENT
dnumber dname

PROJECT
pnumber dnumber

WORKS_ON
ssn pnumber hours

For each employee, find the department he works in and the total hours he spent on projects not controlled by the department he is working on.
Ie, he works on some extra projects not controlled by his department.

Thanks.

gannet · #2 (**permalink**) 11-17-03, 11:49

This should get you going

Code:

SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
  (SELECT e.ssn, e.ename, d.dname, e.dnumber
   FROM employee e, department d 
   WHERE e.dnumber = d.dnumber) ed,
  (SELECT ssn, dnumber, hours
  FROM works_on w, project p
  WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;

satish_ct · #3 (**permalink**) 11-17-03, 23:41

Hi,

SELECT e.ename
, e.dname
, sum(p.hours)
FROM
employee e
, department d
, project p
, works_on w
WHERE
e.dnumber = d.dnumber
and e.ssn = w.ssn
and w.pnumber = p.pnumber
GROUP BY e.ename
;

kullu · #4 (**permalink**) 11-18-03, 10:16

Quote:

Originally posted by gannet
This should get you going

Code:

SELECT ed.ename, ed.dname, sum(pw.hours)
FROM
  (SELECT e.ssn, e.ename, d.dname, e.dnumber
   FROM employee e, department d 
   WHERE e.dnumber = d.dnumber) ed,
  (SELECT ssn, dnumber, hours
  FROM works_on w, project p
  WHERE w.pnumber = p.pnumber) pw
WHERE ed.ssn = pw.ssn
AND ed.dnumber != pw.dnumber
GROUP BY ed.ename, ed.dname
;

Thanks,
That was a great solution!!
:-)

Internet Services

Web Development

Digital Marketing

Consumer Tech