-- Arquivos de comandos SQL - Exemplos para a turma da tarde
-- Este sinal declara um comentário em SQL.
-- Este arquivo contém exemplos dos comandos SQL para cada operação da AR.
-- ==================================================================================================================================================
-- ==================================================================================================================================================
-- Operação: Seleção
--           Gera um subconjunto de linhas de uma tabela que atendam a uma condição.
-- Em AR...: <tab> [[ Condição ]]
-- Em SQL..: SELECT * FROM <tab> WHERE <cond>;
-- Exemplo.: medicos [[ fklogradouromoradia>=500 ]]
select * from medicos; -- lendo todos os dados de uma tabela... pode ser também select * from medicos where 1=1;
select * from medicos where medicos.fklogradouromoradia>=500;
-- Estes dados podem ser 'direcionados' para uma TABELA. Usa-se o comando create table, na forma
create table m1 as (select * from medicos where medicos.fklogradouromoradia>=400);
-- removendo a tabela da base
drop table m1;
-- O create table "cria o arquivo da tabela fisicamente". Isso pode NÃO ser interessante.
-- Para criar arquivos temporários usa-se:
create temporary table m1 as (select * from medicos where medicos.fklogradouromoradia>=400);
select * from m1;
-- removendo a tabela S do setmemory.
drop table m1;
-- o Comando DROP TABLE remove qualquer tabela da base (seja materializada ou não).
-- ==================================================================================================================================================
-- Operação: Projeção
--           Gera uma tabela com campos escolhidos à partir de outra tabela
-- Em AR...: <tab> [[ ListaDeCampos ]]
-- Em SQL..: select <listadecampos - nome separados por ','> from <tab> 
-- Exemplo.: medicos [[ pkmedico, nucrm, txnomemedico, dtcadmedico ]]
select pkmedico, nucrm, txnomemedico,dtcadmedico from medicos;
-- ==================================================================================================================================================
-- Operação: Produto Cartesiano
--           Gera uma tabela emparelhando cada linha uma tabela com todas as linhas de outra tabela.
-- Em AR...: <tab1> X <tab2>
-- Em SQL..: select * from <tab1> , <tab2> ; -- ANSI:87
--           select * from <tab1> CROSS JOIN <tab2>; -- ANSI:92
-- Exemplo.:  cores X funcoes
select * from cores, funcoes; -- ANSI:87
select * from cores cross join funcoes;  -- ANSI:92
-- Este comando processa a 'criação' de combinações de dados.
-- ==================================================================================================================================================
-- Operação: Junção Natural (Fechada)
--           Gera uma tabela emparelhando cada linha uma tabela com todas as linhas de outra tabela desde que
--           atendam a uma condição (em geral: Chave primária de uma tab IGUAL à Chave estrangeira em outra tab)
-- Em AR...: <tab1> [[ <condição da junção> ]] <tab2>
-- Em SQL..: select * from <tab1>, <tab2> where <condição da junção>; -- ANSI:87
--           select * from <tab1> INNER JOIN <tab2> ON <condição da junção>-- ANSI:92
-- Exemplo.: medicos -> m [[ m.fkinstituicao = i.pkinstituicao ]] instituicoesdeensino as i
select m.fklogradouromoradia as morada, i.fklogradouro as endinst
       from medicos as m inner join instituicoesdeensino as i on m.fkinstituicao = i.pkinstituicao
	   order by m.fklogradouromoradia;
-- Este comando pode ser complementado com uma selação seguindo o comando WHERE na forma
--         where m.fklogradouromoradia = i.fklogradouro;
-- AS é um complemento que muda os nomes dos elementos em tempo de execução SEM ALTERAR A ESTRUTURA dos Elementos
-- usando a sintaxe ANSI:87 para escrever um comando 
select m.fklogradouromoradia as morada, i.fklogradouro as endinst
       from medicos as m, instituicoesdeensino as i
	   where m.fkinstituicao = i.pkinstituicao
==================================================================================================================================================
-- Operação: Junção Aberta pela Direita
--           Gera uma tabela emparelhando cada linha uma tabela com todas as linhas de outra tabela desde que
--           atendam a uma condição (em geral: Chave primária de uma tab IGUAL à Chave estrangeira em outra tab),
--           porém mantendo TODAS as linhas da tabela da direita mesmo as não emparelhadas.
-- Em AR...: <tab1> [[ <condição da junção> ][ <tab2> -- Note somente o colchete de 'fora do lado direito' está voltado para a direita.
-- Em SQL..: select * from <tab1> RIGTH JOIN <tab2> ON <condição da junção>
-- Exemplo.: medicos -> m [[ m.fkinstituicao = i.pkinstituicao ][ instituicoesdeensino as i
select m.pkmedico, m.txnomemedico, i.pkinstituicao, i.txnomeinstituicao
       from medicos as m RIGHT join instituicoesdeensino as i on m.fkinstituicao = i.pkinstituicao;
-- note os médicos que aparecem no final da lista. São médicos dos quais não se tem o fkinstituicao;
-- ==================================================================================================================================================
-- Operação: Junção Aberta pela Esquerda
--           Gera uma tabela emparelhando cada linha uma tabela com todas as linhas de outra tabela desde que
--           atendam a uma condição (em geral: Chave primária de uma tab IGUAL à Chave estrangeira em outra tab),
--           porém mantendo TODAS as linhas da tabela da esquerda mesmo as não emparelhadas.
-- Em AR...: <tab1> ][ <condição da junção> ]] <tab2> -- Note somente o colchete de 'fora do lado esquerdo' está voltado para a esquerda.
-- Em SQL..: select * from <tab1> LEFT RIGTH JOIN <tab2> ON <condição da junção>
-- Exemplo.: medicos -> m ][ m.fkinstituicao = i.pkinstituicao ]] instituicoesdeensino as i
select m.pkmedico, m.txnomemedico, i.pkinstituicao, i.txnomeinstituicao
       from medicos as m left join instituicoesdeensino as i on m.fkinstituicao = i.pkinstituicao
	   where i.pkinstituicao is null;
-- ==================================================;================================================================================================
-- Operação: Junção Completa (Aberta pelos dois lados)
--           Gera uma tabela emparelhando cada linha uma tabela com todas as linhas de outra tabela desde que
--           atendam a uma condição (em geral: Chave primária de uma tab IGUAL à Chave estrangeira em outra tab),
--           porém mantendo TODAS as linhas da tabela da direita e da esquerda mesmo as não emparelhadas.
-- Em AR...: <tab1> ][ <condição da junção> ][ <tab2> -- Note os DOIS colchetes 'de fora' abertos.
-- Em SQL..: select * from <tab1> FULL OUTER JOIN <tab2> ON <condição da junção>
-- Exemplo.: medicos -> m ][ m.fkinstituicao = i.pkinstituicao ][ instituicoesdeensino as i
select pkmedico, txnomemedico, pkinstituicao, txnomeinstituicao
       from medicos as m full outer join instituicoesdeensino as i on m.fkinstituicao = i.pkinstituicao;
-- O comando acima faz uma junção completa e mostra (nos registros finais) os dois exemplos de junções
-- vistos anteriormente. Além da junção completa fazemos uma projeção para facilitar a visão dos dados.
-- ==================================================================================================================================================
-- Para as operações UNIÃO, SUBTRAÇÃO e INTERSECÇÃO a base de dados deve conter tabelas união compatíveis (mesmo que temporárias).
-- Lembrando: tabelas UC PRECISAM ter o esquema completamente igual. Ou de outra forma:
--            - Campos com mesmo nome
--            - Campos com mesmo tipo de dado
--            - Campos com mesmo tamanho
--            - Campos na mesma ordem dentro da tabela
-- Alguns SGBDs não verificam todos estes requisitos. Teste depois com o PostgreSQL.
-- Bem, vamos gerar tabelas UC (partindo de médicos). Elas serão temporárias... Ou seja, 
-- para testar seus comandos de União, Subtração e Intersecção, EXECUTE ANTES os comandos que criam estas tabelas temporárias.
-- create temporary table m1 as (select * from medicos where fkespecialidade >= 4);
create temporary table m1 as (select * from medicos where fkespecialidade <= 4);
select * from m1; -- recupera 12 linhas
create temporary table m2 as (select * from medicos where fkespecialidade >= 4);
select * from m2; -- recupera 8 linhas
-- Note: nestes conjuntos existem tuplas da intersecção (aparecem nas duas tabelas)
-- ==================================================================================================================================================
-- Operação: União
--           Gera uma tabela com as tuplas comuns e não comuns de duas tabelas união compatíveis.
-- Em AR...: <tab1> U <tab2>
-- Em SQL..: select * from <tab1> UNION (select * from <tab2>);
-- Exemplo.:
select * from m1 UNION ALL (select * from m2); -- União mostrando TODAS as linhas (repetidas e não repetidas)
-- O PostgreSQL NÃO exige que os () sejam escritos no comando. Isso pode facilitar a escrita de muitos comandos.
-- ==================================================================================================================================================
-- Operação: Subtração
--           Gera uma tabela com as tuplas da primeira tabela que não aparecem na segunda tabela.
-- Em AR...: <tab1> - <tab2>
-- Em SQL..: select * from <tab> MINUS | EXCEPT (select * from <tab2>);
-- Exemplo.:
select * from m1 EXCEPT (select * from m2); -- Subtração tirando de m1 as tuplas que aparecem em m2
--                                            Portanto o PostgreSQL não reconhece este comando.
-- Entretanto, o comando EXCEPT... vale para gerenciadores que seguem o padrão desde 87.
-- Em 92 foi introduzido o MINUS (aceito em alguns SGBDs - MariaDB, MySQL, etc.)
-- Invertendo as tabelas no comando...
select * from m2 EXCEPT (select * from m1); -- Subtração tirando de m1 as tuplas que aparecem em m2
-- O resultado deste comando NÃO É O MESMO do primeiro... o comando não é comutativo.
-- ==================================================================================================================================================
-- Operação: Intersecção
--           Gera uma tabela com as tuplas comuns de duas tabelas união compatíveis.
-- Em AR...: <tab1> ^ <tab2>
-- Em SQL..: select * from <tab1> intersect (select * from <tab2>);
-- Exemplo.:
select * from m2 EXCEPT (select * from m1); -- Subtração tirando de m1 as tuplas que aparecem em m2
-- ==================================================================================================================================================
-- ==================================================================================================================================================
-- Vamos agora escrever os comandos de atualização de tabelas do banco de dados.
-- Os comandos de Atualização na SQL são três: INSERT, DELETE e UPDATE.
-- Como esttes comandos não tem representação na AR, vamos apresenta-los somente com a sintaxe da SQL.
-- ==================================================================================================================================================
-- Comandos: INSERT
-- Em SQL..: INSERT INTO <TAB> [(CAMPOS DE <TAB>)] VALUES (LISTA DE VALORES1)[,(LISTA DE VALORES 2),...(LISTA DE VALORES n)];
-- Exemplo.: 
insert into medicos (pkmedico,txnomemedico,nucrm,fkespecialidade,fkinstituicao,
                     fklogradouromoradia,txcomplementomoradia,nucepmoradia,
					 fklogradouroclinica,txcomplementoclinica,nucepclinica,aosituacao,dtcadmedico)
       values (19,'Marcelo Alves de Lima e Cruz','23456543','1','1','350','1450 Ap.251','14512546','620','2345 - Clinica A Sala 55','17885264','A','2023-03-15');
-- ou
insert into medicos (pkmedico,txnomemedico,nucrm,fkespecialidade,fkinstituicao,fklogradouromoradia,txcomplementomoradia,nucepmoradia,fklogradouroclinica,txcomplementoclinica,nucepclinica,aosituacao,dtcadmedico)
       values (19,'Marcelo Alves de Lima e Cruz','23456543','1','1','350','1450 Ap.251','14512546','620','2345 - Clinica A Sala 55','17885264','A','2023-03-15'),
              (20,'Marcelo Alves de Lima e Cruz','23456543','1','1','350','1450 Ap.251','14512546','620','2345 - Clinica A Sala 55','17885264','A','2023-03-15');
-- Existem sintaxes complementares
-- Referencie estudos em: https://www.postgresql.org/docs/current/sql-insert.html
select * from medicos;
-- ==================================================================================================================================================
-- Comandos: DELETE
-- Em SQL..: DELETE FROM <TAB> [WHERE <condição>];
-- Exemplo.:
DELETE FROM MEDICOS where pkmedico=19;
-- O complemento WHERE é usado para 'selecionar' as linhas que serão excluídas. SE NÃO especificado,
-- TODAS as linhas da tabela excluídas.
-- ==================================================================================================================================================
-- Comandos: UPDATE
-- Em SQL..: UPDATE <TAB> SET <atrib1>[,<atrib2>,...,<atribn>] [WHERE <condição>];
-- Exemplo.:
update medicos set aosituacao='A' where aosituacao='D';
-- ==================================================================================================================================================
-- Exemplos de questões das fases 2, 3 e 4 do PraticaSQL
-- ==================================================================================================================================================
-- Exemplo de uma sequencia de SQL que resolve uma pergunta: FASE 2 do PraticaSQL
-- Questão do Slide resolvida com uso de projeções parciais para 'fugir' da repetição de nomes de campos
-- Seleção : R <- Clientes[[Nome="José"]]
create temporary table R as (select * from clientes where txnome='José');
-- Projeção: R1 <- R[[cdcli->cdcliente]]
create temporary table R1 as (select cdcli as cdcliente from R);
-- Junção  : S <- R1[[R1.CdCliente=Carros.CdCli]]Carros
create temporary table S as (select * from R1 inner join carros on R1.CdCliente=Carros.CdCli);
-- Projeção: S1 <- S[[cdcor->cecor, cdcarro->cpcarro]]
create temporary table S1 as (select cdcor as cecor, cdcarro as cpcarro from S);
-- Junção  : T <- S1[[S1.ceCor=Cores.CdCor]]Cores
create temporary table T as (select * from S1 inner join cores on S1.cecor=Cores.CdCor);
-- Seleção : G <- T[[txNome="Prata"]]
create temporary table G as (select * from T where txnome='Prata');
-- Junção  : W <- G[[G.cpCarro=Paradas.CdCarro]]Paradas
create temporary table W as (select * from G inner join paradas on G.cpCarro=Paradas.CdCarro);
-- Seleção : K <- W[[dtent entre "01/05/2010" "31/05/2010" e "01/05/2010" <= dtsai <= "31/05/2010"]]
create temporary table K as (select * from W where dtent between '2023-05-31 00:00:00' and '2023-05-31 23:59:59' or dtsai between '2023-05-31 00:00:00' and '2023-05-31 23:59:59');
-- Projeção: F <- K[[dtent, dtsai]](distintos)
create temporary table F as (select dtent,dtsai from K);
select * from F

drop table R,R1,S,S1,T,G,W,K,F;
-- ==================================================================================================================================================
-- Exemplo de questão na Fase 3 do PraticaSQL (Questão apresentada SEM AR de resposta)
-- Aluno deve desenvolver AR que resolve a questão e traduzir para SQL... e somente isso.
-- Aqui vamos resolver complete outro exercício de AR-SQL e 'reduzir' os comandos da respostas 'combinando' os comandos traduzidos da AR.
-- Dadas as tabelas abaixo responda à pergunta:
--     Quais as marcas dos carros cujos os donos tenham nome “Jose” e que
--     não fizeram serviço de "pintura" no mês de “Maio de 2010”?
--

--  Clientes       Carros                  Aplicação                  Serviços
-- |cdcli|nome|   |CdCli|CdCarro|Marca|   |CdCarro|CdSer|DtServiço|  |CdSer|Nome|

-- AR para adaptar as estruturas das tabelas para NÃO apresentarem campos com nomes repetidos
-- Facilitando a escrita de comandos SQL
-- Projeção.: CL <- clientes [[ cdcli->cpcliente, nome-> txnomecliente]]
-- Projeção.: CR <- carros [[ cdcli->cecliente, cdcarro->cpcarro, marca->txmarca]]
-- Projeção.: AP <- aplicacao [[ cdcarro->cecarro,cdser->ceservico, dtservico ]]
-- Projeção.: SR <- servicos[[ cdser->cpservico,nome->txnomeservico ]]
-- Seleção..: A <- CL[[ txnomecliente='José' ]]
-- Seleção..: B <- AP[[ dtservico entre '01/05/2010' e '31/05/2010' ]]
-- Seleção..: C <- SR[[ txnomeservico<>'pintura' ]]
-- Junção...: D <- C [[ C.cpservico=B.ceservico ]] B
-- Junção...: E <- D [[ D.cecarro=CR.cpcarro ]] CR
-- Junção...: F <- E [[ E.cecliente=A.cpcliente ]] A
-- Projeção.: F [[ txmarca ]]
-- Pode-se, agora, traduzir todas as operações para SQL.

-- Projeção.: CL <- clientes [[ cdcli->cpcliente, nome-> txnomecliente]]
create TEMPORARY table CL as (select cdcli as cpcliente, nome as txnomecliente from clientes);
-- Projeção.: CR <- carros [[ cdcli->cecliente, cdcarro->cpcarro, marca->txmarca]]
create TEMPORARY table CR as (select cdcli as cecliente, cdcarro as cpcarro, marca as txmarca from carros );
-- Projeção.: AP <- aplicacao [[ cdcarro->cecarro,cdser->ceservico, dtservico ]]
create TEMPORARY table AP as (select cdcarro as cecarro, cdser as ceservico, dtservico from  aplicacao);
-- Projeção.: SR <- servicos[[ cdser->cpservico,nome->txnomeservico ]]
create TEMPORARY table SR as (select cdser as cpservico, nome as txnomeservico from servicos);
-- Seleção..: A <- CL[[ txnomecliente='José' ]]
create TEMPORARY table A as (select * from CL where txnomecliente='José');
-- Seleção..: B <- AP[[ dtservico entre '01/05/2010' e '31/05/2010' ]]
create TEMPORARY table B as (select * from AP where dtservico between '2010-05-01' and '2010-05-31' );
-- Seleção..: C <- SR[[ txnomeservico<>'pintura' ]]
create TEMPORARY table C as (select * from SR where txnomeservico <> 'pintura');
-- Junção...: D <- C [[ C.cpservico=B.ceservico ]] B
create TEMPORARY table D as (select * from C inner join B on C.cpservico=B.ceservico);
-- Junção...: E <- D [[ D.cecarro=CR.cpcarro ]] CR
create TEMPORARY table E as (select * from D inner join CR on D.cecarro=CR.cpcarro);
-- Junção...: F <- E [[ E.cecliente=A.cpcliente ]] A
create TEMPORARY table F as (select * from E inner join A on E.cecliente=A.cpcliente);
-- Projeção.: F [[ txmarca ]]
select txmarca from f;
drop table CR,CL,AP,SR,A,B,C,D,E,F;

-- ==================================================================================================================================================
-- Exemplo de questão na fase 4 do PraticaSQL (Questão que deve ser resolvida com comando SQL otimizado).
-- Trabalhando sobre a mesma sequencia de comandos da questão anterior escrevemos:
select txmarca from (select cdser as cpservico, nome as txnomeservico from servicos) as SR
                    inner join (select cdcarro as cecarro, cdser as ceservico, dtservico from  aplicacao) as AP on SR.cpservico=AP.ceservico
					inner join (select cdcli as cecliente, cdcarro as cpcarro, marca as txmarca from carros ) as CR on AP.cecarro=CR.cpcarro
					inner join (select cdcli as cpcliente, nome as txnomecliente from clientes) as CL on CR.cecliente=CL.cpcliente
			   where dtservico between '2010-05-01' and '2010-05-31' and
					 txnomeservico <> 'pintura' and
					 txnomecliente='José'
-- Veja que este comando processa as 4 projeções iniciais criando tabelas com as quais são feitas
-- as junções e seleções subsequentes.
-- No 'final' do processamento é feita a Projeção do campo txmarca (criado em uma das projeções iniciais.
-- EMBORA a 'ordem' como são apresentadas as junções (inner join) dê uma SUGESTÃO de como são acessados os
-- arquivos de dados, é o ANALISADOR DE ESTRATÉGIA DE ACESSO que decide a melhor ordem de execução dos comandos de junção.
-- Na maior parte dos SGBD o algoritmo 'resolve' a rota à partir do processamento do menor volume de dados possível.
-- 