Módulo 05

-- AULA DE JOINS

-- 1º NATURAL JOIN (SÓ SE PODE FAZER JUNÇÃO QUANDO OS DADOS DE AMBAS SÃO IGUAIS)
SELECT * FROM hr.locations;

SELECT * FROM hr.departments;

SELECT department_id, department_name, city
FROM hr.departments
NATURAL JOIN hr.locations
WHERE department_id IN (50, 10);

--2º CLAUSULA USING E EQUIJOINS
SELECT hr.employees.employee_id, hr.employees.last_name,
       hr.departments.location_id, department_id
FROM hr.employees
    JOIN hr.departments
USING (department_id);

SELECT l.city, d.department_name
FROM hr.locations l
JOIN hr.departments d
USING(location_id)
WHERE location_id = 1400;

SELECT e.employee_id, e.last_name,
       d.location_id, e.department_id,
       d.department_id, e.manager_id
FROM hr.employees e
JOIN hr.departments d
ON (d.department_id = e.department_id) AND e.manager_id = 149;

SELECT hr.employees.employee_id, hr.employees.last_name,
       hr.departments.department_id, hr.departments.location_id
FROM hr.employees
JOIN hr.departments
    ON hr.employees.department_id = hr.departments.department_id;

--3º CLAUSULA ON SELF-JOIN (RELAÇÃO ENTRE DADOS DA MESMA TABELA AUTO JOIN)
SELECT f.last_name Funcionário, g.last_name Gerente
FROM hr.employees f
JOIN hr.employees g
ON (g.employee_id = f.manager_id);

SELECT employee_id, last_name, city, department_name
FROM hr.employees e
JOIN hr.departments d
ON d.department_id = e.department_id
JOIN hr.locations l
ON d.location_id = l.location_id;

--4º RIGHT OUTER JOIN, LEFT OUTER JOIN E FULL OUTER JOIN

SELECT e.last_name, e.salary, j.job_title
    FROM hr.employees e
    JOIN hr.jobs j
    ON e.salary
BETWEEN j.min_salary AND j.max_salary;

SELECT * FROM hr.departments
WHERE department_id = 180;

SELECT e.last_name, e.department_id, d.department_name
    FROM hr.employees e FULL OUTER JOIN hr.departments d
    ON (e.department_id = d.department_id);

--5º CROSS JOIN (produto cartesiano)

SELECT e.last_name, e.salary, j.job_title
    FROM hr.employees e
    JOIN hr.jobs j
    ON e.salary
BETWEEN j.min_salary AND j.max_salary;

SELECT last_name, department_name
FROM hr.employees
CROSS JOIN hr.departments;

-- Exercicios Módulo

/* Crie uma consulta para o departamento de recursos humanos a fim de gerar os endereços de todos os departamentos. Use as tabelas LOCATIONS e COUNTRIES.
Mostre o ID do local, o endereço, a cidade, o estado e o país na saída. Use NATURAL JOIN para gerar os resultados.*/

SELECT location_id,
       street_address,
       city,
       state_province,
       country_name
FROM hr.locations
NATURAL JOIN hr.countries;

SELECT * FROM hr.locations;
SELECT * FROM hr.countries;

/* O departamento de recursos humanos precisa de um relatório de todos os funcionários. Crie uma consulta para exibir o sobrenome,
o número do departamento e o nome do departamento de todos os funcionários.*/

SELECT e.last_name, d.department_id, d.department_name
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
ORDER BY last_name ASC;

SELECT * FROM hr.departments;
SELECT * FROM hr.employees;

/*O departamento de recursos humanos precisa de um relatório dos funcionários em Toronto.
Exiba o sobrenome, o cargo, o número do departamento e o nome do departamento de todos os funcionários que trabalham em Toronto. */

SELECT last_name, --employees
       job_id, --jobs
       e.department_id, --department
       department_name,
       city
FROM hr.employees e
JOIN hr.departments d
ON e.department_id = d.department_id
JOIN hr.locations l
ON d.location_id = l.location_id AND l.city = 'Toronto';

/*Crie um relatório para exibir o sobrenome e o número dos funcionários, bem como o sobrenome e o número dos respectivos gerentes.
Atribua às colunas os labels Employee, Emp#, Manager e Mgr#, respectivamente.*/

SELECT e.last_name Emp#,
       e.employee_id Emp#,
       m.last_name Mgr#,
       e.manager_id Mgr#
FROM hr.employees e
JOIN hr.employees m
ON m.employee_id = e.manager_id;

/*Crie um relatório para o departamento de recursos humanos que exiba os sobrenomes e os números de departamento dos funcionários,
bem como todos os funcionários que trabalham no mesmo departamento como um funcionário específico. */

SELECT last_name, department_id, department_name
FROM hr.employees e FULL OUTER JOIN hr.departaments d
ON e.department_id = d.department_id;

/*O departamento de recursos humanos precisa de um relatório sobre níveis de cargos e salários. Para se familiarizar com a tabela JOBS,
primeiro mostre a estrutura dessa tabela. Em seguida, crie uma consulta que exiba o nome, o cargo, o nome do departamento,
o salário e o nível de todos os funcionários.*/


SELECT e.last_name,
       j.job_id,
       d.department_name,
       salary,
       job_title
FROM hr.employees e
JOIN hr.jobs j
ON e.job_id = j.job_id
JOIN hr.departments d
ON e.department_id = d.department_id;

SELECT * FROM hr.employees;
SELECT * FROM hr.departments;
SELECT * FROM hr.jobs;

/* Crie uma query para obter os nomes e as datas de admissão de todos os funcionários admitidos antes dos respectivos gerentes,
além dos nomes e das datas de admissão desses gerentes.*/

SELECT e.last_name "Nome Funcinário",
       e.hire_date "Data Admissão Funcionário",
       g.last_name "Nome Gerente",
       g.hire_date "Data Admissão Gerente"
FROM hr.employees e
JOIN hr.employees g
ON g.employee_id = e.manager_id
WHERE e.hire_date < g.hire_date;