consulta.sql

-- QUAIS SÃO OS PROJETOS DE OBRA RELACIONADOS A CADA ENGENHEIRO?
-- LISTE O NOME DA OBRA, CNPJ DO CLIENTE, CODIGO DA OBRA E O TIPO DA OBRA
SELECT F.NOME, O.CNPJ_CLIENTE, O.CODIGO_OBRA, O.TIPO
FROM PROJETO P
JOIN OBRA O ON O.CNPJ_CLIENTE = P.CNPJ_CLIENTE AND O.CODIGO_OBRA = P.CODIGO_OBRA
JOIN ENGENHEIRO E ON E.CPF_ENGENHEIRO = P.CPF_ENGENHEIRO
JOIN FUNCIONARIO F ON F.CPF_FUNCIONARIO = E.CPF_ENGENHEIRO
ORDER BY E.CPF_ENGENHEIRO;

-- LISTE A QUANTIDADE DE ENGENHEIROS PARA CADA ESPECIALIZACAO
-- PARA AS ESPECIALIZAÇÕES QUE TEM MAIS DE UM ENGENHEIRO
SELECT ES.ESPECIALIZACAO, COUNT(*)
FROM ESPECIALIZACAO ES, ENGENHEIRO E  
WHERE E.CPF_ENGENHEIRO = ES.CPF_ENGENHEIRO
GROUP BY ES.ESPECIALIZACAO
HAVING COUNT(*) > 1

-- CRIE UMA VIEW QUE LISTE AS OBRAS (NOME DO CLIENTE, TIPO E DATA DE INICIO E ENTREGA) QUE JA FORAM CONCLUIDAS
-- ORDENADAS PELA DATA DE ENTREGA DA OBRA
DROP VIEW OBRAS_CONCLUIDAS;
CREATE VIEW OBRAS_CONCLUIDAS AS
SELECT C.NOME, O.TIPO, O.DATA_INICIO, O.DATA_ENTREGA
FROM OBRA O
JOIN CLIENTE C ON O.CNPJ_CLIENTE = C.CNPJ_CLIENTE
WHERE O.DATA_ENTREGA IS NOT NULL
ORDER BY O.DATA_INICIO;
SELECT * FROM OBRAS_CONCLUIDAS;

-- CRIANDO UM GATILHO PRA ATUALIZAR A CADA OBRA QUE É ADCIONADA, REMOVIDA OU EDITADA NO BANCO DE DADOS
CREATE SEQUENCE OBRA_AUDIT_SEQ;

CREATE TABLE OBRA_AUDITORIA (
    CODIGO NUMBER,
    ACAO VARCHAR2(7),
    CNPJ_CLIENTE NUMBER,
    CODIGO_OBRA NUMBER,
    NUMERO_REGISTROS NUMBER,
    HORA_ACAO TIMESTAMP,
    CONSTRAINT CODIGO_PK PRIMARY KEY (CODIGO)
);
-- GATILHO DE AUDITORIA PARA TODA VEZ QUE MEXEREM NA TABELA OBRA
CREATE OR REPLACE TRIGGER AUDITORIA_OBRA
FOR INSERT OR DELETE OR UPDATE ON OBRA 
COMPOUND TRIGGER 
X INTEGER;
OPERACAO VARCHAR2(7);
BEFORE STATEMENT IS 
BEGIN 
SELECT COUNT (*) INTO X FROM OBRA;
END BEFORE STATEMENT;
AFTER EACH ROW IS 
BEGIN 
IF INSERTING THEN
    X := X + 1;
    OPERACAO := 'INSERT';
ELSIF DELETING THEN
    X := X - 1;
    OPERACAO := 'DELETE';
ELSE OPERACAO := 'UPDATE';
END IF;
INSERT INTO OBRA_AUDITORIA(CODIGO, ACAO, CNPJ_CLIENTE, CODIGO_OBRA, NUMERO_REGISTROS, HORA_ACAO)
VALUES (OBRA_AUDIT_SEQ.NEXTVAL, OPERACAO, :NEW.CNPJ_CLIENTE, :NEW.CODIGO_OBRA, X, SYSTIMESTAMP);
END AFTER EACH ROW;
END AUDITORIA_OBRA;

-- PROCEDIMENTO QUE ATUALIZA OS SALARIOS ABAIXO DA MEDIA
CREATE OR REPLACE PROCEDURE ATUALIZA_SALARIO IS
   V_MEDIA_SALARIO IN NUMBER;
   v_FUNCIONARIO_REC IN FUNCIONARIO%ROWTYPE;
BEGIN
   -- Declaração da variável para armazenar a média salarial
   DECLARE
      V_MEDIA_SALARIO NUMBER;
   BEGIN
      -- Calcula a média salarial
      SELECT AVG(SALARIO) INTO V_MEDIA_SALARIO FROM FUNCIONARIO;
   END;

   -- Inicializa o cursor
   DECLARE
      CURSOR C_FUNCIONARIOS IS
         SELECT * FROM FUNCIONARIO WHERE SALARIO < V_MEDIA_SALARIO;
   BEGIN
      -- Abre o cursor
      OPEN C_FUNCIONARIOS;

      -- Processa os registros enquanto houver FUNCIONARIOS com salário abaixo da média
      WHILE (C_FUNCIONARIOS%FOUND) LOOP
         FETCH C_FUNCIONARIOS INTO V_FUNCIONARIO_REC;

         -- Atualiza o salário
         UPDATE FUNCIONARIO
         SET SALARIO = V_FUNCIONARIO_REC.SALARIO * 1.05
         WHERE CPF_FUNCIONARIO = V_FUNCIONARIO_REC.CPF_FUNCIONARIO;

         -- Verifica se há mais registros no cursor
         IF C_FUNCIONARIOS%NOTFOUND THEN
            EXIT;
         END IF;
      END LOOP;

      -- Fecha o cursor
      CLOSE C_FUNCIONARIOS;
   END;

   COMMIT; -- Confirma as alterações no banco de dados
END ATUALIZA_SALARIO;
/

-- PACOTE PARA ATUALIZAR AS OBRAS
CREATE OR REPLACE PACKAGE OBRAS_PACKAGE AS
   PROCEDURE ADICIONA_OBRA(
      p_codigo_obra OBRA.CODIGO_OBRA%TYPE,
      p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE,
      p_data_inicio OBRA.DATA_INICIO%TYPE,
      p_data_entrega OBRA.DATA_ENTREGA%TYPE,
      p_tipo OBRA.TIPO%TYPE,
      p_orcamento OBRA.ORCAMENTO%TYPE,
      p_cep OBRA.CEP%TYPE,
      p_numero_endereco OBRA.NUMERO_ENDERECO%TYPE
   );

   PROCEDURE REMOVE_OBRA(p_codigo_obra OBRA.CODIGO_OBRA%TYPE, p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE);

   PROCEDURE ATUALIZA_OBRA(
      p_codigo_obra OBRA.CODIGO_OBRA%TYPE,
      p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE,
      p_nova_data_entrega OBRA.DATA_ENTREGA%TYPE,
      p_novo_tipo OBRA.TIPO%TYPE
   );
END OBRAS_PACKAGE;
/

CREATE OR REPLACE PACKAGE BODY OBRAS_PACKAGE AS
   PROCEDURE ADICIONA_OBRA(
      p_codigo_obra OBRA.CODIGO_OBRA%TYPE,
      p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE,
      p_data_inicio OBRA.DATA_INICIO%TYPE,
      p_data_entrega OBRA.DATA_ENTREGA%TYPE,
      p_tipo OBRA.TIPO%TYPE,
      p_orcamento OBRA.ORCAMENTO%TYPE,
      p_cep OBRA.CEP%TYPE,
      p_numero_endereco OBRA.NUMERO_ENDERECO%TYPE
   ) IS
   BEGIN
      INSERT INTO OBRA (CODIGO_OBRA,CNPJ_CLIENTE,DATA_INICIO,DATA_ENTREGA,TIPO,ORCAMENTO,CEP,NUMERO_ENDERECO) 
      VALUES (p_codigo_obra, p_cnpj_cliente, p_data_inicio, p_data_entrega, p_tipo, p_orcamento, p_cep, p_numero_endereco);
   END ADICIONA_OBRA;

   PROCEDURE REMOVE_OBRA(p_codigo_obra OBRA.CODIGO_OBRA%TYPE, p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE) IS
   BEGIN
      DELETE FROM OBRA WHERE CODIGO_OBRA = p_codigo_obra AND CNPJ_CLIENTE = p_cnpj_cliente;
   END REMOVE_OBRA;

   PROCEDURE ATUALIZA_OBRA(
      p_codigo_obra OBRA.CODIGO_OBRA%TYPE,
      p_cnpj_cliente OBRA.CNPJ_CLIENTE%TYPE,
      p_nova_data_entrega OBRA.DATA_ENTREGA%TYPE,
      p_novo_tipo OBRA.TIPO%TYPE
   ) IS
   BEGIN
      UPDATE OBRA
      SET DATA_ENTREGA = p_nova_data_entrega, TIPO = p_novo_tipo
      WHERE CODIGO_OBRA = p_codigo_obra AND CNPJ_CLIENTE = p_cnpj_cliente;
   END ATUALIZA_OBRA;
END OBRAS_PACKAGE;
/

-- SELECIONA TODOS OS ENGENHEIROS COM ESPECIALIZACAO EM ALGUMA AREA CIVIL
SELECT F.NOME, E.CPF_ENGENHEIRO
FROM ENGENHEIRO E
JOIN FUNCIONARIO F ON E.CPF_ENGENHEIRO = F.CPF_FUNCIONARIO
WHERE CPF_ENGENHEIRO IN (SELECT CPF_ENGENHEIRO 
                         FROM ESPECIALIZACAO 
                         WHERE ESPECIALIZACAO LIKE 'CIVIL%');

-- Seleciona todos os clientes, agrupados por nome e CNPJ_CLIENTE, e a quantidade de obras associadas a cada um
SELECT C.NOME, C.CNPJ_CLIENTE, COUNT(O.CODIGO_OBRA) AS QUANTIDADE_OBRAS
FROM CLIENTE C
LEFT OUTER JOIN OBRA O ON C.CNPJ_CLIENTE = O.CNPJ_CLIENTE
GROUP BY C.NOME, C.CNPJ_CLIENTE
ORDER BY QUANTIDADE_OBRAS DESC, CNPJ_CLIENTE;

-- Calcula o máximo valor de um financiamento feito em obras no bairro "Centro"
SELECT MAX(O.ORCAMENTO) AS MAX_FINANCIAMENTO
FROM OBRA O
WHERE O.ORCAMENTO >= ALL (SELECT O2.ORCAMENTO
                          FROM OBRA O2
                          INNER JOIN ENDERECO E ON O2.CEP = E.CEP
                          WHERE E.BAIRRO = 'Centro');

-- Encontrar o menor salário entre as pessoas que trabalharam na empresa entre 1/1/2023 e 1/4/2023
SELECT MIN(SALARIO) AS MENOR_SALARIO
FROM FUNCIONARIO
WHERE DATA_ADMISSAO BETWEEN TO_DATE('01-JAN-2023', 'DD-MON-YYYY') AND TO_DATE('01-APR-2023', 'DD-MON-YYYY')
  AND SALARIO IN (SELECT SALARIO
                  FROM FUNCIONARIO
                  WHERE DATA_ADMISSAO BETWEEN TO_DATE('01-JAN-2023', 'DD-MON-YYYY') AND TO_DATE('01-APR-2023', 'DD-MON-YYYY'));

-- FUNÇÃO QUE CALCULA A MÉDIA SALARIAL DOS FUNCIONÁRIOS E PRINTA
CREATE OR REPLACE FUNCTION CALCULAR_MEDIA_SALARIOS(CODIGO_OBRA_PARAM IN NUMBER) RETURN NUMBER IS
    V_TOTAL_SALARIOS NUMBER := 0;
    V_NUMERO_FUNCIONARIOS NUMBER := 0;
    V_MEDIA_SALARIOS NUMBER;
BEGIN
    -- Loop sobre os funcionários da obra
    FOR FUNCIONARIO_REC IN (SELECT F.SALARIO
                            FROM FUNCIONARIO F
                            JOIN REALIZA R ON F.CPF_FUNCIONARIO = R.CPF_PEDREIRO
                            WHERE R.CODIGO_OBRA = CODIGO_OBRA_PARAM) 
    LOOP
        -- Adiciona o salário do funcionário ao total
        V_TOTAL_SALARIOS := V_TOTAL_SALARIOS + FUNCIONARIO_REC.SALARIO;
        
        -- Incrementa o número de funcionários
        V_NUMERO_FUNCIONARIOS := V_NUMERO_FUNCIONARIOS + 1;

    END LOOP;

    -- Calcula a média de salários usando CASE WHEN
    V_MEDIA_SALARIOS := 
        CASE
            WHEN V_NUMERO_FUNCIONARIOS > 0 THEN V_TOTAL_SALARIOS / V_NUMERO_FUNCIONARIOS
            ELSE 0
        END;

    -- Retorna a média de salários
    RETURN V_MEDIA_SALARIOS;
END CALCULAR_MEDIA_SALARIOS;
/

-- PERCORRE CLIENTE E PRINTA CNPJ, NOME E RESPONSAVEL NO TERMINAL
DECLARE
    -- DECLARAÇÃO DO TIPO RECORD
    TYPE CLIENTE_RECORD IS RECORD (
        CNPJ_CLIENTE NUMBER,
        NOME VARCHAR2(40),
        RESPONSAVEL VARCHAR2(40)
    );

    -- DECLARAÇÃO DE UMA VARIÁVEL DO TIPO RECORD
    CLIENTE_ROW CLIENTE_RECORD;

    -- DECLARAÇÃO DO CURSOR PARA RECUPERAR DADOS DA TABELA CLIENTE
    CURSOR CLIENTE_CURSOR IS
        SELECT CNPJ_CLIENTE, NOME, RESPONSAVEL
        FROM CLIENTE;
BEGIN
    -- ABRE O CURSOR
    OPEN CLIENTE_CURSOR;

    -- INICIA O LOOP
    LOOP
        -- RECUPERA A PRÓXIMA LINHA DO CURSOR PARA A VARIÁVEL RECORD
        FETCH CLIENTE_CURSOR INTO CLIENTE_ROW;

        -- SAI DO LOOP QUANDO NÃO HÁ MAIS LINHAS PARA RECUPERAR
        EXIT WHEN CLIENTE_CURSOR%NOTFOUND;

        -- EXIBE INFORMAÇÕES DO CLIENTE (SUBSTITUA ISSO PELO QUE VOCÊ PRECISA FAZER)
        DBMS_OUTPUT.PUT_LINE('CNPJ: ' || CLIENTE_ROW.CNPJ_CLIENTE || ', NOME: ' || CLIENTE_ROW.NOME || ', RESPONSAVEL: ' || CLIENTE_ROW.RESPONSAVEL);
    END LOOP;

    -- FECHA O CURSOR
    CLOSE CLIENTE_CURSOR;
END;
/

-- PROCEDIMENTO QUE ADICIONA/CRIA NOVOS PROJETOS
CREATE OR REPLACE PROCEDURE ADICIONAR_NOVO_PROJETO (
    P_CODIGO_OBRA IN NUMBER,
    P_CNPJ_CLIENTE IN NUMBER,
    P_DATA_INICIO IN DATE,
    P_DATA_ENTREGA IN DATE,
    P_TIPO IN VARCHAR2,
    P_ORCAMENTO IN NUMBER,
    P_CEP IN VARCHAR2,
    P_NUMERO_ENDERECO IN NUMBER
) AS
BEGIN
    -- INSERIR DETALHES DO NOVO PROJETO NA TABELA OBRA
    INSERT INTO OBRA (CODIGO_OBRA, CNPJ_CLIENTE, DATA_INICIO, DATA_ENTREGA, TIPO, ORCAMENTO, CEP, NUMERO_ENDERECO)
    VALUES (P_CODIGO_OBRA, P_CNPJ_CLIENTE, P_DATA_INICIO, P_DATA_ENTREGA, P_TIPO, P_ORCAMENTO, P_CEP, P_NUMERO_ENDERECO);

    -- INSERIR ENGENHEIRO PADRÃO PARA O PROJETO NA TABELA PROJETO
    INSERT INTO PROJETO (CPF_ENGENHEIRO, CODIGO_OBRA, CNPJ_CLIENTE, CNPJ_INSTITUICAO)
    VALUES (12345678901, P_CODIGO_OBRA, P_CNPJ_CLIENTE, 12345678901234); -- SUPONDO ENGENHEIRO E INSTITUIÇÃO PADRÃO

    -- INSERIR EQUIPAMENTO PADRÃO PARA O PROJETO NA TABELA SOLICITA
    INSERT INTO SOLICITA (CNPJ_FORNECEDOR, CODIGO_EQUIPAMENTO, CPF_ENGENHEIRO, DATA_HORA)
    VALUES (98765432109876, 1, 12345678901, SYSTIMESTAMP); -- SUPONDO FORNECEDOR E EQUIPAMENTO PADRÃO

    COMMIT;

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('PROJETO COM O MESMO CÓDIGO JÁ EXISTE.');
    WHEN OTHERS THEN
        -- TRATAR EXCEÇÕES AQUI
        DBMS_OUTPUT.PUT_LINE('OCORREU UM ERRO: ' || SQLERRM);
END ADICIONAR_NOVO_PROJETO;
/

-- BUSCA OS FUNCIONARIOS QUE RECEBEM 4000
SELECT CPF_FUNCIONARIO, NOME, SALARIO
FROM FUNCIONARIO
WHERE SALARIO IN (4000);

-- UNE AS TABELAS DE CLIENTES E FORNECEDORES
SELECT CNPJ_CLIENTE, NOME, 'CLIENTE' AS TIPO
FROM CLIENTE
UNION
SELECT CNPJ_FORNECEDOR, NOME, 'FORNECEDOR' AS TIPO
FROM FORNECEDOR;

-- CRIA INDEX EM OBRA PARA FACILITAR CONSULTAS
CREATE INDEX IDX_CNPJ_CODIGO_OBRA ON OBRA (CNPJ_CLIENTE, CODIGO_OBRA);

-- Adicionando a coluna DESCRICAO à tabela OBRA
ALTER TABLE OBRA
ADD DESCRICAO VARCHAR2(255);

-- GRANT E REVOKE
GRANT SELECT, INSERT ON OBRA TO VITOLA;
REVOKE SELECT, INSERT ON OBRA FROM MARIO;