Curso Oracle 11g - Lesson 6 - Programação

--Lesson 6

--Natural Join

SELECT department_id, department_name,
       location_id, city
FROM   departments
NATURAL JOIN locations ;



--Join Using

SELECT employee_id, last_name,
       location_id, department_id
FROM   employees JOIN departments
USING (department_id) ;


-- Inner Join Or Join

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


Select e.last_name || ', ' || e.first_name as emp,
    b.last_name || ', ' || b.first_name as boss,
    department_name, city
FROM employees e
join departments d
on (e.department_id = d.department_id)
join locations l
on (d.location_id = l.location_id)
join employees b
on (e.manager_id = b.employee_id);

Select e.last_name || ', ' || e.first_name as emp,
    department_name
FROM employees e
join departments d
on e.department_id = d.department_id
and e.manager_id = '149';

--Self Join

SELECT worker.last_name emp, manager.last_name mgr
FROM   employees worker JOIN employees manager
ON    (worker.manager_id = manager.employee_id);


--Nonequijoin

SELECT e.last_name, e.salary, j.grade_level
FROM   employees e
JOIN job_grades j
ON     e.salary
       BETWEEN j.lowest_sal AND j.highest_sal;



--Left Outer Join
SELECT e.last_name, nvl(e.department_id,'000'), nvl(d.department_name,'sem departamento')
FROM   employees e
LEFT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;

--Right Outer Join
SELECT nvl(e.last_name,'sem funcionario'), e.department_id, d.department_name
FROM   employees e
RIGHT OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;


--Right Outer Join
SELECT nvl(e.last_name, 'sem funcionario'), nvl(e.department_id,'000'), nvl(d.department_name, 'sem departamento')
FROM   employees e
FULL OUTER JOIN departments d
ON   (e.department_id = d.department_id) ;



--Cross Join

SELECT last_name, department_name
FROM   employees
CROSS JOIN departments ;

Select country_name, first_name, employee_id
From countries , employees ;

Select country_name, first_name, employee_id
From countries cross join employees ;

Fiquem a vontade para comentar e sugerir melhorias e/ou correção dos exemplos acima.