Prévia do material em texto
Curso Microsoft
SQL Server 2000
Utilizando a Linguagem Transact SQL
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 2 de 86
ÍNDICE
1. INTRODUÇÃO...................................................................................................................................4
2. CONHECENDO SQL – SERVER ....................................................................................................5
2.1 FERRAMENTAS DO SQL – SERVER..............................................................................................5
ENTERPRISE MANAGER....................................................................................................................5
QUERY ANALYZER .............................................................................................................................6
SERVICE MANAGER ...........................................................................................................................6
PROFILER............................................................................................................................................7
CLIENT NETWORK UTILITY..............................................................................................................7
SQL – SERVER BOOKS ON-LINE.......................................................................................................8
3. OS DATABASES DO SQL-SERVER...............................................................................................9
4. CRIAÇÃO DE UM DATABASE.....................................................................................................13
5. INSERT (INSERE REGISTRO) .........................................................................................................19
6. UPDATE (ALTERA VALOR DO REGISTRO) ...............................................................................20
7. DELETE (EXCLUSÃO DE REGISTROS)........................................................................................21
8. APRESENTANDO O SISTEMA INFONEW....................................................................................22
8.1 OS OBJETIVOS GERAIS DO INFONEW SÃO:..........................................................................22
PARTE 1 – MANTER DADOS A RESPEITO DOS CLIENTES DA EMPRESA .....................................................22
PARTE 2 – ANÁLISE DO CREDITO DO CLIENTE.........................................................................................23
PARTE 3 – MANTER DADOS A RESPEITO DOS PRODUTOS A SERREM VENDIDOS ......................................23
PARTE 4 – MANTER E GERENCIAR OS PEDIDOS DE COMPRA FEITOS PELOS CLIENTES .............................24
PARTE 5 – MANTER DADOS A RESPEITO DOS FUNCIONÁRIOS..................................................................24
8.2 DIAGRAMA COM RELACIONAMENTOS DO SISTEMA INFONEW ......................................25
9. SELECT (CONSULTA).......................................................................................................................26
9.1 ORDER BY (ORDEM DE RETORNO) ..................................................................................................27
9.2 ALIAS (APELIDO) .............................................................................................................................28
9.3 WHERE (RESTRIÇÕES) .....................................................................................................................29
9.4 BETWEEN (CONDIÇÃO COM UMA FAIXA DE VALORES)....................................................................30
9.5 LIKE (COMPARAÇÃO COM UMA PARTE DE UMA LITERAL).................................................................31
9.6 DISTINCT (VALORES ÚNICOS)....................................................................................................32
10. FUNÇÕES DE AGRUPAMENTO....................................................................................................33
10.1 COUNT (CONTADOR DE REGISTROS) ..............................................................................................33
10.1.1 GROUP BY (Agrupamento de dados)......................................................................................34
10.2 SUM (SOMATÓRIA DE VALORES).....................................................................................................35
10.3 AVG (MÉDIA DE VALORES) ............................................................................................................36
10.4 MIN, MAX (MENOR E MAIOR VALOR) ............................................................................................37
11.HAVING (CONDIÇÃO DO AGRUPAMENTO) .............................................................................38
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 3 de 86
12. JOIN (RELACIONAMENTO DE TABELAS)................................................................................39
12.1 INTRODUÇÃO A JOINS: ....................................................................................................................39
12.2 TIPOS DE JOINS............................................................................................................................39
12.2.1 Usando Inner Joins:.................................................................................................................39
12.2.2 Usando Outer Joins: ...............................................................................................................39
12.2.3. Usando Cross Joins: ...............................................................................................................40
12.3 COMBINAÇÃO DE MAIS DE 2 TABELAS..............................................................................................41
13. IN (CONDIÇÃO COM VALORES FIXOS) ....................................................................................42
14. SUBQUERIES (PESQUISA DENTRO DE UM COMANDO).......................................................43
15. CRIANDO VISÕES (VIEW) .............................................................................................................45
16. CRIANDO STORED PROCEDURE (PROCEDIMENTOS).........................................................47
16.1 STORED PROCEDURE (PARÂMETROS DE SAÍDA): ............................................................................49
17. CRIANDO FUNCTION (UDF :FUNCÕES DEFINIDAS PELO O USUÁRIO ) .........................55
17.1 DEFININDO UMA FUNCTION UDF:....................................................................................................56
17.2 TIPOS DE FUNCTION: .......................................................................................................................57
17.2.1. Funções Scalar valued:...........................................................................................................57
17.2.2. Funções Table Valued: ...........................................................................................................58
17.2.3. Funções Multi-Statement Table Valued:.................................................................................59
18. CRIANDO TRIGGERS (GATILHOS ) ...........................................................................................61
19. EXERCICIOS .....................................................................................................................................67
EXERCÍCIO 2 - MANIPULANDO DADOS DO SYSAMPLE......................................................................67EXERCÍCIO 3 - MANIPULANDO DADOS DO SYSAMPLE......................................................................68
EXERCÍCIO 4 - MANIPULANDO DADOS DO SYSAMPLE......................................................................69
EXERCÍCIO 5 - MANIPULANDO DADOS DO SYSAMPLE......................................................................70
EXERCÍCIO 6- MANIPULANDO DADOS DO SYSAMPLE......................................................................71
EXERCÍCIO 7– VIEWS ...........................................................................................................................72
EXERCÍCIO 8 – FUNCTIONS ..............................................................................................................73
EXERCÍCIO 9 - STORED PROCEDURE .............................................................................................74
EXERCÍCIO 11 – TRIGGERS ...............................................................................................................75
MANUAL TÉCNICO...............................................................................................................................76
INSTALAÇÃO DO MICROSOFT SQL SERVER 2000 ......................................................................76
CONSIDERAÇÕES PRELIMINARES..................................................................................................76
REQUISITOS .........................................................................................................................................76
INSTALAÇÃO DO SQL SERVER 2000 ...............................................................................................76
CONSIDERAÇÕES FINAIS..................................................................................................................85
REFERÊNCIAS BIBLIOGRÁFICAS:...................................................................................................86
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 4 de 86
1. INTRODUÇÃO
Veremos nesta apostila de forma simplificada os comandos de
manipulação e consultas de dados, bem como suas clausulas, operadores e
funções. Para isso estaremos utilizando o gerenciador de banco de dados
Microsoft SQL Server 2000.
O comando Transact-SQL é uma linguagem estruturada para consultas,
utilizada no banco de dados da Microsoft, o SQL Server 2000.
Veja em seguida uma lista dos comandos Transact – SQL.
DCL – Data Control Language – Linguagem de Controle de Dados.
GRANT Concede permissões.
DENY Nega permissões.
REVOKE Revoga a concessão ou a negação de permissão.
DDL- Data Definition Language- Linguagem de Definição de dados.
CREATE Cria objeto no sistema.
ALTER Altera a estrutura dos objetos.
DROP Elimina objetos do sistema.
DML- Data Manipulation Language – Linguagem de Manipulação de Dados.
SELECT Lê dados de tabelas e views.
INSERT Insere dados em tabelas.
UPDATE Altera dados da tabela.
DELETE Exclui dados das tabelas.
BACKUP Realiza backup de dados.
RESTORE Restaura dados de um backup
BULK INSERT Realiza a inclusão de grande quantidade de dados em uma
tabela.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 5 de 86
2. CONHECENDO SQL – SERVER
2.1 FERRAMENTAS DO SQL – SERVER
O SQL-Server possui diversas ferramentas para a criação e manutenção
de banco de dados, para auxiliar o desenvolvimento de aplicações.
Essas ferramentas podem ser executadas a partir de um servidor NT ou
superior, de uma estação Windows NT Workstation ou superior ou ate mesmo
Windows 95 e / ou 98.
ENTERPRISE MANAGER
Através do SQL Enterprise Manager, podemos manipular os objetos
criados em um banco de dados (tabelas, usuários, stored procedures, etc).
Utilizamos esta ferramenta para ter acesso direto a estes objetos,
podendo criar novos e alterar os já existentes.
Os direitos do que cada usuários podem fazer com os objetos de um
Banco de dados são atribuídos pelo administrador do banco de dados. É muito
importante manter este controle para evitar que uma operação indevida
danifique um banco de dados.
Ele gerencia vários servidores, permitindo executar qualquer tarefa
relacionada ao SQL – Server.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 6 de 86
QUERY ANALYZER
Permite administrar diretamente o SQL – Server usando comandos
Transact – SQL. Os comandos SQL podem ser executados interativamente, ou
podem ser executados de procedimentos armazenados (stored procedures) ou
scripts.
SERVICE MANAGER
Permite iniciar, pausar, desligar os serviços do SQL – Server.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 7 de 86
PROFILER
Permite monitorar toda a atividade do servidor e registrar essa atividade em
arquivos de log, incluindo comandos SQL – Server.
CLIENT NETWORK UTILITY
Configura o software de acesso cliente numa estação.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 8 de 86
SQL – SERVER BOOKS ON-LINE
Toda a documentação do SQL – Server, para consultar on-line. Permite
fazer pesquisas de texto na documentação.
Para abrir o Books On-Line basta pressionar a tecla F1 quando o SQL
Server 2000 estiver operando.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 9 de 86
3. OS DATABASES DO SQL-SERVER
Uma vez instalado o SQL Server são criadas automaticamente quatro
databases:
a) master
b) model
c) tempdb
d) msdb
Depois, o poderemos criar e instalar nossos próprios bancos de dados
livremente, os quais serão os bancos de dados de usuário.
Embora ambos os tipos de bancos de dados (sistema e usuário)
armazenem dados, o SQL Server utiliza os bancos de sistema para operar e
gerenciar o sistema. O catálogo de sistema, por exemplo, consiste unicamente
de tabelas armazenadas no banco de dados master.
A figura a seguir ilustra os bancos de dados no SQL Server.
System
tables
master
System
tables
model
System
tables
tempdb
System
tables
msdb
System
tables
SAU02
System
tables
SAU05
Bancos de
dados de
usuário
Bancos de
dados do
sistema
Vejamos a função de cada um dos bancos de sistema.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 10 de 86
MASTERControla os bancos de dados de usuários e a operação do SQL Server,
por isso os dados armazenados em suas tabelas são críticos e deve-se sempre
manter backup atualizado. Ocupa inicialmente cerca de 17 Mbytes, mantendo:
a) contas de login;
b) processos em andamento;
c) mensagens de erro do sistema;
d) databases armazenados no servidor;
e) espaço alocado a cada database;
f) locks ativos;
g) databases disponíveis e dispositivos de dump;
h) procedimentos de sistema, que são primariamente utilizados para
administração.
O banco de dados master contém 13 tabelas de uso compartilhado
com o sistema, conhecidas como Catálogo do Sistema ou Dicionário de Dados,
que são:
1. syscharsets - códigos de página que estabelecem quais
caracteres estão disponíveis e sua ordem de classificação;
2. sysconfigures - variáveis de ambiente configuráveis;
3. syscurconfigs - variáveis de ambiente configuráveis;
4. sysdatabases - bancos existentes no servidor;
5. sysdevices - referência física aos dispositivos e bancos do
servidor;
6. syslanguages - entrada para as línguas conhecidas pelo servidor;
7. syslocks - quais são os locks ativos;
8. syslogins - contas de usuários;
9. sysmessages - mensagens de erro do sistema;
10. sysprocesses - processos em andamento
11. sysremotelogins - contas de acesso remoto, para conexão entre
dois servidores;
12. sysservers - servidores remotos;
13. sysusages - espaço em disco disponibilizado para cada banco de
dados (relaciona-se com sysdatabases e sysdevices).
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 11 de 86
MODEL
Fornece um protótipo (template) para um novo banco de dados.
Contém as tabelas de sistema que serão inseridas em cada banco de dados de
usuário. As seguintes implementações podem ser realizadas neste database:
a) tipos definidos pelo usuário (user datatypes), regras (rules),
padrões (defaults), stored procedures;
b) usuários que terão acesso a todos os bancos adicionados ao
sistema (administradores);
c) privilégios padrão, notadamente aos usuários guest (guest
accounts);
O tamanho padrão deste banco é de 1 Mbyte, e sua estrutura básica
pode ser vista na figura a seguir; as 18 tabelas mostradas serão sempre
criadas em novos bancos de dados.
Este conjunto de 18 tabelas é conhecido como Catálogo do Banco de
Dados, e suas funções são as seguintes (note que todas possuem o prefixo
sys):
1. sysalternates - possui uma linha para cada usuário mapeado para
um banco de dados de usuário;
2. syscolumns - possui uma linha para cada coluna em uma tabela
ou view, e para cada parâmetro em uma stored procedure;
3. syscomments - possui uma ou mais linhas para cada view, regra
(rule), padrão (default), trigger e stored procedure que contenha
uma declaração de definição;
4. sysdepends - uma linha para cada procedure, view, ou tabela que
seja referenciada por uma procedure, view ou trigger;
5. sysindexes - uma linha para cada clustered index, nonclustered
index, e tabela sem índices, mais uma linha extra para cada
tabela com informações de textos ou imagens;
6. syskeys - uma linha para cada chave estrangeira (foreign),
primária (primary) ou comum (common);
7. syslogs - armazena o transaction log;
8. sysobjects - uma linha para cada tabela (table), visão (view),
stored procedure, regra (rule), trigger, padrão (default), log e
objeto temporário (somente tempdb);
sysalternates syskeys sysindexes sysdepends syscomments syscolumns
syslogs systypes syssegments sysprotects sysprocedures sysobjects
sysusers syssubscriptions syspublications sysarticles sysconstraints sysreferences
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 12 de 86
9. sysprocedures - uma linha para cada visão (view), stored
procedure, regra (rule), trigger, padrão (default);
10. sysprotects - mantém as informações de permissões de usuário;
11. syssegments - uma coluna para cada segmento;
12. systypes - uma linha para cada datatype definido pelo usuário ou
fornecido pelo sistema;
13. sysusers - uma linha para cada usuário permitido no database;
14. sysreferences - uma linha para cada constraint de integridade
referencial criada (PK-FK, Chave primária, chave estrangeira);
15. sysconstraints - informações sobre cada constraint criada;
As últimas três tabelas são usadas para manter informações
sobre replicação de dados.
16. sysarticles - contém a article information para cada artigo criado
para replicação;
17. syspublications - contém uma linha para cada publicação criada;
18. syssubscriptions - contém uma linha para cada subscrição de um
subscription server.
TEMPDB
Providencia um espaço de armazenamento para tabelas e outras
ações temporárias ou intermediárias, tais como resultados que envolvam a
cláusula GROUP BY, ORDER BY, DISTINCT e cursores (CURSORS). Possui
as seguintes características:
a) criado automaticamente no DEVICE MASTER (atenção, DEVICE
e DATABASE são coisas diferentes);
b) seu conteúdo é apagado quando o usuário fecha a conexão,
exceto para tabelas temporárias globais;
c) quando o banco é parado (stoped) seu conteúdo é apagado
completamente;
d) seu tamanho padrão é de 2 Mbytes.
e) pode ser colocado em memória RAM.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 13 de 86
MSDB
Providencia suporte ao serviço SQL Executive Service (o qual fornece
serviços de schedulle de tarefas, replicação, gerenciamento de alertas). Possui
as seguintes tabelas de sistema:
a) sysalerts - armazena informações sobre todos os alertas definidos
por usuários;
b) sysoperators - informações sobre os operadores;
c) sysnotifications - relaciona quais operadores devem receber quais
alertas;
d) systasks - mantém informações sobre todas as tarefas definidas
por usuários;
e) syshistory - informações a respeito de quando um alerta e uma
tarefa foram executados, se com sucesso ou falha, identificação
do operador, data e hora da execução;
f) sysservermessages - mensagens sobre as operações
relacionadas ao servidor.
4. CRIAÇÃO DE UM DATABASE
Um database é uma estrutura lógica dentro da qual são criados os
sistemas de banco de dados.
Os arquivos de dados ficam organizados em grupos de arquivos e o
primeiro grupo criado automaticamente no momento da criação do database
chama-se PRIMARY.
Criando um Banco de Dados com a utilização do SQL Server
Enterprise Manager
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 14 de 86
Nome: É o nome lógico do arquivo.
DataFile: E o arquivo físico do Banco de Dados.
Transaction Log: E o arquivo lógico do Banco de Dados.
Filename: É o nome físico do arquivo. Deve ser especificado o seu
diretório de criação.
Initial size: É o tamanho inicial do arquivo.
Filegrowth: É o valor por meio do qual o arquivo aumenta de tamanho
automaticamente.
Maximum File Size: É o tamanho máximo de o Arquivo Físico e/ ou
Lógico pode asumir
SQL Server 2000.
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 15 de 86
Criação de um Banco de Dados com a Utilização do SQL
SERVER Query Analyzer
Exemplo:
� Criação de um Banco de Dados Chamado Bookselva
Create database Bookselva
On primary
(
name='BookSelvaBD',
filename='c:\bookselva.mdf',
size= 2 MB,
maxsize= 10 MB,
filegrowth= 1 MB
)
log on
(
name='bookselvalog',
filename='c:\bookselva.ldf',
size= 1 MB,
maxsize= 4 MB,
filegrowth= 1 MB )
CARACTERÍSTICA DE UM DATABASE
Nome: É o nome lógico do arquivo.
Filename: É o nome físico do arquivo. Deve ser especificado o seu
diretório de criação.
Size: É o tamanho limite que o arquivo pode alcançar.
Filegrowth: É o valor por meio do qual o arquivo aumenta de tamanho
automaticamente.
Criação de Tabelas no Banco de Dados criado anteriormente com
a utilização do SQL SERVER Query Analyzer
Confira abaixo alguns dos tipos de dados:
• Char(n): quando o campo for do tipo alfanumérico e com tamanho fixo,
o n quer dizer limitação, o valor máximo de caracteres;
• Varchar(n) : quando o campo for do tipo alfanumérico e com tamanho
não é fixo, o n quer dizer limitação, o valor máximo de caracteres;
• Int: quando o campo for numero com valor inteiro.
• Decimal (p,s): quando o campo for números com valores decimais, o
valor de p é o numero de algarismos e o s é valor dos algarismos
decimais (algarismos após a virgula).
• Money, smallmoney :quando o campo for valores monetários.
Money (8 bits) é quando a quantidade de dinheiro é muito (ex.: loteria), e
smallmoney (4 bits) é quando a quantidade de dinheiro é pequena.
Datetime e SmallDatetime:quando o campo for do tipo data ou hora.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 16 de 86
• Bit :determina se o valor é verdadeiro, falso ou nulo.
Nulabilidade
Nulabilidade: Significa se o campo declarado pode ser ou não nulo.
Para fazer tal declaração é necessário definir o campo com a clausula:
o Not null (não permite valor nulo,preenchimento obrigatório do
campo)
o Null (permite valor nulo, preenchimento não obrigatório do
campo)
Constraints: Integridade
Primary Key(Chave Primária):
Campo principal da tabela onde não permite dados repetidos no campo.
Tornando assim, identificar unicamente cada registro da tabela.
Exemplo:
Constraint PK_Cliente Primary Key (Cód_Cli)
Foreign Key(Chave Estrangeira):
É um campo cujos os dados aparecem necessariamente na chave primária de
uma outra tabela, mecanismo principal para efetuarmos relacionamento entre
tabelas.
Exemplo:
Constraint FK_NotaFiscal Foreign Key(Cód_Cli)
References Cliente(Cod_Cli)
Use bookselva
Create table Cliente
(
Cod_Cli int not null,
Nome_Cli varchar(40) not null,
End_Cli varchar(30) not null,
Bai_Cli varchar(20) not null,
Cid_Cli varchar(20) not null,
Uf_Cli char(3) not null,
Tel_Cli varchar(15) null,
Constraint PK_Cliente
Primary Key(Cod_Cli)
)
Create Table NotaFiscal
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 17 de 86
(
Num_Nota int not null,
Cod_Cli int not null,
Serie_Nota varchar(10) not null,
Emissao_Nota smalldatetime null,
Vtot_Nota SmallMoney not null,
Constraint PK_NotaFiscal
Primary Key(Num_Nota),
Constraint FK_Cliente
Foreign Key(Cod_Cli)
References cliente(Cod_Cli)
)
Criando um Diagrama
O Enterprise Manager permite que você crie um diagrama das tabelas para
verificar os relacionamentos das tabelas se houver relacionamento, Mas o
diagrama só poderá ser feito depois de ter criado as tabelas e seus
relacionamentos.
• Para criar um Diagrama com o Enterprise Manager aberto, expanda seu
database no exemplo o BookSelva, clique com o botão direito sobre o
ícone” Diagram “e escolha a opção “New Database Diagram” como
mostra a figura abaixo:
Em seguida aparecerá a primeira tela do Wizard para que você crie seu
diagrama:
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 18 de 86
Clique no botão “Avançar”
Selecione as tabelas Cliente e Nota Fiscal e clique no botão “Add>” e seguida
clique no botão “Avançar”
E para finalizar clique no botão “Concluir”
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 19 de 86
5. INSERT (Insere registro)
Definição: O comando INSERT insere um novo registro em uma tabela
Sintaxe:
INSERT INTO <tabela> [<campos>] VALUES <valores>
Exemplos:
Adotando-se que a ordem dos campos da tabela cliente seja
(Cod_Cli, Nome_Cli, End_Cli, Bai_Cli, Cid_Cli, Uf_Cli, Tel_Cli)
temos:
/*insere os valores (1,‘Nilson Borges’, Av.Paulista,929’,
’Cerqueira César’, ’São Paulo’, ’SP’, ’3285-0202’) na tabela
CLIENTES */
INSERT INTO CLIENTE VALUES (1,’Nilson Borges’,
‘Av.Paulista,929’, ’Cerqueira César’, ’São Paulo’,
’SP’, ’3285-0202’)
ou
INSERT INTO CLIENTE (Cod_Cli, Nome_Cli, End_Cli,
Bai_Cli, Cid_Cli, Uf_Cli, Tel_Cli)VALUES
(1,’Nilson Borges’, ‘Av.Paulista,929’, ’Cerqueira
César’, ’São Paulo’, ’SP’, ’3285-0202’)
/*insere os valores (1,1,‘WEP2525’,’2004/08/12’,250.89)
na tabela NOTAFISCAL*/
INSERT INTO NOTAFISCAL VALUES (1, 1, ‘WEP2525’,
’2004/08/12’,250.89)
ou
INSERT INTO NOTAFISCAL (Num_Nota, Cod_Cli,
Serie_Nota, Emissao_Nota, Vtot_Nota) VALUES (1, 1,
‘WEP2525’, ’2004/08/12’,250.89)
Exercicio
1) Insira 4 registros na tabela Cliente e 5 registros na tabela NotaFiscal
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 20 de 86
6. UPDATE (Altera valor do registro)
Definição: O comando UPDATE altera campos de um ou vários registros
de uma tabela.
Sintaxe: UPDATE <tabela> SET <campo> = <expressão> [WHERE
<condição>];
Exemplos:
/* atualiza o campo NOME_CLI para ‘Juliana Costa’ do registro da
tabela CLIENTE para o Cód_Cli igual a 1 */
UPDATE CLIENTE SET Nome_Cli = 'Juliana Costa'
WHERE Cod_Cli = 1
/* atualiza o campo valor total para 125.75 do registros da tabela
NOTAFISCAL onde o campo Emissão_nota for maior que
10/08/2004 */
UPDATE NOTAFISCAL SET Vtot_Nota = 125.75
WHERE Emissao_nota > ‘2004/08/10’
/* atualiza o campo valor total da tabela NOTAFISCAL com um
acréscimo de 12% em todos os campos onde a emissão for
menor do que 20/08/2004
UPDATE NOTAFISCAL SET Vtot_Nota = Vtot_Nota*1.12
WHERE Emissao_nota < ‘2004/08/20’
Operadores de Comparação
Operador Descrição
= Igual a
<> Diferente
> Maior que
>= Maior ou igual a
< Menor que
<= Menor ou igual a
Exercício
1) Atualize o campo Cid_Cli da tabela Cliente para ‘Santo André’ para os registros dos
clientes do campo UF_Cli igual a SP.
2) Atualize o campo Cod_Cli da tabela NotaFiscal para 3 para os registros das Notas
Fiscais com valor total abaixo de 130.00.
3) Atualiza o campo VTot_Nota com um desconto de 23% para o registros das Notas
Fiscais com valortotal diferente de 581.20
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 21 de 86
7. DELETE (Exclusão de registros)
Definição: O comando DELETE exclui um ou mais registros de uma tabela
Sintaxe: DELETE [FROM] <tabela> [WHERE <condição>];
Exemplo:
/* exclui todos os registros da tabela NotaFiscal onde o campo
Cod_Cli é igual a 1 */
DELETE FROM NOTAFISCAL WHERE Cod_Cli = 1
/* exclui todos os registros da tabela CLIENTE onde o campo
Nome_Cli é igual a ‘João Pedro’*/
DELETE FROM CLIENTE WHERE Nome_Cli = ‘João Pedro’
Exercícios
1) Excluir todas as NotasFiscais onde a Data de Emissão é superior a 15/08/2004
2) Excluir todas as NotasFiscais onde o valor total seja menor ou igual a 4520.21
3) Excluir todos os clientes onde moram no Estado de Minas Gerais.
4) Excluir todos os clientes onde moram na Cidade de São Roque.
5) Excluir todas as NotasFiscais que menos a do Cliente de Código igual a 2.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 22 de 86
8. APRESENTANDO O SISTEMA INFONEW
O objetivo deste sistema fictício é criar um ambiente OLTP para que vocês
coloquem em prática os tópicos que serão vistos nos próximos capítulos desta
apostila. O Sistema INFONEW deve gerenciar as operações comerciais de
uma suposta empresa.
8.1 Os objetivos gerais do INFONEW são:
� Parte 1: Manter dados a respeito dos Clientes:
� Parte 2: Realizar análise de crédito de cada Cliente
� Parte 3: Manter dados a respeito dos produtos a serem vendidos
� Parte 4: Manter e gerenciar os pedidos de compra feitos por esses clientes.
� Parte 5: Manter dados a respeito dos Funcionários
Parte 1 – Manter Dados a Respeito dos Clientes da Empresa
Manter dados a respeito dos clientes envolve armazenar todas as
informações a respeito de cada cliente para que ele possa ser encontrado
facilmente num momento de necessidade.
Nessa empresa, um cliente pode possuir vários tipos de endereço, por
exemplo, endereço residencial, de faturamento, de entrega, de cobrança, etc...,
e o sistema tem que ser capaz de armazenar qualquer quantidade e qualquer
tipo de endereço para cada cliente individualmente. Cada endereço deve ser o
mais completo possível, contendo além do nome da rua, do bairro, número do
prédio, o nome da cidade e do estado dessa cidade.
É necessário saber também dados a respeito do cônjuge de cada cliente
(se ele tiver um cônjuge), pois no cálculo do crédito para cada clientes, a renda
do cônjuge pode ser considerada.
É necessário também que o sistema consiga armazenar qualquer
quantidade de telefone que o cliente possa informar e qualquer quantidade de
e-mails.
Assim que um cliente é cadastrado no sistema, deve ser atribuído a ele
um tipo de cliente, ou seja, uma classificação de acordo com a sua renda
mensal.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 23 de 86
Parte 2 – Análise do Credito do Cliente
Inicialmente, quando um cliente é cadastrado no sistema, é atribuído a
ele um tipo de cliente, ou seja, uma classificação de acordo com a sua renda.
O cálculo do limite de crédito deve ser uma aplicação que poder ser executada
periodicamente, para todos os clientes de uma só vez ou para um cliente
especifico. Esse cálculo deve levar em consideração o tipo do cliente, a renda
do cônjuge, a soma total de pedidos realizados no ano corrente e o atraso
médio nos pagamentos das parcelas.
Esse valor que limita o crédito do cliente deve ser levado em
consideração sempre antes de realizar uma venda para cada cliente.
Parte 3 – Manter Dados a Respeito dos Produtos a Serrem Vendidos
Cada produto disponível para venda deve ser classificado por um tipo para
que seja possível realizar totalizações em estoque, produzindo dados
gerenciais. Por exemplo, o sistema deve ser capaz de fornecer informações
como:
� Quantidade de cada produto vendida num determinado período.
� Quantidade de um determinado tipo de produto vendida em um
determinado período.
� Tipo de produto vendido em cada região de cada cidade.
� Tipo de produto vendido em cada região de cada estado.
� Controle de quantidade em estoque de cada produto.
� Controle de quantidade em estoque de cada tipo de produto.
� Tipos de produto que são mais vendidos.
� Tipos de produto manos vendidos.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 24 de 86
Parte 4 – Manter e Gerenciar os Pedidos de Compra Feitos pelos
Clientes
Cada pedido de compra pode ser pago á vista ou ser parcelado.
O sistema deve ser capaz de fornecer informações a respeito de cada
pedido de um determinado usuário. Essas informações são:
� Data e valor do primeiro pedido do cliente.
� Parcelas em atraso de um pedido.
� Valor original de cada parcela.
� Valor de cada parcela com juros e multa.
O sistema deve ser capaz de informar os produtos que cada cliente já
comprou, e os produtos que cada um nunca comprou.
A cada venda realizada o sistema deve ser capaz de calcular o valor
total de cada pedido e dar baixa em estoque a cada produto vendido.
O sistema deve aceitar devolução de produtos comprados pelos clientes
se o referido pedido estiver em aberto. E essa devolução deve recolocar o
produto em estoque e retirar o valor do pedido a ser pago pelo cliente.
Parte 5 – Manter Dados a Respeito dos Funcionários
O sistema deve ser capaz de armazenar dados a respeito dos
funcionários e dos dependentes que cada funcionário possui.
Cada funcionário deve receber, alem de um salário fixo, bônus mensais
de acordo com os produtos que ele vender.
O desempenho de cada funcionário será medido por pontos que ele
recebe mensalmente. De acordo com essa pontuação, seu bônus pode ser
acrescido de determinados valores.
Todas as vezes que o salário fixo de cada funcionário for alterado, o
sistema deve armazenar no histórico o salário anterior, o atual e a data da
alteração.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 25 de 86
8.2 DIAGRAMA COM RELACIONAMENTOS DO SISTEMA
INFONEW
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 26 de 86
9. SELECT (Consulta)
Descrição: Este comando faz a seleção dos dados de uma ou mais
tabelas.
Sintaxe: SELECT <campo> FROM <tabela>
Exemplos:
SELECT COD_CLI, NOME_CLI,SEXO_CLI FROM CLIENTE
SELECT COD_CLI, NUM_LANC, DATA_CREDCLI, CRED_CLI
FROM CREDITO
SELECT COD_PROD, NOME_PROD, VAL_UNITPROD FROM
PRODUTO
SELECT COD_FUNC, NOME_FUNC, SAL_FUNC FROM
FUNCIONARIO
Para listar todos os campos da tabela, utilizamos o operador "*"
(asterisco)
SELECT * FROMCLIENTE
SELECT * FROM CREDITO
SELECT * FROM PRODUTO
SELECT * FROM FUNCIONARIO
Exercícios
1)Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
TipoEnd.
2) Escreva um comando que exiba todas as colunas e todas as linhas
de dados da tabela TipoProduto.
3) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
TipoCli.
4) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Estado.
5) Escreva um comando que exiba a Sigla do Estado e o nome das Cidades que
você possui em cada Estado no seu sistema.
6) Escreva um comando que exiba o nome dos clientes do seu cadastro de clientes,
a data de cadastro deste cliente no sistema, e o valor da renda que cada cliente possui.
7) Escreva um comando que exiba o nome de todos os funcionários do seu
cadastro de funcionários, a data de cadastro, o sexo e o salário de um dos funcionários
do seu sistema.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 27 de 86
9.1 ORDER BY (Ordem de retorno)
Definição: A cláusula ORDER BY determina a ordem de apresentação do
resultado de uma pesquisa de forma ascendente ou descendente.
Sintaxe:
SELECT <campo> FROM <tabela> [WHERE <condição>]
ORDER BY <campo_ordenacao> [ASC/DESC]
Exemplos:
/* seleciona todos os registros da tabela CLIENTES ordenando o
retorno pelo campo CODIGO em ordem ascendente */
SELECT * FROM CLIENTE ORDER BY COD_CLI ASC
/* seleciona todos os registros da tabela CLIENTES ordenando o
retorno primeiro pelo campo CODIGO em ordem descendente e
depois pelo campo NOME em ordem ascendente */
SELECT * FROM CLIENTES ORDER BY COD_CLI DESC,
NOME_CLI ASC
Exercícios
1) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Cliente ordenado pelo nome em ordem decrescente .
2) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Produto ordenado pelo valor unitário em ordem crescente
3) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Funcionário ordenado pelo nome na ordem crescente e a renda em ordem decrescente.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 28 de 86
9.2 ALIAS (Apelido)
Definição: Literal que identifica um campo, uma função ou uma tabela
Sintaxe:
SELECT (<campo> ou <função>) <alias do retorno>
FROM <tabela> <alias da tabela>
Exemplos:
/* seleciona o valor do campo NOME da tabela CLIENTES */
SELECT NOME_CLI AS "NOME DO CLIENTE"
FROM CLIENTE
Observe que a coluna Nome_Cli passou a chamar Nome do Cliente
/* listar o código do cliente, nome do cliente ,renda do CLIENTE e
renda do CLIENTE com acréscimo de 37% */
SELECT COD_CLI,NOME_CLI,RENDA_CLI,RENDA_CLI*1.37
FROM CLIENTE
Observe que está mostrando uma coluna com nome (No column name)
para não aparecer está coluna é necessário criar um alias para a coluna
conforme código abaixo.
SELECT COD_CLI,NOME_CLI,RENDA_CLI,RENDA_CLI*1.37
AS "RENDA COM AUMENTO"
FROM CLIENTE
Exercícios
1) Escreva um comando que exiba o nome do cliente, o valor da sua renda e apresente esta
mesma renda com acréscimo de 11%.
2) Escreva um comando que exiba o número do pedido, o valor do pedido e apresente
também o valor do pedido com desconto de 23%.
3) Escreva um comando que exiba o Nome do funcionário, o salário e apresente também o
salário com desconto de 9% para todos os funcionários que ganham mais do que 2500.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 29 de 86
9.3 WHERE (Restrições)
Descrição: Com esta cláusula é possível fazermos restrição dos registros
a serem manipulados, como veremos posteriormente ela também poderá
ser usados para outros comandos do SQL.
Sintaxe: SELECT <campo> FROM <tabela> WHERE <condição>
Exemplo:
/*serão listados todos os registro da tabela CLIENTES onde o
campo Cod_Cli for igual a 1 */
SELECT * FROM CLIENTE WHERE COD_CLI = 1
/*serão listados todos os registros da tabela PEDIDO onde o
campo Data_Ped for superior a 01/02/2001 E cod_sta= 1*/
SELECT * FROM PEDIDO WHERE DATA_PED > ‘2001/02/01’
AND COD_STA=1
/*serão listados todos os registros da tabela CLIENTE onde o
campo Nome_Cli for igual à Daniel Souza OU Clientes do sexo
Feminino */
SELECT * FROM CLIENTE WHERE NOME_CLI = 'DANIEL
SOUZA' OR SEXO_CLI=’F’
Obs: Não esqueça que na clausula where utiliza-se os operadores de
comparação: maior, menor, igual, diferente, maior ou igual e menor ou
igual.
Exercícios
1) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Cliente onde o código do cliente é igual a 3 .
2) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Produto onde o valor unitário do produto é acima de 1500.00
3) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Pedido que o Funcionário de código 3 vendeu E com valor abaixo de 250.00.
4) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Funcionário do sexo Feminino E que o salário seja superior a 1800.00
5) Escreva um comando que exiba todas as colunas e todas as linhas da tabela Pedido onde
não mostre os pedidos do cliente 2 E valor do pedido superior a 200 E na data inferior a
12/04/2002.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 30 de 86
9.4 BETWEEN (Condição com uma faixa de valores)
Definição: O operador BETWEEN determina a faixa de valores aceito de
uma pesquisa. Este comando substitui os operadores ‘>=’ e ‘<=’.
Sintaxe: SELECT <campo> FROM <tabela>
WHERE <campo> BETWEEN <valor_inicial> AND <valor_final>
Exemplos:
/* seleciona todos os registros da tabela CLIENTE onde o campo
Cod_Cli estiver entre 1 e 5 */
SELECT * FROM CLIENTE
WHERE COD_CLI BETWEEN 1 AND 5
/* seleciona todos os registros da tabela PEDIDO onde o campo
Data_Ped estiver entre ‘10/10/2003’ a ‘02/10/2004’ */
SELECT * FROM PEDIDO
WHERE DATA_PED BETWEEN '2003/10/10' AND
‘2004/10/02'
/*seleciona todos os registros da tabela PRODUTO onde o campo
Val_UnitProd estiver entre 500 e 1000 */
SELECT * FROM PRODUTO WHERE VAL_UNITPROD BETWEEN
500 AND 1000
Exercícios
1) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Cidade com o código entre 3 a 8.
2) Escreva um comando que exiba todas as colunas e todas as linhas dos clientes que possui
renda entre 250 a 1250.
3) Escreva um comando que exiba todas as colunas e todas as linhas dos funcionários que
possui salário entre 520,23 a 2544,45.
4) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Pedido no período entre 09/02/2002 a 02/09/2004.
5) Escreva um comando onde exiba todas as colunas e todas as linhas da tabela Produto com
valores unitários entre 435,9 a 875,23.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: NilsonA. Borges 31 de 86
9.5 LIKE (Comparação com uma parte de uma literal)
Definição: O operador LIKE faz a comparação somente com uma parte de
uma literal, desconsidera tudo que possa vir antes ou depois do valor
passado. O caracter ‘%’ significa qual à parte a ser desconsiderada
Sintaxe: SELECT <campo> FROM <tabela>
WHERE <campo> LIKE <[%]parte_literal[%]>
Exemplos:
/* seleciona todos os registros da tabela CLIENTES onde o campo
Nome_Cli terminar com a literal SOUZA */
SELECT * FROM CLIENTE
WHERE NOME_CLI LIKE ‘%SOUZA’
/* seleciona todos os registros da tabela CLIENTE onde o campo
Nome_Cli começar com a letra A */
SELECT * FROM CLIENTE
WHERE NOME_CLI LIKE ‘A%’
/* seleciona todos os registros da tabela CLIENTE onde o campo
Nome_Cli possuir a literal OLIVEIRA dentro do seu conteúdo */
SELECT * FROM CLIENTE WHERE NOME_CLI LIKE
‘%OLIVEIRA%’
Exercícios
1) Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
Cidade onde nome da cidade começa com a letra A.
2) Escreva um comando que exiba todas as colunas e todas as linhas dos Funcionários
onde possui o sobrenome GARCIA.
3) Escreva um comando onde exiba todas as colunas e todas as linhas da tabela Produto onde
liste os produtos que terminam com a letra O.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 32 de 86
9.6 DISTINCT (Valores únicos)
Definição: O operador DISTINCT elimina todas as replicações,
significando que somente dados distintos serão apresentados como
resultado de uma pesquisa.
Sintaxe: SELECT DISTINCT <campo> FROM <tabela> [WHERE
<condição>]
Exemplos:
/* seleciona todos os valores do campo Código do Cliente, valor
do pedido da tabela PEDIDO sem repetição dos códigos do cliente
*/
SELECT DISTINCT COD_CLI,VAL_PED FROM PEDIDO
/* seleciona todos os valores do campo data do pedido , valor do
pedido, da tabela Pedido sem repetição da data do pedido*/
SELECT DISTINCT DATA_PED,VAL_PED FROM PEDIDO
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 33 de 86
10. FUNÇÕES DE AGRUPAMENTO
� COUNT (CONTAR)
� SUM (SOMAR)
� AVG (MÉDIA)
� MAX (MÁXIMO)
� MIN (MÍNIMO)
10.1 COUNT (Contador de registros)
Definição: A função COUNT retorna a quantidade de registros
correspondentes a uma pesquisa.
Sintaxe: SELECT COUNT(*) FROM <tabela> [WHERE <condição>]
Exemplos:
/* seleciona a quantidade de registros da tabela Clientes */
SELECT COUNT(*) AS “TOTAL CLIENTES” FROM CLIENTE
/* seleciona a quantidade de registros da tabela pedidos onde o
campo data_ped estiver entre 02/02/2000 a 03/08/2002 */
SELECT COUNT(*)AS ”QUANTIDADE PEDIDOS” FROM PEDIDO
WHERE DATA_PED BETWEEN ‘2000/02/02’ AND
‘2002/08/03’
/* seleciona a quantidade de telefones que cada cliente possui */
SELECT COD_CLI,COUNT(COD_CLI) AS “TOTAL TELEFONE”
FROM FONE
GROUP BY COD_CLI
Exercícios
1) Escreva um comando onde mostra quantas Cidades tem Cadastrado no Infonew.
2) Escreva um comando onde mostre quantos funcionários é do sexo Masculino.
3) Escreva um comando que apresente quantos clientes são casados.
4) Escreva um comando que exiba quantos e-mails cada cliente tem.
5) Escreva um comando que exiba a quantidade de dependentes que cada funcionário tem
6) Escreva um comando que mostre quantos pedidos você tem de cada status.
.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 34 de 86
10.1.1 GROUP BY (Agrupamento de dados)
Definição: A cláusula GROUP BY agrupa todas as linhas de retorno de
uma pesquisa que possuírem o valor de seus campos iguais.
Sintaxe
:
SELECT (<campo_agrupamento> e/ou <funcao_agrupamento>)
FROM <tabela> [WHERE <condição>]
GROUP BY <campo_agrupamento>
Exemplo:
/* seleciona a quantidade de telefones que cada cliente possui
agrupado por cliente */
SELECT COD_CLI,COUNT(COD_CLI) AS “TOTAL TELEFONE”
FROM FONE
GROUP BY COD_CLI
/* seleciona a quantidade de emails que cada cliente possui
agrupado por cliente */
SELECT COD_CLI,COUNT(COD_CLI) AS “TOTAL EMAIL”
FROM EMAIL
GROUP BY COD_CLI
/* seleciona a quantidade de parcelas que cada pedido possui
agrupado pedido */
SELECT NUM_PED, COUNT(NUM_PED) AS “QUANT.PARCELAS”
FROM PARCELA
GROUP BY NUM_PED
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 35 de 86
10.2 SUM (Somatória de valores)
Definição: A função SUM faz a somatória dos valores de um campo.
Sintaxe: SELECT SUM(<campo>) FROM <tabela> [WHERE <condição>]
Exemplos:
/* seleciona a somatória dos valores do campo Renda_Cli da
tabela Clientes */
SELECT COD_CLI, SUM(RENDA_CLI) AS ”RENDA CLIENTE”
FROM CLIENTE
/* seleciona a somatória dos valores do campo Val_UnitProd da
tabela PRODUTO*/
SELECT COD_PROD, SUM(VAL_UNITPROD)AS ”VALOR TOTAL
PRODUTOS” FROM PRODUTO
GROUP BY COD_PROD
Exercícios
1) Escreva um comando onde mostre a soma de todos os salários dos funcionários agrupados
por código.
2) Escreva um comando onde mostre a soma de renda de todos os clientes do sexo Feminino.
3) Escreva um comando onde mostre a soma dos valores onde cada cliente gastou em
pedidos. Agrupar por cliente.
4) Escreva um comando onde mostre a soma dos valores em produtos vendidos em cada item.
Agrupar por produto.
5) Escreva um comando onde mostre a soma dos valores recebido de cada pedido de acordo
com a tabela parcela. Agrupar por pedido.
6) Escreva um comando onde mostre a soma dos valores recebido de cada pedido de acordo
com a tabela parcela onde as parcelas pagas e acima de 12/04/2002. Agrupar por pedido
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 36 de 86
10.3 AVG (Média de valores)
Definição: A função AVG faz a média dos valores de um campo.
Sintaxe: SELECT AVG(<campo>) FROM <tabela> [WHERE <condição>]
Exemplos:
/* seleciona a média dos valores do campo Renda_Cli da tabela
Clientes */
SELECT COD_CLI, AVG(RENDA_CLI) AS ”RENDA CLIENTE”
FROM CLIENTE
GROUP BY COD_CLI
/* seleciona a média dos valores do campo Val_UnitProd da
tabela PRODUTO*/
SELECT COD_PROD, AVG(VAL_UNITPROD)AS ”VALOR TOTAL
PRODUTOS” FROM PRODUTO
GROUP BY COD_PROD
Exercícios
1) Escreva um comando onde mostre a média de todos os salários dos funcionários agrupados
por código.
2) Escreva um comando onde mostre a média de renda de todos os clientes do sexo Feminino.
3) Escreva um comando onde mostre a média dos valores onde cada cliente gastou em
pedidos. Agrupar por cliente.
4) Escreva um comando onde mostre a média dos valores em produtos vendidos em cada
item. Agrupar por produto.
5) Escreva um comando onde mostre a média dos valores recebido de cada pedido de acordo
com a tabela parcela. Agrupar por pedido.
6) Escreva um comando ondemostre a média dos valores recebido de cada pedido de acordo
com a tabela parcela onde as parcelas pagas e acima de 21/08/2003. Agrupar por pedido
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 37 de 86
10.4 MIN, MAX (Menor e maior valor)
Definição: As funções MIN e MAX retornam respectivamente o menor e o
maior valor encontrado para um campo.
Sintaxe: SELECT MIN(<campo>) FROM <tabela> [WHERE <condição>]
SELECT MAX(<campo>) FROM <tabela> [WHERE <condição>]
Exemplos:
/* seleciona o menor do valor do campo Renda_Cli da tabela
Clientes */
SELECT MIN(RENDA_CLI) AS ”MENOR RENDA” FROM
CLIENTE
/* seleciona o maior do valor do campo Val_UnitProd da tabela
PRODUTO*/
SELECT MAX(VAL_UNITPROD) AS ”MAIOR PRODUTO” FROM
PRODUTO
Exercícios
1) Escreva um comando onde mostre o maior salário de todos os funcionários.
2) Escreva um comando onde mostre a menor de renda dos clientes do sexo Feminino.
3) Escreva um comando onde mostre o maior valor onde cada cliente gastou em pedidos.
4) Escreva um comando onde mostre o menor valor em produtos vendidos em cada item.
5) Escreva um comando onde mostre o menor valor recebido de cada pedido de acordo com a
tabela parcela. Agrupar por pedido
6) Escreva um comando onde mostre o maior valor recebido de cada pedido de acordo com a
tabela parcela onde as parcelas pagas e acima de 21/08/2003. Agrupar por pedido
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 38 de 86
11.HAVING (condição do agrupamento)
Definição: A cláusula HAVING faz com que o resultado da pesquisa seja
feito sob determinada condição. A condição HAVING está para a cláusula
GROUP BY da mesma forma que a cláusula WHERE está para o
comando SELECT
Sintaxe:
SELECT (<campo> ou <função>) FROM <tabela>
[WHERE <condição1>]
GROUP BY <campo>
HAVING <condição2>
Exemplos:
/* seleciona o campo Código Funcionário e a quantidade de
registros da tabela Dependentes agrupados pelo campo Cod_Func
onde o valor do campo Quantidade de Dependentes deve possuir
mais de 1 ocorrência */
SELECT COD_FUNC, COUNT(COD_FUNC) AS “TOTAL”
FROM DEPENDENTE
GROUP BY COD_FUNC HAVING COUNT(COD_FUNC) > 1
/* seleciona o campo RENDA, o menor e o maior valor do campo
RENDA da tabela CLIENTE agrupados pelo campo CODIGO onde
o valor do campo CODIGO deve possuir média superior a 450 */
SELECT COD_CLI, MIN(RENDA_CLI) AS “MINIMO”,
MAX(RENDA_CLI) AS “MÁXIMO” FROM CLIENTE GROUP BY
COD_CLI
HAVING AVG(RENDA_CLI) > 450
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 39 de 86
12. JOIN (Relacionamento de tabelas)
12.1 Introdução a Joins:
Um join é uma operação que permite você consultar duas ou mais
tabelas para produzir um result set que incorpora linhas e colunas de cada
tabela. Seu join ocorre em colunas das tabelas que são comuns em ambas às
tabelas.
Quando na execução de um join de tabelas, o SQL Server compara os
valores das colunas especificas, linha a linha e ele usa o resultado da
comparação para combinar os valores qualificando para novas linhas.
São quatro tipos de joins: inner joins, outer joins, cross joins e self joins.
12.2 TIPOS DE JOINS
� Inner Join
� Outer Join
� Cross Join
� Self Join
12.2.1 Usando Inner Joins:
Inner joins combinam tabelas pela comparação em colunas que são
comuns a ambas as tabelas. SQL Server retorna somente linhas que atendem
as condições do join.
Quando usar joins considere os seguintes fatos e diretrizes:
� Inner Joins são DEFAULT SQL Server. Você pode abreviar a clausula
INNER JOIN para JOIN.
� Especifique as colunas que você gostaria de mostrar no seu result set
pela inclusão do qualified name da coluna na listas do select.
� Inclua a clausula Where para restringir as linhas que são retornadas no
result set.
Não use um valor nulo como uma condição join porque valores nulos não
são avaliados igualmente com um outro.
12.2.2 Usando Outer Joins:
� Você usa um outer join para ver linhas que normalmente não atendem a
uma condição de join.
� Operador do Outer join é um sinal de (*) antes ou depois da condição de
igualdade, ou usando a palavra chave LEFT OUTER JOIN, RIGHT
OUTER JOIN.
Left ou Right outer joins, combinam linhas a partir de duas tabelas que
atenda a uma condição de join, mais qualquer das linhas que não atendam ou
da tabela do lado esquerdo ou da tabela do lado direito como especificado na
clausula JOIN. Linhas que não atendem a condição join mostram null no result
set.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 40 de 86
Você também pode usar full outer joins para mostrar toas às linhas nas
tabelas join, indiferentemente das tabelas terem atendido quaisquer dos
valores.
Quando utilizar left ou right outer joins, considere os seguintes fatos ou
diretrizes:
• SQL Server retorna somente linhas únicas quando você usa left ou right
outer joins.
• Use um left outer join para mostrar todas as linhas a partir da primeira
tabela nomeada. Se você inverter a ordem no qual as tabelas estão
listadas na clausula FROM, a declaração produz o mesmo resultado
como um right outer join.
• Não use um valor nulo como condição de join porque valores nulos são
avaliados igualmente com um outro.
• Voce pode abreviar o LEFT OUTER JOIN ou RIGHT OUTER JOIN
como LEFT JOIN ou RIGHT JOIN.
• Você pode usar outer joins entre duas tabelas somente.
12.2.3. Usando Cross Joins:
Cross join mostra combinação de todas as linhas das tabelas unidas.
Nenhuma coluna comum será necessária para fazer um cross join.
Quando você usa cross joins, SQL Server produz um produto cartesiano
no qual o numero de linhas no resultado seja igual ao numero de segunda
tabela. Por exemplo, se são 8 linhas na primeira tabela e 9 linhas na outra
tabela SQL Server retorna um total de 72 linhas.
12.3 Exemplos de JOIN (Relacionamento de tabelas)
/* seleciona o campo CODIGO, NOME da tabela CLIENTE e o
campo EMAIL DO CLIENTE da tabela EMAIL */
SELECT CLIENTE.COD_CLI,CLIENTE.NOME_CLI,
EMAIL.EMAIL_CLI
FROM CLIENTE INNER JOIN EMAIL
ON CLIENTE.COD_CLI =EMAIL.COD_CLI
OU
SELECT C.COD_CLI,C.NOME_CLI,
E.EMAIL_CLI
FROM CLIENTE C INNER JOIN EMAIL E
ON C.COD_CLI =E.COD_CLI
(Exemplos com a utilização de alias)
/*Utilizando o exemplo anterior agora queremos listar todos os
clientes possui ou não email, para fazer tal consulta é necessário a
utilização do comando OUTER JOIN */
SELECT CLIENTE.COD_CLI,CLIENTE.NOME_CLI,
EMAIL.EMAIL_CLI
FROM CLIENTE LEFT OUTER JOIN EMAIL
ON CLIENTE.COD_CLI = EMAIL.COD_CLI
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 41 de 86
/*Utilizando o exemplo anterior agora queremos fazer todas as
combinações possíveis de cliente com email, para fazer tal
consulta é necessário a utilização do comando CROSS JOIN */
SELECT CLIENTE.COD_CLI,CLIENTE.NOME_CLI,
EMAIL.EMAIL_CLIFROM CLIENTE CROSS JOIN EMAIL
12.3 Combinação de mais de 2 tabelas
/* seleciona o campo CODIGO, NOME da tabela CLIENTE
NOME_RUA, NOME_BAIRRO da tabela ENDEREÇO e
NOME_CID da tabela CIDADE */
SELECT CLIENTE.COD_CLI,CLIENTE.NOME_CLI,
ENDERECO.NOME_RUA,ENDERECO.NOME_BAIRRO,
CIDADE.NOME_CID
FROM CLIENTE INNER JOIN ENDERECO
ON CLIENTE.COD_CLI =ENDERECO.COD_CLI
INNER JOIN CIDADE
ON CIDADE.COD_CID =ENDERECO.COD_CID
OU
SELECT CL.COD_CLI,CL.NOME_CLI,E.NOME_RUA,
E.NOME_BAIRRO,CI.NOME_CID
FROM CLIENTE CL JOIN ENDERECO E
ON CL.COD_CLI =E.COD_CLI
JOIN CIDADE CI
ON CI.COD_CID =E.COD_CID
Exercícios
1) Escreva um comando o exibe o código e nome do cliente da tabela Cliente e número do
telefones dos clientes .
2) Escreva um comando o exibe o nome do funcionário e o sexo da tabela Funcionário e o
nome dos dependentes, sexo e data de nascimento da tabela dependente.
3) Escreva um comando o exibe o código e nome do cliente da tabela Cliente e nome do
cônjuge da tabela Cônjuge.
4) Escreva um comando o exibe o código do funcionário e o nome do funcionário da tabela
Funcionário e a pontuação de cada funcionário da tabela pontuação. Exibir todos os
funcionários tendo pontuação ou não.
5) Escreva um comando onde mostre o nome do cliente, sexo do cliente da tabela CLIENTE,
número do pedido da tabela PEDIDO e o status do pedido da tabela STATUSPEDIDO
6) Escreva um comando onde mostre o nome do cliente da tabela CLIENTE, número do pedido
da tabela PEDIDO e o nome do funcionário que atendeu este pedido da tabela FUNCIONARIO.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 42 de 86
13. IN (Condição com valores fixos)
Definição: O operador IN determina os valores fixos aceitos de uma
pesquisa.
Sintaxe:
SELECT <campo> FROM <tabela>
WHERE <campo> IN (<lista_de_valores>)
Exemplos:
/* seleciona todos os registros da tabela CLIENTE onde o campo
CODIGO for igual a 1 ou 2 ou 3 ou 4 */
SELECT * FROM CLIENTE
WHERE COD_CLI IN (1,2,3,4)
/* seleciona todos os registros da tabela PEDIDO onde o campo
NUMERO for igual a 45 ou 50 */
SELECT * FROM PEDIDO
WHERE NUM_PED IN (45,50)
/* seleciona todos os registros da tabela TIPOCLI onde o campo
CODIGO for igual a 1 ou 3 ou 5 ou 6*/
SELECT * FROM TIPOCLI
WHERE COD_TIPOCLI IN (1, 3, 5, 6)
/* seleciona todos os registros da tabela TIPOCLI onde o campo
NOME for igual a OURO
SELECT * FROM TIPOCLI
WHERE NOME_TIPOCLI IN (‘OURO’)
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 43 de 86
14. SUBQUERIES (Pesquisa dentro de um comando)
Definição: O conceito de SUBQUERIES possibilita criar um consulta
(comando SELECT) dentro de um outro comando(SELECT, INSERT,
UPDATE, DELETE).
Sintaxe:
Não existe uma sintaxe fixa, mas existem algumas restrições:
- deve estar sempre entre parênteses;
- não pode ser usada na cláusula ORDER BY;
- a subquery não pode conter ORDER BY, COMPUTE ou SELECT
INTO
- a subquery não pode, em hipótese alguma, retornar mais de uma
coluna no caso de comparação por valores ou listas;
- a subquery deve usar a função EXISTS se fizer um SELECT *;
- não pode usar na lista de colunas do SELECT uma coluna do tipo
IMAGE ou TEXT.
Exemplos:
/* seleciona todos os registros da tabela CLIENTE onde o campo
RENDA for maior ou igual ao maior valor da média do campo
RENDA */
SELECT * FROM CLIENTE
WHERE RENDA_CLI > = (SELECT AVG(RENDA_CLI)
FROM CLIENTE )
/* seleciona todos os registros da tabela PRODUTOS o campo
VALOR UNITARIO é maior do o menor valor do campo VALOR
DO PEDIDO */
SELECT * FROM PRODUTO WHERE VAL_UNITPROD >
(SELECT MIN(VAL_PED) FROM PEDIDO)
/* seleciona todos os registros da tabela CLIENTE onde exibe os
clientes de não possuem telefone */
SELECT * FROM CLIENTE
WHERE COD_CLI NOT IN (SELECT COD_CLI FROM FONE)
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 44 de 86
/* seleciona todos os registros da tabela CLIENTE onde exibe os
clientes que possuem email */
SELECT * FROM CLIENTE
WHERE COD_CLI IN (SELECT COD_CLI FROM EMAIL)
/* seleciona todos os registros da tabela CLIENTE onde exibe os
clientes que fizeram pedidos */
SELECT * FROM CLIENTE
WHERE COD_CLI IN (SELECT COD_CLI FROM PEDIDO)
Alguns casos podem ser resolvidos tanto por SUBQUERIES como por
JOINS, outros apenas por SUBQUERIES.
Exercícios
1) Escreva um comando que exiba todos os Funcionários onde o salário seja maior ou igual à
média dos Salários. .
2) Escreva um comando que exiba os pedidos onde o valor_unitário de cada pedido seja
maior do que a média dos valores dos produtos cadastrados.
3) Escreva um comando que exiba todos os dados dos clientes solteiros.
4) Escreva um comando que apresente os dados de todos os clientes que não
fizerem nenhum pedido.
5) Escreva um comando que apresente os dados de todos os clientes que possuem telefone.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 45 de 86
15. CRIANDO VISÕES (VIEW)
As views nada mais são do que instruções SELECT já pré-definidas
e armazenadas no banco. É um objeto do SQL Server utilizado para dar
nome a uma query. Este conceito pode parecer simples, e é, mas pode-se
resolver muitos problemas com as views.
Uma view não é um programa, portanto dentro dela você só pode
escrever o comando Select. Você pode colocar dentro de uma view o
comando select feito com as seguintes critérios:
� Consultas com união (Join)
� Consultas com união (Union)
� Consultas com Group By com função de totalização
� Consultas com subconsultas (Subquery)
� Consultas com Order By apenas com Top.
Uma view não pode ter um select com order by sem a clausula Top. Uma
view não recebe parâmetros de entrada.
Para criar uma view, utilizamos o comando CREATE VIEW. Sua
sintaxe:
Sintaxe:
CREATE VIEW <view_name> AS
<instrução_SELECT>
Exemplos:
/* Criar uma view que seleciona todos os registros da tabela
CLIENTE onde o campo CODIGO for igual a 1 ou 2 ou 3 ou 4 */
CREATE VIEW V_CLIENTE AS
SELECT * FROM CLIENTE
WHERE COD_CLI IN (1,2,3,4)
Para testar a View digite somente o código abaixo
SELECT * FROM V_CLIENTE
Suponha que necessitamos liberar o acesso aos dados da tabela
Funcionário, mas não podemos liberar o acesso aos dados da
coluna Salário (Sal_Func). Para solucionar esta questão, você
poderia criar uma View sobre essa tabela e liberar para o usuário
acesso à tabela apenas por meio da view:
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 46 de 86
/* Criar uma view Funcionário da situação relatada anteriormente */
CREATE VIEW V_FUNC AS
SELECT COD_FUNC,NOME_FUNC,
SEXO_FUNC,END_FUNC
FROM FUNCIONARIO
Para testar a View digite somente o código abaixo
SELECT * FROM V_FUNC
Outro ponto importante sobre as views: não podemosutilizar a
cláusula ORDER BY na definição da view. Como uma exceção: quando se
utiliza TOP para limitar os registros:
-- Isto é uma definição válida de uma view
CREATE VIEW V_PEDIDO
AS
SELECT TOP 50 NUM_PED,COD_CLI,DATA_PED
FROM PEDIDO
ORDER BY DATA_PED
Para testar a View digite somente o código abaixo
SELECT * FROM V_FUNC
Não podemos utilizar as cláusulas COMPUTE, COMPUTE BY e INTO
dentro de uma view
Exercícios
1) Crie uma view que apresenta o nome dos clientes, o salário de cada um (totalizando com o
salário dos cônjuges – daqueles que tiverem cônjuge) e o valor do último crédito concedito a
cada um.
2) Crie uma view apresente o nome de todos os produtos vendidos em cada pedido.
3) Crie uma view que apresente o nome dos funcionários e o total de bônus de cada um.
4) Crie uma view que apresente os dados de todos os clientes que não fizerem nenhum
pedido.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 47 de 86
16. CRIANDO STORED PROCEDURE
(PROCEDIMENTOS)
Stored Procedures: são conjuntos de instruções, armazenados em
bancos de dados, que realizam operações com os demais dados do banco.
São úteis em um programa por várias razões, uma delas é para se evitar a
necessidade de ler os dados do banco, fazer as operações neles e depois
gravar novamente. Outra é pela possibilidade que trazem de se balancear o
processamento entre o servidor de banco de dados e o servidor da aplicação
que acessa o banco. Possuem 2 tipos a respeito a execução: dinâmica e
estática.
A utilização da palavra dinâmico na computação, em geral,
representa algo mais desejável do que sua contrapartida estática. Como
exemplo podemos comparar as funcionalidades de uma página web dinâmica
com uma página web estática ou mesmo na resolução dinâmica de nomes
(DNS) com uma resolução estática.
Pois bem, nem sempre devemos preferir o que é dinâmico ao que é
estático por vários motivos. No que diz respeito aos bancos de dados, sempre
que possível devemos utilizar instruções estáticas. As instruções estáticas são
aquelas que normalmente programamos em uma aplicação e que serão
encaminhadas para o banco de dados por um método de acesso. Elas podem
ser estáticas para o banco de dados e serem montadas dinamicamente na
aplicação.
Para explicar como funcionam os comandos dinâmicos no SQL
Server vou utilizar um exemplo. Suponham que, por alguma necessidade,
precisamos fazer um SELECT em uma tabela através de uma Stored
Procedure. Mas não sabemos em qual tabela devemos utilizar, ou seja,
somente em tempo de execução saberemos qual tabela devemos consultar.
Nesta situação uma boa opção é utilizar o recurso de execução dinâmica de
instruções SQL.
Esta execução dinâmica funcionará da seguinte forma: uma string
deve ser montada com a instrução que desejamos executar. Esta string deve
possuir uma instrução sintaticamente correta para o SQL Server e, tendo esta
string montada, devemos executa-la em tempo de execução. No nosso
exemplo, vamos passar como parâmetro de entrada de uma Stored Procedure
no nome da tabela que desejamos consultar.
Em seguida vamos concatenar o valor do parâmetro com uma string
e colocar a string completa dentro de uma variável. Por fim, iremos executar
esta instrução com o comando EXECUTE() do SQL Server. O código desta
Stored Procedure é mostrado abaixo:
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 48 de 86
/* Esta Procedure vai fazer um Select Dinâmico em alguma Tabela */
CREATE PROCEDURE ST_SQL_DINAMICO @NOME_TABELA
VARCHAR(50)
AS
BEGIN
/* Declarando a Variável que conterá a instrução */
DECLARE @INSTR_SQL VARCHAR(500)
/* Contatenando a instrução com o Nome da Tabela */
SET @INSTR_SQL = "SELECT * FROM " + @NOME_TABELA
/* EXECUTANDO A INSTRUÇÃO DINAMICAMENTE */
EXECUTE(@INSTR_SQL)
END
Para executar esta Stored Procedure basta indicar qual é o nome da
tabela:
EXEC ST_SQL_DINAMICO "Infonew.dbo.Cliente"
Muito bem, vimos como funciona a execução dinâmica de instruções
do SQL Server. Mas quais são os pontos negativos da execução dinâmica? Em
exemplos como o citado acima não há grandes problemas. O SQL Server só
vai ter um pouco de trabalho para fazer o parser da instrução em tempo de
execução, compilar e colocar o plano de execução no procedure cache a cada
vez que esta Stored Procedure for executada. Se esta Stored Procedure for
executada muitas vezes podemos contornar o problema acima utilizando a
system Stored Procedure sp_executesql no lugar do EXECUTE(). Para mais
informações sobre a sp_executesql procurem na documentação oficial do SQL
Server, o Books Online, que contém uma boa descrição de seus parâmetros e
alguns exemplos de uso.
Outro inconveniente da execução dinâmica é o retorno de dados. Se
desejarmos fazer alguma manipulação nos dados retornados por uma
execução dinâmica, devemos fazer uso de uma tabela temporária que será
preenchida com o retorno da execução. Esta tabela temporária vai armazenar o
último resultado da(s) instrução(es) dinâmica(s) e deve conter a mesma
quantidade e tipos de dados das colunas que o resultado.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 49 de 86
16.1 Stored Procedure (Parâmetros de Saída):
Stored Procedure - Passagem de parâmetros em SQL Server 2000”,
trago agora neste texto, uma forma prática de como usar parâmetros de
saídas, que é um recurso complementar ao trabalho com stored procedure.
Lembrando que continuamos usando como exemplo o Microsoft
SQL Server 2000, mas os conceitos básicos podem ser aplicados em
praticamente todos os bancos de dados relacionais do mercado.
Parâmetros de saída
O uso de parâmetros de saída em stored procedure possibilita que o
engine do servidor de banco de dados utilize menos recursos de hardware para
retornar e exibir os resultados de uma instrução SQL, possibilitando um
aumento de performance em seu envio, processamento e saída.
Vale lembrar também, que o uso de parâmetros de saída, permite
uma boa manipulação dos valores retornados.
O comando CREATE PROCEDURE (com o uso de OUTPUT)
Para especificar que um stored procedure terá uma variável do tipo
saída, use a forma normal de sintaxe para criação de stored procedure
adicionando o flag OUTPUT no final da declaração de cada variável, veja a
sintaxe na Listagem 1:
Create Procedure NOME_DA_PROCEDURE (@VARIÁVEL
TIPO_DE_DADO OUTPUT)
As
Begin
** Instrução SQL desejada atribuindo um valor ao
parâmetro(variável) **
end
Listagem 1. Sintaxe do comando CREATE PROCEDURE.
Executando uma procedure e recuperando seu valor de retorno.
Para invocar a execução de um stored procedure, é usado a mesma sintaxe
simples EXEC NOMEPROCEDURE, porém, a diferença está na necessidade
em declararmos uma variável com o mesmo tipo de dado do parâmetro, e
especificarmos que ela receberá o valor retornado pelo parâmetro de saída
através da função “output”.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 50 de 86
Veja na Listagem 2, a sintaxe básica para esse tipo de execução:
/*Declaraçãode variáveis que receberam o resultado da procedure */
declare @VARIÁVEL tipo ...
/*execução e atribuição do valor de retorno. */
exec NomeStoredProcedure @VARIÁVEL output …
/* Manipulação do resultado. */
PRINT 'O valor é: '+@VARIAVEL
Listagem 2. Sintaxe de execução de um stored procedure e recuperação do
valor retornado através do uso da função output.
Lembrando que você pode aplicar todos os recursos de criação e execução de
stored procedure, como por exemplo, as funções: alter procedure, drop
procedure, with encryption, etc.
Exemplos práticos:
Veja aqui, alguns exemplos práticos que mostram como criar stored procedure
com parâmetros de saída.
Nota 01
Você pode testar esses scripts em qualquer database, porém antes, é
necessário criar e inserir dados na tabela de exemplo conforme a Listagem 3.
/*Criação da tabela que será o objeto de nossos exemplos */
create table FUNCIONARIOS
(CODIGO varchar(5),
NOME varchar(80),
FUNCAO int,
DEPARTAMENTO int,
SALARIO money
CONSTRAINT PK_CODIGO PRIMARY KEY (CODIGO))
go
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 51 de 86
/*Inserções para povoamento da tabela de FUNCIONARIOS */
insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO,
SALARIO )
values('00001','OSCAR ALHO DA SILVA', '5','30', 2500)
go
insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO,
SALARIO )
values('00002','JOÃO DA SILVA SAURO', '5','30', 3500)
go
insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO,
SALARIO )
values('00003','MARIA DA BOA MORTE', '7','20',1500)
go
insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO,
SALARIO )
values('00004','BENEVENUTO LOPES ARAUJO', '7','10',1500)
go
insert into FUNCIONARIOS (CODIGO, NOME,FUNCAO,DEPARTAMENTO,
SALARIO )
values('00005','MATUZALEM ALVES', '5','30',1500)
Listagem 3. Script SQL para Criação e povoamento da tabela de
testes(FUNCIONARIOS).
Exemplo 02 - Implementação de um procedure com um parâmetro de saída
/*Procedure simples para recuperar a soma dos salários da tabela
FUNCIONARIOS */
create procedure sp_BuscaSalario (@SOMA money OUTPUT)
as
begin
select @SOMA=sum(SALARIO) from FUNCIONARIOS
end
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 52 de 86
/*Execução do stored procedure */
/*Declaração de variáveis que receberam o resultado do procedure */
declare @SALARIO_TOTAL money
/*execução*/
exec sp_BuscaSalario @SALARIO_TOTAL output
/* Manipulação do resultado*/
PRINT 'Salário total R$'+(CAST(@SALARIO_TOTAL AS
varchar(20)))
Listagem 4. Recuperando o valor de uma soma de consulta.
Exemplo 03 - Recuperando um valor e aplicando uma estrutura de decisão
/*Procedure simples para recuperar a soma dos salários e aplicar uma
estrutura de decisão*/
Create Procedure sp_BuscaSalario2 (@SOMA money OUTPUT)
as
begin
select @SOMA=sum(SALARIO) from FUNCIONARIOS
end
/*Execução do procedure */
declare @SALARIO_TOTAL money
exec sp_BuscaSalario2 @SALARIO_TOTAL output
if @SALARIO_TOTAL < 20000
begin
print '***SALÁRIO BAIXO***** '
print 'Salário total R$'+(CAST(@SALARIO_TOTAL AS
varchar(20)))
end
else
begin
print '***SALÁRIO ALTO***** '
print 'Salário total R$'+(CAST(@SALARIO_TOTAL AS
varchar(20)))
end
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 53 de 86
Listagem 5. Parâmetros e estrutura IF ..ELSE..
Exemplo 04 - Passando e Recuperando parâmetros
/*Procedure com passagem de parâmetro como critério de seleção para
recuperar a soma dos salários da tabela FUNCIONARIOS através de
parâmetro de saída*/
create procedure sp_BuscaSalario_porDepto (@DEPTO int,
@SOMA money OUTPUT)
as
begin
select @SOMA=sum(SALARIO) from FUNCIONARIOS
where
DEPARTAMENTO=@DEPTO
end
/*Execução do procedure */
/*Variável de saída */
declare @SALARIO_TOTAL money
/*Variável para passagem de parâmetro*/
declare @DEPARTAMENTO int
set @DEPARTAMENTO=30
/*execução*/
exec sp_BuscaSalario_porDepto @DEPARTAMENTO, @SALARIO_TOTAL
output
PRINT 'Salário total do departamento '+ cast(@DEPARTAMENTO
as varchar)+ ' é R$'+(CAST(@SALARIO_TOTAL AS varchar(20)))
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 54 de 86
Listagem 6. Procedure com um parâmetro de entrada, e outro de saída.
Nota 02
Caso você esteja usando o SQL Query Analyzer, o resultado da execução de
seus stored procedure será semelhante ao mostrado na Figura 01.
Figura 1: Resultado da execução de um stored procedure através do SQL
Query Analyzer
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 55 de 86
17. CRIANDO FUNCTION (UDF :FUNCÕES DEFINIDAS
PELO O USUÁRIO )
FUNÇÕES DEFINIDAS PELO USUÁRIO - UDF
Nas linguagens de programação as funções quebram grandes
tarefas de processamento de dados em pequenas partes. Conjuntos
complexos de funções podem ser divididos em pequenas unidades lógicas
mais facilmente gerenciáveis. Qualquer código que deve executar uma tarefa
lógica específica, quando incorporado a uma função, pode ser chamado de
dentro de um programa sem a necessidade de repetir o código responsável por
aquela tarefa várias vezes.
Uma função definida pelo usuário (UDF) é um módulo de instruções
Transact-SQL que pode ou não possuir argumentos e que retorna um
resultado. Por exemplo, uma função pode informar o valor de uma parcela
mensal a partir de uma taxa de juros informada, calcular a distância entre dois
pontos, selecionar uma lista de empregados que pertencem a um
departamento, etc.
Uma UDF é muito parecida com uma stored procedure, ou seja,
possui instruções que controlam o fluxo do programa e a manipulação dos
dados.
Uma UDF difere de uma stored procedure nos seguintes aspectos:
• Não permite qualquer alteração no estado global da sessão, por
exemplo, como a mudança do banco de dados.
• Não permite a utilização de parâmetros com a clásula OUTPUT.
• Não pode ser usada com a opção “FOR XML” para obtenção do
resultado. Portanto, a opção “FOR XML” pode ser utilizada
apenas em stored procedures.
• Erros do Transact-SQL que causam o cancelamento da instrução,
e então continuam na próxima instrução de um módulo (como em
triggers e stored procedures) são tratados de modo diferente
dentro de uma função. Em uma função, os erros encontrados em
instruções Transact-SQL interrompem seu processamento.
Entretanto, se você necessita de procedimentos que retornam
resultados, uma UDF é frequentemente a melhor escolha para uma aplicação
SQL, pelo simples fato de poder ser chamada a partir de uma instrução SQL.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 56 de 86
17.1 Definindo uma functionUDF:
Antes de poder definir uma UDF, deve-se determinar suas
características, tais como, seu nome, sua vinculação ou não aos objetos
referenciados, o número e tipos de dados dos parâmetros que ela terá, e
finalmente, os tipos de valores retornados. Você então executa a instrução
CREATE FUNCTION para criar a UDF. Caso após definir a função, você venha
a descobrir que qualquer uma das características não são apropriadas, você
poderá usar a instrução ALTER FUNCTION para alterar as informações
utilizadas em sua definição.
Você não poderá fazer uso da instrução ALTER FUNCTION para
modificar o tipo de uma função, ou seja, uma função do tipo scalar valued para
uma função do tipo table valued ou vice-versa. Similarmente, você não poderá
utilizar a instrução ALTER FUNCTION para modificar uma função do tipo inline
para uma função multi-statement ou vice-versa. Entretanto, você poderá
remover funções UDF com a instrução DROP FUNCTION.
É necessário ter permissão sobre a instrução CREATE FUNCTION
para criar, alerar ou excluir UDFs. Outros usuários, além do criador da UDF,
devem ter as permissões apropriadas antes de poder utilizá-la em instruções
Transact-SQL. Para criar ou alterar tabelas com CHECK constraint, cláusula
DEFAULT ou uma computed column, que façam referências a UDFs, o usuário
deve ter a permissão de REFERENCES sobre a função.
As instruções válidas em uma função incluem:
• A instrução DECLARE poderá ser utilizada para definer as variáveis e
cursores que serão locais para a função.
• As atribuições de valores para objetos locais da função são permitidas
com a utilização da opção SET para variáveis locais do tipo table e
scalar.
• Operações com cursores locais que são declarados, abertos, fechados e
liberados dentro da função. A instrução FETCH que retorna dados para
o cliente não é permitida. Apenas a instrução FETCH que atribui valores
para variáveis locais e que utilizam a cláusula INTO são permitidas.
• São permitidas as instruções de controle de fluxo (tais como IF..ELSE,
WHILE, RETURN, GOTO, BREAK, and CONTINUE).
• As instruções SELECT que contenham expressões que atribuam valores
para variáveis que são locais para a função.
• As instruções UPDATE, INSERT, and DELETE que modificam variáveis
do tipo table que são locais para a função.
• A instrução EXECUTE com chamadas para extended stored procedures.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 57 de 86
As seguintes regras se aplicam quando uma UDF faz chamadas a uma
extended stored procedure:
• A extended stored procedure não pode retornar dados para o cliente. Se
a stored procedure retorna dados, a execução da função chamadora
falhará.
• A extended stored procedure pode se reconectar ao SQL Server;
entretanto, a chamada a extended stored procedure deve fazer parte da
mesma transação que a função que a chamou
• As funções que chamam extended stored procedure são marcadas
como funções nondeterministic. (Veja abaixo no ítem determinismo da
função).
• A extended stored procedure é executada no contexto de segurança da
conta de usuário do Microsoft Windows® sob a qual o SQL Server está
rodando. O criador da stored procedure deve considerer isso quando
atribuir direitos de EXECUTE para os usuários.
17.2 Tipos de Function:
O SQL Server 2000 implementa três tipos de UDFs assim conhecidas:
• Scalar valued functions
• Inline table valued functions
• Multi-statement table valued functions
17.2.1. Funções Scalar valued:
Uma função definida pelo usuário do tipo scalar valued retorna um
único valor em resposta a cada uma das chamadas à função. Uma função é do
tipo scalar valued se a cláusula de retorno especifica um tipo de dado scalar do
SQL Server. Funções do tipo Scalar valued podem ser definidas com a
utilização de múltiplas instruções Transact-SQL.
Exemplo:
CREATE FUNCTION Volume
(@Altura decimal (4,1), @Largura decimal (4,1), @Profund
decimal (4,1))
RETURNS decimal (12,3) –- tipo de dado de retorno.
AS
BEGIN
RETURN (@Altura * @Largura * @Profund)
END
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 58 de 86
A cláusula RETURNS, no exemplo anterior, especifica que um
decimal scalar_data_type sera retornado pela função. A cláusula RETURNS
pode ser qualquer um dos tipos de dados conhecidos como scalar_data_types
do SQL Server exceto timestamp, text, ntext, or image.
Para as funções do tipo scalar valued, utiliza-se a instrução
RETURN com um argumento. O valor desse argumento é retornado como o
resultado da função. O tipo de dado do argumento passado com a instrução
RETURN deve poder ser implicitamente convertido para o tipo de dado do
valor de retorno da função.
Nas chamadas às funções do tipo scalar, deve-se utilizar ou o
formato de nome nome_do_usuário.nome_da_função ou
nome_do_banco_de_dados. nome_do_usuário.nome_da_função
Você não pode usar o nome da função isoladamente; essa restrição
existe para diferenciar as chamadas às UDFs das chamadas às funções do
SQL Server. Nós poderíamos chamar a função que definimos anteriormente da
seguinte forma:
Para testar a Function
Select dbo.Volume(12.2,10.6,10.0)
17.2.2. Funções Table Valued:
Para funções do tipo inline table valued, a cláudula RETURNS é
seguida da palavra TABLE sem uma lista de colunas. As funções do tipo inline
table valued retornam valores apresentados como se extraídos de tabelas e
são definidas com uma única instrução SELECT em sua construção. As
colunas, incluindo os tipos de dados retornados pela função, são derivados da
lista da instrução SELECT que define a função.
Exemplo:
CREATE FUNCTION fn_Clientes_na_Regiao (@Regiao nvarchar
(30))
RETURNS TABLE
AS
RETURN (
SELECT CustomerID, CompanyName
FROM Northwind.dbo.Customers
WHERE Region = @Regiao
)
A cláusula RETURNS no exemplo acima apresenta apenas a
palavra TABLE sem especificar qualquer lista de colunas desejadas. Note
também que existe uma única instrução RETURN que contém a instrução
SELECT com a definição do corpo da função.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 59 de 86
Para testar a Function :
SELECT * FROM fn_Clientes_na_Regiao (N'WA')
17.2.3. Funções Multi-Statement Table Valued:
Se a cláusula RETURNS especificar uma tabela com suas colunas e
tipos de dados, a função será do tipo multi-statement table valued.
Exemplo:
CREATE FUNCTION LargeOrderShippers (@FreightParm
money)
RETURNS @OrderShipperTab TABLE
(
ShipperID int,
ShipperName nvarchar (80),
OrderID int,
ShippedDate datetime,
Freight money
)
AS
BEGIN
INSERT @OrderShipperTab
SELECT S.ShipperID, S.CompanyName,
O.OrderID, O.ShippedDate, O.Freight
FROM Shippers AS S INNER JOIN Orders AS O
ON S.ShipperID = O.ShipVia
WHERE O.Freight > @FreightParm
RETURN
END
A cláusula RETURNS no exemplo acima define uma variável local
de retorno do tipo table chamada @OrderShipperTab, e também define a
estrutura da tabela com a definição das colunas.
As instruções no corpo da função inserem linhas na variável table
local @OrderShipperTab para construir a tabela que será o resultado retornado
pela função.
Note que a instrução RETURN não tem argumento. Ovalor da
variável local do tipo table definida sera o valor retornado pela função.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 60 de 86
A seguir temos um exemplo de como chamar a função do tipo multi-
statement table valued:
SELECT *
FROM LargeOrderShippers ($500)
Opcionalmente, chamadas às funções do tipo table valued poderão ser
seguida de um alias para a tabela. Pode-se também utilizar um alias para
se referir às colunas que são retornadas pela função nas cláusulas SELECT
e WHERE.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 61 de 86
18. CRIANDO TRIGGERS (GATILHOS )
Triggers são procedimentos que podem ser gravados em Transact
SQL, Java, PL/SQL ou C. São executados (ou disparados) implicitamente
quando uma tabela é modificada, um objeto é criado ou ocorrem algumas
ações de usuário ou de sistema de banco de dados.
As triggers são similares as stored procedures diferindo, apenas, na
maneira como são chamadas. As Triggers são chamadas indiretamente pelos
comandos INSERT, UPDATE ou DELETE executada implicitamente quando
ocorre algum evento de trigger enquanto a stored procedure deve ser
executado explicitamente.
Uma trigger é composta por quatro partes:
• Momento
• Evento
• Tipo
• Corpo
O momento define quando uma trigger irá ser acionada. Pode ser:
• BEFORE (tabela)
• AFTER (tabela)
• INSTEAD OF (view)
BEFORE indica que os comandos PL/SQL do corpo da trigger serão
executados ANTES dos dados da tabela serem alterados. Normalmente
usamos BEFORE nos casos em que precisamos incializar variáveis globais,
validar regras de negócios, alterar o valor de flags ou para salvar o valor de
uma coluna antes de alterarmos o valor delas.
Exemplo:
/ * Escreva um trigger que realize a baixa em estoque a cada produto vendido
em cada pedido * /
CREATE TRIGGER T_BaixaEstoque
ON Itens
BEFORE INSERT
AS
IF(SELECT Cod_Sta
FROM Pedido INNER JOIN inserted
ON Pedido.Num_Ped = Inserted.Num_Ped) = 1 --Se
o Pedido estiver aberto
UPDATE Produto
SET Qtd_EstqProd = Qtd_EstqProd -
Inserted.Qtd_Vend
FROM Produto INNER JOIN inserted
ON Produto.Cod_Prod = Inserted.Cod_Prod
ELSE
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 62 de 86
BEGIN
RAISERROR('Este pedido não está aberto -
Operação não Executada',16,1)
ROLLBACK TRANSACTION
RETURN
END
AFTER indica que os comando PL/SQL do corpo da trigger será
executado APÓS os dados da tabela serem alterados. Normalmente usamos
AFTER para completar os dados de outras tabelas e para completar a atividade
de outra trigger de momento BEFORE.
Exemplo:
CREATE TRIGGER T_BaixaEstoque
ON Itens
AFTER INSERT
AS
IF(SELECT Cod_Sta
FROM Pedido INNER JOIN inserted
ON Pedido.Num_Ped = Inserted.Num_Ped) = 1 --Se
o Pedido estiver aberto
UPDATE Produto
SET Qtd_EstqProd = Qtd_EstqProd -
Inserted.Qtd_Vend
FROM Produto INNER JOIN inserted
ON Produto.Cod_Prod = Inserted.Cod_Prod
ELSE
BEGIN
RAISERROR('Este pedido não está aberto -
Operação não Executada',16,1)
ROLLBACK TRANSACTION
RETURN
END
INSTEAD OF indica que a trigger irá ser executada no lugar da
instrução que disparou a trigger. Literalmente, a instrução é substituída pela
trigger. Essa técnica permite que façamos, por exemplo, alterações em uma
tabela através de uma view. É usado nos casos em que a view não pode
alterar uma tabela por não referenciar uma coluna com a constraint not null.
Nesse caso a trigger pode atualizar a coluna que a view não tem acesso.
Dois detalhes muito importantes sobre INSTEAD OF:
• Só funcionam com views
• É sempre de linha. Será considerado assim, mesmo que "FOR EACH
ROW" for omitido.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 63 de 86
Exemplo:
/* Escreva um trigger que não permita a alteração e a exclusão de dados na
tabela Estado * /.
CREATE TRIGGER T_Estado
ON Estado
INSTEAD OF UPDATE,DELETE
AS RAISERROR('Operação não permitida nesta
tabela',16,1)
O evento define qual é a instrução DML que aciona a trigger. Informa
qual instrução SQL irá disparar a trigger. Pode ser:
• INSERT
• UPDATE
• DELETE
Quando o evento for um UPDATE podemos informar quais colunas
que, ao serem alteradas, irão disparar a trigger. O mesmo NÃO ocorre com
INSERT e DELETE porque essas instruções sempre afetam a linha por inteiro.
Exemplo:
CREATE OR REPLACE TRIGGER novo_func
AFTER INSERT ON emp
.
.
.
END
O evento pode conter uma, duas ou todas as três operações DML
em uma única linha de comando.
Exemplo:
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
.
.
.
END
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 64 de 86
O tipo define quantas vezes uma trigger será executa. A trigger pode
ser executada uma vez para a instrução que a disparou ou ser disparada para
cada linha afetada pela instrução que disparou a trigger. Pode ser:
- Instrução (STATEMENT)
- Linha (ROW)
Quando a trigger for do tipo instrução ela será disparada uma vez
para cada evento de trigger, mesmo que nenhuma linha tenha sido afetada.
São úteis para aquelas trigger que eventualmente não alteram dados ou para
situações onde o que queremos é uma resposta da trigger, por exemplo, em
uma restrição complexa de negócio. Por DEFAULT toda trigger é deste tipo.
Exemplo:
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH STATEMENT
.
.
.
END
Quando a trigger for do tipo linha, a trigger será executada toda vez
que a tabela for afetada pelo evento da trigger. Se nenhuma linha for afetada a
trigger não será executada. São muito úteis quando a ação da trigger depende
dos dados afetados pelo evento da trigger.
Exemplo:
CREATE OR REPLACE TRIGGER novo_func
BEFORE INSERT OR UPDATE OR DELETE ON emp
FOR EACH ROW
.
.
.
END
O corpo define a ação que uma trigger irá executar quando
acionada. O corpo de uma trigger é composto por um bloco PL/SQL, a
chamada de uma PROCEDURE ou por um procedimento JAVA. Por definição,
o tamanho de uma trigger não pode ultrapassar 32K.
Como, normalmente, precisamos trabalhar com os valores antes e
depois da alteração dos dados, a trigger permite que façamos referencia aos
valores antes da alteração (OLD) e após a alteração (NEW).
O nome de uma trigger deve ser único dentro de um mesmo
esquema, e sua sintaxe básica é:
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 65 de 86
CREATE [OR REPLACE] TRIGGER [schema.]
nome_da_trigger
[BEFORE|AFTER]
[DELETE|OR INSERT|OR UPDATE[OF coluna]]ON [schema.] nome_da_tabela_ou_da_view
[REFERENCING [OLD [AS] OLD] [NEW [AS] NEW]
[FOR EACH ROW]
[WHEN [condição]]
BLOCO PL/SQL
Onde:
a) Nome_da_trigger é o nome da trigger;
b) Nome_da_tabela_ou_da_view indica a tabela ou view associada com a
trigger; Corpo_da_trigger é a ação que a trigger irá executar.
c) Inicia por DECLARE ou BEGIN e termina por END. Também pode
conter a chamada de um procedimento.
d) O uso do nome da coluna na cláusula UPDATE pode aumentar a
performance porque a trigger só será disparada quando aquela coluna
especificada na cláusula for alterada.
Agora que sabemos como criar uma trigger veremos um exemplo
completo:
Primeiro vamos criar uma tabela para gravar um registro de todos os
usuários que se conectaram ao banco:
CREATE TABLE vigia
(marca VARCHAR2(100));
CREATE OR REPLACE TRIGGER marca_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' entrou no sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY
HH24:MI:SS'));
COMMIT;
END
Pronto, temos nossa primeira trigger. Ela registra o nome do usuário
e a que horas ele entrou. Esse exemplo foi retirado diretamente da
documentação Oracle. No nosso exemplo fazemos referencia a um evento do
sistema ao invés de referenciarmos uma tabela.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 66 de 86
Outros eventos do sistema são:
• AFTER SERVERERROR
• AFTER LOGON
• BEFORE LOGOFF
• AFTER STARTUP
• BEFORE SHUTDOWN
Você pode criar triggers usando os eventos acima para DATABASE
e SCHEMA. As duas exceções são SHUTDOWN e STARTUP que só se
aplicam a DATABASE.
Exemplo:
CREATE OR REPLACE TRIGGER marca_logoff
BEFORE LOGOFF ON SCHEMA
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' saiu do sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY
HH24:MI:SS'));
COMMIT;
END;
Eventualmente podemos ter algum tipo de erro em nossa trigger.
Para verificar quais são os erros de compilação que temos na trigger basta
usar o comando SHOW ERRORS TRIGGER nome_da_trigger. Caso você
queira ver os erros de compilação da última trigger que você compilou pode
escrever apenas SHOW ERRORS ou SHO ERR. Ao executarmos esse
comando ele mostrará a linha onde está o erro. Atenção: caso a linha onde
está o erro se estenda por mais de uma linha, este comando indicará o início
da linha. Vamos criar uma trigger com erro para servir como exemplo:
CREATE OR REPLACE TRIGGER marca_logon
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO sys.vigia
VALUES (USER || ' entrou no sistema em ' ||
TO_CHAR(sysdate, 'DD-MM-YYYY
HH24:MI:SS));
COMMIT;
END;
/
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 67 de 86
19. EXERCICIOS
EXERCÍCIO 2 - Manipulando Dados do SYSAMPLE
Neste exercício, você vai pôr em prática os conceitos básicos da linguagem Transact-SQL,
utilizando comandos que executam as quatro operações com dados:
� SELECT
� INSERT
� UPDATE
� DELETE
Neste exercício você vai trabalhar com leitura dos dados.
1. Escreva um comando que exiba todas as colunas e todas as linhas de dados da
tabela TipoEnd.
2. Escreva um comando que exiba todas as colunas e todas as linhas de dados da
tabela TipoProd.
3. Escreva um comando que exiba todas as colunas e todas as linhas de dados da
tabela TipoCli.
4. Escreva um comando que exiba todas as colunas e todas as linhas de dados da
tabela Estado.
5. Escreva um comando que exiba a sigla do estado e o nome das cidades que você
possui em cada estado no seu sistema.
6. Escreva um comando que exiba o nome dos clientes do seu cadastro de clientes, a
data de cadastro desse cliente no sistema e o valor da renda que cada cliente possui.
7. Escreva um comando que exiba o nome de todos os funcionários do seu cadastro de
funcionários, a data de nascimento, o sexo e o salário de um dos funcionários do seu
sistema.
8. Escreva um comando que exiba todos os dados da tabela Histórico do funcionário de
código 1.
9. Escreva um comando que exiba o nome, o tipo, a quantidade e o valor unitário dos
produtos em estoque.
10. Escreva um comando que exiba o nome do cliente, o valor da sua renda e apresente
também essa mesma renda com um acréscimo de 10%, para todos os clientes do seu
database.
11. Escreva um comando que exiba o nome do cliente, o valor da sua renda e apresente
também essa mesma renda com um acréscimo de 10%, apenas para os clientes com
renda inferior a 1000.00.
12. Escreva um comando que aplique 20% de aumento salarial para todos os funcionários
do sexo feminino e que ganhem um salário inferior a 1000.00.
13. Escreva um comando que aplique um aumento de 10% ao valor dos produtos do tipo
1.
14. Escreva um comando que aplique um aumento de 5% ao valor dos produtos do tipo 2.
15. Escreva um comando que aplique uma redução de 20% ao valor dos produtos do tipo
3.
16. Escreva um comando que elimina todos os itens vendidos no pedido 10.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 68 de 86
EXERCÍCIO 3 - Manipulando Dados do SYSAMPLE
1. Escreva um comando que exiba todas as colunas e todas as linhas de dados da tabela
TipoEnd, ordenando os dados de forma crescente pela coluna Nome_Tipo.
2. Escreva um comando que apresente os dados dos clientes ordenados de forma
decrescente pela coluna Nome_Cli.
3. Escreva um comando que apresente quantos clientes são casados e quantos clientes
são solteiros.
4. Escreve um comando que exiba quantos e-mails cada cliente tem.
5. Escreva um comando que exiba quantos clientes você tem de cada tipo.
6. Escreva um comando que exiba a quantidade de parcelas de que é composto cada
pedido.
7. Escreva um comando que exiba a quantidade de dependentes que cada funcionário
tem.
8. Escreva um comando que mostre quantos produtos você tem de cada tipo.
9. Escreva um comando que mostre o quanto cada cliente gastou em pedidos.
10. Escreva um comando que mostre quantos clientes você tem em cada cidade.
11. Escreva um comando que mostre quantas cidades você tem em cada estado.
12. Escreva um comando que exiba o total de bônus que cada funcionário recebeu.
13. Escreva um comando que mostre o total de salários pagos para os funcionários do
sexo masculino e para os do sexo feminino.
14. Escreva um comando que mostre quantos pedidos você tem de cada status.
15. Escreva um comando que exiba a quantidade de endereços que cada cliente tem.
16. Escreva um comando que exiba o total de pontos que cada funcionário fez por mês.
17. Escreva um comando que mostre quantos pedidos cada funcionário atendeu.
18. Escreva um comando que mostre quantos pedidos de cada cliente cada funcionário
atendeu.
19. Escreva um comando que mostre o total de salários pagos aos funcionários.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 69 de 86
EXERCÍCIO 4 - Manipulando Dados do SYSAMPLE
1. Escreva um comando que apresente todos os dados dos clientes solteiros.
2. Escreva um comando que apresente os dados do cliente que tem o salário mais alto.
3. Escrevaum comando que apresente os dados dos clientes que não têm telefone.
4. Escreva um comando que apresente os dados dos clientes que não têm telefone nem
e-mails.
5. Escreva um comando que apresente os dados de todos os clientes que não fizeram
nenhum pedido.
6. Escreva um comando que apresente os dados dos clientes que fizeram pelo menos um
pedido.
7. Escreva um comando que apresente os dados dos clientes que fizeram pedidos que
foram atendidos pelo funcionário de código 1.
8. Escreva um comando que apresente todos os pedidos que foram pagos à vista.
9. Escreva um comando que apresente os dados dos clientes que fizeram pedidos
atendidos pelos funcionários que não têm dependentes.
10. Escreva um comando que mostre os dados dos clientes que têm o maior crédito.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 70 de 86
EXERCÍCIO 5 - Manipulando Dados do SYSAMPLE
(Junção de Tabelas “Join” )
1. Escreve um comando que exiba o e-mails cada cliente , apresentando o nome de cada
cliente.
2. Escreva um comando que exiba os clientes você tem de cada tipo, apresentando o
nome de cada tipo.
3. Escreva um comando que mostre o número, a data e o valor total de cada pedido,
mostrando também o nome de cada cliente que fez o pedido e o nome de cada funcionário
que atendeu cada pedido.
4. Escreva um comando que apresente o nome de cada cliente e o nome do cônjuge
daqueles que forem casados.
5. Escreva um comando que apresente os dados dos cliente, os e-mails de cada um e os
telefones de cada um.
6. Escreva um comando que mostre os nomes dos clientes, os endereços com o nome de
cada cidade e o nome de cada estado de cada endereço que cada cliente tem.
7. Escreva um comando que mostre o nome de cada funcionário e o nome de cada
dependente de que cada funcionário que tem dependentes.
8. Escreva um comando que apresente o nome dos funcionários e o histórico de cada um.
9. Escreva um comando que mostre o nome de cada cliente, o número, data e valor total de
cada pedido que cada cliente fez e o valor de cada parcela de cada pedido.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 71 de 86
EXERCÍCIO 6- Manipulando Dados do SYSAMPLE
(Junção de Tabelas “Join” com funções de agrupamento)
1. Escreva um comando que exiba a quantidade de parcelas que compõem cada pedido,
exibindo todos os dados dos pedidos.
2. Escreva um comando que exiba a quantidade de dependentes que cada funcionário
tem, mostrando o nome de cada funcionário.
3. Escreva um comando que mostre quantos produtos você tem de cada tipo, mostrando
o nome do tipo.
4. Escreva um comando que mostre o quanto cada cliente gastou em pedidos, mostrando
o nome do cliente.
5. Escreva um comando que mostre quantos clientes você tem em cada cidade,
mostrando o nome de cada cidade.
6. Escreva um comando que mostre quantas cidades você tem em cada estado,
mostrando o nome do estado.
7. Escreva um comando que exiba o total de bônus que cada funcionário recebeu,
mostrando o nome de cada funcionário.
8. Escreva um comando que exiba a quantidade de endereços que cada cliente tem,
mostrando o nome de cada cliente.
9. Escreva um comando que exiba o total de pontos que cada funcionário fez por mês,
mostrando o nome do funcionário.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 72 de 86
EXERCÍCIO 7– Views
1. Crie uma view que apresente o nome dos clientes, o salário de cada um (totalizado
com o salário dos cônjuges - daqueles que tiverem cônjuge) e o valor do último
crédito concedido a cada um.
2. Crie uma view que apresente o nome de todos os produtos vendidos em cada
pedido.
3. Crie uma view que apresente o nome dos funcionários e o total de bônus de cada
um.
4. Crie uma view que apresente o nome de cada cliente, o número do pedido, data do
pedido, valor do pedido.
5. Crie uma view que apresente o nome de cada funcionário e o nome de cada
dependente de cada funcionário que tem dependentes.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 73 de 86
EXERCÍCIO 8 – FUNCTIONS
1. Escreva uma função que receba o código do cliente como parâmetro e retorne a
renda salarial desse cliente, incluindo na sua renda o salário do seu cônjuge se ele
for casado.
2. Escreva uma função que retorne a renda salarial de todos os cliente, incluindo na
renda de cada um o salário do seu cônjuge daqueles que são casados.
3. Escreva uma função que retorne o código do cliente como parâmetro e o valor do
último pedido que esse cliente fez.
4. Escreva uma função que retorne todos os dados do funcionário cujo código deve ser
recebido como parâmetro.
5. Escreva uma função de receba o número de um pedido como parâmetro, retorne o
código do funcionário que atendeu este pedido e o valor e data de vencimento de
cada parcela desse pedido.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 74 de 86
EXERCÍCIO 9 - STORED PROCEDURE
1. Escreva uma procedure que receba o código do cliente e um valor percentual como
parâmetro e aplique este percentual de desconto no último pedido que esse cliente
fez.
2. Escreva uma procedure que receba o código do cliente e um valor percentual como
parâmetro e aplique este percentual de desconto no último pedido que esse cliente
fez se o valor deste pedido representar menos de 10% da renda salarial deste cliente
(considerando o salário do cônjuge na renda).
3. Escreva uma procedure que receba o número de pedido como parâmetro e calcule o
valor total deste pedido, gravando esse total para o referido pedido.
4. Faça uma procedure que aplique um aumento salarial (cujo valor deve ser recebido
como parâmetro) para os funcionários que tiverem menos de dois dependentes.
5. Escreva uma procedure que receba um valor percentual como parâmetro e aplique
um desconto no último pedido ainda não pago de cada cliente do estado de São
Paulo.
6. Escreva uma procedure que receba um valor percentual como parâmetro, e aplique
este valor percentual com relação ao salário de cada funcionário para cada um que
tenha tido uma pontuação acima de 8.0 no mês corrente.
7. Escreva uma procedure que receba como parâmetro o número do pedido e um
número que represente a quantidade de parcelas em que este pedido será dividido.
A procedure deve obter o valor total deste pedido, calcular o valor de cada parcela e
gravar cada parcela na tabela Parcelas.
Se a quantidade de parcelas ultrapassar 3, acrescente 10% ao valor total do pedido,
divida-o na quantidade de parcelas recebida como parâmetro e grave-as na tabela
Parcelas.
Se a quantidade de parcelas for 1, retornea mensagem: pedido à vista e interrompa
o processamento.
Não deixe que o número de parcelas ultrapasse o 10. Se ultrapassar, retorne a
mensagem: Quantidade de parcelas inválida.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 75 de 86
EXERCÍCIO 11 – TRIGGERS
1. Escreva um trigger que realize a baixa em estoque a cada produto vendido em cada
pedido.
2. Escreva um trigger que realize a devolução em estoque de cada produto retirado
(devolvido) de cada pedido.
3. Escreva um trigger que realize a atualização em estoque de cada produto trocado em um
pedido.
4. Escreva um trigger que não permita a alteração de dados na tabela TipoEnd e a sua
exclusão.
5. Escreva um trigger que não permita a alteração de dados na tabela Estado e a sua
exclusão.
6. Escreva um trigger que não permita a alteração de dados na tabela TipoProd e a sua
exclusão.
7. Escreva um trigger que não permita a alteração de dados na tabela TipoCli e a sua
exclusão.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 76 de 86
MANUAL TÉCNICO
Instalação do Microsoft SQL Server 2000
CONSIDERAÇÕES PRELIMINARES
Este manual descreve a instalação do SQL 2000 Server.
REQUISITOS
Requisito Mínimo Recomendável
Memória 128 MB 256 MB
Processador Celeron 800 Mhz Celeron 900 Mhz
HD* 3 GB 5 GB
Obs.: Os requisitos mínimos de instalação referem-se ao particionamento do disco, feito por
meio da instalação do Windows Server 2003. De acordo com o tamanho da base de dados
da Unidade, a utilização da configuração mínima pode ocasionar travamento do
computador.
INSTALAÇÃO DO SQL SERVER 2000
Coloque o CD do SQL 2000 Server no drive. Caso o autorun não seja executado, acesse o
CD pelo Windows Explorer ou pelo botão START, opção RUN... e execute o arquivo
autorun.exe.
Será exibida uma caixa de mensagem, como a demonstrada na figura abaixo, informando
que o SQL Server 2000, quando instalado sem o Service Pack 2, não é suportado pelo
Windows Server 2003. Não se assuste! Para contornar essa situação, a Franqueadora
disponibiliza, junto com o kit do WebGestão, o CD de instalação do Service Pack 3a, que
será tratado mais adiante.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 77 de 86
Em seguida, selecione a opção SQL Server 2000 Componentes, clicando sobre ela.
Na tela seguinte, selecione a opção Install Database Server.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 78 de 86
Aguarde até que o programa de instalação seja carregado.
Na janela “Welcome”, clique sobre o botão Next >.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 79 de 86
Na janela “Computer Name”, selecione a opção Local Computer e clique sobre o botão
Next >.
Na janela “Installation Selection”, selecione a opção Create a new instance of SQL
Server, or Install Client Tools e clique sobre o botão Next >.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 80 de 86
Na janela “User Information”, digite o nome do usuário e o nome da empresa nos campos
Name e Company. Em seguida, clique sobre o botão Next >.
Na janela “Software License Agreement”, será é exibido o contrato de licença. Leia-o com
atenção e, em seguida, clique sobre o botão Yes.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 81 de 86
Com isso, surgirá a janela “Installation Definition”. Nela, selecione a opção Server and
Client Tools e clique sobre o botão Next >.
Na janela “Instance Name”, selecione a opção Default e clique sobre o botão Next >.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 82 de 86
Na janela “Setup Type”, selecione o tipo de instalação Typical. Mude o path de instalação
nas opções “Program Files” e “Data Files” para E:, mantendo as pastas-padrão e clique
sobre o botão Next >.
Na janela “Services Accounts”, selecione a opção Use the same account for each
service. Auto start SQL Server Service. Feito isso, no quadro “Service Settings”,
selecione a opção Use the Local System account e clique sobre o botão Next >.
Na janela “Authentication Mode”, selecione a opção Mixed Mod (Windows Authention
and SQL Server Authentication). Feito isto, digite a senha desejada e clique sobre o
botão Next >.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 83 de 86
Na janela “Start Copying Files”, apenas leia o texto e, em seguida, clique sobre o
botão Next >.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 84 de 86
Na janela “Choose Licensing Mode”, selecione a opção Per Seat for, digite o número 10 na
caixa “Devices” e clique sobre o botão Continue.
Após ter informado todos os dados necessários para o programa de instalação, a cópia dos
arquivos será iniciada.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 85 de 86
Ao término da instalação, clique sobre o botão Finish
CONSIDERAÇÕES FINAIS
Esta apostila foi confeccionada para o treinamento de SQL Server
2000 no Centro Universitário Fundação Santo André.
Em caso de dúvida mandar e-mail para nilson.dba@uol.com.br, não
esqueça de citar as mensagens de erro.
SQL Server 2000 .
Curso Microsoft SQL Server 2000
Autor: Nilson A. Borges 86 de 86
REFERÊNCIAS BIBLIOGRÁFICAS:
Leão, Renata de Oliveira
SQL 2000 SERVER: Estrutura e Implementação de sistemas de Banco de
Dados
São Paulo , Editora Erica, 2002Gunderloy,Mike e Jorden,Joseph L.
Dominando o SQL SERVER 2000: A Bíblia
São Paulo , Editora Makron Books, 2001Mais conteúdos dessa disciplina
Proposta de Estratégia de Tuning para Consultas- ATIVIDADE EXTENSIONISTA Cidadania Digital e Segurança de Dados para Comunidades PRONTO ABNT 2025
- Programação e Desenv de Banco de Dados
- Folha Resposta_SAP 2 - Resumo
- Exercicios Tecnologia da informacao
- Tradicionalmente, o Hadoop Distributed File System (HDFS) dominou ambientes on-premises, otimizado para grandes volumes e processamento em lote. Co...
- 2. O que é o modelo Cliente/Servidor e quais são os papéis do cliente e do servidor nesse tipo de arquitetura
- Durante a análise da proposta da Lei Orçamentária Anual (LOA) de 2024 no Estado X, o Tribunal de Contas identificou a inclusão de dispositivos que tra
- Uma intervenção eficaz contra doenças transmitidas por vetores em uma cidade exige vigilância oportuna e ação territorializada, guiadas por dados de q
- 0:05:37 Questão 4/10 - Big Data Presto é um motor de consultas SQL distribuído open source para consultas analíticas interativas capaz de operar em vo
- Métricas de Tl são ferramentas essenciais para mensurar desempenho, otimizar recursos e alinhar tecnologia e estratégia. O entendimento preciso des...
- Métricas bem-definidas permitem avaliar se a tecnologia da informacáo está contribuindo para atingir os objetin égicos da organizacáo. Indicadores ...
- A distinção entre dados, informação e conhecimento é essencial para o uso estratégico da tecnologia da informação. Com base nisso, considere o segu...
- Na nomenclatura de banco de dados, restrição corresponde a uma regra que deve ser obedecida pelo SGBD. Seja a restrição "um funcionário não pode te...
- m relação a banco de dados, analise as seguintes afirmações: Um banco de dados pode adicionar outra camada de segurança se for usado com sua próp...
- voce precisa Exemplos de temas: A inteligência artificial na educação O uso de plantas medicinais pela população Surto de alguma doença infecciosa ...
- m relação ao MySQL, assinale (V) VERDADEIRO ou (F) FALSO, para as seguintes afirmações: ( ) O MySQL é um código aberto e um RDBMS (SQL Relationa...
- O trecho "Os dados obtidos sugerem que a nova política de incentivo é promissora, apesar da pequena margem de erro na medição." utiliza marcadores tex
- AV3 GABARITO INTROD. A PROGRAMAÇÃO 2019
- AUDITORIA DE SISTEMAS Exercícios 03 NOTA 100 - 2019
