SQL
SQL
• Tipos de domínios básicos
– char(n) - character, varchar(n) – character varying, int
– integer, smallint, numeric(p,d), real, double
precision, float(n)
• Definição básica de esquema na SQL
create table r(A1D1, A2D1,...,AnDn),
<restrição-de-integridade1>
...,
<restrição-de-integridadek>
SQL
– A chave primaria é uma restrição de integridade.
Primary key(Aj1, Aj2,...,Ajm)
Definição de Dados SQL para parte do Banco de Dados de uma
Empresa Bancária
Esquema:
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)
SQL
create table cliente
(nome_cliente char(20),
rua_cliente char(30),
cidade_cliente char(30),
primary key (nome_cliente))
create table agencia
(nome_agencia char(15),
cidade_agencia char(30),
ativo numerc(16,2),
primary key (nome_agencia))
create table conta
(numero_conta char(10),
nome_agencia char(15),
saldo numeric(12,2),
primary key (numero_conta))
create table depositante
(nome_cliente char(20),
Numero_conta char(10),
primary key (nome_cliente, numero_conta))
SQL
Inserção de Dados
insert into conta values(‘A-9732’, ’Perryridge’, 1200)
Deleção de Dados
delete from conta
Deleção de Tabela
drop table conta
Acrescentar Atributos a uma Relação
alter table conta add coluna_nome Domínio
Excluir Atributos de uma Relação
alter table conta drop coluna_nome
SQL
Estrutura Básica das Consultas SQL
select - Projeção da Álgebra Relacional
from – Operação Produto Cartesiano na Álgebra Relacional
where – Predicado na Álgebra Relacional
Notação SQL:
select A1, A2, ..., Na
from r1, r2, ..., rn
where P
Notação Álgebra Relacional:
πA1,A2, ..., Na ( σ p (r1x21xr3 ... X rm ))
SQL
A cláusula select
select nome_agencia
from emprestimo
ou
select all nome_agencia
from emprestimo
Eliminação de duplicatas:
select distinct nome_agencia
from emprestimo
Expressões aritméticas
select numero_emprestimo, quantia*100
from emprestimo
SQL
A cláusula where
select numero_emprestimo
from emprestimo
where nome_agencia = ‘Perryridge’ and quantia > 1200
select numero_emprestimo
from emprestimo
where quantia between 90000 and 100000 (Pode-se usar not between)
ou
select numero_emprestimo
from emprestimo
where quantia <= 100000 and quantia >= 90000
SQL
A cláusula from
select nome_cliente, tomar.numero_emprestimo,quantia
from tomador,emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
select nome_cliente, tomar.numero_emprestimo,quantia
from tomador,emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
and nome_agencia= ‘Perryridge’
SQL
Operação de Renomeação
nome_antigo as nome_novo
select nome_cliente, tomador.numero_emprestimo, quantia
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
Com Variáveis de Tuplas T e S
select nome_cliente, T.numero_emprestimo as id_emprestimo, S.quantia
from tomador as T, emprestimo as S
where T.numero_emprestimo = S.numero_emprestimo
select distinct T.nome_agencia
from agencia as T, agencia as S
where T.ativo > S.ativo and S.cidade_agencia = ‘Brooklyn’
SQL
Operações de string
String usa sinal de apóstrofo – ‘Perryridge’
Operador like (not like) – Verifica correspondência de padrões
Padrões são descritos com:
% (Porcentagem) - Qualquer substring
_ (Sublinhado) – Qualquer caractere
Ex: ‘Perry%’, ‘%idge%’, ‘_ _ _’, ‘_ _ _ %’
select nome_cliente
from cliente
where rua_cliente like ‘%Main%’
Operador de escape \
SQL
Operações de string
Ex: ‘ab\%cd%’ e ‘ab\\cd%’
Funções em strings:
- || - concatenação
- upper() – string em maiúscula
- lower() – string minúscula
SQL
Ordenação da exibição de tuplas
select nome_cliente
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo_numero_emprestimo and
nome_agencia = ‘Perryridge’
order by nome_cliente (Ordem crescente)
desc – decrescente
asc – Crescente
select *
from emprestimo
order by quantia desc, numero_emprestimo asc
SQL
Operações de conjunto
Union – U
Intersect – ∩
Except - –
(select nome_cliente
from depositante)
union
(select nome_cliente
from tomador)
(select nome_cliente
from depositante)
union all
(select nome_cliente
from tomador)
UNIÃO
(duplicatas são eliminadas)
(duplicatas não são eliminadas)
SQL
Operações de conjunto
INTERSEÇÃO
(select distinct nome_cliente
from depositante)
intersect
(duplicatas são eliminadas)
(select distinct nome_cliente
from tomador)
(select nome_cliente
from depositante)
intersect all
(select nome_cliente
from tomador)
(duplicatas não são eliminadas)
SQL
Operações de conjunto
DIFERENÇA
(select distinct nome_cliente
from depositante)
except
(duplicatas são eliminadas)
(select nome_cliente
from tomador)
(select nome_cliente
from depositante)
except all
(select nome_cliente
from tomador)
(duplicatas não são eliminadas)
SQL
Operações de conjunto
FUNÇÕES AGREGADAS
Average: avg
Minimum: min
Maximum: max
Total: sum
Count: count
select avg(saldo)
from conta
where nome_agencia = ‘Perryridge’
SQL
Operações de conjunto
FUNÇÕES AGREGADAS
select nome_agencia, avg(saldo)
from conta
group by nome_agencia
select nome_agencia, count(distinct nome_cliente)
from depositante, conta
where depositante.numero_conta = conta.numero_conta
group by nome_agencia
select nome_agencia, avg(saldo)
from conta
group by nome_agencia
having avg(saldo)> 1200
SQL
Operações de conjunto
FUNÇÕES AGREGADAS
select avg(saldo)
from conta
select count(*) //Não se usa distinct
from cliente
select depositante.nome_cliente, avg(saldo)
from depositante, conta, cliente
where depositante.numero_conta = conta.numero_conta and
depositante.nome_cliente=‘Harrison’
group by depositante.nome_cliente
having count (distinct depositante.numero_conta) >= 3
SQL
Valores Nulos
select numero_emprestimo
from emprestimo
where quantia is null
(is not null)
A SQL trata como unknown o resultado de qualquer comparação envolvendo um
valor nulo
true and unknown = unknown
false and unknown = false
Unknown and unknown = unknown
true or unknwon = true
false or unknown = unknown
unknown or unknown = unknown
not unknown = unknown
SQL
Valores Nulos
select sum (quantia)
from emprestimo
//O operador sum ignora valores nulos
Todas as funções agregadas exceto count(*) ignoram valores nulos em sua coleção
de entrada.
Subconsultas aninhadas
Participação de Conjuntos
select distinct nome_cliente
from tomador
where nome_cliente in (select nome_cliente from depositante)
SQL
Participação de Conjuntos
select distinct nome_cliente
from tomador, emprestimo
where tomador.numero_cliente = emprestimo.numero_emprestimo and
nome_agencia=‘Perryridge’ and (nome_agencia, nome_cliente) in
(select nome_agencia, nome_cliente
from depositante, conta
where depositante.numero_conta = conta.numero_conta)
SQL
Participação de Conjuntos
select distinct nome_cliente
from tomador
where nome_cliente not in (select nome_cliente from depositante)
select distinct nome_cliente
from tomador
where nome_cliente not in (‘Smith’, ‘Jones’)
SQL
Comparação de Conjuntos
select distinct T.nome_agencia
from agencia as T, agencia as S
where T.ativo > S.ativo and S.cidade_agencia = ‘Brooklyn’
ou
select nome_agencia
from agencia
where ativo > some (select ativo
from agencia
where cidade_agencia = ‘Brooklyn’
Outros: < some, >= some, = some, <>some =some é o mesmo que in
SQL
Comparação de Conjuntos
select nome_agencia
from agencia
where ativo > all (select ativo
from agencia
where cidade_agencia = ‘Brooklyn’
Outros: < all, >= all, = all, <>all <>all é o mesmo que not in
SQL
Comparação de Conjuntos
select nome_agencia
from conta
group by nome_agencia
having avg(saldo) >=all (select avg(saldo)
from conta
group by nome_agencia)
Obs.: As funções agregadas não podem ser compostas
max(avg(...))
SQL
Teste de relações vazias
select nome_cliente
from tomador
where exists (select *
from depositante
where depositante.nome_cliente=tomador.nome_cliente)
select distinct S.nome_cliente
from depositante as S
where not exists((select nome_agencia
from agencia
where cidade_agencia=‘Brooklyn’)
except
(select R.nome_agencia
from depositante as T, conta as R
where T.numero_conta = R.numero_conta and S.nome_cliente = T.nome_cliente))
SQL
Teste da Ausência de tuplas duplicatas
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.numero_conta = conta.numero_conta and
conta.nome_agencia = ‘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.numero_conta = conta.numero_conta
and conta.nome_agencia = ‘Perryridge’)
SQL
Consultas complexas
Relações Derivadas e Cláusula with
Relações Derivadas
(select nome_agencia, avg(saldo)
from conta
group by nome_agencia)
as media_agencia(nome_agencia, saldo_medio)
select nome_agencia, saldo_medio
from (select nome_agencia, avg(saldo)
from conta
group by nome_agencia)
as media_agencia(nome_agencia, saldo_medio)
where saldo_medio > 1200
SQL
Consultas complexas
Relações Derivadas e Cláusula with
Relações Derivadas
select max(saldo_total)
from (select nome_agencia, sum(saldo)
from conta
group by nome_agencia) as total_agencia(nome_agencia, saldo_total)
SQL
A cláusula with
with saldo_maximo(valor) as
select max(saldo)
from conta
select numero_conta
from conta, saldo_maximo
where conta.saldo = saldo_maximo.valor
with total_agencia(nome_agencia, valor) as
select nome_agencia, sum(saldo)
from conta
group by nome_agencia
with media_total_agencia(valor) as
select avg(valor)
from total_agencia
select nome_agencia
from total_agencia, media_total_agencia
where total_agencia.valor >=media_total_agencia.valor
SQL
Views
//consulta sem o valor do emprestimo
select nome_cliente, tomador. Numero_emprestimo, nome_agencia
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
//Outra consulta
(select nome_agencia, nome_cliente
from depositante, conta
where depositante.numero_conta = conta.numero_conta)
union
(select nome_agencia, nome_cliente
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo)
SQL
Definição de View
create view v as <expressão de consulta>
create view todos_clientes as
(select nome_agencia, nome_cliente
from depositante, conta
where depositante.numero_conta = conta.numero_conta)
union
(select nome_agencia, nome_cliente
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo)
select nome_cliente
from todos_clientes
where nome_agencia = ‘Perryridge’
SQL
Definição de View
create view emprestimo_total_agencia(nome_agencia, emprestimo total) as
select nome_agencia, sum(quantia)
from emprestimo
group by nome_agencia
Views materializadas se mantém atualizadas.
Views Definidas usando outras Views
create view cliente_perryridge as
select nome_cliente
from todos_cliente
where nome_agencia=‘Perryridge’
Definição de View
SQL
Expansão de view
select *
from cliente_perryridge
where nome_cliente =‘John’
select *
from( select nome_cliente
from todos_clientes
where nome_agencia = ‘Perryridge’)
Where nome_cliente = ´John’
Definição de View
SQL
Expansão de view
select *
from( select nome_cliente
from (
(select nome_agencia, nome_cliente
from depositante, conta
where depositante.numero_conta = conta.numero_conta)
union
(select nome_agencia, nome_cliente
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
)
)
where nome_agencia = ‘Perryridge’
Where nome_cliente = ´John’
SQL
Modificação do banco de dados
Exclusão
delete from r
where P
delete from r
delete from conta
where nome_agencia = ‘Perryridge’
delete from emprestimo
where quantia between 1300 and 1500
Delete from conta
Where nome_agencia in(select nome_agencia
from agencia
where cidade_agencia = ‘Brooklyn’)
SQL
Modificação do banco de dados
Exclusão
Delete from conta
Where saldo < (select avg(saldo)
from conta)
SQL
Modificação do banco de dados
Inserção
insert into conta values(‘A-9732’,’Perryridge’,1200)
insert into conta(numero_conta, nome_agencia, saldo) values(‘A-9732’,’Perryridge’,1200)
insert into conta(nome_agencia, numero_conta, saldo) values(’Perryridge’,‘A-9732’, 1200)
Insert into conta
select numero_emprestimo, nome_agencia, 200 //Numero_emprestimo corresponde a poupança
from emprestimo
where nome_agencia = ‘Perryridge’
SQL
Modificação do banco de dados
Inserção
insert into depositante
select nome_cliente, numero_emprestimo
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo and
nome_agencia = ‘Perryridge’
insert into conta values(‘A-101’,null,1200)
SQL
Modificação do banco de dados
Atualizações
update conta
set saldo = saldo * 1.05
update conta
set saldo = saldo * 1.05
where saldo >= 1000
update conta
set saldo = saldo * 1.05
where saldo > (select avg(saldo)
from conta)
SQL
Modificação do banco de dados
Atualizações
update conta
set saldo = saldo * 1.06
where saldo > 10000
update conta
set saldo = saldo * 1.05
where saldo <= 10000
Update conta
Set saldo = case
when saldo < = 10000 then saldo * 1.05
else saldo * 1.06
end
SQL
Modificação do banco de dados
Atualização de uma view
create view agencia_emprestimo as
select numero_emprestimo, nome_agencia
from emprestimo
Insert into agencia_emprestimo values(‘L-37’, ‘Perryridge’)
Soluções:
- Rejeitar a inserção
- Inserir uma tupla (L-37, “Perryridge”, nulo)
SQL
Modificação do banco de dados
Atualização de uma view
Relações:
emprestimo(numero_emprestimo, nome_agencia, quantia)
tomador(nome_cliente, numero_emprestimo)
create view info_emprestimo as
select nome_cliente, quantia
from tomador, emprestimo
where tomador.numero_emprestimo = emprestimo.numero_emprestimo
Insert into info_emprestimo values (‘Johnson’, 1900)
Equivale a inserir (“Johnson”,nulo) em tomador e (nulo, nulo, 1900) em emprestimo
SQL
Modificação do banco de dados
Atualização de uma view
create view conta_downtown as //View atualizável
select numero_conta, nome_agencia, saldo
from conta
where nome_agencia = ‘Downtown’
Tupla (‘A-999’,’Downtown’,1000)
Tupla (‘A-999’,’Perryridge’,1000)
//
//
É possível inserir a tupla na relação ou na view
É possível inserir a tupla na relação mas não na view
Cláusula with check option ao final da view para testar se o valor satisfaz a condição da
cláusula where
SQL
Transações
Uma transação consiste em uma sequência de instruções de consulta e/ou
atualização.
Commit – confirma a transação
Rollback – reverte a transação
Uso em algumas implementações:
begin atomic
...
end
SQL
Relações Juntadas
numero_emprestimo
nome_agencia
quantia
nome_cliente
numero_emprestimo
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
L-260
Perryridge
17000
Hayes
L-155
Emprestimo
Tomador
Junções Internas (Inner Join)
emprestimo inner join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo
numero_emprestimo
nome_agencia
quantia
nome_cliente
numero_emprestimo
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
SQL
Relações Juntadas
Junções Externas (Outer Join)
Left Outer Join (Junção Externa Esquerda)
emprestimo left outer join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo
numero_emprestimo
nome_agencia
quantia
nome_cliente
numero_emprestimo
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-130
L-260
Perryridge
17000
Right Outer Join (Junção Externa Direita)
emprestimo right outer join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo
numero_emprestimo
nome_agencia
quantia
nome_cliente
numero_emprestimo
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-230
Hayes
L-155
SQL
Relações Juntadas
Junções Externas (Outer Join)
Full Outer Join (Junção Externa Completa)
emprestimo full outer join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo
numero_emprestimo
nome_agencia
quantia
nome_cliente
numero_emprestimo
L-170
Downtown
3000
Jones
L-170
L-230
Redwood
4000
Smith
L-130
L-260
Perryridge
17000
Hayes
L-155
SQL
Relações Juntadas
Junção Natural (Natural Inner Join)
emprestimo natural inner join tomador
numero_emprestimo
nome_agencia
quantia
nome_cliente
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
Tipos e Condições de Junção
Tipos: inner join
left outer join
right outer join
full outer join
Condições de junção: Natural (natural)
on <predicado>
using(A1, A2,..., A3)
SQL
Relações Juntadas
emprestimo natural right outer join tomador
numero_emprestimo
nome_agencia
quantia
nome_cliente
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
Hayes
L-155
emprestimo full outer join tomador using(numero_emprestimo)
numero_emprestimo
nome_agencia
quantia
nome_cliente
L-170
Downtown
3000
Jones
L-230
Redwood
4000
Smith
L-260
Perryridge
1700
L-155
Hayes