-- Arquivo de comandos SQL.
-- Apresentam-se os comandos DQL (sub-divisão da DML)
-- Serão considerados as sintaxes de SQL dos padrões 87 e 92.
-- Os itens da AR serão considerados sempre o da versão revisada (1992 em diante)
-- --------------------------------------------------------------------------------------------------------------------
-- 2024-09-05 foi visto:
-- comandos de DQL (ou DML de Consulta)
-- Seleção
-- Definição..: Determina um conjunto de linhas de uma tabela que atenda a uma condição.
-- Sintaxe AR.: <tab> [[condição]]
-- Exemplo AR.: areasdeestudo [[dtcadareaestudo>='2010-01-01']]
-- Sintaxe SQL: select * from <tab> [where <condição>];
-- Exemplo SQL: 
select * from areasdeestudo;
select * from areasdeestudo where dtcadareaestudo>='2010-01-01';
-- Pesquise o uso da função EXTRACT() para 'tirar' o ano da do campo dtcadareaestudo.
-- --------------------------------------------------------------------------------------------------------------------
-- Projeção
-- Definição..: Determina um conjunto de linhas de uma tabela copiando todas as linhas de colunas especificadas.
-- Sintaxe AR.: <tab>[-><novonome>] [[lista de campos]]
-- Nota.......: O operador [-><novonome>] pode ser usado em qualquer operação de AR para trocar
--              o nome de uma tabela ou campo.
-- Exemplo AR.: funcionarios->f [[f.idfuncionario, f.txprenomes, f.txsobrenome]]
-- Sintaxe SQL: select <lista de campos> from <tab>;
-- Exemplo SQL: 
select * from funcionarios limit 5;
-- Nota.......: O sub-comando (ou cláusula) limit determina a quantidade de linhas que serão exibidas.
--              Embora seja padrão alguns SGBDs não seguem a mesma especificação. TESTE no seu SGBD
select f.idfuncionario, f.txprenomes, f.txsobrenome  from funcionarios as f limit 5;
-- Nota.......: O 'as' serviu para trocar 'dinamicamente' o nome da tabela.
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Natural (Fechada)
-- Definição..: Determina um emparelhamento de linhas de duas tabelas obedecendo a uma condição.
--              Na maior parte das situações o emparelhamento se dá comparando valores de uma CP com valores de uma CE.
-- Sintaxe AR.: <tab1>[->t2][[condição]]<tab2>[->t2]
-- Exemplo AR.:  funcionarios->f[[f.cedepto = d.cpdepto]]departamentos->d
-- Nota.......: Se os operadores de troca de nome forem usados a condição deverá usar os novos Nomes de tab1 e tab2.
-- Sintaxe SQL: select * from <tab1>, <tab2> where <condição>; [ANSI:86]
--              select * from <tab1> INNER JOIN <tb2> ON <condição>; [ANSI:92]
-- Nota.......: Na junção a condição quase sempre é feita entre CP e CE.
-- Exemplo SQL: 
select * from funcionarios, departamentos where funcionarios.cedepto = departamentos.cpdepto;
-- Nota.......: O comando acima pode ficar mais simples (e ser escrito com menos chance de erros), na forma:
select * from funcionarios as f, departamentos as d where f.cedepto = d.cpdepto;
--              Usando a sintaxde ANSI:92
select * from funcionarios as f inner join departamentos as d on f.cedepto = d.cpdepto;
-- --------------------------------------------------------------------------------------------------------------------
-- Na aula de 2024-09-11 foi visto:
-- Produto Cartesiano
-- Definição..: Emparelhamento de Linhas de DUAS tabelas (Quaisquer, sem a necessidade de comparação)
-- Nota.......: Acaba gerando um 'combinação' de todas as possibilidades de arranjo de dados.
-- AlgebraRel.: <tab1>[->t2] X <tab2>[->t2]
-- Sintaxe SQL: [ANSI:86] select * from <tab1>, <tab2>;
-- Exemplo SQL:
select * from cores, cursos;
-- Sintaxe SQL: [ANSI:92] select * from <tab1> CROSS JOIN <tab2>;
select * from cores CROSS JOIN cursos;
-- Nota.......: Até aqui não se criou nenhuma tabela com as operações da AR.
--              A Parte da Operação de AR que sinaliza a criaçãol de uma tabela é o simbolo '<-'.
--              Na forma:
--              a <- <tab> [[condição]]
--              Em SQL o comando pode ser escrito assim:
--              create table <tab> AS (Comando SQL que 'traduz' a operação AR);
--              NESTA forma a <tab> é criada 'fisicamente' na base de dados.
--              Assim, se dois ou mais usuários 'executarem' este comando somente
--              o primeiro terá sucesso.
--              Em SQL é possível criar uma tabela na memória RAM da 'sessão' do sgbd
--              Para isso o SQL dispõe do comando 'temporary'. Na forma:
--              create TEMPORARY table <tab> AS (Comando SQL que 'traduz' a operação AR);
-- Exemplo SQL:
create temporary table mescla as (select * from cores CROSS JOIN cursos);
-- 'Dropando' - removendo ou EXCLUINDO a tabela
DROP TABLE mescla;
-- Nota.......: Os dois comandos anteriores fazem parte do grupo de comandos DDL (data defination language)
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Aberta pela Direita
-- Definição..: Empararelhamento de linhas de duas tabelas atendendo a uma condição e 'trazendo'
--              todas as linhas da tabela do lado direito do operador JOIN
-- AlgebraRel.: select * from <tab1>[->t1] [[Condição][ <tab2>[->t1]
-- Exemplo.AR.:  medicos->m[[i.idinstituicao = m.instdeensinoid]]instituicoesdeensino->i
-- Nota.......: Se os operadores de troca de nome forem usados a condição deverá usar os novos Nomes de tab1 e tab2.
-- Sintaxe SQL: select * from <tab1> RIGHT JOIN <tb2> ON <condição>; [ANSI:92]
-- Nota.......: Na junção a condição quase sempre é feita entre CP e CE.
-- Exemplo SQL:
select idmedico, txnomemedico, i.idinstituicao, i.txnomeinstituicao
       from medicos as m right join instituicoesdeensino as i on i.idinstituicao = m.instdeensinoid;
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Aberta pela Esquerda
-- Definição..: Empararelhamento de linhas de duas tabelas atendendo a uma condição e 'trazendo'
--              todas as linhas da tabela do lado esquerdo do operador JOIN
-- AlgebraRel.: select * from <tab1>[->t1] ][Condição]] <tab2>[->t1]
-- Nota.......: Se os operadores de troca de nome forem usados a condição deverá usar os novos Nomes de tab1 e tab2.
-- Sintaxe AR.: select * from <tab1> LEFT JOIN <tb2> ON <condição>; [ANSI:92]
-- Nota.......: Na junção a condição quase sempre é feita entre CP e CE.
-- Exemplo.AR.: medicos->m ][i.idinstituicao = m.instdeensinoid]] instituicoesdeensino->i
-- Nota.......: Em SGBDs com mais de 25 anos era imperativo que a condição tivesse sempre a chave primária do lado
--              'esquerdo' da condição. Isso implicava em uma melhor performance no algoritmo que fazia o rastreio
--              dos dados nas estruturas de índices correspondentes.
-- Sintaxe SQL: select * from <tab1> LEFT JOIN <tab2> ON <condição>;
-- Exemplo SQL:
select idmedico, txnomemedico, i.idinstituicao, i.txnomeinstituicao
       from medicos as m left join instituicoesdeensino as i on i.idinstituicao = m.instdeensinoid;
-- Nota.......: no comando acima foi feita a junção aberta pela esquerda e do resultado foi feita uma PROJEÇÃO.
--              este comando não tem uma 'tradução' para UMA Operação da AR.
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Completa (Aberta pelos dois lados)
-- Definição..: Empararelhamento de linhas de duas tabelas atendendo a uma condição e 'trazendo'
--              todas as linhas das DUAS tabelas (emparelhadas pela condição e as NÃO emparelhadas).
-- AlgebraRel.: select * from <tab1>[->t1] ][Condição][ <tab2>[->t1]
-- Nota.......: Se os operadores de troca de nome forem usados a condição deverá usar os novos Nomes de tab1 e tab2.
-- Sintaxe SQL: select * from <tab1> FULL OUTER JOIN <tb2> ON <condição>; [ANSI:92]
-- Nota.......: Na junção a condição quase sempre é feita entre CP e CE.
-- Exemplo....:
select idmedico, txnomemedico, i.idinstituicao, i.txnomeinstituicao
       from medicos as m full outer join instituicoesdeensino as i on i.idinstituicao = m.instdeensinoid;
-- --------------------------------------------------------------------------------------------------------------------
-- Em 2024-09-12
-- Para as operações restantes: União, subtração e intersecção, é necessário que as tabelas sejam União Compatível (UC).
-- Dadas duas tabelas A e B, diz-se que são UC se e somnete se tiveram ESQUEMAS idênticos
-- (Campos com mesmo Nome, Tipo de Dado, Tamanho e Ordem dentro da tabela)
-- Vamos criar duas tabelas (temporárias) que sejam UC.
-- Criando f1 partindo de funcinarios onde o cefuncao entre 1 e 6
CREATE temporary table f1 as (SELECT * FROM funcionarios where cefuncao between 1 and 6 order by cefuncao);
-- Criando f2 partindo de funcinarios onde o cefuncao entre 5 e 7
CREATE temporary table f2 as (SELECT * FROM funcionarios where cefuncao between 5 and 7 order by cefuncao);
-- Olhando as linhas das tabelas
select * from f1;
select * from f2;
-- União
-- Defininição: Gera um conjunto com TODAS as linhas de DUAS tabelas.
-- AlgebraRel.: <tab1> U <tab2>
-- Nota.......: As tabelas DEVEM SER UC.
-- Sintaxe.SQL: select * from <tab1> UNION (select * from <tab2>);
-- Nota.......: No Padrão ANSI:86 o operador UNION exibe TODAS as linhas de <tab1> e <tab2>, inclusive com a 
--              DUPLICIDADE de linhas da Intersecção. Já no Padrão ANSI:92 o operador OMITE as repetições,
--              oferecendo o complemento ALL que apresenta explicitamente as linhas da intersecção repetidas.
-- Exemplo....: 
select * from f1 union (select * from f2);
-- Nota.......: o comando pode ser complementado com operadores de ordenação (orde by)
-- --------------------------------------------------------------------------------------------------------------------
-- Intersecção
-- Defininição: Gera um conjunto de dados com as LINHAS COMUNS das Duas Tabelas
-- AlgebraRel.: <tab1> ^ <tab2>
-- Sintaxe.SQL: select * from f1 INTERSECT (select * from <tab2>);
-- Exemplo....: 
select * from f1 intersect (select * from f2);
-- --------------------------------------------------------------------------------------------------------------------
-- Subtração
-- Defininição: Gera um conjunto de dados com as linhas da primeira tabela que não estão presentes na segunda tabela.
-- AlgebraRel.: <tab1> - <tab2>
-- Nota.......: Esta operação NÃO é COMUTATIVA (A-B é diferente de B-A)
-- Sintaxe.SQL: select * from <tab1> MINUS | EXCEPT (select * from <tab2>)
-- Nota.......: MINUS foi 'imposto' pela Oracle e o EXCEPT importo pela IBM e o ANSI aceitou os dois.
-- Exemplo....: 
select * from f1 except (select * from f2);
-- No PostgreSQL o MINUS não funcionou
-- --------------------------------------------------------------------------------------------------------------------


