Introdução à SQL e Banco de Dados

Prévia do material em texto

21/09/2010
1
Prof. Esp. André Ricardo Jovetta
1Programação em Banco de Dados
� Logo abaixo temos um exemplo de um banco de dados 
de uma empresa da área bancária:
◦ agencia = (nome_agencia, cidade_agencia, ativo)
◦ cliente = (nome_cliente, rua_cliente, cidade_cliente)
◦ emprestimo = (numero_emprestimo, nome_agencia, quantia)
◦ tomador = (nome_cliente, numero_emprestimo)
◦ conta = (numero_conta, nome_agencia, saldo)
◦ depositante = (nome_cliente, numero_conta)
2Programação em Banco de Dados
� A estrutura básica de uma expressão em SQL consiste em 
três cláusulas: select, from e where.
◦ A cláusula select corresponde á operação de projeção da álgebra relacional. 
Ela é usada para relacionar os atributos desejados no resultado de uma 
consulta.
◦ A cláusula from corresponde à operação de produto cartesiano da álgebra 
relacional. Ela associa a relações que serão pesquisadas durante a evolução 
de uma expressão.
◦ A cláusula where corresponde à seleção do predicado da álgebra relacional. 
Ela consiste em um predicado envolvendo atributos da relação que aparece 
na cláusula from
3Programação em Banco de Dados
� O Resultado de uma consulta de SQL é, naturalmente, uma 
relação. Consideremos uma consulta simples usando nosso 
exemplo de banco:
� “encontre os nomes de todas as agências da relação empréstimo.”
select nome_agencia
from emprestimo
4Programação em Banco de Dados
21/09/2010
2
� Nos casos em que desejamos forçar a eliminação de duplicidade, 
podemos inserir a palavra distinct depois de select. Poderemos 
reescrever a consulta anterior da seguinte forma:
Select distinct nome_agencia
from emprestimo
5Programação em Banco de Dados
� A cláusula select na forma select * indica que todos os atributos 
de todas as relações que aparecem na cláusula from devem ser 
selecionados.
Select * from emprestimo
� Pode conter expressões aritméticas envolvendo os operadores +,-, 
* e /, e operandos constantes ou atributos das tuplas. Por exemplo, 
a consulta:
Select nome_agencia, numero_emprestimo, quantia * 100 
from emprestimo
6Programação em Banco de Dados
� Vejamos como ilustrar o uso da cláusula where na SQL. Considere a 
consulta:
“Encontre todos os números de empréstimos feitos na agência Perryridge
com quantia emprestados acima de 1.200 dólares.”
Select numero_emprestimo
from emprestimo
where nome_agencia = “Perryridge” and quantia >1200
� A SQL usa conectores lógicos and, or e not – em vez dos símbolos 
matemáticos ^, v e __ , na cláusula where . Os operandos dos 
conectivos lógicos podem ser expressões envolvendo operadores de 
comparação ,>=, = e .
7Programação em Banco de Dados
� A SQL possui o operador de comparação Between para simplificar 
a cláusula where que especifica que um valor pode ser menor ou 
igual a algum valor e maior ou igual a algum outro valor.
� “Encontrar os números dos empréstimos cujos as quantias estejam 
entre 900 dólares e 1000 dólares.”
Select numero_emprestimo
from emprestimo
where quantia between 900 and 1000
� Em vez de:
Select numero_emprestimo
from emprestimo
where quantia = 900
Similarmente, podemos usar o operador de comparação not between
8Programação em Banco de Dados
21/09/2010
3
� A cláusula from por si só define um produto cartesiano das relações 
na cláusula.
“para todos os clientes que tenham um empréstimo em um banco, 
encontre seus nomes e números de empréstimos.”
Select distinct nome_cliente, tomador.numero_emprestimo
from tomador, emprestimo
where tomador.numero_emprestimo=emprestimo.numero_emprestimo
9Programação em Banco de Dados
“Encontre os nomes e números dos empréstimos de todos os 
clientes que tenham um empréstimo na agência Perryridge.”
Select distinct nome_cliente, tomador.numero_emprestimo
from tomador, emprestimo
where tomador.numero_emprestimo=emprestimo.numero_emprestimo
and nome_agencia = ‘Perryridge’
10Programação em Banco de Dados
� A SQL proporciona um mecanismo para rebatizar tanto relações 
quanto atributos, usando a cláusula asasasas, da seguinte forma:
nome_antigo asasasas nome_novo
� Por exemplo, se desejarmos que o nome do atributo 
numero_emprestimonumero_emprestimonumero_emprestimonumero_emprestimo seja substituído pelo nome id_emprestimoid_emprestimoid_emprestimoid_emprestimo, 
podemos reescrever a consulta precedente como:
SelectSelectSelectSelect distinctdistinctdistinctdistinct nome_cliente, tomador.numero_emprestimo as as as as 
id_emprestimo
fromfromfromfrom tomador, emprestimo
wherewherewherewhere tomador.numero_emprestimo =
emprestimo. numero_emprestimo
andandandand nome_agencia = “Perryridge”
11Programação em Banco de Dados
� As operações em strings mais usadas são as checagens para 
verificação de coincidências de pares, usando o operador like. 
Identificaremos esses pares por meio do uso de caracteres especiais:
◦ Porcentagem (%): o caracter % compara qualquer substring.
◦ Comparações desse tipo são sensíveis ao tamanho das letras. Para 
ilustrar a comparação por pares, consideremos os seguintes exemplos:
� “Perry%” corresponde a qualquer string que comece com “Perry”.
� “%idge%” corresponde a qualquer string que possua uma substring “idge”, 
por exemplo, “Perryridge”, “Rock Ridge” e “Mianus Bridge”.
12Programação em Banco de Dados
21/09/2010
4
“Encontre os nomes de todos os clientes cujas ruas possuam a 
substring ‘pri’”.
� Essa consulta pode ser escrita assim:
Select nome_cliente
from cliente
where rua_cliente like ‘%pri%’
� A SQL também permite-nos pesquisar diferenças em vez de 
coincidências, por meio do uso do operador de comparação not like.
13Programação em Banco de Dados
� A SQL oferece um controle sobre a ordenação por meio da qual as tuplas
de uma relação serão apresentadas. 
� A cláusula order by faz com que as tuplas do resultado de uma consulta 
apareçam em uma determinada ordem.
“Listar em ordem alfabética todos os clientes que tenham um empréstimo 
na agência Perryridge”
Select distinct nome_cliente
From tomador, emprestimo
where tomador.numero_emprestimo= 
empréstimo.numero_emprestimo
and nome_agencia = “Perryridge” 
order by nome_cliente
14Programação em Banco de Dados
� Por padrão, a cláusula order by relaciona os itens em ordem 
ascendente. 
� Para a especificação da forma de ordenação, devemos indicar desc
para ordem descendente e asc para ordem ascendente.
“Listar a relação empréstimo inteira por ordem descendente de quantia.”
Select *
from emprestimo
order by quantia desc, numero_emprestimo asc
15Programação em Banco de Dados
� são funções que tomam uma coleção (um conjunto ou subconjunto) 
de valores como entrada, retornando um valor simples.
� A SQL oferece cinco funções agregadas pré-programadas:
◦ Média (average): avg.
◦ Mínimo (minimum): min.
◦ Máximo (maximum): max.
◦ Total (total): sum.
◦ Contagem (count): count.
Programação em Banco de Dados 16
21/09/2010
5
� A entrada para sum e avg precisa ser um conjunto de números, mas 
as outras operações podem operar com conjunto de tipos não-
muméricos, como strings e semelhantes.
� “Encontre a média dos saldos em contas na agência Brighton”
Select avg (saldo)
from conta 
where nome_agencia = ‘Brighton’
Programação em Banco de Dados 17
� “Encontre a média dos saldos nas contas de cada uma das 
agências do banco.”
Select nome_agencia, avg (saldo)
from conta 
group by nome_agencia
Programação em Banco de Dados 18
� “Encontre todos os números dos depositantes de cada agência.”
Select nome_agencia, count (distinct nome_cliente) 
from depositante, conta 
where depositante.numero_conta = conta.numero_conta
group by nome_agencia
Programação em Banco de Dados 19
� Se uma cláusula where e uma cláusula having aparecem na 
mesma consulta, o predicado que aparece em where é aplicado 
primeiro;
� As tuplas que satisfazem a cláusula where são colocadas em 
grupos por meio da cláusula group by;
� A cláusula having é aplicada cada grupo;
� Os grupos que não satisfizerem o predicado da cláusula having
serão removidos;
� Os gruposremanescentes são usados pela cláusula select pra 
criação das tuplas da relação resultante da consulta;
Programação em Banco de Dados 20
21/09/2010
6
“Listar quais são as agências que possuem média dos saldos 
aplicados em conta maior ou igual a 700 dólares”
� Essa condição não se aplica a uma única tupla, mas em cada 
grupo determinado pela cláusula group by.
� Para exprimir tal consulta, usamos a cláusula having da SQL.
Select nome_agencia, avg (saldo) 
from conta
group by nome_agencia
having avg (saldo) > = 700
Sistema de Banco de Dados 21
� Às vezes, desejamos tratar a relação como um todo, como um 
grupo simples.
� Nesses casos, não usamos cláusula group by.
“encontre a média dos saldos de todas as contas”.
Select avg (saldo) 
from conta
Programação em Banco de Dados 22
� Usamos a função agregada count com muita freqüência para 
contar o número de tuplas em uma relação;
� A notação para essa função em SQL é count (*).
“Encontrar o número de tuplas da relação cliente”
Select count (*) 
from cliente
� A SQL não permite o uso do distinct com count (*). É válido usar 
distinct com max e min, mesmo não alterando o resultado.
Programação em Banco de Dados 23
� A palavra-chave especial null é um predicado para testar a 
existência de valores nulos;
� “Encontrar todos os números de empréstimos que aparecem na 
relação empréstimo com valores nulos para quantia”.
Select numero_emprestimo
from emprestimo
where quantia is null
� O predicado is not null testa a ausência de valores nulos.
Programação em Banco de Dados 24
21/09/2010
7
� A SQL proporciona um mecanismo para o aninhamento de 
subconsultas;
� Uma subconsulta é uma expressão select-from-where aninhada 
dentro de outra consulta;
� O conectivo in testa os membros de um conjunto, no qual o 
conjunto é a coleção de valores produzidos pela cláusula select.
� O conectivo not in verifica a ausência de membros de um conjunto;
Programação em Banco de Dados 25
“Encontre todos os clientes que tenham tanto conta quanto 
empréstimo no banco”.
� Começaremos por encontrar todos os correntistas, escrevendo esta 
subconsulta:
(select nome_cliente
from depositante)
� Precisamos, agora, encontrar todos os clientes que contraíram 
empréstimos no banco e que aparecem na lista dos correntistas 
obtidos pela subonculta;
Programação em Banco de Dados 26
� Faremos isso por meio do aninhamento da subconsulta em uma 
consulta externa;
Select distinct nome_cliente
from tomador
where nome_cliente in (select nome_cliente
from depositante)
Programação em Banco de Dados 27
� Ilustramos o uso do construtor not in;
“Encontrar todos os clientes que tenha um empréstimo no banco, mas 
que não tenham uma conta o banco”
select distinct nome_cliente
from tomador
where nome_cliente not in (select nome_cliente
from depositante)
Programação em Banco de Dados 28
21/09/2010
8
� Os operadores in e not in podem também ser usados em conjuntos 
enumerados;
“Selecionar os nomes dos clientes que tenham um empréstimo no 
banco e cujos nomes não sejam nem “Smith” nem “Jones”.
Select distinct nome_cliente
from tomador
where nome_cliente not in (‘Smith’,’Jones’)
Programação em Banco de Dados 29
� Definimos uma visão em SQL usando o comando create view.
� Para definir a visão, precisamos dar-lhe um nome e definir a 
consulta que processará essa visão;
� A forma do comando create view é:
Create view v as 
� Em que é qualquer expressão de 
consulta válida e o nome da visão é representado por v.
Programação em Banco de Dados 30
“Considere uma visão de todas as agências e nomes de clientes que 
tenham uma conta”.
Create view todos_clientes as
select nome_cliente, nome_agencia
from depositante, conta
where depositante.numero_conta = conta.numero_conta;
Programação em Banco de Dados 31
� Os nomes de visão podem aparecer em qualquer lugar onde o nome 
de uma relação aparece;
� Usando a visão todos_clientes, podemos encontrar todos os clientes 
da agência Perryridge, escrevendo:
select nome_cliente
from todos_clientes
where nome_agencia = ‘Perryridge’
� A cláusula create view cria uma definição de visão em um banco de 
dados e essa definição fica no banco até que um comando drop view
nome_visão seja executado.
Programação em Banco de Dados 32