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 SQLcreate 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)

SQLcreate 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))

SQLInserçã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

SQLEstrutura 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 ))

SQLA cláusula select select nome_agencia from emprestimoou select all nome_agencia from emprestimo

Eliminação de duplicatas:

select distinct nome_agencia from emprestimoExpressões aritméticas select numero_emprestimo, quantia*100 from emprestimo

SQLA 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

SQLA 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’

SQLOperaçã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’

SQLOperaçõ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 caractereEx: ‘Perry%’, ‘%idge%’, ‘_ _ _’, ‘_ _ _ %’

select nome_clientefrom clientewhere rua_cliente like ‘%Main%’

Operador de escape \

SQLOperações de stringEx: ‘ab\%cd%’ e ‘ab\\cd%’

Funções em strings: - || - concatenação - upper() – string em maiúscula - lower() – string minúscula

SQLOrdenação da exibição de tuplasselect nome_clientefrom tomador, emprestimowhere tomador.numero_emprestimo = emprestimo_numero_emprestimo and

nome_agencia = ‘Perryridge’order by nome_cliente (Ordem crescente)

desc – decrescenteasc – Crescente

select *from emprestimoorder by quantia desc, numero_emprestimo asc

SQLOperações de conjunto

Union – UIntersect – ∩Except - – UNIÃO

(select nome_clientefrom depositante)union (duplicatas são eliminadas)(select nome_clientefrom tomador)

(select nome_clientefrom depositante)union all (duplicatas não são eliminadas)(select nome_clientefrom tomador)

SQLOperações de conjunto

INTERSEÇÃO

(select distinct nome_clientefrom depositante)intersect (duplicatas são eliminadas)(select distinct nome_clientefrom tomador)

(select nome_clientefrom depositante)intersect all (duplicatas não são eliminadas)(select nome_clientefrom tomador)

SQLOperações de conjunto

DIFERENÇA

(select distinct nome_clientefrom depositante)except (duplicatas são eliminadas)(select nome_clientefrom tomador)

(select nome_clientefrom depositante)except all (duplicatas não são eliminadas)(select nome_clientefrom tomador)

SQLOperações de conjunto

FUNÇÕES AGREGADAS

Average: avgMinimum: minMaximum: maxTotal: sumCount: count

select avg(saldo)from contawhere nome_agencia = ‘Perryridge’

SQLOperações de conjunto

FUNÇÕES AGREGADAS

select nome_agencia, avg(saldo)from contagroup by nome_agencia

select nome_agencia, count(distinct nome_cliente)from depositante, contawhere depositante.numero_conta = conta.numero_contagroup by nome_agencia

select nome_agencia, avg(saldo)from contagroup by nome_agenciahaving avg(saldo)> 1200

SQLOperações de conjunto

FUNÇÕES AGREGADASselect avg(saldo)from conta

select count(*) //Não se usa distinctfrom cliente

select depositante.nome_cliente, avg(saldo)from depositante, conta, clientewhere depositante.numero_conta = conta.numero_conta and

depositante.nome_cliente=‘Harrison’group by depositante.nome_clientehaving count (distinct depositante.numero_conta) >= 3

SQLValores Nulos

select numero_emprestimofrom emprestimowhere quantia is null (is not null)

A SQL trata como unknown o resultado de qualquer comparação envolvendo um valor nulo

true and unknown = unknownfalse and unknown = falseUnknown and unknown = unknown

true or unknwon = truefalse or unknown = unknownunknown or unknown = unknown

not unknown = unknown

SQLValores Nulos

select sum (quantia) //O operador sum ignora valores nulosfrom emprestimo

Todas as funções agregadas exceto count(*) ignoram valores nulos em sua coleção de entrada.

Subconsultas aninhadasParticipação de Conjuntos

select distinct nome_clientefrom tomadorwhere nome_cliente in (select nome_cliente from depositante)

SQL

Participação de Conjuntos

select distinct nome_clientefrom tomador, emprestimowhere 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_clientefrom tomadorwhere nome_cliente not in (select nome_cliente from depositante)

select distinct nome_clientefrom tomadorwhere nome_cliente not in (‘Smith’, ‘Jones’)

SQL

Comparação de Conjuntos

select distinct T.nome_agenciafrom agencia as T, agencia as Swhere T.ativo > S.ativo and S.cidade_agencia = ‘Brooklyn’

ouselect nome_agenciafrom agenciawhere 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_agenciafrom agenciawhere 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_agenciafrom contagroup by nome_agenciahaving avg(saldo) >=all (select avg(saldo) from conta group by nome_agencia)

Obs.: As funções agregadas não podem ser compostasmax(avg(...))

SQL

Teste de relações vazias

select nome_clientefrom tomadorwhere exists (select * from depositante where depositante.nome_cliente=tomador.nome_cliente)

select distinct S.nome_clientefrom depositante as Swhere 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))

SQLTeste da Ausência de tuplas duplicatas

select T.nome_clientefrom depositante as Twhere 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_clientefrom depositante as Twhere 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’)

SQLConsultas complexas

Relações Derivadas e Cláusula with

Relações Derivadas

(select nome_agencia, avg(saldo)from contagroup by nome_agencia)as media_agencia(nome_agencia, saldo_medio)

select nome_agencia, saldo_mediofrom (select nome_agencia, avg(saldo) from conta group by nome_agencia) as media_agencia(nome_agencia, saldo_medio)where saldo_medio > 1200

SQLConsultas 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)

SQLA cláusula with

with saldo_maximo(valor) as select max(saldo) from contaselect numero_contafrom conta, saldo_maximowhere conta.saldo = saldo_maximo.valor

with total_agencia(nome_agencia, valor) as select nome_agencia, sum(saldo) from conta group by nome_agenciawith media_total_agencia(valor) as select avg(valor) from total_agenciaselect nome_agenciafrom total_agencia, media_total_agenciawhere total_agencia.valor >=media_total_agencia.valor

SQLViews

//consulta sem o valor do emprestimo

select nome_cliente, tomador. Numero_emprestimo, nome_agenciafrom tomador, emprestimowhere tomador.numero_emprestimo = emprestimo.numero_emprestimo

//Outra consulta(select nome_agencia, nome_clientefrom depositante, contawhere depositante.numero_conta = conta.numero_conta) union(select nome_agencia, nome_clientefrom tomador, emprestimowhere tomador.numero_emprestimo = emprestimo.numero_emprestimo)

SQLDefiniçã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_clientefrom todos_clienteswhere nome_agencia = ‘Perryridge’

SQLDefinição de View

create view emprestimo_total_agencia(nome_agencia, emprestimo total) asselect nome_agencia, sum(quantia)from emprestimogroup by nome_agencia

Views materializadas se mantém atualizadas.

Views Definidas usando outras Views

create view cliente_perryridge asselect nome_clientefrom todos_clientewhere nome_agencia=‘Perryridge’

SQLDefinição de View

Expansão de view

select * from cliente_perryridgewhere nome_cliente =‘John’

select *from( select nome_cliente from todos_clientes where nome_agencia = ‘Perryridge’)

Where nome_cliente = ´John’

SQLDefinição de View

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’

SQLModificação do banco de dados

Exclusão

delete from rwhere P

delete from r

delete from contawhere nome_agencia = ‘Perryridge’

delete from emprestimowhere quantia between 1300 and 1500

Delete from contaWhere nome_agencia in(select nome_agencia from agencia where cidade_agencia = ‘Brooklyn’)

SQLModificação do banco de dados

Exclusão

Delete from contaWhere saldo < (select avg(saldo) from conta)

SQLModificaçã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’

SQLModificaçã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)

SQLModificação do banco de dados

Atualizações

update contaset saldo = saldo * 1.05

update contaset saldo = saldo * 1.05where saldo >= 1000

update contaset saldo = saldo * 1.05where saldo > (select avg(saldo) from conta)

SQLModificação do banco de dados

Atualizações

update contaset saldo = saldo * 1.06where saldo > 10000

update contaset saldo = saldo * 1.05where saldo <= 10000

Update contaSet saldo = case when saldo < = 10000 then saldo * 1.05 else saldo * 1.06 end

SQLModificação do banco de dados

Atualização de uma view

create view agencia_emprestimo asselect numero_emprestimo, nome_agenciafrom emprestimo

Insert into agencia_emprestimo values(‘L-37’, ‘Perryridge’)

Soluções: - Rejeitar a inserção - Inserir uma tupla (L-37, “Perryridge”, nulo)

SQLModificaçã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 asselect nome_cliente, quantiafrom tomador, emprestimowhere 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

SQLModificação do banco de dados

Atualização de uma view

create view conta_downtown as //View atualizávelselect numero_conta, nome_agencia, saldofrom contawhere nome_agencia = ‘Downtown’

Tupla (‘A-999’,’Downtown’,1000) // É possível inserir a tupla na relação ou na viewTupla (‘A-999’,’Perryridge’,1000) // É 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

SQLTransações

Uma transação consiste em uma sequência de instruções de consulta e/ou atualização.

Commit – confirma a transaçãoRollback – reverte a transação

Uso em algumas implementações:

begin atomic

...end

SQLRelações Juntadas

Junções Internas (Inner Join)

emprestimo inner join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

L-260 Perryridge 17000

nome_cliente numero_emprestimo

Jones L-170

Smith L-230

Hayes L-155

Emprestimo Tomador

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

nome_cliente numero_emprestimo

Jones L-170

Smith L-230

SQLRelaçõ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

Right Outer Join (Junção Externa Direita)

emprestimo right outer join tomador on emprestimo.numero_emprestimo = tomador.numero_emprestimo

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

L-260 Perryridge 17000

nome_cliente numero_emprestimo

Jones L-170

Smith L-130

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

nome_cliente numero_emprestimo

Jones L-170

Smith L-230

Hayes L-155

SQLRelaçõ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

L-170 Downtown 3000

L-230 Redwood 4000

L-260 Perryridge 17000

nome_cliente numero_emprestimo

Jones L-170

Smith L-130

Hayes L-155

SQLRelações Juntadas

Junção Natural (Natural Inner Join)

emprestimo natural inner join tomador

Tipos e Condições de Junção

Tipos: inner join Condições de junção: Natural (natural) left outer join on <predicado> right outer join using(A1, A2,..., A3) full outer join

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

nome_cliente

Jones

Smith

SQLRelações Juntadas

emprestimo natural right outer join tomador

emprestimo full outer join tomador using(numero_emprestimo)

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

L-155

nome_cliente

Jones

Smith

Hayes

numero_emprestimo nome_agencia quantia

L-170 Downtown 3000

L-230 Redwood 4000

L-260 Perryridge 1700

L-155

nome_cliente

Jones

Smith

Hayes