-- Guia de comandos SQL sobre a base de dados PRATICASQL
-- 2024-03-07
-- consultando as linhas de atividades
-- Seleção de dados (lendo toda a tabela atividades)
SELECT * FROM atividades;

-- Executando um INSERT na tabela atividades
INSERT into atividades (idatividade,txnomeatividade,txdescricaoativ,dtcadatividade)
       values ('8',
               'teste dos porgramas aplicativos',
               'Teste de efetividade de função feita pelo analista ou programados do aplicativo',
               '2024-03-07');
-- Executando a DELEÇÃO das linhas com chave primária 7 ou 8
DELETE FROM atividades WHERE idatividade IN ('7','8');
-- Alterando o valor do campo de nome da atividade onde o valor da chave priméria vale 6.
UPDATE atividades SET txnomeatividade='Teste dos Programas Aplicativos pelo Analista', 
                      txdescricaoativ='Teste de efetividade funcional feito pelo Analista responsável pelo PA.'
                  WHERE idatividade='6';
-- ========================================================================================================================================
-- 2024-03-14 - Esta aula não aconteceu, devido a realização da Colação de Grau da turma de 2023.2
-- Mesmo assim, continuaremos a cumprir nosso calendário no nosso próximo encontro
-- Seleção
-- Sintaxe AR: <tab>[condição]
-- Exemplo AR: atividades [cpatividade<25 e dtcadatividade<='2010-12-31']
-- ------------------------------------------------------------------
-- Sintaxe SQL: select * from <tab> [where <condição>];
-- Exemplo SQL: select * from atividades where cpatividade<25;
-- Implementando:
select * from atividades where cpatividade<25 and dtcadatividade<='2010-12-31'
-- Nota:
-- O SGBD PostgreSQL possui funções próprias (e não padronizadas) que manipulam de modo otimizado determinados
-- tipos de dados. Notadatamente os tipos de dados 'temporais'.
select * from funcionarios where cefuncao='10' or cefuncao='7';
-- ========================================================================================================================================
-- Projeção
-- Sintaxe AR: <tab>[lista de campos]
-- Exemplo AR: funcionarios [cpfuncionario, txprenomes, txsobrenome]
-- ------------------------------------------------------------------
-- Sintaxe SQL: select <lista de campos - separados por ','> from <tab>;
-- Exemplo SQL: select cpfuncionario, txprenomes, txsobrenome from funcionarios;
-- Implementando:
select f.cpfuncionario, CONCAT(f.txprenomes,' ',f.txsobrenome) as NomeCompleto
       from funcionarios as f;
-- ========================================================================================================================================
-- Produto Cartesiano
-- Sintaxe AR: <tab1> X <tab2>
-- Exemplo AR: cores X funcoes
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:87 select * from <tab1>, <tab2>;
--              ANSI:92 - Operador JOIN com complementos: CROSS
--                      select * from <tab1> CROSS JOIN <tab2>;
-- Exemplo SQL: ANSI:87 select * from cores, funcoes;
--              ANSI:92 select * from cores cross join funcoes;
-- Implementando:
select * from cores, funcoes;
select * from cores cross join funcoes;
-- ========================================================================================================================================
-- Junção Natural (Fechada)
-- Sintaxe AR: 87 <tab1> [condição do emparelhamento] <tab2>
--             92 <tab1> [[condição do emparelhamento]] <tab2>
-- Exemplo AR: 87 funcionarios [funcaoid=idfuncao] funcoes
--         ou     funcionarios [funcionarios.funcaoid=funcoes.idfuncao] funcoes
--         ou     funcionarios->f [f.funcaoid=fc.idfuncao] funcoes->fc
--             92 funcionarios -> f [[f.cefuncao=fc.cpfuncao]] funcoes -> fc
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:87 select * from <tab1>, <tab2> where <condição>
--              ANSI:92 - Operador JOIN com complementos: INNER
--                      select * from <tab1> inner join <tab2> ON <condição>;
-- Exemplo SQL: ANSI:87 select * from funcionarios as f, funcoes as fc where funcaoid=idfuncao;
--              ANSI:92 select * from funcionarios as f inner join funcoes as fc ON f.funcaoid=fc.idfuncao;
-- Implementando:
-- Em SQL:87
select * from funcionarios as f, funcoes as fc where f.funcaoid=fc.idfuncao;
select f.cpfuncionario, CONCAT(f.txprenomes,' ',f.txsobrenome) as NomeCompleto, fc.cpfuncao, fc.txnomefuncao
       from funcionarios as f, funcoes as fc
       where f.cefuncao=fc.cpfuncao;
-- Em SQL:92
select * from funcionarios as f inner join funcoes as fc on f.cefuncao=fc.cpfuncao;
-- Usando a Projeção de uma Junção (com a sintaxe de 92)
select f.cpfuncionario, CONCAT(f.txprenomes,' ',f.txsobrenome) as NomeCompleto, fc.cpfuncao, fc.txnomefuncao
       from funcionarios as f inner join funcoes as fc on f.cefuncao=fc.cpfuncao;
-- 2024-03-21 - - Anotação de comandos na sala
select * from funcionarios as f, funcoes as fc where f.funcaoid=fc.idfuncao;
select f.idfuncionario,f.txprenomes, f.txsobrenome, f.funcaoid, fc.idfuncao, fc.txnomefuncao
       from funcionarios as f, funcoes as fc
       where f.funcaoid=fc.idfuncao;
-- Em SQL:92 teremos:
select f.idfuncionario,f.txprenomes, f.txsobrenome, f.funcaoid, fc.idfuncao, fc.txnomefuncao
       from funcionarios as f INNER JOIN funcoes as fc
       ON f.funcaoid=fc.idfuncao;
-- usando sub-queries
select *       
       from (select idfuncionario,
                    txprenomes,
                    txsobrenome,
                    funcaoid from funcionarios) as f
       INNER JOIN (select idfuncao,
                          txnomefuncao from funcoes) as fc
       ON f.funcaoid=fc.idfuncao;
-- ========================================================================================================================================
-- Junção Aberta Pela Direita
-- Sintaxe AR: 87 - Não Existe
--             92 <tab1> [[Condição][<tab2>
-- Exemplo AR: 87 - Não Existe
--             92 funcionarios -> f [[ f.funcaoid=fc.idfuncao ][ funcoes->fc
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:92 - Operador JOIN com complementos: RIGHT
--                     select * from <tab1> right join <tab2> on <condição>;
-- Exemplo SQL: ANSI:92
-- Implementando:
select * from funcionarios as f
         right join funcoes as fc
         on f.funcaoid=fc.idfuncao;
-- usando sub-query
select * from (select idfuncionario,
                      txprenomes,
                      txsobrenome,
                      funcaoid from funcionarios) as f
              right join (select idfuncao,
                                 txnomefuncao from funcoes) as fc
              on f.funcaoid=fc.idfuncao;
-- ========================================================================================================================================
-- Junção Aberta Pela Esquerda
-- Sintaxe AR: 87 - Não Existe
--             92   <tab1> [[Condição da junção][ <tab2>
-- Exemplo AR: 87 - Não Existe
--             92   funcoes [[idfuncao=funcaoid ][ funcionarios
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:92 - Operador JOIN com complementos: LEFT
--                        select * from <tab1> LEFT JOIN <tab2> ON <condição>;
-- Exemplo SQL: ANSI:92
-- Implementando:
select * from funcoes as fc left join funcionarios as f on fc.idfuncao=f.funcaoid;
select * from (select idfuncionario,
                      txprenomes,
                      txsobrenome,
                      funcaoid from funcionarios) as f
              right join (select idfuncao,
                                 txnomefuncao from funcoes) as fc
              on f.funcaoid=fc.idfuncao;
-- ========================================================================================================================================
-- Junção Completa (Aberta Pelos 2 Lados)
-- Sintaxe AR: 87 - Não Existe
--             92 <tab1>][<tab1>.<campo>=<tab2.<campo>][<tab2>
-- Exemplo AR: 87 - Não Existe
--             92 consultas->c ][f.cpfuncionario=c.cefuncionario][ funcionarios->f
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:92 - Operador JOIN com complementos: FULL OUTER JOIN
-- Exemplo SQL: ANSI:92  select * from <tab1> full outer join <tab2> ON <condição da junção>;
-- Implementando:
select * from consultas as c full outer join funcionarios as f on f.idfuncionario = c.funcionarioid ;
-- Usando sub-queries:
select * from (select cpconsulta, funcionarioid, dthoraconsulta from consultas) as c
         full outer join
              (select idfuncionario, txprenomes, txsobrenome, dtnascimento from funcionarios) as f
         on f.idfuncionario = c.funcionarioid ;
-- Como todos os registros em 'consultas' tem o código do funcionario esta operação surte o mesmo resultado que o 'LEFT JOIN'.
-- Para termos o "vazio' em ambos os lados do JOIN temos que 'anular' alguns códigos de funcionario.
-- Para alterar dados em 'consultas' e poder ver o resultado da junção completa escrevemos os comandos dentro de uma 'transação'.
-- Iniciando a transação:
BEGIN TRANSACTION;
update consultas set funcionarioid = NULL where funcionarioid>120;

-- Trabalhando o conceito de Sub-Queries
-- Escrevendo o comando de junção aberta pela ESQUERDA
select * from (select cpconsulta, funcionarioid, dthoraconsulta from consultas) as c
         left join
              (select idfuncionario, txprenomes, txsobrenome, dtnascimento from funcionarios) as f
         on f.idfuncionario = c.funcionarioid ;
-- Escrevendo o comando de junção aberta pela DIREITA
select * from (select cpconsulta, funcionarioid, dthoraconsulta from consultas) as c
         right join
              (select idfuncionario, txprenomes, txsobrenome, dtnascimento from funcionarios) as f
         on f.idfuncionario = c.funcionarioid ;
-- Escrevendo o comando de junção COMPLETA (Aberta pelos lados - aberta direita-esquerda)
select * from (select cpconsulta, funcionarioid, dthoraconsulta from consultas) as c
         full outer join
              (select idfuncionario, txprenomes, txsobrenome, dtnascimento from funcionarios) as f
         on f.idfuncionario = c.funcionarioid ;
-- Cancelando a transação (a situação em consultas' volta para antes do comando update).
rollback;
-- ========================================================================================================================================
-- Para seguir no estudo de SQL faltam as operações UNIÃO, SUBTRAÇÃO e INTERSECÇÃO
-- Para estas operações devemos "criar" tabelas UC (União Compatíveis).
-- "Montando" as tabelas UC partindo de funcionarios.
create temporary table f1 as (select * from funcionarios where funcaoid>=9 order by funcaoid);
create temporary table f2 as (select * from funcionarios where funcaoid between 4 and 9 order by funcaoid);
-- Podemos, agora escrever:
-- ========================================================================================================================================
-- UNIÃO
-- Sintaxe AR: <tab1> U <tab2>
-- Exemplo AR: F1 U F2
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:87 <tab1> UNION <tab2> -- Traz TODAS as linhas (inclusive repetidas)
-- Exemplo SQL: ANSI:92 <tab1> UNION <tab2> -- Traz linhas SEM REPETIÇÃO (se quiser ver a repetição usa-se UNION ALL
-- Implementando:
select * from f1 UNION (select * from f2);
-- e se for necessário ver as linhas repetidas, deve-se escrever
select * from f1 UNION ALL (select * from f2);
-- ========================================================================================================================================
-- Intersecção
-- Sintaxe AR: <tab1> ^ <tab2> -- Em 1970 o sinal para a INTERSECÇÃO era o "U" invertido (como na teoria dos conjuntos).
--                             -- MAS para facilitar a escrita em arquivos .TXT passou-se a usar o "^" para simbolizar
--                             -- a operação de INTERSECÇÃO
-- Exemplo AR: f1 ^ f2
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:87 select * from <tab1> INTERSECT (select * from <tab2>);
-- Exemplo SQL: select * from f1 intersect (select * from f2);
-- Implementando:
select * from f1 INTERSECT (select * from f2);
-- ========================================================================================================================================
-- SUBTRAÇÃO
-- Sintaxe AR: <tab1> - <tab2>
-- Exemplo AR: f1 - f2
-- ------------------------------------------------------------------
-- Sintaxe SQL: ANSI:87 - select * from <tab1> WHERE EXISTS (select * from f2 where <tab1>.campo1=<tab2>.campo1 and 
--                                                                                  <tab1>.campo2=<tab2>.campo2 and ...
--                                                                                  <tab1>.campon=<tab2>.campon)
--              ANSI:92 - select * from <tab1> EXCEPT | MINUS (select * from f2);
-- Exemplo SQL: ANSI:92  select * from f1 except (select * from f2);
-- Alguns SGBDs aceitam os dois operadores. TESTE os dois no SGBD que você está trabalhando.
-- Implementando:
select * from f1 EXCEPT (select * from f2);
-- ------------------------------------------------------------------
-- Depois de executar os comandos de União, subtração e Intersecção é de boa pratica elimminar as tabelas temporárias com o comando:
drop table f1, f2;
-- Note: Alguns SGBDs exigem um comando DROP para cada objeto removido de uma base.
--       O PostgreSQL permite isso (fora do padrão) MAS desde que os objetos sejam do mesmo tipo - tabelas, como no exemplo.
-- ========================================================================================================================================
-- Segue-se o estudo sobre exemplo das fase 2, 3 e 4 do PraticaSQL.
-- Fase 2
-- Fase 3
-- Fase 4

-- Deixei este comando para gerar uma análise e reflexão.
-- Faça seu comentário.
insert into consultas 
       SELECT ROW_NUMBER() OVER(ORDER BY cpconsulta)+25 as cpconsulta,
              c.funcionarioid,
              5,
              c.dthoraconsulta,
              c.dthorarealizada,
              c.planodesaudeid,
              c.aosituacaoconsulta,
              c.dtcadconsulta
              FROM public.consultas as c where medicoid=1;
select * from consultas;
