SELECT a.dept_no, a.dept_name,
       mng.emp_no, mng.first_name, mng.last_name
  FROM departments a,
       LATERAL
       (SELECT b.dept_no, b.emp_no, c.first_name, c.last_name
          FROM dept_manager b, employees c
         WHERE b.emp_no = c.emp_no
           AND SYSDATE() BETWEEN b.from_date AND b.to_date
           AND a.dept_no = b.dept_no
       ) mng
 ORDER BY 1;