3.23

Consider the query:

WITH dept_total(dept_name, value) AS (
    SELECT dept_name, SUM(salary)
    FROM instructor
    GROUP BY dept_name
), dept_total_avg(value) AS (
    SELECT AVG(value)
    FROM dept_total
)
SELECT dept_name
FROM dept_total, dept_total_avg
WHERE dept_total.value >= dept_total_avg.value

Rewrite this query without using the with construct.

---

SELECT dept_name
FROM (SELECT dept_name, SUM(salary) AS value FROM instructor GROUP BY dept_name) AS dept_total, 
    (SELECT AVG(value) AS value FROM (SELECT dept_name, SUM(salary) AS value FROM instructor GROUP BY dept_name) AS x) AS dept_total_avg
WHERE dept_total.value >= dept_total_avg.value