USE mywork;

WITH mng AS
(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
)
SELECT a.dept_no, a.dept_name, b.emp_no, b.first_name, b.last_name
  FROM departments a, mng b
 WHERE a.dept_no = b.dept_no
 ORDER BY 1;