-- Neste arquivo estão os comandos que foram desenvolvidos em sala de aula com a turma da tarde.
-- É importante que você crie um arquivo com seus comandos como estão apresentados nesta referencia.
-- No SEU arquivo indique a operação e a seguir o comando que implementa a operação.
-- Como o SEU arquivo será um 'campo de teste', você não precisa escrever a indicação da operação em AR.
-- Escreva somente o comando SQL.
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Seleção
-- AR     TAB [[condição de comparação]]
-- SQL    select * from TAB where condição de comparação
-- exemplo
-- Selecionando TODAS AS LINHAS DE UMA TABELA
select * from cursos where 1=1;
-- Selecionando as linhas de cursos onde a dtcadcurso<=2010-12-31
select * from cursos where dtcadcurso<='2010-12-31';
-- Selecionando as linhas de cursos com dtcadcurso entre 01-01-2010 e 31-12-2010.
select * from cursos where dtcadcurso between '2010-01-01' and '2010-12-31';
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Projeção
-- AR   TAB[[lista de campos]]
-- SQL  select lista de campos from TAB;
-- exemplo
-- Projetando pkcurso, txnomecurso, qtcargahoraria da tabela cursos
select pkcurso, txnomecurso, qtcargahoraria from cursos;
-- CUIDADO!!!
-- EM SQL É POSSÍVEL COMBINAR COMANDOS DE OPERAÇÕES DIFERENTES.
-- Quais são os códigos, nomes e carga horária dos cursos criados em 2010?
-- AR A <- cursos[[dtcadcurso entre '01-01-2010' e '31-12-2010']]
--         A [[pkcurso, txnomecurso, qtcargahoraria]]
-- Em SQL
create temporary table A AS (select * from cursos where dtcadcurso between '2010-01-01' and '2010-12-31');
select pkcurso, txnomecurso, qtcargahoraria from A;
-- ENTRETANTO, em SQL pode-se escrever:
select pkcurso, txnomecurso, qtcargahoraria 
       from cursos 
	   where dtcadcurso between '2010-01-01' and '2010-12-31'
-- 'Dropando' a tabela A
DROP TABLE A;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- junção (fechada ou natural)
-- AR  tab1 [[condição de junção]] tab2
-- SQL select * from tab1, tab2 where condição de junção; (ANSI/87)
-- exemplo
select * from clientes,logradouros where logradouros.pklogradouro=clientes.fklogradouro;
-- SQL select * from tab1 inner join tab2 on condição de junção (ANSI/92)
-- exemplo
select * from clientes INNER JOIN logradouros ON logradouros.pklogradouro=clientes.fklogradouro;
-- Já em 87 o SQL possibilita a conexão de 3 ou mais tabelas em só comando. Basta especificar os nomes das tabelas
-- após o comando FROM e as condições de ligação entre as tabelas.
-- (Lembre que no relacional se fala ligação entre DUAS tabelas).
-- Na base existem as tabelas departamentos, funcionarios e funcoes, sendo possível escrever:
select * from departamentos AS d, funcionarios AS f, funcoes AS fc where d.pkdepto=f.fkdepto and f.fkfuncao=fc.pkfuncao;
-- escrevendo o comando equivalente na sintaxe do ANSI/92
select * from departamentos AS d inner join funcionarios AS f ON  d.pkdepto=f.fkdepto
                                 inner join funcoes AS fc ON f.fkfuncao=fc.pkfuncao;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Produto Cartesiano
-- AR  tab1 X tab2
-- SQL select * from tab1, tab2; (ANSI/87)
-- SQL select * from tab1 CROSS join tab2; (ANSI/92)
-- exemplo (ANSI/87)
select * from cores, funcoes;
-- exemplo (ANSI/92)
select * from cores CROSS join funcoes;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- junções abertas (pela direita)
-- Emparalha as tuplas de duas tabelas trazendo TODAS as tuplas da tabela escrita no lado DIREITO do operador JOIN.
-- AR tab1 [[condição da junção][ tab2
-- SQL select * from tab1 RIGHT join tab on condição da junção.
-- exemplo
select * from funcionarios as f right join departamentos as d on f.fkdepto=d.pkdepto;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- junções abertas (pela esquerda)
-- Emparalha as tuplas de duas tabelas trazendo TODAS as tuplas da tabela escrita no lado ESQUERDO do operador JOIN.
-- AR tab1 ][condição da junção]] tab2
-- SQL select * from tab1 LEFT join tab on condição da junção.
-- exemplo
select * from funcoes as fc LEFT join funcionarios as f on fc.pkfuncao=f.fkfuncao;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- junções abertas (pela esquerda e pela direita - completa)
-- Emparalha as tuplas de duas tabelas trazendo TODAS as tuplas das duas tabelas.
-- PODE ser interpretada como uma UNIÃO entre as junções abertas pela ESQUERDA e pela DIREITA.
-- AR tab1 ][condição da junção][ tab2
-- SQL select * from tab1 FULL OUTER join tab on condição da junção.
-- exemplo
-- empresas é uma tabela que tem o fklograodouro que pode ser NULO indicando que o logradouro é desconhecido.
-- ALTERANDO os valores de fklograodouro para NULL para as empresas onde o pkempresa (2,3)
UPDATE empresas SET fklogradouro=NULL where pkempresa in ('2','3');
-- Agora pode-se escrever o comando de junção completa entre empresas e logradouros, na forma:
select * from empresas as e FULL OUTER  join logradouros as l on e.fklogradouro=l.pklogradouro;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- trataremos agora as operações que exigem que as tabelas sejam UNIÃO COMPATÍVEIS.
-- Gerando as tabelas UC.
create temporary table f as (select pkfuncionario, txprenomes, txsobrenome, fkdepto from funcionarios);
-- Gerando A <- f[[fkdpeto entre ('A11', 'E11')]]
-- Gerendo B <- f[[fkdpeto entre ('A11', 'D11')]]
-- em SQL
create temporary table A as (select * from f where fkdepto in ('A11','E11'));
create temporary table B as (select * from f where fkdepto in ('A11','D11'));
-- Agora vamos executar as operações.
-- SUBTRAÇÃO
-- AR: tab1 - tab2
-- SQL: select * from tab1 minus | execpt (select * from tab2);
-- exemplo
select * from a except (select * from b);
select * from b except (select * from a);
-- INTERSECÇÃO
-- AR: tab1 ^ tab2
-- SQL: select * from tab1 intersect (select * from tab2);
-- exemplo
select * from a intersect (select * from b);
-- UNIÃO
-- AR: tab1 U tab2
-- SQL: select * from tab1 UNION (select * from tab2);
-- Em ANSI/87 o UNION trazia linhas COM REPETIÇÃO.
-- Em ANSI/92 o UNION traz linha SEM repatição (se for preciso usa-se o complemento ALL para ver as repatições.)
select * from a UNION (select * from b);
DROP TABLE a,b,f;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Finalizamos os comandos de 'CONSULTA' (Query).
-- Agora vamos ver os comandos de Atualização. Estes comandos NÃO TEM REPRESENTAÇÃO EM AR.
-- Inclusão de Linhas
-- SQL: INSERT INTO tab (lista de campos que recebem valores) VALUES (lista de valores)[,(lista de valores)];
-- exemplo:
INSERT INTO CORES (pkcor, txnomecor, nupantone, dtcadcor) VALUES (11,'AMARELA',234567,'2022-03-03');
INSERT INTO CORES  VALUES (12,'AMARELA',234567,'2022-03-03');
INSERT INTO CORES  VALUES (13,'AMARELA',234567,'2022-03-03'),
                          (14,'AMARELA',234567,'2022-03-03');
-- No PostgreSQL é possível gerar um conjunto de dados e 'abastecer' uma tabela com estes dados.
-- Analise o exemplo:
insert into cursos SELECT (PKCURSO+4) AS pkcurso, txnomecurso, '5' as fkinstituicao, qtcargahoraria, dtcadcurso 
       from cursos where fkinstituicao>=3;
-- NOTE: A palavra VALUES foi descartada.
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Alteração de Linhas
-- SQL: UPDATE FROM tab SET Atribuiçoes [WHERE condições];
-- exemplo
UPDATE cursos SET fkinstituicao=5, dtcadcurso='2022-03-03' WHERE pkcurso in ('1','2');
-- NOTE: a omissão da condição (no WHERE) causa a alteração de TODAS as linhas da tabela.
--       o trecho pode ter várias atribuições de valore em campo (seperadas por vírgula)
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Exclusão de Linhas
-- SQL: DELETE FROM tab [WHERE condição];
-- exemplo
delete from cursos where pkcurso >= 8 and dtcadcurso<='2000-01-01';
-- Todas as linhas que atenderam a condições são excluídas... modificando a condição, podemos escrever:
delete from cursos where pkcurso >= 8;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Agora podemos começar a sequencias de comandos para responder perguntas que tratam os dados sobre a base de dados.
-- Qual a quantidade de disciplinas atribuídas para cada professor nos cursos da instituicao de ensino "Faculdade de Arquitetura e Urbanismo da USP"
-- Vamos desenvolver esta resposta considerando a base já 'corrigida'.
-- Em AR
-- seleção.: A <- instituicoesensino[[txnomeinstituicao='Faculdade de Arquitetura e Urbanismo da USP']]
-- junção..: B <- A[[A.pkinstituicao = cursos.fkinstituicao]]cursos
-- junção..: C <- B[[B.pkcurso = disciplinas.fkcurso]]disciplinas
-- junção..: D <- C[[C.pkdisciplina=atribuicoes.fkdisciplina]]atribuicoes
-- projeção: D1 <- D[[fkdisciplina, fkprofessor]]
-- junção..: E <- D1[[D1.fkprofessor=professores.pkprofessor]]professores
-- projeção: E[[pkprofessor, txnomeprofessor, conta(*) as QtdDisc]]
--              {agrupados por pkprofessor}
--              {ordenados pelo pkprofessor}
-- Em SQL
create temporary table A as 
(select * from instituicoesensino where txnomeinstituicao='Faculdade de Arquitetura e Urbanismo da USP');
create temporary table B as 
(select * from A inner join cursos on A.pkinstituicao = cursos.fkinstituicao);
create temporary table C as 
(select * from B inner join disciplinas on B.pkcurso = disciplinas.fkcurso);
create temporary table D as
(select * from C inner join atribuicoes on C.pkdisciplina=atribuicoes.fkdisciplina);
create temporary table D1 as (select fkdisciplina, fkprofessor from D);
create temporary table E as
(select * from D1 inner join professores on D1.fkprofessor=professores.pkprofessor);
select pkprofessor, txnomeprofessor, COUNT(fkdisciplina) from E group by pkprofessor order by pkprofessor;
-- é de boa prática excluir as tabelas temporárias geradas.
drop table a,b,c,d,d1,e;
-- Estes comandos escritos para resolver esta pergunta podem ser 'reescritos' em uma quantidade menor de comandos.
select * from instituicoesensino inner join cursos 
                                 on instituicoesensino.pkinstituicao = cursos.fkinstituicao
                                 inner join disciplinas
                                 on cursos.pkcurso = disciplinas.fkcurso
                                 inner join atribuicoes
                                 on disciplinas.pkdisciplina=atribuicoes.fkdisciplina
                                 inner join professores
                                 on atribuicoes.fkprofessor=professores.pkprofessor
                                 where txnomeinstituicao='Faculdade de Arquitetura e Urbanismo da USP'
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- 2022-02-24
-- 2022-03-03
-- 2022-03-10
-- 2022-03-17
-- Hoje veremos o procedimento da Divisão e Otimização de comandos em SQL.
-- Para isso responderemos a duas perguntas:
-- 1. Quais são os nomes de professores que têm atribuicoes com TODAS as disciplinas?
-- 2. Quais são os nomes dos propfessores e nomes de suas respectivas disciplinas?
-- 
-- Para responder a pergunta 1. recordemos do conceito de divisão.
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Os comentários foram excluídos neste segmento para adequar o desenho da tabelas.
-- CUIDADO para executar o comando no PGAdmin 4. Marque e execute SOMENTE o segmento de comandos SQL.
 Procedimento da Divisão
F      PF         P
+--+   +--+--+   +--+
|CF|   |CF|CP|   |CP|
|F1|   |F1| A|   | A|
|F2|   |F1| B|   | B|
|F3|   |F2| A|   | C|
|F4|   |F1| C|   +--+
+- +   |F2| B|
       |F3| A|
       |F3| B|
       |F4| A|
       |F3| C|
       |F4| B|
       +--+--+
Quais são os codigos dos 'F' que se ligam com TODOS os 'P'?
prod. cart.: S <- F X P (Gera combinações possíveis)
subtração..: R <- S - PF (do possível tiro o real = sobra o o que não ocorre)
projeção...: T <- R[[cf]](dist.) (lista de F que não se ligam com pelo menos UM P)
subtração..: W <- F - T (Lista de todos os F que se ligam com todos os P).
*/
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Executando uma divisão
-- Quais são os nomes das professores que têm atribuições com TODAS as disciplinas?
-- preparação das tabelas
-- projeção...: F <- disciplinas[[iddisciplina]]
-- projeção...: P <- professores[[idprofessor]]
-- projeção...: PF <- atribuicoes[[disciplinaid->iddisciplina, professorid->idprofessor]]
-- 'fazendo a divisão'
-- prod. cart.: S <- F X P
-- subtração..: R <- S - PF
-- projeção...: T <- R[[idprofessor]](dist.)
-- subtração..: W <- P - T
-- finalizando a pergunta
-- projeção...: K <- W[[idprofessor -> professorid]]
-- junção.....: L <- K[[k.professosrid=professores.idprofessor]]professores
-- projeção...: L[[txnomeprofessor]]

-- Traduzindo para SQL
-- projeção...: F <- disciplinas[[iddisciplina]]
CREATE TEMPORARY TABLE f AS (SELECT iddisciplina FROM disciplinas);
-- projeção...: P <- professores[[idprofessor]]
CREATE TEMPORARY TABLE p AS (SELECT idprofessor FROM professores);
-- projeção...: PF <- atribuicoes[[disciplinaid->iddisciplina, professorid->idprofessor]]
CREATE TEMPORARY TABLE pf AS (SELECT disciplinaid AS iddisciplina, professorid AS idprofessor FROM atribuicoes);
-- prod. cart.: S <- F X P
CREATE TEMPORARY TABLE s AS (SELECT * FROM f CROSS JOIN p);
-- subtração..: R <- S - PF
CREATE TEMPORARY TABLE r AS (SELECT * FROM s except (SELECT * from pf));
-- projeção...: T <- R[[idprofessor]](dist.)
CREATE TEMPORARY TABLE t AS (SELECT DISTINCT idprofessor FROM s);
-- subtração..: W <- P - T
CREATE TEMPORARY TABLE w AS (SELECT * FROM p except (SELECT * from t));
-- projeção...: K <- W[[idprofessor-> professorid]]
CREATE TEMPORARY TABLE K AS (SELECT idprofessor AS professorid FROM W);
-- junção.....: L <- K[[k.professosrid=professores.idprofessor]]professores
CREATE TEMPORARY TABLE L AS (SELECT * FROM k INNER JOIN professores ON k.professorid=professores.idprofessor);
-- projeção...: L[[txnomeprofessor]]
SELECT txnomeprofessor FROM L;
-- removendo as tabelas temporarias
DROP TABLE f,p,pf,s,r,t,w,k,l;
-- --------------------------------------------------------------------------------------------------------------------------------------------------
-- Questão 1. Respondida.
-- ---------------------------------------------------------------------------------------------------------------
-- Questão 2.
-- 2. Quais são os nomes dos professores e nomes de suas respectivas disciplinas?
-- Junção...: A <- professores [[ professores.idprofessor=atribuicoes.professorid ]] atribuicoes
-- Junção...: B <- A [[ A.disciplinaid=disciplinas.iddisciplina ]] disciplinas
-- Projeção.: B [[txnomeprofessor, txnome]]
-- Em SQL
-- Junção...: A <- professores [[ professores.idprofessor=atribuicoes.professorid ]] atribuicoes
create temporary table A as
       (select * from professores inner join atribuicoes on professores.idprofessor=atribuicoes.professorid);
-- Junção...: B <- A [[ A.disciplinaid=disciplinas.iddisciplina ]] disciplinas
create temporary table B as
       (select * from A inner join disciplinas on A.disciplinaid=disciplinas.iddisciplina);
-- Projeção.: B [[txnomeprofessor, txnome]] {ordenado por txnomeprofessor}
select txnomeprofessor, txnome from B order by txnomeprofessor;
-- removendo as tabelas temporárias
drop table A,B;
-- Questão respondida
-- Agora vamos 'otimizar' a resposta da segunda questão
select txnomeprofessor, txnome
       from professores inner join atribuicoes on professores.idprofessor=atribuicoes.professorid
                        inner join disciplinas on atribuicoes.disciplinaid=disciplinas.iddisciplina
       order by txnomeprofessor;
-- Nesta forma:
-- A) NÃO existe a criação de tabelas temporárias.
-- B) A sequencia de conexão e execução é feita pelo SGBD e não pelo analista

