Banco II - PostgreSQL - Funções

Embed Size (px)

Text of Banco II - PostgreSQL - Funções

  • #02

    Banco de Dados II

    2015.2 Gustavo Svio 1

  • Funes da SQLAs funes de SQL existem para facilitar amanipulao dos dados armazenados na base dedados.

    Elas so ferramentas projetadas para desempenharuma tarefa nica e bem definida.

    As funes de SQL so chamadas dentro de umaconsulta SQL pelo seu nome:

    Algumas recebem argumento, outras no.

    Todas elas retornam um valor.

    2

    #Funes

  • Categorias Gerais de FunesCategoria de Funo Descrio

    Matemticas Funes usadas para realizar clculos matemticosespecficos, como as funes trigonomtricas e outras.

    Cadeia de Caracteres Funes que realizam manipulao de cadeia decaracteres, tais como: localizar padres dentro decadeias de caracteres, inserir caracteres, concatenarcadeias de caracteres, descobrir o comprimento,converter para maisculo e minsculo.

    Data/Hora Funes utilizadas para retornar informaes sobredata/hora correntes, formatar data e hora como cadeiade caracteres, realizar clculos baseado em horrios,etc.

    Agregao Funes utilizadas para fazer agrupamento.

    Formatao Funes utilizadas para retornar informaesformatadas.

    3

    #Funes

  • SQL - Funes Matemticas

    Algumas funes Matemticas (PostgreSQL):

    valor % valor: retorna o valor do resto da diviso entre dois nmeros.

    Valor ^ valor: retorna o valor elevado a potncia.

    valor!: retorna o valor fatorial do nmero.

    @(valor): retorna o valor absoluto.

    4

    #Funes Matemticas

  • SQL - Funes Matemticas

    Algumas funes Matemticas (PostgreSQL):

    |/ valor: retorna o valor da raiz quadrada.

    ||/ valor: retorna o valor da raiz cbica.

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

    CEIL(valor): retorna o valor arredondado.

    5

    #Funes Matemticas

  • SQL - Funes Matemticas

    Algumas funes Matemticas (PostgreSQL):

    FLOOR(valor): retorna o valor truncado.

    MOD(x, y): retorna o valor do resto de diviso de x/y.

    PI(): retorna o valor de PI.

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

    6

    #Funes Matemticas

  • SQL - Funes Matemticas

    Algumas funes Matemticas (PostgreSQL):

    RANDOM(): retorna o valor aleatrio 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

    #Funes Matemticas

  • SQL - Funes Matemticas

    Algumas funes Matemticas (PostgreSQL):

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

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

    8

    #Funes Matemticas

  • SQL - Funes Matemticas

    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

    #Funes Matemticas

  • SQL - Funes de Caracteres

    Algumas funes 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 maisculo.

    LOWER(expresso): converte uma cadeia para minsculo.

    10

    #Funes de cadeia de caracteres

  • SQL - Funes de Caracteres

    Algumas funes de Cadeia de Caracteres(PostgreSQL):

    POSITION(procurar IN texto): retorna a posio docaractere.

    STRPOS(texto, procurar): retorna a posio docaractere.

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

    11

    #Funes de cadeia de caracteres

  • SQL - Funes de Caracteres

    Algumas funes 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

    #Funes de cadeia de caracteres

  • SQL - Funes de CaracteresAlgumas funes de Cadeia de Caracteres(PostgreSQL):

    MD5(texto): retorna o cdigo 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 (comeando de 1).

    13

    #Funes de cadeia de caracteres

  • SQL - Funes de Caracteres

    Algumas funes de Cadeia de Caracteres(PostgreSQL):

    RTRIM(texto, remover): remove os caracteres dadireita. O padro espao em branco.

    LTRIM(texto, remover): remove os caracteres daesquerda. O padro espao em branco.

    INITCAP(texto): Capitaliza uma cadeia de caracteres.

    REVERSE(texto): retorna uma String reversa.

    14

    #Funes de cadeia de caracteres

  • SQL - Funes de CaracteresExemplo - Exibir o nome dos produtos em minsculo,maisculo e os 10 primeiros caracteres do nome tambm emmaisculo.

    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

    #Funes de cadeia de caracteres

  • SQL - Funes de Data/Hora

    Algumas funes 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

    #Funes de data/hora

  • SQL - Funes de Data/HoraAlgumas funes de Data/Hora (PostgreSQL):

    AGE: Diferena entre datas.Retorna um INTERVAL

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

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

    17

    #Funes de data/hora

  • SQL - Funes de Data/HoraAlgumas funes de Data/Hora (PostgreSQL): EXTRACT: Extrai informaes 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

    #Funes de data/hora

  • SQL - Funes de Data/Hora

    Algumas funes de Data/Hora (PostgreSQL):

    Extrair a diferena de anos entre duas datas:

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

    19

    #Funes de data/hora

  • SQL - Funes de Data/HoraAlgumas funes 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

    #Funes de data/hora

  • SQL - Funes de Data/Hora

    Algumas funes de Data/Hora (PostgreSQL):

    DATE_PART: Extrai informaes 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

    #Funes de data/hora

  • SQL - Funes de AgregaoAlgumas funes de Agregao (PostgreSQL):

    COUNT(*): conta a quantidade de registros.

    COUNT(DISTINCT expresso): conta a quantidade deregistros filtrando pela expresso.

    SUM ( ): soma os valores de uma coluna de dadosnumricos.

    AVG ( ): calcula a mdia de uma coluna de dadosnumricos.

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

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

    22

    #Funes de Agregao

  • SQL - Funes de AgregaoAlgumas funes de formatao (PostgreSQL):

    TO_CHAR(tipo_de_dado, text) Converte um tipo de dadoem uma string seguindo um padro.

    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

    #Funes de Formatao

  • SQL - Funes de Agregao

    Casting (PostgreSQL):

    CAST(expresso AS tipo) Efetua um CAST entre tiposde dados.

    Expresso::tipo Efetua um cast entre tipos dedados

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

    24

    #Funes de Casting

  • SQL - Funes de Agregao1 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

    #Exerccios

  • SQL - Funes de Agregao

    2 Contar quantos clientes j fizeram pedidos.

    Resultado esperado:

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

    6

    26

    #Exerccios

  • SQL - Funes de Agregao3 Obter as seguintes informaes da loja: Um relatrio contendo asinformaes: 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----------------+--------------------+---