Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE staff (sname, job) AS
SELECT 'JONES', 'MANAGER' FROM DUAL UNION ALL
SELECT 'HAYAT', 'MANAGER' FROM DUAL UNION ALL
SELECT 'CLARK', 'MANAGER' FROM DUAL UNION ALL
SELECT 'ALICE', 'CEO'     FROM DUAL UNION ALL
SELECT 'BERYL', 'CTO'     FROM DUAL
5 rows affected

SELECT SNAME,
       JOB
FROM   STAFF
WHERE  JOB IN (SELECT JOB FROM STAFF WHERE SNAME = 'JONES')
AND    sname != 'JONES';


SNAME
JOB


HAYAT
MANAGER

CLARK
MANAGER


SELECT sname,
       job
FROM   (
  SELECT sname,
         job,
         COUNT(CASE sname WHEN 'JONES' THEN 1 END) OVER (PARTITION BY job)
           AS num_jones
  FROM   staff
)
WHERE  num_jones > 0
AND    sname != 'JONES';


SNAME
JOB


HAYAT
MANAGER

CLARK
MANAGER