WHERE DEPT_NO IS NOT NULL
SELECT EMP_NO, EMP_NAME, EMP_SAL
FROM EMP
WHERE DEPT_NO IS NOT NULL AND
EMP_SAL BETWEEN
(SELECT AVG(EMP1.EMP_SAL)
FROM EMP EMP1
WHERE EMP.DEPT_NO = EMP1.DEPT_NO)
AND
(SELECT EMP1.EMP_SAL
FROM EMP EMP1
WHERE EMP1.EMP_NO =
( SELECT DEPT.DEPT_MNG
FROM DEPT
WHERE DEPT.DEPT_NO = EMP.DEPT_NO ) );
