07 bd sql dml

Prévia do material em texto

Banco de DadosBanco de Dados
DML(Data DML(Data ManipulationManipulation LanguageLanguage))
20122012--11
Professor Melo
Apresentação
� Técnico em Desenvolvimento de Sistemas - Ibratec, 
Recife-PE
� Bacharel em Sistemas de Informação – FIR, Recife-PE
� Especialista em Docência no Ensino Superior –
Faculdade Maurício de Nassau, Recife-PE
� Mestre em Ciência da Computação – UFPE/CIN, 
Recife-PE
� Currículo Lattes
http://lattes.cnpq.br/0759508594425296)
� Homepage
https://sites.google.com/site/hildebertomelo/
Disciplinas Lecionadas
� Desenvolvimento de Aplicações Desktop
� Programação Orientada a Objetos
� Estrutura de Dados
� Tecnologia da Informação & Sociedade
� Sistemas Operacionais
� Sistemas Distribuídos
� Introdução a Informática
� Lógica de Programação
� Informática Aplicada a Saúde
� Banco de Dados
� Projeto de Banco de Dados
� Análise de Projetos Orientado a Objetos
� Programação Cliente Servidor
� Linguagens de Programação: C, C#, Pascal, PHP, ASP, Delphi, Java, JavaScript
� Programação WEB
4
DML
Instruções Select Básicas
A instrução SELECT recupera informações do banco de dados
A instrução SELECT permite as seguintes operações:
• Projeção: você pode usar o recurso de projeção da
linguagem SQL para escolher as colunas de uma tabela
que devem ser retornadas por uma consulta. É possível
escolher o número de colunas que for necessário da
tabela.
• Seleção: você pode usar o recurso de seleção da
linguagem SQL para escolher as linhas de uma tabela que
devem ser retornadas por uma consulta e pode usar vários
critérios para restringir as linhas exibidas.
• Junção: você pode usar o recurso de junção da linguagem
SQL para reunir dados armazenados em diferentes tabelas, 
criando um vínculo entre eles.
5
SeleçãoProjeção
Tabela 1 Tabela 2
Tabela 1Tabela 1
Junção
SELECT
6
SELECT
� As instruções SQL não fazem distinção entre maiúsculas
e minúsculas.
� As instruções SQL podem estar em uma ou mais linhas.
� As palavras-chave não podem ser abreviadas ou dividas
de uma linha para outra.
� Normalmente, as cláusulas são colocadas em linhas
separadas.
� Os recuos são usados para aperfeiçoar a legibilidade.
7
SELECT
Instrução SELECT Básica
SELECT *|{[DISTINCT] coluna|expressão [apelido],...}
FROM tabela;
SELECT *|{[DISTINCT] coluna|expressão [apelido],...}
FROM tabela;
� SELECT identifica quais colunas
� FROM identifica qual tabela
8
SELECT
� Select [lista da(s) coluna(s)]
� From [lista da(s) tabela(s)]
� Where [lista da(s) condição(ões) de pesquisa]
� Group by
� A Cláusula GROUP BY é utilizada para agrupar linhas da 
tabela que compartilham os mesmos valores em todas as 
colunas da lista. 
� Having
� podem fazer referência tanto a expressões agrupadas 
9
SELECT
Operadores:
� Aritmético
� Comparação
� Lógicos
� Concatenação
Operador
+
-
*
/ 
Descrição
Adicionar
Subtrair
Multiplicar
Dividir
Aritmético
10
SELECT
Precedência de Operadores
� A multiplicação e a divisão têm prioridade sobre a 
adição e a subtração.
� Os operadores com a mesma prioridade são
avaliados da esquerda para a direita.
� Os parênteses são usados para forçar a avaliação
priorizada e para esclarecer as instruções.
*** /// +++
___
11
SELECT
� A multiplicação e a divisão têm prioridade sobre a 
adição e a subtração.
� Os operadores com a mesma prioridade são
avaliados da esquerda para a direita.
� Os parênteses são usados para forçar a avaliação
priorizada e para esclarecer as instruções.
12
SELECT
Operador
<
>
<=
>=
=
<>
Descrição
Menor que
Maior que
Menor ou igual que
Maior ou igual que
Igual a
Diferente
Comparação
13
SELECT
Operador
ALL
ANY
BETWEEN
CONTAININIG
EXISTS
IN
IS
LIKE
NULL
SOME
STARTING WITH
Descrição
Todos
Algum
Entre
Contendo
Existe
Em
É
Igual a 
Indefinido
O mesmo que Any
Iniciando com
Comparação
14
SELECT
Usando a Condição LIKE
� Use a condição LIKE para executar pesquisas
curingas de valores válidos de string de pesquisa.
� As condições de pesquisa podem conter caracteres
literais ou números:
� % denota zero ou muitos caracteres.
� _ denota um caractere.
15
SELECT
Lógicos
Operador
AND
OR
NOT
Significado
Retorna TRUE se ambas as condições
de componentes forem verdadeiras
Retorna TRUE se uma das condições
de componente for verdadeira
Retorna TRUE se a condição seguinte
for falsa
NOT � Pode ser usado com os operadores 
LIKE, IN, BETWEEN AND.
16
SELECT
Concatenação
Operador
||
Descrição
Encadeamento
Um operador de concatenação:
� Concatena colunas ou strings de caracteres a outras
colunas
� É representado por duas barras verticais (||)
� Cria uma coluna resultante que é uma expressão de 
caracteres
17
SELECT
Apelidos (Alias)
� Renomeia um cabeçalho de coluna
� É útil para cálculos
� Segue imediatamente o nome da coluna. Também
pode haver a palavra-chave AS opcional entre o 
nome da coluna e o apelido
� Necessita de aspas duplas caso contenha espaços
ou caracteres especiais ou faça distinção entre 
maiúsculas e minúsculas
18
SELECT
Sobreponha regras de precedência usando parênteses.
Ordem de Avaliação Operador
1 Operadores aritméticos 
2 Operador de concatenação
3 Condições de comparação
4 IS [NOT] NULL, LIKE, [NOT] IN
5 [NOT] BETWEEN
6 Condição lógica NOT
7 Condição lógica AND
8 Condição lógica OR
Regras de Precedência
Modelagem Para os Exemplos
19
20
SELECT
Regras de Precedência
SELECT gid, nome, populacao
FROM municipio
WHERE nome = ‘Nordeste'
OR nome = ‘Sul'
AND populacao > 15000;
SELECT gid, nome, populacao
FROM municipio
WHERE (nome = ‘Nordeste'
OR nome = ‘Sul')
AND populacao > 15000;
Use parênteses para forçar a prioridade.
21
SELECT - Consulta sem Operadores
Select * 
From regiao
Select uf, estado
From estado
Select nome, sigla
From regiao
--renomeando as colunas
Select uf as sigla, estado as nome
From estado
22
SELECT - Consulta Com 
Operadores Lógicos e de 
Comparação
Select *
From regiao
Where nome like ‘A%’
Select *
From regiao
Where nome >= ‘A’ and nome <= ‘B’
Select *
From regiao
Where nome between ‘A’ and ‘B’
23
SELECT - Consulta Com 
Operadores Lógicos e de 
Comparação
Select * 
From estado
Where like like ‘_F%’
Select *
From estado
Where uf = ‘PE’ or estado = ‘PB’
Select *
From estado
Where uf in (‘RJ’, ‘SP’)
24
SELECT - Consulta Com 
Operadores Lógicos e de 
Comparação
Select nome, populacao
From municipio
Where nome <> ‘Recife’
Select nome, populacao
From municipio
Where nome not in (‘Fortaleza’)
25
SELECT - Consulta Com 
Operadores Aritméticos
Select gid, nome, populacao
From municipio
Select gid, nome, populacao, (populacao * 0.20) as percentual
From municipio
Select gid, nome, populacao, (populacao / 2) as percentual
From municipio
26
SELECT - Consulta Com Operador 
de Concatenação
Select (uf + estado) as Nome_Completo
From estado
Where estado = ‘Pernambuco’
� Um literal é um caractere, um número ou uma data incluída na lista 
SELECT.
� Os valores literais de caracteres e datas devem estar entre aspas 
simples.
� Cada string de caracteres é gerada uma vez para cada
linha retornada.
27
SELECT- A Cláusula Distinct
Select nome
From subestacao
Select distinct nome
From subestacao
Select uf, estado
From estado
Select distinct uf, estado
From Customer
28
SELECT A Cláusula Order By
• Pode-se indicar a posição da coluna por que se 
deseja ordenara saída do SELECT ao invés de 
indicar o nome da coluna. 
• É possível ordenar a saída da consulta por 
colunas que NÃO estão no SELECT.
� A cláusula ORDER BY aparece por último na
instrução SELECT.
� Pode-se classificar pelo apelido da coluna, bem 
como por várias colunas
29
SELECT - A Cláusula Order By
Select uf, estado
From estado
Order by uf asc
Select uf, estado
From estado
Order by uf desc
Select uf, estado
From estado
Where estado = ‘Bahia’
Order by estado
Select uf, estado
From estado
Where estado like ‘%de%’
Order by uf, estado
30
SELECT - A Cláusula Group By
(agrupar linhas no conjunto 
resultado)
Select gid, nome, sigla
From regiao
Order by nome, sigla
Select count(gid), nome
From regiao
Group by nome
Order by nome
31
Junções
32
Instruções Select Básicas
A instrução SELECT recupera informações do banco de dados
A instrução SELECT permite as seguintes operações:
• Projeção: você pode usar o recurso de projeção da 
linguagem SQL para escolher as colunas de uma tabela 
que devem ser retornadas por uma consulta. É possível 
escolher o número de colunas que for necessário da 
tabela.
• Seleção: você pode usar o recurso de seleção da 
linguagem SQL para escolher as linhas de uma tabela que 
devem ser retornadas por uma consulta e pode usar vários 
critérios para restringir as linhas exibidas.
• Junção: você pode usar o recurso de junção da linguagem 
SQL para reunir dados armazenados em diferentes tabelas, 
criando um vínculo entre eles.
33
SeleçãoProjeção
Tabela 1 Tabela 2
Tabela 1Tabela 1
Junção
34
EMPLOYEE DEPARTMENT
O
O
35
Produtos Cartesianos
� Um produto cartesiano será formado quando:
� Uma condição de junção for omitida.
� Uma condição de junção for inválida.
� Todas as linhas da primeira tabela forem unidas a 
todas as linhas da segunda tabela.
� Para evitar um produto Cartesiano, sempre inclua uma 
condição de junção válida em uma cláusula WHERE.
36
Produto
cartesiano: 
20x8=160 linhas
EMPLOYEES (20 linhas) DEPARTMENTS (8 linhas)
O
O
37
EMPLOYEES DEPARTMENTS
Chave 
estrangeira
Chave 
primária
O O
Junções
38
JUNÇÕES
Situação: Recuperar os nomes de todos empregados e os 
códigos dos seus respectivos cargos.
Resposta:
Select Full_Name, Job_Code
From Employee
Order by Job_Code
39
Situação: Recuperar os nomes de todos os empregados, os 
códigos e os nomes dos seus respectivos cargos.
Usar uma junção para consultar dados de uma ou mais
tabelas.
� Crie uma condição de junção na cláusula WHERE.
� Coloque o nome da tabela antes do nome da coluna 
quando aparecer o mesmo nome de coluna em mais 
de uma tabela.
SELECT tabela1.coluna, tabela2.coluna
FROM tabela1, tabela2
WHERE tabela1.coluna1 = tabela2.coluna2;
SELECT tabela1.coluna, tabela2.coluna
FROM tabela1, tabela2
WHERE tabela1.coluna1 = tabela2.coluna2;
40
� Simplifique consultas usando apelidos de 
tabela.
� Melhore o desempenho usando prefixos de 
tabela.
SELECT e.employee_id, e.last_name, e.department_id, 
d.department_id, d.location_id
FROM employees e, departments d
WHERE e.department_id = d.department_id;
41
Select distinct e.Full_Name, e.Job_Code, 
j.Job_Title
From Employee e, Job j
Where e.Job_Code = j.Job_Code
Order by e.Job_Code
Resposta:
Select distinct Employee.Full_Name, 
Employee.Job_Code, Job.Job_Title
From Employee, Job
Where Employee.Job_Code = Job.Job_Code
Order by Employee.Job_Code
42
Situação: Recuperar os códigos e nomes dos empregados, e 
os nomes dos seus respectivos cargos. Recuperar 
também os atuais salários dos empregados. Selecionar 
apenas os cargos com código maior ou igual a 100.
Select distinct e.Emp_No, e.Full_Name, 
j.Job_Title,sh.New_Salary
From Employee e, Job j, Salary_History sh
Where e.Job_Code = j.Job_Code
and e.Emp_No = sh.Emp_No
and e.Emp_No >= 100
Order by e.Emp_No
43
Unindo Mais de Duas Tabelas
� Para unir n tabelas, é necessário um mínimo de n-1 condições 
de junção. Por exemplo, para unir três tabelas, é necessário 
um mínimo de duas junções. 
EMPLOYEES LOCATIONSDEPARTMENTS
O
44
Select ep.Proj_Id, p.Proj_Name, ep.Emp_No, 
e.Full_Name
From Employee_Project ep, Project p, 
Employee e
Where ep.Proj_Id = P.Proj_Id
and ep.Emp_No = e.Emp_no
and e.Depto_No = 621
Order by ep.Proj_Id, ep.Emp_no
Situação: Recuperar todos os projetos em que estejam 
envolvidos empregados do departamento 621.
45
Situação: Auto-Relacionamento.
Relacionar todos os departamentos superiores e seus 
departamentos subordinados.
Select distinct Head_Dept as Dep_Sup, 
Depto_No as Dep_Sub, Department as 
Nome_Depto_Subordinado
From Department
O que falta? 
46
Select distinct d1.Head_Dept as Dep_Sup, 
d2.Department as Nome_Depto_Superior, 
d1.Depto_No as Dep_Sub, d1.Department as 
Nome_Depto_Subordinado
From Department d1, Department d2
Where d1.Head_Dept = d2.Dept_No
order d1.Head_Dept, d2.Depto_No
Trazendo o nome do Departamento Superior
47
Junções Internas e Externas
EMPLOYEESDEPARTMENTS
Não há funcionários no 
departamento 190. 
O
48
� No relacionamento Employee e Department existe a 
seguinte situação:
Um empregado às vezes gerencia um ou mais 
departamentos
Um departamento às vezes é gerenciado por um 
empregado
� Temos Deptos sem gerentes 
� Temos empregados que não gerenciam Deptos
49
� Junção interna: Inner Join
� Junção externa: Left Outer Join, Right Outer 
Join e Full Outer Join.
� Inner Join = colunas com valores nulos são 
ignorados no processo de seleção e deixam de 
ser retornadas.
50
Inner Join
Recuperar os nomes de todos os departamentos e os 
nomes dos empregados que os gerenciam.
Select distinct Department as Depto, 
Full_Name as Gerente
From Department 
Inner Join Employee on Emp_No = Mngr_No
Order by Department
51
Criando Junções com a Cláusula ON
� Para especificar condições arbitrárias ou colunas a 
serem unidas, é usada a cláusula ON.
� A condição de junção é separada de outras condições 
de pesquisa.
� A cláusula ON facilita a compreensão do código.
52
Junções INNER Versus OUTER
� A junção de duas tabelas que retorna apenas linhas 
correspondentes é uma junção interna.
� Uma junção entre duas tabelas que retorna os resultados da 
junção interna assim como linhas não correspondentes em 
tabelas esquerdas (ou direitas) 
é uma junção externa esquerda (ou direita).
� Uma junção entre duas tabelas que retorna os resultados de 
uma junção interna assim como os resultados de uma 
junção esquerda ou direita é uma junção externa completa.
53
Left Outer Join: retorna todas as linhas da tabela à esquerda do 
operador, sem preocupar-se com as condições especificadas.
54
Recuperar os nomes de todos os departamentos e os nomes dos 
empregados que os gerenciam. Incluir os nomes dos 
departamentos que estão sem gerentes.
Select distinct Department as Depto, 
Full_Name as Gerente
From Department 
Left outer join Employee on Emp_No = Mngr_No
Order by Department
55
Right Outer Join: ao contrário do Left Outer Join, retorna todas 
as linhas da tabela à direita do operador, sem preocupar-se 
com as condições especificadas.
56
Recuperar os nomes de todos os departamentos e os nomes dos 
empregados que os gerenciam. Incluir os nomes dos 
empregados que não gerenciam departamentos.
Select distinct Department as Depto, 
Full_Name as Gerente
From Department 
Right outer join Employee on Emp_No = Mngr_No
Order by Department
57
� Full Alter Join: combina as cláusulas Left Outer Join e Right 
Outer Join.
Recuperar os departamentos sem gerentes e os empregados 
que não gerenciamdepartamentos:
Select distinct Department as Depto, 
Full_Name as Gerente
From Department 
Full outer join Employee on Emp_No = Mngr_No
Order by Department
58
Funções
59
Funções de Grupo
As funções de grupo operam em conjuntos de linhas para 
fornecer um resultado por grupo.
EMPLOYEES
O salário 
máximo 
na tabela
EMPLOYEES.
O
60
Produz um único valor a partir dos valores obtidos das 
colunas, ou seja, retornam um conjunto resultado que 
representa um agregado de valores referentes às várias 
linhas manipuladas no processo de seleção de dados.
Funções Agregadas
� AVG
� COUNT
� MAX
� MIN
� SUM
61
Sintaxe deFunções de Grupo
SELECT [coluna,] função_de_grupo(coluna), ...
FROM tabela
[WHERE condição]
[GROUP BY coluna]
[ORDER BY coluna];
62
AVG
� Situação: obter o orçamento médio dos departamentos
Select AVG (budget) as orcamento
From Department
Select AVG (distinct budget) as orcamento
From Department
Observações:
� Valores nulos ou desconhecidos são ignorados pela função.
� Somente colunas numéricas podem ser utilizadas.
� Se nenhuma linha é retornada, a função retorna NULL.
63
COUNT
� Permite contar linhas em uma tabela.
� Opções: 
ALL – contar linhas, excluindo nulos
Distinct – eliminar valores duplicados e nulos
* - contar linhas, incluindo nulos
64
Select Cust_No, Customer, State_Province
From Customer
Select count(*) 
From Customer
Select count(all State_Province) 
From Customer
Select count(distinct State_Province) 
From Customer
Select count(* State_Province)
From Customer
Select count(Custo_No, State_Province)
From Customer
Select count(Custo_No), count(State_Province)
From Customer
65
MAX, MIN
� Situação: obter o maior e menor salários
Select min(salary), max(salary)
From Employee
Observações:
� Aceitam argumentos numéricos ou do tipo texto.
� Se nenhuma linha é retornada, a função retorna NULL.
66
SUM
Situação: obter a soma dos orçamentos dos Departamentos
Select sum(budget) as Orcamento_Total
From Department
Select sum(distinct budget) as Orcamento_Total
From Department
Observações:
� Aceitam somente argumentos numéricos.
� Se nenhuma linha é retornada, a função retorna NULL.
67
Sofisticando Consultas
EMPLOYEES
O salário
médio 
na 
tabela
EMPLOYEES
para cada
departamento.
4400
O
9500
3500
6400
10033
68
Criando Grupos de Dados: 
A Sintaxe da Cláusula GROUP BY
Divida linhas de uma tabela em grupos menores 
usando a cláusula GROUP BY.
SELECT coluna, função_de_grupo(coluna)
FROM tabela
[WHERE condição]
[GROUP BY expressão_group_by]
[ORDER BY coluna];
69
Sofisticando Consultas
� Situação: Obter a quantidade de empregados existentes 
em cada país.
Select Job_Country as Pais, Count(*) as Quantidade
From Employee
Group by Job_Country
70
Sofisticando Consultas
� Situação: Obter o total vendido a cada cliente, desde que 
a venda total do cliente seja maior que 100.000
Select Cust_No as Cliente, Sum(Total_Values) as 
Total_Vendas
From Sales
Where cust_no between 1 and 1000
Group by Cust_No
Having sum(Total_Value) > 100000
Order By Cust_No
71
Agrupando por mais de uma Coluna
EMPLOYEES
Adicione os 
salários na
tabela
EMPLOYEES
para
cada cargo, 
agrupado por 
departamento.O
72
Qualquer coluna ou expressão na lista SELECT que 
não seja uma função agregada deve estar na cláusula
GROUP BY.
SELECT dept_no, COUNT(last_name)
FROM employee;
SELECT dept_no, COUNT(last_name)
FROM employee;
73
� Não é possível usar a cláusula WHERE para 
restringir grupos.
� Use a cláusula HAVING para restringir grupos.
� Não é possível usar funções de grupo na 
cláusula WHERE.
SELECT dept_no, AVG(salary)
FROM employee
WHERE AVG(salary) > 8000
GROUP BY dept_no;
SELECT dept_no, AVG(salary)
FROM employee
WHERE AVG(salary) > 8000
GROUP BY dept_no;
74
Excluindo Resultados do Grupo
O orçamento
máximo por
departamento
quando for
maior que
US$ 10.000
EMPLOYEES
O
75
Excluindo Resultados do Grupo:
A Cláusula HAVING
Use a cláusula HAVING para restringir grupos:
1.As linhas são agrupadas.
2.A função de grupo é aplicada.
3.Os grupos que correspondem à cláusula HAVING
são exibidos.
SELECT coluna, função_de_grupo
FROM tabela
[WHERE condição]
[GROUP BY expressão_group_by]
[HAVING condição_de_grupo]
[ORDER BY coluna];
76
SELECT dept_no, MAX(budget)
FROM employees
GROUP BY dept_no
HAVING MAX(budget)>10000 ;
77
Sofisticando Consultas
� Situação: Obter o total vendido a cada cliente, 
com cógido e nome, desde que a venda total do 
cliente seja maior que 100.000
Select distinct s.Cust_No as Cliente, c.Customer as Nome, 
Sum(s.Total_Value) as Total_Vendas
From Sales s, Customer c
Where s.Cust_No = c.Cust_No
Group by s.Cust_No, c.Customer
Having sum(s.Total_Value) > 100000
Order By s.Cust_No
78
Funções que aplicam-se sobre as colunas resultado de 
uma consulta.
Funções Não-Agregadas
� CAST
� EXTRACT
� UPPER
79
CAST
� Executa a conversão de um tipo de dado em 
outro.
Recuperar os departamentos subordinados ao 
departamento ‘000’
Select Dept_No, Department, Location
From Department
Where Head_Dept = ‘000’
Select Dept_No, Department, Location
From Department
Where cast(Head_Dept as integer) = 0
80
EXTRACT
Recuperar os empregados que tiveram mudança de salário no ano 
de 1992 e que o novo salário tenha sido superior a 80.000.
Select distinct Emp_No, Change_Date, New_Salary
From Salary_History
Where Extract(Year from Change_Date) = 1992
and New_Salary > 80000
Select distinct Emp_No, extract(month from Change_Date) 
|| ‘/’ || extract(year from Change_Date) as Mes_Ano, 
New_Salary
From Salary_History
Where Extract(Year from Change_Date) = 1992
and New_Salary > 80000
81
UPPER
Converte caracteres minúsculos em maiúsculos, porém 
não converte caracteres especiais.
Select Emp_No, First_Name, Last_Name
From Employee
Where Job_Country = ‘England’
Select Emp_No, First_Name, Last_Name
From Employee
Where upper(Job_Country) = ‘ENGLAND’
Select Emp_No, First_Name, Last_Name
From Employee
Where upper(Job_Country) = upper(‘England’)
82
Sub Consultas
83
Subconsulta
Usando uma Subconsulta
para Resolver um Problema
Quem tem um salário maior que o de Michael?
Que funcionários têm salários 
maiores que o de Michael?
Consulta Principal:
??
Qual é o salário de 
Michael?
??
Subconsulta
84
Subconsulta
� Uma subconsulta é “uma consulta dentro de outra 
consulta”.
� A subconsulta é executada antes da consulta 
principal.
� Os resultados da subconsulta são utilizados pela 
consulta principal.
� Uma subconsulta limita a saída da consulta pai (mais 
externa) por produzir um resultado intermediário de 
algum tipo.
85
Subconsulta
SELECT lista_de_seleção
FROM tabela
WHERE operador expr
(SELECT lista_de_seleção
FROM tabela);
SELECT last_name
FROM employee
WHERE salary >
(SELECT salary
FROM employee
WHERE last_name = ‘Michael');
4400
Sintaxe de Subconsulta
86
Subconsulta
Diretrizes para o Uso de Subconsultas
� Coloque as subconsultas entre parênteses.
� Coloque as subconsultas no lado direito da condição 
de comparação.
� A cláusula ORDER BY na subconsulta não é
necessária.
� Use operadores de uma única linha com 
subconsultas de uma única linha e use operadores 
de várias linhas com subconsultas de várias linhas.
87
Subconsulta
Tipos de Subconsultas
• Subconsulta de várias linhas
• Subconsulta de uma única linha
ST_CLERK
Consulta principal
Subconsulta
retorna
ST_CLERK
SA_MANConsulta principal
Subconsulta
retorna
88
Subconsulta
Subconsultas de uma Única Linha
� Retorne somente uma linha
� Use operadores de comparação de uma única linha
Operador
=
>
>=
<
<=
<>
Significado
Igual a
Maior que
Maior que ou igual a
Menor que
Menor que ou igual a
Diferente de
89
Subconsulta
SELECT last_name, job_code, salary
FROM employee
WHERE job_code =
(SELECT job_code
FROM employee
WHERE emp_no = 121)
AND salary >
(SELECT salary
FROM employee
WHERE emp_no = 127) ;
SRep
4400
Executando Subconsultas
de uma Única Linha
90
Subconsulta
Usando Funções de Grupo
em uma Subconsulta
SELECT last_name, job_code, salary
FROM employee
WHERE salary =
(SELECT MIN(salary)
FROM employee);
22935
91
Subconsulta
A Cláusula HAVING com Subconsultas
� O SGBD executa primeiro as subconsultas.
� O SGBD retorna os resultados para a cláusula HAVING
da consulta principal.
SELECT dept_no, MIN(salary)
FROM employee
GROUP BY dept_no
HAVING MIN(salary) >
(SELECT MIN(salary)
FROM employee
WHERE dept_no = 110);
61637,84
92
Subconsulta
O que há de Errado 
com esta Instrução?
SELECT emp_no, last_name
FROM employee
WHERE salary =
(SELECT MIN(salary)
FROM employee
GROUP BY dept_no);
Operador de uma única linha com 
subconsulta de várias linhas
Operador de uma única linha com 
subconsulta de várias linhas
93
Subconsulta
Subconsultas de Várias Linhas
� Retorne mais de uma linha
� Use operadores de comparação de várias linhas
Operador
IN
ANY
ALL
Significado
Igual a qualquer membro da lista
True se a comparação é verdadeira a pelo 
menos um valor retornado da subquery
True se a comparação é verdadeira para 
todos os valores retornados da subquery
94
Subconsulta
Operador Descrição
IN Igual a qualquer valor da lista
ANY (SOME) Deve ser usado em conjunto com os operadores =, >, <. 
>=, <=, <>.
Compara um valor a um dos valores da lista. Por exemplo, 
> ANY significa que o valor deve ser maior que qualquer 
valor da lista.
ALL Deve ser usado em conjunto com os operadores =, >, <. 
>=, <=, <>.
Compara um valor a todos os valores da lista. Por 
exemplo, > ALL significa que o valor deve ser maior que 
todos os valores da lista
95
Subconsulta
SELECT emp_no, last_name, job_code, salary
FROM employee
WHERE salary < ANY
(SELECT salary
FROM employee
WHERE job_code = ‘Admin')
AND job_code <> ‘Admin';
Usando o Operador ANY em
Subconsultas de Várias Linhas
53793,22935,31275,27000
96
Subconsulta
SELECT emp_no, last_name, job_code, salary
FROM employee
WHERE salary < ALL
(SELECT salary
FROM employee
WHERE job_code = 'Eng')
AND job_code <> 'Eng';
Usando o Operador ALL em
Subconsultas de Várias Linhas
97
Subconsulta
� Recuperar o último nome do empregado, o número do 
depto e o salário de todos os empregados cujos salários 
sejam iguais a qualquer salário dos empregados do 
departamento 623.
Select Last_Name, Dept_No, Salary
From Employee
Where salary in ( Select salary 
From Employee
Where Depto_No = 623)
Order by Dept_No
98
Subconsulta
Selecione os empregados que ganham o mesmo salário que o 
menor salário do departamento.
select last_name, dept_no, salary
from employee
where salary in (select min(salary)
from employee
group by dept_no);
99
Subconsulta
Selecione os empregados que não ganham o 
mesmo salário que o menor salário dos 
departamentos.
select last_name, dept_no, salary
from employee
where salary not in (select min(salary)
from employee
group by dept_no);
100
Subconsulta
� Selecione o nome e o salário dos empregados , cujo 
salário seja maior do que qualquer salário do 
departamento 623.
select last_name, salary
from employee
where salary > any (select salary
from employee
where dept_no = 623);
101
Subconsulta
� Selecione o nome e o salário dos empregados , cujo 
salário seja maior do que todo salário do departamento 
623.
select last_name, salary
from employee
where salary > all (select salary
from employee
where dept_no = 623);
102
Insert – Update - Delete
103
DML
� Uma instrução DML é executada quando você:
� Adiciona novas linhas a uma tabela
� Modifica linhas existentes em uma tabela
� Remove linhas existentes de uma tabela
� Os comandos DML caracterizam-se por poderem ser 
desfeitos, ou seja, pode-se recuperar a posição do banco 
de dados anterior ao comando.
� A qualidade das informações armazenadas será
diretamente proporcional à qualidade da definição do 
modelo de dados que servir de base para a criação do 
banco.
104
Insert
Adicionando uma Nova Linha a uma Tabela
DEPARTMENT
Nova
linha
Oinsira uma nova 
linha na tabela 
DEPARTMENTO
105
Insert
� Adicione novas linhas a uma tabela usando a 
instrução INSERT.
� Somente uma linha é inserida por vez com esta 
sintaxe.
INSERT INTO tabela [(coluna [, coluna...])]
VALUES (valor [, valor...]);
INSERT INTO tabela [(coluna [, coluna...])]
VALUES (valor [, valor...]);
106
Insert
Insert into Department
(Depto_No, Department, Head_Dept, Mngr_No, Budget, Location, 
Phone_No)
values
(‘000’, ‘Corporate Headquarters’, NULL, 105, 1000000.00, 
‘Monterey’, ‘(408) 555-1234’)
Insert into Department
(Depto_No, Department, Head_Dept, Mngr_No, Budget, Location, 
Phone_No)
values
(‘100’, ‘Sales and Marketing’, ‘000’, 85, 2000000.00, ‘San 
Francisco’, ‘(415) 555-1444’)
Testar chave primária e índice único
107
Insert
� Não é necessário seguir a mesma ordem da 
instrução Create Table na inserção.
Insert into Department
(Department, Head_Dept, Mngr_No, Budget, Location, Phone_No, 
Depto_No)
values
(‘Sales and Marketing’, ‘000’, 85, 2000000.00, ‘San 
Francisco’, ‘(415) 555-1444’, ‘100’)
108
Insert
� Pode-se omitir os nomes das colunas. Neste caso 
os valores devem ser fornecidos na mesma ordem 
do Create Table.
Insert into Department
values
(‘700’, ‘Sales’, ‘000’, 85, 2000000.00, ‘San Francisco’, 
‘(415) 555-1444’)
109
Insert
� Nem todas as colunas precisam ser fornecidas. Colunas 
não especificadas recebem valor NULL ou valores 
padrões definidos.
Insert into Department
(Depto_No, Department, Head_Dept, Mngr_No, Budget)
values
(‘999’, ‘Sales’, ‘000’, 85, 2000000.00)
110
Insert
� Crie sua instrução INSERT com uma subconsulta.
� Não use a cláusula VALUES.
� Estabeleça a correspondência entre o número de 
colunas da cláusula INSERT e o da subconsulta.
INSERT INTO sales_reps(id, name, salary, commission_pct)
SELECT employee_id, last_name, salary, commission_pct
FROM employees
WHERE job_id LIKE '%REP%';
4 rows created.4 rows created.
Copiando Linhas de Outra Tabela
111
Update
Alterando os Dados em uma Tabela
EMPLOYEE
Atualize linhas na tabela EMPLOYEE.
112
Update
A Sintaxe da Instrução UPDATE
� Modifique linhas existentes com a instrução 
UPDATE.
� Atualize mais de uma linha por vez, se necessário.
UPDATE tabela
SET coluna = valor [, coluna = valor, ...]
[WHERE condição];
UPDATE tabela
SET coluna = valor [, coluna = valor, ...]
[WHERE condição];
113
Update
� Uma ou mais linhas específicas serão 
modificadas se você especificar a cláusula 
WHERE.
� Se você omitir a cláusula WHERE, todas as 
linhas da tabela serão modificadas.
UPDATE employees
SET department_id = 70
WHERE employee_id = 113;
1 row updated.1 row updated.
UPDATE copy_emp
SET department_id = 110;
22 rows updated.
UPDATE copy_emp
SET department_id = 110;
22 rows updated.22 rows updated.
114
Update
Update Customer
set Phone_No= ‘(619) 530-2710’
where Cust_No = 1001
Update Customer
set Address_Line2 = Null
Update Department
set Mngr_No = 999
where Dept_No = ‘623’
115
Update
UPDATE employee
SET dept_no = (SELECT dept_no 
FROM employee
WHERE emp_no = 127), 
salary = (SELECT salary 
FROM employee
WHERE emp_no = 127)
WHERE employee_id = 114;
1 row updated.1 row updated.
Atualizando Duas Colunas com uma 
Subconsulta
Atualize o Depto e o salário do funcionário 114 para que 
correspondam aos do funcionário 127.
116
Update
UPDATE employee
SET salary = (SELECT MAX(max_salary)
FROM Job
WHERE Job_Code = ‘Eng’)
WHERE emp_no = (SELECT emp_no
FROM Salary_History
WHERE old_salary = 40000) ;
Atualizando Linhas com Base em Outra 
TabelaUse subconsultas nas instruções UPDATE para atualizar 
as linhas de uma tabela com base em valores de outra 
tabela.
117
Delete
Delete uma linha da tabela DEPARTMENTS.
Removendo uma Linha de uma 
TabelaDEPARTMENT
118
Delete
A Instrução DELETE
Você pode remover linhas de uma tabela usando a 
instrução DELETE.
DELETE [FROM] tabela
[WHERE condição];
DELETE [FROM] tabela
[WHERE condição];
119
Delete
� Se você especificar a cláusula WHERE, linhas 
específicas serão deletadas.
� Se você omitir a cláusula WHERE, todas as 
linhas da tabela serão deletadas.
DELETE FROM department
WHERE dept_no = 180;
DELETE FROM department
WHERE dept_no = 180;
DELETE FROM Salary_History;DELETE FROM Salary_History;
120
Delete
DELETE FROM employee
WHERE dept_no = 
(SELECT dept_no
FROM department
WHERE department LIKE '%Electronics%');
Deletando Linhas com Base em Outra 
Tabela
Use subconsultas nas instruções DELETE para 
remover linhas de uma tabela com base em valores 
de outra tabela.
121
Delete
Deletando Linhas: Erro de
Restrição de Integridade
DELETE FROM department
WHERE dept_no = 130;
DELETE FROM department
WHERE dept_no = 130;
Violation of FOREIGN KEY constraint "INTEG_28" on table "EMPLOYEE"
Statement: DELETE FROM department
WHERE dept_no = 130
Você não pode deletar uma linha que contenha uma chave primária 
usada como chave estrangeira em outra tabela.
122
Perguntas...
123
Bibliografia
� DATE, Christopher J., DARWEN, Hugh. Foundations 
for future database systems. New York: Addison 
Wesley, 2000.
� ELMASRI, Rames, NAVATHE, Shamkant B. Sistema 
de Banco de Dados – Fundamentos e Aplicações. 3ª
Ed., Rio de Janeiro: LTC, 2000.
� MANZANO, José Augusto N. G. Estudo Dirigido de 
SQL. São Paulo: Érica, 2002.
124
Bibliografia Complementar
� OPPEL, Andy. Banco de Dados Desmistificado. Rio de Janeiro: 
Alta Books, 2004.
� COSTA, Rogério Luís de C. SQL - Guia Prático. São Paulo: 
Brasport, 2004.
� DATE, Christopher J. Introdução a Sistemas de Banco de Dados. 
Rio de Janeiro: Campus, 2004.
� DICOVERY. Princípios de Banco de Dados. São Paulo: Senac, 
1999.
� ELMASRI, R., NAVATHE, S. B. Fundamentals of Database 
Systems. Califórnia: Benjamin/ Cummings, 2000.