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

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

Thanks a lot for the answer, Umayer!!

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