Sistema de Banco de Dados, 5/E
Capítulo 3: SQL
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Capítulo 3: SQL
 Definição de dados
 Estrutura básica das consultas SQL
 Operações de conjunto
 Funções agregadas
 Valores nulos
 Subconsultas aninhadas
 Consultas complexas
 Views
 Modificação do banco de dados
 Relações juntadas **
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
História
 Linguagem IBM Sequel desenvolvida como parte do projeto System R
no IBM San Jose Research Laboratory
 Renomeada para Structured Query Language (SQL)
 Padrão SQL ANSI e ISO:
 SQL-86
 SQL-89
 SQL-92
 SQL:1999 (nome da linguagem se tornou concordante com o Ano 2000!)
 SQL:2003
 Os sistemas comerciais oferecem a maioria, se não todos, os recursos
da SQL-92, além de conjuntos de recursos variáveis dos últimos
padrões e recursos proprietários especiais.
 Nem todos os exemplos aqui podem funcionar em seu sistema
específico.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Linguagem de definição de dados
•
Permite a especificação não só de um conjunto de relações, mas
também de informações sobre cada relação, incluindo:
 O esquema para cada relação
 O domínio dos valores associados a cada atributo
 As restrições de integridade
 O conjunto dos índices a serem mantidos para cada
relação
 As informações de segurança e autorização para cada
relação
 A estrutura de armazenamento físico de cada relação no
disco
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Tipos de domínio na SQL

char(n). String de caracteres de tamanho fixo com tamanho n especificado pelo
usuário.

varchar(n). String de caracteres de tamanho variável com tamanho n máximo
especificado pelo usuário.

int. Inteiro (um subconjunto finito de inteiros que é dependente da máquina).

smallint. Inteiro pequeno (um subconjunto dependente da máquina do tipo de
domínio inteiro).

numeric(p,d). Número de ponto fixo, com precisão de p dígitos especificada pelo
usuário, com n dígitos à direita do ponto decimal.

real, double precision. Números de ponto flutuante e ponto flutuante de precisão
dupla com precisão dependente da máquina.

float(n). Número de ponto flutuante, com precisão de pelo menos n dígitos.

Mais são abordados no Capítulo 4.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Construção de Create Table
 Uma relação SQL é definida usando o comando create table:
–
create table r (A1 D1, A2 D2, ..., An Dn,
(restrição-de-integridade1),
...,
(restrição-de-integridadek))
 r é o nome da relação
 cada Ai é o nome de um atributo no esquema da relação r
 Di é o tipo de domínio dos valores no domínio do atributo Ai
 Exemplo:
–
create table agência
(nome_agência
char(15) not null,
cidade_agência
char(30),
ativo
integer)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Restrições de integridade em Create Table
 not null
 primary key (A1, ..., An )
•
Exemplo: Declare nome_agência como a chave primária para agência
e certifique-se de que os valores de ativo não sejam negativos.
•
create table agência
(nome_agência char(15),
cidade_agência char(30),
ativo
integer,
chave primária (nome_agência))
•
Declaração de chave primária em um atributo assegura not null
automaticamente na SQL-89 em diante; precisa ser declarada
explicitamente na SQL-89
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Construções Drop Table e Alter Table
 O comando drop table exclui do banco de dados todas as
informações sobre a relação removida.
 O comando alter table é usado para acrescentar atributos a uma
relação existente:
–
–
alter table r add A D
onde A é o nome do atributo a ser acrescentado à relação r e D
é o domínio de A.
 A todas as tuplas na relação é atribuído nulo como o valor para o
novo atributo.
 O comando alter table também pode ser usado para descartar
atributos de uma relação:
–
–
alter table r drop A
onde A é o nome de um atributo da relação r
 O descarte de atributos não é aceito por muitos bancos de dados.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Estrutura básica das consultas
•
•
•
•
•
•
•
•
A SQL é baseada em operações de conjunto e relacionais
com certas modificações e melhorias
Uma componente SQL típica tem a forma:
select A1, A2, ..., An
from r1, r2, ..., rm
where P
Ai representa um atributo
Ri representa uma relação
P é um predicado.
Essa consulta é equivalente à expressão de álgebra
relacional.
A ,A ,,A ( P (r1  r2   rm ))
1
•
2
n
O resultado de uma consulta SQL é uma relação.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula select
 A cláusula select lista os atributos desejados no resultado de uma
consulta
 corresponde à operação projeção da álgebra relacional
 Exemplo: Encontre os nomes de todas as agências na relação
empréstimo:
select nome_agência
from empréstimo
 Na álgebra relacional, a consulta seria:
–
Õnome_agência (empréstimo)
 NOTA: Os nomes SQL não fazem distinção entre letras maiúsculas
e minúsculas.
 Algumas pessoas usam maiúsculas sempre que usamos o
estilo negrito.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula select (cont.)
 A SQL permite duplicatas nas relações bem como nos resultados
de consulta.
 Para forçar a eliminação de duplicatas, insira a palavra-chave
distinct após select.
 Encontre os nomes de todas as agências na relação empréstimo e
remova as duplicatas.
–
select distinct nome_agência
from empréstimo
 A palavra-chave all especifica que as duplicatas não são
removidas.
–
select all nome_agência
from empréstimo
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula select (cont.)
 Um asterisco na cláusula select indica “todos os atributos”
–
select *
from empréstimo
 A cláusula select pode conter expressões aritméticas envolvendo
os operadores +,
tuplas.
, * e / operando em constantes ou atributos de
 A consulta:
– select número_empréstimo, nome_agência, quantia * 100
from empréstimo
– retornará uma relação que é a mesma relação empréstimo, exceto
que o atributo quantia é multiplicado por 100.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula where
 A cláusula where especificam condições que o resultado precisa
satisfazer
 Corresponde ao predicado de seleção da álgebra relacional.
 Para encontrar todos os números de empréstimo para
empréstimos feitos na agência Perryridge com quantias
superiores a US$1200:
–
select número_empréstimo
from empréstimo
where nome_agência = ‘ Perryridge’ and quantia > 1200
 Os resultados da comparação podem ser combinados usando os
conectivos lógicos and, or, e not.
 As comparações podem ser aplicadas aos resultados das
expressões aritméticas.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula where (cont.)
 A SQL inclui um operador de comparação between
 Exemplo: Encontre o número de empréstimo dos
empréstimos com quantias entre US$ 90.000 e US$ 100.000
(ou seja, US$ 90.000 and US$ 100.000)
– select número_empr
from empréstimo
where quantia between 90000 and 100000
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A cláusula from
 A cláusula from lista as relações envolvidas na consulta
 Corresponde à operação de produto cartesiano da álgebra relacional.
 Encontre o produto cartesiano tomador X empréstimo
–
select *
from tomador, empréstimo
 Encontre os nomes de cliente, os números de empréstimo e as quantias de
empréstimo para todos os empréstimos da agência Perryridge
•
select nome_cliente, tomador.número_empréstimo, quantia
from tomador, empréstimo
where tomador.número_empréstimo =
empréstimo.número_empréstimo and
nome_agência = ‘Perryridge’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
A operação de renomeação
 A SQL permite renomear relações e atributos usando a cláusula
as:
–
nome-antigo as nome-novo
 Encontre o nome, o número de empréstimo e a quantia de
empréstimo de todos os clientes; renomeie a coluna
número_empréstimo como empréstimo_id.
•
select nome_cliente, tomador.número_empréstimo as id_empréstimo,
quantia
from tomador, empréstimo
where tomador.número_empréstimo = empréstimo.número_empréstimo
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Variáveis de tupla
 As variáveis de tupla são definidas na cláusula from por meio da
cláusula as.
 Encontre os nomes de cliente, os números de empréstimo e a
quantia para todos os clientes que têm um empréstimo em alguma
agência.
•
select nome_cliente, T.número_empréstimo, S.quantia
from tomador as T, empréstimo as S
where T.número_empréstimo = S.número_empréstimo
 Encontre os nomes de todas as agências que têm ativos maiores
do que pelo menos uma agência localizada em Brooklyn
•
select distinct T.nome_agência
from agência as T, agência as S
where T.ativo > S.ativo and S.cidade_agência = ‘Brooklyn’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Operações de string


A SQL incui um operador de correspondência de string para comparações em strings de
caractere. O operação like usa padrões que são descritos usando dois caracteres
especiais:

Porcentagem (%): O caractere % corresponde a qualquer substring.

Sublinhado (_): O caractere _ corresponde a qualquer caractere.
Encontre os nomes de todos os clientes cujos endereços de rua incluem a substring
‘Main’.
–

Localize o nome “Main%”
–

select nome_cliente
from cliente
where rua_cliente like ‘%Main%’
like ‘Main\%’ escape ‘\’
A SQL aceita várias operações de string como

concatenação (usando “||”)

conversão de maiúscula em minúscula (e vice-versa)

localização do tamanho da string, extração de substrings etc.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Ordenação da exibição de tuplas
 Liste em ordem alfabética todos os clientes que têm um empréstimo na
agência Perryridge
–
select distinct nome_cliente
from tomador, empréstimo
where tomador número_empréstimo = empréstimo.número_empréstimo
and
nome_agência = ‘Perryridge’
order by nome_cliente
 Podemos especificar desc para ordem decrescente ou asc para ordem
crescente, para cada atributo; a ordem crescente é o padrão.
 Exemplo: order by nome_cliente desc
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Duplicatas
 Em relações com duplicatas, a SQL pode definir quantas cópias
das tuplas aparecem no resultado.
 Versões multiconjunto de algumas operações de álgebra relacional
— dadas as relações multiconjunto r1 e r2,:
• 1.  (r1): Se existem c1 cópias da tupla t1 em r1, e t1 satisfaz a
seleção  então, existem c1 cópias de t1 em  (r1).
• 2. A (r ): Para cada cópia da tupla t1 em r1, existe uma cópia
da tupla A(t1) em A(r1), onde A(t1) indica a projeção da tupla
única t1.
• 3. r1 x r2 : Se existem c1 cópias da tupla t1 em r1 e c2 cópias da
tupla t2 em r2, existem c1 * c2 cópias da tupla t1.t2 em r1  r2.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Duplicatas (cont.)
 Exemplo: suponha que as relações r1 (A,B) e r2 (C) sejam as
seguintes:
–
r1 = {(1, a) (2,a)}
r2 = {(2), (3), (3)}
 Então, B(r1) seria {(a), (a)}, enquanto B(r1)  r2 seria
–
{(a,2), (a,2), (a,3), (a,3), (a,3), (a,3)}
 A semântica da duplicata SQL:
–
select A1,, A2, ..., An
from r1, r2, ..., rm
where P
–
é equivalente à versão de multiconjunto da expressão:
–
A1, A2,..., An(
Korth • Silberschatz • Sundarshan
P(r1
 r2  ...  rm))
Sistema de Banco de Dados, 5/E
Operações de conjunto
 As operações SQL union, intersect e except operam em relações e
correspondem às operações da álgebra relacional .
 Cada uma das operações acima elimina automaticamente as
duplicatas; para menter todas as duplicatas, use as versões de
multiconjunto correspondentes union all, intersect all e except all.
Suponha que uma tupla ocorre m vezes em r e n vezes em s; então,
ela ocorre:
 m + n vezes em r union all s
 min(m,n) vezes em r intersect all s
 max(0, m – n) vezes em r except all s
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Set Operations
 Encontre todos os clientes do banco que possuem um empréstimo, uma
conta ou as duas coisas no banco:
•
(select nome_cliente from depositante)
union
(select nome_cliente from tomador)
 Encontre todos os clientes que possuem um empréstimo e uma conta no
banco:
•
(select distinct nome_cliente from depositante)
intersect
(select distinct nome_cliente from tomador)
 Encontre todos os clientes que possuem uma conta mas nenhum
empréstimo no banco:
•
(select distinct nome_cliente from depositante)
except
•
(select nome_cliente from tomador)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções agregadas
 Essas funções operam no multiconjunto dos valores de uma
coluna de uma relação e retornam um valor
–
avg: valor médio
min: valor mínimo
max: valor máximo
sum: soma dos valores
count: número de valores
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções agregadas (cont.)
 Encontre o saldo médio das contas na agência Perryridge.
–
select avg (saldo)
from conta
where nome_agência = ‘Perryridge’
 Encontre o número de tuplas na relação cliente:
–
select count (*)
from cliente
 Encontre o número de depositantes do banco:
–
(select count (distinct nome_cliente)
from depositante
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções agregadas – group by
 Encontre o número de depositantes de cada agência.
•
(select nome_agência, count (distinct nome_cliente)
from depositante, conta
where depositante.número_conta = conta.número_conta
group by nome_agência
•
Nota: Os atributos na cláusula select fora das funções agregadas
precisam aparecer na lista group by.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Funções agregadas – Cláusula having
 Encontre o nome de todas as agências onde o saldo médio é
maior que US$ 1200:
•
select nome_agência, avg (saldo)
from conta
group by nome_agência
having avg (saldo) > 1200
•
Nota: Os predicados na cláusula having são aplicados após a formação de
grupos, enquanto os predicados na cláusula where são aplicados antes da
formação de grupos.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Valores nulos
 É possível que as tuplas tenham um valor nulo, indicado por nulo,
para algum(ns) de seus atributos

nulo significa um valor desconhecido ou que um valor não existe.
 O predicado is null pode ser usado para verificar a presença de valores
nulos.
 Exemplo: Encontre todos os números de empréstimo na relação
empréstimo com valores nulos para quantia:
–
select número_empréstimo
from empréstimo
where quantia is null
 O resultado de qualquer expressão aritmética envolvendo nulo é nulo
 Exemplo: 5 + nulo retorna nulo
 Entretanto, as funções aregadas simplesmente ignoram nulos
 Mais no próximo slide
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Valores nulos e lógica de três valores
 Qualquer comparação com nulo retorna desconhecido
 Exemplo: 5 < nulo or nulo <> nulo
or
nulo = nulo
 Lógica de três valores usando o valor de verdade desconhecido:
 OR: (desconhecido or verdadeiro) = verdadeiro, (desconhecido or falso)
= desconhecido
(desconhecido or desconhecido) = desconhecido
 AND: (verdadeiro and desconhecido) = desconhecido,
(falso and
desconhecido) = falso,
(desconhecido and desconhecido) = desconhecido
 NOT: (not desconhecido) = desconhecido
 “P is unknown” evaluates to true if predicate P evaluates to
desconhecido
 Resultado do predicado da cláusula where é tratado como falso se
avaliar para desconhecido
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Valors nulos e aregados
 Total das quantias de empréstimo
–
select sum (quantia )
from empréstimo
 A instrução acima ignora quantias nulas
 O resultado é nulo se não houver uma quantia não nula
 Todas as operações agregadas exceto count(*) ignoram tuplas com
valores nulos nos atributos agregados.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Subconsultas aninhadas
 A SQL fornece um mecanismo para aninhar subconsultas.
 Uma subconsulta é uma expressão select-from-where que é
aninhada dentro de outra consulta.
 Um uso comum das subconsultas é realizar testes para
participação de conjuntos, fazer comparações de conjuntos e
determinar cardinalidade de conjuntos.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo

Encontre todos os clientes que possuem uma conta e um
empréstimo no banco.
select distinct nome_cliente
from tomador
where nome_cliente in (select nome_cliente
from depositante)
 Encontre todos os clientes que possuem um empréstimo no banco,
mas que não têm uma conta no banco.
select distinct nome_cliente
from tomador
where nome_cliente) not in (select nome_cliente
from depositante)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo
 Encontre todos os clientes que possuem uma conta e um
empréstimo na agência Perryridge
select distinct nome_cliente
from tomador, empréstimo
where tomador.número_cliente = empréstimo.número_empréstimo and
nome_agência = ‘Perryridge’ and
(nome_agência, nome_cliente) in
(select nome_agência, nome_cliente
from depositante, conta
where depositante.número_conta
= conta.número_conta)
 Nota: A consulta acima pode ser escrita de uma maneira muito
mais simples. A formulação acima é simplesmente para ilustrar
os recursos da SQL.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Comparação de conjuntos

Encontre os nomes de todas as agências que possuem ativos maiores
do que o ativo de pelo menos uma agência localizada em Brooklyn.
select distinct T.nome_agência
from agência as T, agência as S
where T.ativo > S.ativo and
S.cidade_agência = ‘Brooklyn’

Mesma consulta usando a cláusula > some
– Select ome_agência
from agência
where ativo > some
(select ativo
from agência
where cidade_agência = ‘Brooklyn’)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Definição da cláusula some

•
F <comp> some r t  r tal que (F
<comp> t )
Onde <comp> pode ser:     
(5 < some
•
(5 < some
•
(5 = some
•
(5  some
5)
0
5
6
0
5
0
5
0
5
) = verdadeiro
leia: 5 < alguma tupla na
relação)
) = falso
) = verdadeiro
) = verdadeiro (já que 0 
•
(= some)  in
•
Entretanto, ( some)  not in
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo

Encontre os nomes de todas as agências que possuem ativos
maiores do que todas as agências localizadas em Brooklyn.
select nome_agência
from agência
where ativo > all (select ativo
from agência
where cidade_agência = ‘Brooklyn’)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Definição da cláusula all

F <comp> all r t  r (F <comp> t)
(5 < all
0
5
6
6
10
) = falso
•
(5 < all
•
(5 = all
•
(5  all
•
( all)  not in
•
Entretanto, (= all)  in
4
5
4
6
) = verdadeiro
) = falso
) = verdadeiro (já que 5  4 e 5  6)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Teste para relações vazias
 A construção exists retorna o valor true se a subconsulta
de argumento não é vazia.
 exists r  r  Ø
 not exists r  r = Ø
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo
 Encontre todos os clientes que têm uma conta em todas as agências
localizadas em Brooklyn.
select distinct S.nome_cliente
from depositante as S
where not exists ((select nome_agência
from agência
where cidade_agência = ‘Brooklyn’)
except
(select R.nome_agência
from depositante as T, conta as R
where T.número_conta = R.número_conta and
S.nome_cliente = T.nome_cliente))
 Note que X – Y = Ø  X  Y
 Nota: Não é possível escrever essa consulta usando = all e suas variantes.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Teste para ausência de tuplas duplicatas
 A construção unique testa se uma subconsulta possui alguma
tupla duplicata em seu resultado.
 Encontre todos os clientes que possuem pelo menos uma conta
na agência Perryridge.
– select T.nome_cliente
from depositante as T
where unique (select R.nome_cliente
from conta, depositante as R
where T.nome_cliente = R.nome_cliente and
R.número_conta = conta.número_conta and
conta.nome_agência = ‘Perryridge’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo

Encontre todos os clientes que têm pelo menos duas contas na
agência Perryridge.
select distinct T.nome_cliente
from depositante as T
where not unique (select R.nome_cliente
from conta, depositante as R
where T.nome_cliente = R.nome_cliente and
R.número_conta = conta.número_conta and
conta.nome_agência = ‘Perryridge’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações derivadas
 A SQL permite que uma expressão de subconsulta seja usada
na cláusula from
 Encontre o saldo médio das agências onde o saldo médio é
maior que US$1200.
– select nome_agência, saldo_médio
from (select nome_agência, avg (saldo)
from conta
group by nome_agência)
as média_agência (nome_agência, saldo_médio)
where saldo_médio > 1200
•
Note que não precisamos usar a cláusula having, já que a
subconsulta na cláusula from calcula o saldo médio e seu
resultado é nomeado como média_agência; podemos usar os
atributos de média_agência diretamente na cláusula where.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Cláusula With
 A cláusula with fornece uma maneira de definir uma view
temporária cuja definição está disponível apenas para a
consulta onde a cláusula with ocorre.
 Encontre todas as contas com o saldo máximo.
with saldo_máximo (valor) as
select max (saldo)
from conta
select número_conta
from conta, saldo_máximo
where conta.saldo = saldo_máximo.valor
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta complexa usando a cláusula With

Encontre todas as agências onde o depósito em conta total é maior
do que a média dos depósitos totais em todas as agências.
with total_agência (nome_agência, valor) as
select nome_agência, sum (saldo)
from conta
group by nome_agência
with média_total_agência (valor) as
select avg (valor)
from total_agência
select nome_agência
from total_agência, média_total_agência
where total_agência.valor >= média_total_agência.valor
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Views

Em alguns casos, não é desejável que todos os usuários vejam o
modelo lógico inteiro (ou seja, todas as relações reais armazenadas no
banco de dados.)

Considere uma pessoa que precisa saber o número de empréstimo e o
nome da agência de um cliente, mas não precisa ver o valor do
empréstimo. Essa pessoa deve ver uma relação descrita, na SQL, por
(select nome_cliente, número_empréstimo
from tomador, empréstimo
where tomador.número_empréstimo =
empréstimo.número_empréstimo )

Uma view fornece um mecanismo para ocultar certos dados da visão de
certos usuários.

Qualquer relação que não seja do modelo conceitual mas é visível a um
usuário como uma “relação virtual” é chamada uma view.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Definição de view

Uma view é definida usando o comando create view, que tem a forma
– create view v as <expressão de consulta>
– onde <expressão de consulta> é qualquer expressão de consulta
válida. O nome da view é representado por v.

Uma vez uma view é definida, o nome da view pode ser usado para se
referir à relação virtual gerada por ela.

Definição de view não é o mesmo que criar uma nova relação
avaliando a expressão de consulta.
 Em vez disso, uma definição de view causa o salvamento de uma
expressão; a expressão é substituída nas consultas usando a
view.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consultas de exemplo

Uma view consistindo nas agências e seus clientes
create view todos_clientes as
(select nome_agência, nome_cliente
from depositante, conta
where depositante.número_conta = conta.número_conta)
union
(select nome_agência, nome_cliente
from tomador, empréstimo
where tomador.número_empréstimo = empréstimo.número_empréstimo

Encontre todos os clientes da agência Perryridge
select nome_cliente
from todos_clientes
where nome_agência = ‘Perryridge’
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Views definidas usando outras views

Uma view pode ser usada na expressão que define outra view

Diz-se que uma relação de view v1 depende diretamente de uma relação de
view v2 se v2 é usada na expressão que define v1

Diz-se que uma relação de view v1 depende da relação de view v2 se v1
depende diretamente de v2 ou se existe um caminho de dependências de v1 a
v2

Diz-se que uma relação de view v é recursiva se ela depende de si mesma.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Expansão de view
•
Uma forma de definir o significado das views definidas em termos de outras
views.
•
Façamos a view v1 ser definida por uma expressão e1 que pode, ela própria,
conter usos das relações de view.
•
A expansão de view de uma expressão repete o seguinte passo de
substituição:
repeat
•
Encontre qualquer relação de view vi em e1
•
Substitua a relação de view vi pela expressão que define vi
until (até que) nenhuma outra relação de view esteja presente em e1
•
Desde que as definições de view não são recursivas, esse loop irá terminar.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Modificação do banco de dados – exclusão

Exclua todas as tuplas conta na agência Perryridge
delete from conta
where nome_agência = ‘Perryridge’

Exclua todas as contas em cada agência localizada em Needham.
delete from conta
where nome_agência in (select nome_agência
from agência
where cidade_agência = ‘Needham’)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Consulta de exemplo

Exclua os registros de todas as contas com saldos abaixo da média
no banco.
delete from conta
where saldo < (select avg (saldo)
from conta)

Problema: Conforme excluímos tuplas do depósito, o saldo médio é
modificado.
 Solução usada na SQL:
1 Primeiro, calcule o saldo avg e encontre todas as tuplas a serem excluídas
2 Em seguida, exclua todas as tuplas encontradas acima (sem recalcular
avg ou retestar as tuplas)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Modificação do banco de dados – inserção

Insira uma nova tupla em conta
– insert into conta
values (‘A-9732’, ‘Perryridge’, 1200)
ou equivalentemente
insert into conta (nome_agência, saldo, número_conta)
values (‘Perryridge’, 1200, ‘A-9732’)

Insira uma nova tupla em conta com saldo definido como nulo
–
insert into conta
values (‘A-777’,‘Perryridge’, nulo )
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Modificação do banco de dados – inserção

Ofereça uma nova conta de poupança no valor de US$ 200 como um presente
para todos os clientes de empréstimo da agência Perryridge Deixe que o
número de empréstimo sirva como o número de conta para a conta de
poupança.
–

insert into conta
select número_empréstimo, nome_agência, 200
from empréstimo
where nome_agência = ‘Perryridge’
insert into depositante
select nome_cliente, número_empréstimo
from tomador, empréstimo
where tomador.número_empréstimo = empréstimo.número_empréstimo and
nome_agência = ‘Perryridge’
A instrução select from where é avaliada totalmente antes de quaisquer dos seus
resultados serem inseridos na relação (caso contrário, consultas como
insert into tabela1 select * from tabela1
causariam problemas
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Modificação do banco de dados –
atualizações

Aumente todas as contas com saldos acima de US$ 10000 em 6 por cento,
enquanto todas as outras recebem 5 por cento.
 Escreva duas instruções update:
update conta
set saldo = saldo * 1,06
where saldo >= 10000
update conta
set saldo = saldo * 1,05
where saldo <= 10000
 A ordem é importante
•
Pode ser feito melhor usando a instrução case (próximo slide)
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Instrução case para atualizações
condicionais

Mesma consulta de antes: Aumente todas as contas com saldos
acima de US$10000 em 6 por cento, enquanto todas as outras
recebem 5 por cento.
update conta
set saldo = case
when saldo <= 10000 then saldo * 1,05
else saldo * 1,06
end
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Atualização de uma view

Crie uma view de todos os dados de empréstimo na relação
empréstimo, ocultando o atributo quantia
–

create view agência_empréstimo as
select nome_agência, número_empréstimo
from empréstimo
Insira uma nova tupla em agência_empréstimo
insert into agência_empréstimo
values (‘Perryridge’, ‘L-307’)
Essa inserção precisa ser representada pela inserção da tupla
(‘L-307’, ‘Perryridge’, nulo )
na relação empréstimo
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Atualizações através de Views (cont.)

Algumas atualizações através de views são impossíveis de se
traduzirem em atualizações nas relações de banco de dados
 create view v as
select nome_agência from conta
–

insert into v values (‘L-99’, ‘ Downtown’, ‘23’)
Outras não podem ser traduzidas unicamente
 insert into todos_clientes values (‘ Perryridge’, ‘John’)
 Precisa escolher empréstimo ou conta, e criar um novo
número de empréstimo/conta!

A maioria das implementações SQL permite atualizações apenas em
views simples (sem agregados) definidas em uma única relação.
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações juntadas**

As operações de junção tomam duas relações e retornam como resultado outra relação.

Essas operações adicionais normalmente são usadas como expressões de subconsulta
na cláusula from.

Condição de junção – define que tuplas nas duas relações correspondem e que atributos
estão presentes no resultado da junção.
•
Tipo de junção – define como são tratadas as tuplas em cada relação que não
corresponde a qualquer tupla na outra relação (com base na condição de junção).
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações juntadas – datasets
para exemplos

Relação empréstimo

Relação tomador

Nota: Informações de tomador faltando para L-260 e informações
de empréstimo faltando para L-155
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações juntadas – Exemplos
empréstimo inner join tomador on
empréstimo.número_empréstimo = tomador.número_empréstimo
empréstimo left outer join tomador on
empréstimo.número_empréstimo = tomador.número_empréstimo
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações juntadas – Exemplos
empréstimo natural inner join tomador
empréstimo natural right outer join tomador
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Relações juntadas – Exemplos
empréstimo full outer join tomador using (número_empréstimo)
Encontre todos os clientes que possuem uma conta ou um empréstimo
(mas não ambos) no banco.
select nome_cliente
from (depositante natural full outer join tomador)
where número_conta is null or número_empréstimo is null
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Fim do Capítulo 3
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Figura 3.1: Esquema do banco de dados
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Figura 3.3: Tuplas inseridas
em empréstimo e tomador
Korth • Silberschatz • Sundarshan
Sistema de Banco de Dados, 5/E
Figura 3.4:
As relações empréstimo e tomador
Korth • Silberschatz • Sundarshan