Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2005
    Posts
    8

    Unanswered: DB2 Query -- Need Help !!

    Hi...Can anyone please analyse the below query and help me find the right answer. The options for result is also given.

    Given the following table definitions:

    DEPARTMENT
    deptno char(3)
    deptname char(30)
    mgrno integer
    admrdept char(3)

    EMPLOYEE
    empno integer
    firstname char(30)
    midinit char
    lastname char(30)
    workdept char(3)

    Which of the following statements will produce a result set satisfying these criteria?
    - The empno and lastname of every employee
    - For each employee, include the empno and lastname of their manager
    - Includes employees both with and without a manager

    Choose one of the following options:

    a) SELECT e.empno, e.lastname FROM employee e LEFT OUTER JOIN
    (department INNER JOIN employee m ON mgrno = m.empno)
    ON e.workdept = deptno

    b) SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT INNER JOIN
    (department INNER JOIN employee m ON mgrno = m.empno)
    ON e.workdept = deptno

    c) SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e LEFT OUTER JOIN
    (department INNER JOIN employee m ON mgrno = m.empno)
    ON e.workdept = deptno

    d) SELECT e.empno, e.lastname, m.empno, m.lastname FROM employee e RIGHT OUTER JOIN
    (department INNER JOIN employee m ON mgrno = m.empno)
    ON e.workdept = deptno

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    a) No - the result table only consists of empno and lastname of the employee

    b) No - there is no "LEFT INNER JOIN"

    c) Yes - this query will satisfy the requirements

    d) No - the RIGHT OUTER JOIN will include managers without employees but not employees without manager

  3. #3
    Join Date
    Dec 2005
    Posts
    8
    Thanks a lot for the answer, Umayer!!

  4. #4
    Join Date
    Aug 2004
    Posts
    330
    Of course, in the real world, the difficult part is to get the user to define the criteria in an unambiguous way.

    I would expect to encounter critera more like ...

    - The empno and lastname of some of the employees, or maybe contractors
    - For each employee, whether or not they have a manager, include the empno and lastname of their manager
    - Each employee can have zero, one or more managers but just give me one manager per employee

Posting Permissions

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