OQL and SQL queries. Select all department numbers whose employees have the same salary

Question

So, I have two tables:

EMP {EMP_NO, EMP_SALARY, EMP_DEPT_NO}

DEPT {DEPT_NO, DEPT_MNG}

EMP_NO, DEPT_NO - primary keys, EMP_DEPT_NO - external key to DEPT, DEPT_MNG - external key to EMP.

I need to find all departments where every employee has the same salary.

Answer 1

You can use the COUNT DISTINCT in the HAVING section to achieve that. the COUNT DISTINCT will return how many variations of salary there are in a certain dept.

SELECT DEPT_NO
FROM DEPT JOIN EMP ON DEPT.DEPT_NO=EMP.EMP_DEPT_NO
GROUP BY DEPT_NO
HAVING COUNT(DISTINCT SALARY) =1