SQL


1-)
Realizar uma consulta que retorne os dados do Paciente (CPF e nome),
quantidade de consultas/atendimentos realizados e o valor total dos mesmos. Ordenar a consulta por CPF.




SELECT P.cpf_paciente, P.nome, COUNT(CON.ID_consulta) AS consultas, SUM(PROC.preco) AS precos
            FROM paciente P
        LEFT OUTER JOIN consulta CON 
			ON P.cpf_paciente = CON.cpf_paciente
        LEFT OUTER JOIN informacoesConsulta INF 
			ON CON.id_consulta = INF.id_consulta
        LEFT OUTER JOIN procedimento PROC 
			ON INF.id_proc = PROC.id_proc
GROUP BY P.cpf_paciente, P.nome ORDER BY P.cpf_paciente; 

minha
SELECT p.cpf_paciente, p.nome, COUNT(c.id_consulta) as consultas, SUM(pr.preco) as preco 
	FROM Paciente p 
		LEFT OUTER JOIN Consulta c
			ON p.cpf_paciente=c.cpf_paciente
		LEFT OUTER JOIN InformacoesConsulta ic
			ON c.id_consulta = ic.id_consulta
		LEFT OUTER JOIN Procedimento pr
			ON ic.id_proc = pr.id_proc
GROUP BY p.cpf_paciente, p.nome ORDER BY p.cpf_paciente;


2-)
Realizar uma consulta que retorne as consultas/atendimentos realizados
durante um determinado período (20/07/2011 a 20/08/2011, por exemplo), indicando os dentistas envolvidos (CPF, CRO e nome). Ordenar as consultas por data/horário.

minha
SELECT c.id_consulta, d.cro, f.cpf_func, f.nome, c.horario FROM Funcionario f, Consulta c, Dentista d 
WHERE c.cro = d.cro AND d.cpf_func = f.cpf_func AND c.horario BETWEEN to_date('20/08/2011') AND to_date('20/09/2011') ORDER BY c.horario;

SELECT DISTINCT CON.id_consulta, DEN.cro, DEN.cpf_func, FUN.nome, CON.horario
FROM consulta CON
INNER JOIN dentista DEN ON CON.cro = DEN.cro
INNER JOIN funcionario FUN ON DEN.cpf_func = FUN.cpf_func
WHERE CON.horario BETWEEN to_date('20/08/2011') AND to_date('20/09/2011')
ORDER BY CON.horario;


 
3-)
Realizar uma consulta que retorne os Funcionários que possuam nome começando com a letra A e recebam salários entre 500 e 1000 reais. 
Ordene-os por data de nascimento de forma decrescente.

minha
SELECT nome, cpf_func, salario, data_de_nascimento FROM Funcionario
WHERE nome LIKE 'A%' AND Salario BETWEEN 500 AND 1000 ORDER BY data_de_nascimento DESC;

SELECT cpf_func, nome, salario FROM funcionario
WHERE nome LIKE 'A%' AND salario BETWEEN 500 AND 1000
ORDER BY data_de_nascimento DESC;
 






4-)
Considerando os funcionários que supervisionam outros, informe CPF, nome e média salarial dos funcionários supervisionados por estes. Ordenar pela média salarial indicada anteriormente.


SELECT nome, cpf_func, media FROM Funcionario
			INNER JOIN (SELECT cpf_supervisor, AVG(salario) as media FROM Funcionario WHERE cpf_supervisor IS NOT NULL GROUP BY cpf_supervisor) sub
				ON funcionario.cpf_supervisor = sub.cpf_supervisor;        


 
5-)Crie uma consulta que liste o cpf, o nome e o salário de todos os funcionários que não são supervisores e que também não são supervisionados.


SELECT F1.cpf_func, F1.nome, F1.salario
FROM funcionario F1
WHERE F1.CPF_Supervisor IS NULL
AND F1.cpf_func NOT IN (SELECT F2.cpf_supervisor FROM Funcionario F2 WHERE F2.CPF_SUPERVISOR IS NOT NULL);




6-)Crie uma consulta que retorne todos os pacientes que possuem um telefone da TIM, ou seja, que começam com 96, 97, 98 ou 99 e que participaram de pelo menos 2 consultas.


SELECT P.cpf_paciente, P.nome, P.telefone1, P.telefone2
FROM paciente P
WHERE P.telefone1 like ('96%') or P.telefone1 like ('97%') or P.telefone1 like ('98%') or P.telefone1 like ('99%')
OR  P.telefone2 like ('96%') or P.telefone2 like ('97%') or P.telefone2 like ('98%') or P.telefone2 like ('99%')
AND (SELECT COUNT(*) FROM Consulta C WHERE C.cpf_paciente = P.cpf_paciente) > 1;
 
 


















7-)Retorne o lucro da clínica no mês de agosto. O quanto foi recebido pelas consultas (procedimentos) menos os gastos com salários e materiais.






------
(SELECT SUM(p.preco) as faturamento FROM InformacoesConsulta ic, Consulta c, Procedimento p
			WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta AND EXTRACT(MONTH FROM c.horario)='8');

(SELECT SUM(m.preco) as despesas FROM Requerimento r, Material m, Informacoesrequerimento ir
			WHERE m.id_mat = ir.id_mat AND r.id_pedido=ir.id_pedido AND EXTRACT(MONTH FROM r.horario)='8');


(SELECT SUM(salario) as salarios FROM Funcionario);


-------
SELECT SUM(preco) as faturamento FROM InformacoesConsulta ic
	INNER JOIN Procedimento p
			ON ic.id_proc =p.id_proc
	INNER JOIN Consulta c
			ON c.id_consulta = ic.id_consulta
	WHERE EXTRACT(MONTH FROM c.horario)='8';
    
    
SELECT SUM(preco) as despesas FROM InformacoesRequerimento ir
	INNER JOIN Material m
		ON m.id_mat =ir.id_mat
	INNER JOIN Requerimento r
		ON r.id_pedido = ir.id_pedido
	WHERE EXTRACT(MONTH FROM r.horario)='8';
-------


Final:


SELECT (faturamento - despesas - salarios) as lucro FROM (SELECT SUM(p.preco) as faturamento FROM Consulta c, Procedimento p, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta AND EXTRACT(MONTH FROM c.horario)='8') ,
    (SELECT SUM(m.preco) as despesas FROM Requerimento r, Material m, Informacoesrequerimento ir
            WHERE m.id_mat = ir.id_mat AND r.id_pedido=ir.id_pedido AND EXTRACT(MONTH FROM r.horario)='8'), (SELECT SUM(salario) as salarios FROM Funcionario);     


 














8-)
Retorne a lista dos Funcionários que não são dentistas (CPF e nome) que possuem sobrenome 'Santos' e morem no bairro da Várzea. Retornar supervisor destes também, caso haja.


SELECT F.cpf_func, F.nome, F.cpf_supervisor
FROM funcionario F
    INNER JOIN endereco E on E.cod_end = F.cod_end
WHERE F.nome LIKE '% Santos'
AND E.bairro LIKE 'Várzea'
AND F.cpf_func NOT IN (SELECT cpf_func FROM Dentista);
 
9-)
Calcule a média salarial dos funcionários com idade entre 20 e 30 anos, agrupando-os por ano de nascimento.
 
        SELECT DISTINCT EXTRACT(YEAR FROM data_de_nascimento) Ano_Nasc, AVG(salario) as Media_Salario
            FROM funcionario
            WHERE TRUNC((months_between(sysdate, data_de_nascimento))/12)
            BETWEEN 20 AND 30
            GROUP BY (EXTRACT(YEAR FROM data_de_nascimento)) 
ORDER BY (EXTRACT(YEAR FROM data_de_nascimento));


10-)
Os pacientes ganham bônus de acordo com o valor gasto as consultas. Para cada consulta fechado com valor até 100, o cliente recebe desconto de 10%. Para consultas com valor entre 100 e 200, o cliente recebe 15% de desconto. Para contratos com valor maior que 200, o cliente recebe 20% de desconto. Calcule o valor total do desconto oferecido pela empresa.


 (SELECT SUM(SUM(preco)*0.10) as desconto1 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) < 100);
            
 (SELECT SUM(SUM(preco)*0.15) as desconto2 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) BETWEEN 100 AND 200);
            
 (SELECT SUM(SUM(preco)*0.20) as desconto3 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) > 300);
            
Soma total:
SELECT desconto1 + desconto2 + desconto3 FROM  (SELECT SUM(SUM(preco)*0.10) as desconto1 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) < 100), (SELECT SUM(SUM(preco)*0.20) as desconto3 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) > 300), (SELECT SUM(SUM(preco)*0.15) as desconto2 FROM Procedimento p, Consulta c, InformacoesConsulta ic
    WHERE p.id_proc = ic.id_proc AND c.id_consulta = ic.id_consulta
            GROUP BY ic.id_consulta HAVING SUM(preco) BETWEEN 100 AND 200);


















PL


1)Na clínica odontológica não é permitido funcionários com idade menor que 18 anos e com salário menor que 545 reais.
Crie um trigger que informe o problema utilizando EXCEPTION.


CREATE OR REPLACE TRIGGER verifica
BEFORE INSERT OR UPDATE ON funcionario
FOR EACH ROW
DECLARE
  e_salario_invalido EXCEPTION;
  e_idade_invalida EXCEPTION;
BEGIN
    IF (:NEW.salario < 545)
            THEN RAISE e_salario_invalido;
    END IF;
    IF  (ROUND((SYSDATE - :NEW.data_de_nascimento)/365.24,0) < 18)
            THEN RAISE e_idade_invalida;
    END IF;
    
    EXCEPTION
    WHEN e_salario_invalido THEN RAISE_APPLICATION_ERROR(-20105,'O salario deve ser maior que 545.');    
            WHEN e_idade_invalida THEN RAISE_APPLICATION_ERROR(-20106,'A idade deve ser pelo menos 18 anos.');
            
            
END;
/






























2)Crie um procedimento que, dado um determinado mês, imprima o dia em que a clínica arrecadou mais dinheiro informando o valor.








CREATE OR REPLACE PROCEDURE melhor_faturamento(mes NUMBER) IS
ano NUMBER;
prox_ano NUMBER;
ano_temp char(4);
proximo_mes NUMBER;
mes_invalido EXCEPTION;
melhor_dia CHAR(2);
faturamento NUMBER;
BEGIN


    SELECT TO_CHAR(SYSDATE, 'yyyy') INTO ano_temp FROM DUAL;
    SELECT TO_NUMBER(ano_temp, 9999) INTO ano FROM DUAL;
    
    if(mes < 1 OR mes > 12) THEN
            RAISE mes_invalido;
    ELSE
            if(mes = 12) THEN
                    prox_ano := ano+1;
                    proximo_mes := 1;
            ELSE
                    prox_ano := ano;
                    proximo_mes := mes+1;
            END IF;
            
            SELECT  MAX(SUM(P.preco)) INTO faturamento FROM Consulta C
                    INNER JOIN InformacoesConsulta IC ON C.id_consulta = IC.id_consulta
                    INNER JOIN Procedimento P ON IC.id_proc = P.id_proc
                    WHERE C.horario BETWEEN to_date(mes || '/' || ano , 'mm/yyyy') AND to_date(proximo_mes || '/' || prox_ano , 'mm/yyyy')
            GROUP BY(C.horario);
            
            SELECT to_char(C.horario,'dd') INTO melhor_dia FROM Consulta C
                    INNER JOIN InformacoesConsulta IC ON C.id_consulta = IC.id_consulta
                    INNER JOIN Procedimento P ON IC.id_proc = P.id_proc
            WHERE C.horario BETWEEN to_date(mes || '/' || ano , 'mm/yyyy') AND to_date(proximo_mes || '/' || prox_ano , 'mm/yyyy')
            GROUP BY(C.horario)
            HAVING SUM(P.preco) = faturamento;    
            
            dbms_output.put_line(melhor_dia || ' - ' || faturamento);
    END IF;
    
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
                    dbms_output.put_line('Não há consultas nesse mes');
            WHEN mes_invalido THEN
                    dbms_output.put_line('MES INVALIDO');
END;
/






3)Crie uma função que insira um endereço no banco (Código, Logradouro, Número, Bairro, CEP, cidade e estado). Retornar todos os dados do endereço recém cadastrado concatenados em uma string com padrão: "Código: X, Logradouro: Y, Número: Z [...]".


create or replace
FUNCTION insere_endereco (cod_end Endereco.cod_end%TYPE,
rua  Endereco.logradouro%TYPE, num Endereco.numero%TYPE, bairro  ENDERECO.bairro%TYPE,
cep  Endereco.CEP%TYPE, cidade  Endereco.cidade%TYPE, estado Endereco.estado%TYPE) RETURN varchar2 IS
retorno varchar2(200);
BEGIN


   INSERT INTO Endereco(cod_end, logradouro, numero, bairro, cep, cidade, estado) VALUES(cod_end,
   rua, num, bairro, cep, cidade, estado);


   retorno := ('CODIGO ' || cod_end || ' Logradouro ' || rua || ' Numero ' || num
                   || ' Bairro ' || bairro || ' CEP ' || cep || ' Cidade ' || cidade || ' Estado ' || estado );
   RETURN retorno;


END;






4)Crie um pacote que tenha 3 procedimentos, um para inserir dentistas, outro para zeladores e outro para recepcionistas.


CREATE OR REPLACE PACKAGE cadastroPackage AS
    PROCEDURE insere_dentista(cro Dentista.cro%TYPE,
                                                      cpf_func Dentista.cpf_func%TYPE);
    PROCEDURE insere_zelador(cod_zel Zelador.cod_zel%TYPE,
                                                     cpf_func Zelador.cpf_func%TYPE);
    PROCEDURE insere_recepcionista(cod_rec Recepcionista.cod_rec%TYPE,
                                                                    cpf_func Recepcionista.cpf_func%TYPE);
END cadastroPackage;


CREATE OR REPLACE PACKAGE BODY cadastroPackage AS
    PROCEDURE insere_dentista(cro Dentista.cro%TYPE, cpf_func Dentista.cpf_func%TYPE) IS
    BEGIN
            INSERT INTO Dentista(cro, cpf_func) VALUES(cro, cpf_func);
    END;
    
    PROCEDURE insere_zelador(cod_zel Zelador.cod_zel%TYPE,
                                                     cpf_func Zelador.cpf_func%TYPE) IS
    BEGIN
            INSERT INTO Zelador(cod_zel, cpf_func) VALUES(cod_zel, cpf_func);
    END;
    
    PROCEDURE insere_recepcionista(cod_rec Recepcionista.cod_rec%TYPE, cpf_func Recepcionista.cpf_func%TYPE) IS
    BEGIN
            INSERT INTO Recepcionista(cod_rec, cpf_func) VALUES(cod_rec, cpf_func);
    END;
END cadastroPackage;


5) Considere que os funcionários recebem gratificações periodicamente. Os funcionários que não supervisionam outros gerentes recebem gratificações a cada ano. Aqueles que supervisionam até 2 outros funcionários recebem gratificações a cada 6 meses. Os demais funcionários que supervisionam mais de 2  recebem gratificação a  cada 3 meses. Considerando que o valor da gratificação é R$ 500,  crie uma função que retorne o valor total gasto pela clínica com gratificações durante 2 anos.
    
    CREATE OR REPLACE FUNCTION valor_bonus RETURN NUMBER IS
   
   retorno NUMBER;
   qtd_zero NUMBER;
   qtd_dois NUMBER;
   qtd_tres NUMBER;
   
   v_cpf VARCHAR2(11);
   v_cpfsuper VARCHAR2(11);
   v_var NUMBER;
   CURSOR c_bonus IS SELECT cpf_func, cpf_supervisor FROM funcionario;
   
   BEGIN
   OPEN c_bonus;
   qtd_zero := 0;
   qtd_dois := 0;
   qtd_tres := 0;
   LOOP
    
    FETCH c_bonus into v_cpf, v_cpfsuper;
    EXIT WHEN c_bonus%NOTFOUND;
    
    SELECT COUNT(*) INTO v_var from funcionario F where F.cpf_supervisor = v_cpf;
    
               IF (v_var=0) THEN qtd_zero := qtd_zero+1;
               ELSIF (v_var=1) THEN qtd_dois := qtd_dois+1;
               ELSIF (v_var=2) THEN qtd_dois := qtd_dois+1;
               ELSIF (v_var>2) THEN qtd_tres := qtd_tres+1;
                   END IF;
           
           END LOOP;
           CLOSE c_bonus;
           qtd_zero := qtd_zero*2*500;
           qtd_dois := qtd_dois*4*500;
           qtd_tres := qtd_tres*8*500;




           retorno := qtd_zero+qtd_dois+qtd_tres;


           RETURN retorno;
   END;
   /








6)Crie uma tabela chamada log_consulta que armazene as operações realizadas na tabela Consulta. A tabela deve possuir data e operação realizada (INSERT, UPDATE, DELETE). Criar um trigger que armazene os dados nessa tabela de log.


CREATE TABLE log_consulta(
horario timestamp,
operacao varchar2(15)
)


CREATE OR REPLACE TRIGGER controle_log AFTER INSERT OR UPDATE OR DELETE ON Consulta
BEGIN
    if(INSERTING) THEN
            INSERT INTO log_consulta(horario, operacao) VALUES (SYSDATE, 'INSERT');
    ELSIF (UPDATING) THEN


            INSERT INTO log_consulta(horario, operacao) VALUES (SYSDATE,'UPDATE');
    ELSIF (DELETING) THEN
            INSERT INTO log_consulta(horario, operacao) VALUES (SYSDATE, 'DELETE');
    END IF;
END;
/


7)Criar um procedimento que informe o nome, endereço (CEP, logradouro, cidade e número) de um dado funcionário (passar CPF como parâmetro).


CREATE OR REPLACE PROCEDURE imprime_endereco(cpf IN funcionario.cpf_func%type ) IS


nome funcionario.nome%type;
logradouro endereco.logradouro%type;
cidade endereco.cidade%type;
numero endereco.numero%type;
cep endereco.cep%type;


CURSOR c_end IS SELECT F.nome, E.logradouro, E.cidade, E.numero, E.cep FROM funcionario F, endereco E
where E.cod_end = F.cod_end AND cpf=cpf_func;


BEGIN
    
    OPEN c_end;
    
    FETCH c_end INTO nome,logradouro,cidade,numero,cep;
   
   dbms_output.put_line('Nome: '||nome|| ' Logradouro: ' ||logradouro || ' Numero: ' ||numero || ' Cidade: ' ||cidade|| ' CEP: ' ||cep);


CLOSE c_end;
END;
/


8)Criar bloco que imprima a lista dos fornecimentos ordenados por data de forma que os mais recentes sejam mostrados primeiro. Mostrar ID do fornecimento, materiais fornecidos (id, nome e preço) e o Fornecedor (CNPJ e nome). Utilizar CURSOR.


Ao inves de imprimirmos o cnpj do fornecedor imprimimos o id, pois nao guardamos o cnpj dele.


DECLARE


   /* Output variables to hold the result of the query: */
   
   id_material material.id_mat%TYPE;
   nome material.nome%TYPE;
   preco material.preco%TYPE;
   id_pedido requerimento.id_pedido%TYPE;
   id_forn fornecedor.id_forn%TYPE;
   fnome fornecedor.nome%TYPE;
   
   /* Cursor declaration: */
   
    CURSOR fornecimento IS
            SELECT m.id_mat, m.nome, m.preco, r.id_pedido, f.id_forn, f.nome FROM Requerimento r, Material m, InformacoesRequerimento ir, Fornecedor f
    WHERE ir.id_pedido = r.id_pedido AND ir.id_mat = m.id_mat AND r.id_forn=f.id_forn ORDER BY horario DESC;
            
BEGIN
    
    OPEN fornecimento;
    
    LOOP
            
            FETCH fornecimento INTO id_material, nome, preco, id_pedido, id_forn, fnome;
            
            EXIT WHEN fornecimento%NOTFOUND;
            
            dbms_output.put_line(id_pedido || ' - ' || id_material || ' - ' || nome || ' - ' || preco || ' - ' || id_forn || ' - ' || fnome);
            
            
    END LOOP;
    
    CLOSE fornecimento;
    
END;
/


9)Um pedido de fornecimento de materal não pode conter mais de 5 materiais distintos(pode ter mais de 5 se for repetido,ou seja, o mesmo material).Criar mecanismo que respeite as regras descritas anteriormente.


CREATE OR REPLACE TRIGGER controle_fornecimento BEFORE INSERT ON InformacoesRequerimento
FOR EACH ROW
DECLARE
numero_produtos NUMBER;
produtos_distintos NUMBER;
controle InformacoesRequerimento.id_mat%TYPE;
BEGIN
    SELECT COUNT(IR.id_pedido) INTO numero_produtos FROM InformacoesRequerimento IR
    WHERE :NEW.id_pedido = IR.id_pedido;
    dbms_output.put_line('TEM '|| numero_produtos);
    
    IF (numero_produtos > 4) THEN
            RAISE_APPLICATION_ERROR (-20012, 'pode ter mais de 5 produtos não mago');
    END IF;
   
    EXCEPTION
            WHEN NO_DATA_FOUND THEN
                    dbms_output.put_line('PEDIDO ACEITO');
END;
/








10) A clínica odontológica está completando 5 anos de funcionamento e
deseja aumentar
 os salários de todos os funcionários! Para os dentistas,
o aumento será igual a quantidade de pacientes multiplicado por 15.
Para os demais funcionários, o aumento será de 5% para homens e 10% para mulheres.
Utilizar procedimento para Informar o salário antes e após o aumento.




CREATE OR REPLACE PROCEDURE AumentoSalario IS


cursor c_salario IS SELECT nome, cpf_func, salario, sexo FROM funcionario;
v_nome VARCHAR2(50);
v_salario_novo NUMBER;
v_salario_antigo NUMBER;
v_sexo CHAR(1);
v_cpf VARCHAR(11);
v_numero NUMBER;
v_variavel NUMBER;
BEGIN
OPEN c_salario;


LOOP


FETCH c_salario INTO v_nome, v_cpf, v_salario_antigo,v_sexo;
EXIT WHEN c_salario%NOTFOUND;


    SELECT COUNT(cpf_func) into v_variavel FROM dentista WHERE v_cpf = cpf_func;
    SELECT COUNT(cpf_paciente) into v_numero FROM paciente;
        IF (v_variavel=1) THEN v_salario_novo := v_salario_antigo+ v_numero*15;
                   
        ELSIF (v_sexo = 'H') THEN v_salario_novo := v_salario_antigo*.05 + v_salario_antigo;
                    
        ELSIF (v_sexo = 'M') THEN v_salario_novo := v_salario_antigo*.1 + v_salario_antigo;
   END IF;
        dbms_output.put_line(v_nome);
        dbms_output.put_line(v_salario_antigo);
        dbms_output.put_line(v_salario_novo);
   END LOOP;
   
   CLOSE c_salario;
   END;
   /