--1)
SELECT P.nome, P2.nome, R.data, A.pontos_debatidos
FROM Pessoa P JOIN Funcionario func ON P.CPF = func.CPF_func
JOIN Reuniao R ON R.CPF_func_reuni = func.CPF_func
JOIN Investidor inv ON inv.CPF_inv = R.CPF_inv_reuni
JOIN Pessoa P2 ON inv.CPF_inv = P2.CPF
JOIN Ata A ON A.codigo = R.cad_ata
WHERE func.cod_departamento = 'D003';

--3)
SELECT P.CPF, P.nome, E.cidade, func.cargo, dep.nome
FROM Funcionario func JOIN Pessoa P ON func.CPF_func = P.CPF
JOIN Endereco E ON E.CPF_habitante = P.CPF
JOIN Departamento dep ON func.cod_departamento = dep.cod_depart
JOIN Pessoa sup ON sup.CPF = func.CPF_gerente
WHERE LOWER(sup.nome) LIKE '%novelino%';

--4)
SELECT DISTINCT P.CPF, P.nome, C.tipo_emprego, T.numero
FROM Pessoa P JOIN Cliente C ON P.CPF = C.CPF_cli
JOIN Contrata_cli_serv Con ON Con.CPF_cli = P.CPF
JOIN Telefone T ON T.CPF_pessoa = P.CPF
WHERE EXTRACT(YEAR FROM Con.data_contrato) >= 2012 AND EXTRACT(YEAR FROM Con.data_contrato) <= 2015;


--5 +/-)
SELECT P.nome, F.cargo, F.salario, E.bairro, P2.nome AS nomeGerente, T.contagem
FROM Pessoa P JOIN Funcionario F ON P.CPF = F.CPF_func
JOIN Endereco E ON E.CPF_habitante = P.CPF
RIGHT JOIN Pessoa P2 ON F.CPF_gerente = P2.CPF
JOIN (SELECT reuni.CPF_func_reuni, COUNT(reuni.CPF_func_reuni) AS contagem
FROM Reuniao reuni GROUP BY (reuni.CPF_func_reuni)) T
ON T.CPF_func_reuni = F.CPF_func
WHERE T.contagem = (SELECT MAX(contagem) FROM (SELECT reuni2.CPF_func_reuni, COUNT(reuni2.CPF_func_reuni) AS contagem
FROM Reuniao reuni2 GROUP BY (reuni2.CPF_func_reuni)));


--6)
(SELECT P.nome, (2015 - EXTRACT(YEAR FROM P.data_nasc)) AS idade, E.estado
FROM Pessoa P JOIN Cliente C
ON P.CPF = C.CPF_cli
JOIN Endereco E ON E.CPF_habitante = P.CPF
WHERE EXTRACT(YEAR FROM P.data_nasc) < 1989)
UNION
(SELECT P.nome, (2015 - EXTRACT(YEAR FROM P.data_nasc)) AS idade, E.estado
FROM Pessoa P JOIN Investidor I
ON P.CPF = I.CPF_inv
JOIN Endereco E ON E.CPF_habitante = P.CPF
WHERE EXTRACT(YEAR FROM P.data_nasc) < 1989);



-- #### PL ####

--2)
CREATE OR REPLACE TRIGGER aumentar_preco
BEFORE INSERT
ON Servico
FOR EACH ROW
DECLARE PRAGMA AUTONOMOUS_TRANSACTION;
v_cod_ser Servico.cod_serv%TYPE;
v_new_cost Servico.custo%TYPE;
BEGIN

SELECT S.cod_serv 
FROM Servico S
WHERE S.cod_serv = :new.cod_serv;

UPDATE Servico
SET custo = custo*1.15
WHERE cod_serv = :new.cod_serv;

COMMIT;
END;

-- TESTE:
INSERT INTO Servico(cod_serv, cod_dep, custo)
VALUES('bb123456', 'D001', 100);

--3)
DECLARE

v_CPF_cli Contrata_cli_serv.cpf_cli%TYPE;
v_cod_serv Contrata_cli_serv.cod_serv%TYPE;
V_data_prom Promocao.data_prom%TYPE;	

BEGIN

SELECT Con.CPF_cli, Con.cod_serv
INTO v_CPF_cli, v_cod_serv
FROM contrata_cli_serv Con
WHERE Con.duracao = (SELECT MIN(Con2.duracao)
	FROM contrata_cli_serv Con2);

SELECT Pro.data_prom INTO v_data_PROM
FROM Promocao Pro
WHERE Pro.cpf_cliente_prom = v_CPF_cli AND Pro.cod_serv_prom = v_cod_serv;

IF v_data_prom = NULL THEN
	INSERT INTO Promocao(desconto, cpf_cliente_prom, cod_serv_prom, 		data_prom, periodo_extensao)
	VALUES(250, v_cpf_cli, v_cod_serv, TO_DATE(12/05/16), 365);
ELSE
	UPDATE Promocao
	SET desconto = desconto + 250, periodo_extensao = 		periodo_extensao + 365
	WHERE cpf_cliente_prom = v_CPF_cli AND cod_serv_prom = v_cod_serv;
END IF;

END;


--4)
CREATE OR REPLACE TRIGGER telefone_unico
BEFORE INSERT
ON Telefone
FOR EACH ROW
DECLARE 
v_qtd NUMBER(20);
BEGIN

SELECT COUNT(*) INTO v_qtd FROM Telefone T WHERE T.CPF_pessoa = :NEW.CPF_pessoa;

IF (v_qtd > 0) THEN
	RAISE_APPLICATION_ERROR(-34624, 'erro');
END IF;
END;

-- TESTE:
INSERT INTO Telefone(CPF_pessoa, numero)
VALUES('04771475423', 32312073);



--6)
CREATE OR REPLACE PROCEDURE update_salario
(v_novo_salario Funcionario.salario%TYPE) IS
BEGIN

UPDATE Funcionario
SET salario = v_novo_salario
WHERE salario < v_novo_salario;

END;

--10)
CREATE OR REPLACE PROCEDURE aumentar_antigo
(inutil VARCHAR2(30)) IS
v_cpf_cli Contrata_serv_cli.cpf_cli%TYPE;
v_cod_serv Contrata_serv_cli.cod_serv%TYPE;
v_data_contrato Contrata_serv_cli.data_contrato%TYPE;
BEGIN

SELECT Con.cod_serv, Con.data_contrato, Con.cpf_cli
INTO v_cod_serv, v_data_contrato, v_cpf_cli
FROM Contrata_cli_serv Con
WHERE Con.data_contrato = (SELECT MIN(Con2.data_contrato) FROM Contrata_cli_serv Con2 );

UPDATE Contrata_cli_serv
SET valor = valor * 1.15
WHERE cod_serv = v_cod_serv AND data_contrato = v_data_contrato AND cpf_cli = v_cpf_cli;

END;