-- 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: TAB [[condição]]
-- Exemplo: funcionarios[[dtcontratacao entre '1980-01-01' e '1989-12-31']]
-- Em SQL ANSI:92
select * from funcionarios where dtcontratacao between '1980-01-01' and '1989-12-31';
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Projeção
-- EM AR = Sintaxe: TAB [[Lista de Campos]]
-- Exemplo: cidades [[txnomecidade, qtpopulacao]]
-- Em SQL:
select txnomecidade, qtpopulacao from cidades;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Produto Cartesiano
-- Em AR = Sintaxe: TAB1 X TAB2
-- Em SQL: ANSI:87 
select * from autores, cores;
--         ANSI:92
select * from autores CROSS JOIN cores;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Junção (Natural ou Fechada)
-- Em AR = Sintaxe:
-- Em SQL: ANSI:87
select * from cidades, estadosdauniao WHERE cidades.estadodopaisid=estadosdauniao.idestadouniao;
--         ANSI:92
select * from cidades INNER JOIN estadosdauniao ON cidades.estadodopaisid=estadosdauniao.idestadouniao;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- 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 idfuncionario, txprenomes, txsobrenome, dtcontratacao
       from funcionarios order by dtcontratacao ASC;
select idfuncionario, txprenomes, txsobrenome, dtcontratacao
       from funcionarios 
       where dtcontratacao <= '2004-12-31'
       order by dtcontratacao ASC;

select idfuncionario, txprenomes, txsobrenome, dtcontratacao
       from funcionarios 
       where dtcontratacao >= '2001-01-01'
       order by dtcontratacao ASC;
create temporary table F1 as (select idfuncionario, txprenomes, txsobrenome, dtcontratacao
                                     from funcionarios 
                                     where dtcontratacao <= '2004-12-31'
                                     order by dtcontratacao ASC);
create temporary table F2 as (select idfuncionario, txprenomes, txsobrenome, dtcontratacao
                                     from funcionarios 
                                     where dtcontratacao >= '2001-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;  -- 26 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);
-- Os dados podem ser ordenados 'completando' o comando com ORDER BY...
select * from f1 UNION (select * from f2) order by dtcontratacao ASC;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Subtração
-- Sintaxe: TAB1 - TAB2
-- Exemplo: F1 - F2
-- Em SQL:
select * from f1 MINUS | EXCEPT (select * from f2);
-- O MINUS é um operador ANSI:87 mas não reconhecido no PostgreSQL
-- Para subrtração usa-se o EXCEPT, na forma:
select * from f1 EXCEPT (select * from f2);
-- NOTE: o resultado (linhas) de
--       select * from f2 EXCEPT (select * from f1);
--       É DIFERENTE pois 'pega' as linhas de f2 que NÃO estão em f1
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Intersecção
-- Sintaxe: Tab1 ^ Tab2
-- Exemplo: f1 ^ f2	
-- Em SQL:
select * from f1 INTERSECT (select * from f2);
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Terminados os comandos de tabelas UC, voltamos para fazer comandos de JUNÇÃO ABERTA.
-- Operação: Junção Aberta pela DIREITA.
-- Sintaxe: Tab1 [[condição da junção][ Tab2
-- Em SQL
select * from medicos as m right join especialidadesmedicas as e
                           on m.especialidadeid = e.idespecialidade;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Junção aberta pela ESQUERDA
-- para fazer uma junção aberta pela esquerda vamos 'alterar' alguns dados em 'medicos' mudando o valor
-- do código da instituição de ensino. para isso vamos escrever o comando de 'UPDATE', na forma:
update medicos SET idinstituicao=1 where idmedico=2;
update medicos SET idinstituicao=3 where idmedico=7;
update medicos SET idinstituicao=4 where idmedico=16;
-- Agora é possível fazer uma junção aberta pela esquerda de medicos com instituicoesensino, na forma:
-- Sintaxe: Tab1 ][condição da junção]] Tab2
-- Em SQL
select * from medicos as m left join instituicoesensino as i on m.idinstituicao = i.idinstituicao;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Operação: Junção COMPLETA (Aberta pela Direita e pela Esquerda)
-- Sintaxe: Tab1 ][condição da junção][ Tab2
-- Em SQL
-- Para fazer uma junção COMPLETA podemos alterar valores em 'medicos' ANULANDO o valor de especialidadeid, usando o comando UPDATE na forma:
update medicos SET logradouromoradiaid=NULL where idmedico in (1,3);
-- podemos escrever uma junção completa, na forma:
select * from medicos as m full join logradouros as lm
         on m.logradouromoradiaid = lm.idlogradouro;
-- os operadores left, right e full aceitam um complemento 'outer' na forma:
-- 'left outer', 'right outer' e 'full outer'; apresentando o mesmo resultado.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Agora, os comandos de atualização: INSERT, DELETE e UPDATE; escritos somente SQL, uma vez que não existe representação AR.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Comando: Inclusão de Linhas: INSERT
-- Em SQL: INSERT INTO tabela [(lista de campos da tabela)] VALUES (lista de valores de cada campo)[,(),...,()];
-- Exemplo:
-- inserindo uma linha em autores com o preenchimento dos campos requeridos.
INSERT INTO autores (idautor,txnomeautor,nucep,dtnascimento,dtcadautor)
       VALUES ('30','Carlos Drumonnd de Andrade','26345672','1929-05-20','2022-08-31');
-- Note os valores de campos não infomados na Lista de Campos são assumidos como NULL
-- Inserindo linhas sem a consistência de Chave Primária e de Chave Estrangeira (Tabela não tem estas definições)
INSERT INTO autores 
       VALUES ('30','Carlos Drumonnd de Andrade','30','345 - casa 2','26345672','1929-05-20','2022-08-31');
-- O comando INSERT permite incluir duas ou mais linhas em um mesmo comando, na forma:
INSERT INTO autores 
       VALUES 
	   ('180','José Carlos Figueiredo Ferraz','40','1234 Ap. 234','26345672','1929-05-20','2022-08-31'),
	   ('190','José Carlos Figueiredo Ferraz','50','1345 - cj 39','26345672','1929-05-20','2022-08-31');
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Comando: Exclusão de Linhas: DELETE
-- Em SQL: DELETE FROM tabela [where condicao];
-- Exemplo:
DELETE FROM  autores  where idautor=30 and logradouroid IS NULL;
delete from autores where idautor in (180,190); 
-- Note: As condições são verificadas para autenticar a execução do comando.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Comando: Alteração de Linhas: UPDATE
-- Em SQL: UPDATE tabela SET Atribuções [WHERE condição];
-- Exemplo:
UPDATE autores SET logradouroid=40, txnomeautor='Carlos Drumond De Andrade' WHERE idautor=30;
-- -----------------------------------------------------------------------------------------------------------------------------------
-- Em SQL existe a possibilidade de combinar comandos (usando Sub-Queries), isso permitiu uma revisão da AR.
-- -----------------------------------------------------------------------------------------------------------------------------------
-- 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 ]]
-- SQL
create temporary table A AS (select * from especialidadesmedicas where txnomeespecialidade='Ortopedia');
create temporary table B AS (select * from A inner join medicos as m on A.idespecialidade = m.especialidadeid);
create temporary table D AS (select * from B inner join consultas as c on b.idmedico = c.medicoid);
create temporary table E AS (select * from D inner join funcionarios as F on D.funcionarioid=F.idfuncionario);
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?
-- Preparando as tabelas
-- Projeção..: PF <- bibliografia [[livroid->idlivro, disciplinaid->iddisciplina]]
-- Projeção..: P <-  livros [[idlivro]]
-- Projeção..: F < - disciplinas [[iddisciplina]]
-- Executando a Divisão
-- Prod.Cart.: S <- P X F
-- Subtração.: T <- S - PF
-- Projeção..: W <- T[[idlivro]]
-- Subtração.: R <- P - W
-- Complementando a resposta
-- Junção....: K <- R [[R.idlivro=l.idlivro]] livros->l
-- Projeção..: K[[txtitulolivro]]
-- SQL, agora escrevemos:
-- Projeção..: PF <- bibliografia [[livroid->idlivro, disciplinaid->iddisciplina]]
create temporary table PF as (select livroid as idlivro, disciplinaid as iddisciplina from bibliografia);
-- Projeção..: P <-  livros [[idlivro]]
create temporary table P as (select idlivro from livros);
-- Projeção..: F < - disciplinas [[iddisciplina]]
create temporary table F as (select iddisciplina from disciplinas);
-- 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[[idlivro]](distintos)
create temporary table W as (select distinct idlivro from T);
-- Subtração.: R <- P - W
create temporary table R as (select * from P except (select * from W));
-- Trocando o NOME DO CAMPO idlivro para livroid
-- Projeção..: R1 <- R [[idlivro -> livroid]]
create temporary table R1 as (select idlivro as livroid from R);
-- Junção....: K <- R1 [[R1.livroid=l.idlivro]] livros->l
create temporary table K as (select * from R1 inner join livros as l on R1.livroid=l.idlivro);
-- Projeção..: K[[txtitulolivro]]
select txtitulolivro from k;
drop table pf,p,f,s,t,w,r,R1,k;
-- ===================================================================================================================================
-- Quantas notas fiscais de vendas foram emitidas pelo funcionário "José Prado" durante o segundo trimestre de 2012?
-- ===================================================================================================================================
-- Segundo trimestre de 2012 está entre as datas: '2012-04-01' e '2012-06-30'
-- o nome do funcionario deve ser trocado para IRVING STERN pois este funcionario tem notas de vendas
-- emitidas no periodo referenciado.
-- Desse a pergunta será:
-- Quantas notas fiscais de vendas foram emitidas pelo funcionário "IRVING STERN" durante o segundo trimestre de 2012?
-- Seleção..: T1 <- funcionarios [[ CONCAT(txprenomes,' ',txsobrenome)='IRVING STERN' ]]
create temporary table T1 as (select * from funcionarios where CONCAT(txprenomes,' ',txsobrenome)='IRVING STERN');
-- Seleção..: T2 <- nfvendas [[  entre '2012-04-01' e '2012-06-30' ]]
create temporary table T2 as (select * from nfvendas where dtvenda between '2012-04-01' and '2012-06-30');
-- Junção...: T3 <- T1 [[ T1.idfuncionario=T2.funcionarioid  ]] T2
create temporary table T3 as (select * from T1 inner join T2 on T1.idfuncionario=T2.funcionarioid);
-- Projeção.: T3 [[ CONTA(idnunfvenda)->QtdNotas ]] {agrupando por idfuncionario} {ordenado por idfuncionario}
select COUNT(idnunfvenda) as QtdNotas from T3
-- Escrevendo os mesmos comandos em um só comando SQL para responder as perguntas.
-- Na ordem 3, 1 e 2.
-- ===================================================================================================================================
-- Quantas notas fiscais de vendas foram emitidas pelo funcionário "José Prado" durante o segundo trimestre de 2012?
select COUNT(idnunfvenda) as QtdNotas
        from funcionarios inner join nfvendas 
		on funcionarios.idfuncionario=nfvendas.funcionarioid
		where dtvenda between '2012-04-01' and '2012-06-30' and 
		      CONCAT(txprenomes,' ',txsobrenome)='IRVING STERN';
-- ===================================================================================================================================
-- Quais são os nomes e sobrenomes dos funcionarios que passaram por consultas médicas na especialidade de 'Ortopedia'?
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'
-- ===================================================================================================================================
-- Quais são os títulos de livros que participam em bibliografias de TODAS as disciplinas?
-- Preparando as tabelas
-- Projeção..: PF <- bibliografia [[livroid->idlivro, disciplinaid->iddisciplina]]
-- Projeção..: P <-  livros [[idlivro]]
-- Projeção..: F < - disciplinas [[iddisciplina]]
-- Executando a Divisão
-- Prod.Cart.: S <- P X F
-- Subtração.: T <- S - PF
-- Projeção..: W <- T[[idlivro]]
-- Subtração.: R <- P - W
-- Complementando a resposta
-- Junção....: K <- R [[R.idlivro=l.idlivro]] livros->l
-- Projeção..: K[[txtitulolivro]]
-- SQL, agora escrevemos:
-- Projeção..: PF <- bibliografia [[livroid->idlivro, disciplinaid->iddisciplina]]
create temporary table PF as (select livroid as idlivro, disciplinaid as iddisciplina from bibliografia);
-- Projeção..: P <-  livros [[idlivro]]
create temporary table P as (select idlivro from livros);
-- Projeção..: F < - disciplinas [[iddisciplina]]
create temporary table F as (select iddisciplina from disciplinas);
-- 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[[idlivro]](distintos)
create temporary table W as (select distinct idlivro from T);
-- Subtração.: R <- P - W
create temporary table R as (select * from P except (select * from W));
-- Trocando o NOME DO CAMPO idlivro para livroid
-- Projeção..: R1 <- R [[idlivro -> livroid]]
create temporary table R1 as (select idlivro as livroid from R);
-- Junção....: K <- R1 [[R1.livroid=l.idlivro]] livros->l
create temporary table K as (select * from R1 inner join livros as l on R1.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);