Usando uma subconsulta(subquery)

para solucionar um problema

Quem tem um salário maior que o salário de Abel?
Consulta principal:

Quais funcionários têm salário maior que Abel?

Subconsulta:

Qual é o salário de Abel?

Sintaxe de subconsulta
SELECT select_list
FROM table
WHERE expr operator
(SELECT select_list
FROM table);

◦ A subconsulta (consulta interna) é executada antes da consulta

principal (consulta externa).
◦ O resultado da subconsulta é usado pela consulta principal.
Usando uma subconsulta
SELECT last_name, salary
FROM employees
WHERE salary > 11000
(SELECT salary
FROM employees
WHERE last_name = 'Abel');
Diretrizes para usar subconsultas
◦ Insira as subconsultas entre parênteses.
◦ Insira subconsultas do lado direito da condição
de comparação por questão de legibilidade.
(Entretanto, a subconsulta pode ser exibida nos
dois lados do operador de comparação.)
◦ Use operadores de uma única linha com
subconsultas de uma única linha e operadores de
várias linhas com subconsultas de várias linhas.
Tipos de subconsultas
◦ Subconsulta de uma única linha
Consulta principal
retornos
Subconsulta ST_CLERK
◦ Subconsulta de várias linhas

Consulta principal
retornos ST_CLERK
Subconsulta
SA_MAN
Subconsultas de uma única linha
◦ Retorna somente uma linha
◦ Use operadores de comparação de uma única linha

Operator Significado
= Igual a
> Maior que
>= Maior que ou igual a
< Menor que
<= Menor que ou igual a
<> Diferente de
Executando as subconsultas de uma
única linha
SELECT last_name, job_id, salary
FROM employees
WHERE job_id = SA_REP
(SELECT job_id
FROM employees
WHERE last_name = 'Taylor')
AND salary > 8600
(SELECT salary
FROM employees
WHERE last_name = 'Taylor');
Usando funções de grupo em uma
subconsulta
SELECT last_name, job_id, salary
FROM employees
WHERE salary = 2500
(SELECT MIN(salary)
FROM employees);
Cláusula HAVING com subconsultas
◦ O Oracle executa as subconsultas primeiro.
◦ O Oracle retorna resultados para a cláusula HAVING da
consulta principal.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id 2500
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employees
WHERE department_id = 50);
O que está errado com esta
instrução?
SELECT employee_id, last_name
FROM employees
WHERE salary =
(SELECT MIN(salary)
FROM employees
GROUP BY department_id);

Operador de uma
única linha com
subconsulta de
várias linhas
Nenhuma linha retornada pela
consulta interna
SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE last_name = 'Haas');

A subconsulta não retorna linhas, porque não

existe um funcionário com o nome “Haas”.
Subconsultas de Várias Colunas

Consulta principal
WHERE (MANAGER_ID, DEPARTMENT_ID) IN

Subconsulta
100 90
102 60
124 50

Cada linha da consulta principal é comparada a

valores de uma subconsulta de várias linhas e várias
colunas.
Comparações de Coluna
As comparações de várias colunas envolvendo
subconsultas podem ser:
◦ Comparações em não-pares
◦ Comparações em pares
Subconsulta de Comparação em Pares
Exiba os detalhes dos funcionários gerenciados pelo
mesmo gerente e que trabalham no mesmo
departamento que os funcionários de nome “John”.
SELECT employee_id, manager_id, department_id
FROM empl_demo
WHERE (manager_id, department_id) IN
(SELECT manager_id, department_id
FROM empl_demo
WHERE first_name = 'John')
AND first_name <> 'John';
Subconsulta de Comparação em Não-
pares
Exiba os detalhes dos funcionários gerenciados pelo
mesmo gerente que os funcionários com o nome “John”
e que trabalham no mesmo departamento que os
funcionários de nome “John.”

SELECT employee_id, manager_id, department_id

FROM empl_demo
WHERE manager_id IN
(SELECT manager_id
FROM empl_demo
WHERE first_name = 'John')
AND department_id IN
(SELECT department_id
FROM empl_demo
WHERE first_name = 'John')
AND first_name <> 'John';
Expressões de Subconsulta Escalar
◦ Uma expressão de subconsulta escalar é uma
subconsulta que retorna exatamente um valor de
coluna para uma linha.
◦ As subconsultas escalares podem ser usadas:
 Na condição e parte da expressão de DECODE e CASE
 Em todas as cláusulas de SELECT, exceto GROUP BY
 Nas cláusulas SET e WHERE de uma instrução UPDATE
Subconsultas Escalares: Exemplos
◦ Subconsultas escalares em expressões CASE:
SELECT employee_id, last_name,
(CASE
20
WHEN department_id =
(SELECT department_id
FROM departments
WHERE location_id = 1800)
THEN 'Canada' ELSE 'USA' END) location
FROM employees;
◦ Subconsultas escalares na cláusula ORDER BY:
SELECT employee_id, last_name
FROM employees e
ORDER BY (SELECT department_name
FROM departments d
WHERE e.department_id = d.department_id);
Subconsultas Correlacionadas
As subconsultas correlacionadas são usadas para
processamento linha por linha. Cada subconsulta é
executada uma vez para cada linha da consulta externa.

GET
a linha candidata da consulta externa

EXECUTE
a consulta interna usando o valor da linha candidata

USE
valores da consulta interna para qualificar
ou desqualificar a linha candidata
Subconsultas Correlacionadas
A subconsulta se refere a uma coluna com base em uma tabela na
consulta pai.

SELECT column1, column2, ...

FROM table1 Outer_table
WHERE column1 operator
(SELECT column1, column2
FROM table2
WHERE expr1 =
Outer_table .expr2);
Usando Subconsultas Correlacionadas
Localize todos os funcionários que ganham acima do salário médio
em seu departamento.

SELECT last_name, salary, department_id

FROM employees outer_table
WHERE salary >
(SELECT AVG(salary)
FROM employees inner_table
WHERE inner_table.department_id =
outer_table.department_id);

Cada vez que uma linha da

consulta externa é processada,
a consulta interna é avaliada.
Usando Subconsultas Correlacionadas
Exiba detalhes dos funcionários que tiverem mudado de cargo pelo
menos duas vezes.

SELECT e.employee_id, last_name,e.job_id

FROM employees e
WHERE 2 <= (SELECT COUNT(*)
FROM job_history
WHERE employee_id = e.employee_id);
Usando o Operador EXISTS
◦ O operador EXISTS verifica a existência de linhas no
conjunto de resultados da subconsulta.
◦ Se um valor de linha de subconsulta for localizado:
 A pesquisa não continuará na consulta interna
 A condição é marcada com flag como TRUE
◦ Se um valor de linha de subconsulta não for localizado:
 A condição será marcada com flag como FALSE
 A pesquisa continua na consulta interna
Usando o Operador EXISTS
SELECT employee_id, last_name, job_id, department_id
FROM employees outer
WHERE EXISTS ( SELECT 'X'
FROM employees
WHERE manager_id =
outer.employee_id);
Localize Todos os Departamentos que
Não Possuem Funcionários
SELECT department_id, department_name
FROM departments d
WHERE NOT EXISTS (SELECT 'X'
FROM employees
WHERE department_id = d.department_id);

…
Cláusula WITH
◦ Com a cláusula WITH, você pode usar o mesmo bloco de
consulta em uma instrução SELECT quando ele ocorre mais
de uma vez em uma consulta complexa.
◦ A cláusula WITH recupera os resultados de um bloco de
consulta e o armazena no tablespace temporário do usuário.
◦ A cláusula WITH pode melhorar o desempenho.
◦ Inline view
Cláusula WITH: Exemplo
Com a cláusula WITH, crie uma consulta para exibir o nome do
departamento e o total de salários dos departamentos cujo
salário total seja maior que o salário médio de todos os
departamentos.
WITH
dept_costs AS (
SELECT d.department_name, SUM(e.salary) AS dept_total
FROM employees e JOIN departments d
ON e.department_id = d.department_id
GROUP BY d.department_name),
avg_cost AS (
SELECT SUM(dept_total)/COUNT(*) AS dept_avg
FROM dept_costs)
SELECT *
FROM dept_costs
WHERE dept_total >
(SELECT dept_avg
FROM avg_cost)
ORDER BY department_name;
O que é uma view?
Tabela EMPLOYEES
Vantagens das views
Para restringir Para simplificar
acesso de dados consultas complexas

Para prover Para apresentar views

independência diferentes dos
de dados mesmos dados
Views simples e views complexas
Recurso Views simples Views complexas
Número de tabelas Uma Uma ou mais

Contém funções Não Sim

Contém grupos de dados Não Sim

Operações DML usando Sim Nem sempre

uma view
Criando uma view
◦ Você incorporou uma subconsulta na instrução
CREATE VIEW:
CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[, alias]...)]
AS subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

◦ A subconsulta pode conter a sintaxe complexa de

SELECT.
Criando uma view
◦ Crie a view EMPVU80 contendo os detalhes dos
funcionários no departamento 80:
CREATE VIEW empvu80
AS SELECT employee_id, last_name, salary
FROM employees
WHERE department_id =80;

DESCRIBE empvu80
Criando uma view
◦ Crie uma view usando aliases de colunas na subconsulta:

CREATE VIEW salvu50

AS SELECT employee_id ID_NUMBER, last_name NAME,
salary*12 ANN_SALARY
FROM employees
WHERE department_id =50;

◦ Selecione as colunas dessa view pelos nomes de alias

atribuídos.
Recuperando dados de uma view
SELECT *
FROM salvu50;
Modificando uma view
◦ Modifique a view EMPVU80 usando uma cláusula
CREATE OR REPLACE VIEW. Adicione um alias
para cada nome de coluna:

CREATE OR REPLACE VIEW empvu80

(id_number, name, sal, department_id)
AS SELECT employee_id, first_name || ' '
|| last_name, salary, department_id
FROM employees
WHERE department_id =80;

◦ Os aliases de coluna na cláusula CREATE OR

REPLACE VIEW são listados na mesma ordem que
as colunas na subconsulta.
Criando uma view complexa
Crie uma view complexa que contenha funções do grupo para
exibir os valores de duas tabelas:

CREATE OR REPLACE VIEW dept_sum_vu

(name, minsal, maxsal, avgsal)
AS SELECT d.department_name, MIN(e.salary),
MAX(e.salary),AVG(e.salary)
FROM employees e JOIN departments d
ON (e.department_id = d.department_id)
GROUP BY d.department_name;
Regras para execução das operações
DML em uma view
Não será possível modificar/inserir/excluir os dados
em uma view se ela contiver:
◦ Funções de grupo
◦ Uma cláusula GROUP BY
◦ A palavra-chave DISTINCT
◦ A palavra-chave ROWNUM da pseudocoluna
◦ Colunas definidas por expressões
Removendo uma view
É possível remover uma view sem perda de dados porque a view
se baseia em tabelas subjacentes no banco de dados.

DROP VIEW view;

DROP VIEW empvu80;