Subqueries, Operadores de Conjuntos SQL Oracle

SELECT last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees WHERE employee_id =141)
AND salary = (SELECT job_id FROM employees WHERE employee_id =143)

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

SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary) FROM employees WHERE department_id = 50)

Errado porque o group by retorna múltiplas linhas
SELECT employee_id, last_name
FROM employees
WHERE salary = (SELECT MIN(salary) FROM employees GROUP BY department_id)
ORA-01427: single-row subquery returns more thans one row.

Retornar nulo
SELECT last_name, job_id
FROM employees WHERE job_id = (SELECT job_id FROM employees WHERE last_name = ‘Haas’)

Múltiplas Linhas

IN igual a qualquer um dos membros da lista

ANY ou SOME compara valor com dada valor retornado na subquery
< ANY significa menor que o máximo da lista
> ANU significa maqior que o minimo da lsita
= ANY é equivalente ao IN

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY (SELECT salary FROM employees WHERE job_id = 'IT_PROG')
AND job_id <> ‘IT_PROG’

ALL
Compara todos o valor da lista retornada pela query
< ALL significa menor que o minimo da lista
> ALL significa que o máximo da lista

O operador NOT pode ser usado com os operadores IN, ANY e ALL

Valores NULL em uma subquery

NÃO CONSIDERA OS NULLOS
SELECT emp.last_name
FROM employees emp WHERE emp.employee_id NOT IN (SELECT mgr.manager_id FROM employees mgr)

CONSIDERA OS NULLOS
SELECT emp.last_name
FROM employees emp WHERE emp.employee_id IN (SELECT mgr.manager_id FROM employees mgr)

Exibir o sobrenome e a data de cotnratação de todos os empregados do mesmo departamento do empregado fornecido sem inclui-lo no resultado.
SELECT emp.last_name, emp.HIRE_DATE
FROM employees emp WHERE emp.DEPARTMENT_ID IN (SELECT department_id FROM employees WHERE last_name = ‘King’)
AND emp.last_name <> ‘King’

Ou pelo código do empregado.
SELECT emp.last_name, emp.HIRE_DATE
FROM employees emp WHERE emp.DEPARTMENT_ID IN (SELECT DEPARTMENT_ID FROM employees WHERE employee_id = 100)
AND emp.employee_id <> 100

Relatório mostar o número, sobrenome e salário de todos os empregados que ganhem mais que o sálario médio. Ordenar em ordem crescente de salários.

SELECT emp.employee_id, emp.last_name, emp.salary
FROM employees emp WHERE emp.salary > (SELECT AVG(salary) FROM employees) ORDER BY emp.salary ASC

Crie um relatório que mostre o número e o sobrenome dos empregados que trabalhem em um departamento com qualquer empregado cujo sobrenome contenha a letra u.

SELECT emp.employee_id, emp.last_name
FROM employees emp WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE ‘%u%’)

Retirar o nome dos empregados com u no sobrenome:
SELECT emp.employee_id, emp.last_name, emp.department_id
FROM employees emp WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE ‘%u%’) AND emp.last_name NOT LIKE ‘%u%’

Mostar o siobrenome, número do departamento e o cargo de todos os emrpegados cuja localização do departamento é 1700

SELECT emp.last_name, emp.department_id, emp.job_id
FROM employees emp WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700)

Exiba o número, sobrenome e salário de todos os empregados que ganhem mais que a média salarial e que trabalhem em um departamento com qualquer emrpegado cujo sobrenome contenha um u.
SELECT emp.employee_id, emp.last_name, emp.salary, emp.department_id
FROM employees emp WHERE department_id IN (SELECT department_id FROM employees WHERE last_name LIKE ‘%u%’) AND emp.salary > (SELECT AVG(salary) FROM employees) ORDER BY emp.salary ASC

OPERADORES DE CONJUNTOS
As expressões na lista SELECT devem bater em quantidade e tipo de dados.
Parenteses podem ser utilizado para alterar a sequencia de execução.
O operador IN tem precedência maior que UNION
Valores NULL não são descartados
Operadores de conjunto podem aparecer em subqueries
A clausula ORDER BY pode aparecer somente no final do comando todo, aceitará o nome da coluna ou alias do primeiro SELECT ou a notação de sua posição.

UNION
retorna resultados de ambas as queries após eliminar as duplicações de registros
SELECT employee_id, job_id FROM wemployees
UNION SELECT employee_id, job_id FROM job_history

UNION ALL – sintaxe mais rápida
retorna todos os resultados incluindo as duplicações de registros.
SELECT employee_id, job_id FROM wemployees
UNION ALL SELECT employee_id, job_id FROM job_history

INTERSECT
retorna linhas que são comuns a ambas as queries
SELECT employee_id, job_id FROM employees
INTERSECT
SELECT employee_id, job_id FROM job_history

MINUS
retorna linhas da primeira query que não estão presentes na segunda query.
SELECT employee_id FROM employees
MINUS
SELECT employee_id FROM job_history
mostra os empregados q nunca trocaram de cargo.

Igualando as instruções SELECT do Operador, para resolver o problema de manter a quantidade de colunas na sintaxe de UNION
SELECT department_id, TO_NUMBER(null) location, hire_date FROM employees
UNION SELECT department_id, location_id, TO_DATE(null) FROM departments

Usando o 0 (zero) como terceiro parametro.
SELECT employee_id, job_id, salary FROM employees
UNION SELECT employee_id, job_id, 0 FROM job_history

Listagem dos IDs dos departamentos que não possuem o cargo ST_CLERK.
SELECT department_id FROM departments
MINUS
SELECT department_id FROM employees WHERE
job_id = ‘ST_CLERK’

Listagem dos países que não possuem departamentos localizados neles, mostrar o ID e o nome dos países resultantes, utilizar operadores de conjunto para criar a listage.
SELECT country_id, contry_name FROM countries
MINUS
SELECT country_id, country_name FROM countries
NATURALJOIN locations
NATURALJOIN departments

Listagem dos cargos para os departamentos 10, 50 e 20, ordenando os resultados nessa exata ordem de departamentos, mostre o cargo e o ID do departamento. Utilize operadores de conjunto para criar listagem.

SELECT job_id, department_id, ‘x’ dummy FROM employees WHERE department_id = 10
UNION
SELECT job_id, department_id, ‘y’ dummy FROM employees WHERE department_id = 50
UNIO
SELECT job_id, department_id, ‘z’ dummy FROM employees WHERE department_id=20