Consultas sql avançadas

PostgreSQL

select d.nome_dep, e.nome,
(select sum(valor) from vencimento tv inner join venc_emp vp on tv.cod_venc=vp.cod_venc group by matr having matr=e.matr) as “Salario Bruto”,
(select sum((select sum(valor) from vencimento v1 inner join venc_emp vp on v1.cod_venc=vp.cod_venc group by matr having vp.matr=e.matr)*indice/100) from desconto tv inner join desc_emp vp on tv.cod_desc=vp.cod_desc group by vp.matr having vp.matr=e.matr) as "Descontos",
((select sum(valor) from vencimento tv inner join venc_emp vp on tv.cod_venc=vp.cod_venc group by matr having matr=e.matr) - (select sum((select sum(valor) from vencimento tv inner join venc_emp vp on tv.cod_venc=vp.cod_venc group by matr having matr=e.matr)*indice/100) from desconto tv inner join desc_emp vp on tv.cod_desc=vp.cod_desc group by matr having matr=e.matr)) as "Salario Liquido" from departamento d inner join empregado e on e.lotacao=d.cod_dep order by d.nome_dep, 3 desc

SQL Server (não permite subqueries dentro de funções agregadas)

create view v_salario_bruto (matr,sal_bruto) as select matr, sum(valor) from vencimento tv inner join venc_emp vp on tv.cod_venc=vp.cod_venc group by matr

select d.nome_dep, e.nome,
(select sal_bruto from v_salario_bruto where matr=e.matr) as 'Salario Bruto',
(select sum(vsb.sal_bruto*indice/100) from desconto tv inner join desc_emp vp inner join v_salario_bruto vsb on vsb.matr=vp.matr on tv.cod_desc=vp.cod_desc group by vp.matr having vp.matr=e.matr) as 'Descontos',
((select sal_bruto from v_salario_bruto where matr=e.matr) - (select sum(vsb.sal_bruto*indice/100) from desconto tv inner join desc_emp vp inner join v_salario_bruto vsb on vsb.matr=vp.matr on tv.cod_desc=vp.cod_desc group by vp.matr having vp.matr=e.matr)) as 'Salario Liquido' from departamento d inner join empregado e on e.lotacao=d.cod_dep order by d.nome_dep, 3 desc

select nome_dep, (select count(matr) from empregado where lotacao=d.cod_dep) as 'Numero de