NUNCA MAIS PASSE RAIVA POR NÃO CONSEGUIR RESOLVER UM PROBLEMA COM O EXCEL - GARANTIDO!

UNIVERSIDADE DO VBA - Domine o VBA no Excel Criando Sistemas Completos - Passo a Passo - CLIQUE AQUI

Você está em: PrincipalArtigosSQL Server 2005 › Capítulo 09 : 19
Quer receber novidades e e-books gratuitos?
« Lição anterior Curso Completo de SQL Server 2005 - Júlio Battisti Δ Página principal Curso Completo de SQL Server 2005 - Júlio Battisti ¤ Capítulos Curso Completo de SQL Server 2005 - Júlio Battisti Próxima lição »
SQL Server 2005 - CURSO COMPLETO
Autor: Júlio Battisti
Lição 151 - Capítulo 09 - EXEMPLO PRÁTICO 01: USANDO VIEWS PARA CRIAR CAMPOS CALCULADOS E TOTALIZAÇÕES

No Capítulo 1, eu apresentei os fundamentos do Modelo Relacional de Dados, que é o modelo utilizado pelo SQL Server 2005. Dentre outras, apresentei duas regras fundamentais:

Cada assunto em uma tabela. Não se misturam assuntos, na mesma tabela.

Não se armazenam valores calculados, a não ser em casos específicos, de bancos de dados utilizados para dados consolidados, para suporte a sistemas de decisão ou Data Mining.

 Neste nosso primeiro exemplo, vamos analisar um caso que se encaixa bem com estas duas regras básicas. Vamos continuar trabalhando com o banco de dados NwindAccess, o qual você importou para o SQL Server 2005, no início deste capítulo. Neste banco de dados, temos uma tabela Pedidos, na qual estão armazenas as informações do cabeçalho do pedido, tais como: Número do Pedido, Código do Cliente, Código do Funcionário, Data do Pedido, Valor do frete e assim por diante. Neste banco de dados, foi criada uma segunda tabela, chamada Detalhes do Pedido. Esta tabela contem os itens individuais, de cada pedido. Esta separação foi necessária, por que um mesmo pedido, pode ter vários itens. Se não tivesse sido feita esta separação, as informações do cabeçalho do pedido, teriam que ser repetidas, tantas vezes quantos fossem os itens do pedido. Vimos que esta repetição vai contra todos os princípios do Modelo Relacional e só causaria problemas. Por isso a separação em duas tabelas. Na Figura 9.18, temos uma visão geral das tabelas Pedidos e Detalhes do Pedido, os campos de cada tabela e o relacionamento que existe entre elas, através do campo NúmeroDoPedido.

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.18 As tabelas Pedidos e Detalhes do Pedido.

 Muito bem, o nosso primeiro problema prático, é diretamente relacionado com as tabelas Pedidos e Detalhes do pedido. Note que não temos um campo que calcula o valor total para cada pedido. Este será justamente o nosso trabalho, neste exemplo. Vamos criar uma View, que nos dê o resultado indicado na Figura 9.19

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.19 O resultado que deve ser retornado pela nossa View.

Neste exemplo, será exibido o Número do Pedido, a Data do Pedido, o nome do cliente e o valor total para o pedido. Observe que não temos armazenados, nas tabelas, o valor total para o pedido. Mas, por outro lado, temos todas as informações necessárias para calcular este total. Se fossemos calcular, manualmente, o total do pedido, o que teríamos que fazer???

Muito simples. Primeiro devemos calcular o valor individual de cada item do pedido. Depois, somar os valores de todos os itens do pedido. Para o cálculo do valor de cada item, vamos usar uma fórmula bem simples, na qual utilizaremos o PreçoUnitário do item, a Quantidade e o percentual de desconto. A fórmula que utilizaremos, para o total de cada item, individualmente, será a seguinte:

([Quantidade]*[PreçoUnitário])*(1-[Desconto])

Bem, a questão que fica é: Como chegar a este resultado, usando uma View, no SQL Server 2005?? A resposta esta logo a seguir. Continue acompanhando.

Exemplo prático: Criar uma View que retorne os resultados indicados na Figura 9.19. Para criar esta View, siga os passos indicados a seguir:

1.         Abra o SQL Server Management Studio e navegue até o banco de dados NwindAccess.

2.         Clique no sinal de + ao lado do banco de dados NwindAccess.

3.         Clique com o botão direito do mouse na opção Views, abaixo de NwindAccess e,  no menu de opções que é exibido, clique em New View...

4.         Será exibida a janela para criação de uma nova View, a qual você aprendeu a utilizar no exemplo anterior. No campo View name, digite Con - Total por Pedido

5.         Na parte do comando da View, após o AS é que vem o comando T-SQL para a criação da View. Vamos iniciar bem “light”, onde criaremos um comando que exibe os campos NúmeroDoPedido da tabela Pedidos, DataDoPedido da tabela Pedidos e NomeDoDestinatário da tabela Pedidos. Para criar esta View inicial, digite o seguinte comando, na linha abaixo do AS:

SELECT Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário FROM Pedidos

6.         Pressione Ctrl+S para salvar a view e feche a janela de criação da View.

7.         Abra a janela de execução de comandos T-SQL e execute o seguinte comando:

SELECT * FROM [Con - Total por Pedido]

Observe que o nome da View vem entre colchetes. Isso é necessário por que existem espaços em branco no nome da View.

8.         Você deverá obter os resultados indicados na Figura 9.20:

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.20 Resultados da versão 1.0 da nossa View.

9.         Muito bem. Agora vamos começar a alterar nossa View. O primeiro passo será adicionar também campos da tabela Detalhes do Pedido. Com isso, veremos como fica o comando para criar uma View que retorna dados de duas ou mais tabelas relacionadas.

10.       Localize a view Con - Total por Pedido, clique com o botão direito do mouse na View e, no menu de opções que é exibido, clique em Delete. Será exibida uma janela solicitando confirmação. Clique em OK para confirmar a exclusão da View. É preciso excluir a View e criá-la novamente, pois vamos adicionar novas colunas na View.

11.       Clique com o botão direito do mouse na opção Views, abaixo de NwindAccess e,  no menu de opções que é exibido, clique em New View...

12.       Será exibida a janela para criação de uma nova View. No campo View name, digite Con - Total por Pedido

13.       Na parte do comando da View, após o AS é que vem o comando T-SQL para a criação da View. Vamos agora usar um comando que, além dos campos que já existiam na versão anterior da View, irá adicionar também os campos PreçoUnitário, Quantidade e Desconto, da tabela detalhes do pedido. O objetivo desta etapa, é mostrar como representar, através de comandos T-SQL, o relacionamento entre duas ou mais tabelas. Para criar esta View, digite o seguinte comando, na linha abaixo do AS:

SELECT Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário, [Detalhes do Pedido].PreçoUnitário, [Detalhes do Pedido].Quantidade,

[Detalhes do Pedido].Desconto FROM Pedidos

INNER JOIN [Detalhes do Pedido] ON Pedidos.NúmeroDoPedido = [Detalhes do Pedido].NúmeroDoPedido

O detalhe realmente importante, nesta versão da View, é o seguinte trecho do comando:

INNER JOIN [Detalhes do Pedido] ON Pedidos.NúmeroDoPedido = [Detalhes do Pedido].NúmeroDoPedido

Este é o trecho que faz o relacionamento entre as tabelas Pedidos e Detalhes do pedido, através do campo NúmeroDoPedido. O nome da tabela Detalhes do Pedido vem sempre entre colchetes, por que tem espaços no nome da tabela.

14.       Pressione Ctrl+S para salvar a view e feche a janela de criação da View.

15.       Abra a janela de execução de comandos T-SQL e execute o seguinte comando:

SELECT * FROM [Con - Total por Pedido]

Observe que o nome da View vem entre colchetes. Isso é necessário por que existem espaços em branco no nome da View.

16.       Você deverá obter os resultados indicados na Figura 9.21:

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.21 Resultados da versão 2.0 da nossa View.

Observe que já são exibidos os campos PreçoUnitário, Quantidade e Desconto, da tabela Detalhes do Pedido. Ainda estamos longe do resultado final desejado, mas estamos caminhando na direção certa. Um detalhe interessante a observar é que na listagem, são exibidas várias linhas para cada um dos pedidos. Por exemplo, para o primeiro pedido – 10248 são exibidas três linhas. Isso ocorre por que este pedido tem três itens. Cada linha tem informação de um dos itens do pedido.

Muito bem. Qual o próximo passo?

Agora vamos alterar novamente o comando T-SQL. Vamos retirar os campos PreçoUnitário, Quantidade e Desconto. No lugar destes três campos, vamos criar um único campo calculado, o qual nos fornecerá o total por item de cada pedido. Vamos ao trabalho então.

17.       Localize a view Con - Total por Pedido, clique com o botão direito do mouse na View e, no menu de opções que é exibido, clique em Delete. Será exibida uma janela solicitando confirmação. Clique em OK para confirmar a exclusão da View. É preciso excluir a View e criá-la novamente, pois vamos retirar colunas e adicionar uma nova coluna (calculada) na View.

18.       Clique com o botão direito do mouse na opção Views, abaixo de NwindAccess e,  no menu de opções que é exibido, clique em New View...

19.       Será exibida a janela para criação de uma nova View. No campo View name, digite Con - Total por Pedido

20.       Na parte do comando da View, após o AS é que vem o comando T-SQL para a criação da View. Vamos agora usar um comando que, além dos campos NúmeroDoPedido, DataDoPedido e NomeDoDestinatário, da tabela Pedidos, exiba também um campo calculado, que chamaremos de TotalDoPedido. O valor deste campo será calculado a fórmula que indicamos anteriormente: ([Quantidade]*[PreçoUnitário])*(1-[Desconto]). Para criar a versão 3.0 da nossa View, digite o seguinte comando, na linha abaixo do AS:

SELECT Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário, ([Quantidade]*[PreçoUnitário])*(1-[Desconto]) AS TotalDoPedido

FROM Pedidos INNER JOIN [Detalhes do Pedido] ON Pedidos.NúmeroDoPedido = [Detalhes do Pedido].NúmeroDoPedido

A novidade, nesta versão da View, é o seguinte trecho do comando:

([Quantidade]*[PreçoUnitário])*(1-[Desconto]) AS TotalDoPedido

Este é o trecho onde criamos um campo calculado. O campo calculado vem junto com os demais campos. Observe que logo após o campo NomeDoDestinatário, coloco a fórmula de cálculo e depois da fórmula, uma cláusula AS para dar nome ao campo calculado. Neste exemplo, a fórmula é ([Quantidade]*[PreçoUnitário])*(1-[Desconto]) e o nome atribuído a esta coluna é TotalDoPedido.

21.       Pressione Ctrl+S para salvar a view e feche a janela de criação da View.

22.       Abra a janela de execução de comandos T-SQL e execute o seguinte comando:

SELECT * FROM [Con - Total por Pedido]

23.       Você deverá obter os resultados indicados na Figura 9.22:

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.22 Resultados da versão 3.0 da nossa View.

Observe que agora, com a versão 3.0 da nossa View, já estamos bem mais próximos do resultado final proposto. Ainda não estamos lá, mas falta muito pouco. Observe que já é exibido o total de cada item de cada pedido. Porém, ainda não está sendo totalizado por pedido. Por exemplo, o pedido 10248 tem três itens. Estão sendo exibidas três linhas para este pedido, uma para cada item. Em cada linha é exibido o total do item. Nos temos que fazer com que o SQL Server 2005 “junte todas os itens do mesmo pedido em uma única linha e, ao invés do total por item, some os valores dos itens do pedido, exibindo o total do pedido”. Conforme você verá, logo a seguir, é bastante simples fazer esta totalização.

Vamos continuar modificando a nossa View. Agora iremos criar a versão 4.0, que será a nossa versão final. Então mãos a obra.

24.       Localize a view Con - Total por Pedido, clique com o botão direito do mouse na View e, no menu de opções que é exibido, clique em Delete. Será exibida uma janela solicitando confirmação. Clique em OK para confirmar a exclusão da View. É preciso excluir a View e criá-la novamente, pois vamos fazer alterações.

25.       Clique com o botão direito do mouse na opção Views, abaixo de NwindAccess e,  no menu de opções que é exibido, clique em New View...

26.       Será exibida a janela para criação de uma nova View. No campo View name, digite Con - Total por Pedido

27.       Na parte do comando da View, após o AS é que vem o comando T-SQL para a criação da View. Vamos criar um comando que além de retornar os resultados da versão 3.0 da nossa view, faça também a totalização por pedido. Para criar a versão 4.0 da nossa View, digite o seguinte comando, na linha abaixo do AS:

SELECT Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário, Sum(([Quantidade]*[PreçoUnitário])*(1-[Desconto])) AS TotalDoPedido

FROM Pedidos INNER JOIN [Detalhes do Pedido] ON Pedidos.NúmeroDoPedido = [Detalhes do Pedido].NúmeroDoPedido

GROUP BY Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário;

Aqui temos diversas novidades a serem consideradas. Vamos comentá-las por parte. A primeira novidade é o uso da função SUM, para fazer a soma do campo Calculado TotalDoPedido:

Sum(([Quantidade]*[PreçoUnitário])*(1-[Desconto])) AS TotalDoPedido

Porém temos que orientar o SQL Server em como agrupar as linhas. Pois é dentro de cada agrupamento que a soma será efetuada. No nosso exemplo, vamos agrupar por pedidos. Dentro de um mesmo pedido, os campos NúmeroDoPedido, DataDoPedido e NomeDoDestinatário são iguais, para todos os itens de um pedido. Com isso, vamos pedir que estes itens sejam agrupados em um único e que os diferentes valores de cada item, sejam somados. Ou seja, o resultado prático é obter cada pedido listado uma única vez com o respectivo total. Para fazer este agrupamento, utilizamos a cláusula GROUP BY, conforme trecho de código a seguir:

GROUP BY Pedidos.NúmeroDoPedido, Pedidos.DataDoPedido, Pedidos.NomeDoDestinatário

Observe que utilizamos a cláusula GROUP BY nos três campos da tabela Pedidos, ou seja, justamente os campos que são iguais, para todos os itens de um mesmo pedido.

28.       Pressione Ctrl+S para salvar a view e feche a janela de criação da View.

29.       Abra a janela de execução de comandos T-SQL e execute o seguinte comando:

SELECT * FROM [Con - Total por Pedido] ORDER BY NúmeroDoPedido

30.       Você deverá obter os resultados indicados na Figura 9.23:

Curso Completo de SQL Server 2005 - Júlio Battisti
Figura 9.23 Resultados da versão 4.0 da nossa View – versão final.

Observe que agora, cada pedido aparece uma única vez e, na última coluna é exibido o total para o pedido, total este que nada mais é do que a soma dos itens do pedido.

31.       Muito bem, com isso concluímos o exercício proposto, ou seja, criar uma View que retornasse o total por pedido. Vimos que, basicamente, criar um View, é saber criar o comando T-SQL para retornar o resultado desejado. Por isso que venho insistindo, ao longo de todo o Capítulo, na importância de conhecer os comandos T-SQL. Pois tudo no SQL Server 2005 se resume a comandos T-SQL. Mesmo quando você está usando a interface gráfica, é apenas uma interface mais amigável, que o esta ajudando a criar um comando T-SQL o qual será executado. Com comandos T-SQL você cria e modifica bancos de dados, cria tabelas, views, triggers, stored procedures, configura a segurança e a replicação, enfim, faz tudo no SQL Server 2005.

A seguir você aprenderá um pouco mais sobre as views chamadas de Referência Cruzada, as quais usam as cláusulas TRANSFORM e PIVOT. Você verá que é possível fazer pequenas maravilhas com estas cláusulas.

Muito bem, para não perdermos o ritmo, vamos a mais alguns exemplos.

« Lição anterior Curso Completo de SQL Server 2005 - Júlio Battisti Δ Página principal Curso Completo de SQL Server 2005 - Júlio Battisti ¤ Capítulos Curso Completo de SQL Server 2005 - Júlio Battisti Próxima lição »

você conhece a universidade do access?

Universidade do Access - Curso Completo de Access
com tudo para você dominar o Access - do Básico ao
Avançado - até a Criação de Sistemas Profissionais
Completos - Passo a Passo - Tela a Tela

Capa da Universidade do Access

Aplica-se ao Access 2019, 2016, 2013 e 2010!

13 Cursos - 574 Vídeo-Aulas - 63:32 horas

Para todos os detalhes, acesse:

https://juliobattisti.com.br/universidade-do-access.asp

Quer receber novidades e e-books gratuitos?

 
 

Contato: Telefone: (51) 3717-3796 | E-mail: webmaster@juliobattisti.com.br | Whatsapp: (51) 99627-3434

Júlio Battisti Livros e Cursos Ltda | CNPJ: 08.916.484/0001-25 | Rua Vereador Ivo Cláudio Weigel, 537 - Universitário, Santa Cruz do Sul/RS, CEP: 96816-208

Todos os direitos reservados, Júlio Battisti 2001-2025 ®

LIVRO: MACROS E PROGRAMAÇÃO VBA NO EXCEL 2016 - CURSO COMPLETO E PRÁTICO

DOMINE A PROGRAMAÇÃO VBA NO EXCEL - 878 PÁGINAS - CLIQUE AQUI