aula 9 funções agregadas e cálculos

1
BANCO DE DADOS GERENCIAL
AULA 9
FUNÇÕES AGREGADAS E CÁLCULOS
Funções Agregadas
São funções aquelas que tomam uma coleção (um conjunto ou subconjunto) de
valores como entrada, retornando um único valor. O número de funções agregadas na
linguagem SQL é vasta. Por isso, veremos alguns exemplos e abaixo, estamos passando
um link para que acessem mais funções.
Importante: dependendo do SGBD, poderá haver diferentes funções que executem a
mesma coisa entre eles, e até mesmo a sintaxe de funções similares poderão ser diferentes.
Por isso, é importante que consultem o manual ou o tutorial do SGBD que resolverem
utilizar.
Para ver mais funções, veja nos links:
Manual PostgreSQL
Funções matemáticas: http://pgdocptbr.sourceforge.net/pg80/functions-math.html
Funções caracteres: http://pgdocptbr.sourceforge.net/pg80/functions-string.html
Manual Mysql
http://dev.mysql.com/doc/refman/4.1/pt/functions.html
2
BANCO DE DADOS GERENCIAL
Para auxiliar nos exemplos abaixo, vamos criar uma tabela.
VENDEDOR
NOME DO CAMPO
TIPO DO CAMPO
TAMANHO
Regras
Codigo
integer
Nome
Varchar
40
NOT NULL
Float
8,2
NOT NULL
Comissao
PK
Inserir alguns registros
Funções do SQL
Os exemplos de funções abaixo apresentados fazem parte da linguagem SQL.
Funções são comandos prontos para executar determinada tarefa.
COUNT()
Contagem do número de registros agregados de uma determinada tabela ou junção
entre tabelas.
Exemplo:
SELECT count(*) FROM vendedor
SUM()
Somatória do conjunto de valores do campo passado como parâmetro.
Exemplo:
3
BANCO DE DADOS GERENCIAL
SELECT sum(comissao) FROM vendedor
MIN()
Retorna o menor valor do conjunto de valores do campo passado como parâmetro.
Exemplo:
SELECT min(comissao) FROM vendedor
MAX()
Retorna o maior valor do conjunto de valores do campo passado como parâmetro.
Exemplo:
SELECT min(comissao) FROM vendedor
FUNÇÕES PARA STRING
LOWER( string )
A função LOWER converte string maiúsculas para minúsculas.
Exemplo:
SELECT lower(nome) FROM vendedor
UPPER( string )
A função UPPER converte string de minúsculas para maiúsculas.
Exemplo:
SELECT upper( nome) FROM vendedor
4
BANCO DE DADOS GERENCIAL
SUBSTRING( string, inteiro, inteiro )
A função SUBSTRING obtém parte de uma string completa.
Exemplo:
SELECT substring(nome,4,6) FROM vendedor
EXERCÍCIOS
-- O BANCO DE DADOS DEVERÁ CONTROLAR A VENDA DE CADA VENDEDOR
-- TABELA CADASTRO DE VENDEDORES
create table vendedor (
codvendedor integer primary key,
nome varchar(40) not null,
percomissao integer not null
)
-- TABELA PARA CONTROLAR A VENDA DE CADA VENDEDOR
create table vendas (
codvenda integer primary key,
datavenda date not null,
valor numeric(10,2) not null,
codvendedor integer references vendedor(codvendedor)
)
5
BANCO DE DADOS GERENCIAL
-- INCLUIR DADOS DOS VENDEDORES
INSERT INTO vendedor VALUES (1, 'Pedro', 8);
INSERT INTO vendedor VALUES (2, 'Marcos', 5);
INSERT INTO vendedor VALUES (3, 'Jair', 9);
-- INCLUIR DADOS DAS VENDAS FEITAS PELOS VENDEDORES
INSERT INTO vendas VALUES (1, '10/05/2016', 300.00, 1);
INSERT INTO vendas VALUES (2, '09/05/2016', 1000.00, 3);
INSERT INTO vendas VALUES (3, '14/05/2016', 140.00, 1);
INSERT INTO vendas VALUES (4, '05/05/2016', 1200.00, 2);
INSERT INTO vendas VALUES (5, '04/05/2016', 1000.00, 3);
INSERT INTO vendas VALUES (6, '10/05/2016', 1000.00, 2);
-- SELECIONAR TODOS OS VENDEDORES E SUAS RESPECTIVAS VENDAS
SELECT
*
FROM
vendedor
INNER
JOIN
vendas
vendedor.codvendedor=vendas.codvendedor
ORDER BY nome
-- CÁLCULOS ARIMÉTICOS
-- CALCULAR A COMISSÃO DO VENDEDOR
SELECT nome, valor, percomissao, (valor * percomissao)/100
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
ON
BANCO DE DADOS GERENCIAL
6
-- CALCULAR VENDA - COMISSÃO
SELECT nome, valor, percomissao, (valor * percomissao)/100 as comissao, valor - (valor *
percomissao)/100 as sobrou
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
-- FUNÇÕES MATEMÁTICAS
-- CEIL() - ARREDONDA O VALOR PARA O PRÓXIMO NÚMERO INTEIRO
SELECT nome, valor, percomissao, ceil((valor * percomissao)/100), ceil(valor - (valor *
percomissao)/100)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
-- TRUNC() - ARREDONDA PARA (N) CASAS DECIMAIS
SELECT nome, valor, percomissao, trunc((valor * percomissao)/100,2), trunc(valor - (valor *
percomissao)/100,2)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
-- FUNÇÃO COUNT(*) - CONTAR A QUANTIDADE DE VENDAS DE CADA VENDEDOR
SELECT nome, count(*)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
GROUP BY nome
-- FUNÇÃO SUM() - SOMAR A QUANTIA VENDIDA DE CADA VENDEDOR
SELECT nome, sum(valor)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
GROUP BY nome
BANCO DE DADOS GERENCIAL
-- FUNÇÃO MIN() - VERIFICAR O MENOR VALOR VENDIDO DE CADA VENDEDOR
SELECT nome, MIN(valor)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
GROUP BY nome
-- FUNÇÃO MAX() - VERIFICAR O MAIOR VALOR VENDIDO DE CADA VENDEDOR
SELECT nome, MAX(valor)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
GROUP BY nome
-- FUNÇÕES STRING
-- LOWER() - CONVERTER O NOME DO VENDEDOR PARA CAIXA BAIXA
SELECT lower(nome)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
ORDER BY nome
-- UPPER() - CONVERTER O NOME DO VENDEDOR PARA CAIXA ALTA
SELECT upper(nome)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
ORDER BY nome
-- SUBSTRING() - OBTÉM PARTE DE UMA STRING
SELECT substring(nome,2,4)
FROM vendedor INNER JOIN vendas ON vendedor.codvendedor=vendas.codvendedor
ORDER BY nome
7