---------------------------------------FICHA 8----------------------------------------------
CREATE OR REPLACE VIEW v_total_doutorados
AS
select p.ID_INST as intituicao, count(*) as total_doutorados
from pessoa p join docente d ON (p.id = d.id)
where d.grau = 'D'
GROUP BY p.ID_INST;
---------------------------------------------------

GRANT SELECT ON v_total_doutorados TO presidente;

--------------------------------------------------
CREATE OR REPLACE VIEW v_total_alunos_benef
AS
Select p.id_inst as instituicao, count(*) as total_alunos_benef
from pessoa p join aluno a on (p.id = a.id)
where a.TEM_BENEFICIOS = 'S'
group by p.ID_INST;

--------------------------------------------------

GRANT SELECT ON v_total_alunos_benef TO presidente;

--------------------------------------------------

select p.prim_nome as nome_pessoa, i.nome as nome_instituicao, nvl(t.num_telefone, '<sem contacto>') as num_telefone
from pessoa p join instituicao_ensino ie  ON (p.ID_INST = ie.id)
              join instituicao i ON (ie.id = i.id)
              join telefone t ON (p.id = t.id_pessoa);

-----------------------------------------------------

CREATE OR REPLACE FUNCTION func_nome_completo (pNome VARCHAR2 ,pId pessoa.id%type)
RETURN VARCHAR2
IS
  primeiroNome pessoa.prim_nome%type;
  ultimoNome PESSOA.ULT_NOME%type;
BEGIN
  SELECT PRIM_NOME, ULT_NOME
  INTO primeiroNome, ultimoNome
  FROM pessoa
  WHERE id=pId;

  if(pNome = 'pnome') THEN
    return primeiroNome || ' ' || ultimoNome;
  elsif (pNome = 'unome') THEN
    return ultimoNome || ', ' || primeiroNome;
  else
    return null;
  end if;
END func_nome_completo;
/

select func_nome_completo('pnome',3)
from pessoa;


----------------FICHA PARTE II--------------
--R9
CREATE SEQUENCE seq_pessoa_pk START WITH 1 INCREMENT BY 5 MAXVALUE 999999;

DROP SEQUENCE seq_pessoa_pk;

CREATE OR REPLACE TRIGGER tbi_pessoa
BEFORE INSERT ON pessoa
FOR EACH ROW
BEGIN
  :NEW.id := seq_pessoa_pk.NEXTVAL;
END;
/


INSERT INTO pessoa(prim_nome,ult_nome,genero,nif,id_inst)
  VALUES(Jarbas,Beterraba,M,123456789,1);