SELECT D.DEPTNO, D.DEPTNAME, 
              (SELECT AVG(SALARY)      ==>  or SELECT DEC(AVG(SALARY),7,2)
               FROM EMP E
               WHERE E.WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL
FROM DEPT D

This scalar fullselect coded will return either a value or nulls for each DEPTNO value in the DEPT table.  These 
scalar fullselects are codes as correlated sub selects and will return a value for each specific DEPTNO coming 
from the DEPT table.  This executes just like a correlated subquery.  This could also be coded as: 

SELECT D.DEPTNO, D.DEPTNAME,
             AVG(E.SALARY) AS DEPT_AVG_SAL
FROM DEPT D  LEFT JOIN EMP E
     ON E. WORKDEPT = D.DEPTNO)
GROUP BY D.DEPTNO, D.DEPTNAME
; 

If no name is provided for the scalar fullselect column in the SELECT clause, then in this example it will show the number 3 since it is the third column in the list.

Note:  The left join is needed here in order to include those departments that do not have any employees on the EMP table and will return nulls for those average salaries.  An inner join would eliminate those departments that have no rows on the EMP table to calculate an average salary.  

Is the first one with the scalar fullselect more or less efficient than the left outer join?  I don’t know.  It would have to be tested.  But it provides another way to gather the results needed.

Example 2:

List the departments where the average salary is >  ??? some amount,  Here is an example of those

 with an average > 40000.00.  Also show the number of employees that went into the average.

SELECT D.DEPTNO, D.DEPTNAME,
              (SELECT DEC(AVG(SALARY),7,2) 
               FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL,
               (SELECT COUNT(*)
               FROM EMP E
              WHERE E. WORKDEPT = D.DEPTNO) AS NUM_EMPS
FROM DEPT D
WHERE (SELECT AVG(SALARY) FROM EMP E
               WHERE E.WORKDEPT = D.DEPTNO)      >  40000.00  
;

  

Example 3: 

List the departments where the average bonus of its employees is > average salary of its employees.

SELECT D.DEPTNO, D.DEPTNAME,
              (SELECT AVG(BONUS) 
               FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO) AS DEPT_AVG_BONUS,
              (SELECT AVG(SALARY) 
               FROM EMP E
               WHERE E.WORKDEPT = D.DEPTNO) AS DEPT_AVG_SAL
FROM DEPT D
WHERE (SELECT AVG(BONUS) FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO)    
               >   
              (SELECT AVG(SALARY) FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO)   
;

Note:  In the sample data there is no department where the average bonus > average salary.  But this would show

 any that did.

Example 4: 

List each departments with the number of employees and the number of projects.

SELECT D.DEPTNO, D.DEPTNAME,
              (SELECT COUNT(*)  
               FROM EMP E
               WHERE E. WORKDEPT = D.DEPTNO) AS NUM_EMPS ,
              (SELECT COUNT(*) 
               FROM PROJ P 
               WHERE P.DEPTNO = D.DEPTNO) AS NUM_PROJS 
 FROM DEPT D
;