Banco de Dados
UNIVAP - CTI
1 – INTRODUÇÃO
Neste curso estaremos estudando conceitos e práticas normalmente utilizadas quando projetamos em
banco de dados relacionais.
Vamos utilizar neste curso o Sistema Gerenciador de Banco de Dado (SGBD) SQL-Server 2005
Express Edition. Vale ressaltar que no mercado existem vários softwares desenvolvidos para gerenciar banco
de dados, tais como:
• Access
• MySQL
• Postgree (gratuito)
• Oracle
• Paradox
• Firebird (gratuito)
• Etc...
O SQL-Server 2005 Express Edition é um software oferecido gratuitamente no site da Microsoft. Você
poderá baixá-lo diretamente do seguinte endereço:
http://www.microsoft.com/downloads/details.aspx?familyid=4C6BA9FD-319A-4887-BC75-3B02B5E48A40&displaylang=en
Cuidado, pois existem outros pacotes do SQL-SERVER 2005 EXPRESS, porém não oferecem
ADVANCED SERVICES (serviços avançados). Estes serviços nos permitem manter os bancos de dados
armazenados em um servidor.
Esta versão será usada neste módulo do curso, pois, trata-se de um software LIVRE, ou seja, você não
precisará ter uma licença para usá-lo em seu computador.
Antes de falarmos do SGBD SQL-Server, temos que revisar vários conceitos relevantes sobre banco
de dados.
Um banco de dados relacional, gerado por um SGBD, possui várias TABELAS que se relacionam entre
si, através de CAMPOS. Daí se origina o nome dado a este tipo de banco de dados, RELACIONAL.
Os CAMPOS das tabelas serão utilizados para armazenar REGISTROS. Vale dizer que cada registro é
composto por CARACTERES.
O esquema abaixo ilustra os conceitos abordados acima:
Banco de Dados Tabelas Campos Registros Caracteres
Professor: Alberson Wander
Página: 1
Banco de Dados
UNIVAP - CTI
2 – VOCABULÁRIOS USADOS EM BANCO DE DADOS
Quando consultamos qualquer bibliografia de banco de dados, o vocabulário utilizado pelos autores
são os seguintes:
ENTIDADES São as tabelas do banco de dados;
ATRIBUTOS São os campos das entidades;
DOMÍNIOS São todos os valores possíveis de dados armazenados em um atributo. Exemplo: Todos os
códigos de peças armazenados no atributo CODPEÇA.
TUPLA Cada registro armazenado numa entidade; Lembre-se que um registro refere-se ao conjunto de
dados armazenados em todas os atributos de uma tabela.
CHAVE PRIMÁRIA um ou mais atributos, usados para identificar e unificar as tuplas armazenadas;
CHAVE ESTRANGEIRA atributo existente em uma entidade filho, ou seja, entidade que recebe dados de
outra entidade em um relacionamento. A entidade FILHO, no relacionamento, deve possuir os mesmos
atributos da chave primária da tabela PAI, e estes devem ser do mesmo tipo e tamanho, a fim de tornar
possível o relacionamento entre as tabelas.
3 – POR QUE USAR UM SISTEMA GERENCIADOR DE BANCO DE DADOS?
A resposta para essa pergunta pode ser dada baseada nas seguintes vantagens:
• É compacto Não há necessidade de arquivos de papéis;
• Acesso rápido Os computadores podem acessar rapidamente qualquer informação
armazenada num Banco de dados (B.D.).
• Menos trabalho braçal Os funcionários de empresas não precisam preocupar-se
com o arquivamento de fichas, pois o computador é responsável por fazê-lo.
• Fluxo corrente informações certas e atualizadas a qualquer momento, basta pedir.
4 – VANTAGENS EM USARMOS BANCO DE DADOS RELACIONAIS
Um banco de dados deve oferecer para empresas algumas vantagens. Estas vantagens facilitarão a
tomada de decisões e permitirão a armazenagem de dados confiáveis. Veja as vantagens:
• Redução na redundância de dados eliminar repetições DESNECESSÁRIAS em
atributos das entidades.
• Compartilhamento de dados pelas aplicações novas e as já existentes;
• Padronização de padrões esta tarefa refere-se à padronização de dados e
documentos dentro da empresa, para facilitar o compartilhamento e a compreensão de
dados;
• Restrições de segurança referem-se aos controles de acessos aos usuários do
banco de dados (pessoas ou aplicações).
• Manter a integridade de dados possuir nas entidades do banco de dados
informações atualizadas e consistentes, para tomada de decisões dentro da empresa.
5 – NORMALIZAÇÕES DE ENTIDADES DO BANCO DE DADOS
Quando estamos criando as tabelas de um banco de dados, devemos tomar o cuidado de retirarmos
dessas os campos que armazenarão registros redundantes desnecessários. A esse processo damos o nome
de NORMALIZAÇÃO. Em alguns momentos a redundância de dados torna-se necessária, como veremos
adiante.
Normalizar, diante do que foi descrito acima, nada mais é do que:
A) MINIMIZAR REDUNDÂNCIAS e INCONSISTÊNCIAS de dados;
B) FACILITAR A MANIPULAÇÃO DO BANCO DE DADOS;
C) FACILITAR A MANUTENÇÃO DOS DADOS NO BANCO DE DADOS.
Antes de normalizarmos as ENTIDADES de um banco de dados relacional, devemos identificar as
ANOMALIAS NELAS EXISTENTES.
Professor: Alberson Wander
Página: 2
Banco de Dados
UNIVAP - CTI
5.1 – ANOMALIAS DE ENTIDADES DE BANCO DE DADOS
As anomalias mais comuns existentes em ENTIDADES de um banco de dados são as seguintes:
• Anomalia da INCLUSÃO;
• Anomalia da INCONSISTÊNCIA;
• Anomalia da ALTERAÇÃO (atualização);
• Anomalia da EXCLUSÃO;
Vamos ilustrar as situações de anomalias de entidades. Com isso será possível sabermos quando
devemos normalizar a ENTIDADE. Observe a entidade abaixo:
Pedidos_Pecas
Num_pedido
Data
Num_peça
Descricao
Quantidade
Preco
1000
14/11/98
Ax12
Bicicleta
3
90,00
1020
15/11/98
BT04
Tv
10
400,00
1030
15/11/98
BZ66
Bola
300
5,00
1040
16/11/98
BT04
Tv
4
160,00
1050
17/11/98
CB03
Vidro
5
25,00
1070
20/11/98
BT04
Bola
2
80
5.1.1 – Anomalia de INCLUSÃO
Repare na entidade Pedidos_pecas, mostrada no capítulo 5.1 desta apostila, que para trabalhar com
uma nova peça no banco de dados, o usuário será obrigado fazer um pedido da mesma, visto que não é
possível somente cadastrar dados da peça. Este é um exemplo prático de uma anomalia de INCLUSÃO.
5.1.2 – Anomalia de ALTERAÇÃO (atualização)
Repare na entidade Pedidos_pecas, mostrada no capítulo 5.1 desta apostila, que ao efetuar UMA
alteração na peça BT04, essa se fará necessária em várias outras tuplas da entidade, visto que vários pedidos
desta mesma peça foram cadastrados na entidade. Este é um exemplo prático de uma anomalia de
ALTERAÇÃO.
5.1.3 – Anomalia de EXCLUSÃO
Repare na entidade Pedidos_pecas, mostrada no capítulo 5.1 desta apostila, que ao excluirmos o
pedido 1000 perderemos também qualquer referência da peça AX12, pois esta foi requisitada somente em um
pedido. Imagine o que aconteceria se esta peça fosse vendida para algum cliente! Este é um exemplo prático
de uma anomalia de EXCLUSÃO.
5.1.4 – Anomalia de INCONSISTÊNCIA
Repare na entidade Pedidos_pecas, mostrada no capítulo 5.1 desta apostila, que não há nada que
impeça que a peça BT04 seja cadastrada com várias descrições diferentes. Isso seria uma catástrofe no
momento de uma venda! Este é um exemplo prático de uma anomalia de INCONSISTÊNCIA.
Diante das anomalias exemplificadas anteriormente iremos falar neste ponto das normalizações de
entidades de um banco de dados. Existem 6 formas de normalizarmos uma entidade, segundo CODD, a saber:
• 1NF primeira forma normal;
• 2NF segunda forma normal;
• 3NF terceira forma normal;
• BCNF Forma normal de Boyce Codd;
• 4NF quarta forma normal;
• 5NF quinta forma normal;
Professor: Alberson Wander
Página: 3
Banco de Dados
UNIVAP - CTI
5.2 – 1NF (PRIMEIRA FORMA NORMAL)
Uma relação está na 1FN, se somente todos os domínios básicos (CONTEÚDOS DE CADA CAMPO)
contiverem somente valores atômicos (não repetidos). Para atingir esta forma normal devemos eliminar os
grupos de registros repetidos. Como?
Procedimentos:
a) Identificar a chave primária da entidade;
b) Identificar o grupo de atributos que causam a repetição de registros e excluí-lo da entidade;
c) Criar uma nova entidade com a chave primária da entidade anterior e o grupo de atributos que causavam a
repetição.
A chave primária da nova entidade será obtida pela concatenação da chave primária da entidade
inicial e a chave primária do grupo repetitivo.
Figura 1
A ENTIDADE ACIMA NÃO ESTÁ NORMALIZADA, PORTANTO DEVEMOS NORMALIZÁ-LA PARA A 1NF,
pois apresenta as seguintes ANOMALIAS:
a) Para toda nota fiscal cadastrada, há necessidade de informarmos o código, a descrição e o
preco de venda de CADA mercadoria da nota. Desta forma, corremos o risco de
cadastrarmos uma descrição ou preço, de uma MESMA MERCADORIA, de diferentes
formas.
b) Caso seja necessário alterar a descrição ou preço de venda de uma mercadoria,
correríamos o risco de alterar somente em uma tupla da entidade.
c) Se quisermos excluir uma determinada mercadoria da entidade, deveremos procurá-la
tupla a tupla, o que tornaria uma tarefa trabalhosa.
d) Caso excluíssemos da entidade uma mercadoria que fosse cadastrada uma única vez,
perderemos as informações dessas para consultas futuras.
Para solução das anomalias acima, deveremos normalizar a tabela notafiscal para a 1NF, conforme
figura abaixo:
Figura 2
Professor: Alberson Wander
Página: 4
Banco de Dados
UNIVAP - CTI
OBSERVAÇÃO IMPORTANTE: Na nova entidade “Vendas”, ilustrada na figura anterior, note que a
chave primária é composta pela chave da tabela NotaFiscal (NumeroNotaFiscal) e CódigoMercadoria.
5.3- 2NF (SEGUNDA FORMA NORMAL)
Uma relação está na 2FN, se e somente se, ela estiver na 1NF e todos os atributos não chave forem
totalmente dependentes da chave primária (dependente de toda a chave e não apenas de parte dela).
Procedimentos:
a) Identificar os atributos que não são funcionalmente dependentes de toda a chave primária.
b) Remover da entidade todos esses atributos identificados e criar uma nova entidade com eles.
A chave primária da nova entidade será o atributo nos quais os atributos removidos são funcionalmente
dependentes.
Exemplo: Vejamos as entidades a seguir:
Notas Fiscais:
(Num. NF, Série, Código do Cliente, Nome do cliente, Endereço do cliente, Total Geral da Nota)
Arquivo de Vendas:
(Num. NF, Código da Mercadoria, Descrição da Mercadoria, Quantidade vendida, Preço de venda e Total da
Venda)
Figura 3
AS ENTIDADES ACIMA NÃO ESTÃO NA SEGUNDA FORMA NORMAL,
MAS SIM NA PRIMEIRA FORMA NORMAL
Normalizando para segunda forma normal (2FN):
Arquivo de Notas Fiscais (Num. NF, Série, Código do Cliente, Nome do cliente, Endereço do cliente, Total
Geral da Nota)
Arquivo de Vendas (Num. NF, Código da Mercadoria, Quantidade vendida e Total da Venda)
Arquivo de Mercadorias (Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Figura 4
Como resultado desta etapa, houve um desdobramento do arquivo de Vendas (a ENTIDADE
NOTAFISCAL, não foi alterada, por não possuir chave composta) em duas estruturas, a saber:
Professor: Alberson Wander
Página: 5
Banco de Dados
UNIVAP - CTI
Primeira estrutura (Arquivo de Vendas): Contêm os atributos originais, sendo excluídos os dados que
são dependentes apenas do campo Código da Mercadoria.
Segundo estrutura (Arquivo de Mercadorias): Contém os elementos que são identificados apenas
pelo Código da Mercadoria, ou seja, independentemente da Nota Fiscal, a descrição e o preço de venda serão
constantes.
5.4- 3FN (Terceira Forma Normal)
Uma relação está na 3FN se somente estiver na 2FN e todos os atributos não chave forem
dependentes não transitivos da chave primária (cada atributo for funcionalmente dependente apenas dos
atributos componentes da chave primária, ou se, todos os seus atributos não chave forem independentes entre
si).
Procedimentos:
a) Identificar todos os atributos que são funcionalmente dependentes de outros atributos não chave;
b) Removê-los e criar uma nova entidade com os mesmos.
A chave primária da nova entidade será o atributo nos quais os atributos removidos são funcionalmente
dependentes.
Estrutura na segunda forma normal (2FN): Observem as entidades abaixo:
Notas Fiscais:
(Num. NF, Série, Data emissão, Código do Cliente, Nome do cliente, Endereço do cliente, Total Geral da Nota)
Vendas:
(Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria)
Mercadorias:
(Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Estrutura na terceira forma normal (3FN): Observem as entidades abaixo:
Notas Fiscais:
(Num. NF, Série, Data emissão, Código do Cliente e Total Geral da Nota)
Vendas:
(Num. NF, Código da Mercadoria, Quantidade vendida e Total da venda desta mercadoria)
Mercadorias:
(Código da Mercadoria, Descrição da Mercadoria, Preço de venda)
Clientes:
(Código do Cliente, Nome do cliente, Endereço do cliente)
Como resultado desta etapa, houve um desdobramento do arquivo de Notas Fiscais, por ser o único que
possuía campos que não eram dependentes da chave principal (Num. NF), uma vez que independente da Nota
Fiscal, o Nome, Endereço são inalterados. Este procedimento permite evitar inconsistência nos dados dos
arquivos e economizar espaço, por eliminar o armazenamento freqüente e repetidas vezes destes dados. A
cada nota fiscal comprada pelo cliente, haverá o armazenamento destes dados e poderá ocorrer divergência
entre eles.
As estruturas alteradas e o motivo das alterações:
- Primeira estrutura (Notas Fiscais): Contêm os elementos originais, sendo excluído os dados que são
dependentes apenas do campo Código do Cliente (informações referentes ao cliente).
- Segundo estrutura (Clientes): Contém os elementos que são identificados apenas pelo Código do Cliente, ou
seja, independente da Nota Fiscal, o Nome, Endereço serão constantes.
Após a normalização, as estruturas dos dados estão projetadas para eliminar as inconsistências e
redundâncias dos dados, eliminando desta forma qualquer problema de atualização e operacionalização do
Professor: Alberson Wander
Página: 6
Banco de Dados
UNIVAP - CTI
sistema. A versão final dos dados poderá sofrer alguma alteração, para atender as necessidades específicas
do sistema, a critério do analista, durante a fase de desenvolvimento do projeto físico do sistema.
5.5- BCNF ou NFBC (Forma Normal de BOYCE CODD) / 4FN (Quarta Forma Normal) e 5FN(Quinta Forma
Normal)
A BCNF, a 4FN e a 5FN não serão abordadas neste curso. Ressalta-se que estas normalizações
existem e devem ser tratadas em cursos que abordam a fundo as teorias de banco de dados. Vale dizer
também, que o objetivo desse curso é aprofundarmos na linguagem Transact-SQL e conhecer a ferramenta
SQL-SERVER, o que faz necessário termos noções básicas de normalizações e relacionamentos entre
entidades de um banco de dados relacional.
Antes de normalizarmos as tabelas de um banco de dados devemos passar por etapas de modelagem
de dados. As etapas envolvidas na construção de modelos de dados são:
• Modelo Conceitual
• Modelo Lógico
• Modelo Físico
– MODELO CONCEITUAL
Nesta etapa da modelagem de dados o DBA deve:
• Conhecer o negócio
• Rascunhar as principais entidades do BD. e seus principais atributos.
• Não se preocupar com os relacionamentos n : n.
– MODELO LÓGICO
Nesta etapa da modelagem de dados o DBA deve:
• Representar o negócio.
• Criar as entidades de relacionamentos para substituir os relacionamentos n:n.
• Definir as chaves primárias de cada entidade.
• Normalizar as tabelas.
• Adequar aos padrões do banco de dados escolhido, neste caso, ao padrão do SQL
SERVER.
• Documentar as entidades e seus atributos.
– MODELO FÍSICO
Nesta etapa da modelagem de dados o DBA deve:
• Tomar ciência das limitações do banco de dados, neste caso, o SQL-SERVER.
• Considerar os requisitos dos softwares que farão acesso ao banco de dados.
• Criar fisicamente as entidades, relacionamentos, chaves, índices, definir níveis de
acessos entre outros, ou seja, criar fisicamente o banco de dados projetado nas etapas
anteriores.
6 – SQL-SERVER
De agora para frente iremos abordar assuntos relativos à ferramenta SQL-SERVER 2005 Express
Edition.
Comparando o SQL-SERVER com o ACCESS, podemos destacar principalmente entre as diferenças,
a capacidade de armazenamento, visto que o ACCESS manipula no MÁXIMO 2GB de registros distribuídos
entre as entidade de um banco de dados. Já o SQL-SERVER é capaz de manipular registros desde que haja
espaço no HD. Porém, esta versão que estamos usando, 2005 EXPRESS EDITION, permite manipularmos em
cada arquivo de banco de dados no máximo de 4 GB.
Outra grande vantagem do SQL-SERVER refere-se ao fato do mesmo trabalhar com arquitetura
cliente-servidor. Com isso, todos os bancos de dados criados no SQL poderão ser acessados pelo cliente, no
servidor.
Um Servidor SQL pode conter vários bancos de dados, que por sua vez são acessados por vários
usuários.
Professor: Alberson Wander
Página: 7
Banco de Dados
UNIVAP - CTI
Num banco de dados criado no SQL-SERVER, poderemos limitar os acessos ao próprio banco, como
também às entidades e até mesmo seus atributos. Tarefas como leitura, gravação, exclusão e consulta à tuplas
também podem ser limitadas para determinados usuários.
6.1 – COMPONENTES DO SQL-SERVER 2005 Express Edition
Os principais componentes do SQL-SERVER são os seguintes:
• Banco de dados
• Tabelas
• Diagrams
• Visões
• Stored Procedures
• Triggers
• Etc..
A seguir veremos com mais detalhes cada componente de um banco de dados do SQL.
6.1.1 – BANCO DE DADOS
Um banco de dados é composto por:
• Tabelas;
• Visões;
• Triggers;
• Stored Procedures;
• E outros;
Como dito anteriormente o SQL Server 2005 Express Edition pode manter vários bancos de dados.
Cada novo banco de dados criado possuirá os componentes citados acima. A figura abaixo ilustra os
componentes dos bancos de dados criados no servidor SQL:
Figura 5
Repare que o banco de dados MASTER possui seus componentes: Tables, Views, Stored Procedures
e etc. Perceba que no banco de dados NORTWIND, estes mesmos componentes estão presentes.
Professor: Alberson Wander
Página: 8
Banco de Dados
UNIVAP - CTI
6.1.6 – TRIGGERS
Uma trigger nada mais é do que uma rotina de programação que é disparada (executada)
automaticamente, diante de um cadastro, alteração e/ou exclusão de registros em uma tabela.
Quando criamos a trigger informamos em que momento ela será disparada.
Nas triggers são utilizados comandos da linguagem Transact-SQL.
7 – O AMBIENTE DO SQL-SERVER 2005 EXPRESS EDITION
Para acessarmos o SQL-Server 2005 Express Edition, devemos acessá-lo no grupo de programas do
SQL, conforme mostrado abaixo:
Figura 7
Ao clicar na opção “SQL Server Management Studio Express”, surgirá então a seguinte tela:
Professor: Alberson Wander
Página: 10
Banco de Dados
UNIVAP - CTI
Figura 8
Na tela acima, na janela “Connect to Server”, não se esqueça de informar o conteúdo para o campo
“Server name”, pois esta informação refere-se ao nome do servidor SQL instalado no seu computador.
Inclusive o nome que aparece no exemplo acima, com certeza será diferente no seu computador.
Por fim, para efetuar a conexão com o servidor SQL, clique no botão “Connect”. Surgirá então as
seguintes áreas do ambiente do SQL-SERVER:
Figura 9
Professor: Alberson Wander
Página: 11
Banco de Dados
UNIVAP - CTI
7.1 – ASSISTENTE DE CRIAÇÃO DE BANCO DE DADOS NO SQL-SERVER
Figura 10
Para criação de um novo banco de dados basta:
• Clicarmos com botão direito do mouse sobre “Databases”
• No menu que surgir (mostrado na figura acima) clique na opção “New Database”
• Na tela que surgir, indique o nome do banco de dados que será criado. Digite o nome do banco
no campo “Database Name”, conforme ilustra a figura abaixo:
Figura 11
•
O arquivo do banco de dados será gravado na pasta do servidor SQL-SERVER, localizada
em “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data”.
Professor: Alberson Wander
Página: 12
Banco de Dados
•
UNIVAP - CTI
Para concluir a criação física do arquivo de banco de dados, basta clicar no botão “OK”.
Após seguir os passos descritos anteriormente, seu banco de dados será mostrado como nos
exemplos abaixo, indicados pelas setas:
Figura 12
7.2 – ATTACH DATABASE
Este recurso é utilizado para acessarmos um banco de dados no SQL-SERVER.
Quando houver necessidade de copiarmos um banco de dados para outro servidor SQL, devemos
“atachá-lo” neste servidor. Os arquivos de log (.ldf), e do banco de dados (.mdf) devem ser copiados para o
computador, NA PASTA “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data”, onde se encontra o
servidor SQL.
Em seguida, na janela “Object Explorer” siga os passos abaixo:
• Clique com o botão direito do mouse sobre o componente “Databases”;
• Escolha a opção “Attach...”;
A figura abaixo ilustra os passos acima:
Figura 13
Professor: Alberson Wander
Página: 13
Banco de Dados
UNIVAP - CTI
Surgirá então a seguinte janela:
Figura 14
Siga os passos abaixo, a partir da janela mostrada acima, para “atachar” (puxar/fixar/carregar) o banco
de dados copiado anteriormente para a pasta do servidor SQL.
•
•
Na janela acima, dê um clique no botão “Add...”.
Surgirá a janela “Locate Database Files”, mostrada abaixo:
Figura 15
Professor: Alberson Wander
Página: 14
Banco de Dados
UNIVAP - CTI
Obs.: Repare que a janela da mostrada, mostra todos os bancos de dados (arquivos .mdf), existentes
na pasta do servidor.
•
Basta, então, selecionar o nome do banco de dados desejado e clicar no botão “OK”. Desta
forma o banco de dados aparecerá na janela “Object Explorer”.
7.3 – CRIAÇÃO DE TABELAS NO BANCO DE DADOS
Para criação de tabelas em um banco de dados é necessário abri-lo primeiramente. Faça isso clicando
no nome do banco de dados, mostrado na janela “Object Explorer”, conforme exemplo abaixo:
Para criar uma nova tabela em um banco de dados, siga os passos a seguir:
• clique com o botão direito do mouse no objeto “Table”;
• Surgirá um submenu, neste selecione a opção “New Table...”, conforme figura abaixo:
Figura 16
•
Surgirá então as seguintes áreas, apontadas pelas setas:
Figura 17
Professor: Alberson Wander
Página: 15
Banco de Dados
UNIVAP - CTI
A área mostrada é usada para criar uma nova tabela. Nela indique:
Column Name: Nesta coluna você deve indicar os nomes dos atributos da entidade (Campos da
tabela) que serão criados.
Data Type: Indique o tipo de dado de cada atributo criado.
Allow Nulls: Quando esta coluna apresentar uma marca de checagem
indicará que o
atributo em questão aceitará gravação registros sem que o usuário tenha informado um dado qualquer para o
referido campo.
Para criar fisicamente a tabela, basta clicar no botão
e indicar o nome da tabela na janela que
surgirá.
Após criar a tabela, quando necessário, será possível alterá-la. Para isto, basta clicar com o botão
direito do mouse sobre o nome da tabela, estando com a árvore de objetos “Table” aberta, no banco de dados
onde a mesma se encontra, conforme a figura abaixo:
Figura 18
Ao clicar na opção “Design” no submenu mostrado, a área estrutura da tabela será novamente
mostrada, para que se possa incluir, alterar ou excluir campos.
7.4 – CRIAÇÃO DE CHAVE PRIMÁRIA PARA TABELAS
Para criação de chaves primárias siga os passos abaixo:
• Selecione o(s) atributos (s) que irão compor a chave primária;
•
Clique no botão
, mostrado na figura a seguir.
Veja o exemplo abaixo:
Figura 19
Professor: Alberson Wander
Página: 16
Banco de Dados
UNIVAP - CTI
Na figura, foram selecionados para chave primária os atributos: “codigo" e “cpf”. Em seguida, foi clicado
no botão da chave, disposto na barra de ferramentas. Esses processos fizeram dos atributos indicados, chave
primária COMPOSTA da tabela.
Para exemplificar, vamos excluir alguns atributos da entidade “professores”. Veja como ficará o layout
da tabela:
Figura 20
Para excluir um atributo da tabela selecione-o e pressione o botão “Delete”.
7.5 – RELACIONANDO TABELAS(ENTIDADES)
O relacionamento entre tabelas normalizadas é outra importante operação disponível nos SGBD’s. No
SQL-SERVER, utilizaremos inicialmente o objeto “DIAGRAMS” para relacionar as tabelas do banco de dados.
Antes, porém, é importante citarmos os tipos de relacionamentos existentes entre tabelas de um banco
de dados, a saber:
1. 1..N: Um único registro da tabela PAI pode se relacionar com vários registros na tabela filho;
2. 1..1: Um único registro da tabela PAI pode se relacionar com um único registro na tabela filho;
3. N..N: Vários registros da tabela PAI poderão se relacionar com vários registros na tabela filho.
É importante ressaltar que este tipo de relacionamento não deve existir num banco de dados.
Para isso, devemos criar uma tabela de entidade de relacionamento, que fará com que a
relação se torne 1..n. Veremos a solução prática para este exemplo mais a frente;
IMPORTANTE:
1. PARA RELACIONAR DUAS OU MAIS TABELAS, NÃO DEVEM EXISTIR REGISTROS CADASTRADOS
NAS MESMAS.
2. Os campos envolvidos no relacionamento (chave(s) primária(s) e chave(s) estrangeira(s) devem ser
do MESMO TIPO E TAMANHO). OS NOMES DE TAIS CHAVES NÃO PRECISAM SER O MESMO.
7.5.1 – RELACIONAMENTO 1..N
Conforme descrito anteriormente, este tipo de relacionamento faz com que 1 registro da tabela Pai se
relacione com vários outros na tabela FILHO. Vejamos o exemplo na figura a seguir:
Professor: Alberson Wander
Página: 17
Banco de Dados
UNIVAP - CTI
Figura 21
Reparem na figura que foram criadas as tabelas “professores” e “telefonesprofessor”.
Analise as seguintes observações:
1. Na tabela “professores”, verifique que o campo “codigoprofessor” é chave primária. Isso fará com
que exista somente UM professor com um código específico.
2. Na tabela “telefonesprofessor”, veja que existe uma chave primária COMPOSTA pelos campos
“codprofessor” (também conhecida como chave estrangeira) e “telefoneprofessor”. Isso possibilitará
o cadastro de vários telefones para um professor. Por exemplo:
Figura 22
3. Se relacionamos as duas tabelas, veremos que o tipo de relacionamento será 1..N, conforme
mostrado na figura abaixo:
1
N
Figura 23
4. Na tabela FILHO, SE O CAMPO “CodProfessor” não fizesse parte da chave primária, o tipo de
relacionamento ainda sim seria 1..N. Conforme ilustra a figura abaixo:
Figura 24
7.5.2 – RELACIONAMENTO 1..1
Neste tipo de relacionamento teremos o campo chave primária da tabela PAI sendo também sendo o
campo chave da tabela FILHO, ou seja, o mesmo campo chave primária nas tabelas envolvidas no
Professor: Alberson Wander
Página: 18
Banco de Dados
UNIVAP - CTI
relacionamento. Por esse motivo temos um único registro cadastrado somente uma vez em ambas as tabelas,
o que torna o relacionamento 1..1. Veja o exemplo mostrado na figura abaixo:
1
1
Figura 25
Analise a seguintes observação:
• A figura acima mostra que para cada professor cadastrado na tabela “professores”, poderemos
ter SOMENTE UM telefone cadastrado na tabela “telefoneprofessor”, ISTO PORQUE somente
o campo “codprofessor” é chave primária e estrangeira nesta tabela.
7.5.3 – RELACIONAMENTO N..N
Este tipo de relacionamento não deve aparecer em banco de dados. Eles são “excluídos” devido à
necessidade de criação de uma tabela chamada “entidade de relacionamento”.
Uma entidade de relacionamento deve conter os campos chaves das tabelas que possuem o tipo de
relacionamento N..N. Com isso, relacionam-se as tabelas PAI e FILHO com esta entidade de relacionamento.
Veja a figura abaixo, ela representa a situação descrita acima:
Figura 26
Professor: Alberson Wander
Página: 19
Banco de Dados
UNIVAP - CTI
Se relacionarmos as tabelas criadas teremos:
1
N
N
1
Figura 27
Analisemos o relacionamento acima:
1. Com o relacionamento acima, tornamos possível que um professor tenha vários telefones e que um
mesmo telefone pertença a mais de um professor. Um exemplo típico para esta situação é a de
professores casados que tenham os mesmos telefones de contato.
2. A tabela “TelefonesProfessores” foi criada para solucionarmos o tipo de relacionamento N..N.
Portanto esta tabela é chamada de “entidade de relacionamento” entre as tabelas “professores” e
“telefones”.
7.5.4 – CRIAÇÃO DE RELACIONAMENTOS PELO OBJETO DATABASE DIAGRAMS
Como visto anteriormente, o objeto “DATABASE DIAGRAMS” é utilizado para criarmos os
relacionamentos entre tabelas de um banco de dados.
Utilize os passos abaixo para relacionar as tabelas de um banco de dados:
1. Clique com o botão direito do mouse sobre o objeto “Database Diagrams”, disposto na árvore
de objetos do banco de dados em aberto;
2. No menu que irá surgir, clique sobre “New Database Diagram”, conforme ilustra a figura a
seguir:
Figura 28
Após seguir os passos acima, veremos a janela do assistente de criação de relacionamentos, chamada
“Add Table”, conforme ilustra a próxima figura:
Professor: Alberson Wander
Página: 20
Banco de Dados
UNIVAP - CTI
Figura 29
Usando a janela “Add Table” clique no botão “Add” para incluir as tabelas que serão relacionadas,
neste exemplo serão relacionadas todas as tabelas. Após adicionar todas as tabelas que serão relacionadas
entre si, basta clicar no botão “Close”, para fechar a janela “Add Table”. Veja o resultado na figura abaixo:
Figura 30
Professor: Alberson Wander
Página: 21
Banco de Dados
UNIVAP - CTI
Se você notar que se esqueceu de relacionar uma tabela qualquer na área acima, clique com o botão
direito do mouse sobre a área onde as tabelas estão sendo mostradas, e em seguida clique na opção “Add
Table...”. Com isso você poderá inserir outras tabelas na área de diagramas acima.
Para relacionarmos as tabelas mostradas na área de diagramas, devemos arrastar O(S) CAMPO(S)
DA CHAVE PRIMÁRIA, DA TABELA PAI, para cima do(s) CAMPO(S) CHAVE ESTRANGEIRA na tabela
FILHO. Quando este processo for feito, surgirá a tela “Tables and Columns”, para definições gerais do
relacionamento que será criado:
Figura 31
Na janela acima, temos:
Primary Key Table: Indica o nome da tabela PAI onde está a chave primária;
Foreign Key Table: Indica o nome da tabela FILHO onde se encontra a chave estrangeira;
: Indicam quais os campos serão relacionados.
Caso algum campo não esteja relacionado, basta clicar na linha e seleciona-lo na caixa que será aberta.
Ao termino da indicação dos campos que serão relacionados, clique no botão “OK”. A janela será
fechada e a seguinte janela será mostrada:
Professor: Alberson Wander
Página: 22
Banco de Dados
UNIVAP - CTI
Figura 32
Na janela acima observe as seguintes opções:
Figura 33
Delete Rule: Com a opção “Cascade” selecionada, as exclusões de registros na tabela “PAI” excluirão
automaticamente registros relacionados na tabela “FILHO”.
Update Rule: Com a opção “Cascade” selecionada, as alterações em registros na tabela “PAI” alterarão
automaticamente registros relacionados na tabela “FILHO”.
O botão “OK”: este botão criará o relacionamento físico entre as tabelas PAI e FILHO. O tipo do
relacionamento será indicado AUTOMATICAMENTE, diante das definições de chave primária e estrangeiras,
nas tabelas PAI e FILHO.
Após o término das definições dos relacionamentos, criamos um relacionamento físico entre as tabelas,
conforme ilustra a próxima figura:
Professor: Alberson Wander
Página: 23
Banco de Dados
UNIVAP - CTI
Figura 34
IMPORTANTE:
Tabela PAI: Refere-se à tabela que ENVIA dados para outra tabela da relação;
Tabela FILHO: Refere-se à tabela que RECEBE dados da tabela pai da relação;
Vale dizer que TODOS os relacionamentos devem ser criados, antes de gravá-los. No exemplo acima
criamos somente a relação entre as tabelas “professores” e “telefoneprofessor”. Poderíamos relacionar também
a tabela “univap” com a tabela “telefoneprofessor”.
Para gravar os relacionamentos criados, basta clicar no botão
, disponível na barra de ferramentas
do SQL-SERVER. Dê o nome que desejar para os relacionamentos criados.
8 – MANIPULANDO TUPLAS EM UMA ENTIDADE
Depois de criados os relacionamentos entre as tabelas do banco de dados, você poderá gravar e
manipular registros nessas.
LEMBRETE: NÃO GRAVE REGISTROS NAS TABELAS ANTES DE RELACIONÁ-LAS. Se as
tabelas possuírem registros, você não conseguirá criar os relacionamentos, pois o SGBD irá verificar se
existem registros que farão com que se perca a integridade de dados, ou seja, por exemplo: a tabela FILHO
possua registros não cadastrados anteriormente na tabela PAI.
Para cadastrar registros É NECESSÁRIO FAZÊ-LO PRIMEIRAMENTE NA TABELA PAI.
Baseado nas últimas tabelas, mostradas anteriormente nesta apostila, vamos cadastrar registros na
tabela “professores”, para isso siga os passos a seguir:
• Clique no objeto “Tables”, na árvore de objetos do SQL-SERVER, para que o SQL mostre
a árvore com nomes de todas as tabelas existentes no banco de dados desejado;
• Clique com o botão direito do mouse sobre o nome da tabela desejada;
• No submenu, clique sobre “Open Table...”, conforme ilustrado na figura abaixo:
Figura 35
Professor: Alberson Wander
Página: 24
Banco de Dados
UNIVAP - CTI
Seguidos os passos acima, surgirá a seguinte tela de edição de registros do SQL-SERVER:
Figura 36
Para CADASTRAR um registro, basta começar a digitar o “codigoprofessor” , o “nomeprofessor” e
“idadeprofessor”. Após a digitação da idade do professor, basta pressionar <enter>, para o registro ser gravado
na tabela.
Para ALTERAR um registro, basta clicar no campo do registro desejado e redigitar o dado. O simples
fato de passar para um próximo registro, fará com que a alteração seja concretizada.
e
Para EXCLUIR um registro, basta clicar na seta que aponta para o registro corrente
pressionar a tecla “Delete”, no teclado do computador. Este processo irá excluir o registro da tabela.
9 – TESTES DE INTEGRIDADE DE DADOS
Os testes de integridade de dados podem ser feitos em vários momentos, a saber:
• Na INCLUSÃO de registros nas tabelas FILHO;
• Na EXCLUSÃO e ALTERAÇÃO de registros nas tabelas PAIS.
Vamos esclarecer melhor os testes citados acima. Vamos observar os seguintes relacionamentos entre
as tabelas :
Figura 37
Agora vamos explicar na prática os testes de integridade, diante dos relacionamentos mostrados na
figura acima:
Professor: Alberson Wander
Página: 25
Banco de Dados
UNIVAP - CTI
INCLUSÃO: Na tabela “TelefonesProfessores” só será permito o cadastro de novos registros se, e
somente se, existirem professores e telefones cadastrados nas tabelas “professores” e “telefone”,
respectivamente.
EXCLUSÃO ou ALTERAÇÃO: Se no momento da criação dos relacionamentos acima, o DBA NÃO
OPTOU por EXCLUSÃO e/ou ALTERAÇÃO EM CASCATA, e tentarmos excluir, ou alterar registros de
“professores” e/ou “telefones” que estão diretamente ligados a registros na tabela “TelefonesProfessores”, o
SQL-SERVER exibirá uma mensagem de alerta, indicando que a operação escolhida não será concretizada,
isso para não ocorrer falha na integridade de dados. Na prática, imagine se conseguíssemos excluirmos um
professor cujo código, por exemplo, fosse 100, da tabela “professores” e existissem telefones do mesmo,
cadastrados na entidade de relacionamento “TelefonesProfessores”. Diante deste exemplo prático, não
saberíamos dizer qual o nome e/ou idade do professor que possui os telefones existentes na entidade de
relacionamento “TelefoneProfessores”, pois o excluímos da tabela PAI (“professores”). O mesmo controle vale
para a operação de Alteração.
10 – VISÕES
Como dito anteriormente, cada banco de dados possui o objeto “VIEWS”. Esse objeto deve ser usado
para criarmos consultas a registros de uma tabela, utilizando a instrução “SELECT” da Transact-SQL.
O objeto VIEWS de um banco de dados, cria uma tabela TEMPORÁRIA, cujos registros armazenados
serão filtrados de tabela(s) do banco em uso.
No SQL-SERVER, poderemos usar o assistente de criações de visão. Para isso, devemos abrir o
banco de dados desejado. Em seguida, siga os passos ilustrados a seguir:
Figura 38
Surgirá então a seguinte tela:
Professor: Alberson Wander
Página: 26
Banco de Dados
UNIVAP - CTI
Figura 39
Onde:
- Usado para gravar as INSTRUÇÕES que geraram uma consulta;
- Estes botões farão fechar ou mostrar, os painéis da área de trabalho mostrado na
figura;
- Executa uma consulta criada;
- Analisa uma consulta SQL criada.
- Faz uso da cláusula Group...By (será abordada posteriormente), na consulta SQL criada;
- Este botão é o mais importante da janela acima, pois ele permitirá escolhermos quais tabelas
físicas serão usadas para criação das consultas de registros.
Para ilustrar uma consulta criada pelo objeto VIEWS, vamos indicar uma tabela qualquer de um banco
de dados, por exemplo a tabela “professores”. Para isso, basta selecionarmos o nome da tabela na janela “Add
Table” e clicar no botão “Add”, conforme a figura a seguir:
Professor: Alberson Wander
Página: 27
Banco de Dados
UNIVAP - CTI
Figura 40
, a janela “Add Table” surgirá novamente, se estiver fechada. Então,
Quando clicarmos no botão
selecione o nome da tabela desejada e clique novamente no botão “Add”.
No exemplo acima, foi escolhida a tabela “professores”. Quando o botão “Add” foi clicado, a tabela foi
inserida na área de criação da visão, disposta atrás da “Add Table”. Caso necessário, você poderá utilizar,
numa só consulta, várias tabelas. Veremos mais adiante a utilidade de usarmos numa consulta várias tabelas.
Depois de inserida a tabela escolhida para consultas, podemos fechar a janela “Add Table” e
definirmos, por exemplo, quais os campos serão mostrados na consulta sql, bem como quais serão os critérios
de filtro de registros na tabela. Veja o exemplo a seguir:
Figura 41
No exemplo acima, indicamos na coluna “COLUMN”, quais os dados deveriam ser mostrados de cada
professor gravado, no caso: codigoprofessor e nomeprofessor. Isto pode ser feito clicando na caixa localizada
ao lado do nome do campo, na tabela exibida (
Professor: Alberson Wander
).
Página: 28
Banco de Dados
UNIVAP - CTI
Você deve ter reparado também, que após indicar cada campo na coluna “Column”, uma instrução
“Select” ia sendo criada automaticamente pelo SGBD.
Para executarmos o comando “SELECT codigoprofessor, nomeprofessor FROM dbo.professores”,
basta clicar no botão
. Desta forma, foram mostrados todos os códigos e nomes de professores gravados
na tabela professores. O resultado pode ser visualizado numa tabela, disposta após a linha de código gerada
pelo assistente de “visão”.
11 – CRIANDO E EXECUTANDO QUERY COM O BOTÃO “New Query”
Podemos criar banco de dados, tabelas e manusear registros utilizando linhas de comandos da
linguagem Transact-SQL. Tudo que foi mostrado até este ponto, nesta apostila, podemos fazer por meio de
comandos.
Vale dizer que no SQL-SERVER, assim como a maioria dos SGBD’s existentes, a linguagem usada
para criar banco de dados e tabelas, bem como manusear registros nessas estruturas é a SQL (Structured
Query Language).
Os comandos da SQL são divididos em cinco grupos, a saber:
1º) DML - Linguagem de Manipulação de Dados
Primeiro há os elementos da DML (Data Manipulation Language - Linguagem de Manipulação
de Dados). A DML é um subconjunto da linguagem usada para inserir, atualizar e apagar dados.
• INSERT é usada para inserir um registro (formalmente uma tupla) a uma tabela
existente.
• UPDATE para mudar os valores de dados em uma ou mais linhas da tabela
existente.
• DELETE permite remover linhas existentes de uma tabela.
2º) DDL - Linguagem de Definição de Dados
O segundo grupo é a DDL (Data Definition Language - Linguagem de Definição de Dados).
Uma DDL permite ao utilizador definir tabelas novas e elementos associados. A maioria dos bancos de
dados de SQL comerciais tem extensões proprietárias no DDL.
Os comandos básicos da DDL são poucos
• CREATE cria um objeto (uma Tabela, por exemplo) dentro da base de dados.
• DROP apaga um objeto do banco de dados.
Alguns sistemas de banco de dados usam o comando ALTER, que permite ao usuário alterar
um objeto, por exemplo, adicionando uma coluna a uma tabela existente.
Outros comandos DDL:
• ALTER TABLE
• CREATE INDEX
• ALTER INDEX
• DROP INDEX
• CREATE VIEW
• DROP VIEW
3º) DCL - Linguagem de Controle de Dados
O terceiro grupo é o DCL (Data Control Language - Linguagem de Controle de Dados). DCL
controla os aspectos de autorização de dados e licenças de usuários para controlar quem tem acesso
para ver ou manipular dados dentro do banco de dados.
Duas palavras-chaves da DCL:
• GRANT - autoriza ao usuário executar ou setar operações.
• REVOKE - remove ou restringe a capacidade de um usuário de executar
operações.
Professor: Alberson Wander
Página: 29
Banco de Dados
UNIVAP - CTI
Outros comandos DCL:
• ALTER PASSWORD
• CREATE SYNONYM
4º) DTL - Linguagem de Transação de Dados
• BEGIN WORK (ou START TRANSACTION, dependendo do dialeto SQL) pode ser usado
para marcar o começo de uma transação de banco de dados que pode ser completada ou
não.
• COMMIT envia todos os dados das mudanças permanentemente.
• ROLLBACK faz com que as mudanças nos dados existentes desde que o último COMMIT
ou ROLLBACK sejam descartadas.
COMMIT e ROLLBACK interagem com áreas de controle como transação e locação. Ambos
terminam qualquer transação aberta e liberam qualquer cadeado ligado a dados. Na ausência de um
BEGIN WORK ou uma declaração semelhante, a semântica de SQL é dependente da implementação.
5º) DQL - Linguagem de Consulta de Dados
Embora tenha apenas um comando, a DQL é a parte da SQL mais utilizada. O comando
SELECT permite ao usuário especificar uma consulta ("query") como uma descrição do resultado
desejado. Esse comando é composto de várias cláusulas e opções, possibilitando elaborar consultas
das mais simples às mais elaboradas.
Antes de abordar comandos da linguagem TRANSACT-SQL, vamos conhecer o ambiente do SQL
Server que será utilizado para digitar os comandos da DDL e da DML. Para isso, basta clicar no botão “New
Query” disposto na barra de ferramentas, mostrado abaixo:
Figura 42
Repare que ao clicar no botão “New Query”, foi aberta uma janela, mostrada acima pela seta em
vermelho.
Novos botões foram criados na barra de ferramentas, a fim de possibilitar a criação/uso de instruções
da Transact-SQL:
- Botão “Parse”: Usado para analisar a falta ou não de parâmetros de um comando SQL digitado;
- Botão “Execute” – Usado para executar as instruções SQL digitadas ou selecionadas;
Professor: Alberson Wander
Página: 30
Banco de Dados
UNIVAP - CTI
Figura 44
Na janela acima, você deve informar:
File Name: Local onde você deve definir o nome do arquivo que armazenará o resultado exposto no
grid;
Save Type: Determina o tipo de arquivo que será criado. Normalmente para acessarmos estes dados
por outros programas, podemos gravar os resultados do painel de resultados em formato TEXTO. Nesta caixa
estão disponíveis outros tipos de arquivos, tais como “SQL files”, “Text Files” ou “All Files”.
Botão “Save”: Por fim, clique neste botão para o arquivo ser gerado fisicamente.
11.2 – CRIANDO, ALTERANDO, EXCLUINDO VISÕES OU VISUALIZANDO REGISTROS FILTRADOS POR
“VISÕES”
Podemos criar/executar/excluir/alterar uma visão por meio de comandos. Estes devem ser utilizados na
área de edição de comandos SQL, depois de clicado o botão “New Query”.
Para criarmos uma visão, basta usarmos a sintaxe abaixo:
Create View <nomevisão>
AS
<instrução Select>
Por exemplo, vamos criar uma Visão para exibirmos todos os dados de professores cadastrados, que
possuam a idade superior ou igual há 30 anos. Para isso, digite o comando conforme exemplo a seguir e
pressione o botão “Execute”:
Professor: Alberson Wander
Página: 32
Banco de Dados
UNIVAP - CTI
Figura 45
Quando a sintaxe digitada estiver correta e o botão “Execute” for pressionado, a mensagem
“Command(s) completed successfully” aparecerá no painel de resultados, conforme visto na figura acima.
Podemos alterar ou excluir uma visão criada, para isso devemos usar os comandos Alter View ou Drop
View, respectivamente.
No comando Alter View, deveremos digitar novamente todos os comandos que irão compor a “Visão”.
Para isto usaremos a seguinte sintaxe:
Alter View <nome_da_visão_criada>
As
<NOVO Comando SELECT>
Baseado no exemplo da última figura se desejasse alterar a visão criada, por exemplo, para
selecionarmos todos os dados de professores que tivessem idade INFERIOR a 30 anos, escreveríamos o
seguinte comando:
Alter view mostratodos
As
Select * from professores
Where idadeprofessor < 30
, você verá novamente a mensagem “Command(s) completed
Após pressionar o botão
successfully”, que indicará que a sua “View” foi alterada corretamente.
Para excluir uma visão por meio de comando, basta usar a seguinte sintaxe:
Drop View <nome_da_visão_criada>
Se desejarmos apagar, por exemplo, a “View” criada nos exemplos anteriores, basta digitarmos:
Drop view mostratodos
Professor: Alberson Wander
Página: 33
Banco de Dados
UNIVAP - CTI
Em seguida, pressione o botão
. Se não ocorrer algo errado, novamente a mensagem
“Command(s) completed successfully” aparecerá no painel de resultados.
Para visualizarmos os registros filtrados por uma visão anteriormente criada, basta seguir os passos
ilustrados na figura a seguir:
Figura 46
Serão mostrados os registros filtrados pela instrução SELECT criada dentro da View, conforme mostra
a seta na figura acima.
O resultado exibido na figura anterior pode ser gravado da mesma forma que um resultado de
instruções digitadas na área de edição de comandos. Para isso, siga os passos descritos no item 11.1 desta
apostila.
Podemos usar os assistentes para criar, alterar e excluir “Visões”, como vimos anteriormente.
12 – TRANSACT SQL (DDL)
No SQL-SERVER é possível criar banco de dados, tabelas, relacionamentos entre as mesmas, gravar
registros, excluí-los, alterá-los e consultá-los, tudo através de instruções a linguagem SQL ((Structured Query
Language), conhecida como TRANSACT SQL.
Como citado anteriormente, os comandos da Transact SQL são divididos em grupos, conhecidos como
DDL (DATA DEFINITION LANGUAGE) e DML (DATA MANIPULATION LANGUAGE).
Vamos conhecer os principais comandos da DDL e da DML.
12.1 – CRIANDO BANCO DE DADOS (DDL)
Para criarmos um banco de dados no SQL, usamos a seguinte sintaxe:
CREATE DATABASE <nome_do_banco_dados>
Professor: Alberson Wander
Página: 34
Banco de Dados
UNIVAP - CTI
Exemplo:
Create database LOJA
Quando for executado o comando acima, serão criados fisicamente os arquivos .MDF e .LOG,
referentes ao banco de dados LOJA. Os arquivos serão criados automaticamente, pelo SQL SERVER, na
pasta do servidor. Após a execução do comando acima será exibida a seguinte mensagem:
Command(s) completed successfully.
12.2 – EXCLUINDO BANCO DE DADOS (DDL)
Quando necessário, podemos utilizar o comando DROP DATABASE, para excluir fisicamente um
banco de dados existente.
Sintaxe:
DROP DATABASE <nome_do_banco_dados>
Exemplo:
Drop database loja
È importante salientar que ao excluir um banco de dados, todo seu conteúdo também será excluído, ou
seja, tabelas, registros, visões, stored procedures, triggers e etc, serão perdidos com a exclusão.
12.3 – CRIAÇÃO DE TABELAS NUM BANCO DE DADOS (DDL)
Para criação de tabela em um banco de dados usamos o comando Create Table.
Sintaxe:
CREATE TABLE <nome_da_tabela)
(
<campo1> <tipo_campo> [primary key] [ not null ] ,
......
<campon> <tipo_campo> [not null]
)
Exemplo 1:
Create table cliente
(
Codcli int primary key not null,
Nomecli char(30) not null,
Endcli varchar(40),
Telcli varchar(25)
)
No exemplo acima está sendo criada a tabela cliente. Os campos desta tabela serão: Codcli do tipo
inteiro, será chave primária e exigirá dados no momento do cadastro de um registro; Nomecli do tipo caracter,
armazenará 40 dígitos, também exigirá dados no momento da gravação do registro; endcli do tipo varchar,
capaz de armazenar no máximo 40 dígitos no momento da gravação de um registro, porém, não é um campo
de preenchimento obrigatório; telcli do tipo varchar, capaz de armazenar no máximo 25 caracteres no momento
da gravação de um registro, porém, não é um campo de preenchimento obrigatório.
IMPORTANTE: no exemplo acima somente o campo codcli é chave primária da tabela cliente.
Professor: Alberson Wander
Página: 35
Banco de Dados
UNIVAP - CTI
Exemplo 2:
Create table depentedentecliente
(
Codcli int not null,
Coddependente int not null,
Nomedep char(40),
Idadedep int,
Primary key (codcli, coddependente)
)
IMPORTANTE: no exemplo acima, repare que os campos codcli E coddependente compõem a chave
primária desta tabela. As demais definições são as mesmas do exemplo 1. Portanto, sempre que uma tabela
possuir mais de um campo compondo a chave primária, a definição deve ser feita como no exemplo acima.
12.4 –ALTERAÇÃO DE ESTRUTURA DE TABELA DE UM BANCO DE DADOS (DDL)
Para alterarmos a estrutura de uma tabela criada usaremos o comando ALTER TABLE.
Sintaxe:
Para excluir um campo de uma tabela:
ALTER TABLE <nome_da_tabela_existente>
DROP COLUMN <nome_campo>
OU
Sintaxe:
Para adicionar um campo na tabela:
ALTER TABLE <nome_da_tabela_existente>
ADD <nome_campo> <tipo_dado>
OU
Sintaxe:
Para alterar o TIPO DE DADO de um campo da tabela
ALTER TABLE <nome_da_tabela_existente>
ALTER COLUMN <nome_campo> <tipo_dado>
OU
Sintaxe:
Para renomear um campo da tabela usa-se o MÉTODO SP_RENAME:
SP_RENAME ‘<nome_tabela>.<nome_antigo_campo>’ , <novo_nome_campo>
OU
Sintaxe:
Para inserir uma chave primária numa tabela criada anteriormente:
ALTER TABLE <nome_da_tabela_existente>
ADD CONSTRAINT <nome_chave> PRIMARY KEY (<nome_campo>)
OU
Sintaxe:
Para excluir uma chave primária numa tabela criada anteriormente:
ALTER TABLE <nome_da_tabela_existente>
DROP CONSTRAINT <nome_chave>
OU
Sintaxe:
Para inserir uma chave estrangeira numa tabela criada anteriormente:
ALTER TABLE <nome_da_tabela_filho_jacriada >
ADD FOREIGN KEY (<nome_campo_chaveestrangeira>)
REFERENCES <nome_tabela_pai> ( <chave_primaria_tabela_pai> )
ON DELETE CASCADE
ON UPDATE CASCADE
Professor: Alberson Wander
Página: 36
Banco de Dados
UNIVAP - CTI
Exemplo 1:
Alter table cliente
drop column telcli
Neste exemplo está sendo excluído o campo telcli da tabela cliente
Exemplo 2:
Alter table cliente
add telefonedocli varchar(25)
Neste exemplo está sendo adicionado na tabela cliente o campo chamado telefonedocli, tipo varchar
de 25,
Exemplo 3:
alter table cliente
alter column telcli varchar(20)
Neste exemplo está sendo alterado o tipo de dado do campo telcli para varchar(20)
Exemplo 4:
SP_RENAME 'CLIENTE.telcli' , telefonecli
Neste exemplo está sendo renomeado o campo telcli, da tabela cliente, para telefonecli. Neste caso
repare que não foi usada a instrução alter table, mas sim o MÉTODO SP_RENAME.
Exemplo 5:
alter table Vendas
add constraint chave PRIMARY KEY (nvenda, codcli)
Neste exemplo está sendo criado na tabela copiavendas a chave primaria composta chamada “chave”,
pois os campos nvenda e codcli compõem a chave primária. OBS: PARA O COMANDO ACIMA FUNCIONAR,
OS CAMPOS NVENDA e CODCLI DEVEM TER SIDO CRIADO ANTERIORMENTE COM A PROPRIEDADE
NOT NULL.
Exemplo 6:
alter table Vendas
drop constraint chave
Neste exemplo está sendo excluída a chave primária que foi criada no exemplo anterior. A chave
primária possui o nome “chave”.
Exemplo 7:
alter table vendas
add foreign key (codcli) references cliente(codcli)
on delete cascade on update cascade
Neste exemplo está sendo criada uma chave estrangeira para a tabela vendas e relacionando-a com a
tabela cliente. As cláusulas “on delete cascade” e “on update cascade” são opcionais.
12.5 – EXCLUSÃO DE TABELA DE UM BANCO DE DADOS (DDL)
Para exclusão de uma tabela de um banco de dados usamos o comando Drop table.
Sintaxe:
DROP TABLE <nome_tabela>
Exemplo:
Drop table cliente
No exemplo acima a tabela cliente está sendo excluída de um banco de dados em uso.
Professor: Alberson Wander
Página: 37
Banco de Dados
UNIVAP - CTI
12.6 – RELACIONANDO TABELAS DO BANCO DE DADOS (DDL)
No momento da criação de uma tabela FILHO, em uma relação, poderemos definir o relacionamento
dessa com a tabela pai. Para isso usamos a seguinte cláusula no comando create table:
Sintaxe:
CREATE TABLE <nome_da_tabela)
(
<campo1> <tipo_campo> [primary key] [ not null ] ,
......
<campon> <tipo_campo> [not null] ,
FOREIGN KEY <campo_chave_estrangeira_desta_tabela)
REFERENCES <nome_tabela_pai> ( <campo_chaveprimaria_tabela_pai>
ON DELETE CASCADE
ON UPDATE CASCADE
)
Para criar uma tabela FILHO numa relação, É NECESSÁRIO CRIAR ANTES A TABELA PAI, pois,
como é percebido na sintaxe, será necessário referenciar a tabela pai criada anteriormente.
Exemplo 1:
Create table cliente
(
Codcli int primary key not null,
Nomecli char(30) not null,
Endcli varchar(40),
Telcli varchar(25)
)
create table produtos
(
codproduto int primary key,
nomeproduto char(30)
)
create table produtoscliente
(
codcliente int,
codproduto int,
primary key (codcliente, codproduto),
foreign key (codcliente) references cliente(codcli)
on delete cascade
on update cascade,
foreign key (codproduto) references produtos(codproduto)
on delete cascade
on update cascade
)
NO EXEMPLO ACIMA, PERCEBA QUE:
1) AS TABELAS cliente E produtos FORAM CRIADAS ANTES DA TABELA produtoscliente.
2) Após a indicação da primeira chave estrangeira da tabela produtoscliente (codcliente), indicou-se
que exclusões e atualizações de registros na tabelas pai (cliente) reproduzirão o efeito cascata na
tabela produtoscliente. Em seguida, DEVE SER COLOCADA UMA VÍRGULA (,) para indicarmos a
segunda chave estrangeira (codproduto)
Para visualizarmos o relacionamento criado entre as tabelas criadas acima, deveremos abrir o banco
de dados e o diagrama. Você irá perceber que ao abrir o objeto “diagrams” no banco de dados, não existirá
nenhum diagrama de relacionamento disponível. PORÉM, faça uso dos passos indicados no item 7.5.4 desta
apostila, para visualizar as tabelas já relacionadas. Após adicionar as tabelas na área de relacionamento você
irá visualizar o relacionamento. Em seguida, basta gravá-lo fisicamente. Se tudo der certo você verá o seguinte
resultado:
Professor: Alberson Wander
Página: 38
Banco de Dados
UNIVAP - CTI
Figura 47
Exemplo 2: Neste exemplo, vamos incluir uma nova tabela no relacionamento feito no exemplo 1. A
tabela abaixo irá receber dados da tabela produtoscliente. Pelo fato da tabela produtoscliente possuir uma
chave primária COMPOSTA, a tabela filho, que será criada com o comando mostrado abaixo, deve referenciar
a chave composta da tabela pai. Vejamos:
create table datacomprasprodutos
(
datacompra datetime not null,
codclien int not null,
codprod int not null,
primary key (datacompra, codclien, codprod),
foreign key(codclien, codprod) references produtoscliente(codcliente, codproduto)
on delete cascade
on update cascade
)
OBSERVAÇÃO: LEMBRE-SE QUE VOCÊ DEVERÁ ADICIONAR ESTA ÚLTIMA TABELA NO DIAGRAMA JÁ
CRIADO. Para isso, primeiramente abra novamente a área “Database Diagrams” do banco de dados. Em
seguida, pressione o BOTÃO DIREITO DO MOUSE sobre qualquer local da área que exibe as tabelas já
relacionadas. No submenu que irá surgir, selecione a opção “Add Table...”, conforme figura a seguir:
Professor: Alberson Wander
Página: 39
Banco de Dados
UNIVAP - CTI
Figura 48
Surgirá a janela “Add Table”. Se a janela não exibir o nome da última tabela criada, pressione o botão
“Refresh”. A janela deverá ter a seguinte aparência:
Figura 49
Após clicar no botão “Add” o diagrama de relacionamento deverá ser atualizado, conforme a figura a
sseguir:
Professor: Alberson Wander
Página: 40
Banco de Dados
UNIVAP - CTI
Figura 50
12.7 – CRIAÇÃO DE CAMPO AUTONUMERAÇÃO (INCREMENTO AUTOMÁTICO) (DDL)
Para criação de um campo de incremento automático em uma tabela, usaremos a PROPRIEDADE
IDENTITY vinculado ao mesmo, no momento da criação da tabela.
Quando a PROPRIEDADE IDENTITY for utilizada em uma tabela, saiba que:
• Os registros para o campo definido com IDENTITY são gerados pelo sistema de forma seqüencial;
• É permitido somente um único campo IDENTITY por tabela;
• O campo não pode ser alterado por um comando UPDATE;
• Deve ser usado somente para campos do tipo: tinyint, smallint, int, numeric, decimal
Sintaxe:
IDENTITY [(valor_inicial, incremento)]
Onde:
Valor_inicial: indica o valor para o primeiro registro gravado;
Incremento: indica qual o valor será acrescido para cada registro gravado
Exemplo: No exemplo abaixo indicamos que no campo aluno_id iniciará com 1, quando o primeiro
registro for gravado. A partir daí, sempre que um novo registro for cadastrado, será somado automaticamente 1
no campo aluno_id, fazendo com isso, que nunca se repita os números de identificação de alunos na tabela.
CREATE TABLE aluno
(
aluno_id INT IDENTITY (1, 1),
nome
VARCHAR(20)
)
O exemplo acima, foi criado o campo aluno_id, na tabela aluno. Quando desejar gravar um registro na
tabela aluno, NÃO SERÁ NECESSÁRIA a indicação do dado para o campo aluno_id, pois, como descrito
anteriormente, o próprio sistema irá gerar o número do aluno automaticamente. Veja abaixo o exemplo de
como os registros devem ser cadastrados na tabela aluno:
INSERT aluno VALUES ('Mirtes')
// Será criado com aluno_id = 1
INSERT aluno VALUES ('Wagner')
// Será criado com aluno_id = 2
Professor: Alberson Wander
Página: 41
Banco de Dados
UNIVAP - CTI
13 – TRANSACT SQL (DML)
Para exemplificar os próximos comandos da Transact-SQL, usaremos as seguintes tabelas mostradas
no diagrama abaixo:
Figura 51
Para os exemplos dos comandos da DML, devemos conhecer os tipos de dados de cada campo. Por
esse motivo o diagrama de relacionamento das tabelas foram mostradas acima no modo STANDARD.
13.1 – COMANDO INSERT (DML)
O comando Insert é usado para gravar registro numa tabela do banco de dados ativo.
Sintaxe:
INSERT INTO <nome_tabela> ( campo1, ..... , campoN)
VALUES ( <dado_campo1> , ..... , <dado_campoN>)
Exemplo 1:
insert into cliente (codcli, nomecli, endcli, telefonecli)
values ( 1, 'joão da silva', 'rua JK 127', '(012) 3939-3021' )
Neste exemplo estamos cadastrando o cliente com o código 1, nome joão da silva, que reside à rua JK
127, com o telefone (012) 3939-3021, na tabela cliente.
OBSERVAÇÃO: A ordem dos dados indicados dentro da cláusula values, deve obedecer à mesma
ordem dos campos descritos após o nome da tabela. No exemplo acima descrevemos o campo codcli para
receber o número 1 e assim sucessivamente.
Exemplo 2:
insert into cliente values ( 1, 'joão da silva', 'rua JK 127', '(012) 3939-3021' )
Neste exemplo, repare que foram omitidos os nomes de campos que receberão os dados descritos na
cláusula values. Com isso, os dados serão gravados respectivamente obedecendo à ordem de criação dos
campos na tabela, ou seja, codcli, nomecli, endcli e telefonecli. Para usar esta sintaxe, o usuário deve conhecer
a ordem dos campos na tabela.
Exemplo 3: Vamos supor que tenha sido criada a tabela clientetemporario e que esta não tenha sido
relacionada a nenhuma tabela. Logo em seguida, usando o comando Insert, gravamos 4 registros de clientes.
Vejamos:
create table clientetemporario
(
codcliT int primary key,
nomecliT char (30)
)
Professor: Alberson Wander
Página: 42
Banco de Dados
UNIVAP - CTI
insert into clientetemporario values (2, 'alberson')
insert into clientetemporario values (3, 'wagner')
insert into clientetemporario values (4, 'julia')
insert into clientetemporario values (5, 'maria josé')
Agora vamos fazer outro teste, cadastrando todos os professores da tabela clientetemporario, na tabela
cliente. Vale lembrar que os campos das duas tabelas devem possuir tamanhos e tipos IDÊNTICOS. Veja o
exemplo abaixo:
insert into cliente(codcli, nomecli) select codcliT, NomecliT from clientetemporario
O exemplo acima, seleciona codcliT e NomecliT de todos os registros gravados na tabela
clientetemporario. Em seguida, os registros filtrados são gravados automaticamente na tabela cliente, nos
campos codcli e nomecli. Esta sintaxe poderia também ser escrita da seguinte forma:
insert into cliente(codcli, nomecli) select * from clientetemporario
IMPORTANTE: Nos dois últimos exemplos, torna-se necessária a indicação dos nomes de campos que
RECEBERÃO os registros filtrados da tabela clientetemporario, isto porque as tabelas não possuem a mesma
quantidade de campos.
Após a execução de um dos dois últimos exemplos, os registros serão gravados da seguinte forma, na
tabela cliente:
1
2
3
4
5
joão da silva
alberson
wagner
julia
maria josé
rua JK 127
NULL
NULL
NULL
NULL
(012) 3939-3021
NULL
NULL
NULL
NULL
Repare que os registros de clientes com código maior ou igual a 2, não possuem endereços
residenciais e telefones, isto porque na tabela clientetemporario estes dados não existem.
Exemplo 4:
Insert into datacomprasprodutos values (‘12/31/08’, 1, 1020)
Este exemplo grava um produto de um cliente no dia 31/12/2008 na tabela datacomprasprodutos.
Repare que a data, quando cadastrada, deve ser indicada no formato de padrão AMERICANO.
13.2 – OPERADORES RELACIONAIS, OPERADORES LÓGICOS e OPERADORES ARITIMÉTICOS DO
SQL SERVER
A seguir estão dispostos a relação dos operadores ARITIMÉTICOS, LÓGICOS E RELACIONAIS, mais
utilizados na linguagem Transact-SQL.
OPERADOR
ARITMÉTICO
*
+
/
%
||
Professor: Alberson Wander
UTILIDADE
Multiplicação
Soma
Divisão
Subtração
Módulo
Concatenação
OPERADOR
RELACIONAL
=
<>
>
<
>=
<=
!<
!>
!=
UTILIDADE
Igual
Diferente
Maior que
Menor que
Maior ou igual
Menor ou igual
Não menor que
Não maior que
Não igual
Página: 43
Banco de Dados
OPERADOR
LÓGICO
Not
And
Or
Between
Like
IN
UNIVAP - CTI
UTILIDADE
Inverte o valor booleano
E
Ou
Entre
Usado para encontrar padrões de strings
Usado para verificar se conteúdo do campo possui algum valor estipulado
13.3 – COMANDO UPDATE (DML)
Este comando é usado para alterar registros gravados numa tabela.
Sintaxe:
UPTADE <nome_tabela>
SET <nome_campo> = <novo_valo>
WHERE <critérios_para_alteração>
Exemplo 1:
Update cliente
Set endcli = ‘nenhum’
O exemplo acima irá alterar os endereços de TODOS os clientes para ‘nenhum’, isto porque não foram
definidos critérios (CONDIÇÕES) para indicar quais os registros seriam alterados.
Exemplo 2:
Update cliente
Set endcli = ‘Rua da morte’
Where codcli = 1
O exemplo acima irá alterar somente o endereço do cliente que possui o código igual a 1, para ‘Rua da
morte’.
Exemplo 3:
Update cliente
Set endcli = ‘Rua JK’ , telefonecli = ‘(012) – ‘
Where codcli = 1
O exemplo acima altera o endereço do cliente para ‘Rua JK’ e o telefone do cliente para ‘(012) – ‘, do
cliente cujo código é 1.
Os operadores Relacionais, Lógicos e Aritméticos que podem ser utilizados em instruções TransactSQL estão relacionados no item 13.2 desta apostila.
13.3 – COMANDO DELETE (DML)
Este comando é usado para excluir REGISTROS de uma tabela.
Sintaxe:
Delete from <nome_tabela>
Where <condição_critérios_exclusão>
Exemplo 1:
Delete from produtos
Where nomeproduto like ‘a%’
No exemplo acima serão excluídos da tabela produtos, todos os produtos cujo nome inicie com a letra
“a”.
Professor: Alberson Wander
Página: 43
Banco de Dados
UNIVAP - CTI
Perceba que não foi informado nenhum nome de campo após o comando delete, antes da cláusula
“from”, pois, o comando delete exclui o registro TODO e não somente o conteúdo de um campo da tabela.
Poderemos usar outros CURINGAS juntamente com o operador LIKE, vejam alguns exemplos:
Nomecli Like ‘a[ul]ber[sc]on%’
Nomecli like ‘%e%’
Nomecli like ‘%e’
Nomecli like ‘e%’
Nomecli like ‘[a – c]%’
Nomecli like ’joão’
Filtra registros que nomecli inciem com a letra “a”; o
segundo dígito seja “u” OU “l”; a partir do 3º dígito
contenha a seqüência “ber”; 6º dígito seja “s” OU “c”;
7º e o 8º dígito sejam “on” e que terminem com
quaisquer outros caracteres.
Filtra registros que nomecli contenha a letra “e”
disposta em qualquer local da string gravada
Filtra registros que nomecli termine com a letra “e”
Filtra registros que nomecli inicie com a letra “e”
Filtra registros que nomecli inicie com qualquer
caractere entre as letras “a” e “c”
Filtra registros que nomecli seja EXATAMENTE igual
a “joão”, pois o curinga % não foi utilizado na sintaxe.
É bom dizer que o operador like pode ser usado em qualquer instrução SQL QUE POSSUA A
CLÁUSULA WHERE e que o campo usado para filtro SEJA DO TIPO CHAR ou VARCHAR.
13.5 – COMANDO SELECT (DQL)
Este comando é um dos comandos mais utilizados no SQL, pois é usado para filtrar registros de uma
ou várias tabelas provendo com isso a criação de uma nova tabela temporária na memória do computador.
Sintaxe 1 :
Select <campo1> , .... , <campoN>
From <nome_tabela1> , .... , <nome_tabelaN>
Where <critérios_filtros_registros>
Exemplo 1:
Select * from cliente
Mostrará codigo, nome, endereço e telefone de todos os clientes cadastrados. O “*”substitui o nome de
todos os campos no exemplo acima. Pelo fato do exemplo não conter cláusula WHERE, todos os registros
serão mostrados para o usuário.
Exemplo 2:
Select nomecli, codcli from cliente where endcli = ‘Rua JK’ and telefonecli like ‘012%’
O exemplo acima exibirá nome(s) e código(s) do(s) cliente(s) que resida(am) na ‘Rua JK’ E QUE O
telefone inicie com ‘012’. A ordem de exibição dos campos será nome do cliente e código do cliente.
Professor: Alberson Wander
Página: 44
Banco de Dados
UNIVAP - CTI
14 – EXEMPLOS GERAIS DE INSTRUÇÕES TRANSACT-SQL
Para os novos exemplos de instruções SQL, iremos usar as seguintes tabelas relacionadas:
Figura 52
14.1 –PARÂMETRO DISTINCT
Este parâmetro é usado com o comando SELECT. O distinct retorna somente registros diferentes de
uma coluna da tabela.
Exemplo:
Select distinct datavenda from vendas
O exemplo acima mostrará somente data de vendas DIFERENTES, gravadas na tabela vendas.
Lembre-se que várias vendas são realizadas numa data. Se o distinct não fosse usado, as datas de vendas
apareceriam várias vezes, pois corresponderiam as vendas efetuadas para cada dia.
14.2 – PARÂMETRO ORDER BY
Este parâmetro é usado com o comando SELECT para ordenar os registros filtrados. Podemos ordenar
os registros por vários campos filtrados de maneira ascendente (crescente) ou descendente (decrescente).
Exemplo:
Select * from cliente where idadecli between 15 and 21 order by idadecli asc, nomecli desc
O exemplo acima irá mostrar todos os dados do cliente que possui idade entre 15 e 21 anos
(BETWEEN). Neste caso, os registros filtrados serão ordenados primeiramente pela idade, de forma crescente.
Caso existam clientes com a mesma idade, os registros serão ordenados pelo nome do cliente, de forma
decrescente.
14.3 – UNION (UNINDO CAMPOS DE VÁRIAS TABELAS NUMA CONSULTA – ASSOCIAÇÃO INTERNA)
Usando o comando SELECT podemos filtrar vários dados de diversas tabelas, usando somente uma
instrução SELECT para filtrar registros.
Professor: Alberson Wander
Página: 45
Banco de Dados
UNIVAP - CTI
Exemplo 1:
Select CLIENTE.nomecli, PRODUTOSVENDIDOS.*, VENDAS.*, PRODUTOS.nomeproduto
From cliente, produtosvendidos, vendas, produtos
Where VENDAS.nvenda = 1 AND VENDAS.codcliente = CLIENTE.codcli AND
PRODUTOSVENDIDOS.nvenda = VENDAS.nvenda AND
PRODUTOSVENDIDOS.codproduto = PRODUTOS.codproduto
Observe o exemplo acima:
1) Os campos exibidos serão: Nome do cliente, gravado na tabela cliente; Todos os campos da tabela
produtos vendidos; Todos os campos da tabela vendas; Nome do produto gravado na tabela
produtos;
2) Na cláusula FROM, perceba que os registros serão filtrados das tabelas cliente, produtosvendidos,
vendas e produtos;
3) O primeiro critério de filtro é que o número da venda seja 1.
4) Os trechos da sintaxe exposta em VERMELHO indicam os relacionamentos entre as tabelas. Isto
deve ser feito para que sejam filtrados os registros EQUIVALENTES nas tabelas relacionadas. Só
serão filtrados registros se, E SOMENTE SE:
a. existir a venda número 1 cadastrada;
b. existir o registro do cliente que comprou na venda 1;
c. existir pelo menos UM produto vendido (produtosvendidos) na venda 1;
d. existirem na tabela produtos, os mesmos código de produtos cadastrados em
produtosvendidos referente à venda 1.
Exemplo 2:
Select CLIENTE.nomecli, VENDAS.codcliente, VENDAS.nvenda
From cliente, vendas
Where VENDAS.nvenda = 1 AND VENDAS.codcliente = CLIENTE.codcli
Observe no exemplo acima:
1) Os campos exibidos serão: o nome do cliente, gravado na tabela CLIENTE; o código do cliente e o
número da venda, gravados na tabela VENDAS;
2) Na cláusula FROM são indicados os nomes das tabelas de onde os registros serão filtrados;
3) O trecho da sintaxe exposta em VERMELHO indica o relacionamento entre as tabelas VENDAS e
CLIENTE. Portanto, com isso, só será exibido algum registro se, e somente se, o código do cliente
cadastrado na venda existir também na tabela cliente. Isso foi feito para que o nome do cliente da
venda 1 pudesse ser exibido no resultado da consulta, pois, como se percebe o nome do cliente
não é cadastrado na tabela VENDAS.
14.4 – GERANDO EXPRESSÕES EM INSTRUÇÕES SQL
Assim como em qualquer linguagem, a transact-SQL nos permite efetuar cálculos durante a execução
de comandos.
Diante do último diagrama de relacionamentos mostrado, avaliemos os seguintes comandos:
a) Update produtosvendidos set subtotal = qtdevendida * valorvenda
Explicando o comando acima:
O comando acima irá atualizar todos os subtotais gravados na tabela produtosvendidos. O novo valor
para o campo subtotal, será resultante da multiplicação de valores armazenados no campo qtdevendida pelos
valores armazenados no campo valorvenda. Todos os registros serão atualizados, pois não foram
estabelecidos critérios na cláusula WHERE.
b) Select codproduto , valorvenda * 1.10 as [ Valor com 10%] from produtosvendidos
Explicando o exemplo acima:
O comando acima irá mostrar códigos de produtos e valores dos produtos acrescido de 10%. Todos os
registros gravados na tabela produtosvendidos serão mostrados. O rótulo “Valor com 10%”, escrito dentro dos
colchetes, na sintaxe acima, indica o rótulo da coluna que será gerada na tabela temporária para o usuário.
Vale dizer que o valorvenda não será mostrado, ele apenas foi usado para ser multiplicado por 1.10, gerando
Professor: Alberson Wander
Página: 46
Banco de Dados
UNIVAP - CTI
com isso o acréscimo de 10%. É importante dizer também que o valorvenda, GRAVADO FISICAMENTE, não
é alterado, pois, o comando utilizado acima foi SELECT.
14.5 – FUNÇÕES
Além de podemos escrever nossas expressões na própria linha de comando SQL, podemos também
usar diversas funções existentes na SQL.
As funções mais importantes, utilizadas pela transact-SQL são as seguintes:
Funções de Manipulação de Dados Numéricos:
Função
ABS
CEILING
Parametro
(numeric_expr)
(numeric_expr)
EXP
FLOOR
(float_expr)
(numeric_expr)
LOG
LOG10
PI
SQRT
STR
(float_expr)
(float_expr)
()
(float_expr)
(float, qtdeinteiro, qtdedecimais)
Resultado
Valor Absoluto
Maior inteiro mais próximo ou igual ao valor
especificado
Valor exponencial do valor especificado
Menor inteiro mais próximo ou igual ao valor
especificado
Logaritmo do numérico especificado
Logaritmo de base 10
Valor constante de 3.141592653589793
Raiz Quadrada
Converte um numero para string mostrando “n”
inteiro e “n” casas decimais
Exemplos:
a) Select codproduto, ceiling(subtotal) as [Sub Total Arredondado] from produtosvendidos
No exemplo acima todos os subtotais gravados serão arredondados para cima. Em seguida, serão
mostrados todos os códigos de produtos e o resultado arredondado dos subtotais, calculados para cada
produto. Pelo fato de não existirem critérios de filtros, todos os registros serão exibidos. O resultado
arredondado será armazenado na coluna “Sub Total Arredondado”.
b) Select codproduto, floor(subtotal) as [Sub Total Arredondado] from produtosvendidos
No exemplo acima todos os subtotais gravados serão arredondados para baixo. Em seguida, serão
mostrados todos os códigos de produtos e o resultado arredondado dos subtotais, calculados para cada
produto. Pelo fato de não existirem critérios de filtros, todos os registros serão exibidos. O resultado
arredondado será armazenado na coluna “Sub Total Arredondado”.
c) Select codproduto, abs(qtdevendida) as [Quantidade Vendida] from produtosvendidos
No exemplo acima, a quantidade vendida gravada, será convertida para valor absoluto, ou seja, não
poderá ser exibida uma quantidade negativa. Os valor ABSOLUTO (ABS()) serão armazenados na coluna
“Quantidade Vendida” na tabela temporária.
d) select str(valorvenda,10,2) as valor from produtosvendidos
No exemplo acima está sendo criada uma coluna na tabela temporária chamada “valor”. Todos os
valores de vendas, gravados na tabela produtosvendidos, serão mostrados com 10 números antes da
vírgula (se existirem) e com 2 casas decimais.
Professor: Alberson Wander
Página: 47
Banco de Dados
UNIVAP - CTI
Funções de Manipulação de Characteres ou Strings:
Função
+
LOWER
LTRIM
PATINDEX
Parâmetro
(expressão, expressão)
(char_expr)
(char_expr)
('%string%',expressão)
REVERSE
RIGHT
LEFT
RTRIM
SUBSTRING
UPPER
REPLACE
(char_expr)
(char_expr, integer_expr)
( cha_expr , int_expr )
(char_expr)
(expression, start, length)
(char_expr)
(char, sequ_proc, sequ_troc)
Resultado
Concatena 2 ou mais character, strings ou colunas
Converte para minúscula
Supressão dos espaços em branco à esquerda
Retorna a primeira posição da primeira ocorrência da string na
expressão definida, ou zero se a string não for encontrada
Retorna o inverso do char_expr.
Retorna n caracteres mais a direita da string
Retorna n caracters mais a esquerda da string
Supressão de espaços em brancos à direita
Retorna parte de uma string de caracteres.
Converte para maiúscula
Busca e Troca de caracteres numa seqüência definida
Exemplos:
a) Select ‘Produto : ‘ + nomeproduto from produtos
O exemplo acima irá mostrar nome do produto após a mensagem “Produto:”. Todos os produtos
cadastrados serão exibidos, pelo fato de não terem sido definidos critérios na cláusula WHERE. Veja
abaixo como os registros poderão ser exibidos:
Produto :café
Produto :leite
b) select lower(nomeproduto) as Produto from produtos
O exemplo acima irá mostrar uma coluna chamada “Produto” e nela serão exibidos todos os nomes de
produtos em letras minúsculas.
c) select upper(nomeproduto) as Produto from produtos
O exemplo acima irá mostrar uma coluna chamada “Produto” e nela serão exibidos todos os nomes de
produtos em letras maiúsculas.
d) select ltrim(endcli) as endereço from cliente
O exemplo acima irá retirar os espaços à esquerda dos endereços de clientes e os exibirá numa coluna
chamada “endereço”.
e) select rtrim(endcli) as endereço from cliente
O exemplo acima irá retirar todos os espaços à direita dos endereços de clientes gravados e os exibirá
numa coluna chamada “endereço”.
f)
select PATINDEX('%s%',endcli) as [Posição do S] from cliente
O exemplo acima irá procurar pela letra s em qualquer parte de cada endereço de cliente cadastrado
na coluna endcli. Caso a letra “s” seja encontrada, será retornado o número da posição da mesma no
endereço. Se não for encontrada a letra “s” no endereço de cada cliente, será retornado 0.
g) select reverse(nomecli) as [Nome invertido] from cliente where codcli = 1
O exemplo acima mostrará o nome do cliente 1 invertido. O resultado será mostrado numa coluna
chamada “Nome Invertido”.
h) select right(bairrocli,10) as [Bairro] from cliente where codcli = 1
O exemplo acima irá retornar os 10 caracteres últimos caracteres do bairro do cliente 1.
IMPORTANTE: se o campo bairrocli foi criado como char(n) e se o conteúdo nele gravado não usar as N
Professor: Alberson Wander
Página: 48
Banco de Dados
UNIVAP - CTI
posições definidas, poderão ser retornados somente espaços em branco ou parte do que se imaginava
buscar.
i)
select left((bairrocli),10) as [Bairro] from cliente where codcli = 1
O exemplo acima irá retornar os 10 primeiros caracteres do bairro do cliente 1.
j)
select substring(cidadecli, 5, 3) as [Bairro] from cliente
O exemplo acima irá retornar 3 caracteres, a partir da posição 5, do nome da cidade dos clientes,
gravados na coluna cidadecli.
k) SELECT REPLACE(nomecli,'A','Ca') from cliente
O exemplo acima irá trocar “A” por “Ca”, na coluna nomecli na tabela cliente.
Funções de Manipulação de Datas:
Função
DATEADD
Parâmetros
(datepart, valor, date)
DATEDIFF
(datepart, date1, date2)
DATENAME
(datepart, date)
GETDATE
DAY
YEAR
MONTH
()
(date)
(date)
(date)
DatePart
Year
Quarter
Month
day of year
Day
Week
Weekday
Hour
Minute
Second
Millisecond
Resultados
Soma o VALOR correspondente datepart na data
especificada
Calcula a diferença definida em dateparts entre 2
datas
Especifica datepart para a data, retornando como
um valor ASCII (Ex: Setembro)
Data e hora corrente do computador
Retorna o dia da data informada
Retorna o ano da data informada
Retorna o mês da data informada
Abreviação
Yy
Qq
Mm
Dy
Dd
Wk
Dw
Hh
Mi
Ss
Ms
Valor
1753 - 9999
1-4
1 - 12
1 - 366
1 - 31
0 - 51
1 - 7 (1 é Domingo)
0 - 23
0 - 59
0 - 59
0 - 999
EXEMPLOS:
a) select dateadd(yy,1,datavenda) from vendas
O exemplo acima está adicionando 1 ano na data da venda de todos os registros gravados em vendas.
É bom lembrar que fisicamente as datas da venda dos registros gravados continuarão as mesmas.
Somente na tabela temporária a data será exibida com o ano adicionado.
b) Select getdate()
O exemplo acima mostratá a data e hora do sistema.
c) select * from vendas where (datavenda) > '02/01/2008'
ATENÇAO: O EXEMPLO ACIMA EXIBIRÁ TODOS DADOS DE VENDAS, CUJA DATA DA
VENDA SEJA SUPERIOR A 01/FEVEREIRO/08. REPARE QUE A DATA, EXPRESSA NA
CLÁUSULA WHERE DEVE SER INDICADA NO FORMATO AMERICANO E ENTRE ASPAS
SIMPLES.
Professor: Alberson Wander
Página: 49
Banco de Dados
UNIVAP - CTI
d) select year(datavenda) as [ano], month(datavenda) as [mês], day(datavenda) as [dia] from vendas
O exemplo acima criar 3 colunas na tabela temporária, a saber: ano, mês e dia. Respectivamente,
nestas colunas, serão armazenados o ano, o mês e o dia das datas gravadas na coluna datavenda na
tabela vendas. Serão exibidos ano, mês e dia de todas as vendas gravadas, pois não existem critérios
definidos na cláusula WHERE.
Funções Agregadas:
AVG
COUNT
SUM
MIN
MAX
Retorna a média aritmética de uma coluna ou grupo
Retorna o total de elementos da coluna ou grupo
Retorna o somatório de uma coluna ou grupo
Retorna o menor valor de uma coluna ou grupo
Retorna o maior valor de uma coluna ou grupo
Exemplos:
a) select avg(subtotal) as [Média] from produtosvendidos
O exemplo acima irá mostrar, numa coluna chamada “Média”, a média de todos os valores gravados na
coluna subtotal na tabela produtosvendidos. Caso fosse definido algum critério de seleção na cláusula
WHERE, a média seria somente dos registros filtrados pela condição.
b) select sum(subtotal) as [Total Faturado] from produtosvendidos
O exemplo acima irá mostrar, numa coluna chamada “Total Faturado”, o somatório de todos os valores
gravados na coluna subtotal na tabela produtos vendidos. Caso fosse definido algum critério de seleção na
cláusula WHERE, o somatório seria somente dos registros filtrados pela condição.
c) select count(*) as [Quantidade Vendas] from vendas
O exemplo acima irá retornar na coluna “Quantidade Vendas” quantas vendas estão gravadas na
tabela vendas.
d) Select count (nvenda) as [Quantidade Vendas] from vendas
O exemplo acima irá retornar a quantidade de números de vendas gravadas na coluna nvenda na
tabela vendas. O resultado retornado será exibido na coluna “Quantidade Vendas!”
e) select min(valorvenda) as [Preço] from produtos
O exemplo acima retornará o preço do produto mais barato gravado no campo valorvenda, na tabela
produtos. O preço será exibido numa coluna chamada “Preço”. Pelo fato de não existirem critérios definidos
na cláusula WHERE, todos os valores de vendas gravados no campo valorvenda serão avaliados para que
seja filtrado o MENOR valor.
f)
select MAX(valorvenda) as [PREÇO] from produtos
O exemplo acima mostrará qual o maior valor de venda gravado no campo valorvenda, na tabela
produtos. O maior valor será exibido numa coluna chamada “PREÇO”. Pelo fato de não existirem critérios
definidos na cláusula WHERE, todos os valores de vendas gravados no campo valorvenda serão avaliados
para que seja filtrado o MAIOR valor.
g) SELECT sum(subtotal)* 1.10 as [Total da Venda]
FROM produtosvendidos
where nvenda = 1
O exemplo acima mostrará o total da venda 1 acrescentado de 10%. Repare que foi feita a soma de
todos os subtotais da venda 1 e em seguida, foram acrescidos os 10%. Neste caso a somatória (Sum()), foi
feita somente sobre os subtotais da venda 1, conforme definido no critério WHERE.
Professor: Alberson Wander
Página: 50
Banco de Dados
UNIVAP - CTI
h) Select count(distinct nvenda) as [Quantidade de vendas] from produtosvendidos
O exemplo acima irá contar quantos números de vendas DIFERENTES existem na tabela produtos
vendidos. O resultado da contagem será mostrado num campo da tabela temporária chamado “Quantidade
de Vendas”. Lembre-se que na tabela produtosvendidos, para cada produto vendido numa venda o número
dessa é gravado novamente, gerando-se desta forma redundâncias necessárias, pois com isso, estamos
relacionando os produtos de cada venda.
14.6 – USO DO OPERADOR IN
Este operador é útil na cláusula WHERE, pois, ele permite testar diversos valores definidos numa lista
para um campo da tabela.
Exemplo:
Select * from cliente where codcli in (1,2,9)
O exemplo acima irá mostrar todos os dados dos clientes cujos códigos sejam 1 OU 2 OU 9. Neste
caso o operador IN está substituindo o operador OR, que tornaria a sintaxe muito mais extensa.
14.7 – SUBCONSULTAS
Em algumas instruções SQL torna-se necessário realizarmos subconsultas.
usaremos os seguintes operadores:
OPERADOR
> ALL
< ALL
<> ALL
= ANY
> ANY
< ANY
<> ANY
IN
Exists
Para subconsultas,
UTILIDADE
Se o campo armazena registros maiores que todos os elementos da subconsulta
Se o campo armazena registros menores que todos os elementos da subconsulta
Se o campo armazena registros diferentes de todos os elementos da subconsulta (o
mesmo que NOT IN)
Se o campo armazena registros iguais a algum dos elementos da subconsulta (o
mesmo que IN)
Se o campo armazena registros maiores que algum dos elementos da subconsulta
Se o campo armazena registros menores que algum dos elementos da subconsulta
Se o campo armazena registros diferentes de algum dos elementos da subconsulta
(falso se igual a todos)
Se campo armazena registros com valores iguais a algum dos elementos da
subconsulta
Verifica se a subconsulta gerou registros
Exemplos:
a) select codproduto, qtdevendida from produtosvendidos
where nvenda = 1 AND exists (select nvenda from vendas)
No exemplo acima os códigos de produtos e quantidade de produtos, vendidos da venda 1, gravados
na tabela produtosvendidos, SÓ SERÃO MOSTRADOS se, e somente se, existir a venda 1 na tabela
vendas
b) select produtos.nomeproduto, produtosvendidos.codproduto, produtosvendidos.qtdevendida
from produtosvendidos, produtos
where nvenda = 1 AND exists (select nvenda from vendas)
AND produtosvendidos.codproduto = produtos.codproduto
Repare neste exemplo que estamos buscando o nome do produto na tabela produtos. Portanto, as 3
condições escritas na cláusula WHERE deverão acontecer, para que os registros sejam exibidos. O trecho
em VERMELHO é uma condição que informa a ligação entre as tabelas produtosvendidos e produtos, ou
seja, somente os nomes de produtos vendidos na venda 1 deverão ser mostrados juntamente no resultado
retornado pelo comando select.
Professor: Alberson Wander
Página: 51
Banco de Dados
UNIVAP - CTI
c) delete from vendas
where nvenda = 1 AND NOT EXISTS( select * from produtosvendidos where nvenda = 1 )
O exemplo acima só irá excluir o registro da venda 1, na tabela vendas SE NÃO EXISTIREM produtos
vendidos desta venda gravados na tabela produtosvendidos. O comando acima trata de uma regra de
integridade de dados vista no início desta apostila, ou seja, só podemos apagar registros de uma tabela pai
se, e somente se, na tabela filho (produtosvendidos) tais registros não forem utilizados.
d) update produtosvendidos
set produtosvendidos.valorvenda = produtos.valorvenda
from produtos, produtosvendidos
where produtosvendidos.codproduto = produtos.codproduto
and exists (select * from produtosvendidos)
O exemplo anterior mostra o uso da cláusula FROM no comando UPDATE. O uso da cláusula FROM
foi necessário neste exemplo porque os valores de vendas dos produtos, gravados na tabela
produtosvendidos, deveriam ser atualizados para os mesmos valores gravados no campo valorvenda, na
tabela produtos. Outra observação importante refere-se ao fato de que a alteração só será executada se
existirem registros na tabela produtosvendidos (CONDIÇÃO EXISTS, DA CLÁUSULA WHERE).
e) select nvenda, codcliente
from vendas
where codcliente IN (select codcli from cliente)
O exemplo mostrará números de venda e o código dos clientes, gravados na tabela vendas,
SOMENTE DOS clientes cadastrados na tabela cliente. Lembre-se que ao usar o operador IN, todos os
valores inseridos dentro dos parênteses, servirão de parâmetros dos filtros de registros na tabela vendas,
ou seja, neste caso todos os códigos de clientes filtrados na tabela cliente, servirão para filtrar os registros
de vendas efetuadas para esses clientes.
f)
select nvenda, codcliente
from vendas
where codcliente = ANY (select codcli from cliente)
Conforme definição do operador “=ANY”, mostrada na tabela no início deste item da apostila, sua
utilização têm a mesma função que o operador IN. Portanto, o comando acima realizará a mesma consulta
do exemplo dado na letra “e”.
14.8 – APELIDANDO TABELAS NAS CLÁUSULAS FROM
Este recurso é muito utilizado em instruções SQL. Determinando apelidos para tabelas diminuiremos
bastante o tamanho das instruções escritas. Vejam o exemplo abaixo:
select V.* , C.nomecli
from vendas V , cliente C
where V.codcliente = C.codcli
Neste exemplo estamos apelidando as tabelas Vendas com “V” e cliente com “C”. Dessa forma, toda
vez que desejarmos referenciar tais tabelas nas cláusulas das instruções, basta usarmos “V” ou “C”. Os
apelidos são determinados na cláusula FROM, portanto, será possível apelidarmos as tabelas somente em
instruções que fazem uso dessa cláusula.
•
•
IMPORTANTE:
OS APELIDOS SÃO GERALMENTE UTILIZADOS PARA SIMPLIFICAR A INSTRUÇÃO,
REDUZINDO O CÓDIGO ESCRITO.
GERALMENTE SÃO USADOS EM COMANDOS QUE FILTRAM DADOS DE DIVERSAS TABELAS,
COMO EXEMPLO DADO ACIMA QUE FILTRA DADOS DAS TABELAS “CLIENTE” E “VENDAS”.
Professor: Alberson Wander
Página: 52
Banco de Dados
UNIVAP - CTI
14.9 – CLÁUSULA GROUP BY... HAVING
A cláusula GROUP BY é utilizada geralmente para agrupar registros filtrados por uma instrução
SELECT. Geralmente utiliza-se o GROUP BY em conjunto com as funções AGREGADAS (MIN, MAX, SUM,
AVG e COUNT)
Já a cláusula HAVING deve ser usada em conjunto com a cláusula GROUP BY.
A cláusula HAVING tem a mesma função da cláusula WHERE , PORÉM as condições estabelecidas
na HAVING agirão sobre os registros AGRUPADOS, ou seja, depois do agrupamento dos registros filtrados.
FUNCIONAMENTO: No comando SELECT poderemos ter as cláusulas WHERE e HAVING (esta,
desde que o GROUP BY tenha sido escrito). Primeiramente os registros serão filtrados pelas condições
definidas na cláusula WHERE, em seguida, serão AGRUPADOS, e por fim, os critérios definidos na cláusula
HAVING agirão em tais registros, realizando outro filtro nos registros agrupados.
Exemplos:
a) select codcliente, count(*) as [Qtde vendas para cliente]
from vendas
group by codcliente
O exemplo irá contar e mostrar quantas vendas foram efetuadas para cada cliente. Primeiramente o
comando agrupa todos os códigos de clientes, em seguida, realiza-se a contagem de registros em cada
grupo, por fim, serão exibidos o código de cada cliente e a quantidade de vendas efetuadas para cada um
deles (Qtde vendas para cliente). LEMBRE-SE que na função COUNT não é necessário a indicação do
nome de um campo, pois a mesma contará REGISTROS.
b) select nvenda, sum(subtotal) as [total de cada venda]
from produtosvendidos
group by nvenda
Este exemplo agrupa os registros gravados na tabela produtosvendidos pelo nvenda. Em seguida, é
realizada a soma de todos os subtotais de cada venda. Por fim, são exibidos todos os nvendas agrupados
com seus devidos totais calculados.
c) select nvenda, sum(subtotal) as [total de cada venda com produto 1]
from produtosvendidos
where codproduto = 1
group by nvenda
Repare que no exemplo acima fazemos uso da cláusula WHERE. Com isso, será filtrados
primeiramente os registros de produtosvendidos que relacionam vendas do produto 1. Em seguida, os
registros filtrados serão agrupados pelo número da venda. Dessa forma, serão exibidos o somente o total
arrecadado com vendas do produto 1 em todas as vendas.
d) select cidadecli, avg(idadecli) as [Média Idade de Clientes]
from cliente
group by cidadecli
O exemplo acima mostra a média de idade de clientes de cada cidade cadastrada na tabela cliente.
Primeiro os registros são agrupados por cidade, em seguida, é calculado a média aritmética das idades dos
clientes ligados a cada grupo de cidade. Por fim, será exibido o nome da cidade com sua a devida média de
idade de clientes.
e) select cidadecli, avg(idadecli) as [Média Idade de Clientes]
from cliente
group by cidadecli
having avg(idadecli) >= 18
Neste exemplo, o nome da cidade e média de idade de clientes, serão exibidos somente se a média de
idade dos clientes for igual o superior a 18 anos. Percebam que neste exemplo a cláusula HAVING foi
Professor: Alberson Wander
Página: 53
Banco de Dados
UNIVAP - CTI
usada. Isso fará com que após o agrupamento e o cálculo da média de idade, seja feito um filtro dos
registros.
f)
select cidadecli, avg(idadecli) as [Média Idade de Clientes]
from cliente
where idadecli >=18
group by cidadecli
having avg(idadecli) between 20 and 30
Neste exemplo perceba que ANTES de agrupar os registros e calcular a média de idade dos clientes,
foi realizado um filtro de registros, de tal forma que a média de idade dos clientes de cada cidade fosse
calculada somente entre os clientes com no mínimo 18 anos. O nome da cidade e a média de idade de
clientes só será exibida se a média calculada estiver entre 20 e 30 anos, pois o critério definido na cláusula
HAVING fará o filtro após o agrupamento.
14.10 – USO DOS PREDICADOS TOP e PERCENT
O uso do TOP em um comando SELECT fará com que sejam mostrados certo número de registros do
início ou do final da tabela em questão, especificado pela cláusula ORDER BY.
Torna-se necessário informar que você poderá exibir também uma porcentagem de registros diante
dos registros que seriam filtrados. Neste caso deverá usar PERCENT junto ao predicado TOP.
IMPORTANTE: O PREDICADO TOP NÃO CLASSIFICA REGISTROS IGUAIS. PORTANTO,
PODERÁ OCORRER QUE SEJAM FILTRADOS REGISTROS A MAIS DO QUE FORAM SOLICITADOS.
Exemplos:
a) select top 1 nvenda from vendas order by totalvenda desc
O exemplo mostrará o número da venda cujo total é o MAIOR entre os gravados na tabela vendas.
Veja que as vendas foram ordenadas pelo totalvenda de forma decrescente. Com isso o predicado TOP
forçará a exibição da primeira venda que possui o maior valor. Ressalta-se que se que se vários totais de
vendas possuírem o maior valor, ou seja, possuírem o mesmo totalvenda, todas serão exibidas, apesar da
sintaxe solicitar somente a primeira (Top 1).
b) select top 1 nvenda from vendas order by totalvenda asc
Já o exemplo acima, mostrará o número da venda cujo total vendido é o MENOR entre os gravados na
tabela vendas. Veja que as vendas foram ordenadas pelo totalvenda de forma crescente. Com isso o
predicado TOP forçará a exibição da primeira venda que possui o menor valor. Ressalta-se que se vários
totais de vendas possuírem o menor valor, ou seja, possuírem o mesmo totalvenda, todas serão exibidas,
apesar da sintaxe solicitar somente a primeira (Top 1).
c) select top 50 percent nvenda from vendas order by totalvenda asc
Será mostrado somente o número da venda de 50% das piores vendas efetuadas, ou seja, com
menores totalvenda gravados na tabela vendas.
15 – STORED PROCEDURE
Uma Stored Procedure nada mais é do que uma rotina de programação escrita em Transact-SQL, que
pode ser executada manualmente por um usuário do banco de dados ou até mesmo ser chamada por um
programa qualquer.
Uma Stored Procedure pode ou não receber parâmetros de entrada e retornam resultados para o
programa ou usuário que a pôs em execução.
Uma Stored Procedure pode chamar outra Stored Procedure.
Professor: Alberson Wander
Página: 54
Banco de Dados
UNIVAP - CTI
IMPORTANTE: COM EXCEÇÃO DOS COMANDOS CREATE, usados para criar estruturas gerais
(banco, tabelas, stored procedures e triggers), praticamente qualquer comando SQL pode ser utilizado em
uma Stored Procedure.
Exemplos de ALGUNS comandos NÃO PERMITIDOS em Stored Procedure:
Create Database
Create Table
Create Procedure
Create Trigger
Create View
Etc.
Uma Stored Procedure pode ser criada através de linhas de comandos ou com o auxílio de assistentes
do SQL Server.
15.1 – CRIAÇÃO DE STORED PROCEDURE COM O OBJECT EXPLORER
Para criar uma Stored Procedure usando o assistente, siga os seguintes passos:
1) Abra a árvore de objetos do banco de dados onde você deseja criara Stored Procedure. POR
EXEMPLO:
2) Abra o objeto “Programmability” e em seguida clique com o botão direito do mouse sobre o objeto
“Stored Procedure”, conforme figura abaixo:
Figura 53
3) Clique na opção “New Stored Procedure...” mostrada na figura acima. Surgirá então a seguinte
área para criação da Stored Procedure:
Professor: Alberson Wander
Página: 55
Banco de Dados
UNIVAP - CTI
Figura 54
4) Na janela mostrada, você deverá substituir o trecho de programa destacado em azul, na figura
acima, pelos comandos da sua Stored Procedure..Por exemplo, vamos escrever neste local uma
programação para criar uma Stored Procedure que mostrará todos os dados de professores
cadastrados na tabela professores, no banco de dados em questão. Após a digitação do novo
trecho pressione o botão
, veja abaixo
Figura 55
Professor: Alberson Wander
Página: 56
Banco de Dados
UNIVAP - CTI
No exemplo, perceba que ao clicar no botão
, a mensagem “Command(s) completed
successfully”, indicando que a Stored Procedure “Mostraprofessores” foi criada no banco de dados UNIVAP.
IMPORTANTE: REPARE QUE FORAM APAGADOS VÁRIOS TRECHOS DO PROGRAMA INCIAL,
PROPOSTO PELO SQL QUANDO A JANELA DO ASSITENTE FOI ABERTA. AQUELE TRECHO
DEMONSTRA A SINTAXE DE VÁRIOS COMANDOS QUE NORMALMENTE PODEM SER UTILIZADOS.
VEREMOS ADIANTE ESTES TRECHOS.
15.2 – ALTERANDO UMA STORED PROCEDURE COM O OBJECT EXPLORER
Estando criada a Stored Procedure, você poderá alterá-la, incluindo e/ou adicionando novos
comandos. Para isso basta seguir os seguintes passos:
a. Abra na árvore de objetos do SQL a área de Strored Procedure;
b. Clique com o botão direito do mouse sobre a Stored Procedure que deseja alterar, como
exemplo, vamos alterar a stored procedure “mostraprofessores”, no banco de dados UNIVAP;
c. No submenu que aparecerá, clique na opção “Modify”;
Veja EXEMPLO abaixo:
Figura 56
Aparecerá na tela a Stored Procedure indicada para alteração, veja abaixo:
Figura 57
Professor: Alberson Wander
Página: 57
Banco de Dados
UNIVAP - CTI
15.3 – EXCLUINDO UMA STORED PROCEDURE USANDO O OBJECT EXPLORER
Para excluir uma stored procedure criada, basta:
a) Abrir a árvore do banco de dados onde se encontra a Stored Procedure;
b) Abrir o objeto “Stored Procedure”, do banco aberto;
c) Quando aparecerem os nomes das procedures, na janela OBJECT EXPLORER, clique com o
botão direito do mouse sobre o nome da stored que deseja excluir.
d) No menu pop-up que irá surgir, clique em “Delete”.
Veja a figura abaixo, ela ilustra os passos descritos acima:
Figura 58
e) Surgirá então à janela “Delete Object” contendo o nome da procedure que foi selecionada para
exclusão. Por fim, clique no botão “OK”.
Veja exemplo a seguir:
Figura 59
Professor: Alberson Wander
Página: 58
Banco de Dados
UNIVAP - CTI
15.4 – EXECUTANDO UMA STORED PROCEDURE
QUALQUER Stored Procedure criada pode ser executada a qualquer momento. Para isso basta:
a) Clique no botão
;
b) Indique na barra de ferramentas qual o nome do banco de dados que deve estar ativo, no nosso
caso:
;
c) Estando com a área de digitação de instruções SQL aberta, escreva o nome da Stored Procedure.
No nosso exemplo digitaremos “mostraprofessores” .
d) Por fim, basta pressionar o botão
.
No exemplo abaixo, será executada a Stored Procedure “mostraprofessores”, veja:
Figura 60
Veja que o resultado da execução dos comandos escritos na Stored Procedure “mostraprofessores”,
fez com que fosse exibido o painel de resultado, exibindo os registros filtrados.
15.5 – CRIAÇÃO DE STORED PROCEDURE (SEM PASSAGEM DE PARÂMETROS) USANDO O BOTÃO
“New Query” E PROGRAMAÇÃO
IMPORTANTE: Antes de criar uma stored procedure, tome cuidado de verificar qual o banco de dados
está aberto na barra de ferramentas:
Figura 61
Professor: Alberson Wander
Página: 59
Banco de Dados
UNIVAP - CTI
Para criarmos uma stored procedure com botão “New Query” (através de linha de comando), teremos
que usar a seguinte sintaxe:
Create Procedure <nome_procedure>
AS
<instruções SQL>
Veja abaixo um exemplo de criação de uma stored procedure através de linha de comandos:
Figura 62
A figura acima, ilustra os passos a seguir:
1) Selecione o nome do banco de dados onde você deseja criar a Stored Procedure, neste caso foi
“UNIVAP”;
2) Após a digitação da rotina que cria a stored procedure “alunoscadastrados” você deve clicar no
botão
, ou então pressionar F5. Se a mensagem “Command(s) completed successfully”
aparecer, significará que a rotina foi criada com sucesso, no banco de dados “UNIVAP”.
Digitando o trecho de programação anterior, você criará a stored. Para executá-la, basta seguir os passos
descritos no item 15.4 desta apostila, ou seja, escrever o nome da procedure e pressionar novamente o
botão
, conforme mostrado na figura abaixo:
Professor: Alberson Wander
Página: 60
Banco de Dados
UNIVAP - CTI
Figura 63
15.6 – ALTERAÇÃO DE STORED PROCEDURE (SEM PASSAGEM DE PARÂMETROS) USANDO O
BOTÃO “New Query” E PROGRAMAÇÃO
Para alterarmos a programação de uma stored procedure, poderemos usar o comando Alter
Procedure, conforme sintaxe abaixo:
ALTER PROCEDURE <nome_procedure_existente>
AS
<novas_instruções_SQL>
Vamos alterar a programação da stored procedure criada no item 15.5 desta apostila. Veja exemplo
abaixo:
Figura 64
Professor: Alberson Wander
Página: 61
Banco de Dados
UNIVAP - CTI
IMPORTANTE: após a digitação dos comandos SQL, que serão usados para substituir o código antigo,
torna-se necessário pressionar o botão
para que a alteração seja realizada efetivamente. Para
testar a alteração feita, use novamente os passos do item 15.4 desta apostila.
15.7 – CRIAÇÃO DE STORED PROCEDURE (COM PASSAGEM DE PARÂMETROS) USANDO O BOTÃO
“New Query” E PROGRAMAÇAO
Muitas vezes torna-se necessário a passagem de parâmetros para uma procedure. O parâmetro
recebido é usado na instrução SQL, escrita no interior da stored procedure.
Sintaxe:
Create procedure <nome_procedure>
@<variável_1> <tipo_variável> , @<variável_2> <tipo_variável>, ........... , @<variável_N>
<tipo_variável>
AS
<instrução_SQL_usando_variáveis declaradas>
IMPORTANTE: OS NOMES DE VARIÁVEIS, QUE RECEBERÃO OS PARÂMETROS, DEVEM SER
PRECEDIDOS PELO CARACTERE “@”, CONFOME MOSTRADO NA SINTAXE ACIMA.
Vejamos exemplo de criação de procedure com área de parâmetros:
Figura 65
15.8 – EXECUTANDO UMA STORED PROCEDURE (COM PASSAGEM DE PARÂMETROS)
Para testarmos uma stored procedure com passagem de parâmetros, temos que saber quais são os
parâmetros requisitados pela rotina.
Para exemplificar melhor, vamos executar a rotina criada no item 15.7 desta apostila. Veja como
devemos testá-la:
Professor: Alberson Wander
Página: 62
Banco de Dados
UNIVAP - CTI
Figura 66
IMPORTANTE RESSALTAR:
a) “MOSTRARALUNO” é o nome da stored procedure criada anteriormente;
b) O número 1, digitado na frente do nome da procedure, será passado para a variável @ID;
c) Neste caso, então, serão exibidos os registros de clientes cujo conteúdo do campo ALUNO_ID seja
igual a 1. A figura acima mostra o resultado da execução da stored procedure.
15.9 – ALTERANDO UMA STORED PROCEDURE (COM PASSAGEM DE PARÂMETROS) COM O BOTÃO
“New Query” E PROGRAMAÇÃO
Para alterar uma stored procedure que possua área de parâmetros, você deve usar a sintaxe indicada
no item 15.6 desta apostila.
Vale dizer que, se desejar, você pode reescrever a procedure com ou sem passagem de parâmetros.
Diante disso, será necessário alterar a instrução SQL escrita na rotina. Vejamos um exemplo:
Figura 67
Professor: Alberson Wander
Página: 63
Banco de Dados
UNIVAP - CTI
Neste exemplo, repare que a rotina foi alterada, de tal forma que receberá DOIS PARÂMETROS
quando for chamada. Portanto, quando for executar a rotina “MOSTRARALUNO”, devemos indicar o valor
inicial e final referente ao Aluno_ID, ou seja, estamos pesquisando por alunos cujo conteúdo do campo
ALUNO_ID esteja entre valores informados quando a stored procedure é chamada para execução.
15.10 – CRIANDO UMA STORED PROCEDURE COM PASSAGEM DE PARÂMETROS E COM VALORES
PADRÕES
Caso tentássemos executar uma stored procedure sem informar um parâmetro, requisitado pela
mesma, seria mostrada a seguinte mensagem de erro:
Figura 68
Veja que quando a procedure “mostraraluno” foi alterada, foram criadas DUAS variáveis que receberão
parâmetros informados pelo usuário, no momento da chamada mesma. Perceba que foi digitado somente o
nome da rotina e um dos valores obrigatórios como parâmetro e que não foi indicado o segundo valor de
parâmetro para consulta. Com isso foi exibida a mensagem de erro, informando o usuário que a rotina exige
outro parâmetro de entrada.
Para evitarmos esse tipo de erro, podemos definir valores padrões para as variáveis que receberão os
parâmetros de entrada.
Vamos reescrever a rotina de programação “mostraraluno” de tal forma que, se o @ID_INICIAL ou
@ID_FINAL forem omitidos, no momento da chamada da mesma, não deverá ocorrer erro, mas, deverá
sempre ser mostrado dados do aluno entre valores definidos como parâmetro, vejamos:
Figura 69
Professor: Alberson Wander
Página: 64
Banco de Dados
UNIVAP - CTI
Para resolvermos o problema, no momento da criação da variável devemos indicar um valor padrão, ou
seja, um valor que será atribuído à variável somente se a mesma não receber valores indicados pelo usuário,
no momento da chamada da rotina.
Vamos testar a nova rotina digitando somente o nome da mesma, sem passar nenhum parâmetro,
veja:
Figura 70
Pelo fato de não ter sido passado o parâmetro referente o @ID_FINAL, o SQL-Server atribuiu a
variável @ID_INICIAL o número 2, digitado pelo usuário. Como o usuário não informou um valor para o
@ID_FINAL, foi atribuído automaticamente para esta variável o valor 100. Portanto a instrução mostrou dados
de alunos cujo campo ALUNO_ID esteja entre 2 e 100(definido como padrão)
15.11 – EXCLUINDO UMA STORED PROCEDURE COM O BOTÃO “New Query” E PROGRAMAÇÃO
Para excluir uma strored procedure, basta usar a seguinte sintaxe:
Drop procedure <nome_procedure>
Veja o exemplo a seguir:
Figura 71
Professor: Alberson Wander
Página: 65
Banco de Dados
UNIVAP - CTI
No exemplo mostrado, após digitar a linha de comando “drop procedure mostraraluno”, foi pressionado
o botão
. Isso fez com que fosse excluída a procedure “mostraraluno”, do banco de dados UNIVAP.
16 –TRIGGER
São rotinas de programação que são disparadas automaticamente, diante das seguintes operações
realizadas com tabelas:
• INCLUSÃO DE REGISTRO
• ALTERAÇÃO(ÕES) DE REGISTRO(S)
• EXCLUSÃO(ÕES) DE REGISTRO(S)
Estas rotinas são pré-programadas pelo administrador do banco de dados.
Geralmente os sistemas desenvolvidos pelos programadores em linguagens tais como: Visual Basic,
Delphi e etc, são responsáveis pelo gerenciamento de todas as regras de negócio do referido sistema. Por
exemplo, o sistema que dá baixa automaticamente de produtos do estoque. A principal aplicação de uma
TRIGGER é cuidar destas regras de negócio, sem que seja necessário um programa qualquer para fazê-lo.
16.1 – CRIANDO / ALTERANDO / EXCLUINDO TRIGGER
Para criarmos TRIGGER, usamos a seguinte sintaxe:
Sintaxe:
CREATE TRIGGER [NOME_TRIGGER] ON [TABELA]
FOR <INSERT, DELETE, UPDATE>
AS
<instruções_sql>
Para alterarmos uma TRIGGER, usamos a seguinte sintaxe:
Sintaxe:
ALTER TRIGGER [NOME_TRIGGER] ON [TABELA]
FOR <INSERT, DELETE, UPDATE>
AS
<instruções_sql>
Nas sintaxes acima, existe a cláusula “FOR”. Esta cláusula refere-se ao momento quando a trigger
será disparada, por exemplo: Se na frente da cláusula “FOR” estiver definida a palavra INSERT, significa que a
trigger será disparada quando ocorrer um cadastro na tabela definida na cláusula “[TABELA]”. Quando
queremos que uma trigger seja disparada ao excluir um registro, devemos indicar na frente da cláusula “FOR”
a palavra DELETE. Para disparar uma trigger quando uma alteração for efetuada em um registro qualquer,
basta indicar a palavra UPDATE na frente da cláusula “FOR”. Se necessário for, podemos indicar que a trigger
será executada quando ocorrer cadastro, alteração ou exclusão escrevendo INSERT, DELETE, UPDATE na
frente da cláusula “FOR”.
Para excluirmos uma TRIGGER, usamos a seguinte sintaxe:
Sintaxe:
DROP TRIGGER [NOME_TRIGGER]
Vejamos alguns exemplos:
Professor: Alberson Wander
Página: 66
Banco de Dados
UNIVAP - CTI
Vamos imaginar as seguinte tabelas relacionadas de um banco de dados:
Figura 72
Vamos criar uma TRIGGER no SQL-SERVER,siga os passos abaixo:
1) Clique no botão
;
2) Digite a seguinte programação na área de edição de comandos:
Figura 73
3) Em seguida, pressione o botão
;
4) Se tudo estiver certo, será mostrada a seguinte mensagem:
Figura 74
A TRIGGER CRIADA ACIMA,COMO EXEMPLO, será disparada automaticamente sempre que um
registro novo for cadastrado na tabela venda.
16.2 – TESTANDO O FUNCIONAMENTO DE UMA TRIGGER
Para testar o funcionamento de uma trigger, basta realizar uma operação (cadastro, alteração ou
exclusão, dependendo do que foi definido na cláusula “FOR”) na tabela definida na cláusula “[TABELA]”
quando a TRIGGER foi criada.
Professor: Alberson Wander
Página: 67
Banco de Dados
UNIVAP - CTI
Como exemplo, vamos testar a trigger “VENDI”, criada como exemplo no item 16.1 desta apostila. Para
isso, basta cadastrar um registro. Usando o botão “New Query” vamos escrever uma linha de comando
INSERT, como o exemplo ilustrado a seguir:
Figura 75
Veja o que aconteceu:
Quando o comando INSERT foi executado, a TRIGGER “vendi” foi disparada automaticamente,
exibindo no painel de resultados todos os dados cadastrados na tabela “VENDA”.
16.3 – AS TABELAS TEMPORÁRIAS INSERTED E DELETED
As tabelas INSERTED e DELETED são criadas automaticamente na memória do computador somente
no momento em que as trigger´s estão sendo executadas.
Após a execução das trigger´s, as tabelas INSERTED e DELETED são excluídas da memória do
computador.
Vale dizer que a tabela INSERTED é criada quando a(S) operação(ões) INSERT ou UPDATE são
executadas. Já a tabela DELETED é criada quando a operação DELETE é executada.
Estas tabelas armazenam exatamente os registros que foram incluídos, alterados e/ou excluídos. É
importante lembrar que na tabela INSERTED, quando for criada pela operação INSERT, armazenará somente
um registro que acabou de ser cadastrado. Quando um grupo de registro for alterado, ESTES REGISTROS
serão armazenados na tabela INSERTED. Quando um grupo de registro for excluído, ESTES REGISTROS
serão armazenados temporariamente na tabela DELETED.
Para exemplificar melhor vamos fazer o seguinte:
a) Vamos criar uma trigger para puxar automaticamente o preço do produto vendido, e calcular
automaticamente o subtotal da venda, ou seja, o usuário não informará o valor do produto nem o
subtotal, quando efetuar o cadastro na tabela “VENDA”.
b) Em seguida, vamos cadastrar um registro na tabela VENDA, contendo somente as seguintes
informações:
Professor: Alberson Wander
Página: 68
Banco de Dados
•
•
•
•
UNIVAP - CTI
número da venda,
código do produto,
código do cliente
e quantidade vendida do produto
Vamos ilustrar tudo que foi descrito anteriormente. Primeiramente vamos criar a trigger, conforme
exemplo abaixo:
Figura 76
Agora vamos testar a trigger, cadastrando um registro na tabela venda:
Figura 77
Repare que no exemplo acima, duas tabelas foram exibidas no painel de resultados. Isso aconteceu,
porque existiam, neste caso, DUAS TRIGGER´S ligadas à tabela VENDA, a saber: “VENDI” (criada no exemplo
Professor: Alberson Wander
Página: 69
Banco de Dados
UNIVAP - CTI
do item 16.1 desta apostila) e “CALCULASUBOTAL”. Portanto, saiba que podemos ter várias trigger´s criadas
e ligadas a uma tabela.
Veja que a segunda tabela exibida atualizou o VALORPROD e calculou o SUBTOTAL, de acordo com
os comandos SQL descritos na trigger. Esta tabela foi mostrada/gerada pela trigger que acabamos de criar
(CALCULASUBTOTAL).
Como você deve ter percebido, nos comandos SQL criados na trigger, referenciamos a tabela
INSERTED, isto porque ela armazena uma cópia exata do registro que acabou de ser cadastrado.
COMO OS VALORES DOS CAMPOS VALOR PROD E SUBTOTAL FORAM GRAVADOS, VISTO
QUE NÃO OS INFORMAMOS NO MOMENTO DO CADASTRO?
R. A resposta é simples. O primeiro comando UPDATE, escrito na trigger, buscou o código do
produto que acabou de ser cadastrado, e que estava armazenado TEMPORARIAMENTE na tabela
INSERTED. Conhecido o código do produto, buscou-se o VALORPROD do mesmo, na tabela PRODUTO. Por
fim, na cláusula WHERE foram definidos os critérios para atualização do registro da tabela “VENDA”, ou seja,
somente será atualizado o valor do produto, do registro de VENDA, cujo CODP e NVENDA (ambos da tabela
VENDA) sejam iguais aos codp e nvenda armazenados na tabela INSERTED, sendo assim informou-se que
seria atualizado o registro da venda que acabou de ser cadastrado.
O segundo comando UPDATE, escrito na trigger, é responsável por atualizar o campo SUBOTOTAL do
registro que acabou de ser cadastrado na tabela VENDA. Para isso, multiplicou-se a quantidade informada pelo
usuário, no momento do cadastro (INSERT), pelo VALORPROD atualizado pelo primeiro comando UPDATE,
gerando assim, o SUBTOTAL. Como critério, foi informado na cláusula WHERE, que somente será atualizado
o SUBOTOTAL do registro de VENDA cujo codp e nvenda fossem iguais aos armazenados na tabela
INSERTED, ou seja, da venda que acabou de ser cadastrada.
O comando SELECT, escrito na trigger, é responsável pela exibição do registro contendo o VALORPROD e
SUBTOTAL. Perceba também, que na cláusula WHERE, foi estabelecido o mesmo critério dos comandos
UPDATE, descritos anteriormente, ou seja, filtrar somente o registro da venda que acabou de ser cadastrado.
Professor: Alberson Wander
Página: 70