-- Arquivo com exemplos de comandos SQL
-- Todos os Comandos de Consulta da Álgebra Relacional
-- (Seleção, Projeção, Produto Cartesiano, Junção, União, Intersecção e Subtração)
-- são implementados pelo MESMO COMANDO SQL (SELECT).
-- Para CADA operação o SELECT tem uma sintaxe complementar (usando palavras reservadas que mudam a forma
-- de saída dos dados no processamento do comando).
-- Aqui vamos apresentar apresentar exemplos mais simples que podem ser melhorados com os complementos.
-- PESQUISE, ESTUDE e USE estes complementos
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Seleção
-- Em AR = Sintaxe: tab1[[condição]]
-- Exemplo: funcionarios[[dtcontratacao entre '1980-01-01' e '1989-12-31']]
-- Em SQL:
select * from funcionarios where dtcontratacao between '1980-01-01' and '1989-12-31';
-- o WHERE permite a especificação de uma condição.
-- Para a SELEÇÃO, a condição é uma comparação de campo com valores.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- projeção
-- Em AR: Sintaxe: tab[[lista de campos]]
-- Exemplo: funcionarios[[idfuncionario, txprenomes, txsobrenome]]
-- Em SQL:
select idfuncionario, txprenomes, txsobrenome from funcionarios;
-- Note: O que foi 'trocado' no comando é o simbolo '*' pela lista de campos da tabela.
--       CUIDADO! NÃO LEIA: select * from tab como 'seleciona todos os campos da tabela...',
--                O que caracteriza a SELEÇÃO é o uso de WHERE comparando campo com valores.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- produto cartesiano
-- Em AR = Sintaxe: Tab1  X  Tab2
-- Exemplo: autores X cores
-- Em SQL:
   -- ANSI:87
      select * from autores, cores;
   -- ANSI:92
      select * from autores cross join cores;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- junção natural (fechada)
-- Em AR = Sintaxe: Tab1  [[condição de junção]]  Tab2
-- Nota: a condição de junção é a comparação de valores de campos
--       Na maioria das vezes, compara-se Chave Primária com Estrangeira
-- Exemplo: medicos->M [[M.especialidadeid=E.idespecialidade]] especialidadesmedicas->E
-- Em SQL:
   -- ANSI:87
      select * from medicos AS M, especialidadesmedicas AS E
               WHERE M.especialidadeid=E.idespecialidade;
   -- ANSI:92
      select * from medicos AS M INNER JOIN especialidadesmedicas AS E
                                 ON M.especialidadeid=E.idespecialidade;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Para as operações: UNIÃO, SUBTRAÇÃO e INTERSECÇÃO as tabelas devem ser União Compatíveis
--                 (campos com mesmo nome, tipo de dado, tamanho e ordem dentro da tabela).
-- Sendo assim, à partir de funcionarios, criam duas tabelas F1 e F2, na forma:
SELECT * FROM public.funcionarios where dtcontratacao <= '2004-12-31' ORDER BY dtcontratacao ASC;
SELECT * FROM public.funcionarios where dtcontratacao >= '2000-01-01' ORDER BY dtcontratacao ASC;
-- E criam-se as tabelas temporárias na forma:
CREATE TEMPORARY TABLE F1 AS
      (SELECT idfuncionario, txprenomes, txsobrenome, dtnascimento, dtcontratacao
              FROM public.funcionarios where dtcontratacao <= '2004-12-31'
              ORDER BY dtcontratacao ASC);
CREATE TEMPORARY TABLE F2 AS
      (SELECT idfuncionario, txprenomes, txsobrenome, dtnascimento, dtcontratacao
              FROM public.funcionarios where dtcontratacao >= '2000-01-01'
              ORDER BY dtcontratacao ASC);
-- Note:
-- cada comando 'gerador' de F1 e F2 é uma Projeção combnada com uma Seleção exibindo os dados 'Ordenados'
-- Analisando os dados de F1 e F2
select * from f1; -- 34 linhas
select * from f2; -- 28 linhas
-- As tabelas estão prontas para fazer as operações UC
-- -----------------------------------------------------------------------------------------------------------------------------------
-- operação: UNIÃO
-- Em AR: Sintaxe: Tab1 U Tab2
-- Exemplo F1 U F2
-- Em SQL
select * from f1 UNION (select * from f2);
-- -----------------------------------------------------------------------------------------------------------------------------------
-- operação: SUBTRAÇÃO
-- Em AR: Sintaxe: Tab1 - Tab2
-- Exemplo F1 - F2
-- Em SQL
select * from f1 MINUS | EXCEPT (select * from f2);
-- Note: O operador MINUS não é executado no PostgreSQL.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- operação: INTERSECÇÃO
-- Em AR: Sintaxe: Tab1 ^ Tab2
-- Exemplo F1 ^ F2
-- Em SQL
select * from f1 INTERSECT (select * from f2);
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Tratando as junçoes abertas
-- Na proposta do modelo relacional as situações de dados não ligados não foram considerados importantes. Entretanto, percebeu-se
-- com o tempo que dados não ligados em uma junção tinham importância. Daí os desenvolvedores do SQL propuseram o operador JOIN com a
-- possibilidade de realizar as junções abertas. Agora apresenta-se o operador JOIN com os complementos para junções abertas.
-- operação: Junção natural (fechada) - Revendo!
-- Em AR => Sintaxe: Tab1 [[Condição de Ligação]] Tab2
-- Em SQL (ANSI:92) - Operador JOIN com complemento INNER
--                    SELECT * FROM Tab1 INNER [OUTER] JOIN Tab2 ON Condição.
-- Exemplo:
select idmedico, txnomemedico, logradouromoradiaid,idlogradouro,txnomelogradouro
       from medicos as m inner join logradouros as lm on m.logradouromoradiaid=lm.idlogradouro;
-- --------------
-- Junçao Aberta pela esquerda
-- Em AR => Sintaxe: Tab1 ][Condição de Ligação]] Tab2
-- Note: Apenas um colchete (do lado esquerdo) indicando a junção aberta pela esquerda.
--       Todas as linhas de Tab1 serão exibidas na execução do comando.
-- Em SQL (ANSI:92) - Operador JOIN com complemento LEFT
--                    SELECT * FROM Tab1 LEFT [OUTER] JOIN Tab2 ON Condição.
-- Exemplo:
select idmedico, txnomemedico, logradouromoradiaid,idlogradouro,txnomelogradouro
       from medicos as m left join logradouros as lm on m.logradouromoradiaid=lm.idlogradouro;
-- --------------
-- junção aberta pela direita
-- Em AR => Sintaxe: Tab1 [[Condição de Ligação][ Tab2
-- Note: Apenas um colchete (do lado direito) indicando a junção aberta pela direita.
--       Todas as linhas de Tab2 serão exibidas na execução do comando.
-- Em SQL (ANSI:92) - Operador JOIN com complemento RIGHT
--                    SELECT * FROM Tab1 RIGHT [OUTER] JOIN Tab2 ON Condição.
-- Exemplo:
select idmedico, txnomemedico, logradouromoradiaid,idlogradouro,txnomelogradouro
       from medicos as m right join logradouros as lm on m.logradouromoradiaid=lm.idlogradouro;
-- --------------
-- junção completa (aberta pela esquerda UNIDA com a aberta pela direita)
-- Em AR => Sintaxe: Tab1 ][Condição de Ligação][ Tab2
-- Note: Os colchetes 'externos' estão voltados para fora, desta forma indicando a junção completa.
--       Todas as linhas de Tab1 E Tab2 serão exibidas na execução do comando.
-- Em SQL (ANSI:92) - Operador JOIN com complemento FULL
--                    SELECT * FROM Tab1 FULL [OUTER] JOIN Tab2 ON Condição.
-- Exemplo:
select idmedico, txnomemedico, logradouromoradiaid,idlogradouro,txnomelogradouro
       from medicos as m full join logradouros as lm on m.logradouromoradiaid=lm.idlogradouro;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Agora, estudam-se os comandos de atualização de linhas nas tabelas. INSERT, UPDATE e DELETE formam os comandos de atualização.
-- Os comandos de atualização não tem uma sintaxe apresentada na AR.
-- --------------
-- INSERT
-- Em SQL: INSERT INTO Tabela [(Lista de campos que recebem valores)]
--                VALUES (lista de valores atribuídos aos campos)[,
--                       (),...,()];
-- Nota: 
-- Exemplos:
insert into funcoes (idfuncao,txnomefuncao,dtcadfuncao)
       values ('13','Designer Gráfico','2010-03-20'); 
insert into funcoes 
       values ('13','Designer Gráfico','txt','5','2010-03-20'),
              ('14','Teste Incluso',   'txt','5','2010-03-20'),
              ('15','Teste Incluso',   'txt','2','2010-03-20'); 
-- Nota: ao executar o comando acima vemos um erro por duplicação do valor da PK (13). Executamos o comando com '16' no lugar de '13'
-- --------------
-- UPDATE
-- Em SQL:
-- UPDATE tabela SET <atribuições> [WHERE <condição>];
-- As Atribuições são sempre feitas com a forma: CAMPO = VALOR
-- As condições sempre envolvem campo comparados com valor na forma CAMPO operação VALOR
-- exemplos
update funcoes
       SET txnomefuncao = 'Design Gráfico',
	       dtcadfuncao = '2022-08-10'
       WHERE idfuncao = 14 or idfuncao = '15';
-- ou
update funcoes
       SET txnomefuncao = 'Design Gráfico',
	       dtcadfuncao = '2022-08-10'
       WHERE idfuncao IN (14,'15');
-- Nota: o operador de comparação IN faz a substituição do operador lógico OR.
-- --------------
-- DELETE
-- Em SQL: DELETE FROM tabela [WHERE <condição>];
-- Nota: Cuidado... Se a condição NÃO for especificada TODAS as linhas serão excluídas da tabela.
-- Exemplo:
DELETE FROM funcoes WHERE idfuncao between 13 and 16;
DELETE FROM funcoes WHERE idfuncao in (1,9,4,7);
-- ===================================================================================================================================
-- Em 2022-09-13 resolvemos perguntas nos formatos do TesteSQL.
-- Perguntas para praticar a especificação de AR e tradução para SQL.
-- ===================================================================================================================================
-- Quais são os nomes e sobrenomes dos funcionarios que passaram por consultas médicas na especialidade de 'Ortopedia'?
-- AR
-- Seleção.: A <- especialidadesmedicas[[txnomeespecialidade='Ortopedia']]
-- Junção..: B <- A [[ A.idespecialidade = m.especialidadeid ]] medicos -> m
-- Junção..: D <- B [[ b.idmedico = c.medicoid ]] consultas -> c
-- Junção..: E <- D [[ D.funcionarioid=F.idfuncionario ]] funcionarios -> F
-- Projeção: E [[ txprenomes, txsobrenome ]]
-- Em SQL
-- Seleção.: A <- especialidadesmedicas[[txnomeespecialidade='Ortopedia']]
create temporary table A as (select * from especialidadesmedicas where txnomeespecialidade='Ortopedia');
-- Junção..: B <- A [[ A.idespecialidade = m.especialidadeid ]] medicos -> m
create temporary table b as (select * from A inner join medicos as M on A.idespecialidade=M.especialidadeid);
-- Junção..: D <- B [[ b.idmedico = c.medicoid ]] consultas -> c
create temporary table D as (select * from B inner join consultas as C on B.idmedico = C.medicoid);
-- Junção..: E <- D [[ D.funcionarioid=F.idfuncionario ]] funcionarios -> F
create temporary table E as (select * from D inner join funcionarios as F on D.funcionarioid=F.idfuncionario);
-- Projeção: E [[ txprenomes, txsobrenome ]]
select txprenomes, txsobrenome from E;
drop table A,B,D,E;
-- ===================================================================================================================================
-- Quais são os títulos de livros que participam em bibliografias de TODAS as disciplinas?
-- Montar as estruturas compatíveis com a divisão (em geral com projeções)
-- Projeção..: P <- livros [[idlivro -> livroid]]
-- Projeção..: F <- disciplinas [[iddisciplina->disciplinaid]]
-- Projeção..: PF <- bibliografia[[livroid, disciplinaid]]
-- Executar a divisão
-- Prod.Cart.: S <- P X F
-- Subtração.: T <- S - PF
-- Projeção..: W <- T[[livroid]](distintos)
-- Subtração.: R <- P - W
-- completar a resposta (em geral com junção e projeção)
-- Junção....: K <- R[[R.livroid=l.idlivro]]livros->l
-- Projeção..: K[[txtitulolivro]]
-- Em SQL
-- Projeção..: P <- livros [[idlivro -> livroid]]
create temporary table P as (select idlivro as livroid from livros);
-- Projeção..: F <- disciplinas [[iddisciplina->disciplinaid]]
create temporary table F as (select iddisciplina as disciplinaid from disciplinas);
-- Projeção..: PF <- bibliografia[[livroid, disciplinaid]]
create temporary table PF as (select livroid, disciplinaid from bibliografia);
-- Prod.Cart.: S <- P X F
create temporary table S as (select * from P cross join F);
-- Subtração.: T <- S - PF
create temporary table T as (select * from S except (select * from PF));
-- Projeção..: W <- T[[livroid]](distintos)
create temporary table W as (select distinct livroid from T);
-- Subtração.: R <- P - W
create temporary table R as (select * from P except (select * from W));
-- Junção....: K <- R[[R.livroid=l.idlivro]]livros->l
create temporary table K as (select * from R inner join livros as l on R.livroid=l.idlivro);
-- Projeção..: K[[txtitulolivro]]
select txtitulolivro from K;
-- ===================================================================================================================================
-- Quantas notas fiscais de vendas foram emitidas pelo funcionário "JOHN SILVESTER GEYER" durante o segundo trimestre de 2012?
-- Seleção...: f <- funcionarios [[concat(txprenomes," ",txsobrenome)='JOHN SILVESTER GEYER']]
-- Seleção...: n <- nfvenda [[ dtvenda entre '2012-04-01' e '2012-06-30' ]]
-- Junção....: t <- f [[ f.idfuncionario=n.funcionarioid ]] n
-- Projeção..: t [[ conta(idnunfvenda) ]] {agrupados por idfuncionario } {ordenados por idfuncionario }
create temporary table f as (select * from funcionarios where CONCAT(txprenomes,' ',txsobrenome)='JOHN SILVESTER GEYER');
create temporary table n as (select * from nfvendas where dtvenda between '2012-04-01' and '2012-06-30');
create temporary table t as (select * from f inner join n on f.idfuncionario=n.funcionarioid);
select COUNT(idnunfvenda) from t group by idfuncionario order by idfuncionario;
drop table f,n,t;
-- ===================================================================================================================================
-- ===================================================================================================================================
-- Combinando comandos
-- Vamos reescrever os comandos apresentados nas respostas das questões para otimizar os comandos
-- (combinando comandos SQL).
-- Para efeitos de estudo, muda-se a ordem das perguntas, que será: 1ª, 3ª e 2ª.
-- ===================================================================================================================================
-- Questão: Quais são os nomes e sobrenomes dos funcionarios que passaram por consultas médicas na especialidade de 'Ortopedia'?
-- AR
-- Seleção.: A <- especialidadesmedicas[[txnomeespecialidade='Ortopedia']]
-- Junção..: B <- A [[ A.idespecialidade = m.especialidadeid ]] medicos -> m
-- Junção..: D <- B [[ b.idmedico = c.medicoid ]] consultas -> c
-- Junção..: E <- D [[ D.funcionarioid=F.idfuncionario ]] funcionarios -> F
-- Projeção: E [[ txprenomes, txsobrenome ]]
-- Em SQL
-- Seleção.: A <- especialidadesmedicas[[txnomeespecialidade='Ortopedia']]
create temporary table A as (select * from especialidadesmedicas where txnomeespecialidade='Ortopedia');
-- Junção..: B <- A [[ A.idespecialidade = m.especialidadeid ]] medicos -> m
create temporary table b as (select * from A inner join medicos as M on A.idespecialidade=M.especialidadeid);
-- Junção..: D <- B [[ b.idmedico = c.medicoid ]] consultas -> c
create temporary table D as (select * from B inner join consultas as C on B.idmedico = C.medicoid);
-- Junção..: E <- D [[ D.funcionarioid=F.idfuncionario ]] funcionarios -> F
create temporary table E as (select * from D inner join funcionarios as F on D.funcionarioid=F.idfuncionario);
-- Projeção: E [[ txprenomes, txsobrenome ]]
select txprenomes, txsobrenome from E;
-- Em um só comando...
select txprenomes, txsobrenome
       from especialidadesmedicas as e inner join medicos as m on e.idespecialidade=m.especialidadeid
                                       inner join consultas as c on m.idmedico = c.medicoid
                                       inner join funcionarios as f on c.funcionarioid=f.idfuncionario
       where txnomeespecialidade='Ortopedia';
-- ===================================================================================================================================
-- Quantas notas fiscais de vendas foram emitidas pelo funcionário "JOHN SILVESTER GEYER" durante o segundo trimestre de 2012?
   select COUNT(idnunfvenda)
          from funcionarios as f inner join nfvendas as n on f.idfuncionario=n.funcionarioid
          where CONCAT(txprenomes,' ',txsobrenome)='JOHN SILVESTER GEYER' and
                dtvenda between '2012-04-01' and '2012-06-30'
          group by idfuncionario order by idfuncionario;
-- Cuidado! Alguns podem tentar pregar uma regra: Para combinar comandos sempre devemos fazer as junções,
-- depois as seleções; depois as ordenações e agrupamentos para finalmente fazermos a projeção.
-- Mas nem sempre isso acaba sendo válido.
-- ===================================================================================================================================
-- Questão: Quais são os títulos de livros que participam em bibliografias de TODAS as disciplinas?
-- As Estruturas das tabelas bibliografia, disciplinas e professores é apresentada a seguir:
bibliografia
+--------------+------------+-------+------+---------------+-----------------+
|idbibliografia|disciplinaid|livroid|aotipo|dtadocaodolivro|dtcadbibliografia|

disciplinas
+------------+-------+----------------+--------+-------+-------------------+---------------+
|iddisciplina|cursoid|txnomedisciplina|txementa|qthoras|txcriterioavaliacao|dtcaddisciplina|

professores
+-----------+---------------+------------+-------------+-----+------------+--------------+
|idprofessor|txnomeprofessor|logradouroid|txcomplemento|nucep|dtnascimento|dtcadprofessor|
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Com base nestas estruturas podemos responder a pergunta apresentada com a seguinte sequencia de comandos:
-- Montar as estruturas compatíveis com a divisão (em geral com projeções)
-- Projeção..: P <- livros [[idlivro -> livroid]]
-- Projeção..: F <- disciplinas [[iddisciplina->disciplinaid]]
-- Projeção..: PF <- bibliografia[[livroid, disciplinaid]]
-- Executar a divisão
-- Prod.Cart.: S <- P X F
-- Subtração.: T <- S - PF
-- Projeção..: W <- T[[livroid]](distintos)
-- Subtração.: R <- P - W
-- completar a resposta (em geral com junção e projeção)
-- Junção....: K <- R[[R.livroid=l.idlivro]]livros->l
-- Projeção..: K[[txtitulolivro]]
-- Em SQL
-- Projeção..: P <- livros [[idlivro -> livroid]]
create temporary table P as (select idlivro as livroid from livros);
-- Projeção..: F <- disciplinas [[iddisciplina->disciplinaid]]
create temporary table F as (select iddisciplina as disciplinaid from disciplinas);
-- Projeção..: PF <- bibliografia[[livroid, disciplinaid]]
create temporary table PF as (select livroid, disciplinaid from bibliografia);
-- Prod.Cart.: S <- P X F
create temporary table S as (select * from P cross join F);
-- Subtração.: T <- S - PF
create temporary table T as (select * from S except (select * from PF));
-- Projeção..: W <- T[[livroid]](distintos)
create temporary table W as (select distinct livroid from T);
-- Subtração.: R <- P - W
create temporary table R as (select * from P except (select * from W));
-- Junção....: K <- R[[R.livroid=l.idlivro]]livros->l
create temporary table K as (select * from R inner join livros as l on R.livroid=l.idlivro);
-- Projeção..: K[[txtitulolivro]]
select txtitulolivro from K;
drop table pf,p,f,s,t,w,r,R1,k;

-- Em SQL, o comando equivalente aos 9 comandos apresentados no procedimento acima pode ser escrito assim:
select livros.idlivro, livros.txtitulolivro
       from livros
            inner join
            (select idlivro as livroid from livros
                    except (select distinct livroid 
                                   from (select * from (select idlivro as livroid from livros) as P cross join
                                        (select iddisciplina as disciplinaid from disciplinas) as F
                                         except (select livroid, disciplinaid from bibliografia)) as w)) as R
            on livros.idlivro=R.livroid;
-- A execução do comando acima não produz resultado porque na tabela bibliografia não existe nenhum livro que participe na
-- bibliografia de TODAS as disciplinas. Mais adiante mostra-se o comando que realizar o 'INSERT' em bibliografia com
-- livros que passam a participar em bibliografias de TODAS as disciplinas.
-- A seguir se apresenta um comando que 'aparentemente' deveria realizar a mesma operação que o comando anterior, entretanto
-- apresenta uma falha de construção que leva a um erro.
-- Para um exercício, analise o comando e indique o erro. Mantenha o comando abaixo e copie fazendo a alteração segundo
-- sua proposta de ajuste. TESTE seu comando.
select txtitulolivro
       from (select * from
                     (select idlivro as livroid from livros) as l
                      except
                (Select distinct livroid
               from (select idlivro as livroid from livros) as P cross join
                    (select iddisciplina as disciplinaid from disciplinas) as d
		             except (select livroid, disciplinaid from bibliografia)) as w)) as r
		inner join livros on r.livroid=livros.idlivro;
-- PRESERVE este comando e se for tentar alguma alteração copie e cole o comando no espaço abaixo.
-- =========== Espaço para copia e teste do comando acima.

-- =======================================================

-- Agora fazemos a geração de dados para que a execução da divisão tenha dados a serem apresentados
-- A tabela bibliografia pode ser consultada com o comando:
select * from bibliografia;
select * from livros;
select * from disciplinas;
-- Observe que a tabela 'bilbiografia' tem uma Chave Primária com incremento de 5 unidades.
-- Escreve-se, assim, um comando que gera uma massa de dados de livros em produto cartesiano com todas as disciplinas,
-- tomando-se alguns livros e projetando-se a contagem de linha multiplicada por 5 e somado ao último valor registrado para a
-- Chave Primária na tabela 'bibliografia'.
-- Este comando de 'produto cartesiano'+'seleção'+'projeção' gera os dados que podem ser inseridos em 'bibliografia'.
select (50+5*(ROW_NUMBER() OVER (ORDER BY livroid,disciplinaid))) AS idbibliografia,
       d.disciplinaid,livroid,
       CASE WHEN l.livroid=40 THEN 'B' ELSE 'C' END as aotipo,
	   '2022-09-10' as dtadocaodolivro,
	   '2022-09-15' as dtcadbibliografia
       from (select iddisciplina as disciplinaid from disciplinas) as d cross join (select idlivro as livroid from livros) as l
       where l.livroid in ('100','110','120');
-- Para efeito de estudo, note o uso da função ROW_NUMBER() OVER e o uso do Operador CASE...END 
-- Note que a saída deste comando pode ser direcionada para o comando 'INSERT' gerando linhas em 'bibliografia', na forma:
INSERT INTO bibliografia 
       (select (50+5*(ROW_NUMBER() OVER (ORDER BY livroid,disciplinaid))) AS idbibliografia,
               d.disciplinaid,livroid,
               CASE WHEN l.livroid=40 THEN 'B' ELSE 'C' END as aotipo,
               '2022-09-10' as dtadocaodolivro,
               '2022-09-15' as dtcadbibliografia
               from (select iddisciplina as disciplinaid from disciplinas) as d cross join (select idlivro as livroid from livros) as l
               where l.livroid in ('100','110','120'));
-- Agora é possivel testar o comando que executa a divisão e exibe os livros que aparecem nas bibliogafias de todas as disciplinas.

create temporary table P as (select idlivro as livroid from livros);
-- Projeção..: F <- disciplinas [[iddisciplina->disciplinaid]]
create temporary table F as (select iddisciplina as disciplinaid from disciplinas);
-- Projeção..: PF <- bibliografia[[livroid, disciplinaid]]
create temporary table PF as (select livroid, disciplinaid from bibliografia);
-- Prod.Cart.: S <- P X F
create temporary table S as (select * from P cross join F);
-- Subtração.: T <- S - PF
create temporary table T as (select * from S except (select * from PF));
-- Projeção..: W <- T[[livroid]](distintos)
create temporary table W as (select distinct livroid from T);
-- Subtração.: R <- P - W
create temporary table R as (select * from P except (select * from W));
-- Junção....: K <- R[[R.livroid=l.idlivro]]livros->l
create temporary table K as (select * from R inner join livros as l on R.livroid=l.idlivro);
-- Projeção..: K[[txtitulolivro]]
select txtitulolivro from K;
drop table P,F,PF,S,T,W,R,K;
-- o comando equivalente pode ser assim escrito
select txtitulolivro from
  (select idlivro as livroid from livros
          except ((select distinct livroid
                          from
                          (select * from (select * from (select idlivro as livroid from livros) as p 
	                                cross join (select iddisciplina as disciplinaid from disciplinas) as f) as S
                   except (select * from (select livroid, disciplinaid from bibliografia) as PF)) as T))) as R
   inner join livros as l on R.livroid=l.idlivro
-- O comando acima é a 'remontagem' do comando proposto pela Álgebra Relacional apresentada entre as linhas 294-311.
-- Contribuindo como exercício, localize o erro no comando apresentado nas linhas 329-337 e proponha uma solução.
-- ===================================================================================================================================
-- 2022-09-21
-- Nos estudos preparatórios da aula foi desenvolvido o seguinte comando SQL.
select MaioresProjetos.projetosuperiorid, TotSubProj, txnomeprojeto as txnomeprojsup
       from (select projetosuperiorid, count(*) as TotSubProj from projetos
                    group by projetosuperiorid
                    order by count(*) desc, projetosuperiorid limit 3) as MaioresProjetos
              inner join projetos
              on MaioresProjetos.projetosuperiorid=projetos.projetosuperiorid
       order by totsubproj desc, idprojeto;
-- Interprete este comando e escreva a pergunta que pode ser respondida com este comando.
-- Melhorei a projeção executada no final do comando e apresentei os dados um pouco mais trabalhados facilitando a interpretação.
-- ===================================================================================================================================
-- 2022-09-29
-- Existem pessoas que são motivadas pelo desafio. Perguntaram-me sobre tentativa de criar as 'tabelas' 'carros' e 'onibus' e sobre
-- o ERRO que ocorria. Expliquei que 'carros' e 'onibus' não são tabelas. São VISÕES de dados processados à partir da tabela veiculos.
-- Os primeiros SGBDs da década de 1980 já apresentavam este recurso: "ROTULAR" um processamento de dados que fosse usado frequentemente
-- permitindo que se operassem processos sobre estes 'Rótulos' tratando-os como 'tabelas'. Esse conceito se define como 'VISÃO'.
-- Bem, agora falta apresentar um comando demonstrando o uso de uma das visões da base de trabalho.
-- Analise o seguinte comando:
select f.idfuncionario,f.txprenomes,f.txsobrenome, f.funcaoid
                          from funcionarios as f
                               left join carros as c
					           on idfuncionario=funcionarioid 
						  where idcarro is null
-- O comando acima responde: Exiba os códigos, nomes, sobrenomes e códigos das funções dos funcionarios que NÃO POSSUEM carros.
-- A condição que pega os funcionarios SEM carros é o 'WHERE idcarro IS NULL' que mostra as linhas 'sem código do carro'.
-- Note que sobre todo o resultado ainda foi processado a operação de PROJEÇÃO.
-- Agora pode-se perguntar: Mas e SE eu quiser saber os dados dos funcionarios que tem carros. Processa-se uma SUBTRAÇÃO
-- tirando de 'funcionarios' as linhas separadas. Deve-se notar que a SUBTRAÇÃO deve ter formados dois conjuntos união compatíveis.
-- Sendo assim, fez-se o comando:
select idfuncionario,txprenomes,txsobrenome, funcaoid from funcionarios
       except
      (select f.idfuncionario,f.txprenomes,f.txsobrenome, f.funcaoid
              from funcionarios as f
                   left join carros as c
				           on idfuncionario=funcionarioid 
						  where idcarro is null)
-- No comando anterior chegamos então na lista de nomes e códigos das funções dos funcionarios que tem carros.
-- Esta lista pode ser complementada com o nome da função da função exercída por estes funcionarios.
-- Finalmente escreve-se:
select idfuncionario,txprenomes,txsobrenome, funcaoid,txnomefuncao
       from funcoes
            inner join
           (select idfuncionario,txprenomes,txsobrenome, funcaoid from funcionarios
                   except
                  (select f.idfuncionario,f.txprenomes,f.txsobrenome, f.funcaoid
                          from funcionarios as f
                               left join carros as c
                               on idfuncionario=funcionarioid 
						              where idcarro is null)) as j
			on funcoes.idfuncao=j.funcaoid;
-- Note no comando que o 'conjunto' j foi nomeado para permitir a execução da junção de 'j' com funcoes.
-- Finalmente, este comando responde à seguinte pergunta:
-- "Exiba os códigos, nome, sobrenome e codigo da função dos funcionarios que possuem carros.
--  Exiba também nesta lista o nome da função que o funcionario exerce."
-- É importante citar um fato: Este comando pode ser mais simples.
-- Montou-se a lista dos funcionarios que NÃO  TEM carros para mostrar o processamento da visão carros como 'tabela' do banco e
-- o uso do operador LEFT JOIN permitindo acessar as linhas não ligadas pela comparação do funcionario 'dono de carros'.
-- Depois se processa a subtração de todos os funcionarios 'tirando' os que não tem carros (sobrando então quem tem carro).
-- No final se faz a junção com 'funcoes' para se ter o nome da função. Este processamento pode ser simplificado buscando-se os
-- dados dos 'donos de carros' diretamente da operação INNER JOIN ao invés de 'EXCEPT' de 'LEFT JOIN' com 'IS NULL'.
-- Assim pode-se escrever:
select distinct  idfuncionario,txprenomes,txsobrenome, funcaoid,txnomefuncao
                 from funcoes inner join (select f.idfuncionario,f.txprenomes,f.txsobrenome, f.funcaoid
                                                 from funcionarios as f
                                                      inner join carros as c
                                                      on idfuncionario=funcionarioid) as j
                              on funcoes.idfuncao=j.funcaoid;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Ainda no dia 2022-09-29 fui consultado para elaborar uma lista de todos os funcionarios que ganham mais do que o
-- valor mínimo do salário pago para os funcionarios de uma organização. Um programador usou o operador ANY no seguinte comando:
select idfuncionario from funcionarios where vlsalario > ANY (select vlsalario from funcionarios);
-- Este comando responde com TODOS os idfuncionario onde o vlsalario é maior que qualquer vlsalario.
-- Este comando gera uma lista SEM os funcionarios que ganham o vlsalario mais baixo.
-- Note que interessante o operador ANY (qualquer).
-- Este comando pode ser modificado para responder: Quais são os funcionários que ganham o piso de salários pagos na organização?
-- Para isso pode-se escrever:
select * from funcionarios where idfuncionario not in
      (select idfuncionario from funcionarios where vlsalario > ANY (select vlsalario from funcionarios));
-- Note o uso do operardor IN executando uma operação de 'subtração', ou 'selecionando' os funcionarios onde o
-- idfuncionario 'não esteja' dentro da sequencia de idfuncionario formada no comando anterior.
-- O Efeito do comando deve ser entendido desta forma uma seleção da negação de igualdade - o que resulta na subtração.
-- MAS note que usando NOT IN não existe análise de verificação se as estruturas das tabelas são UC.
-- Entretanto, existe um comando mais simples que pode responder da mesma forma, pesquise.
select * from funcionarios where vlsalario=(select min(vlsalario) from funcionarios);