Oracle 23c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select @@version;
ORA-00936: missing expression

CREATE TABLE Department (
    department_id VARCHAR2(25) ,
    employee_id NUMBER,
    occupation VARCHAR2(25),
    salary NUMBER,
    as_of_date DATE
);
INSERT ALL
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d001', 01, 'Scientist', 1000000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d001', 02, 'Engineer', 334344, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d001', 03, 'Scientist', 230000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d001', 04, 'Engineer', 330000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d002', 04, 'Scientist', 2000000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d002', 05, 'Scientist', 1222333, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d002', 06, 'Scientist', 4000000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
    INTO Department (department_id, employee_id, occupation, salary, as_of_date) 
    VALUES ('d002', 07, 'Scientist', 2100000, TO_DATE('2024-01-01', 'YYYY-MM-DD'))
SELECT * FROM dual;
8 rows affected

SELECT department_id
FROM Department
GROUP BY department_id
HAVING COUNT(DISTINCT occupation) = 1
   AND MAX(occupation) = 'Scientist';


DEPARTMENT_ID


d002


SELECT department_id
FROM Department d
WHERE NOT EXISTS (
    SELECT 1
    FROM Department d2
    WHERE d.department_id = d2.department_id
    AND d2.occupation != 'Scientist'
)
GROUP BY department_id;


DEPARTMENT_ID


d002