Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções de agrupamento/ Agrupando dados.

Fala, galera. Tudo bem?

Hoje vou falar um pouco sobre algumas funções de agrupamento de dados, funções essas que são extremamente utilizadas no dia a dia do programador Oracle.

Separei algumas delas, as quais julguei mais importante pra gente falar um pouco.

AVG => Retorna o valor médio de um conjunto de valores
COUNT => Retorna a quantidade de linhas de uma consulta
MAX => Retorna o valor máximo de (X)
MIN => Retorna o valor mínimo de (X)
SUM => Retorna a soma de (X)

Evidencia da tabela que utilizarei no decorrer dos estudos.

AVG

Vamos começar falando da função AVG, essa função retorna o valor médio da coluna selecionada, note que o banco efetua o agrupamento e devolve a média, no caso do exemplo abaixo, eu estou trazendo a média do campo vl_salario, a função efetua a soma de todos os valores e após isso divide pela quantidade de registros.

SELECT AVG(vl_salario) media
  FROM funcionarios;

COUNT

Essa função faz a conta da quantidade de registros em uma determinada consulta.
Abaixo segue exemplo da utilização da função p/ verificar a quantidade de registros existentes na tabela.

SELECT COUNT(1)
  FROM funcionarios;

MAX e MIN

Como os próprios nomes já sugerem, utilizamos as funções acima p/ retornar um valor máximo, ou mínimo de um conjunto de dados. Abaixo, efetuarei uma consulta p/ retornar o maior/ menor salários
cadastrados na tabela:

SELECT MAX(vl_salario) maior_salr
               ,MIN(vl_salario) menor_salr
   FROM funcionarios  

O MIN e o MAX também podem ser utilizados com caracteres, onde o banco fará a busca utilizando o fator de ordem alfabética p/ recuperar o Mínimo e o Máximo. P/ campos de datas, vai considerar a menor(MIN) e a maior como (MAX).

SELECT MAX(dt_contratacao) min_dt
      ,MIN(dt_contratacao) max_dt
      ,MIN(nm_func) min_nm
      ,MAX(nm_func) max_nm
      ,MIN(nm_departamento) Min_dept
      ,MAX(nm_departamento) MAX_dept
  FROM funcionarios;

SUM

Efetua a somatória de uma determinada coluna, no caso abaixo, estou efetuando a soma da coluna salario, de todos os registros da tabela.

SELECT SUM(vl_salario) soma
  FROM funcionarios 

Agora vamos falar um pouco sobre como agrupar esses dados, falaremos da clausula GROUP BY e da clausula HAVING.

GROUP BY

Utilizamos essa clausula quando se faz necessário o agrupamento de dados de colunas em comum.

Suponhamos que eu queira saber a quantidade de funcionários contratados por ano, pra isso utilizamos o GROUP BY, onde combinamos o agrupamento + as funções de grupo, exemplo:

No SELECT abaixo, eu formatei o campo de data p/ mostrar apenas o ano, efetuei o agrupamento por essa coluna, e inseri a função COUNT para contar os registros.

SELECT to_char(dt_contratacao, 'rrrr') ano, 

               COUNT(1) qt_contratados

  FROM funcionarios 

 GROUP BY to_char(dt_contratacao, 'rrrr')

HAVING

Essa clausula é utilizada quando existe a necessidade de filtrarmos o resultado dos agrupamentos, a mesma é inserida após o GROUP BY, no exemplo abaixo, selecionarei apenas os registros onde o numero de contratados é maior do que 1.

SELECT to_char(dt_contratacao, 'rrrr') ano, 

       COUNT(1) qt_contratados

  FROM funcionarios 

 GROUP BY to_char(dt_contratacao, 'rrrr')

 HAVING COUNT(1) >1

A clausula HAVING pode ser utilizada com outras funções de grupo, abaixo mais um exemplo de utilização do GROUP BY + HAVING, agora utilizando a função AVG. No SELECT abaixo, estou trazendo a media salarial dos funcionários contratados no ano, e filtrando apenas as medias maiores do que 5000.

SELECT to_char(dt_contratacao, 'rrrr') ano, 
               AVG(vl_salario) media
  FROM funcionarios 
 GROUP BY to_char(dt_contratacao, 'rrrr')
 HAVING AVG(vl_salario)> 5000

Espero que tenham gostado, 

Até a próxima.

Leonardo Silva.

Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Utilizando funções de conversão e condicionais. Cont.

Fala, galera. Tudo bem?

Continuando a falar um pouco sobre funções de conversão e funções condicionais.

Hoje vamos falar de algumas funções muito utilizadas por nós desenvolvedores Oracle.

NVL
NVL2
COALESCE
NULLIF
CASE
DECODE

NVL

Muitas vezes temos colunas no banco de dados onde não existe nenhuma informação gravada, ou seja o campo está nulo, porém no resultado do nosso select é necessário a exibição de algum dado, pra isso podemos usar o NVL, exemplos abaixo:

Retornando todos os dados da tabela

Select * from Aluno;

Agora utilizando o NVL na coluna cod_turma, para que o comando funcione corretamente, o dado inserido na função pra retornar quando o campo estiver nulo, deve ser do mesmo tipo de dado da tabela, caso possível conversões também podem ser efetuadas. No exemplo abaixo, eu estou retornando "0" no campo cod_turma, sempre que o campo for nulo.

select cod_aluno, 

          nm_aluno, 

          nvl(cod_turma, o) cod_turma

from aluno;

Mais um exemplo da utilização do NVL, agora passando um campo varchar como parâmetro, repare que você pode escrever algo p/ retornar na sua query caso a coluna esteja nula.

select nm_instrumento
         ,tp_instrumento
        ,nvl(tp_instrumento, 'S/ Tipo cadastrado') tp_instrumentoNVL 
from instr_musicais;

NVL2

No caso do NVL2 é possível trabalharmos com duas opções de retorno, passamos a coluna da tabela como parâmetro, caso a informação não seja nula, retorna a primeira literal escrita, caso seja nulo, retorna a segunda, no caso exemplo abaixo, se não for nulo retorna "S", e se for retorna "N", muito parecido com a estrutura de um IF, se você conhece lógica de programação, vai entender o que eu estou querendo dizer.

select cod_aluno,
          nm_aluno,
          cod_turma,
         nvl2(cod_turma,'S','N') "Tem turma?"
  from aluno;

COALESCE

Função COALESCE, é utilizando quando existe a necessidade de verificar se mais de uma coluna da tabela está nula, nessa função é possível utilizarmos N parâmetros, lembrando que todos os parâmetros devem ser do mesmo tipo de dado.

Exemplos:

No exemplo abaixo, estou testando dois parâmetros, caso estejam nulos, retorna a literal

select coalesce(null, null, 'TesteCoalesce') Teste from dual;






Nessa função você pode incluir quantas colunas forem necessárias, caso todas estejam nulas, a
função retornara o ultimo parâmetro informado, no exemplo abaixo inclui varios parametros nulos p/ deixar bem claro os testes da função.

select coalesce(null, null, null, 
                         null, null, null, 
                         null, 'TesteCoalesce') Teste 
  from dual;

Note no exemplo abaixo que se um dos dados não estiver nulo, a função retornará o primeiro valor que encontrar.

select coalesce('C', null, null, 

                'B', null, null, 

                'A', 'TesteCoalesce') Teste 

  from dual;

NULLIF

A função NULLIF retornará null caso os parâmetros informados estejam iguais, sempre que forem diferentes, a função retornará o primeiro parâmetro informado, exemplo:

select Nullif('A', 'A') Teste1

      ,Nullif('B', 'C') Teste2 

      ,Nullif('C', 'D') Teste3 

      ,Nullif('D', 'D') Teste4 

  from dual;

CASE

Se trata de uma função onde é possível trabalharmos com estruturas de decisões, como se incluíssemos uma estrutura de IF dentro do select, no exemplo abaixo, inclui uma estrutura de decisão p/ manipular os dados da coluna vl_salario, dependendo da data de contratação, adicionei um valor de aumento de salario.

select nm_func

      ,dt_contratacao

      ,vl_salario

      ,case

         when dt_contratacao between to_date('01/01/2017','dd/mm/rrrr') 

                                 and to_date('31/12/2017','dd/mm/rrrr') then

           vl_salario * 1.10

         when dt_contratacao between to_date('01/01/2018','dd/mm/rrrr') 

                                 and to_date('31/12/2018','dd/mm/rrrr') then

           vl_salario * 1.05

         when dt_contratacao between to_date('01/01/2019','dd/mm/rrrr') 

                                 and to_date('31/12/2019','dd/mm/rrrr') then

           vl_salario * 1.03

         else 

           vl_salario

        end vl_ajuste_salario

  from funcionarios;

Estrutura do CASE - WHEN - THEN - ELSE - END.

CASE - Abre a estrutura.

WHEN - Quando (Condição)

THEN - Então (O que deve ser feito, caso entre na condição)

ELSE - Exceção das condições anteriores.

END - Fecha a estrutura

DECODE

Também se trata de uma função onde é possível se trabalhar c/ estruturas de decisões, porém no meu ponto de vista p/ atender necessidades menos complexas, as quais eu utilizaria o CASE p/ resolver.

No exemplo abaixo eu estou manipulando o campo tp_instrumento e retornando um numero no meu select de acordo com a literal recuperada da coluna.

select nm_instrumento,

       tp_instrumento, 

       decode(tp_instrumento, 

              'Corda',     1,

              'Percussão', 2,

              'Digital',   3,

              'Aerofone',  4,

              'Sopro',     5,

              6) teste_decode

  from instr_musicais

No caso do DECODE, o ultimo cadastro na função será a sua exceção, no exemplo acima foi o número 6.

Utilizando o case p/ efetuar o mesmo tratamento de coluna do ultimo exemplo.

select nm_instrumento,

       tp_instrumento, 

       case

         when tp_instrumento = 'Corda' then

           1

         when tp_instrumento = 'Percussão' then

           2

         when tp_instrumento = 'Digital' then

           3

         when tp_instrumento = 'Aerofone' then 

           4

         when tp_instrumento = 'Sopro' then

           5

       else

         6

       end        teste_decode

  from instr_musicais

Não creio que para o exemplo em questão exista codificação melhor ou pior em relação ao uso do CASE ou do DECODE, o que vai determinar qual será utilizado será sua demanda/ o que for mais confortável pra você.

Um abraço e espero que tenham gostado.

Leonardo Silva