RespLista 6 3_6 4

Prévia do material em texto

Programação de Banco de Dados com SQL 6-3: Junções Internas versus Externas Atividades Práticas
Objetivos
· Comparar e contrastar uma junção interna e uma junção externa
· Criar e executar uma consulta para usar uma junção externa esquerda
· Criar e executar uma consulta para usar uma junção externa direita
· Criar e executar uma consulta para usar uma junção externa completa
Vocabulário
Identifique a palavra do vocabulário correspondente a cada definição a seguir.
	FULL OUTER JOIN
	Executa uma junção em duas tabelas, recupera todas as linhas da tabela Left, mesmo que não haja correspondência na tabela Right. Também recupera todas as linhas da tabela Right, mesmo que não haja correspondência na tabela Left.
	OUTER JOIN
	Uma junção que retorna tanto as linhas não correspondentes como as correspondentes.
	LEFT OUTER JOIN
	Executa uma junção em duas tabelas, recupera todas as linhas da tabela Left, mesmo que não haja correspondência na tabela Right.
	RIGHT OUTER JOIN
	Executa uma junção em duas tabelas, recupera todas as linhas da tabela Right, mesmo que não haja correspondência na tabela Left.
	INNER JOIN
	Uma junção de duas ou mais tabelas que retorna somente as linhas correspondentes.
Tente/solucione
Use o banco de dados Oracle para os problemas de 1 a 7.
1. Retorne o nome, o sobrenome e o nome do departamento de todos os funcionários, incluindo aqueles não designados a um departamento.
R:
	SELECT e.first_name, e.last_name, d.department_name
	FROM employees e
	LEFT OUTER JOIN departments d
	ON e.department_id = d.department_id;
	
2. Retorne o nome, o sobrenome e o nome do departamento de todos os funcionários, incluindo os departamentos que não têm um funcionário designado.
R:
	SELECT e.first_name, e.last_name, d.department_name
	FROM employees e
	RIGHT OUTER JOIN departments d
ON e.department_id = d.department_id;
3. Retorne o nome, o sobrenome e o nome do departamento de todos os funcionários, incluindo os departamentos que não têm um funcionário designado e os funcionários não designados a um departamento.
R:
	SELECT e.first_name, e.last_name, d.department_name
	FROM employees e
	FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
4. Crie uma consulta do banco de dados da DJs on Demand para retornar o nome, o sobrenome, a data e a descrição do evento realizado pelo cliente. Inclua todos os clientes mesmo os que não têm um evento programado.
R:
	SELECT c.first_name, c.last_name, e.event_date, e.description
	FROM d_clients c
	LEFT OUTER JOIN d_events e
ON c.client_number = e.client_number;
5. Usando o banco de dados da Global Fast Foods, exiba a descrição e a data de designação dos turnos, mesmo que não haja uma data designada para cada descrição de turno.
R:
	SELECT s.description, a.shift_assign_date
	FROM f_shifts s
	LEFT OUTER JOIN f_shift_assignments a
ON s.code = a.code;
Programação de Banco de Dados com SQL 6-4: Autojunções e Consultas Hierárquicas Atividades Práticas
Objetivos
· Criar e executar uma instrução SELECT para unir uma tabela com ela mesma usando uma autojunção
· Interpretar o conceito de consulta hierárquica
· Criar um relatório estruturado em árvore
· Formatar dados hierárquicos
· Excluir ramificações da estrutura em árvore
Vocabulário
Identifique a palavra do vocabulário correspondente a cada definição a seguir.
	SELF-JOIN
	Une uma tabela com ela mesma
	Consultas Hierárquicas
	Recupera os dados com base em um relacionamento hierárquico natural entre as linhas de uma tabela
	LEVELS
	Determina o número de etapas a partir da linha inicial que devem ser retornadas por uma consulta hierárquica
	START WITH
	Identifica a linha inicial de uma consulta hierárquica
	CONNECT BY PRIOR
	Especifica o relacionamento entre as linhas pai e as linhas filho de uma consulta hierárquica
Tente/solucione
Para cada problema, use o banco de dados Oracle.
1. Exiba o sobrenome e o número do funcionário com o sobrenome e o número do gerente. Identifique as colunas da seguinte maneira: Employee, Emp#, Manager e Mgr#, respectivamente.
R:
	SELECT worker.last_name AS "Employee", worker.employee_id AS "Emp#", manager.last_name AS "Manager", worker.manager_id AS "Mgr#"
	FROM employees worker JOIN employees manager
ON (worker.manager_id= manager.employee_id);
2. Modifique a pergunta 1 para exibir todos os funcionários e os respectivos gerentes, mesmo que o funcionário não tenha um gerente. Ordene a lista alfabeticamente pelo sobrenome do funcionário
R:
	SELECT worker.last_name AS "Employee", worker.employee_id AS "Emp#", manager.last_name AS "Manager", worker.manager_id AS "Mgr#"
	FROM employees worker LEFT OUTER JOIN employees manager
	ON (worker.manager_id= manager.employee_id)
ORDER BY worker.last_name ASC;
3. Exiba os nomes e as datas de contratação de todos os funcionários que foram contratados antes de seus gerentes, além dos nomes e das datas de contratação dos gerentes. Identifique as colunas como Employee, Emp Hired, Manager e Mgr Hired, respectivamente.
R:
SELECT worker.first_name AS "Employee", worker.hire_date AS "Emp Hired", manager.first_name AS "Manager", manager.hire_date AS "Mgr Hired"
FROM employees worker JOIN employees manager
ON (worker.manager_id = manager.employee_id)
WHERE worker.hire_date < manager.hire_date;
4. Crie um relatório que mostre a hierarquia do departamento de Lex De Haans. Inclua o sobrenome, o salário e o id do departamento no relatório.
R:
	SELECT LPAD(last_name, length(last_name) + (LEVEL * 2) - 2, '_') AS SOBRENOME, salary AS SALARIO, department_id AS ID_DEPARTAMENTO
	FROM employees
	START WITH last_name = 'De Haan'
CONNECT BY PRIOR employee_id = manager_id;
5. O que há de errado na instrução a seguir?
SELECT last_name, department_id, salary FROM	employees
START WITH last_name = 'King'
CONNECT BY PRIOR manager_id = employee_id;
R:
Se a intenção é mostrar a quem King é subordinado, não há nada de errado com a instrução. Ela retorna apenas uma linha com o nome de King, pois ele não é subordinado de ninguém. Caso o desejado seja mostrar quem é subordinado a King, então deve-se trocar a palavra PRIOR de lugar e coloca-la após a vírgula.
6. Crie um relatório que mostre o organograma de toda a tabela employee. No relatório, cada nível deverá mostrar cada funcionário com 2 espaços recuados. Como o Oracle Application Express não exibe os espaços antes da coluna, use o sinal - (menos).
R:
	SELECT LPAD(last_name, length(last_name) + (LEVEL * 2) - 2, '--') AS ORG_CHART
	FROM employees
	START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;
7. Modifique o relatório da pergunta 6 para excluir De Haan e todas as pessoas que trabalham para ele.
R:
	SELECT LPAD(last_name, length(last_name) + (LEVEL * 2) - 2, '--') AS ORG_CHART
	FROM employees
	START WITH last_name = 'King'
	CONNECT BY PRIOR employee_id = manager_id
AND last_name != 'De Haan';