Bases de Dados
Introdução à linguagem SQL
História
Princípio dos anos 70
• IBM desenvolve a linguagem Sequel para o System R Renomeada para SQL (Structured Query Language) Standards ISO e ANSI
• SQL-86, SQL-89, SQL-92, SQL:1999, SQL:2003, SQL:2008 Sistemas comerciais suportam:p
Âmbito da linguagem SQL
Definição de dados
Manipulação de dados interactivap ç Restrições de integridade
Definição de vistas Controlo de transacções
Comandos embebidos em programação Autorização e segurança
3 IST ▪ DEI ▪ Bases de Dados
Autorização e segurança
Definição de dados
Permite especificar relações e as características de cada relação
• esquema da relação • domínio de cada coluna • restrições de integridade • índices para a relação • privilégios de acesso
t t fí i d t di
Definição do esquema
Uma tabela define-se com o comando
create table tabela (coluna1tipo1, coluna2tipo2, … restrição-integridade1, restrição-integridade2, ... ) 5 IST ▪ DEI ▪ Bases de Dados
tabela é o nome da tabela (dar um nome adequado) colunai é o nome de uma coluna
Definição do esquema – exemplo
create table account
(account_number char(10),
b h h (15)
branch_name char(15),
balance numeric(12,2),
primary key (account_number))
create table depositor
(customer_name char(20),
account_number char(10),
Tipos básicos de domínio
Tipos de dados suportados em SQL
• char(n) – string de tamanho fixo (pouco usado) • varchar(n) – tamanho variável, máximo n • integer
• smallint
• numeric(p,d) – p dígitos, d casas decimais • real
7 IST ▪ DEI ▪ Bases de Dados
• double
• float(n) – pelo menos n dígitos
Inserção de tuplos
Criar uma nova conta com o número A-9732 na agência de Perryridge com saldo inicial 1200
insert into
account(account_number, branch_name, balance)
values(‘A-9732’,’Perryridge’,1200)
i 1 i t
Alteração de relações
Remover colunas
alter table tabela drop coluna
Inserir colunas
9 IST ▪ DEI ▪ Bases de Dados
alter table tabela add coluna tipo
• todos os tuplos ficam com null neste novo atributo
Remoção de elementos
Apagar todos os registos de uma tabela
delete from account
Remover a tabela da base de dados
Estrutura básica de consultas em SQL Consulta típica em SQL l t select c1, c2, ..., cn from t1, t2, ..., tm where P • ci – colunas t t b l t l õ 11 IST ▪ DEI ▪ Bases de Dados
• ti – tabelas ou outras relações
• P– condições lógicas de selecção
Estrutura básica de consultas em SQL
Consulta típica em SQL l t select c1, c2, ..., cn from t1, t2, ..., tm where P
Π
(
(t
t
t
) )
Π
c1 , c2 , cn(σ
P(t
1x t
2x
... x t
m) )
Estrutura básica de consultas em SQL – notas Consulta típica em SQL l t select c1, c2, ..., cn from t1, t2, ..., tm where P
• se where for omitida, é como se P seja true
lt d d t t l d li d !
13 IST ▪ DEI ▪ Bases de Dados
• o resultado pode conter tuplos duplicados!
• o SGBD pode converter a expressão algébrica para uma forma mais eficiente
A cláusula select
A cláusula select lista as colunas pretendidas no resultado (projecção)
Exemplo: nomes de todas as agências com empréstimos
• em SQL
select branch_name from loan
A cláusula select – duplicados
SQL permite duplicados em relações e resultados • distinct força a eliminação de duplicados
Exemplo: nomes de todas as agências com empréstimos, sem duplicados
select distinct branch_name from loan
P f t ã d d li d (d f lt)
15 IST ▪ DEI ▪ Bases de Dados
Para forçar a apresentação de duplicados (default)
select all branch_name from loan
A cláusula select – símbolo *
Para obter todas as colunas
select * from loan
É possível usar também expressões aritméticas +, –, ∗, /, aplicadas a constantes ou atributos (projecção generalizada)
select loan_number, branch_name, amount ∗ 100 from loan
A cláusula where
A cláusula where especifica as condições que o resultado deve verificar (selecção)
Exemplo: números dos empréstimos da agência de Perryridgecom quantias superiores a 1200
select loan_number from loan
where branch name=‘Perryridge’ and amount >1200
17 IST ▪ DEI ▪ Bases de Dados
where branch_name Perryridge and amount >1200
• condições: =, >, <, >=, <=, <>, like, is null • operadores lógicos: and, or, not
A cláusula from
Indica as relações envolvidas na pergunta (produto) • exemplo: produto cartesiano borrower x loan
select ∗ from borrower, loan
Exemplo: nomes dos clientes e quantias dos empréstimos na agência de Perryridge
select customer_name, amount from borrower, loan
A cláusula from
Exemplo: nomes dos clientes e quantias dos empréstimos
• em álgebra relacional • em álgebra relacional
Πcustomer_name, amount (borrowerڇ loan) • em SQL
19 IST ▪ DEI ▪ Bases de Dados
select customer_name, amount from borrower, loan
where borrower.loan_number = loan.loan_number
Renomeação em SQL
Para renomear usa-se a cláusula as na forma nome_antigo as novo_nome Exemplo: nomes dos clientes e quantias dos
empréstimos
select customer_name as nome, amount as quantia from borrower loan
from borrower, loan
Variáveis
Definidas na cláusula from com recurso à cláusula as • exemplo: nomes dos clientes e quantias dos
empréstimos
select T.customer_name, S.amount from borrower as T, loan as S
where T.loan_number = S.loan_number
21 IST ▪ DEI ▪ Bases de Dados
Variáveis – outro exemplo
Nomes das agências com activo superior a alguma agência de Brooklyn
select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and
Operações com strings
Exemplos de strings: ‘Perryridge’, ‘it’’s right’
Comparação de strings com padrões • % – aplica-se a qualquer substring • _ – aplica-se a qualquer character • os padrões deviam ser case-sensitive
23 IST ▪ DEI ▪ Bases de Dados
Exemplos
• ‘Perry%’, ‘%idge%’, ‘___’, ‘___%’
Operações com strings – operador like
Exemplo: nomes dos clientes cuja rua possui a sequência “Main”
select customer_name from customer
where customer_street like ‘%Main%’
• e se a sequência dada contiver ‘%’ ou ‘ ’?e se a sequência dada contiver % ou _ ? ▫ ‘ab\%cd%’ aplicável a todas as strings com “ab%cd” ▫ ‘ab\\cd%’ aplicável a todas as strings com “ab\cd”
Operações com strings – outros operadores
not like também é um operador
Conversão com upper() e lower()
Muitas outras funções
• concatenação, inserção, procura, substituição, inversão, etc.
i t dif t d i t
25 IST ▪ DEI ▪ Bases de Dados
• sintaxe diferente em cada sistema
Bases de Dados
Ordenação de tuplos – order by
A cláusula order by permite ordenar tuplos
• exemplo: listar por ordem alfabética os nomes dos clientes com empréstimo na agência de Perryridge
select distinct customer_name from borrower, loan
where borrower.loan_number = loan.loan_number and branch name = ‘Perryridge’
27 IST ▪ DEI ▪ Bases de Dados
_ y g order by customer_name customer_name ---Adams Hayes (2 rows)
Ordenação de tuplos – asc e desc
Por omissão, a ordenação é ascendente • asc e desc permitem especificar a ordem
Exemplo: empréstimos por ordem descendente de quantia, e depois ascendente por número
select *
f l
from loan
Ordenação de tuplos – asc e desc
select * from loan
order by amount desc, loan_number asc
loan_number | branch_name | amount ---+---+---L-20 | North Town | 7500.00 L-23 | Redwood | 2000.00 L-14 | Downtown | 1500.00 L-15 | Perryridge | 1500.00 L-16 | Perryridge | 1300.00 L-17 | Downtown | 1000.00 29 IST ▪ DEI ▪ Bases de Dados
L-11 | Round Hill | 900.00 L-21 | Central | 570.00 L-93 | Mianus | 500.00 (9 rows)
Operações em conjuntos
Existem 3 operações com correspondência directa com a álgebra relacional
• union (U) • intersect (∩) • except (–)
Automaticamente eliminam duplicados
õ i ll i t t ll t ll ã
• as versões union all, intersect all, except all não eliminam
Operações em conjuntos – exemplos
Nomes dos clientes com conta ou empréstimo
(select customer_name from depositor)
union
(select customer_name from borrower)
Nomes dos clientes com conta e empréstimo
31 IST ▪ DEI ▪ Bases de Dados
(select customer_name from depositor)
intersect
(select customer_name from borrower)
Operações em conjuntos – exemplos (select customer_name from depositor)
union
(select customer_name from borrower)
customer_name ---Adams Curry Hayes Jackson Johnson Jones Lindsay Majeris McBride Smith Turner Williams (12 rows)
Operações em conjuntos – exemplos (select customer_name from depositor)
intersect
(select customer_name from borrower)
customer_name ---Hayes Jones Smith 33 IST ▪ DEI ▪ Bases de Dados
(3 rows)
Operações em conjuntos – exemplos
Nomes dos clientes com conta mas sem empréstimo
(select customer_name from depositor)
except
Operações em conjuntos – exemplos
(select customer_name from depositor)
except
( l t t f b )
(select customer_name from borrower)
customer_name ---Johnson Lindsay Majeris 35 IST ▪ DEI ▪ Bases de Dados
Majeris Turner (4 rows)
Operações em conjuntos – duplicados
Se um tuplo ocorre • m vezes em r • n vezes em s
então
• ocorre m + n vezes em (r union all s)
• ocorre min(m,n) vezes em (r intersect all s) • ocorre max(0, m – n) vezes em (r except all s)ocorre max(0, m n) vezes em (r except all s)
Funções de agregação
Operam no conjunto de valores de uma coluna • avg, min, max, sum, count
Exemplo: saldo médio das contas da agência de Perryridge
select avg(balance)
37 IST ▪ DEI ▪ Bases de Dados
from account
where branch_name = ‘Perryridge’
Funções de agregação
select avg(balance) from account
from account
where branch_name = ‘Perryridge’
avg
---650.0000000000000000 (1 row)
Funções de agregação – mais exemplos
Número de tuplos na relação customer
select count (*) from customer
Número de titulares com contas no banco count ---15 (1 row)
39 IST ▪ DEI ▪ Bases de Dados
select count (distinct customer_name) from depositor
count ---7 (1 row)
Funções de agregação – group by
Saldo médio das contas de cada agência
select branch_name, avg(balance) from account
group by branch_name
• os atributos do select que não sejam agregados
tê d d
Funções de agregação – group by
select branch_name, avg(balance) from account group by branch_name branch_name | avg ---+---Brighton | 750.0000000000000000 Perryridge | 650.0000000000000000 Round Hill | 350.0000000000000000 41 IST ▪ DEI ▪ Bases de Dados
Redwood | 700.0000000000000000 Downtown | 500.0000000000000000 Central | 850.0000000000000000 Mianus | 700.0000000000000000 North Town | 625.0000000000000000 (8 rows)
Funções de agregação – group by (outro exemplo)
Número de titulares em cada agência
select branch_name, count(distinct customer_name) from depositor, account
where depositor.account_number =
account.account_number
group by branch name group by branch_name
Funções de agregação – group by (outro exemplo)
select branch_name, count(distinct customer_name) from depositor, account
where depositor.account_number = account.account_number group by branch_name branch_name | count ---+---Brighton | 1 Central | 1 43 IST ▪ DEI ▪ Bases de Dados
Downtown | 2 Mianus | 1 North Town | 1 Perryridge | 2 Redwood | 1 Round Hill | 1 (8 rows)
Funções de agregação – having
saldo médio das contas de cada agência, mas só agências com saldo médio superior a 650
select branch_name, avg(balance) from account
group by branch_name having avg (balance) > 650
• predicados de having são aplicados depois do agrupamento • predicados de where são aplicados antes do agrupamento
Funções de agregação – having
select branch_name, avg(balance) from account
group by branch_name having avg (balance) > 650
branch_name | avg ---+---Brighton | 750.0000000000000000 Redwood | 700.0000000000000000 Central | 850 0000000000000000 45 IST ▪ DEI ▪ Bases de Dados
Central | 850.0000000000000000 Mianus | 700.0000000000000000 (4 rows)
Funções de agregação – having com where
Saldo médio dos clientes que vivem em Harrison e têm pelo menos 2 contas
select d.customer_name, avg (balance)
from depositor as d, account as a, customer as c where d.account_number = a.account_number
and d.customer_name = c.customer_name
d t it ‘H i ’
and customer_city = ‘Harrison’ group by d.customer_name
Funções de agregação – having com where
select d.customer_name, avg (balance)
from depositor as d, account as a, customer as c where d.account_number = a.account_number
and d.customer_name = c.customer_name and customer_city = ‘Harrison’
group by d.customer_name
having count (distinct d.account_number) >= 2
47 IST ▪ DEI ▪ Bases de Dados
customer_name | avg ---+---Hayes | 450.0000000000000000 (1 row)
Valores null
Verificar a existência de valores null com is null
select loan_number from loan
where amount is null
Valores null – funções de agregação
Funções de agregação ignoram null
select sum(amount) from loan
• ignora quantias null
• resultado é null se só houver nulls
49 IST ▪ DEI ▪ Bases de Dados
Excepção: count(*)
• conta os tuplos mesmo que sejam null
Perguntas encadeadas
É possível encadear um select-from-where dentro de outro
Aplicações típicas
• ocorrência num conjunto • comparação de conjuntos
Ocorrência num conjunto – in e not in
Nomes dos clientes com conta e empréstimo
select distinct customer_name from borrower
where customer_name in (select customer_name from depositor)
customer_name
---Hayes
51 IST ▪ DEI ▪ Bases de Dados
y Jones Smith (3 rows)
Ocorrência num conjunto – in e not in
Nomes dos clientes com empréstimo cujos nomes não são Smith nem Jones
select distinct customer_name from borrower
where customer_name not in (‘Smith’,’Jones’)
customer_name ---Adams Curry Hayes Jackson McBride Williams (6 rows)
Comparação de conjuntos – some
Agências cujo activo é superior a alguma agência de Brooklyn
select distinct T.branch_name from branch as T, branch as S where T.assets > S.assets and
S.branch_city = ‘ Brooklyn’
select branch_name from branch
where assets > some
53 IST ▪ DEI ▪ Bases de Dados
(select assets
from branch
where branch_city = ‘Brooklyn’)
Comparação de conjuntos – some
select branch_name from branch
where assets > some
(select assets (select assets
from branch
where branch_city = ‘Brooklyn’)
branch_name
---Redwood Perryridge Round Hill
Comparação de conjuntos – all
Agências cujo activo é superior a todas as agências de Brooklyn
select branch_name from branch
where assets > all
(select assets
from branch
55 IST ▪ DEI ▪ Bases de Dados
where branch_city = ‘Brooklyn’)
Comparação de conjuntos – all
select branch_name from branch
h t ll
where assets > all
(select assets
from branch
where branch_city = ‘Brooklyn’)
branch_name
---Round Hill (1 row)
Comparação de conjuntos – all (outro exemplo)
Exemplo importante: determinar a conta com o maior saldo, mostrando também o saldo dessa conta
select account_number, balance from account
where balance >= all (select balance from account)
57 IST ▪ DEI ▪ Bases de Dados
account_number | balance ---+---A-201 | 900.00 (1 row)
Conjuntos vazios – exists e not exists
Nomes dos clientes com conta e empréstimo
select customer_name from borrower where exists (select * from depositor where depositor.customer_name =p _ borrower.customer_name)
Conjuntos vazios – exists e not exists select customer_name from borrower where exists (select * (select * from depositor where depositor.customer_name = borrower. customer_name) customer_name ---59 IST ▪ DEI ▪ Bases de Dados
Jones Smith Hayes Smith Smith (5 rows)
Tuplos duplicados – unique e not unique
Nomes dos clientes com uma só conta na agência de Perryridge
select T.customer_name from depositor as T where unique (
select R.customer_name from account, depositor as R
where T.customer_name = R.customer_name
and R.account_number = account.account_number and account.branch_name = ‘ Perryridge’)
Tuplos duplicados – unique e not unique select T.customer_name from depositor as T where unique ( l t R t select R.customer_name from account, depositor as R
where T.customer_name = R.customer_name
and R.account_number = account.account_number and account.branch_name = ‘ Perryridge’)
61 IST ▪ DEI ▪ Bases de Dados
ERROR: UNIQUE predicate is not yet implemented
Relações derivadas
Pergunta encadeada na cláusula from
• exemplo: saldo médio das contas nas agências em que esse saldo médio é superior a 650
select branch_name, avg_balance
from (select branch_name, avg (balance) from account
group by branch name)
g p y _ )
as branch_avg(branch_name, avg_balance)
Relações derivadas
select branch_name, avg(balance) from account group by branch_name branch_name | avg ---+---Brighton | 750.0000000000000000 Perryridge | 650.0000000000000000 Round Hill | 350.0000000000000000 Redwood | 700.0000000000000000 Downtown | 500.0000000000000000 63 IST ▪ DEI ▪ Bases de Dados
Central | 850.0000000000000000 Mianus | 700.0000000000000000 North Town | 625.0000000000000000 (8 rows)
Relações derivadas
select branch_name, avg_balance
from (select branch_name, avg (balance) from account
group by branch name) group by branch_name)
as branch_avg(branch_name, avg_balance) where avg_balance > 650 branch_name | avg_balance ---+---Brighton | 750.0000000000000000 Redwood | 700.0000000000000000 Central | 850.0000000000000000 Mianus | 700.0000000000000000 (4 rows)
Vistas – views
Uma vista é uma “relação virtual”
Pode restringir a informação disponível
• exemplo: clientes e empréstimos, mas não quantias
Pode consolidar a informação disponível
• exemplo: clientes com conta ou empréstimo, e
ti ê i
65 IST ▪ DEI ▪ Bases de Dados
respectivas agências
Definição de vistas
Definidas com base numa expressão SQL
create view v as …expressão…p
Uma vez criada, pode ser usada como uma relação • mas não é o mesmo que criar uma relação • a vista é uma expressão de substituição em
Definição de vistas – exemplo
Vista das agências e respectivos clientes
create view all_customer as
(select branch_name, customer_name
from depositor, account
where depositor.account_number =
account.account_number)
union
(select branch name customer name
67 IST ▪ DEI ▪ Bases de Dados
(select branch_name, customer_name
from borrower, loan
where borrower.loan_number =
loan.loan_number)
Definição de vistas – exemplo
(select branch_name, customer_name from depositor, account
branch_name | customer_name ---+---Brighton | Jones Central | Majeris Central | Smith Downtown | Hayes where depositor.account_number = account.account_number) union
(select branch_name, customer_name from borrower, loan
where borrower.loan_number = Downtown | Hayes Downtown | Jackson Downtown | Johnson Downtown | Jones Downtown | Williams Mianus | Curry Mianus | Smith North Town | McBride North Town | Smith Perryridge | Adams Perryridge | Hayes
id | h loan.loan_number) Perryridge | Johnson
Redwood | Lindsay Redwood | Smith Round Hill | Smith Round Hill | Turner (19 rows)
Utilização de vistas create view all_customer as
(select branch_name, customer_name from depositor, account
where depositor.account_number = account.account_number) union
(select branch_name, customer_name from borrower, loan
where borrower.loan_number = loan.loan_number)
select customer_name
f ll t
customer_name
---69 IST ▪ DEI ▪ Bases de Dados
from all_customer
where branch_name = ‘Perryridge’
Adams Hayes Johnson (3 rows)
Utilização de vistas
Nomes dos atributos das vistas
create view branch_total_loan (branch_name, total_loan) as select branch_name, sum(amount)
from loan
group by branch_name select * from branch_total_loan
branch_name | total_loan
---+---P id | 2800 00
Perryridge | 2800.00 Round Hill | 900.00
Utilização de vistas
Atenção: não faz sentido criar uma vista só para fazer uma consulta
Uma vista pode ser apagada com drop view
create view v as … select … from v … select … from v … l t f 71 select … from v … drop view v
IST ▪ DEI ▪ Bases de Dados
Bases de Dados
Junção de relações
Junção efectuada na cláusula from SQL oferece várias possibilidades
• tipo de junção – determina como é que os registos excedentes são tratados
• condição da junção – determina quais são as colunas a comparar
73 IST ▪ DEI ▪ Bases de Dados
Junção de relações – inner join
select *
from loan inner join borrower on
loan.loan_number = borrower.loan_number
loan_number | branch_name | amount | customer_name | loan_number ---+---+---+---+---L-17 | Downtown | 1000.00 | Williams | ---+---+---+---+---L-17 L-17 | Downtown | 1000.00 | Jones | L-17 L-23 | Redwood | 2000.00 | Smith | L-23 L-15 | Perryridge | 1500.00 | Hayes | L-15 L-14 | Downtown | 1500.00 | Jackson | L-14 L-93 | Mianus | 500.00 | Curry | L-93 L 93 | Mianus | 500.00 | Curry | L 93
L-11 | Round Hill | 900.00 | Smith | L-11 L-16 | Perryridge | 1300.00 | Adams | L-16
Junção de relações – left outer join e right outer join
select *
from customer right outer join depositor on
customer.customer_name= depositor.customer_name
customer_name | customer_street | customer_city | customer_name | account_number
---+---+---+---+---Johnson | Alma | Palo Alto | ---+---+---+---+---Johnson | A-101 Smith | Main | Rye | Smith | A-215 Hayes | Main | Harrison | Hayes | A-102 Hayes | Main | Harrison | Hayes | A-101 Turner | Putnam | Stamford | Turner | A-305 Johnson | Alma | Palo Alto | Johnson | A-201
Jones | Main | Harrison | Jones | A-217
75 IST ▪ DEI ▪ Bases de Dados
Jones | Main | Harrison | Jones | A 217 Lindsay | Park | Pittsfield | Lindsay | A-222 Majeris | First | Rye | Majeris | A-333 Smith | Main | Rye | Smith | A-444 (10 rows)
Junção de relações – left outer join e right outer join
select *
from customer left outer join depositor on
customer.customer_name= depositor.customer_name
customer_name | customer_street | customer_city | customer_name | account_number
---+---+---+---+---Jones | Main | Harrison | ---+---+---+---+---Jones | A-217 Smith | Main | Rye | Smith | A-444 Smith | Main | Rye | Smith | A-215 Hayes | Main | Harrison | Hayes | A-101 Hayes | Main | Harrison | Hayes | A-102 Curry | North | Rye | | Lindsay | Park | Pittsfield | Lindsay | A-222 Turner | Putnam | Stamford | Turner | A-305
Williams | Nassau | Princeton | |
Williams | Nassau | Princeton | | Adams | Spring | Pittsfield | | Johnson | Alma | Palo Alto | Johnson | A-201 Johnson | Alma | Palo Alto | Johnson | A-101 Glenn | Sand Hill | Woodside | | Brooks | Senator | Brooklyn | | Green | Walnut | Stamford | | Jackson | University | Salt Lake | | Majeris | First | Rye | Majeris | A-333 McBride | Safety | Rye | |
Junção de relações – natural inner join
select *
from loan natural inner join borrower
loan_number | branch_name | amount | customer_name ---+---+---+---L-17 | Downtown | 1000.00 | Williams L-17 | Downtown | 1000.00 | Jones L-23 | Redwood | 2000.00 | Smith L-15 | Perryridge | 1500.00 | Hayes L-14 | Downtown | 1500.00 | Jackson L-93 | Mianus | 500.00 | Curry
L-11 | Round Hill | 900.00 | Smith
77 IST ▪ DEI ▪ Bases de Dados
L-16 | Perryridge | 1300.00 | Adams L-20 | North Town | 7500.00 | McBride L-21 | Central | 570.00 | Smith (10 rows)
Junção de relações – natural outer join
select * from customer natural left outer join depositor
customer_name | customer_street | customer_city | account_number
---+---+---+---Jones | Main | Harrison | A-217
Jones | Main | Harrison | A-217 Smith | Main | Rye | A-444 Smith | Main | Rye | A-215 Hayes | Main | Harrison | A-101 Hayes | Main | Harrison | A-102 Curry | North | Rye | Lindsay | Park | Pittsfield | A-222 Turner | Putnam | Stamford | A-305 Williams | Nassau | Princeton | Adams | Spring | Pittsfield | Johnson | Alma | Palo Alto | A-201
h l l l 101
Johnson | Alma | Palo Alto | A-101 Glenn | Sand Hill | Woodside | Brooks | Senator | Brooklyn |
Junção de relações – full outer join
select * from depositor full outer join borrower using (customer_name)
customer_name | account_number | loan_number
+ + ---+---+---Adams | | L-16 Curry | | L-93 Hayes | A-102 | L-15 Hayes | A-101 | L-15 Jackson | | L-14 Johnson | A-101 | Johnson | A-201 | Jones | A-217 | L-17 Lindsay | A-222 | Majeris | A-333 | 79 IST ▪ DEI ▪ Bases de Dados
McBride | | L-20 Smith | A-215 | L-23 Smith | A-215 | L-11 Smith | A-215 | L-21 Smith | A-444 | L-23 Smith | A-444 | L-11 Smith | A-444 | L-21 Turner | A-305 | Williams | | L-17 (19 rows)
Junção de relações – exemplo
Nomes dos clientes com conta ou empréstimo (mas não ambos)
l t t
select customer_name
from (depositor natural full outer join borrower) where account_number is null
or loan_number is null customer_name ---Adams Curry Jackson Johnson Johnson Lindsay Majeris McBride Turner Williams (10 rows)
Bases de Dados
Modificações ao conteúdo da BD
Remoção
Apagar todas as contas da agência de Perryridge
delete from account
where branch_name = ‘Perryridge’
Apagar todas as contas das agências localizadas em Brooklyn
delete from account
where branch name in(select branch name where branch_name in (select branch_name
Remoção – cuidados a ter
Apagar todas as contas com saldo abaixo da média
delete from account
where balance < (select avg (balance) from account)
• esta consulta só funciona porque o sistema é simpático! (comparação de valor com relação)
83 IST ▪ DEI ▪ Bases de Dados
simpático! (comparação de valor com relação) • cada registo removido poderia alterar a média ▫ em SQL, todos os testes são feitos antes de apagar
qualquer tuplo
Inserção
Criar uma nova conta
insert into account
values (‘A-9732’, ‘Perryridge’,1200)
• ou então
insert into account (branch_name, balance,
account_number)
values (‘Perryridge’, 1200, ‘A-9732’)( y g , , )
Inserção – exemplo
Oferecer uma conta surpresa com saldo 200 a todos os clientes com empréstimo na agência de Perryridge nú
m
insert into account
select loan_number, branch_name, 200 from loan
where branch_name = ‘Perryridge’ insert into depositor
select customer name, loan number
m
ero da nova
conta
85 IST ▪ DEI ▪ Bases de Dados
_ , _
from borrower, loan
where borrower.loan_number= loan.loan_number and branch_name = ‘Perryridge’
Actualização
Vencimento de juros de 6% para saldos superiores a 10 000 e para as restantes contas 5%
update account
set balance = balance ∗ 1.06 where balance > 10000
update account
set balance = balance∗ 1 05 set balance balance ∗ 1.05 where balance <=10000
Bases de Dados
Exemplos de consultas SQL
Exemplo 1
Nomes e cidades onde residem os clientes com empréstimo
select distinctb.customer_name, c.customer_city fromborrower as b, customer as c
Exemplo 2
Nomes e cidades dos clientes com empréstimo na agência de Perryridge
select distinctc.customer_name, c.customer_city fromcustomer as c, borrower as b, loan as l wherec.customer_name = b.customer_name
and b.loan_number = l.loan_number and l.branch_name = 'Perryridge';
89
Exemplo 3
Números de todas as contas com saldo entre 700 e 900
select account_number from account
Exemplo 4
Nomes dos clientes que moram em ruas cujo nome acaba em “Hill”
selectcustomer_name fromcustomer
wherecustomer_street like '%Hill';
91
Exemplo 5
Nomes dos clientes com conta e empréstimo na agência de Perryridge
select distinct b.customer_name from borrower as b, loan as l
where b.loan_number = l.loan_number and l.branch_name = 'Perryridge‘ and b.customer_name in
(select d.customer name (select d.customer_name
fromaccount as a, depositor as d
wherea.account_number = d.account_number anda.branch_name = 'Perryridge')
Exemplo 6
Nomes dos clientes com contas na mesma agência onde o cliente Hayes tem conta
select distinct d.customer_name from depositor as d, account as a
where d.account_number = a.account_number and a.branch_name in
(select a2.branch_name
f d it d2 t 2
93 from depositor as d2, account as a2
where d2.account_number = a2.account_number andd2.customer_name = 'Hayes');
Exemplo 7
Nomes das agências com activo superior a todas as agências da cidade de Brooklyn
selectbranch_name frombranch
whereassets > all
(select assets
frombranch
h b h i 'B kl ')
Exemplo 8
Nomes dos clientes com empréstimo na agência de Perryridge, por ordem alfabética
select distinctcustomer_name fromborrower as b, loan as l
whereb.loan_number = l.loan_number
and l.branch_name = 'Perryridge' order byb.customer_name;
95
Exemplo 9
Nomes das agências e número de clientes com conta nessa agência
selecta.branch_name, count(distinct d.customer_name) fromdepositor as d, account as a
whered.account_number = a.account_number group bya.branch_name;
Exemplo 10
Nomes das agências e respectivo saldo médio, mas só as agências com saldo médio superior a 700
selectbranch_name, avg(balance) fromaccount
group bybranch_name having avg(balance) > 700;
97
Exemplo 11
Nome da agência com o maior saldo médio
selectbranch_name
fromaccount
group bybranch_name having avg(balance) >= all
(select avg(balance)
fromaccount
Exemplo 12
Número total de clientes do banco
select count(*) from customer;
99
Exemplo 13
Saldo médio dos clientes que vivem na cidade de Harrisone têm pelo menos 2 contas no banco
select avg(balance)
fromdepositor, account, customer
wheredepositor.customer_name = customer.customer_name and depositor.account_number = account.account_number
andcustomer city = 'Harrison' and customer_city = Harrison group by depositor.customer_name
Exemplo 14
Quem são os clientes com conta em todas as agências da cidade de Brooklyn
select distinctcustomer_name
fromdepositor as d where not exists(
(select branch_name
frombranch
wherebranch_city = 'Brooklyn')
except
( l tb h
101
(select branch_name
fromdepositor as d2, account as a2
whered2.account_number = a2.account_number and d2.customer_name = d.customer_name) );