Exercicio8 SQL Respostas

Prévia do material em texto

Universidade Estadual do Centro-Oeste - UNICENTRO
Departamento de Ciência da Computação - DECOMP
Banco de Dados II – 2018
Exercício 8
Objetivo: Consultar os dados presentes na base de dados do Sistema de Vendas, utilizado diferentes tipos de junções. 
OUTROS TIPOS DE JUNÇÃO DE TABELAS
JUNÇÃO INTERNA – [INNER] JOIN
A junção interna é a mais simples e a mais utilizada, retornando somente o conjunto de registros que possuem correspondência entre as tabelas que participam da junção (Figura 1).
SELECT *
FROM tabelaA A JOIN tabelaB B ON A.id=B.id
Figura 1 – Diagrama de Venn de Inner Join [1]
JUNÇÃO EXTERNA – [OUTER] JOIN
É utilizada quando a consulta deve apresentar valores de uma das tabelas mesmo que não exista uma ligação correspondente entre os campos usados na junção. Os atributos da linha “não existente” são preenchidos com nulos.
Variantes: 
LEFT
RIGHT
FULL
O LEFT OUTER JOIN retorna todas as linhas combinadas que encontram correspondência pela condição ON, mais uma cópia de cada linha da tabela à esquerda para a qual não há uma linha da tabela à direita que tenha passado pela condição ON. Esta linha da tabela à esquerda é estendida por toda a largura da tabela combinada inserindo-se nulos para as colunas da tabela à direita. Observe que somente as condições ON do próprio JOIN são consideradas na hora de decidir quais linhas possuem correspondência. As condições WHERE externas são aplicadas depois.
De forma inversa, o RIGHT OUTER JOIN retorna todas as linhas da junção, mais uma linha para cada linha da tabela à direita sem correspondência (estendida com nulos na tabela à esquerda). Isto é apenas uma conveniência da notação, porque poderia ser convertida em um LEFT OUTER JOIN trocando-se a tabela à direita pela tabela à esquerda.
O FULL OUTER JOIN retorna todas as linhas da junção, mais uma linha para cada linha da tabela à esquerda sem correspondência (estendida com nulos na tabela à direita), mais uma linha da tabela à direita sem correspondência (estendida com nulos na tabela à esquerda). 
LEFT [OUTER] JOIN
Esta junção externa à esquerda retorna o conjunto de todos os registros que possuem correspondência entre as tabelas que participam da junção, assim como os demais registros da tabela à esquerda da junção (Figura 2).
SELECT *
FROM tabelaA A LEFT JOIN tabelaB B ON A.id=B.id
Figura 2 – Diagrama de Venn de Left Outer Join [1]
RIGHT [OUTER] JOIN
Esta junção externa à direita retorna o conjunto de todos os registros que possuem correspondência entre as tabelas que participam da junção, assim como os demais registros da tabela à direita da junção (Figura 3).
SELECT *
FROM tabelaA A RIGHT JOIN tabelaB B ON A.id=B.id
Figura 3 – Diagrama de Venn de Right Outer Join [1]
FULL [OUTER] JOIN: 
A junção externa total retorna o conjunto de todos os registros que possuem correspondência entre as tabelas que participam da junção, assim como os demais registros das tabelas à direita e à esquerda da junção (Figura 4).
SELECT *
FROM tabelaA A FULL JOIN tabelaB B ON A.id=B.id
Figura 4 – Diagrama de Venn de Full Outer Join [1]
Referências:
[1] Dan Scientia. Ciências Matemáticas, Físicas e Computacionais. Teoria dos Cojuntos em Junções SQL. 2012. Obtido em: http://dan-scientia.blogspot.com.br/2012/10/teoria-dos-conjuntos-em-juncoes-sql.html Acesso em: 25/08/2018.
Exemplo: Listar os departamentos e seus vendedores.
Exemplo: Listar os departamentos e seus vendedores, apresentando também os departamentos que não possuem vendedores – USANDO LEFT JOIN
Exemplo: Listar os departamentos e seus vendedores, apresentando também os departamentos que não possuem vendedores – USANDO RIGHT JOIN
EXERCÍCIOS:
Desenvolva consultas usando junção para:
Apresentar todos os produtos, e quando existirem, as vendas relativas ao produto. Campos: identificador do produto, nome do produto, identificador da venda. Apresente os resultados em ordenados pelo nome do produto.
SELECT p.produtoId, p.nome, iv.vendaId
FROM itemVenda iv RIGHT JOIN produto p ON 
iv.produtoId=p.produtoId
ORDER BY 2
Apresentar todos os fornecedores cadastrados, e quando existirem, os seus pedidos. Campos: nome fantasia do fornecedor, identificador do pedido, data de entrega do pedido. USAR RIGHT JOIN 
SELECT f.nomeFantasia, p.pedidoId, p.dataEntrega
FROM pedido p RIGHT JOIN fornecedor f ON 
f.fornecedorId=p.fornecedorId
Apresentar todas as classes, e quando existirem os produtos da classe. Campos: todos os campos da classe, nome do produto, preço de venda. Os resultados devem ser ordenados por nome da classe. 
SELECT c.*, p.nome, p.precoVenda
FROM produto p RIGHT JOIN classe c ON 
c.classeId=p.classeId
Order by 3
Apresentar todos os clientes e suas respectivas compras, apresentar também os clientes que não fizeram compras. Campos: identificador do cliente, nome do cliente se o mesmo for pessoa física ou o nome fantasia se o mesmo for pessoa jurídica (EM UM CAMPO ÚNICO USANDO CASE), identificador da venda, valor total da venda. Ordenar os resultados por identificador.
SELECT c.clienteId, CASE WHEN cf.nome IS NULL 
THEN cj.nomeFantasia ELSE cf.nome END AS CLIENTE,
v.vendaId, v.valorTotal
FROM cliente c LEFT JOIN clienteFisico cf ON 
c.clienteId=cf.clienteId LEFT JOIN clienteJuridico cj
ON c.clienteId=cj.clienteId LEFT JOIN venda v ON
v.clienteId=c.clienteId
ORDER BY 1
Apresentar todos os estados e, quando existirem suas respectivas cidades. Campos todos de estado e cidade. USAR RIGHT JOIN
Apresentar todos os produtos e seus respectivos lotes quando existirem. Campos: identificador e nome do produto, identificador do lote, data de validade do lote, quantidade vendida. USAR LEFT JOIN 
Apresentar todas as cidades, e quando existirem, os vendedores nela cadastrados. Campos: identificador da cidade, nome, nome do estado, nome do vendedor, logradouro, numero. Apresentar o resultado ordenado por nome do estado, nome da cidade, nome do vendedor.
 PAGE 2