[Bug] Possível divergencia de resultados - Oracle Database: manipulando dados com comando DML

• CURSO: Oracle Database: manipulando dados com comando DML
• Tópico: 06 - Auto-incremento e TRIGGERS
• Exercício: 08 - Relatório de orçamento por funcionário.

Códigos da aula:

PRIMEIRO PASSO - CORRETO:

--CONTANDO O NUMERO DE FUNCIONARIOS DE ACORDO COM O COD_DEPARTAMENTO E AGRUPANDO POR  COD_DEPARTAMENTO
    SELECT TD.COD_DEPARTAMENTO, COUNT() AS NUM_FUNCIONARIOS FROM
    TAB_DEPARTAMENTO TD
    INNER JOIN
    TAB_FUNCIONARIO TF
    ON TD.COD_DEPARTAMENTO = TF.COD_DEPARTAMENTO
    GROUP BY TD.COD_DEPARTAMENTO;

IMAGEM - 1

SEGUNDO PASSO - CORRETO:

--VALOR TOTAL DO ORCAMENTO DE ACORDO COM O COD_DEPARTAMENTO

    SELECT TD.COD_DEPARTAMENTO, SUM(TP.ORCAMENTO) AS ORCAMENTO FROM
    TAB_DEPARTAMENTO TD
    INNER JOIN
    TAB_PROJETO TP
    ON TD.COD_DEPARTAMENTO = TP.COD_DEPARTAMENTO
    GROUP BY TD.COD_DEPARTAMENTO;

IMAGEM - 2

TERCEIRO PASSO - DIVERGENTE:

--JUNTANDO OS SCRIPTS DA CONTAGEM DE FUNCIONÁRIOS E SOMA DO ORÇAMENTO 

SELECT TD.CODDEPARTAMENTO, COUNT() AS NUMFUNCIONARIOS, 
SUM(TP.ORCAMENTO) AS ORCAMENTO FROM
TABDEPARTAMENTO TD
INNER JOIN
TABFUNCIONARIO TF
ON TD.CODDEPARTAMENTO = TF.CODDEPARTAMENTO
INNER JOIN
TABPROJETO TP
ON TD.CODDEPARTAMENTO = TP.CODDEPARTAMENTO
GROUP BY TD.CODDEPARTAMENTO;

IMAGEM - 3

Conforme a imagem, percebemos a divergência nos resultados, contagem dos funcionários e soma do orçamento. Uma possível solução para o resultado seria a junção das tabelas com inner join e sub select, conforme abaixo:

CRIAÇÃO DA TABELA EM COMUM (AUXILIAR)

--CRIACAO DA TABELA AUXILIAR

CREATE TABLE TAB_DEP_PROJ_FATURAMENTO
(NOME_DEPARTAMENTO VARCHAR2(50),
ORCAMENTO FLOAT
);

PRIMEIRO PASSO - POSSÍVEL CORREÇÃO:

SELECT P.COD_DEPARTAMENTO, SUM(P.ORCAMENTO) AS SOMA
FROM TAB_PROJETO P
GROUP BY P.COD_DEPARTAMENTO

SEGUNDO PASSO - POSSÍVEL CORREÇÃO:

SELECT D.NOME_DEPARTAMENTO, COUNT(F.COD_FUNCIONARIO) AS QTD, SUB_SEL.SOMA, ROUND(SUB_SEL.SOMA / COUNT(F.COD_FUNCIONARIO),1) AS TOTAL
FROM TAB_FUNCIONARIO F
INNER JOIN
(SELECT P.COD_DEPARTAMENTO, SUM(P.ORCAMENTO) AS SOMA
FROM TAB_PROJETO P
GROUP BY P.COD_DEPARTAMENTO) SUB_SEL
ON F.COD_DEPARTAMENTO = SUB_SEL.COD_DEPARTAMENTO
INNER JOIN TAB_DEPARTAMENTO D
ON F.COD_DEPARTAMENTO = D.COD_DEPARTAMENTO
GROUP BY D.NOME_DEPARTAMENTO,  SUB_SEL.SOMA;

IMAGEM POSSIVEL SOLUÇÃO - 1

TERCEIRO PASSO - POSSÍVEL CORREÇÃO:

--ELEBORAÇÃO DO SELECT PARA A TRIGGER
DELETE FROM TAB_DEP_PROJ_FATURAMENTO;
INSERT INTO TAB_DEP_PROJ_FATURAMENTO
SELECT D.NOME_DEPARTAMENTO, COUNT(F.COD_FUNCIONARIO) AS QTD, SUB_SEL.SOMA, ROUND(SUB_SEL.SOMA / COUNT(F.COD_FUNCIONARIO),1) AS TOTAL
FROM TAB_FUNCIONARIO F
INNER JOIN
(SELECT P.COD_DEPARTAMENTO, SUM(P.ORCAMENTO) AS SOMA
FROM TAB_PROJETO P
GROUP BY P.COD_DEPARTAMENTO) SUB_SEL
ON F.COD_DEPARTAMENTO = SUBSEL.COD_DEPARTAMENTO
INNER JOIN TAB_DEPARTAMENTO D
ON F.COD_DEPARTAMENTO = D.COD_DEPARTAMENTO
GROUP BY D.NOME_DEPARTAMENTO,  SUB_SEL.SOMA;

QUARTO PASSO - POSSÍVEL CORREÇÃO:

-- TRIGGER - REALIZAR A TRIGGER PARA TAB_PROJETO, TAB_FUNCIONARIO, TAB_DEPARTAMENTO - BASTA ALTERAR O CAMPO EM DESTAQUE, CONFORME OS NOMES DE TABELAS INFORMADOS

CREATE OR REPLACE TRIGGER TG_TAB_DEP_PROJ_FATURAMENTO
AFTER INSERT OR UPDATE OR DELETE ON **TAB_PROJETO**
BEGIN
DELETE FROM TAB_DEP_PROJ_FATURAMENTO;
INSERT INTO TAB_DEP_PROJ_FATURAMENTO
SELECT D.NOME_DEPARTAMENTO, ROUND(SUB_SEL.SOMA / COUNT(F.COD_FUNCIONARIO),1) AS TOTAL
FROM TAB_FUNCIONARIO F
INNER JOIN
(SELECT P.COD_DEPARTAMENTO, SUM(P.ORCAMENTO) AS SOMA
FROM TAB_PROJETO P
GROUP BY P.COD_DEPARTAMENTO) SUB_SEL
ON F.COD_DEPARTAMENTO = SUB_SEL.COD_DEPARTAMENTO
INNER JOIN TAB_DEPARTAMENTO D
ON F.COD_DEPARTAMENTO = D.COD_DEPARTAMENTO
GROUP BY D.NOME_DEPARTAMENTO,  SUB_SEL.SOMA;
END;

--VERIFICACAO APOS OS INSERTS E DELETES

SELECT  * FROM TAB_DEP_PROJ_FATURAMENTO;
`

IMAGEM POSSÍVEL SOLUÇÃO - 2