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

--Lesson 7
--Subquery
--Linha simples

SELECT employee_id,last_name, salary
FROM   employees
WHERE  salary >=
               (SELECT salary
                FROM   employees
                WHERE  UPPER(last_name) = UPPER('Abel'))
Order By last_name;        




SELECT first_name, job_id, salary
FROM   employees
WHERE  job_id = 
                (SELECT job_id
                 FROM   employees
                 WHERE  first_name = 'Christopher')
AND    salary >
                (SELECT salary
                 FROM   employees
                 WHERE  first_name = 'Christopher');

Select First_name, salary, job_id
From employees
where salary >=
                (SELECT AVG(salary)
                FROM EMPLOYEES
                WHERE Job_id =
                              (Select job_id
                                From Employees
                                Where First_name = 'Christopher'))
and Job_id = (Select job_id
              From Employees
              Where First_name = 'Christopher');


SELECT last_name, job_id, salary
FROM   employees
WHERE  salary =
                (SELECT MIN(salary)
                 FROM   employees);

select department_id, round(avg(salary))
from employees
group by department_id
having avg(salary) >= (select avg(salary) from employees where department_id = 50);


-- Multiplas linhas
--ANY , deve atender a qualquer um dos valores retornados
Select first_name, last_name, hire_date
From employees
where hire_date < ANY (Select hire_date From employees where last_name = 'King');


--ALL , deve atender a todos os valores retornados
Select first_name, last_name, hire_date
From employees
where hire_date < ALL (Select hire_date From employees where last_name = 'King');

--IN , retorna apenas os valores correspondentes
Select first_name, last_name, hire_date
From employees
where hire_date IN (Select hire_date From employees where last_name = 'King');


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