Estudando para o exame de certificação Oracle Database SQL | 1Z0-071 - Restringindo e ordenando dados com a cláusula Where

Fala, galera!

Tudo bem?

Continuando os estudos preparatórios p/ realizar o exame 1Z0-071, hoje vou falar um pouco sobre como utilizar a clausula Where, e algumas dicas de ordenação de dados.

Script de criação da tabela p/ nos ajudar nos exemplos:

CREATE TABLE instr_musicais(cd_instrumento INT PRIMARY KEY
                                                     ,nm_instrumento VARCHAR2(50)
                                                     ,Tp_instrumento VARCHAR2(10)
                                                     ,dt_fabricacao  DATE
                                                     ,vl_instrumento NUMBER(15,2))
/

INSERT INTO instr_musicais VALUES(1, 'Violão',   'Corda',     '01-May-2020', 1000);
INSERT INTO instr_musicais VALUES(2, 'Guitarra', 'Corda',     '01-May-2020', 2000);
INSERT INTO instr_musicais VALUES(3, 'Baixo',    'Corda',     '01-jun-2020', 3000);
INSERT INTO instr_musicais VALUES(4, 'Bateria',  'Percussão', '01-Jun-2020', 4000);
INSERT INTO instr_musicais VALUES(5, 'Teclado',  'Digital',   '05-dec-2020', 5000);
INSERT INTO instr_musicais VALUES(6, 'Sanfona',  'Aerofone',  '05-dec-2020', 6000);
INSERT INTO instr_musicais VALUES(7, 'Flauta',   'Sopro',     '05-dec-2020', 7000);
INSERT INTO instr_musicais VALUES(8, 'Saxofone', 'Sopro',     '05-dec-2020', 7000)

                         
COMMIT

/

Como vimos no estudo anterior, podemos utilizar o comando Select p/ recuperar os dados das tabelas criadas no banco.

No exemplo abaixo, estou recuperando todos os dados.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais

Restringindo dados

Na query abaixo, vou trazer apenas os registros onde a coluna tp_instrumento esteja preenchida com a informação "Corda".

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE Tp_instrumento = 'Corda'

Algo muito importante a ser considerado são os caracteres maiúsculos e minúsculos, que é o que chamamos de case sensitive, no caso dos dados é importante que você escreva exatamente como a informação está gravada no campo da tabela, caso contrário o comando select não retornará os dados, ex:

Note que eu escrevi o literal "corda" com a primeira letra minuscula. 

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE tp_instrumento = 'corda';

Outra observação a respeito dos parâmetros, note que sempre que estivermos usando um literal caractere ou uma data, a informação deve estar entre aspas simples, no caso de informações numéricas as aspas não são necessárias, você vai poder observar isso conforme eu for exemplificando.

Restringindo pelo campo de data.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
WHERE dt_fabricacao = '01-JUN-2020';

A clausula Where também pode ser utilizada com os operadores de comparação abaixo.

> "Maior"
< "Menor"
<> ou != "Diferente"

Exemplos:

> "Maior"

No caso abaixo, eu quero que traga apenas os registros onde a coluna vl_instrumento seja maior do que 3000.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE vl_instrumento > 3000;

< "Menor"

No caso abaixo, eu quero que traga apenas os registros onde a coluna vl_instrumento seja menor do que 7000.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE vl_instrumento < 7000;

<> ou != "Diferente"

No caso abaixo, recuperei apenas os registros com onde a informação do campo tp_instrumento fosse diferente de "Corda"

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE Tp_instrumento <> 'Corda'

Utilizando != ao invés de <>

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE Tp_instrumento != 'Corda'

Comando Between (Entre)

Esse comando é muito importante e muito utilizado no dia a dia, pode ser utilizado com caracteres, datas e números. 

No exemplo abaixo, eu trouxe todos os registros onde o campo vl_instrumento fosse entre 1 e 3999

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE vl_instrumento BETWEEN 1 AND 3999;

Utilizando com datas, mandei trazer todos os registros onde o campo dt_fabricacao estivesse entre 01/05/2020 e 01/06/2020

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE dt_fabricacao BETWEEN '01-may-2020' AND '01-jun-2020';

O Between também pode ser usando com o NOT, no caso abaixo mandei trazer todos os registros onde o campo dt_fabricacao NÃO estivesse entre 01/05/2020 e 01/06/2020

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE dt_fabricacao NOT BETWEEN '01-may-2020' AND '01-jun-2020';

Comando IN

No comando abaixo, selecionei apenas os registros onde o campo tp_instrumento estivesse preenchido com os literais, "Corda", "Sopro"

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE tp_instrumento IN('Corda','Sopro');

Comando LIKE

No exemplo abaixo, eu trago apenas os registros onde a primeira letra do campo nm_instrumento é o "B", note que o percentual é usado na sequencia do literal de parâmetro.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE nm_instrumento LIKE 'B%';

No exemplo abaixo, eu trago apenas os registros onde a segunda letra do campo nm_instrumento é o "A", note que o percentual é usado na sequencia do literal de parâmetro e também o uso do "_" p/ definir a posição do dado no campo de busca.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
    FROM instr_musicais
 WHERE nm_instrumento LIKE '_a%';

O LIKE também pode ser usado da seguinte maneira.

Na instrução abaixo, eu seleciono todos os registros onde a informação no campo nm_instrumento  termine com a letra "a", note que o % agora foi pro inicio, antes da informação do literal de parâmetro, nesse caso a letra "a".

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE nm_instrumento LIKE '%a';

Uma outra forma de utilização do LIKE. Usando o % no inicio e no final do parâmetro, desta forma
iremos recuperar os registros onde o caractere informado seja encontrado em qualquer ponto do campo.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE nm_instrumento LIKE '%a%';

Outro exemplo do mesmo tipo acima, apenas pra esclarecer o entendimento.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE nm_instrumento LIKE '%x%';

Comando Escape

P/ exemplificar a forma de utilização desse comando, irei inserir mais 2 registros na tabela.

INSERT INTO instr_musicais VALUES(9, 'Piano_Cauda', 'Corda',  '17-Jan-1980', 27000);
INSERT INTO instr_musicais VALUES(10, 'Baixo_Acustico', 'Corda',  '10-Jan-2020', 2500)
/

COMMIT
/

Pode ser que você um dia precise recuperar dados de uma tabela, onde existam caracteres especiais gravados nos campos, como no exemplo acima, tempos o campo nm_instrumento gravado com o literal "Piano_Cauda", no exemplo abaixo vou tentar usar o LIKE da meneira que expliquei acima pra tentar recuperar o registro gravado.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE nm_instrumento LIKE '%_%'

Note que no exemplo acima, todos os registros da tabela foram recuperados, o banco não compreendeu o parâmetro informado, pois o caractere "_" é especial, p/ que o banco entenda o que estamos querendo fazer, usamos o comando Escape:

P/ isso utilizamos o caractere "\", o que vier depois dele no LIKE, vai ser o caractere utilizado no comando Escape " '%\_%'".

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE nm_instrumento LIKE '%\_%' ESCAPE '\'

Comando IS NULL e IS NOT NULL

P/ o exemplo abaixo, vou inserir mais dois registros na tabela

INSERT INTO instr_musicais VALUES(11, 'Xilofone', NULL,  NULL, 0);
INSERT INTO instr_musicais VALUES(12, 'Pandeirola', 'Percussão', NULL, 50)
/

COMMIT
/

No exemplo abaixo, quero selecionar apenas os registros em que o campo tp_instrumento esteja nulo,
lembre-se bem, nulo é diferente de 0 ou diferente de espaços, nulo é vazio, sem nada, tome cuidado com isso.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE tp_instrumento IS NULL;

Mais um exemplo do IS NULL

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE dt_fabricacao IS NULL;

Exemplo do IS NOT NULL, mandei recuperar todos os registros onde o campo data dt_fabricacao não esteja nulo.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE dt_fabricacao IS NOT NULL;

Operadores de condições. AND e OR 

O AND e o OR abrem diversas possibilidades de escrita de select que são muito interessantes, vou demonstrar algumas abaixo, mas com certeza a utilização desses dois operadores vai muito além disso,  e com o dia a dia trabalhando com eles você vai poder notar. 

No exemplo abaixo, selecionei os registros onde o campo tp_instrumento fosse igual ao literal "Corda" e que o campo vl_instrumento fosse "<" menor do que 10000.    

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE tp_instrumento = 'Corda'
      AND vl_instrumento < 10000

Mais um exemplo abaixo, note que fiz uma combinação de varias condições no meu where.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
WHERE dt_fabricacao IS NOT NULL
      AND vl_instrumento BETWEEN 1000 AND 5000
      AND nm_instrumento LIKE 'B%';

OR

No exemplo abaixo, estou selecionando os registros onde o campo tp_instrumento esteja preenchido com o literal "Corda" ou (or) "Sopro"

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
WHERE tp_instrumento = 'Corda'
         OR tp_instrumento = 'Sopro';

Agora um pouco de lógica na hora da utilização do OR.

Note que estou utilizando o OR e o AND no where, este tipo de combinação precisa ser bem pensada/ escrita, nestes casos precisamos entender a forma de comportamento do banco de dados p/ escrever o select corretamente.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
WHERE tp_instrumento = 'Corda'
        OR tp_instrumento = 'Sopro'
     AND vl_instrumento > 1000

Note que mesmo colocando a clausula "AND vl_instrumento > 1000" no select acima, mesmo assim
a query retornou um registro com o campo vl_instrumento igual a 1000, sendo que eu escrevi p/ trazer apenas os maiores, mas por que isso? Aqui vai a explicação, o banco de dados trabalha de uma forma estruturada, ele efetua a leitura da query de maneira ordenada, é a chamada regra de precedência, a sua escrita precisa deixar clara a separação dos comandos no caso da utilização do OR + o AND.

Note na escrita abaixo, que coloquei a clausula OR entre (), desta maneira o banco de dados irá executar primeiro o OR e na sequencia, irá executar o AND.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE (tp_instrumento = 'Corda'
    OR tp_instrumento = 'Sopro')
   AND vl_instrumento > 1000;

Utilizando o order by.

Por padrão, o a comando order by sempre ira trazer os registros ordenados de maneira ascendente, ou seja do menor p/ o maior.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 ORDER BY nm_instrumento;

O Comando pode ser utilizando com números também, aqui peço p/ ordenar pelo primeiro campo do select.

SELECT nm_instrumento, tp_instrumento, dt_fabricacao, vl_instrumento
   FROM instr_musicais
 WHERE tp_instrumento = 'Sopro'
 ORDER BY 1

Podemos fazer a ordenação pelo apelido do campo também.

SELECT nm_instrumento nome, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE tp_instrumento = 'Sopro'
 ORDER BY nome;

A ordenação também pode ser feita de maneira decrescente, ou seja do maior p/ o menor, p/ isso utilizamos o DESC

SELECT nm_instrumento nome, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 WHERE tp_instrumento = 'Sopro'
 ORDER BY nome DESC;

P/ ordenação também podemos combinar o ASC, e o DESC.

Aqui ordeno por nome do menor pro maior, e por valor do maior p/ o menor.

SELECT nm_instrumento nome, tp_instrumento, dt_fabricacao, vl_instrumento
  FROM instr_musicais
 ORDER BY nome, vl_instrumento DESC ;

Obs: P/ realizar os testes estou usando a ferramenta PL/SQL developer, da empresa Allround Automations, é uma ferramenta paga, porém você pode usar o SQL developer que é da própria Oracle, ou o SQL Plus, que é um pouco mais trabalhoso de se usar, mas ai fica a seu critério.

Bom, espero que tenham gostado, esse é assunto muito vasto e com certeza eu não abordei completamente, ainda existe muito a ser falado.

Até a próxima.

Abs

Leonardo Silva