2992.sql

select bubu.departamento as departamento, toucinho.div as divisao,bubu.maximo as media from
--Seleciona o nome e o valor da maior média salarial de uma divisão do departamento
(select div_dep.depar as departamento,max(div_dep.media) as maximo from (
select dep.nome as depar,div.nome,round(avg(vencimentos.salario_bruto-descontos.desconto),2) as media 
from 
	((	select e.matr as mat, coalesce(sum(v.valor),0) as salario_bruto from empregado as e
	left join emp_venc as ev on (ev.matr = e.matr)
	left join vencimento as v on (v.cod_venc = ev.cod_venc)
	group by e.matr) as vencimentos 
	inner join 
	(	select e.matr as mat,coalesce(sum(des.valor),0) as desconto from empregado as e
		left join emp_desc as ed on (ed.matr = e.matr) --equivocado
		left join desconto as des on (des.cod_desc = ed.cod_desc) --equivocado
		group by e.matr) as descontos on (vencimentos.mat = descontos.mat)
	inner join empregado as e on (vencimentos.mat = e.matr)
	inner join divisao as div on (e.lotacao_div = div.cod_divisao)
	inner join departamento as dep on (div.cod_dep = dep.cod_dep))
	group by dep.nome,div.nome
	order by media desc
) as div_dep
group by div_dep.depar
order by maximo desc
) as bubu
inner join 
--rascunho
(select dep.nome as depar,div.nome as div,round(avg(vencimentos.salario_bruto-descontos.desconto),2) as media 
from 
	((	select e.matr as mat, coalesce(sum(v.valor),0) as salario_bruto from empregado as e
	left join emp_venc as ev on (ev.matr = e.matr)
	left join vencimento as v on (v.cod_venc = ev.cod_venc)
	group by e.matr) as vencimentos 
	inner join 
	(	select e.matr as mat,coalesce(sum(des.valor),0) as desconto from empregado as e
		left join emp_desc as ed on (ed.matr = e.matr) --equivocado
		left join desconto as des on (des.cod_desc = ed.cod_desc) --equivocado
		group by e.matr) as descontos on (vencimentos.mat = descontos.mat)
	inner join empregado as e on (vencimentos.mat = e.matr)
	inner join divisao as div on (e.lotacao_div = div.cod_divisao)
	inner join departamento as dep on (div.cod_dep = dep.cod_dep))
	group by dep.nome,div.nome
	order by media desc) as toucinho on (bubu.maximo = toucinho.media and bubu.departamento = toucinho.depar)
order by bubu.maximo desc;