-- 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 where fklogradouromoradia>=500;
-- Estes dados podem ser 'direcionados' para uma TABELA. Usa-se o comando create table, na forma
create table R as (select * from medicos where fklogradouromoradia>=500);
-- 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 S as (select * from medicos where fklogradouromoradia>=500);
select * from S;
-- removendo a tabela S do setmemory.
drop table S;
-- 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, rxnomemedico, 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
-- ==================================================================================================================================================
-- 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 * from medicos as m inner join instituicoesdeensino as i ON m.fkinstituicao = i.pkinstituicao;
-- AS é um complemento que muda os nomes dos elementos em tempo de execução SEM ALTERAR A ESTRUTURA dos Elementos
-- ==================================================================================================================================================
-- 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 * 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 * from medicos as m left join instituicoesdeensino as i ON m.fkinstituicao = i.pkinstituicao;
-- ==================================================================================================================================================
-- 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 * from medicos full outer join instituicoesdeensino on medicos.fkinstituicao = instituicoesdeensino.pkinstituicao;
-- ==================================================================================================================================================
-- 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 (select * from m2); -- Recupera 20 linhas? Se xistirem linhas repetidas estas NÃO SERÃO exibidas. Para ver todas as linhas
select * from m1 UNION ALL (select * from m2);
-- 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 MINUS (select * from m2); -- Repare que o operador MINUS NÃO aparece em cor de comando do SQL.
--                                            Portanto o PostgreSQL não reconhece este comando.
-- Entretanto, o comando EXCEPT...
select * from m1 EXCEPT (select * from m2); -- Veja são as tuplas de m1 que não estão em m2.
-- Invertendo as tabela no comando...
select * from m2 EXCEPT (select * from m1); -- Veja são as tuplas de m1 que não estão em m2.
-- ==================================================================================================================================================
-- 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 m1 INTERSECT (select * from 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: UPDATE
-- Em SQL..: UPDATE <TAB> SET <Atrib1>[,<Atrib2>,...,<AtribN>] [WHERE <Condição>]
-- Exemplo.: 
UPDATE medicos SET fkespecialidade=5 where  fkespecialidade=4;

-- Nota: o subcomando where é opcional!! Quer dizer que se OMITIDO processa TODAS as linhas da tabela.
-- ==================================================================================================================================================
-- Comandos: DELETE
-- Em SQL..: DELETE FROM <TAB> [WHERE <Condição>]
-- Exemplo.: 
delete from medicos where pkmedico=19;
-- Nota: o subcomando where é opcional!! Quer dizer que se OMITIDO processa TODAS as linhas da tabela.
-- ==================================================================================================================================================
-- Exemplo de uma sequencia de SQL que resolve uma pergunta:
-- 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;
-- Nota: Esta questão mostra que a AR pode (às vezes) ser altarerada para resolver a limitação dos SGBDs em NÃO TRATAR a repetição de nomes de campos.
--       Desse modo, no exemplo acima, foram criadas as tabelas R1 e S1 SEM A REPETIÇÃO de campos.
-- ==================================================================================================================================================
-- Aqui vamos resolver 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|
-- Em 30/03 revisando este exercício resolvi executar os comandos, mas para isso precisamos ter as tabelas
-- criadas em uma base de dados. NA BASE do PraticaSQL já existem criadas com estes nomes.
-- Por isso precisamos CRIAR uma nova database e nesta nova base executar:
CREATE TABLE public.clientes
(   cdcli smallint,
    nome character varying(60) );
ALTER TABLE IF EXISTS public.clientes OWNER to postgres;
CREATE TABLE public.carros
(   cdcli smallint,
    cdcarro smallint,
    marca character varying(60) );
ALTER TABLE IF EXISTS public.carros OWNER to postgres;
CREATE TABLE public.aplicacao
(   cdcarro smallint,
    cdser smallint,
    stservico date );
ALTER TABLE IF EXISTS public.aplicacao OWNER to postgres;
CREATE TABLE public.servicos
(   cdser smallint,
    nome character varying(60) );
ALTER TABLE IF EXISTS public.servicos OWNER to postgres;

-- Depois executar os comandos acima podemos executar a sequencia abaixo e avaliar o resultado.
-- Fazendo as projeções para não termos os nomes de campos repetidos no modelo:
-- Projeção: CL <- clientes [[cdcli->cpcliente, nome->txnomecliente ]]
-- Projeção: CR <- carros [[ CdCli->cecliente,CdCarro->cpcarro,Marca->txmarca ]]
-- Projeção: AP <- aplicação [[ CdCarro-> cecarro, CdSer->ceservico, DtServiço->dtaplicacao ]]
-- Projeção: SR <- serviços [[ cdser->cpservico,nome->txnomeservico ]]

-- As estruturas ficaram assim:
--  CL
-- |cpcliente|txnomecliente|
--  CR
-- |ceCliente|cpCarro|txMarca|
--            AP
--           |CeCarro|ceservico|dtaplicacao|
--                    SR
--                   |cpservico|txnomeservico|
-- Note que foi feito o alinhamento das chaves primárias com as respectivas chaves estrangeiras
-- somente para facilitar a construção de condições de junções. ISSO NÃO é NECESSÁRIO para resolver
-- o Exercício.
-- 

-- Resolvendo a questão com as novas tabelas.
-- Projeção: CL <- clientes [[cdcli->cpcliente, nome->txnomecliente ]]
-- Projeção: CR <- carros [[ CdCli->cecliente,CdCarro->cpcarro,Marca->txmarca ]]
-- Projeção: AP <- aplicação [[ CdCarro-> cecarro, CdSer->ceservico, DtServiço->dtaplicacao ]]
-- Projeção: SR <- serviços [[ cdser->cpservico,nome->txnomeservico ]]
-- Seleção.: A <- CL [[ txnomecliente='josé' ]]
-- Seleção.: B <- SR [[ txnomeservico<>'pintura' ]]
-- Seleção.: C <- AP [[ dtaplicacao entre '01/05/2010' e '31/05/2010' ]]
-- Junção..: D <- A [[ A.cpcliente=CR.cecliente ]] CR
-- Junção..: E <- D [[ D.cpcarro=C.cecarro ]] C
-- Junção..: F <- E [[ E.ceservico=B.cpservico ]] B
-- Projeção: B <- F [[ txmarca ]]
-- Pode-se, agora, 'traduzir' a sequencia para SQL, na forma:
-- 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 <- aplicação [[ CdCarro->cecarro, CdSer->ceservico, DtServiço->dtaplicacao ]]
create temporary table AP as (select CdCarro as cecarro, CdSer as ceservico, DtServiço as dtaplicacao from aplicação);
-- Projeção: SR <- serviços [[ cdser->cpservico,nome->txnomeservico ]]
create temporary table SR as (select cdser as cpservico,nome as txnomeservico from serviços);
-- **
-- Seleção.: A <- CL [[ txnomecliente='josé' ]]
create temporary table A as (select * from CL where txnomecliente='josé');
-- Seleção.: B <- SR [[ txnomeservico<>'pintura' ]]
create temporary table B as (select * from SR where txnomeservico<>'pintura');
-- Seleção.: C <- AP [[ dtaplicacao entre '01/05/2010' e '31/05/2010' ]]
create temporary table C as (select * from AP where dtaplicacao between '01/05/2010' and '31/05/2010');
-- Junção..: D <- A [[ A.cpcliente=CR.cecliente ]] CR
create temporary table D as (select * from A inner join CR on A.cpcliente=CR.cecliente);
-- Junção..: E <- D [[ D.cpcarro=C.cecarro ]] C
create temporary table E as (select * from D inner join C on D.cpcarro=C.cecarro);
-- Junção..: F <- E [[ E.ceservico=B.cpservico ]] B
create temporary table F as (select * from E inner join B on E.ceservico=B.cpservico);
-- Projeção: F [[ txmarca ]]
select txmarca from F
-- Escrevendo um só comando que substituirá os comandos de ** até o final da AR.
select txmarca from CL inner join CR on CL.cpcliente=CR.cecliente
                       inner join AP on CR.cpcarro=AP.cecarro
                       inner join SR on SR.cpservico=AP.ceservico
               where CL.txnomecliente='josé' and
                     AP.dtaplicacao between '01/05/2010' and '31/05/2010' and
                     SR.txnomeservico<>'pintura';
-- 
-- Podemos, ainda, trocar os nomes das tabelas temporárias pelos respectivos comandos que as criam,
-- possibilitando UM único comando que faz tudo, na forma:
select txmarca from (select cdcli as cpcliente, nome as txnomecliente from clientes) as CL 
                     inner join 
					(select CdCli as cecliente,CdCarro as cpcarro,Marca as txmarca from carros) as CR
					 on CL.cpcliente=CR.cecliente
                     inner join
					 (select CdCarro as cecarro, CdSer as ceservico, dtservico as dtaplicacao from aplicacao) as AP on CR.cpcarro=AP.cecarro
                     inner join
					 (select cdser as cpservico,nome as txnomeservico from servicos) as SR
					 on SR.cpservico=AP.ceservico
               where CL.txnomecliente='josé' and
                     AP.dtaplicacao between '01/05/2010' and '31/05/2010' and
                     SR.txnomeservico<>'pintura';
-- Com esta última questão resolvida apresentamos os exemplos suficientes para resolver as Fases 2, 3 e 4 do
-- Exercício (ou projeto) PRATICASQL.
-- ==================================================================================================================================================
-- ==================================================================================================================================================
