show department name and no of employees against each department and manager name of each (also show departments with no employees).

Question

i am using hr schema employees and departments tables.

Answer 1

First of all, my table names are EMPS and DEPTS and DEPTNO is the PK of DEPTS, and the FK column name of the EMPS table.

Secondly, department number 40 is empty. That is the reason I use two LEFT OUTER JOINS. Thirdly, I use Oracle's NVL function to display "No Manager" and a "0" for the number of employees in department 40. Finally, I am using an Oracle12c database.

_{SELECT D.DNAME,}

_{NVL(E.ENAME, 'No Manager') "Manager Name",}

_{NVL(N.NUM,0) "Num Emps"}

_{FROM DEPTS D LEFT OUTER JOIN EMPS E}

_{. ON(D.DEPTNO = E.DEPTNO)}

_{LEFT OUTER JOIN (SELECT DEPTNO, COUNT(*) NUM}

_{FROM EMPS}

_{GROUP BY DEPTNO) N}

_{ON(D.DEPTNO = N.DEPTNO)}

_{WHERE E.JOB = 'MANAGER' OR . E.ENAME IS NULL}

_{ORDER BY D.DEPTNO;}

D.DNAME.......... Manager Name..... Num Emps

-------------------.... ---------------------...... --------------

ACCOUNTING... CLARK................................. 3

RESEARCH..........JONES................................. 4

SALES................ BLAKE................................. 7

OPERATIONS.....No Manager...................... 0

NOTE: Oracle says our query retrieving the number of employees in the "FROM" clause is an "IN LINE VIEW".

We don't have to manually create a view, but can use a "SELECT" statement in the "FROM" clause. I recently worked with a college graduate student, and their class referred to it as a "SUBQUERY".