-- Resolvendo Perguntas sobre processamento de dados na Base de Dados do PRATICASQL
-- Apresentaremos a pergunta e a AR que responde a pergunta, a seguir a tradução de cada AR para SQL
-- Pergunta: Quais são os nomes completos, data de contratação e data de nascimento dos funcionarios que partiparam em todos os projetos que foram ativados em junho de 2015?

-- Seleção.: A <- projetos [[ MES(dtcadpjeto)=6 e ANO(dtcadprojeto)=2015 ]]
-- Junção..: B <- A [[ A.cpprojeto=tp.ceprojeto ]] tarefasprojetos -> tp
-- Junção..: D <- B [[ B.cptarefaprojeto=at.cetarefaprojeto ]] atuacoes -> at
-- Junção..: E <- D [[ D.cefuncionario=f.cpfuncionario ]] funcionarios -> f
-- Projeção: E [[txprenomes, txsobrenome, dtcontratacao, dtnascimento]]

-- ANTES de processar esta pergunta, percebe-se que a tabela projetos não contém linhas que atendam à seleção inicial.
-- ENTÃO, para ajustar os dados (e ter uma resposta 'visível' temos que alterar os dados, com o comando:
update projetos set dtcadprojeto='2015-06-15' where dtcadprojeto='1980-01-01';
-- desta forma teremos dados para 'ver' uma resposta.
-- Agora sim. Traduzindo para o SQL...
-- Este arquivo foi alterado em 23/09 --
-- O PostgreSQL dispõe de funções de processamento de dados de tempo bem interessantes.
-- note o comando:
select * from projetos where EXTRACT(YEAR from dtcadprojeto)=2015 and EXTRACT(MONTH from dtcadprojeto)=6;
-- A função EXTRACT() retira segmentos de interesse do campo em foco. No comando acima retiramos o ano e mês facilitando o entendimento do comando.
-- ---- CONSTRUINDO A RESPOSTA (TRADUÇÃO PARA SQL) DAS OPERAÇÕES DE AR.
-- Seleção.: A <- projetos [[ MES(dtcadpjeto)=6 e ANO(dtcadprojeto)=2015 ]]
create temporary table A as (select * from projetos where dtcadprojeto between '2015-06-01' and '2015-06-30');
-- AQUI de modo alternativo podemos escrever:
   create temporary table A as ( select * from projetos where EXTRACT(YEAR from dtcadprojeto)=2015 and EXTRACT(MONTH from dtcadprojeto)=6 );
-- Junção..: B <- A [[ A.cpprojeto=tp.ceprojeto ]] tarefasprojetos -> tp
create temporary table B as (select * from A inner join tarefasprojetos as tp on A.cpprojeto=tp.ceprojeto);
-- projeção: C <- B [[ cptarefaprojeto ]] -- Tabela criada para eliminar redundancias de campo (dtinicio);
create temporary table C as (select cptarefaprojeto from B);
-- Junção..: D <- C [[ C.cptarefaprojeto=at.cetarefaprojeto ]] atuacoes -> at
create temporary table D as (select * from C inner join atuacoes as at on C.cptarefaprojeto=at.cetarefaprojeto);
-- Junção..: E <- D [[ D.cefuncionario=f.cpfuncionario ]] funcionarios -> f
create temporary table E as (select * from D inner join funcionarios as f on D.cefuncionario=f.cpfuncionario);
-- Projeção: E [[ txprenomes, txsobrenome, dtcontratacao, dtnascimento ]]
select txprenomes, txsobrenome, dtcontratacao, dtnascimento from E;
-- Outra observação: O conjunto de dados pode apresentar linhas em repetição por conta do mesmo funcionario atuar repetidas vezes em projetos ativados em 6/2015
-- Em SQL é possível fazer a projeção final sem apresentar as linhas repetidas, na forma:
select distinct txprenomes, txsobrenome, dtcontratacao, dtnascimento from E
-- Considerações: Como a Base de Dados em estudo é pequena a performance do comando acima não é questionada,
-- entretanto, em volumes de dados elevados o custo do operador distinct deve ser condirado e 
-- pode ser melhorado trabalhando com ordenação ou até mesmo com a criação de índices associados aos dados processados.
drop table a,b,c,d,e;

