Banco de Dados SQL

About This Presentation

Title:

Banco de Dados SQL

Description:

Banco de Dados SQL Elaini Simoni Angelotti elaini.angelotti_at_gmail.com SQL (DDL) Define o esquema do BD (base, tabelas, integridade, etc) Criando BD com comandos SQL ... – PowerPoint PPT presentation

Number of Views:378

Avg rating:3.0/5.0

Slides: 38

Provided by: sistemas22

Category:

more less

Transcript and Presenter's Notes

Title: Banco de Dados SQL

1
Banco de DadosSQL

Elaini Simoni Angelotti
elaini.angelotti_at_gmail.com

2
SQL (DDL)

Define o esquema do BD (base, tabelas,
integridade, etc)
Criando BD com comandos SQL
create database nome_do_banco_de_dados
create database Sistema_Bancário
Por default o SQLServer seta os seguintes
parâmetros
Caminho C\Arquivos de programas\Microsoft
SqlServer\MSSQL\data
Tamanho 1 MB
Log 1 MB
Crescimento do banco e do Log 10 e sem limite
O usuário pode setar os parâmetros usando
comandos SQL
Create database Exemplo1
ON (Name Exemplo1_data,
Filename C\Arquivos de
programas\Microsoft SQL Server\MSSQL\data\Exemplo1
.mdf, Size 2 MB,
MaxSize 10 MB,
Filegrowth 25 )

LOG
ON (Name Exemplo1_log,
Filename C\Arquivos de
programas\Microsoft SqlServer\MSSQL\data\Exemplo1.
ldf Size 2 MB,
MaxSize 6 MB,
Filegrowth 10 )
Alterando BD com comandos SQL
Para expandir o BD
Alter database Sistema_Bancário
modify file
(Name Sistema_Bancário,
Size 3MB)
Para reduzir o BD
DBCC SHRINKDATABASE
(Sistema_Bancário, 5)
Excluindo BD com comandos SQL
Drop database Sistema_Bancário

Criando Tabelas com comandos SQL
Create Table nomeTabela
( Atributo1 Tipo_de_Dados opções,
. . .
)
Exemplo
USE Sistema_Bancário // é necessário se
posicionar no BD antes
// de criar as tabelas
Create Table Cliente
( RG_Cli int not null,
Nome_Cli varchar (50),
Data_Cadastro datetime not null default
(getdate()),
Cidade_Cli varchar (12),
UF char (2) default (PR)
)
Excluindo Tabela com comandos SQL
DROP table nome_da_tabela
DROP table Cliente

Alterando a Estrutura de uma Tabela com comandos
SQL
Adicionando um novo atributo
Alter table Cliente
ADD Celular_Cli varchar (8) null
Alterando o tipo de um atributo
Alter table Cliente
Alter column Cidade_Cli varchar (25)
Excluindo um atributo de uma tabela
Alter Table Cliente
Drop Column Celular_cli

6
SQL - Constraints

UNIQUE
Restrição Unique é utilizada para manter os dados
inseridos com valores únicos.
Create Table Alunos
( Matricula Int,
CPF numeric(11) Unique,
Nome varchar(70)
)
NOT NULL
A restrição Not Null indica que este atributo
deve ter o valor obrigatoriamente preenchido
Create Table Alunos
( Matricula Int,
CPF numeric(11) Unique,
Nome varchar(70) Not Null
)

DEFAULT
A restrição default indicar qual valor será
atribuído ao registro quando não vier valor para
este atributo no Insert.
Create Table Alunos
( Matricula Int,
CPF numeric(11) Unique,
Nome varchar(70) Not Null,
Estado char(2) default PR
)
CHECK
A restrição Check verifica se o valor atribuído
ao registro (no Insert) é um dos valores
permitidos para o atributo.
Create Table Alunos
( Matricula Int,
CPF numeric(11) Unique,
Nome varchar(70) Not Null,
Estado char(2) default PR,
Sexo char(1) check (Sexo in (M,F))

PRIMARY KEY
Restrição Utilizada para definir a chave
primaria.
Create table Aluno
( Matricula Int Primary Key,
CPF numeric(11) Unique,
Nome varchar(70) Not Null,
Estado char(2) default PR,
Sexo char(1) check (Sexo in (M,F)
)
FOREIGN KEY
Esta restrição garante a integridade referencial.
( Matricula Int Primary Key,
CPF numeric(11) Unique,
Nome varchar(70) Not Null,
Estado char(2) default PR,
Sexo char(1) check (Sexo in (M,F),

9
Nomenclatura de Restrições

Create Table Aluno
( Matricula Int Constraint PKAluno Primary Key,
CPF numeric(11) Constraint UnCPF Unique,
Nome varchar(70) Constraint NNNome Not
Null,
Estado char(2) Constraint DFEstado default
PR,
Sexo char(1) Constraint CKSexo check (Sexo
in (M,F)),
Codprofissao Int Constraint FKProfissao
foreign key
references Profissao (cod)
)

10
Restrições de chaves compostas

Exemplo 1 chave primária composta
Create Table AlunoCurso
( CodAluno int not null,
CodCurso int not null,
Constraint PkAlunoCurso Primary
Key(CodAluno, CodCurso),
Constraint FkACAluno Foreign key
(CodAluno) references Aluno(codAluno),
Constraint FkACCurso Foreign key
(CodCurso) references Curso(codCurso)
)
Exemplo 2 chave estrangeira composta
Create Table Curso
( CodCurso int Constraint pkCurso Primary Key,
Nome varchar (70) Not Null,
CodCurriculo int not null,
AnoCurriculo int not null,
Constraint fkCurriculo foreign Key
(CodCurriculo, AnoCurriculo) References
Curriculo (CodCurriculo, AnoCurriculo)

11
Exercício Crie o BD abaixo e as respectivas
tabelas (com chaves primárias, estrangeiras e as
restrições (Constraint) necessárias)
Modelo Relacional Conta (num_conta, nome_ag,
saldo) Agencia (nome_ag, cidade_ag,
fundos) Cliente (nome_cli, cidade_cli,
rua_cli) Depositante (num_conta,
nome_cli) Emprestimo (num_emp, nome_ag,
total) Devedor (nome_cli, num_emp)
12
SQL (DML)

INSERT
podemos especificar uma tupla a ser inserida ou
escrever uma consulta cujo resultado é um
conjunto de tuplas a inserir.
Os valores dos atributos para as tuplas a serem
inseridas devem pertencer ao domínio desses
atributos
as tuplas a serem inseridas devem estar na ordem
correta.
Exemplo inserir a informação de que a conta
A-998 da agência Batel tem um saldo de 1.200
insert into conta
values (A-998, Batel, 1.200)
insert into conta (nome_agencia, numero_conta,
saldo)
values (Batel, A-998, 1.200)

SELECT
A estrutura básica de uma expressão em SQL
consiste em três claúsulas
select corresponde à operação de projeção (?). É
usada para relacionar os atributos desejados no
resultado de uma consulta.
from corresponde a operação do produto
cartesiano ( x ). Associa as Tabelas que serão
pesquisadas durante a evolução de uma expressão.
where corresponde à operação de seleção (?).
Consiste em um predicado (condição) envolvendo
atributos da Tabela que aparece no from.
Uma consulta típica em SQL
select A1, A2, ..., An
from r1, r2, ..., rm
where P
onde A atributo, r Tabela e P condição
O SQL forma um produto cartesiano das Tabelas de
from, executa uma seleção usando o predicado de
where e projeta os resultados sobre os atributos
da cláusula select.

O resultado de uma consulta em SQL é uma Tabela.
Exemplo Encontre os nomes de todas as agências
da Tabela empréstimo.
select nome_ag
from emprestimo
SQL permite duplicidade nas Tabelas, pois a
eliminação da duplicidade consome muito tempo.
Para forçar a eliminação de duplicidade usa-se o
comando distinct. Exemplo select distinct
nome_ag
from emprestimo
O asterisco pode ser usado para denotar
todos os atributos.
select emprestimo. from emprestimo
select from emprestimo
Select pode conter expressões aritmética (, -,
e /). Exemplo
select nome_ag, total 100
from emprestimo

WHERE
Encontre todos os números de empréstimos feitos
na agencia Batel com totais emprestados acima de
R1.200.
select num_emp,total
from emprestimo
where nome_ag Batel and total gt 1.200
O SQL usa os conectores AND, OR e NOT, além do
operadores de comparação lt, lt, gt, gt, e ltgt na
cláusula where.
O operador de comparação between pode ser usado
para simplificar a cláusula where que especifica
em valor que seja maior que ou igual a algum
valor e menor que ou igual a algum outro valor.
Exemplo Encontre os números de empréstimos e
totais cujos os montantes sejam entre R9.000 e
R100.000
select num_emp, total
from emprestimo
where total between 9000 and 100000
Not between também é permitido.

FROM
Define a(s) tabelas que serão utilizadas na
consulta.
Para duas ou mais tabelas, pode-se fazer o
produto cartesiano ou a junção das tabelas.
Ex para todos os clientes que tenham um
empréstimo em um banco, encontre seus nomes,
números de empréstimos e nome da agência
Produto cartesiano
select distinct nome_cli, devedor.num_emp,
nome_ag
from devedor, emprestimo
where devedor.num_emp emprestimo.num_emp
Junção
select distinct nome_cli, devedor.num_emp ,
nome_ag
from devedor INNER JOIN emprestimo ON
devedor.num_emp emprestimo.num_emp
usa-se a notação nome_tabela.nome_atributo para
evitar ambigüidades

ALIASES
Pode-se renomear tanto atributos qto Tabelas.
Para renomear Tabelas ou atributos utilizamos a
cláusula as que pode aparecer tanto tanto no
select qto no from.
nome_antigo as nome_novo
Exemplo
select distinct nome_cli, devedor.num_emp,
nome_ag as Nome Agencia
from devedor INNER JOIN emprestimo ON
devedor.num_emp
emprestimo.num_emp
Encontre os nomes de todas as agências que tenham
fundos maiores que ao menos uma agência daquelas
localizadas em Curitiba
select distinct T.nome_ag
from agencia as T, agencia as S
where T.fundos gt S.fundos and S.cidade_ag Ctba

OPERAÇÕES COM STRINGS
As mais usadas são as verificações de
coincidências de pares, usando o operador like.
Porcentagem () compara qquer substring
sublinhado ( _ ) compara qquer caracter.
maiúsculas não são iguais a minúsculas.
Exemplos
Ba - qualquer string que comece com Ba
iba - qualquer string que possua uma
substring iba.
_ _ _ - qualquer string com 3 caracteres
_ _ _ qualquer string com pelo menos 3
caracteres

Exemplo encontre os nomes de todos os clientes
cujas as ruas possuam a substring aio
select nome_cli
from cliente
where rua_cli like aio
O SQL permite o uso de um caracter de escape para
comparar caracteres especiais.
like ab\cd escape \ corresponde a todas as
strings que começam com abcd
like ab\\cd escape \ corresponde a todas as
strings que começam com ab\cd
é possível pesquisar diferenças em strings pelo
operador de comparação not like

ORDER BY
A cláusula order by faz com que as tuplas do
resultado de uma consulta apareçam em uma
determinada ordem.
Exemplo listar em ordem alfabética todos os
clientes que tenham um empréstimo na agência
Batel.
select distinct nome_cli, devedor.num_emp
from devedor INNER JOIN emprestimo ON
devedor.num_emp emprestimo.num_emp
where nome_ag Batel
order by nome_cli
Order by relaciona os itens em ordem ascendente.
Para especificar a ordem deve-se usar
Desc - para ordem descendente (maior para o
menor)
Asc - para ordem ascendente (menor para o maior)
a ordenação pode ser realizada por diversos
atributos. Ex
select
from emprestimo
order by total desc, num_emp asc

UNION
as Tabelas participantes da operação de União
precisam ser compatíveis, ou seja, precisam ter o
mesmo número de atributos.
Encontre todos os clientes que tenham um
empréstimo, uma conta ou ambos no banco.
(select nome_cli
from depositante)
union
(select nome_cli
from devedor)
elimina repetições automaticamente.
Para obter todas as repetições devemos escrever
union all no lugar de union

FUNÇÕES AGREGADAS
São funções que tomam uma coleção de valores como
entrada e retorna um valor simples
A SQL oferece 5 funções agregadas
média (average) avg
mínimo (minimum) min
máximo (maximum) max
Total (total) sum
contagem (count) count
A entrada para sum e avg precisa ser um conjunto
de números
mim, max e count podem operar com conjuntos de
tipos de dados não-numéricos.
Exemplo Encontre a média dos saldos em contas na
agência batel.
select avg (saldo) as media
from conta
where nome_ag batel

A cláusula GROUP BY é usada qdo queremos aplicar
uma função agregada a um grupo de registros.
O(s) atributo(s) fornecidos em uma cláusula group
by são usados para formar grupos.
Registros com os mesmos valores de todos os
atributos da cláusula group by são colocadas em
um grupo.
Exemplo encontre a média dos saldos nas contas
de cada uma das agências do banco.
select nome_ag, avg (saldo)
from conta
group by nome_ag
Quando tratamos a Tabela como um todo isto é,
como um grupo simples, não é necessário utilizar
a cláusula group by.
Exemplo Encontre a média dos saldos de todas as
contas.
select avg (saldo)
from conta

Se desejarmos eliminar repetições utilizaremos a
palavra distinct na expressão agregada.
Ex Encontre o número de depositantes de cada
agência
select nome_ag, count (distinct nome_cli)
from depositante INNER JOIN conta ON
depositante.num_conta conta.num_conta
group by nome_ag
Pode-se definir condições e aplicá-las a grupos
ao invés de aplicá-las a tuplas. Ex
Encontre quais agências possuem média dos saldos
aplicados em conta maior que R1.200.
select nome_ag, avg (saldo)
from conta
group by nome_ag
having avg (saldo) gt 1.200

Para contar o número total de registros em uma
Tabela, utilizamos a função count ().
Encontre o número de registros da Tabela cliente
select count ()
from cliente
A SQL não permite o uso do distinct com count ()
Se uma cláusula where e having aparecem na mesma
consulta o predicado que aparece em where é
aplicado primeiro.
Exemplo encontre o saldo médio para cada cliente
que mora em Curitiba e tenha ao menos 3 contas.
select depositante.nome_cli, avg (saldo)
from conta INNER JOIN depositante ON
depositante.num_conta conta.num_conta INNER
JOIN
cliente ON depositante.nome_cli
cliente.nome_cli
where cidade_cli Curitiba
group by depositante.nome_cli
having count (depositante.nome_cli) gt 3

VALORES NULOS
A SQL permite o uso do valor nulo para indicar
ausência de informação.
Pode-se utilizar a palavra is null para testar a
existência de valores nulos.
Ex encontre todos os números de empréstimo que
aparecem na Tabela empréstimo com valores nulo
para total
select num_emp
from emprestimo
where total is null
O predicado is not null testa a ausência de
valores nulos
O resultado de uma expressão aritmética (, -,
e /) é nula se qquer um dos valores de entrada
for nulo.

Comparações envolvendo valores nulos pode ser
visto como false. No entanto algumas versões
tratam o resultado dessas comparações como
unknown (desconhecido)
Funções agregadas podem tratar valores nulos
usando a seguinte regra todas as funções
agregadas, exceto o count(), ignoram os valores
nulos dos seus conjuntos de valores de entrada.
Select sum (total)
from emprestimo

SUBCONSULTAS ANINHADAS
Uma subconsulta é uma expressão select-from-where
aninhada dentro de outra consulta.
Membros de Conjuntos
permite verificar se um registro é membro ou não
de uma Tabela.
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 testa a ausência de membros de
um conjunto.
Ex Encontre todos os clientes que possuem tanto
uma conta qto um empréstimo no banco.
select distinct nome_cli
from devedor
where nome_cli in (select nome_cli
from depositante)

Ex2 Encontre todos os clientes que possuem um
empréstimo, mas não uma conta no banco.
select distinct nome_cli
from devedor
where nome_cli not in (select nome_cli
from depositante)
Ex3 Encontre todos os clientes que tenham um
empréstimo e cujos os nomes não sejam nem Smith
nem Jonas
select distinct nome_cli
from devedor
where nome_cli not in (Smith, Jonas)
Ex4 Encontre todos os clientes que tenham tanto
uma conta qto um empréstimo na agência Batel
select nome_cli,nome_ag
from devedor INNER JOIN emprestimo ON
devedor.num_empemprestimo.num_emp
where nome_ag 'Batel' and nome_cli in
(select nome_cli
from depositante INNER JOIN conta ON

VERIFICAÇÃO DE TABELAS VAZIAS
o construtor exists retorna true se o argumento
de uma subconsulta é não-vazio.
Através do construtor not exists podemos testar a
não existência de tuplas na subconsulta
Exemplo encontre todos os clientes que tenham
tanto uma conta qto um empréstimo no banco
select nome_cli
from devedor
where exists (select nome_cli from
depositante
where depositante.nome_cli
devedor.nome_cli)
TABELAS DERIVADAS
É possível o uso de uma expressão de subconsulta
na cláusula from.
Ex Encontre a média dos saldos das agências em
que a média dos saldos em conta é gt que 1.200
select nome_ag
from (select nome_ag, avg (saldo)
from conta
group by nome_ag)
as resultado (nome_ag,
saldo_medio)
where saldo_medio gt 1.200

COMPARAÇÃO DE CONJUNTOS
A frase maior que ao menos uma pode ser
representada em SQL pelo construtor gt some.
Ex encontre os nomes de todas as agências que
tenham fundos maiores que ao menos uma agência
localizada em Curitiba.
select distinct nome_ag
from agencia
where fundos gtsome (select fundos
from agencia
where cidade_ag Curitiba)
A SQL permite comparações ltsome, lt some, gt
some, some, ltgt some
a palavra any é sinônimo de some em SQL.
A frase maior que todos pode ser representada
pelo construtor gt all
A SQL permite comparações ltall, lt all, gt all,
all, ltgt all

Ex encontre os nomes de todas as agências que
tenham fundos maiores que cada uma das agências
localizada em Curitiba.
select distinct nome_ag
from agencia
where fundos gt all (select fundos
from agencia
where cidade_ag
Curitiba)
Funções agregadas não podem ser agregadas em SQL,
por exemplo, max (avg (...))
Ex Encontre a agência que tem o maior saldo
médio.
select distinct nome_ag
from conta
group by nome_ag
having avg (saldo) gt all (select avg (saldo)
from conta
group by nome_ag)

INSERT usando uma consulta cujo resultado é um
conjunto de tuplas a inserir.
Exemplo Dê a todos os clientes da agencia Batel
uma caderneta de poupança de R200, sendo que o
numero do empréstimo serve como o numero da
caderneta de poupança.
insert into conta
select num_emp, nome_ag, 200
from emprestimo
where nome_ag Batel
O comando select é executado primeiro resultando
em um conjunto de tuplas que são inseridas em uma
Tabela conta.

UPDATE
modifica valores de tuplas sem alterar todos os
valores.
Ex1 Dê a todos os saldos 5 como pagamento da
taxa de juros
update conta
set saldo saldo 0.05
comando acima é aplicado uma vez para cada tupla
de conta.
Ex2 Dê às contas com saldo superior a 10.000
juros de 6 e as demais contas juros de 5.
update conta
set saldo saldo 0.06
where saldo gt 10.000
update conta
set saldo saldo 0.05
where saldo lt 10.000
Ex3 Pague 5 de taxa de juros para as contas
cujo o saldo seja maior que a média dos saldos.
update conta

DELETE
podemos remover somente tuplas inteiras. A
remoção é expressa por
delete from r
where P
o comando delete encontra primeiro todas as tupla
t em r para os quais P(t) é verdadeira, e então
remove-as de r.
Pode-se ter uma cláusula where vazia. Ex
delete from empréstimo // remove todos os
registros
Exemplo1 remova todos os registros de contas do
cliente Smith.
delete from depositante
where nome_cli Smith
remova todas as contas de cada uma das agências
localizadas em ctba
delete from conta
where nome_ag in (select nome_ag
from agencia
where cidade_ag ctba)
Ex remova os registros de todas as contas com
saldos abaixo da média.

VISÕES (VIEW)
Definimos uma visão
create view v as ltexpressão da consultagt
Ex1 suponha que desejemos criar uma visão
denominada todos_clientes que seja composta dos
nomes das agências e nomes dos clientes que
tenham uma conta ou um empréstimo na agência
Batel.
create view todos_clientes as
(select nome_ag, nome_cli
from depositante INNER JOIN conta ON
depositante.num_contaconta.num_conta
where nome_ag Batel)
union
(select nome_ag, nome_cli
from devedor INNER JOIN emprestimo ON
devedor.num_emp emprestimo.num_emp
where nome_ag Batel)

Ex2 Crie uma visão que mostre a soma dos totais
de todos os empréstimos de cada agência.
create view emprestimo_total_agencia
(nome_agencia,emprestimo_total) as select
nome_ag, sum (total)
from emprestimo
group by nome_ag
Atualização de Visões
O nome de uma visão pode aparecer em qualquer
lugar onde o nome de uma Tabela aparece.
create view agencia_emprestimo as
select nome_ag, num_emp
from emprestimo
insert into agencia_emprestimo
values (Batel, 998)
na Tabela empréstimo (Tabela real) será inserido
(Batel, L-998l, null)
Regra para alterações por meio de visões na
maioria dos banco de dados
uma alteração por meio de visão somente é
permitida se a visão em questão é definida em
termos de uma Tabela real do banco de dados.