Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Employee (
  EmployeeNo PRIMARY KEY,
  EmployeeName,
  EmployeeDesignation,
  CompanyID
) AS
SELECT 12345, 'ABC', 'Doctor',   3434 FROM DUAL UNION ALL
SELECT 4545,  'XYZ', 'Engineer', 3434 FROM DUAL;
2 rows affected

CREATE TABLE Department (
  DepartmentNo PRIMARY KEY,
  EmployeeNo,
  Location
) AS
SELECT '0808', 12345, 'Loc1' FROM DUAL UNION ALL
SELECT '0989', 12345, 'Loc2' FROM DUAL;
2 rows affected

ALTER TABLE Department ADD CONSTRAINT department__employeeno__fk
  FOREIGN KEY (EmployeeNo) REFERENCES Employee(EmployeeNo);
SELECT e.EmployeeNo,
       e.EmployeeName,
       e.EmployeeDesignation,
       COUNT(d.Location) as count
FROM   Employee e
       LEFT OUTER JOIN Department d
       ON (e.EmployeeNo = d.EmployeeNo)
WHERE  e.CompanyID like '3434'
GROUP BY
       e.EmployeeNo,
       e.EmployeeName,
       e.EmployeeDesignation;


EMPLOYEENO
EMPLOYEENAME
EMPLOYEEDESIGNATION
COUNT


4545
XYZ
Engineer
0

12345
ABC
Doctor
2


SELECT e.EmployeeNo,
       MAX(e.EmployeeName) AS EmployeeName,
       MAX(e.EmployeeDesignation) AS EmployeeDesignation,
       COUNT(d.Location) as count
FROM   Employee e
       LEFT OUTER JOIN Department d
       ON (e.EmployeeNo = d.EmployeeNo)
WHERE  e.CompanyID like '3434'
GROUP BY
       e.EmployeeNo


EMPLOYEENO
EMPLOYEENAME
EMPLOYEEDESIGNATION
COUNT


4545
XYZ
Engineer
0

12345
ABC
Doctor
2