-- Arquivo de comandos SQL.
-- Apresentam-se os comandos DQL (sub-divisão da DML)
-- Exemplos de comandos paraa uso de funções, sub-comandos e procedimento de divisão.
-- Serão considerados as sintaxes de SQL do padrão 92.
-- Os itens da AR serão considerados sempre o da versão revisada (1992 em diante)
-- --------------------------------------------------------------------------------------------------------------------
-- 2024-09-18 foi visto o uso de funções.
-- Nesse texto são exemplificados o uso de funções tomando como referencia a tabela funcionários.
-- Apresenta-se um contexto, a expressão de AR e a seguir o comando SQL que o implementa.
-- Contexto:
--   Totalizar os valores dos campos vlsalario para os funcionário de cada departamento:
-- AR:
--   funcionarios [[ SOMA(vlsalario)->VlSal ]] { agrupado por cedepto } { ordenado por cedepto }
-- SQL:
     select cedepto, SUM(vlsalario+vlcomissao+vlbonus) from funcionarios group by cedepto order by cedepto;
-- ------------------
-- Contexto:
--   Determinar os valores máximo, mínimo, médio e total da soma das rendas dos funcionarios por departamento exibindo
--   os códigos e nomes dos departamentos e os valores correspondentes.
--   Nota: É pedido o código e nome dos departamentos teremos que fazer uma junção dos funcionarios com departamentos
--         com o conjunto montado processam-se as funções em uma operação de junção.
-- AR.:
--   (funcionarios-> [[ f.cedepto=d.cpdepto ]] departamentos->d)
--             [[ d.cpdepto, d.txnomedepto,
--                min(vlsalario+vlcomissao+vlbonus)->Min,
--                 max(vlsalario+vlcomissao+vlbonus)->Max,
--                 media(vlsalario+vlcomissao+vlbonus)->Med,
--                 soma(vlsalario+vlcomissao+vlbonus)->Total ]] {agrupado por cpdepto} {ordenado por cpdepto}
-- SQL:
     select cpdepto, d.txnomedepto,
            min(vlsalario+vlcomissao+vlbonus) as Min,
            max(vlsalario+vlcomissao+vlbonus) as Max,
            avg(vlsalario+vlcomissao+vlbonus) as Med,
            SUM(vlsalario+vlcomissao+vlbonus) as Total
            from funcionarios as f inner join departamentos as d on d.cpdepto = f.cedepto
                 group by d.cpdepto
                 order by d.cpdepto;
--   Nota: O comando acima retorna a coluna Med com diferentes quantidades de casas decimais. O PostgreSQL tem funções
--         de tratamento dos números para arredondamento de valor. Reescrevendo o comando com uso destas funções:
     select cpdepto, d.txnomedepto,
            min(vlsalario+vlcomissao+vlbonus) as Mínimo,
            max(vlsalario+vlcomissao+vlbonus) as Máximo,
            round(CAST(avg(vlsalario+vlcomissao+vlbonus) as numeric),2) as Média,
            SUM(vlsalario+vlcomissao+vlbonus) as Total,
            count(*) as QtdFunc
            from funcionarios as f inner join departamentos as d on d.cpdepto = f.cedepto
                 group by d.cpdepto
                 order by d.cpdepto;
--   Nota: O comando acima retorna as Mínimo, Máximo e Total sem casas decimais. Do mesmo modo como em Média pode-se
--         mostrar os '.00' usando a mesma função da coluna Média.
--         Teste o comando SEM o uso da Função CAST() e observe a resposta do servidor. Analise este erro.
--         Nesse comando foi incluída mais uma coluna que conta a quantidade de registros relacionados para cada
--         departamento (ou seja, expressa a quantidade de funcionarios de cada departamento)
-- ------------------
-- Contexto 
--   Procedimento de divisão
--   Quais são os nomes dos professores que tem atribuição de ministrar TODAS as disciplinas.
--   preparação - projeções de dados das tabelas professores, disciplinas e atribuidas
--   Projeção: P <- professores [[ cpprofessor -> ceprofessor ]]
--   Projeção: F <- Disciplinas [[ cpdisciplina -> cedisciplina ]]
--   Projeção: PF <- atribuidas [[ ceprofessor, dedisciplina ]]
--   divisão - divisão (propriamente) modo convencional
--   ProdCart: S <- P X F
--   Subtrac.: T <- S - PF
--   Projeção: W <- T [[ceprofessor]](distintos)
--   Subtrac.: R <- P - W
--   complemento - junção do resultado da divisão com a tabela professores
--   Junção..: K <- R [[ r.ceprofessor=professores.cpprofessor ]] professores
--   Projeção: K [[ cpprofessor, txnomeprofesso ]]
-- SQL:
--   Pra não se perder na construção dos comandos SQL SUGIRO copiar as operações de AR e embaixo de cada uma montar
--   o comando SQL correspondente. Vamos ao trabalho:
--   Projeção: P <- professores [[ cpprofessor -> ceprofessor ]]
     create temporary table P as (select cpprofessor as ceprofessor from professores);
--   Projeção: F <- Disciplinas [[ cpdisciplina -> cedisciplina ]]
     create temporary table F as (select cpdisciplina as cedisciplina from disciplinas);
--   Projeção: PF <- atribuidas [[ ceprofessor, dedisciplina ]]
     create temporary table PF as (select ceprofessor, dedisciplina from atribuidas);
--   divisão - divisão (propriamente) modo convencional
--   ProdCart: S <- P X F
     create temporary table S as (select * from P cross join F );
--   Subtrac.: T <- S - PF
     create temporary table T as (select * from S except (select * from PF) );
--   Projeção: W <- T [[ceprofessor]](distintos)
     create temporary table W as (select distinct ceprofessor from T);
--   Subtrac.: R <- P - W
     create temporary table R as (select * from P except (select * from W) );
--   complemento - junção do resultado da divisão com a tabela professores
--   Junção..: K <- R [[ r.ceprofessor=professores.cpprofessor ]] professores
     create temporary table K as (select from R inner join professores on R.ceprofessor=professores.cpprofessor );
--   Projeção: K [[ cpprofessor, txnomeprofessor ]]
     select cpprofessor, txnomeprofessor from K
-- 
--   Nota: Podemos escrever a divisão no modo 'curto' e sem a necessidade de criar as tabelas temporárias.
--         Na forma:
           select ceprofessor
                  from atribuicoes group by ceprofessor having count(cedisciplina)=(select count(*) as qtddisc from disciplinas)
                                   order by ceprofessor
--         PORÉM, na base veremos que nenhum professor ministra todas as disciplinas e a resposta será o conjunto vazio
--         Sendo assim, teremos que montar um comando INSERT para termos a resposta da divisão EXISTENTE.
--   Pesquisando quantas discplinas cada professor tem atribuida.
select ceprofessor, count(cedisciplina) from atribuicoes group by atribuicoes.ceprofessor order by atribuicoes.ceprofessor;
--   Na lista de dados vemos que o professor com código 29 tem atribuido 22 das 23 disciplinas.
-- Qual a disciplina que o professor '29' NÃO tem atribuida?
SELECT disciplinas.* FROM (select * from atribuicoes where atribuicoes.ceprofessor = 29) as atr
                          right join disciplinas on disciplinas.cpdisciplina = atr.cedisciplina
                     where cpatribuicao is null;
-- Exibe a lista de disciplinas NÃO ministradas pelo professor 29.
-- Montando o comando INSERT com o comando acima.
insert into atribuicoes 
       SELECT '91', '29', disciplinas.cpdisciplina, '6', '2024-09-25', '2024-09-25'
              FROM (select * from atribuicoes where atribuicoes.ceprofessor =29) as atr
                   right join disciplinas
                   on disciplinas.cpdisciplina = atr.cedisciplina
               where cpatribuicao is null;
-- Agora a 'divisão curta' funciona
select ceprofessor from atribuicoes group by ceprofessor having count(cedisciplina)=(select count(*) as qtddisc from disciplinas)
-- e podemos responder a pergunta com um comando, na forma:
select cpprofessor, txnomeprofessor
       from (select ceprofessor from atribuicoes group by ceprofessor having count(cedisciplina)=(select count(*) as qtddisc from disciplinas)) as prf
            inner join professores on prf.ceprofessor=professores.cpprofessor;

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