Transcript

#02

Banco de Dados II

2015.2 Gustavo Sávio <[email protected]> 1

Funções da SQLAs funções de SQL existem para facilitar amanipulação dos dados armazenados na base dedados.

Elas são ferramentas projetadas para desempenharuma tarefa única e bem definida.

As funções de SQL são chamadas dentro de umaconsulta SQL pelo seu nome:

– Algumas recebem argumento, outras não.

– Todas elas retornam um valor.

2

#Funções

Categorias Gerais de FunçõesCategoria de Função Descrição

Matemáticas Funções usadas para realizar cálculos matemáticosespecíficos, como as funções trigonométricas e outras.

Cadeia de Caracteres Funções que realizam manipulação de cadeia decaracteres, tais como: localizar padrões dentro decadeias de caracteres, inserir caracteres, concatenarcadeias de caracteres, descobrir o comprimento,converter para maiúsculo e minúsculo.

Data/Hora Funções utilizadas para retornar informações sobredata/hora correntes, formatar data e hora como cadeiade caracteres, realizar cálculos baseado em horários,etc.

Agregação Funções utilizadas para fazer agrupamento.

Formatação Funções utilizadas para retornar informaçõesformatadas.

3

#Funções

SQL - Funções Matemáticas

Algumas funções Matemáticas (PostgreSQL):

– valor % valor: retorna o valor do resto da divisão entre dois números.

– Valor ^ valor: retorna o valor elevado a potência.

– valor!: retorna o valor fatorial do número.

– @(valor): retorna o valor absoluto.

4

#Funções Matemáticas

SQL - Funções Matemáticas

Algumas funções Matemáticas (PostgreSQL):

– |/ valor: retorna o valor da raiz quadrada.

– ||/ valor: retorna o valor da raiz cúbica.

– ABS(valor): retorna o valor absoluto (positivo).

– CEIL(valor): retorna o valor arredondado.

5

#Funções Matemáticas

SQL - Funções Matemáticas

Algumas funções Matemáticas (PostgreSQL):

– FLOOR(valor): retorna o valor truncado.

– MOD(x, y): retorna o valor do resto de divisão de x/y.

– PI(): retorna o valor de PI.

– POWER(x, y): retorna o valor de x elevado a y.

6

#Funções Matemáticas

SQL - Funções Matemáticas

Algumas funções Matemáticas (PostgreSQL):

– RANDOM(): retorna o valor aleatório entre 0 e 1.

– ROUND(valor): retorna o valor arredondado.

– SQRT(valor): retorna o valor da raiz quadrada.

– TRUNC(valor): retorna o valor truncado.

7

#Funções Matemáticas

SQL - Funções Matemáticas

Algumas funções Matemáticas (PostgreSQL):

– GREATEST(valor1, valor2,...valorN): retorna o maior valor de uma lista.

– LEAST(valor1, valor2,...valorN): retorna o menor valor de uma lista.

8

#Funções Matemáticas

SQL - Funções Matemáticas

• Exemplo:SELECT ABS(-1.0) AS ‘Absoluto’, FLOOR(20.25) AS

‘Inteiro’,ROUND(0.75,1) AS ‘Arred’, POWER(3,4) AS

‘Potencia’

• Resultado:

Absoluto Inteiro Arred Potencia

1.0 20 0.8 81

9

#Funções Matemáticas

SQL - Funções de Caracteres

Algumas funções de Cadeia de Caracteres(PostgreSQL):

– texto || texto: concatena duas strings.

– CHAR_LENGTH(texto): retorna a quantidade de uma cadeiade caracteres.

– UPPER(texto): converte uma cadeia para maiúsculo.

– LOWER(expressão): converte uma cadeia para minúsculo.

10

#Funções de cadeia de caracteres

SQL - Funções de Caracteres

Algumas funções de Cadeia de Caracteres(PostgreSQL):

– POSITION(procurar IN texto): retorna a posição docaractere.

– STRPOS(texto, procurar): retorna a posição docaractere.

– SUBSTRING(texto FROM inicio FOR fim): retorna asubstring do texto.

11

#Funções de cadeia de caracteres

SQL - Funções de Caracteres

Algumas funções de Cadeia de Caracteres(PostgreSQL):

– SUBSTR(procurar, inicio, fim): retorna a substring dotexto.

– TRANSLATE(texto, procurar, modificar): Substitui todosos caracteres.

– TRIM(texto): remove caracteres em branco da direita eesquerda.

12

#Funções de cadeia de caracteres

SQL - Funções de CaracteresAlgumas funções de Cadeia de Caracteres(PostgreSQL):

– MD5(texto): retorna o código hash usando o algoritmomd5.

– REPEAT(texto, quantidade): repete um texto N vezes.

– REPLACE(texto, procurar, modificar): sobrescreve umtrecho de uma string.

– SPLIT_PART(texto, delimitador, posicao): divide umastring de acordo com o delimitador retornando o campoespecificado pela posicao (começando de 1).

13

#Funções de cadeia de caracteres

SQL - Funções de Caracteres

Algumas funções de Cadeia de Caracteres(PostgreSQL):

– RTRIM(texto, remover): remove os caracteres dadireita. O padrão é espaço em branco.

– LTRIM(texto, remover): remove os caracteres daesquerda. O padrão é espaço em branco.

– INITCAP(texto): Capitaliza uma cadeia de caracteres.

– REVERSE(texto): retorna uma String reversa.

14

#Funções de cadeia de caracteres

SQL - Funções de CaracteresExemplo - Exibir o nome dos produtos em minúsculo,maiúsculo e os 10 primeiros caracteres do nome também emmaiúsculo.

SELECT LOWER(nome) AS nome_minusculo, • UPPER(nome) AS nome_maiusculo,• UPPER(SUBSTR(nome, 1, 10)) AS nome_reduzido

FROM Produto

Resultado:

nome_minusculo nome_maiusculo nome_reduzido

abajuralist ABAJURALIST ABAJURALI

computadorpositivo COMPUTADORPOSITIVO COMPUTADOR

iogurtenestle IOGURTENESTLE IOGURTENE

liquidificador LIQUIDIFICADOR LIQUIDIFIC

15

#Funções de cadeia de caracteres

SQL - Funções de Data/Hora

Algumas funções de Data/Hora (PostgreSQL):

– SELECT TIMESTAMP '2014-08-08' + INTERVAL '48 hours';

INTERVAL: utiliza as unidades SECOND, MINUTE, HOUR, DAY,WEEK, MONTH, YEAR, DECADE, CENTURY, MILLENNIUM eplurais dessas unidades.

16

#Funções de data/hora

SQL - Funções de Data/HoraAlgumas funções de Data/Hora (PostgreSQL):

– AGE: Diferença entre datas.Retorna um INTERVAL

Subtrai da data atual:SELECT AGE (TIMESTAMP'2013-08-08')

Subtrai o período entre duas datas:SELECT AGE(TIMESTAMP '2014-08-08', TIMESTAMP '2013-04-09')

17

#Funções de data/hora

SQL - Funções de Data/HoraAlgumas funções de Data/Hora (PostgreSQL):

– EXTRACT: Extrai informações de um DATE ou TIMESTAMP.Retorna um Double.

Extraindo dados de um TIMESTAMP:SELECT EXTRACT (YEAR FROM (TIMESTAMP'2013-08-08'))

SELECT EXTRACT (SECOND FROM (TIMESTAMP '2013-08-0801:06:12'));

SELECT EXTRACT (MONTH FROM (TIMESTAMP '2013-08-0801:06:12'));

18

#Funções de data/hora

SQL - Funções de Data/Hora

Algumas funções de Data/Hora (PostgreSQL):

Extrair a diferença de anos entre duas datas:

SELECT EXTRACT(YEAR FROM AGE('2014-08-08', TIMESTAMP'1987-02-23'));

19

#Funções de data/hora

SQL - Funções de Data/HoraAlgumas funções de Data/Hora (PostgreSQL):

Obter data e hora atual:

SELECT CURRENT_DATE;SELECT CURRENT_TIME;SELECT CURRENT_TIME(0);SELECT CURRENT_TIMESTAMP;SELECT CURRENT_TIMESTAMP(0);NOW() (TIMESTAMP WITH ZONE);

SELECT TIMESTAMP WITH TIME ZONE '2001-02-16 20:38:40-05' AT TIME ZONE 'MST';

Retornam DATE ou TIMESTAMP

20

#Funções de data/hora

SQL - Funções de Data/Hora

Algumas funções de Data/Hora (PostgreSQL):

– DATE_PART: Extrai informações de um DATE ouTIMESTAMP.

Retorna uma String.

Obter parte de um Data/Hora:

SELECT DATE_PART('DAY', TIMESTAMP '2014-08-10 09:59:00') AS dia, DATE_PART('MONTH', TIMESTAMP '2014-08-10 09:59:00') AS mes, DATE_PART('YEAR', TIMESTAMP '2014-08-10 09:59:00') AS ano

21

#Funções de data/hora

SQL - Funções de AgregaçãoAlgumas funções de Agregação (PostgreSQL):

– COUNT(*): conta a quantidade de registros.

– COUNT(DISTINCT expressão): conta a quantidade deregistros filtrando pela expressão.

– SUM ( ): soma os valores de uma coluna de dadosnuméricos.

– AVG ( ): calcula a média de uma coluna de dadosnuméricos.

– MAX ( ): determina o maior valor de uma coluna.

– MIN ( ): determina o menor valor de uma coluna.

22

#Funções de Agregação

SQL - Funções de AgregaçãoAlgumas funções de formatação (PostgreSQL):

– TO_CHAR(tipo_de_dado, text) – Converte um tipo de dadoem uma string seguindo um padrão.

Tipos suportados: TIMESTAMP, INTERVAL, INT, DOUBLE,NUMERIC, TEXT.

to_char(current_timestamp, 'HH12:MI:SS')to_char(interval '15h 2m 12s', 'HH24:MI:SS')to_char(125, '999')to_char(125.8::real, '999D9')to_date('05 Dec 2000', 'DD Mon YYYY')

23

#Funções de Formatação

SQL - Funções de Agregação

Casting (PostgreSQL):

– CAST(expressão AS tipo) – Efetua um CAST entre tiposde dados.

– Expressão::tipo – Efetua um cast entre tipos dedados

SELECT CAST('10 08 2014' AS DATE);SELECT '10 08 2014’::DATE;

24

#Funções de Casting

SQL - Funções de Agregação1 – Agrupar a quantidade de pedidos de um determinadocliente (tabela pedido) considerando que só devem serretornadas a tuplas que possuam clientes com mais de 2pedidos.

Resultado esperado:

cliente | quantidade_pedidos---------+--------------------

1 | 73 | 34 | 46 | 7

25

#Exercícios

SQL - Funções de Agregação

2 – Contar quantos clientes já fizeram pedidos.

Resultado esperado:

quantidade_clientes---------------------

6

26

#Exercícios

SQL - Funções de Agregação3 – Obter as seguintes informações da loja: Um relatório contendo asinformações: o último nome do cliente, o nome do vendedor que o atendeue a soma de todos os produtos que o mesmo vendeu para o cliente.Considere os descontos.

Resultado esperado:

nome | vendedor | preco----------------+--------------------+---------Representacoes | Francisco da Silva | 22.45Representacoes | Luiza Costa | 92.00Representacoes | Maria de Souza | 162.40Correia | Joao da Silva | 395.60Correia | Luiza Costa | 2845.00Comercio | Joao da Silva | 1374.90Maia | Luiza Costa | 955.00Maia | Maria de Souza | 15.00Andrade | Joao da Silva | 2890.60Andrade | Luiza Costa | 663.35Andrade | Maria de Souza | 6.90Ferreira | Francisco da Silva | 3.70Ferreira | Luiza Costa | 628.60Ferreira | Maria de Souza | 1295.21

27

#Exercícios

SQL - Funções de Agregação4 – Obter o valor médio de produtos vendidos de cadafuncionário. Considere os descontos.

Resultado esperado:

nome | media_venda--------------------+-------------Luiza Costa | 863,99Joao da Silva | 665,87Maria de Souza | 123,29Francisco da Silva | 6,54

2014.2 Gustavo Sávio <[email protected]> 28

#Exercícios

SQL - Funções de Agregação1 – SELECT CLIENTE, COUNT(cliente) AS QUANTIDADE_PEDIDOSFROM PEDIDO GROUP BY cliente HAVING COUNT(cliente) > 2ORDER BY CLIENTE

2 - SELECT COUNT(DISTINCT cliente) AS QUANTIDADE_CLIENTESFROM PEDIDO;

3- SELECT REVERSE(SUBSTRING(REVERSE(cl.nome) FROM 0 FORPOSITION(' ' IN REVERSE(cl.nome)))) AS nome, f.nome ASvendedor, SUM(i.preco) as preco FROM cliente AS cl JOINpedido AS p ON cl.codigo = p.cliente JOIN funcionario ASf ON p.vendedor = f.codigo JOIN itens AS i ON p.codigo =i.pedido GROUP BY cl.codigo, f.codigo ORDER BY cl.nome,f.nome;

29

#Exercícios - Gabarito

SQL - Funções de Agregação

4 – SELECT f.nome,TO_CHAR(AVG(i.preco::NUMERIC –i.desconto::NUMERIC), '999D99') AS media_venda FROMFUNCIONARIO AS f JOIN pedido p ON f.codigo = p.vendedorJOIN itens i ON p.codigo = i.pedido GROUP BY f.nome ORDERBY media_venda DESC;

30

#Exercícios - Gabarito


Recommended