DBMS Korth Edition 6 Exercise 3 Question 24 (Page No. 111)

Question

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.

Answer 1

select D.dept-name from (select dept-name, sum(salary) as total_salary from instructor group by dept-id as D) where D.total_salary >= (select avg(salary) from instructor)