RESUMO ORACLE
FUNÇÕES DE LINHA
• FUNÇÕES DE CARACTERES
Ex. 1)
LOWER (cadeia) – Converte caracteres alfabéticos em letras minúsculas
UPPER (cadeia) - Converte caracteres alfabéticos em letras maiúsculas. Ela é muito utilizada, pois maiúsculas e minúsculas fazem muita diferença na cláusula WHERE.
INITCAP (cadeia) – Converte o primeiro caractere de cada palavra em maiúscula e o restante em minúsculas.
SELECT first_name "Nome",
LOWER(first_name) "Minúsculas",
UPPER(first_name) "Maiúsculas",
INITCAP(first_name) "1ª Letra Maiúscula"
FROM employees;
Ex. 2)
LENGTH (cadeia) – Retorna a quantidade de caracteres da coluna ou expressão fornecida.
SUBSTR (cadeia, posição, tamanho) – Extrai uma subcadeia de outra cadeia ou coluna.
SELECT first_name "Nome",
LENGTH(first_name) "Quantidade de caracteres",
SUBSTR(first_name, 1, 3) "Do 1º ao 3º caractere"
FROM employees;
Ex. 3)
SELECT UPPER(last_name) "Maiúsculas",
LOWER(last_name) "Minúsculas",
LENGTH(last_name) "Quantidade de letras",
SUBSTR(last_name, 3, 2) "3ª e 4ª letras"
FROM employees;
Obs.: No exemplo acima, o comando SUBSTR tem como parâmetros o campo last_name, a posição 3 e o tamanho 2 . Portanto irá exibir 2 caracteres do campo ‘last_name’ a partir da posição indicada, no caso é a posição 3.
Ex. 4)
CONCAT (cadeia1, cadeia2) - Concatena duas colunas ou cadeias de caracteres. Semelhante ao operador de concatenação “||”. O CONCAT não dá espaço entre as colunas.
SELECT UPPER(last_name) “Maiúsculas”,
LOWER(last_name) “Minúsculas”,
LENGTH(last_name) “Quantidade de letras”,
SUBSTR(last_name, 3, 2) “3ª e 4ª letras”
CONCAT(first_name, last_name) “Nome e Sobrenome”
FROM employees
WHERE department_id = 50;
O exemplo acima exibe os campos first_name e last_name concatenados e apenas os empregados do departamento 50.
Ex. 5)
INSTR – Mostra a posição do 1º caractere encontrado.
SELECT first_name "Nome",
INSTR (first_name, 'r') "Posicao da letra r"
FROM employees
WHERE department_id = 30;
Ex. 6)
LPAD – Alinha à direita e preenche o que falta à esquerda.
RPAD – Alinha à esquerda e preenche o que falta à direita.
- Exibe o salário com o tamanho do campo 15. Caso não haja esta quantidade de números completa até o tamanho 15 com o caractere *. Alinha o salário em duas colunas distintas: um a direita e outro esquerda:
SELECT salary "Salário",
RPAD(salary, 15,'*') "Alinhado a esquerda",
LPAD(salary,15,'*') "Alinhado a direita"
FROM employees
WHERE department_id = 50;
Ex. 7)
- Exibe do departamento 80, o nome alinhado a esquerda e o nome alinhado a esquerda e o nome alinhado a direita com tamanho de 20 caracteres. Completa com @:
SELECT first_name "Nome",
RPAD(first_name, 20,'@') "Alinhado a esquerda",
LPAD(first_name,20,'@') "Alinhado a direita"
FROM employees
WHERE department_id = 80;
Ex. 8) TRIM
•
Tira um conjunto de caracteres de uma String.
- Exibe o sobrenome do funcionário extraindo a letra 'r' quando houver:
SELECT last_name "Sobrenome",
TRIM('R' FROM last_name) "Sem a letra R"
FROM employees
WHERE department_id = 30;
• FUNÇÕES NUMÉRICAS
Ex. 1) ROUND (coluna, d)
•
Arredonda o valor ou coluna especificada, em que d indica o número de casas decimais desejado. Se d for negativo, significa que o valor à esquerda do ponto decimal será arredondado. Arredonda para cima.
SELECT ROUND(45.953,2),
ROUND(45.953,0),
ROUND(45.923,-1),
ROUND(45.99)
FROM DUAL;
Ex. 2) TRUNC (coluna, d)
•
Trunca o valor ou coluna especificada, em q d indica o número de casas decimais desejado. Se d for negativo, significa que o valor à esquerda do ponto decimal será truncado. Arredonda para a mesma dezena.
SELECT TRUNC(45.953,2),
TRUNC(45.953,0),
TRUNC(45.953,-1),
TRUNC(45.99)
FROM dual;
Ex. 3) MOD (a, b)
•
Retorna o resto da divisão de a e b, em que a e b podem ser constantes, colunas ou expressões.
SELECT MOD(450,2), MOD(432.666,3)
FROM dual;
•
FUNÇÕES DE GRUPO
Ex. 1)
AVG (coluna) – Média dos valores de uma coluna.
SUM (coluna) - Soma dos valores de uma coluna.
MIN (coluna) – Valor mínimo de uma coluna.
MAX (coluna) – Valor máximo de uma coluna.
- Exibe a média salarial, menor e o maior salário e a soma salarial dos funcionários da tabela EMPLOYEES:
SELECT AVG(salary) "Media",
MIN(salary) "Menor",
MAX(salary) "Maior",
SUM(salary) "Soma"
FROM employees;
Ex. 2) COUNT (coluna) - Total de linhas selecionadas.
- Exibe a quantidade de empregados da empresa:
SELECT COUNT(*) "Qtde",
COUNT(ROWID) "Qtde de linhas"
FROM employees;
Obs. Usamos o ROWID para melhorar a recuperação da quantidade de registros.
Ex. 3) IS NULL - Exibe o campo nulo.
SELECT employee_id "ID Cargo",
department_id "Depto"
FROM employees
WHERE department_id IS NULL;
Ex. 4)
STDDEV – Desvio padrão
VARIANCE – variância
SELECT STDDEV(salary) "Desvio padrão",
VARIANCE(salary) "Variância"
FROM employees;
Ex. 5) MEDIAN – Mediano
SELECT MEDIAN(salary) "Mediano",
AVG(salary) "Média"
FROM employees;
Ex. 6) GROUP BY - Usado para classificar os grupos.
SELECT job_id "Cargo",
AVG(salary) "Media",
MIN(salary) "Menor",
MAX(salary) "Maior"
FROM employees
GROUP BY job_id;
No exemplo, a coluna job_id não tem função de grupo, por isso precisa colocar no GROUP BY.
Ex. 7) ORDER BY – Usado para ordenar por determinada coluna.
SELECT department_id,
SUM(salary),
COUNT(ROWID)
FROM employees
GROUP BY department_id
ORDER BY 1;
No exemplo acima, a consulta é ordenada pela coluna department_id na ordem crescente.
Ex. 8) Aninhando funções
SELECT MAX(AVG(salary)) "Maior salario medio"
FROM employees
GROUP BY department_id;
Ex. 9) HAVING – Usado para restringir grupos.
SELECT department_id “Depto”,
SUM(salary) “Soma salario”
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 20000
ORDER BY 1;
•
FUNÇÕES DE DATA
Ex. 1) MONTHS_BETWEEN – Número de meses entre duas datas.
SELECT MONTHS_BETWEEN(SYSDATE,HIRE_DATE) "Numero de meses"
FROM employees;
Ex. 2)
SELECT SYSDATE "Data atual",
hire_date "Dt. Adm",
MONTHS_BETWEEN(sysdate, hire_date) "Qt. Meses",
ROUND ((MONTHS_BETWEEN(sysdate, hire_date))/12) "Anos"
FROM employees;
Ex. 3) LAST DAY – Último dia do mês.
SELECT sysdate "Data atual", hire_date "Data Admissao",
LAST_DAY(hire_date) "Ultimo dia do mes"
FROM employees
WHERE department_id = 30;
Ex. 4) TRUNC – Data truncada – 1º dia do mês
SELECT SYSDATE "Data atual", LAST_DAY(SYSDATE) "Ultimo dia",
TRUNC(SYSDATE, 'MONTH') "Primeiro dia"
FROM DUAL;
Ex. 5) NEXT DAY – Dia seguinte a data especificada.
SELECT sysdate,
NEXT_DAY(sysdate, 'QUARTA-FEIRA')
FROM DUAL;
Ex. 6) ADD_MONTHS – Adiciona meses de calendário para a data.
SELECT sysdate,
ADD_MONTHS(sysdate,4) "Adiciona Mes",
ADD_MONTHS(sysdate,-2) "Subtrai Mes"
FROM DUAL;
Ex. 7)
TRUNC – Data truncada.
ROUND – Data de arredondamento.
SELECT sysdate "Data atual",
ROUND(sysdate,'YEAR') "Ano arredondado para cima",
TRUNC(sysdate,'YEAR') "Ano arredondado para baixo",
ROUND(sysdate,'MONTH') "Mes arredondado para cima",
TRUNC(sysdate,'MONTH') "Mes arredondado para baixo"
FROM DUAL;
Ex. 8)
Exibe todos os funcionários que trabalham há mais de 150 meses
SELECT MONTHS_BETWEEN(SYSDATE,HIRE_DATE)
FROM EMPLOYEES
WHERE MONTHS_BETWEEN(sysdate,HIRE_DATE) > 150
ORDER BY 1 DESC;
•
FUNÇÕES DE CONVERSÃO
TO_CHAR, TO_NUMBER, TO_DATE:
A)
Com datas:
SELECT SYSDATE,
TO_CHAR(sysdate,'MONTH'),
TO_CHAR(sysdate,'MM'),
TO_CHAR(sysdate,'MON'),
TO_CHAR(sysdate,'YEAR'),
TO_CHAR(sysdate,'YY'),
TO_CHAR(sysdate,'YYYY'),
TO_CHAR(sysdate,'RR'),
TO_CHAR(sysdate,'RRRR'),
TO_CHAR(sysdate,'DAY'),
TO_CHAR(sysdate,'DY'),
TO_CHAR(sysdate,'DD'),
TO_CHAR(sysdate,'HH12'),
TO_CHAR(sysdate,'HH24'),
TO_CHAR(sysdate,'MI'),
TO_CHAR(sysdate,'SS')
FROM DUAL;
Formatos:
MONTH – Mês por extenso.
MM – Mês com duas letras.
MON – Mês com três letras.
YEAR – Ano por extenso.
YY – Ano com dois dígitos (RR).
YYYY – Ano com quatro dígitos (RRRR).
DAY – Dia da semana por extenso.
DY – Dia da semana abreviado.
DD – Dia da semana por número.
HH12 – De 0 a 12 (AM/PM)
HH24 – De 0 a 24
MI – Minutos.
SS – Segundos.
Ex. :
SELECT hire_date "Data Admissao",
TO_CHAR (hire_date,'YEAR') "Ano por extenso",
TO_CHAR (hire_date,'MONTH') "Mes por extenso",
TO_CHAR (hire_date,'HH24:MI:SS') "Horario"
FROM employees;
B)
Com valores:
SELECT TO_CHAR(salary, '99,999.99'),
TO_CHAR(salary, '00999'),
TO_CHAR(salary, '$9999.00'),
TO_CHAR(salary, '00999'),
TO_CHAR(salary, 'L9999.99')
FROM employees
WHERE department_id = 10;
•
JUNÇÃO DE TABELAS
Padrão Oracle
EQUIJOIN - Junção de colunas idênticas nas tabelas:
SELECT d.department_name, e.first_name
FROM departments d, employees e
WHERE e.department_id = d.department_id;
NOEQUIJOIN - Ocorre quando não ha coluna em igualdade e sim corresponde a um intervalo que corresponde a duas colunas da tabela.
SELECT e.last_name, e.salary, j.min_salary, j.max_salary
FROM employees e, jobs j
WHERE e.job_id = j.job_id
AND e.salary BETWEEN j.min_salary AND j.max_salary
AND e.department_id = 80;
SELFJOIN (JUNÇÃO EXTERNA) - Esta junção exibe mesmo quando não ha correspondência na tabela associada.
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e, departments d
WHERE e.department_id(+) = d.department_id;
Obs.: A consulta acima exibe os departamentos que não tem funcioná
SELECT e.employee_id, e.last_name, e.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id(+);
Obs.: A consulta acima exibe o empregado que não esta em departamento algum.
AUTO-JUNÇÃO
SELECT a.last_name || ' trabalha para ' || b.last_name
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
A consulta acima exibe o nome do empregado e o nome do gerente que o chefia.
OBS.: Usada quando queremos exibir hierarquia. No exemplo acima o gerente também é um empregado registrado na tabela employees. Portanto, trazemos a mesma tabela 2 vezes uma com apelido para exibir empregado e outra pra exibir informações do gerente.
SELECT a.last_name "Empregado", a.manager_id "Id Gr",
b.employee_id "Id Empr", b.last_name "Ger."
FROM employees a, employees b
WHERE a.manager_id = b.employee_id;
Padrão Ansi
CROSS JOIN - exibe um produto cartesiano entre as duas tabelas
Usado: quantidade de dados para teste
Ocorre na falta de um join ou em erro de join
Ex 1) Exibir o nome do funcionario e o nome do seu cargo
SELECT last_name, job_title
FROM employees CROSS JOIN jobs;
NATURAL JOIN (JUNÇÃO NATURAL) - Nesta junção ‚ necessário informar a coluna que será feita a junção. Irá buscar colunas idênticas com valores de conteúdos iguais.
Ex 2) Exibir o nome do departamento e a cidade que o departamento está alocado:
SELECT department_name, city
FROM departments NATURAL JOIN locations;
CLAUSULA USING - informa a coluna que dever ser utilizada pelo join.
SELECT e.last_name, j.job_title
FROM employees e JOIN jobs j
USING (job_id);
CLAUSULA ON - usado para deixar a junção mais legível.
SELECT e.employee_id, e.last_name, d.department_id, d.department_name
FROM employees e JOIN departments d
ON (e.department_id = d.department_id);
JUNÇÃO COM 3 TABELAS NO PADRAO ANSI
SELECT e.employee_id, e.first_name, d.department_name, l.city
FROM employees e JOIN departments d
ON d.department_id = e.department_id
JOIN locations l
ON l.location_id = d.location_id;
OUTER JOIN:
LEFT - Exibe todas as informações da tabela a esquerda mesmo que não tenha a direita.
Ex.
SELECT e.first_name || ' ' ||e.last_name "Nome",
d.department_name "Departamento"
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
A consulta acima exibe o nome do empregado e o nome do departamento de todos os empregados mesmo que não esteja alocado em nenhum departamento.
RIGHT - exibe todas as informações da tabela a direita mesmo que não tenha a esquerda.
SELECT e.last_name "Sobrenome",
e.job_id "Cargo",
d.department_id "Depto",
d.department_name "Nome Depto"
FROM employees e RIGHT OUTER JOIN departments d
ON (e.department_id = d.department_id);
A consulta acima exibe o sobrenome, o cargo, o id do departamento e o nome do departamento de todos os funcionários; inclusive dos departamentos que não possuem funcionário algum.
FULL - Exibe todas as linhas da esquerda e da direita mesmo que não haja correspondência.
LEFT + RIGHT
SELECT e.first_name || ' '||e.last_name "Nome", e.job_id "Cargo",
d.department_id "Depto", d.department_name "Nome Depto"
FROM employees e FULL OUTER JOIN departments d
ON e.department_id = d.department_id;
A consulta acima exibe os funcionários que não trabalham em nenhum departamento e os departamentos que não possuem empregado.
SUBCONSULTAS
Ex. 1)
SELECT last_name, salary
FROM employees
WHERE salary > (SELECT salary FROM employees
WHERE last_name = 'Abel');
OBS.: Há 2 tipos de subconsulta:
a)
Uma linha (a subconsulta retorna somente 1 registro);
Operadores: >, <, >=, <=, !=, <>, =
b)
Mais de uma linha (subquerie retorna mais de 1 registro);
Operadores: IN, SOME, ALL, ANY
Ex. 2) SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE job_id = (SELECT job_id FROM employees
WHERE employee_id = 141)
AND salary > (SELECT salary FROM employees
WHERE employee_id = 143);
Ex. 3) SELECT employee_id, last_name, job_id
FROM employees
WHERE job_id = (SELECT job_id FROM employees
WHERE employee_id = 176);
Ex. 4) SELECT last_name, department_id
FROM employees
WHERE department_id = (SELECT department_id FROM employees
WHERE employee_id = 101);
Ex. 5) SELECT e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND e.department_id = (SELECT department_id
FROM employees
WHERE employee_id = 101)
AND e.employee_id <> 101
ORDER BY 1;
OBS.: A ultima condição traz todos os funcionários menos o funcionário de numero 101.
Ex. 6) SELECT last_name "Nome", salary "Salario"
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
Ex. 7) SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) > (SELECT MIN(salary)
FROM departments
WHERE department_id = 50);
Ex. 8) Usando variável. O departamento será digitado.
SELECT department_id, MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary) >=(SELECT MIN(salary)
FROM departments
WHERE department_id = &dept);
Ex. 9)
Exibe o cargo com menor salário médio:
SELECT job_id "Cargo", AVG(salary) "Media"
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);
OPERADORES DE MAIS DE UMA LINHA: IN, ANY, ALL
SELECT employee_id, last_name, salary, job_id
FROM employees
WHERE salary
AND job_id != 'IT_PROG';
>ANY - Exibe o valor maior que o mínimo
OPERADOR ALL
A) >ALL - Exibe o maior que o máximo valor
B)
Exibe os funcionários cujo salário seja menor que o salário de todos os funcionários com um cargo de IT_PROG e cujo cargo não seja este:
SELECT last_name, salary
FROM employees
WHERE salary
AND job_id <> 'IT_PROG';
Ex.) Exibir todos os funcionários que não possuem subordinado:
SELECT e.last_name FROM employees e
WHERE e.employee_id NOT IN (SELECT g.manager_id
FROM employees g
WHERE manager_id IS NOT NULL);
Não citei a fonte, pois era uma anotação antiga do notepad.
caso encontrem a fonte, favor me enviar para atualizar o post.
Até a proxima.
