Banco de Dados SQL Elaini Simoni Angelotti elaini

Banco de Dados SQL Elaini Simoni Angelotti elaini. angelotti@gmail. com 1

SQL (DDL) – Define o esquema do BD (base, tabelas, integridade, etc) – Criando BD 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 programasMicrosoft Sql. ServerMSSQLdata 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 Exemplo 1 ON (Name = Exemplo 1_data, Filename = ‘C: Arquivos de programasMicrosoft SQL ServerMSSQLdataExemplo 1. mdf’, Size = 2 MB, Max. Size = 10 MB, Filegrowth = 25% ) 2

LOG ON (Name = Exemplo 1_log, Filename = ‘C: Arquivos de programasMicrosoft Sql. ServerMSSQLdataExemplo 1. ldf’ Size = 2 MB, Max. Size = 6 MB, Filegrowth = 10% ) • Alterando BD comandos SQL: – Para expandir o BD: Alter database Sistema_Bancário modify file (Name = Sistema_Bancário, Size = 3 MB) – Para reduzir o BD: DBCC SHRINKDATABASE (Sistema_Bancário, 5) • Excluindo BD comandos SQL: Drop database Sistema_Bancário 3

• Criando Tabelas comandos SQL: Create Table nome. Tabela ( Atributo 1 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 comandos SQL: DROP table nome_da_tabela DROP table Cliente 4

• Alterando a Estrutura de uma Tabela 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 5

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

• 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’)) ) 7

• 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 CPF Nome Estado Sexo Codprofissao ) Int Primary Key, numeric(11) Unique, varchar(70) Not Null, char(2) default ‘PR’, char(1) check (Sexo in (‘M’, ’F’), Int foreign key references Profissao (cod) 8

Nomenclatura de Restrições Create Table Aluno ( Matricula CPF Nome Estado Sexo Codprofissao Int Constraint PKAluno Primary Key, numeric(11) Constraint Un. CPF Unique, varchar(70) Constraint NNNome Not Null, char(2) Constraint DFEstado default ‘PR’, char(1) Constraint CKSexo check (Sexo in (‘M’, ’F’)), Int Constraint FKProfissao foreign key references Profissao (cod) ) 9

Restrições de chaves compostas • Exemplo 1: chave primária composta Create Table Aluno. Curso ( Cod. Aluno int not null, Cod. Curso int not null, Constraint Pk. Aluno. Curso Primary Key(Cod. Aluno, Cod. Curso), Constraint Fk. ACAluno Foreign key (Cod. Aluno) references Aluno(cod. Aluno), Constraint Fk. ACCurso Foreign key (Cod. Curso) references Curso(cod. Curso) ) • Exemplo 2: chave estrangeira composta Create Table Curso ( Cod. Curso int Constraint pk. Curso Primary Key, Nome varchar (70) Not Null, Cod. Curriculo int not null, Ano. Curriculo int not null, Constraint fk. Curriculo foreign Key (Cod. Curriculo, Ano. Curriculo) References Curriculo (Cod. Curriculo, Ano. Curriculo) ) 10

Exercício: Crie o BD abaixo e as respectivas tabelas (com chaves primárias, estrangeiras e as restrições (Constraint) necessárias) n CONTA 1 Agencia_ conta n 1 Depositant e agenciaempréstimo n CLIENTE AGENCIA n n devedor n EMPRÉSTIMO 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) 11

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

• 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 A 1, A 2, . . . , An from r 1, r 2, . . . , 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. 13

– 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 14

• WHERE – Encontre todos os números de empréstimos feitos na agencia Batel com totais emprestados acima de R$1. 200. select num_emp, total from emprestimo where nome_ag = ‘Batel’ and total > 1. 200 – O SQL usa os conectores AND, OR e NOT, além do operadores de comparação <, <=, >, >=, = e <> 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 R$9. 000 e R$100. 000 select num_emp, total from emprestimo where total between 9000 and 100000 – Not between também é permitido. 15

• 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 16

• ALIASES – Pode-se renomear tanto atributos qto Tabelas. – Para renomear Tabelas ou atributos utilizamos a cláusula as que pode aparecer 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 > S. fundos and S. cidade_ag = ‘Ctba’ 17

• 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 18

– 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 “ab%cd” • like “ab\cd%” escape “” corresponde a todas as strings que começam com “abcd” – é possível pesquisar diferenças em strings pelo operador de comparação not like 19

• 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 20

• 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 21

• 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’ 22

– 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 23

– 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 R$1. 200. select nome_ag, avg (saldo) from conta group by nome_ag having avg (saldo) > 1. 200 24

– 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) >= 3 25

• 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. 26

– 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 27

• 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) 28

– Ex 2: 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) – Ex 3: 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’) – Ex 4: 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_emp=emprestimo. num_emp where nome_ag = 'Batel' and nome_cli in (select nome_cli from depositante INNER JOIN conta ON depositante. num_conta = conta. num_conta) 29

• 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 é > 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 > 1. 200 30

• COMPARAÇÃO DE CONJUNTOS – A frase “maior que ao menos uma” pode ser representada em SQL pelo construtor > 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 >some (select fundos from agencia where cidade_ag = ‘Curitiba’) – A SQL permite comparações <some, <= some, >= some, <> some – a palavra any é sinônimo de some em SQL. – A frase “maior que todos” pode ser representada pelo construtor > all – A SQL permite comparações <all, <= all, >= all, <> all 31

– 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 > 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) >= all (select avg (saldo) from conta group by nome_ag) 32

• 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 R$200, 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. 33

• UPDATE – modifica valores de tuplas sem alterar todos os valores. – Ex 1: 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. – Ex 2: 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 > 10. 000 update conta set saldo = saldo * 0. 05 where saldo < = 10. 000 – Ex 3: Pague 5% de taxa de juros para as contas cujo o saldo seja maior que a média dos saldos. update conta set saldo = saldo * 0. 05 where saldo > (select avg (saldo) from conta) 34

• 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 – Exemplo 1: 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. delete from conta where saldo < (select avg (saldo) from conta) 35

• VISÕES (VIEW) – Definimos uma visão: create view v as <expressão da consulta> – Ex 1: 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_conta=conta. 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’) 36

• Ex 2: 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-998 l’, 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. 37