-- Guia de Comandos de AR-SQL
-- Foco: Explicitar comandos para auxílio nas fases do PraticaSQL
-- ------------------------------------------------------------------------------------------------------------------------------
-- DCL - Data Control Language
   -- grant
-- ------------------------------------------------------------------------------------------------------------------------------
   -- revoke
-- ------------------------------------------------------------------------------------------------------------------------------
-- DDL - Data Definiation Language
   -- create
-- ------------------------------------------------------------------------------------------------------------------------------
   -- alter
-- ------------------------------------------------------------------------------------------------------------------------------
   -- drop
-- ------------------------------------------------------------------------------------------------------------------------------
-- DQL - Data Query Language
   -- Seleção
   -- Definição: Gera uma lista de dados de uma tabela que Atendam a uma condição
   -- Sintaxe:
       -- Em AR: <tab> [[ Condição ]]
       -- Em SQL: select * from <tab> [where <condição>];
   -- Exemplos:
   select * from funcionarios where 1=1; -- Quais linhas de funcionarios o 1=1?
   select * from funcionarios;
   -- Notas:
      -- A estrutura do Destino é igual à estrutura da Origem
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Projeção
   -- Definição: Gera uma lista de dados de uma tabela copiando todos os dados de determinadas colunas ou campos
   -- Sintaxe:
       -- Em AR: <tab> [[ Lista De Campos ]]
       -- Em SQL: select <lista de campos> from <tab>;
   -- Exemplos:
   select idfuncionario, txprenomes, txsobrenome, vlsalario from funcionarios;
   select f.idfuncionario, f.txprenomes, f.txsobrenome, (f.vlsalario + f.vlbonus) as vltotalsal from funcionarios as f;
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Produto Cartesiano
   -- Definição: Gera uma lista de dados EMPARELHANDO cada linha de uma tabela com todas as linhas de outra tabela.
   -- Sintaxe:
       -- Em AR: <tab1> X <tab2>
       -- Em SQL: select * from <tab1> , <tab2>;  [ANSI87|ISO-IEC:9075/1987]
       --         select * from <tab1> CROSS JOIN <tab2>; [ANSI92|ISO-IEC:9075/1992]
   -- Exemplos:
      select * from funcoes; -- 'Olhando' funcoes
	  select * from funcoes, funcionarios;
	  select * from funcoes CROSS JOIN funcionarios;
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Junção
   -- Definição: Gera uma lista de dados EMPARELHANDO cada linha de uma tabela com todas as linhas de outra tabela, desde que ATEDAM A UMA CONDIÇÃO.
   -- Sintaxe:
       -- Em AR: <tab1> [[ condição ]] <tab2>
       -- Em SQL: select * from <tab1> , <tab2> where <condição>;  [ANSI87|ISO-IEC:9075/1987]
       --         select * from <tab1> INNER JOIN <tab2> ON <condição>; [ANSI92|ISO-IEC:9075/1992]
   -- Exemplos:
   select * from medicos as m inner join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao
   -- Notas:
   -- combinando comandos de junção com projeção
   select m.idmedico, m.txnomemedico, m.instituicaoid, i.idinstituicao, i.txnomeinstituicao
          from medicos as m inner join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao;
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Junções Complementares (JAD, JAE, JC,) - RIGHT, LEFT, FULL OUTER
   -- Definição: Gera uma lista de dados EMPARELHANDO cada linha de uma tabela com todas as linhas de outra tabela, 
      --         desde que ATEDAM A UMA CONDIÇÃO, porém trazendo TODOS as linhas de uma das tabela (da direita ou da
      --         esquerda em relação do operador JOIN - ou de ambas).
   -- Sintaxe:
       -- Em AR: <tab1> ][ condição ]] <tab2> -- JAE
       --        <tab1> [[ condição ][ <tab2> -- JAD
       --        <tab1> ][ condição ][ <tab2> -- JC
       -- Em SQL: select * from <tab1> LEFT  JOIN <tab2> ON <condição>;      [ANSI92|ISO-IEC:9075/1992]
       --         select * from <tab1> RIGHT JOIN <tab2> ON <condição>;      [ANSI92|ISO-IEC:9075/1992]
       --         select * from <tab1> FULL OUTER JOIN <tab2> ON <condição>; [ANSI92|ISO-IEC:9075/1992]
   -- Exemplos:
   -- Escrevendo o comando de JAE (medicos deve estar completa)
   select m.idmedico, m.txnomemedico, m.instituicaoid, i.idinstituicao, i.txnomeinstituicao
          from medicos as m LEFT join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao;
   -- Agora escrevendo o comando de JAD (instituicoesdeensino deve estar completa)
   select m.idmedico, m.txnomemedico, m.instituicaoid, i.idinstituicao, i.txnomeinstituicao
          from medicos as m RIGHT join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao;
   -- Escrevendo o comando de JC (Ambas as tabelas devem aparecer completamente)
   select m.idmedico, m.txnomemedico, m.instituicaoid, i.idinstituicao, i.txnomeinstituicao
          from medicos as m FULL OUTER join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao;
   -- Notas:
   -- Combinando a JAE com uma seleção para trazer os nomes dos médicos sem instituição de ensino informada
   select m.idmedico, m.txnomemedico, m.instituicaoid, i.idinstituicao, i.txnomeinstituicao
          from medicos as m LEFT join instituicoesdeensino as i ON m.instituicaoid = i.idinstituicao
          WHERE i.idinstituicao IS NULL;
      -- Estes comandos SQL são válidos apenas para os padrões apresentados após 1992 (inclusive)
-- ------------------------------------------------------------------------------------------------------------------------------
   -- União
   -- Definição: Gera uma tabela com as linhas de duas outras tabelas, copiando no DESTINO todas as linhas das duas tabelas da ORIGEM
   -- Sintaxe:
       -- Em AR: A = <tab1> U <tab2>
       -- Em SQL: select * from <tab1> UNION ( select * from <tab2> ) ; 
   -- Exemplos:
   -- Para fazer a UNIÃO temos que GERAR Tabelas União Compatível (tem o mesmo esquema=Tiposiguais, mesmo qtd de campos e mesmo domínio de valor)
   -- Vamos Gerar 2 Tabelas UC partindo de da tabela AUTORES
   create temporary table a1 as (select * from autores where autores.logradouroid >= 460);
   create temporary table a2 as (select * from autores where autores.logradouroid <= 490);
   -- Consultando a1 e a2
   select * from a1;
   select * from a2;
   -- Escrevendo o comando de UNIÃO
   select * from a1 UNION (select * from a2);
   -- Notas
   -- O operador UNION OMITE a repetição de linhas à partir do padrão ANSI:92
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Subtração
   -- Definição: GEra no DESTINO todas as linhas da primeira tabela que NÃO ESTÃO na segunda tabelas.
   -- Sintaxe: A - B
   --          select <tab1> EXCEPT (select * from <tab2>);
   -- Exemplo:
   select * from a1 EXCEPT (select * from a2);
   select * from a2 EXCEPT (select * from a1);
   -- Notas
   -- O operador EXCEPT não é comutativo.
-- ------------------------------------------------------------------------------------------------------------------------------
   -- Intersecção
   -- Definição: Gera uma tabela no DESTINO com as linhas em comum de duas tabelas
   -- Sintaxe: A ^ B
   -- Exemplo:
   SELECT * FROM a1 INTERSECT (SELECT * FROM a2);
   SELECT * FROM a2 INTERSECT (SELECT * FROM a1);
   -- Nota
   -- Na apresentação do MR foram apresentadas as seguintes Operações de AR:
   --    Seleção, Projeção, Produto Cartesiano, Junção (Fechada), União e a Diferença (Subtração)
   --    Para Fazer uma Intersecção pode-se duas subtrações, na forma: A - (A - B) ou B - (B - A)
   -- A Intersecção é comuntativa
-- ------------------------------------------------------------------------------------------------------------------------------
-- DTL - Data Transaction Language
   -- Notas Gerais: -- NÃO existe representação em Álgebra Relacional
                    -- Atualizam dados em UMA tabela.
   -- insert
      -- Definição: Permite gerar valores em campos das linhas de UMA tabela
      -- Sintaxe: INSERT INTO <tab> [(Lista de campos que recebe valor)]
      --                 VALUES (LISTA de Valores Associados aos campos - Citados ou definidos na TAB)[,
      --                        (Lista 1 de Valores dos campos),..,(Listan de Valores dos campos)];
      -- Exemplos:
      insert into autores (idautor,txnomeautor,logradouroid,txcomplemento,nucep,dtnascimento,dtcadautor)
             values ('20','Albuquerque Arantes','45','123 - Casa 2','19745856','1998-05-23','2026-03-16');
      -- Notas: -- Nesta tabela já existe uma Chave Primária com valor '20'. Portanto:
                -- ERROR:  duplicar valor da chave viola a restrição de unicidade "cpautor"
                -- Chave (idautor)=(20) já existe.
                -- Alterando o comando para:
      insert into autores (idautor,txnomeautor,logradouroid,txcomplemento,nucep,dtnascimento,dtcadautor)
             values ('30','Albuquerque Arantes','45','123 - Casa 2','19745856','1998-05-23','2026-03-16');
                -- A Chave Estrangeira ce1autor aponta para logradouros... NÃO existe o valor 45 na tabela
                -- logradouros... PORÉM a Chave Estrangeira NÃO foi definida.
-- ------------------------------------------------------------------------------------------------------------------------------
   -- update
      -- Definição: ALTERA Valores de Campos de Linhas de UMA Tabela
      -- Sintaxe: UPDATE <tabela> SET <atribuições> [WHERE <condição>];
      -- Exemplos:
      UPDATE autores SET logradouroid=10, dtcadautor=(current_date-5) WHERE idautor=30;
      -- Notas:
-- ------------------------------------------------------------------------------------------------------------------------------
   -- delete
      -- Definição: Exclui ou remove uma linha de UMA tabela
      -- Sintaxe: DELETE FROM <tab> [WHERE <condição>];
      -- Exemplos:
      DELETE from autores where idautor=30;
      -- Notas:
-- ------------------------------------------------------------------------------------------------------------------------------
-- Executando os comandos de ordenação e agrupamento para processamento de funções.
   -- Ordenação
      -- Definindo
         -- Submete as linhas de uma tabela (ou conjunto de tabelas) à ordenação segundo uma ou mais chaves.
      -- Sintaxe [AR]:
         -- <tabela> {ordenado por <chave1>[,<chave2>,...,<chaven>]};
      -- Exemplo [AR]
         -- funcionarios {ordenado por deptoid}
      -- Sintaxe [SQL]
         -- select * from <tabela> ORDER BY <chave1>[, <chave2>,..., <chaven>];
      -- Exemplo [SQL]
      select * from funcionarios; -- Exibindo TODAS as linhas da tabela 'como foram inseridas'.
      select * from funcionarios order by deptoid; -- Exibindo as linhas 'classificadas' por deptoid
      -- Nota:
         -- O 'order by' deve ser escrito APÓS o comando (ou sub-comando) GROUP BY.
   -- Agrupamento
      -- Definindo
         -- Submete as linhas de uma tabela (ou conjunto de tabelas) a um agrupamento SEGUNDO um ou mais campos.
            -- Nota:
               -- Alguns SGBDs exigem uma ordenação dos dados previamente executadaq pelas mesmas chaves.
               -- O agrupamento significa escrever uma linha no lugar de duas ou mais linhas... MAS
               -- vai implicar em processar alguma função em algum campo que possa ser resumido
      -- Sintaxe [AR]:
         -- <tabela> {agrupando por <chave1>[,<chave2>,...,<chaven>]};
      -- Exemplo [AR]
         -- funcionarios {agrupando por deptoid}
      -- Sintaxe [SQL]
         -- select * from <tabela> GROUP BY <chave1>[, <chave2>,..., <chaven>];
         -- nota:
            -- O 'GROUP BY' DEVE ser escrito 'APÓS' o WHERE E ANTES do ORDER BY
            -- As funções 'básicas' foram apresentadas na ANSI:87 e são:
            -- CONTAGEM    - COUNT()
            -- MÁXIMO      - MAX()
            -- MÍNIMO      - MIN()
            -- TOTALIZAÇÃO - SUM()
            -- MÉDIA       - AVG()
      -- Exemplo [SQL]
select COUNT(idfuncionario) from funcionarios GROUP BY deptoid; -- Exibe a lista com a 'contagem' de funcionarios de cada departamento
select deptoid, COUNT(idfuncionario) as TOTFunc from funcionarios GROUP BY deptoid order by deptoid; -- Exibe a contagem de funcionarios de cada departamento com a lista 'classificada' por departamento.
-- A seguir determina-se a contagem de funcionarios e o total de salário de cada departamento.
select deptoid, COUNT(idfuncionario) as TOTFunc, SUM(vlsalario) as totsal 
       from funcionarios inner join departamentos on funcionarios.deptoid=departamentos.iddepto
	      GROUP BY deptoid
	      order by deptoid;
-- no comando acima pode ser interessante exibir o nome do departamento exibido apenas com seu código.
-- MAS para isso o comando terá que realizar o processamento sobre uma junção de funcionaros e departamentos
-- Na forma:
select f.deptoid as DeptoID, d.txnomedepto) as NomeDepto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      GROUP BY deptoid, d.txnomedepto
       order by deptoid, d.txnomedepto
-- Note a distancia dos dados nas colunas DeptoID e NomeDepto. Pode ser interessante combinar os valores destes campos
-- usando uma outra função (aparece como '||' em 92 e 99 mas em 2003 surge como função Padrão da SQL.
-- Usando o CONCAT()
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
	      SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      GROUP BY deptoid, d.txnomedepto
       order by deptoid, d.txnomedepto
-- Ainda sobre este conjunto pode ser de interesse processar uma seleção 'olhando' os funcionarios que nasceram ANTES de 1979-01-01    
-- na forma:
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal,
       AVG((vlsalario+vlbonus+vlcomissao)) as MEDSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      WHERE dtnascimento<'1979-01-01' -- trocando o sinal < por >= tomamos os dados de funcionarios 'novos'
	      GROUP BY deptoid, d.txnomedepto
       order by SUM((vlsalario+vlbonus+vlcomissao)) );
-- A Coluna MEDSal exibe os valores de média salarial com várias casas decimais.
-- Para 'arredondar' valores pode-se usar a função ROUND() em muitos SGBDs.
-- Teste o comando:
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal,
   	   ROUND(AVG((vlsalario+vlbonus+vlcomissao)),2) as MEDSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      WHERE dtnascimento<'1979-01-01'
	      GROUP BY deptoid, d.txnomedepto
       order by SUM((vlsalario+vlbonus+vlcomissao)) );
-- Este comando resulta em erro, pois a ROUND() não reconhece o tipo de dado que retorna de AVG(). Pode-se mudar o tipo de dado
-- do retorno da função AVG() com a função CAST(), desse modo a ROUND vai funcionar como esperado.
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal,
   	   ROUND(CAST(AVG((vlsalario+vlbonus+vlcomissao)) as NUMERIC),2) as MEDSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      WHERE dtnascimento<'1979-01-01'
	      GROUP BY deptoid, d.txnomedepto
       order by SUM((vlsalario+vlbonus+vlcomissao)) );
-- Ainda pode ser interessante 'criar' tabelas temporárias com os dados de funcionários nascidos antes de 1979-01-01 e os nascidos
-- após esta data. Pode-se criar esta tabela com o comando:
create temporary table salfuncexpert as (
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal,
   	   ROUND(CAST(AVG((vlsalario+vlbonus+vlcomissao)) as NUMERIC),2) as MEDSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      WHERE dtnascimento<'1979-01-01'
	      GROUP BY deptoid, d.txnomedepto
       order by SUM((vlsalario+vlbonus+vlcomissao)) );
-- Uma outra tabela com os funcionarios nascidos APÓS 1979-01-01 pode ser determinada com o comando.
create temporary table salfuncnovos as (
select CONCAT(f.deptoid,'-',d.txnomedepto) as Depto,
       COUNT(f.idfuncionario) as TOTFunc,
   	   SUM((vlsalario+vlbonus+vlcomissao)) as totsal,
	      MAX((vlsalario+vlbonus+vlcomissao)) as MAXSal,
	      MIN((vlsalario+vlbonus+vlcomissao)) as MINSal,
   	   ROUND(CAST(AVG((vlsalario+vlbonus+vlcomissao)) as NUMERIC),2) as MEDSal
       from funcionarios as f inner join departamentos as d on f.deptoid=d.iddepto
	      WHERE dtnascimento>='1979-01-01'
	      GROUP BY deptoid, d.txnomedepto
       order by SUM((vlsalario+vlbonus+vlcomissao)) );
-- Consultando as novas tabelas...
select * from salfuncnovos;
select * from salfuncexpert;