-- Exemplos de comandos SQL para DML (DQL+DTL)
-- Seleção
-- Definição..: Determina um conjunto de linhas de uma tabela que atendam a uma condição.
-- Nota.......: A Estrutura do DESTINO é igual à estrutura da ORIGEM
-- Sintaxe AR.: <tab> [[ condição ]]
-- Sintaxe SQL: select * from <tab> [where <condição>];
-- Exemplo SQL:
select * from funcionarios where cedepto='A01';
-- Nota.......: TODOS os comandos de SQL (DQL-sendo mais preciso) podem CRIAR uma tabela.
--              Este DESTINO pode ser gravado na base de dados ou ficar na 'memoria RAM' da query tool.
--              Criando o arquivo DESTINO (gravado fisicamente)
create table f1 as (select * from funcionarios where cedepto='A01');
-- Sintaxe AR.: f1 <- funcionarios [[ cedepto='A01' ]]
-- Nota.......: Se quiserem, considerem o ' <- ' como todo o comando create table ... as ();
-- Verificando o conteúdo da tabela f1
select * from f1;
-- Removendo o arquivo do DESTINO
drop table f1;
-- Agora vamos criar na memória RAM (criada quando se abre uma query tool)
create temporary table f1 as (select * from funcionarios where cedepto='A01');
-- f1 existe na memória RAM
-- Lendo f1
select * from f1;
drop table f1;
-- Nota.......: Existem SGBDs que não tem o subcomando 'temporary'. Mas então, SE dois ou mais usuários escreverem o
--              mesmo comando 'create table' com a tabela destino tendo o mesmo nome vai ocorrer um erro?
--              Não... e Sim... DEPENDE do SGBD... Alguns destes SGBDs implementam o conceito de TABELA PER USER.
--              Ou seja, "Ah! O usuário XPTO escreveu um 'create table as...' então coloca o nome do usuário na frente
--              do nome da tabela e grava no HD... o usuário continua a ver o nome da tabela como escreveu no comando,
--              mas o SGBD 'arrumou' um jeito de vincular a tabela ao usuário. E quando o usuário 'encerra' a sessão
--              de trabalho com o Servidor, mesmo que não tenha escrito o DROP TABLE, o SGBD exclui a tabela.
--              Tem algumas situações que esta forma de implementação funciona bem.
--              Porém, e se o usuário realmente quiser criar uma tabela 'definitiva' em uma base? Bem, daí a
--              recomendação é: CRIE a tabela (como o comando DDL CREATE TABLE normalmente escrito declarando
--              a estrutura da tabela), depois gere os dados que deverão ser inseridos na tabela. Daí quando se
--              encarra a sessão a tabela 'persiste' na base de dados."
-- --------------------------------------------------------------------------------------------------------------------
-- Projeção
-- Definição..: Determina um conjunto formado pelas colunas de uma tabela.
-- Nota.......: A QUANTIDADE de linhas da ORIGEM e do DESTINO é a mesma.
-- Sintaxe AR.: <tab> [[ lista de campos ]]
-- Sintaxe SQL: select <lista de campos> from <tab>;
-- Exemplo SQL:
select f.idfuncionario, f.txprenomes, f.txsobrenome, f.dtnascimento from funcionarios as f;
-- Nota.......: No SQL acima foi usado o subcomando ' as '. Ele faz a troca do nome do componente (tabela ou campo)
--              em tempo de execução do comando. No exemplo, o nome da tabela funcionarios passa a ser 'f' e facilita
--              a escrita da lista de campos da projeção. Em AR o ' as ' é o simbolo ' -> '.
-- --------------------------------------------------------------------------------------------------------------------
-- Produto Cartesiano - determina um conjunto de dados EMPARELHANDO cada linha de uma tabela com todas as linhas de
--                      outra tabela. 'EMPARELHAR' é colocar uma linha ao lado da outra. Com todos os campos alinhados.
-- AR: <tab1> X <tab2>
-- Sintaxe SQL: ANSI:87 - select * from <tab1> , <tab2>;
--              ANSI:92 - select * from <tab1> CROSS JOIN <tab2>;
-- Exemplo:
select * from cursos , oficinas; -- ANSI:87
select * from cursos cross join oficinas; -- ANSI:92
select c.cpcurso, c.txnomecurso, o.cpoficina, o.txapelido
       from cursos as c cross join oficinas as o; -- ANSI:92
-- em AR o comando acima seria assim:
-- a <- cursos X oficinas
-- a [[ cpcurso, txnomecurso, cpoficina, txapelido ]]
-- Nota.......: A Tabela DESTINO tem a estrutura da tabela da esquerda ao lado da estrutura da tabela da direita.
--              Se existir algum campo com nome repetido a operação é feita e os nomes dos campos repetidos aparecem
--              no DESTINO. Porém, se o resultado for direcionado para uma tabela, existem SGBD que NÃO vão PERMITIR
--              a repatição de nomes de campos em tabelas... Existem SGBD que tentam criar a tabela e 'vinculam'
--              os nomes das tabelas de ORIGEM aos campos da tabela DESTINO... bem, às vezes isso funciona, outras
--              vezes não. POR CONTA DISSO, Boa PRÁTICA: Determine no seu Modelo Lógico, nomes de campos que não se
--              repitam na base de dados. Por exemplo: Tabela ClienTES, no campo 'data de registro' adote o nome
--              'dtregcliente' pois deste modo o 'nome' deste campo não se repetirá na base de dados.
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Natural (Fechada)
-- Definição..: Determina o EMPARELHAMENTO de linhas de duas tabelas atendendo a uma condição.
--              Nota: maior parte das vezes a condição compara chaves primaria e chave estrangeira.
-- Sintaxe AR.: <tab1>[->t1] [[ condição ]] <tab2>[->t2]
-- Nota.......: Se forem as trocas dinâmicas de nomes '->' a condição deve usar os nomes novos dos conjuntos.
-- Sintaxe SQL: select * from <tab1>, <tab2> [where <condição>]; ANSI:87
--              select * from <tab1> INNER JOIN <tab2> ON <condição>; ANSI:92
-- Exemplo:
select * from funcionarios as f , departamentos as d where d.cpdepto = f.cedepto; -- ANSI:87
select * from funcionarios as f inner join departamentos as d on d.cpdepto = f.cedepto; -- ANSI:92
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Aberta pela Direita
-- Definição..: Determina o EMPARELHAMENTO de linhas de duas tabelas atendendo a uma condição, trazendo TODAS as linhas
--              do conjunto que está à direita na junção.
-- Sintaxe AR.: <tab1>[->t1] [[condição][ <tab2>[->t2]
-- Exemplo AR.: medicos->m [[i.cpinstituicao = m.ceinstdeensino][ instituicoesdeensino->i
-- Sintaxe SQL: select * from <tab1> [as t1] RIGHT Join <tab2> [as t2] on <condição>;
-- Exemplo SQL: Já vamos combinar a junção com uma projeção, na forma:
select m.cpmedico, m.txnomemedico, m.ceinstdeensino, i.cpinstituicao, i.txnomeinstituicao 
       from medicos as m RIGHT join instituicoesdeensino as i on i.cpinstituicao = m.ceinstdeensino;
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Aberta pela Esquerda
-- Definição..: Determina o EMPARELHAMENTO de linhas de duas tabelas atendendo a uma condição, trazendo TODAS as linhas
--              do conjunto que está à esquerda na junção.
-- Sintaxe AR.: <tab1>[->t1] ][condição]] <tab2>[->t2]
-- Exemplo AR.: medicos->m ][i.cpinstituicao = m.ceinstdeensino]] instituicoesdeensino->i
-- Sintaxe SQL: select * from <tab1> [as t1] LEFT Join <tab2> [as t2] on <condição>;
-- Exemplo SQL: Já vamos combinar a junção com uma projeção, na forma:
select m.cpmedico, m.txnomemedico, m.ceinstdeensino, i.cpinstituicao, i.txnomeinstituicao 
       from medicos as m LEFT join instituicoesdeensino as i on i.cpinstituicao = m.ceinstdeensino;
-- --------------------------------------------------------------------------------------------------------------------
-- Junção Completa (Aberta Direita-Esquerda)
-- Definição..: Determina o EMPARELHAMENTO de linhas de duas tabelas atendendo a uma condição, trazendo TODAS as linhas
--              dos dois conjuntos na junção.
-- Sintaxe AR.: <tab1>[->t1] ][condição][ <tab2>[->t2]
-- Exemplo AR.: medicos->m ][i.cpinstituicao = m.ceinstdeensino][ instituicoesdeensino->i
-- Sintaxe SQL: select * from <tab1> [as t1] FULL OUTER Join <tab2> [as t2] on <condição>;
-- Exemplo SQL: Já vamos combinar a junção com uma projeção, na forma:
select m.cpmedico, m.txnomemedico, m.ceinstdeensino, i.cpinstituicao, i.txnomeinstituicao 
       from medicos as m FULL OUTER join instituicoesdeensino as i on i.cpinstituicao = m.ceinstdeensino;
-- Nota.......: o PostgreSQL aceita na junção completa que se escreva somente o FULL (o outer é aceito implicitamente).
select m.cpmedico, m.txnomemedico, m.ceinstdeensino, i.cpinstituicao, i.txnomeinstituicao 
       from medicos as m FULL join instituicoesdeensino as i on i.cpinstituicao = m.ceinstdeensino;
-- --------------------------------------------------------------------------------------------------------------------
-- Para União, intersecção e subtração precisamos criar Tabelas União Compatível.
create temporary table f1 as (select idfuncionario, txprenomes, txsobrenome,cedepto, dtnascimento from funcionarios where cedepto between 'A11' and 'C01');
create temporary table f2 as (select idfuncionario, txprenomes, txsobrenome,cedepto, dtnascimento from funcionarios where cedepto between 'C01' and 'D01');
-- SE FOR PRECISO 'dropar' as tabelas, usa-se: drop table f1, f2;
-- Visuzlizando os dados de f1 e f2
select * from f1;
select * from f2;
-- União
-- subtração
select * from f1 except (select * from f2) order by cedepto;
select * from f2 except (select * from f1);
-- intersecção
select * from f1 intersect (select * from f2) order by cedepto;
-- União
-- Definição..: CRIA um conjunto com as TODAS as linhas dos dois conjuntos.
-- AlgebraRel.: <tab1> U <tab2>
-- Nota.......: Em Teoria dos Conjutnos a União 'mostra' uma só vez a intersecção.
-- Sintaxe.SQL: select * from <tab1> UNION (select * from f2);
-- Nota.......: Em ANSI:86 o UNION repetia as linhas da intersecção em ANSI:92 não acontece isso. MAS
--              exite o operador ALL para ver todas linhas do conjunto da UNIÃO.
-- Exemplo....: 
select * from f1 UNION ALL (select * from f2) order by cedepto; -- As não aparecem e se for necessário usa-se o ALL para ver as repetições.
-- --------------------------------------------------------------------------------------------------------------------
-- Intersecção
-- Definição..:
-- AlgebraRel.:
-- Nota.......:
-- Sintaxe.SQL:
-- Nota.......:
-- Exemplo....: 
-- --------------------------------------------------------------------------------------------------------------------
-- Subtração
-- Definição..:
-- AlgebraRel.:
-- Nota.......:
-- Sintaxe.SQL:
-- Nota.......:
-- Exemplo....: 
-- --------------------------------------------------------------------------------------------------------------------


