Aula 2 - Certificação 1Z0-051

RESPOSTAS DA AULA ANTERIOR

1 - select last_name, salary, salary*1.06 "Salário Atualizado" from employees;

2- desc departments;

select distinct q'(Department's ID )' || department_id "Número do Departamento" 

from employees;

3- select  EMPLOYEE_ID || ', ' ||
  FIRST_NAME || ', ' ||
  LAST_NAME || ', ' ||
  EMAIL || ', ' ||
  PHONE_NUMBER || ', ' ||
  HIRE_DATE || ', ' ||
  JOB_ID || ', ' ||
  SALARY || ', ' ||
  COMMISSION_PCT || ', ' ||
  MANAGER_ID || ', ' ||
  DEPARTMENT_ID  "INFORMAÇÕES DO EMPREGADO"
from employees;

Obs.: o último exercício apresentou um erro proposital porque o tamanho máximo que um identificador de coluna pode ter são 30 caracteres. Caso você tenha identificado esse erro antes de vir às respostas, PARABÉNS!

-------------------------------------------------------------------------

Cláusula WHERE

Se for preciso restringir linhas de uma consulta, usamos a cláusula WHERE. Ela vem sempre após a palavra chave FROM.

Podemos usar vários tipos de operadores numa consulta dessas, como operadores aritméticos, operadores de concatenação, operadores de comparação, LIKE, BETWEEN, NULL e operadores lógicos.

SELECT coluna(s) FROM tabela WHERE condição;

Suponhamos um cenário de Recursos Humanos, onde desejam recuperar do BD apenas aqueles funcionários que possuem o cargo de vendedor.

Sabemos de antemão que o ID deste respectivo cargo é SA_REP (Representante de Vendas), assim podemos buscar no BD apenas os funcionários que possuem esse JOB_ID.

SELECT last_name, job_id FROM employees WHERE job_id = 'SA_REP';

O condicionamento é sensitive case, portanto a  palavra que buscamos no BD deve ser idêntica àquela que referenciamos na consulta, caso contrário há possibilidade da consulta não trazer os dados ou pior, trazer dados errados.

Quando condicionamos palavras, estas devem vir entre aspas simples, obrigatoriamente. E não é possível apelidarmos uma condição numa cláusula WHERE.

Se quisermos procurar por uma data específica, podemos realizar a seguinte consulta:

SELECT last_name, hire_date FROM employees WHERE hire_date = '17-JUN-03';

Datas possuem o formato padrão DD-MON-RR, podendo ser alterado no BD, no SQL Developer ou localmente nas configurações da estação de trabalho. São configurações diferentes e não surtem o mesmo efeito. Falaremos sobre elas mais pra frente.

Operadores de Comparação

Quem já está acostumado com desenvolvimento de softwares e sistemas ou mesmo o portugol do primeiro semestre da faculdade, não enfrentará dificuldades em associar esses operadores na hora da construção de uma instrução envolvendo expressões lógicas.

Os operadores reconhecidos pelo Oracle são:

=  significa: IGUAL AO VALOR;

>  significa: MAIOR DO QUE O VALOR;

>=  significa: MAIOR OU IGUAL AO VALOR;

<  significa: MENOR DO QUE O VALOR;

<=  significa: MENOR OU IGUAL AO VALOR;

<> ou != ou ^=  significa: DIFERENTE DO VALOR;

BETWEEN ... AND ...  significa: ENTRE UM VALOR E OUTRO;

NOT BETWEEN ... AND ...  significa: NÃO ESTÁ ENTRE UM VALOR E OUTRO; 

IN ( valores )  significa: VERDADEIRO SE O VALOR ESTIVER DENTRO DOS PARÊNTESES;

NOT IN ( valores )  significa: VERDADEIRO SE O VALOR NÃO ESTIVER DENTRO DOS PARÊNTESES;

LIKE  significa: COMPARAÇÃO DE STRINGS (POSITIVA);

NOT LIKE  significa: COMPARAÇÃO DE STRINGS (NEGATIVA);

IS NULL  significa: VERIFICA SE O VALOR É NULO;

IS NOT NULL significa: VERIFICA SE O VALOR NÃO É NULO.

Caso desejemos recuperar apenas os dados dos funcionários que ganhem entre 2.000 e 5.000, usamos a seguinte instrução:

SELECT * FROM employees WHERE salary BETWEEN 2000 and 5000;

Quando queremos saber quais funcionários NÃO ganham salários de 2.400, 4.000 ou 2.600:

SELECT * FROM employees WHERE salary NOT IN( 4000, 2400, 2600 );

Recuperar somente os dados dos funcionários que ganhem comissão:

SELECT * FROM employees WHERE commission_pct IS NOT NULL;

Ou então recuperar os dados daqueles funcionários que tenham o sobrenome Abel sem usar o comparador IGUAL:

SELECT * FROM employees WHERE last_name like 'Abel';

O operador LIKE possui dois caracteres coringas que podem ser usados na comparação de strings que ele faz.

% significa: RETORNA ZERO OU MUITOS CARACTERES;

_ significa: SUBSTITUI UM CARACTERE.

 SELECT * FROM employees WHERE last_name like 'K_n%';

A instrução acima procura no BD apenas empregados que possuem a primeira letra do sobrenome 'K' maiúsculo, seguida de uma letra desconhecida, depois um 'n' minúsculo e o restante também desconhecido.

 SELECT * FROM employees WHERE last_name like '%h%';

A instrução acima retorna somente aqueles empregados que possuem 'h' minúsculo em seus sobrenomes.

Caso o seu BD possua esses caracteres curingas em seus registros, podemos usar um identificador ESCAPE para informar ao BD que esse caractere não é curinga. A forma de usar o identificador ESCAPE é a seguinte:

SELECT last_name, job_id FROM employees WHERE job_id LIKE 'SA/_%' ESCAPE '/';

O banco desconsiderará o primeiro caractere curinga na instrução acima, mas o segundo não teve alteração de uso. O identificador pode ser qualquer caractere, tendo apenas que ser referenciado entre aspas simples após a palavra chave ESCAPE.

Sugiro que teste algumas das possibilidades de utilização destes e dos próximos operadores para familiarizar-se com eles, pois utilizaremo-os com frequência a partir desta aula.

Operadores Lógicos

Operadores lógicos são utilizados para retornar um valor somente se o resultado geral da condição for verdadeiro.

AND significa: retorna VERDADE se as duas condições forem VERDADEIRAS.

OR significa: retorna VERDADE se pelo menos uma das condições for VERDADEIRA.

NOT significa: retorna VERDADE caso a condição seja FALSA.

Cogitemos a hipótese de precisar buscar no banco funcionários que trabalhem no departamento 90, mas que, necessariamente, também recebam salário de 17.000 e tenham o JOB_ID  AD_VP.

SELECT last_name, department_id, salary, job_id

FROM employees

WHERE department_id = 90 AND

salary = 17000  OR 

salary = 19000 AND

job_id = 'AD_VP' AND NOT 

last_name='Kochhar';

Perceba que mesmo eu especificando na última condição o NÃO aparecimento do empregado Kochhar ele veio mesmo assim. Há uma precedência sobre todos os operadores, como na matemática.

Primeiro o Oracle irá resolver os operadores lógicos NOT, posteriormente os AND e por fim os OR.

Sendo assim, primeiro ele vai separar quem NÃO É Kochhar, em seguida quem é do departamento 90 E ganha 17.000, depois quem ganha 19.000 E tem o job_id AD_VP. Por fim irá trazer TODOS que se qualificam na condição de departamento E salário OU nas outras. Como Kochhar já está qualificado em uma das condições a última condição é 'anulada'.

Obs.: a segunda condição não afeta nenhuma linha do BD, está lá apenas para fazer volume, pois é deste jeito que as perguntas caem na prova de certificação Oracle.

A precedência de todos os operadores é a seguinte:

Cláusula Order By

Por padrão, a classificação sempre é indefinida e o Oracle nunca traz a mesma formação por duas vezes seguidas.

 SELECT last_name, job_id FROM employees;

Mas podemos alterá-la usando a cláusula ORDER BY.

SELECT last_name, job_id

FROM employees

ORDER BY job_id;

Podemos usar o apelido da coluna no ordenamento 

SELECT last_name, job_id numero

FROM employees

ORDER BY numero;

ou então o número de sua posição. 

SELECT last_name, job_id numero

FROM employees

ORDER BY 2;

Em todos os casos obteremos o mesmo resultado. Ainda é possível definir de queremos uma classificação Descendente (DESC) ou Ascendente (ASC, padrão). É bom enfatizar que a cláusula ORDER BY deve, obrigatoriamente, ser a última cláusula de cada instrução.

SELECT last_name, job_id numero

FROM employees

ORDER BY 2 DESC;

Podemos dizer para o Oracle trazer-nos primeiro os valores nulos com a palavra chave NULLS FIRST ou por último com a palavra chave NULLS LAST:

SELECT last_name, commission_pct
FROM employees
ORDER BY 2 DESC NULLS FIRST;

Classifica-se por mais de uma coluna também, como se segue:

SELECT last_name, commission_pct, department_id
FROM employees
ORDER BY last_name, department_id;

Variáveis de Substituição 

As variáveis de substituição servem para não precisar ser informado o mesmo valor mais de uma vez pelo usuário ou numa consulta rotineira ao BD.

A variável armazena um valor temporário, alternando o seu conteúdo conforme novos valores forem sendo informados.

Elas podem ser utilizadas para complementação de condições WHERE, cláusulas ORDER BY,expressões de coluna, nomes de tabela e instruções SELECT inteiras.

Há duas maneiras de declarar variáveis no SQL Developer ou SQLPlus:

• Utilizando um E comercial (&)  antes de um identificador, por exemplo '&Variavel', que irá guardar o valor informado, utilizá-lo na instrução e depois descartá-lo;

SELECT last_name, commission_pct, department_id, &Nome_Tabela

FROM employees

ORDER BY &Ordenar_Por;

• Utilizando dois E comerciais (&&) antes de um identificador, por exemplo '&&Variavel', que irá guardar o valor informado, utilizá-lo na instrução, não irá mais solicitar um novo valor e só irá descartá-lo se a sessão for encerrada ou a cláusula UNDEFINE for utilizada.

SELECT last_name, commission_pct, department_id, &&Nome_Tabela

FROM employees

ORDER BY &Nome_Tabela;

UNDEFINE Nome_Tabela;

Para valores de datas e caracteres, aspas simples necessariamente precisam ser utilizadas.

SELECT last_name, commission_pct, department_id, &Nome_Tabela

FROM employees

WHERE last_name = '&Nome_Peao' 

ORDER BY &Nome_Tabela;

Podemos definir um valor padrão para uma variável usando a cláusula DEFINE e descartar o valor na variável com a cláusula UNDEFINE.

DEFINE empregado_num = 200;

SELECT last_name, commission_pct, department_id

FROM employees

WHERE employee_id= &empregado_num;

UNDEFINE empregado_num;

Utilizando o comando VERIFY podemos ver o valor da variável e toda a instrução utilizada, tanto a nova quanto a antiga, quando não havia um valor a ela associado. Para ver o resultado deste comando pressione F5 ao invés de F9.

SET VERIFY ON

SELECT last_name, commission_pct, &departamento

FROM employees;

Obs.: uma variável de substituição pode ser utilizada em qualquer lugar numa instrução SELECT, menos como primeira palavra inserida no prompt. A utilização de variáveis de substituição pode gerar riscos ao BD por possibilitar ataques de injeção SQL.

QUESTIONÁRIO

1- O RH precisa de uma instrução que traga do BD todos os nomes que contenham a letra 'S' em suas iniciais e a terceira contenha a letra 'i', mas que não trabalhem no departamento 50. Ordene as colunas por salário.

2- Traga do BD somente aqueles funcionários que ganhem entre 2.500 e 3.500, mas mostre aqueles que quando acrescentado 10% ao salário exceda 3.500.  Ordene as colunas por salário.

3- O Departamento Pessoal precisa de uma query padrão que eles possam utilizar para procurar empregados pelo seu ID ou Nome. A instrução deve necessariamente conter nome, sobrenome, salário, departamento e cargo. As colunas deverão ser renomeadas para português. Ordene as colunas por salário.

< Retornar  ||  Avançar Aula 3 >