Results 1 to 4 of 4

Thread: Query

  1. #1
    Join Date
    Oct 2003
    Posts
    11

    Unanswered: Query

    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.

  2. #2
    Join Date
    Oct 2002
    Location
    Plymouth UK
    Posts
    116
    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
    ;

  3. #3
    Join Date
    Nov 2003
    Location
    Bangalore, INDIA
    Posts
    333

    Thumbs up

    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
    ;
    SATHISH .

  4. #4
    Join Date
    Oct 2003
    Posts
    11
    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!!
    :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •