CONSULTAS AVANÇADAS COM SQL E SEU SUPORTE PARA RELATÓRIOS

Centro Universitário - IBMR

Nome:  Alexandre Queiroz da Silva

Mat:   2022100647

Curso: Técnologo em Redes de Computadores

Cadeira: Banco de Dados

Pratique - 4 - Banco de Dados

CONSULTAS AVANÇADAS COM SQL E SEU SUPORTE PARA RELATÓRIOS

Segundo Laudon (2015), Sistemas de Informação (SI) possuem componentes que tratam de variadas maneiras a informação para o apoio à tomada de decisão em uma empresa. Um desses componentes, o banco de dados, serve para manipulações de nível operacional, o registro de uma compra, por exemplo, como para níveis gerenciais e estratégicos, ao ser fonte para transformar dados em informação com os relatórios, por exemplo, qual o volume de vendas em determinado mês.

Compreender um modelo de dados e, a partir de consultas avançadas, extrair informações úteis é uma tarefa comum no suporte à geração de relatórios ou demandas específicas. E isso tem ganhado cada vez mais relevância nos dias atuais por conta das áreas de ciência de dados, aprendizagem de máquina e Big Data. Em uma análise breve, como, a essência da aprendizagem de máquina é extrair conhecimento ou aprender coisas a partir de dados e esses dados “nascem”, sendo que uma de suas fontes são os bancos de dados.

Imagine um cenário de uma pizzaria ou de uma loja on-line. Em um primeiro nível, pense em como estas consultas podem servir de base para relatórios, gerando o volume de movimentação no dia, os itens mais vendidos, os elementos com menor nível de estoque. Em um nível mais avançado, como essas consultas podem gerar dados que podem vir a ser utilizados por algoritmos que descobrem padrões de clientes, quais deles gostam mais de pizzas doces, quem compra mais produtos eletrônicos, dentre outras. E tudo isso para suporte às tomadas de decisões de marketing e de melhoria do negócio. Os dados e as consultas são insumos importantes neste processo.

Vamos Praticar

Imagine que você foi destacado para dar suporte às demandas por relatórios e outras informações para uma determinada diretoria. Além disso, a equipe de marketing está em um projeto o qual envolve aprendizagem de máquina, assim, para isso, requer, por vezes, a extração de alguns dados para suas tarefas de mapeamento de padrões sobre os clientes.

Em determinada semana, surgiram algumas demandas que você deve tratar, dentre elas emitir os seguintes dados:

a) Relatório contendo uma lista com todas as categorias de produtos e a quantidade de produtos em cada categoria. Isso será útil para a equipe de vendas tentar diversificar o catálogo, e ordenados por nome da categoria.

b) Relatório com o volume de compras (quantidade de compras e valor total de compras) organizados por mês. Isso servirá de base para a Diretoria Comercial verificar fatos nos meses de maior e de menor volume de vendas.

c) Relatório com todos os clientes com suas características de sexo, data de nascimento ou idade, bairro em que mora e o total de compras nos últimos dois meses. Para total de compras, considere quantidade de compras, valor e média de valor por compra. Essa consulta servirá de dados para um projeto de aprendizagem de máquina que visa mapear os padrões de consumidores da loja. Como esta demanda será frequente, prepare uma visão (view) para esse caso e analise as vantagens nessa produção de consultas sob demanda.

O modelo de dados do sistema está exposto a seguir:

[pic 1]

Além destes sugeridos, imagine outros 5 relatórios que podem ser emitidos a partir do modelo, discuta qual utilidade para o negócio e depois crie visões (views) para eles.

Enfim, é hora de dar suporte às demandas por informação a partir de suas consultas mais avançadas usando group by, order, joins e outros. Bom trabalho!

Respostas:

1 - Relatório contendo uma lista com todas as categorias de produtos e a quantidade de produtos em cada categoria.

SELECT

CTG.COD_CATEGORIA, PROD.DCR_PRODUTO, COUNT (PROD.COD_PRODUTO) AS QTDE DE PRODUTOS

FROM

CATEGORIA AS CTG RIGHT OUTER JOIN PRODUTO AS PROD

ON

PROD.COD_CATEGORIA = CTG.COD_CATEGORIA

 

2 - Relatório com o volume de compras (quantidade de compras e valor total de compras) organizados por mês.

 

SELECT

DATA_COMPRA, COUNT (NUM_COMPRA) AS VENDAS, SUM (VLR_COMPRA) AS FATURAMENTO

FROM

COMPRA

GROUP BY

DATA_COMPRA ASC

 

3 - Relatório com todos os clientes com suas características de sexo, data de nascimento ou idade, bairro em que mora e o total de compras nos últimos dois meses. Para o total de compras, considere a quantidade de compras, o valor e a média de valor por compra. Prepare uma view para esse caso e analise as vantagens nessa produção de consulta sob demanda.

 CREATE VIEW ‘CLIENTE_COMPRA_VW’ AS

SELECT

                C.NOME_CLIENTE, C.SEXO, C.DATA_NASC, C.BAIRRO, COUNT (CP.NUM_COMPRA) AS TOTAL DE COMPRAS, SUM (CP.VLR_COMPRA) AS TOTAL GASTO, AVG (CP.VLR_COMPRA) AS GASTO MEDIO, CP.DATA_COMPRA, CID.DCR_CIDADE

FROM

                CLIENTE AS C LEFT OUTER JOIN COMPRA AS CP

ON

                C.COD_CLIENTE = CP.COD_CLIENTE

                INNER JOIN CIDADE AS CID

ON

                C.COD_CIDADE = CID.COD_CIDADE

WHERE

                CAST (CP.DATA_COMPRA AS DATA) BETWEEN CAST (DATEADD (DAY, -60, GETDATE()) AS DATA) AND CAST (GETDATE() AS DATA)