IMP BD VenturyAula 07

Prévia do material em texto

IMPLEMENTAÇÃO DE BANCO DE DADOS
Aula 7- Sql Parte 5
Conteúdo Programático desta aula
Aprender os como construir subconsulta
Conhecer os operadores de conjunto
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
Modelo de Dados
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO
Um comando SELECT pode fazer uma consulta que traz dados de duas ou mais tabelas. Esse é um processo chamado de junção [join]. As tabelas têm uma coluna em comum que é usado para fazer as junções.
 Sintaxe da Junção 
Sintaxe ANSI
Sintaxe tradicional (não ANSI)
Tipos Junção:
Junção Cruzada ou Irrestrita (Produto Cartesiano)
Junção Interior
Junção Exterior
Auto-Junção
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO CRUZADA
RETORNANDO TODAS AS COLUNAS DAS DUAS TABELAS E COMBINANDO TODAS AS LINHAS
SELECT * FROM EMPREGADO CROSS JOIN DEPARTAMENTO, 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO INTERIOR – SINTAXE TRADICIONAL
Projetando Colunas
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO INTERIOR – SINTAXE ANSI
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO EXTERIOR - ANSI
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO EXTERIOR - TRADICIONAL
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
AUTO JUNÇÃO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
AUTO JUNÇÃO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
AUTO JUNÇÃO
SELECT G.ID, G.ULT_NOME, G.CARGO 
FROM EMPREGADO G , EMPREGADO S 
WHERE G.ID = S.ID_GERENTE (+) AND S.ID IS NULL
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
Subconsulta é uma consulta dentro de um outro comando SQL que pode ser:
 
CREATE TABLE
CREATE VIEW
SELECT
INSERT
DELETE 
UPDATE
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
Regras para Construção de Subconsultas
 
A consulta interna tem que estar entre parênteses e sempre a mais interna é executada primeiro.
Admite o aninhamento de n consultas internas ou em conjunto com operadores AND e OR
Retorna uma ou várias linhas ou colunas
Usualmente é usada na cláusula WHERE dos comandos SELECT, DELETE e UPDATE
Pode ser usada na cláusula FROM do comando SELECT .
Pode referenciar colunas da consulta mais externa
Não pode conter a cláusula ORDER BY
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
Operador
Descrição
=
Igual a
<> 
Diferente
> 
Maior que
< 
Menor que
>=
Maior ou igual
<=
Menor ou igual
Operadores de Comparação em Subconsultas Simples
Os operadores abaixo podem ser usados em condições de comparação com subconsultas simples, pois manipulam o retorno de apenas uma linha de comparação com a consulta principal.
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
EMPREGADOS DO 
DEPARTAMENTO DE VENDAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
SELECT *
FROM EMPREGADO 
WHERE ID_DEPTO = 20 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
ESTE COMANDO PRODUZ O RESULTADO 20 QUE É UTILIZADO NO COMANDO EXTERNO 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
EMPREGADOS QUE GANHAM MAIS QUE O SALARIO MEDIO DA EMPRESA 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA
SELECT * 
FROM EMPREGADO
WHERE SALARIO > ( SELECT AVG (SALARIO) 
 FROM EMPREGADO)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
EMPREGADOS QUE GANHAM SALARIO DIFERENTE DOS VENDEDORES 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILNHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
Operador
Descrição
IN
Igual a qualquer elemento de
NOT IN
Diferente que qualquer elemento de
> ALL
maior que todos os elementos da lista
< ALL
menor que todos
<> ALL
diferente de todos (o mesmo que NOT IN)
= ANY
igual a algum dos elementos da lista (o mesmo que IN)
> ANY
maior que algum dos elementos da lista
< ANY
menor que algum dos elementos
<> ANY
diferente de algum dos elementos da lista (falso se igual a todos)
OPERADORES DE COMPARAÇÃO EM SUBCONSULTAS MULTI-LINHAS
Existe um conjunto de operadores que podem ser utilizados em subconsultas que retornam mais de uma linhas, este operadores são
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILNHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
EMPREGADOS QUE GANHAM SALARIO MAIORES QUE TODOS OS VENDEDORES 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
SELECT *
FROM EMPREGADO
WHERE SALARIO > ALL ( SELECT SALARIO 
 FROM EMPREGADO 
 WHERE UPPER(CARGO) = 'VENDEDOR')
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
EMPREGADOS QUE GANHAM SALARIO MAIORES QUE TODOS OS VENDEDORES 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - MULTILINHAS
SELECT *
FROM EMPREGADO
WHERE SALARIO > ANY ( SELECT SALARIO 
 FROM EMPREGADO 
 WHERE UPPER(CARGO) = 'VENDEDOR')
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - SELECT
SELECT ID, ULT_NOME, SALARIO , 
 (SELECT AVG(SALARIO) FROM EMPREGADO) AS "SALARIO MEDIO"
FROM EMPREGADO
WHERE SALARIO < (SELECT AVG(SALARIO) FROM EMPREGADO)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - SELECT
SELECT ID, ULT_NOME, SALARIO , 
 (SELECT SALARIO FROM EMPREGADO 
 WHERE UPPER(CARGO) = 'VENDEDOR') AS "SALARIO VENDEDORES"
FROM EMPREGADO
WHERE SALARIO >ALL (SELECT SALARIO FROM EMPREGADO 
 WHERE UPPER(CARGO) = 'VENDEDOR')
NO SELECT A SUBCONSULTA SOMENTE PODE RETORNAR UMA LINHA
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - HAVING
DEPARTAMENTOS COM MEDIA SALARIAL MAIOR QUE MEDIA SALARIAL DA EMPRESA
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - HAVING
SELECT ID_DEPTO, AVG(SALARIO) 
FROM EMPREGADO
GROUP BY ID_DEPTO
HAVING AVG(SALARIO) > ( SELECT AVG(SALARIO) FROM EMPREGADO)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - FROM
ID, ULT_NOME, SALARIO E ID_DEPTO DO EMPREGADO E 
MEDIA SALARIAL DE SEU DEPARTAMENTO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA – FROM
SELECT E.ID, E.ULT_NOME, E.SALARIO, E.ID_DEPTO, RD.MEDIA
FROM EMPREGADO E INNER JOIN 
 (SELECT ID_DEPTO, AVG(SALARIO) AS MEDIA 
 FROM EMPREGADO GROUP BY ID_DEPTO) RD
 ON E.ID_DEPTO = RD.ID_DEPTO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - CORRELATA
EMPREGADOS QUE GANHAM MAIS QUE A MEDIA SALARIAL DE SEU DEPARTAMENTO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA - CORRELATA
SELECT ID, ULT_NOME, SALARIO, ID_DEPTO 
FROM EMPREGADO E
WHERE SALARIO > (SELECT AVG(SALARIO) 
 FROM EMPREGADO I
 WHERE I.ID_DEPTO = E.ID_DEPTO)
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA – TESTE EXISTENCIA
DEPARTAMENTOS QUE POSSUEM VENDEDORES
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
SUBCONSULTA – TESTE EXISTENCIA
SELECT * 
FROM DEPARTAMENTO E
WHERE EXISTS (SELECT * 
 FROM EMPREGADO I
 WHERE E.ID = I.ID_DEPTO AND UPPER(I.CARGO) = 'VENDEDOR')
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO
Trabalhando comConjuntos
Muitas vezes necessitamos que nossas consultas incluam, em uma mesma coluna, dados de mais de uma tabela. Para isto utilizamos os operadores de conjuntos UNION, UNION ALL, INTERSECT e MINUS.
Regras para Utilização dos Operadores de Conjuntos
Os comandos SELECT participantes têm que ter o mesmo número de colunas,
As colunas correspondentes têm que ser do mesmo tipo de dado,
Linhas duplicadas são automaticamente descartadas, exceto com UNION ALL,
Os nomes das colunas resultantes são os da primeira consulta,
ALIAS de colunas só tem efeito se utilizados na primeira consulta,
A cláusula ORDER BY só pode ser utilizada ao final do comando,
Os operadores de conjuntos podem ser utilizados em subconsultas
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - UNION
Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, eliminando as linhas duplicadas resultantes.
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - UNION
20
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - UNION
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
UNION 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
JUNÇÃO EXTERIOR - TRADICIONAL
RESULTADO EQUIVALENTE AO FULL JOIN MAS O COMANDO NÃO É DE FULL JOIN
SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO 
FROM CLIENTE C , EMPREGADO E 
WHERE C.VENDEDOR (+) = E.ID 
UNION OPERADOR DE CONJUNTO
SELECT C.ID, C.NOME, E.ID, E.ULT_NOME,E.CARGO 
FROM CLIENTE C , EMPREGADO E 
WHERE C.VENDEDOR = E.ID (+)
COMANDO EQUIVALENTE À LEFT 
COMANDO EQUIVALENTE À RIGHT 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – UNION ALL
Resulta na combinação de todas as linhas de duas ou mais tabelas participantes do UNION, mantendo todas as linhas duplicadas.
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – UNION ALL
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
UNION ALL
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – INTERSECT
Resulta na interseção entre todas as linhas de duas ou mais tabelas participantes do INTERSECT, ou seja, apenas as linhas comuns entre ela
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - INTERSECT
20 E
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – INTERSECT
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
INTERSECT 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - MINUS
Resulta nas linhas existentes na primeira tabela, mas que não existem na segunda
. 
As linhas comuns também não são resultantes. 
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO - MINUS
20 E QUE NÃO SÃO VENDEDORES
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – MINUS
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
MINUS 
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = 'VENDEDOR'
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
OPERADORES DE CONJUNTO – ORDENAÇÃO
SELECT ID FROM EMPREGADO WHERE ID_DEPTO IN (10,20)
UNION ALL
SELECT ID FROM EMPREGADO WHERE UPPER(CARGO) = VENDEDOR'
ORDER BY ID DESC
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS
Próxima Aula
Aprender a criar Visões, Indices e Sequences
 LINGUAGEM SQL
IMPLEMENTAÇÃO DE BANCO DE DADOS