Projeto e Administração de Banco de Dados

Funções de Agregação, Subconsultas e Views

Objetivo
Nesta unidade você vai estudar sobre pesquisas com funções de agregação de dados,
sobre a criação de subconsultas e o uso de views.

Introdução
Nosso último tema estudaremos tópico que permitem a realização de relatórios e consultas
mais avançadas. Serão três conteúdos distintos que resolverão alguns problemas pontuais.

Funções de Agregação

As funções de agregação são funções do SQL que nos ajudam a fazer somas, contagens
e outros cálculos em tabelas. É especialmente útil para, por exemplo, calcular o total de uma venda
(ou seja, a soma do valor de cada item), ou o a quantidade de itens presentes nesta venda (neste
caso, a quantidade de linhas desta consulta). As principais funções de agregação que existem e
estudaremos aqui são:

 COUNT(coluna) – Contagem de linhas em que esta coluna aparece preenchida

 SUM(coluna) – Somatório dos valores nesta coluna
 AVG(coluna) – Valor médio desta colina
 MAX(coluna) – Valor máximo que esta coluna apresenta
 MIN(coluna) – Valor mínimo que esta coluna apresenta

Estes códigos serão usados logo após o comando SELECT e antes do FROM. Observe
agora exemplo abaixo. Você pode testar em seu computador com a tabela de clientes do banco
DB_LOJA mas, para exemplificar, inventamos algumas colunas. Veja:

TB_CLIENTES
CLI_CODIGO CLI_NOME CLI_IDADE CLI_SALÁRIO CLI_SETOR
1 ARI 30 788,00 RH
2 JOSÉ 45 3800,00 RH
3 MARIA 3200,00 RH
4 CARLOS 59 3300,00 RECEPÇÃO

SELECT COUNT(CLI_NOME) FROM TB_CLIENTES

-- O resultado a ser exibido, neste caso, é 4!

Execute agora este comando no seu banco (BD_LOJA)! Perceba que será exibido o total

2
Projeto e Manutenção de Banco de Dados
2
de linhas da sua tabela. A coluna CLI_NOME foi citada apenas porque é necessário especificar
alguma coluna. Mas poderíamos usar apenas um asterisco (*) para indicar que seja contada a
linha inteira. Desta forma:

SELECT COUNT(*) FROM TB_CLIENTES

Precisamos ainda fazer um ajuste. Usando desta forma, a coluna não tem nome! E toda
coluna precisa ser nomeada. Faremos isto usando um alias (apelido). Veja o exemplo para
renomear esta coluna de contagem para "quantidade":

SELECT COUNT(*) as quantidade FROM TB_CLIENTES

Pronto! Agora a nossa coluna será devidamente nomeada. Execute e veja o resultado.
Outra coisa interessante a se observar é que Maria não revelou a sua idade. Caso você deseje
contar apenas os itens preenchidos (não nulos) de uma tabela, basta especificar o nome desta
coluna dentro da função. Desta forma:

SELECT COUNT(CLI_IDADE) as idades FROM TB_CLIENTES

-- O resultado a ser exibido, neste caso, é 3! Pois apenas 3 linhas possuem
-- idade preenchida

As outras funções de agregação, listadas anteriormente, têm uso semelhante. Veja os

exemplos:

SELECT AVG(FUN_SALARIO) AS TOTAL FROM TB_FUNCIONARIOS

-- Informará a média salarial dos funcionários:

SELECT SUM(ENT_PRECO) AS TOTAL FROM TB_ENTREGAS

-- Informará o valor total de todas as entregas

GROUP BY

Quando estivermos usando alguma função de agregação, como o COUNT(*) por exemplo,
esta deve estar listada "sozinha" ENTRE O SELECT e o FROM. Ou seja, não podemos trazer
outras colunas. Caso você deseje trazer outras colunas a mais (sem função de agregação), será
necessário inseri-las também na clausula GROUP BY. Mas o que é esta clausula? O GROUP BY
fica localizado próximo ao ORDER BY (se existir). Ele serve para agrupar itens semelhantes nos
seus cálculos (de soma, média, etc.). Seria como dizer que a cada mudança no valor desta coluna
deve ser feita uma nova soma. Veja os exemplos:

3
Projeto e Manutenção de Banco de Dados
3
 SELECT SUM(EMP_SALARIO) AS TOTALSALARIO, EMP_SETOR
FROM TB_EMPREGADOS
GROUP BY EMP_SETOR
-- Somatório dos salários de uma empresa por Setor

SELECT MAX(VEN_VALOR) AS MAXIMO, VEN_EMP_CODIGO

FROM TB_VENDAS
GROUP BY VEM_EMP_CODIGO
-- O máximo já vendido por cada um dos vendedores

Percebeu o macete? Todos os itens que forem colocados após o SELECT também devem
ser citados no GROUP BY.

HAVING

Para terminar, temos que estudar o filtro de registros usando agregação. Quando
aprendemos a usar o WHERE, descobrimos que ele filtra os resultados que foram buscados de
um banco de dados. Ele cria uma condicional que simplesmente não traz estas linhas na consulta!
Veja o exemplo abaixo:

SELECT SUM(EMP_SALARIO) AS TOTALSALARIO, EMP_SETOR

FROM TB_EMPREGADOS
WHERE EMP_DATANASC > '1990-01-01'
GROUP BY EMP_SETOR
-- Somatório dos salários de uma empresa por Setor, apenas dos funcionários
-- com data de nascimento posterior a 01/01/1990

Neste caso, o WHERE serviu para restringir as linhas usadas no somatório. Ou seja, se um
determinado setor só tem pessoas que nasceram antes de 1990, ele não vai ser nem mencionado
nesta consulta! Isto porque o WHERE vai excluir estes dados da consulta! Bem, isto você já sabia,
não é novidade! Mas imagine que você queira fazer outro tipo de restrição. Queira restringir para
mostrar os setores que tenham o total de salário superior a 20.000 reais. Como fazer isto? Nossa
tendência é querer adicionar este critério no WHERE! Desta forma:

WHERE SUM(EMP_SALARIO) > 20000

-- Isto não funcionará!

Pois bem, infelizmente o WHERE não consegue entender funções de agregação! Caso
deseje fazer a restrição proposta, é necessário usar um comando diferente, feito exclusivamente
para ser usado com funções de agregação. Trata-se do HAVING, que é escrito logo após o
4
Projeto e Manutenção de Banco de Dados
4
GROUP BY. Veja como ficará:

SELECT SUM(EMP_SALARIO) AS TOTALSALARIO, EMP_SETOR

FROM TB_EMPREGADOS
WHERE EMP_DATANASC > '1990-01-01'
GROUP BY EMP_SETOR
HAVING SUM(EMP_SALARIO) > 20000
-- Somatório dos salários de uma empresa por Setor, apenas dos funcionários
-- com data de nascimento posterior a 01/01/1990. Serão exibidos apenas os
setores que tenham a soma (total de salários) maior que 20.000

Veja outros exemplos de uso do HAVING:

SELECT COUNT(*) AS QTD, TCL_CLI_CODIGO

FROM TB_TELEFONE_CLIENTES
GROUP BY TCL_CLI_CODIGO
HAVING COUNT(*) >= 1
-- Clientes com mais de um telefone

SELECT SUM(IVE_PRECO_UN) AS Total, IVE_VEN_CODIGO

FROM TB_ITENS_VENDAS
GROUP BY IVE_VEN_CODIGO
HAVING SUM(IVE_PRECO_UN) >= 100
-- Vendas que custaram mais de 100 reais

SELECT COUNT(*) AS QTD, CLI_NOME

FROM TB_TELEFONE_CLIENTES
JOIN TB_CLIENTES ON TCL_CLI_CODIGO = CLI_CODIGO
WHERE CLI_BAI_CODIGO = 1
GROUP BY CLI_NOME
HAVING COUNT(*) >= 1
-- Quantidade de telefones dos clientes com mais de um telefone,
-- exibindo o nome do cliente, mas somente para clientes do bairro 1

5
Projeto e Manutenção de Banco de Dados
5
 SELECT MAX(VEN_VALOR) AS MAXIMO, VEM_EMP_CODIGO
FROM TB_VENDAS
WHERE VEN_BAI_CODIGO IN (3,5,8)
GROUP BY VEM_EMP_CODIGO
-- O máximo já vendido por cada um dos vendedores,
-- somente dos vendedores que moram nos bairros 3, 5 ou 8

SELECT MAX(VEN_VALOR) AS MAXIMO, VEM_EMP_CODIGO

FROM TB_VENDAS
GROUP BY VEM_EMP_CODIGO
HAVING MAX(VEN_VALOR) >1000

-- O máximo já vendido por cada um dos vendedores,

-- somente dos vendedores que já venderam mais de 1000 reais

ATIVIDADES

Interessante, não? Agora tente resolver estes problemas usando o banco de dados
fornecido na pasta da Aula 3 (DB_LOJA), e tente criar as seguintes consultas:

1) Nome do departamento e a quantidade de produtos associados a cada departamento.

2) Nome do cliente e a quantidade de telefones cadastrados para cada cliente, apenas
para clientes com mais de um telefone.
3) Quantidade de unidades vendidas e total arrecadado por cada marca.
4) Nome do fornecedor e o total gasto em compras, pela loja, com cada fornecedor em
2012.
5) Descrição do produto, total de unidades vendidas e total arrecadado por produto em
junho de 2012.
6) Nome do cliente, quantidade de produtos vendidos e total arrecadado por cliente em no
1º semestre de 2012, apenas para os clientes que renderam mais de R$ 100,00 em
vendas.
7) Descrição do produto, total de unidades compradas e total gasto por produto em março
de 2012, apenas para produtos do departamento de calçados.
8) Descrição do produto, departamento do produto, total de unidades vendidas e total
arrecadado por produto no primeiro semestre 2012, apenas para produtos com os
quais se arrecadou mais de R$ 500,00.

Caso não encontre alguma coluna ou dados correspondentes a sua pesquisa, insira-os
(modificando a tabela ou inserindo linhas que atendam o critério) e tente novamente!

6
Projeto e Manutenção de Banco de Dados
6
Subconsultas

Subconsulta é nada mais que alinhar uma consulta dentro de outra consulta, ou mesmo
inserir uma consulta dentro de uma instrução INSERT, UPDATE ou DELETE. Testaremos a seguir
o poder desta técnica, e sua utilidade.
Em nosso primeiro teste, vamos fazer uma consulta simples, para descobrir quais são os 3
itens mais caros da minha loja. Para isto, podemos usar a seguinte consulta MySQL:

SELECT PRO_CODIGO FROM TB_PRODUTOS

ORDER BY PRO_PRECO DESC LIMIT 3

Este código, basicamente vai exibir o código dos 3 produtos mais caros (uma vez que
ordenamos a listagem em ordem decrescente de preço e em seguida limitamos a 3 resultados).
Esta consulta está terminada. Mas vamos agora pensar em outra situação. Imagine que deseje
fazer uma pesquisa na tabela de itens das vendas, listando todas as vendas dos itens 1, 2, 3, 4.
De acordo com o que aprendemos anteriormente, a resposta seria esta:

SELECT * FROM TB_ITENSVENDAS

WHERE ITV_PRO_CODIGO IN (1,2,3,4)

Perfeito! Este código vai trazer os registros de todas as vezes que qualquer um dos itens
nesta lista (no caso: 1, 2, 3 ou 4) foi inserido em alguma venda! Mas agora vamos juntar as duas
coisas. Quero uma consulta que me retorne os registros de venda dos 3 itens mais caros! Isso
poderia ser útil na loja para saber se algum produto caro está sendo pouco vendido, por exemplo!
Para isto, eu terei que substituir a minha lista manual, que criamos com o código
ITV_PRO_CODIGO IN (1,2,3,4) por uma consulta! O resultado seria:

SELECT * FROM TB_ITENSVENDAS

WHERE ITV_PRO_CODIGO IN (SELECT PRO_CODIGO FROM TB_PRODUTOS
ORDER BY PRO_PRECO DESC LIMIT 3)

Mas esta não é a única forma de se usar uma subconsulta, ou subquery. Veja na
continuação alguns exemplos interessantes.

SELECT *
FROM TB_PRODUTOS
WHERE PRO_PRECO > (SELECT AVG(PRO_PRECO) FROM TB_PRODUTOS)
-- Produtos que tenham o preço maior que a média

7
Projeto e Manutenção de Banco de Dados
7
 SELECT EMP_NOME
FROM TB_EMPREGADOS
WHERE EMP_SEXO = 'F' AND EMP_IDADE > ANY
(SELECT EMP_IDADE FROM TB_EMPREGADOS WHERE EMP_SEXO
='M')
-- Retorna, em uma tabela de empregados, todas as mulheres cuja idade
seja maior que a de qualquer homem
-- Este mesmo código poderia ser também feito da seguinte forma:

SELECT EMP_NOME
FROM TB_EMPREGADOS
WHERE EMP_SEXO = 'F' AND EMP_IDADE >
(SELECT MAX(EMP_IDADE) FROM TB_EMPREGADOS WHERE
EMP_SEXO ='M')

VIEWS
Imagine que você criou um SELECT com várias tabelas usando JOIN, filtrando os resultados
com WHERE, ordenando os resultados com ORDER BY e usando vários outros recursos. Agora,
imagine que deseje executar esta mesma consulta várias vezes em várias partes do seu software.
Será necessário copiar e colar várias vezes o comando! Além disto, o processador irá converter a
sua consulta em linguagem de máquina toda vez que tentar executar! Não esqueça também que
quando for fazer uma manutenção na sua pesquisa, deverá alterar todas as consultas
semelhantes! É realmente um problema.
Para resolver estes problemas, os SGBD's oferecem um recurso para trabalhar com
visualizações ou views, como são mais conhecidas. O objetivo de uma VIEW é armazenar uma
consulta para execução posterior. Esta consulta já fica "compilada", ou seja, preparada para ser
executada (o que reduz o tempo para execução da tarefa) além de não ocupar quase nada de
espaço em disco! Uma vez criada, a view será tratada como uma tabela normal. Será possível
fazer consultas em cima dela! Vejamos os exemplos abaixo.
Primeiramente, vamos criar ima view. O código SQL é bem simples:

CREATE VIEW VW_CLIENTES_ZONA_NORTE

AS
SELECT * FROM TB_CLIENTES
WHERE CLI_BAI_CODIGO IN (5,7,9,11)

Este código criou uma view chamada VW_CLIENTES_ZONA_NORTE, que armazena uma
consulta que basicamente exibe os clientes da zona norte (neste caso, que moram nos bairros 5,

8
Projeto e Manutenção de Banco de Dados
8
7, 9 ou 11). Lembre-se que o prefixo VW_ não é obrigatório, mas apenas nos ajuda a identificar
mais facilmente que se trata de uma VIEW.
Você pode usar qualquer recurso do SELECT em sua view. Por exemplo: subconsultas,
funções de agregação, uniões, JOIN, GROUP BY e o que mais desejar! Basta usar esta sintaxe
básica que a view será criada. Mas agora... Como usar a VIEW? Usar uma view é ainda mais fácil
do que criar! Use-a como se fosse uma tabela qualquer. Veja o código abaixo:

SELECT * FROM VW_CLIENTES_ZONA_NORTE

Viu como é fácil? Teste você mesmo, criando agora VIEWS para as atividades anteriores de
select. O uso de views pode facilitar bastante nosso trabalho. Uma vez criada, podemos fazer
consultas normais nesta view, como se fosse uma tabela comum! Veja o exemplo:

SELECT * FROM VW_CLIENTES_ZONA_NORTE

WHERE CLI_IDADE > 18

No exemplo acima, fizemos uma restrição. A pesquisa acima exibiria apenas os clientes que
tem mais de 18 anos e que morem na zona norte (uma vez que a VW_CLIENTES_ZONA_NORTE
exibe apenas os clientes que moram nos bairros 5,7, 9 ou 11). Veja outros exemplos de views:

CREATE VIEW VW_FUNC

AS
SELECT FUN_CODIGO, FUN_NOME, FUN_IDADE
FROM TB_FUNCIONARIOS WHERE FUN_SETOR = 1

Neste exemplo, criamos uma view para os funcionários de uma empresa. Esta view só vai
retornar 3 colunas (código, nome e idade). A coluna de setor, por exemplo, não será exibida,
apesar do filtro estar sendo aplicado! Desta forma, a consulta abaixo retornará um erro:

SELECT FUN_SETOR FROM VW_FUNC -- Essa consulta retorna um erro

Este erro ocorre porque nossa view está restrita as três colunas que foram definidas na
criação. A coluna de setor ou qualquer outra coluna existente na tabela original não poderá ser
usada! Isto pode ser uma vantagem se desejar ocultar alguma informação (por exemplo, o salário)
das pessoas que usam o banco.

9
Projeto e Manutenção de Banco de Dados
9
Bibliografia
[1] Material didático do professor Ari Oliveira. <http://www.arioliveira.com>. Acessado em julho de 2015.

10
Projeto e Manutenção de Banco de Dados
10