CREATE OR REPLACE VIEW dept_sal_v AS
WITH tmp AS
(SELECT a.dept_no, a.dept_name, COUNT(*) cnt, SUM(c.salary) salary
   FROM departments a, dept_emp_v b, salaries c
  WHERE a.dept_no = b.dept_no
    AND b.emp_no = c.emp_no
    AND SYSDATE() BETWEEN c.from_date AND c.to_date
  GROUP BY a.dept_no, a.dept_name
),
dept_avg AS
(SELECT AVG(salary) avg_sal
   FROM tmp
)
SELECT dept_no, dept_name, salary, avg_sal
  FROM tmp, dept_avg;

SELECT *
  FROM dept_sal_v;