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

--Lesson 6
--SubQuery (PAIRWISE COMPARISON SUBQUERY)
--comparação com duas colunas

Select last_name, manager_id, department_id
from employees
where first_name = 'John';

Select first_name, last_name, manager_id, department_id
from employees
where manager_id = 108 and department_id = 100;

Select first_name, last_name, manager_id, department_id
from employees
where (manager_id = 108 and department_id = 100) or
      (manager_id = 123 and department_id = 50);

Select first_name, last_name, manager_id, department_id
from employees
where manager_id in
                (select manager_id from employees where first_name = 'John')
and department_id in
                (select department_id from employees where first_name = 'John')
and first_name <> 'John';

--Com dois parametros
Select first_name, last_name, manager_id, department_id
from employees
where (manager_id,department_id) in
                (select manager_id, department_id from employees where first_name = 'John');

Select *
from departments
where location_id = 1800;

Select employee_id, first_name,
case when department_id = 20 then 'canada' else 'usa' end
from employees;

Select employee_id, first_name,
case when department_id =
                  (select department_id from departments where location_id = 1800)
then 'canada' else 'usa' end
from employees;

--a mesma consulta sem subquery, melhor performance
Select employee_id, first_name,
case when location_id = 1800 then 'canada' else 'usa' end
from employees e
  left join departments d
            on(e.department_id = d.department_id);


--Correlated Subquery
Select first_name, department_id,
      ( select department_name
        from departments
        where department_id = e.department_id)
from employees e;


--Todos que ganha salario maior que a média
select avg(salary), department_id from employees group by department_id;

select first_name, salary,(select avg(salary) from employees
                where department_id = e.department_id)
from employees e
where salary > (select avg(salary) from employees
                where department_id = e.department_id);

---WITH CLAUSE
--Cria-se um View temporaria, apenas em tempo de execução
With
media_salario as
  (select department_id, round(avg(salary),2) as media
  from employees
  group by department_id)
select first_name, e.department_id, salary, media 
from employees e inner join media_salario m
                    on(e.department_id = m.department_id)
where salary > media;

--EXISTS
--select normal
select employee_id, first_name, job_id
from employees e
where employee_id in (select manager_id from employees);

--usando EXISTS
select employee_id, first_name, job_id
from employees e
where exists (Select 'qualquer coisa'
              from employees
              where manager_id = e.employee_id);
             
--NOT EXISTS             
--select normal
select department_id, department_name
from departments
where department_id not in (select department_id
                            from employees
                            where department_id is not null  );
                           
--outro jeito
select d.department_id, department_name
from departments d
                left join employees e
                on (d.department_id = e.department_id)
where e.department_id is null;

--usando NOT EXISTS
select d.department_id, department_name
from departments d
where not exists  ( select 'qualquer coisa'
                    from employees e
                    where e.department_id = d.department_id);
                   


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