Linguagem SQL em Ambiente Oracle SQL Plus

Prévia do material em texto

�PAGE �19�
LINGUAGEM SQL EM AMBIENTE ORACLE SQL*Plus
2LINGUAGEM SQL	
BENEFÍCIOS	2
AMBIENTE SQL	2
COMO ENTRAR NO AMBIENTE?	2
LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL)	3
LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML)	3
OPERAÇÕES DE RECUPERAÇÃO: QUERY	4
RECUPERAÇÃO SIMPLES	4
A) RECUPERAÇÃO USANDO EXPRESSÕES ARITMÉTICAS	5
B) APELIDOS DE COLUNAS	6
C) OPERADOR DE CONCATENAÇÃO	6
D) MANUSEIO DE VALORES NULOS	7
CLÁUSULA DISTINCT	8
CLÁUSULA ORDER BY	9
CLÁUSULA WHERE	11
OPERADORES SQL	14
FUNÇÕES INTEGRADAS NO SQL	16
A) FUNÇÕES PARA MANIPULAR CARACTERES	16
B) FUNÇÕES PARA MANIPULAR NÚMEROS	17
C) FUNÇÕES PARA MANIPULAR DATAS	18
D) FUNÇÕES DE CONVERSÃO	20
E) FUNÇÕES PARA MANIPULAR GRUPOS	22
CLÁUSULA GROUP BY	24
CLÁUSULA HAVING	25
SUBQUERIES (SUBCONSULTAS)	25
A) SUBQUERY DE UMA LINHA	26
B) SUBQUERIES QUE RETORNAM MAIS DE UMA LINHA DA TABELA	26
C) CLÁUSULA HAVING COM SUBQUERIES ANINHADAS	28
EXTRAIR DADOS DE MAIS DE UMA TABELA	29
JOIN-EQUI-JOIN (SIMÉTRICO)	29
OPERADORES DE CONJUNTOS (SET OPERATORS)	30
REGRAS PARA UTILIZAR UNION, INTERSECT e MINUS	31
LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL)	31
CRIAR UMA TABELA	31
CRIAR UMA TABELA UTILIZANDO A ESTRUTURA DE OUTRA	32
DATA MANIPULATION LANGUAGE – DML	33
A) INSERIR LINHAS NA TABELA	33
A.1) UTILIZANDO SUBSTITUIÇÃO DE VARIÁVEIS	33
A.2) COPIAR LINHAS (ROWS) DE OUTRA TABELA	33
B) ALTERAR LINHAS DA TABELA - SINTAXE	34
C) EXCLUIR LINHAS DA TABELA - SINTAXE	34
�
LINGUAGEM SQL
A linguagem SQL é própria para a realização de operações como recuperar dados (QUERY), atualizar ou eliminar dados, alterar colunas de tabelas, criar e eliminar tabelas e qualquer outras modificações que se deseje fazer em um banco de dados relacional.
Os comandos SQL podem ser divididos em quatro grupos de comandos:
Query;
Definição de dados;
Manipulação de dados;
Controle de dados;
Em 1986 a ANSI adotou o SQL como uma linguagem padrão para banco de dados relacional - RDBMS.
BENEFÍCIOS
É uma linguagem não procedural;
Permite trabalhar com várias tabelas;
Permite utilizar o resultado de uma instrução SQL em outra instrução SQL (sub-queries);
Não necessita especificar o método de acesso ao dado;
É uma linguagem para vários usuários como:
A) Administrador do sistema;
B) Administrador do banco de dados (DBA);
C) Programadores de aplicações;
D) Pessoal de gerência e tomada de decisão;
É de fácil aprendizado;
Permite a utilização dentro de uma linguagem procedural como C, COBOL, FORTRAN, Pascal, Java – através de SQL embutida.
LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL)
Um banco de dados projetado através do modelo ER, deve ser mapeado para um esquema relacional através de uma linguagem de definição de dados (DDL). A linguagem que será utilizada neste contexto será SQL padrão ANSI/ISO.
COMANDOS
CREATE TABLE
Cria uma tabela e define suas colunas e outras propriedades.
CREATE VIEW
Define uma visão sobre uma ou mais tabelas e /ou sobre outras visões.
CREATE INDEX
Cria índice para uma tabela.
ALTER TABLE
Inclui ou redefine uma coluna numa tabela existente (não consta no padrão ANSI).
DROP TABLE
Exclui uma tabela.
DROP VIEW
Exclui uma visão.
DROP INDEX
Exclui um índice.
LINGUAGEM DE MANIPULAÇÃO DE DADOS (DML)
A manipulação do banco de dados é feita através de uma ou mais linguagens comerciais, que devem respeitar as restrições de integridade do banco de dados. A linguagem utilizada neste contexto será o SQL.
COMANDOS
SELECT
Seleciona linhas e colunas de uma ou mais tabelas.
INSERT
Inclui linhas numa tabela ou visão.
UPDATE
Muda o valor de uma ou mais campos de uma tabela.
DELETE
Exclui linhas de uma tabela.
OPERAÇÕES DE RECUPERAÇÃO: QUERY
A estrutura básica para a recuperação de informações do banco de dados, consiste de 2 cláusulas:
FORMATO: 
SELECT coluna1, coluna2, ..., colunan 
FROM nome-da-tabela;
A cláusula SELECT relaciona as colunas que se quer presentes no resultado da recuperação.
A cláusula FROM relaciona a tabela ou tabelas que devem ser pesquisadas na execução da query.
O SELECT e o FROM são necessários em todas as consultas e sempre devem aparecer antes de qualquer outra cláusula.
O resultado de uma recuperação SQL é sempre uma tabela.
Para recuperar todos os valores de todas as colunas da tabela, usa-se o asterisco (*) após a palavra SELECT.
RECUPERAÇÃO SIMPLES
SELECT * FROM emp;
ou
SELECT empno, ename, job, mgr, hiredate, sal, comm, depto 
FROM emp;
	EMPNO
	ENAME
	JOB
	MGR
	HIREDATE
	SAL
	COMM
	DEPTO
	----------
	-----------
	--------------
	------
	--------------
	-------
	---------
	---------
	7369
	SMITH
	CLERK
	7902
	17-DEC-80
	800
	
	20
	7499
	ALLEN
	SALESMAN
	7698
	20-FEB-81
	1600
	300
	30
	7521
	WARD
	SALESMAN
	7696
	22-FEB-81
	1250
	500
	30
	7566
	JONES
	MANAGER
	7839
	02-APR-81
	2975
	
	20
	7654
	MARTIN
	SALESMAN
	7698
	28-SEP-81
	1250
	1400
	30
	7698
	BLAKE
	MANAGER
	7839
	01-MAI-81
	2850
	
	30
	7782
	CLARK
	MANAGER
	7839
	09-JUN-81
	2450
	
	10
	7782
	SCOTT
	ANALYST
	7566
	09-DEC-82
	3000
	
	20
	7839
	KING
	PRESIDENT
	
	17-NOV-81
	5000
	
	10
	7844
	TURNER
	SALESMAN
	7698
	08-SEP-81
	1500
	500
	30
	7876
	ADAMS
	CLERK
	7788
	12-JAN-83
	1100
	
	20
	7900
	JAMES
	CLERK
	7698
	03-DEC-91
	950
	
	30
	7902
	FORD
	ANALYST
	7566
	03-DEC-81
	3000
	
	20
	7934
	MILLER
	CLERK
	7782
	23-JAN-82
	1300
	
	10
14 rows selected.
A linguagem SQL suporta as seguintes expressões simples:
ADIÇÃO 		( + )
SUBTRAÇÃO		( - )
MULTIPLICAÇÃO	( * )
DIVISÃO		( / )
Observação:
Caso a expressão aritmética contenha mais de um operador, a prioridade é *, / e depois +, -, sempre da esquerda para a direita quando existir vários operadores com a mesma prioridade.
Também podem ser utilizados parênteses para especificar a ordem de execução dos operadores.
A) RECUPERAÇÃO USANDO EXPRESSÕES ARITMÉTICAS
Recuperar os números dos empregados, salários e salário anual.
		SQL> SELECT empno, sal, sal*12 FROM emp;
	EMPNO 
	SAL
	 SAL*12
	---------- 
	-------
	-----------
	7369
	800
	9600
	7499
	1600
	19200
	7521
	1250
	15000
	7566
	2975
	35700
	7654
	1250
	15000
	7698
	2850
	34200
	7782
	2450
	29400
	7788
	3000
	36000
	7839
	5000
	60000
	7844
	1500
	18000
	7876
	1100
	13200
	7900
	950
	11400
	7902
	3000
	36000
	7934
	1300
	15600
14 rows selected
B) APELIDOS DE COLUNAS
Para criar um apelido para uma coluna, entre com o apelido depois do nome da coluna no comando SELECT.
SQL> SELECT ename, sal * 12 SAL_ANUAL, comm FROM emp;
	ENAME
	SAL_ANUAL
	COM
	-----------
	------------------
	----------
	SMITH
	9600
	
	ALLEN
	19200
	300
	WARD
	15000
	500
	JONES
	35700
	
	MARTIN
	15000
	1400
	.
	.
	.
	.
	.
	.
	.
	.
	.
	MILLER
	15600
	
C) OPERADOR DE CONCATENAÇÃO
O operador de concatenação permite ligar uma coluna a outra.
SQL> SELECT empno||ename EMPREGADO FROM emp;
	EMPREGADO
	--------------------------
	7369SMITH
	7499ALLEN
	7521WARD
	7566JONES
	.
	.
	.
	7934MILLER
D) MANUSEIO DE VALORES NULOS
Sempre que existir um valor nulo na tabela, este deve ser tratado, pois toda expressão feita com valor nulo o resultado é nulo.
Um valor nulo é diferente de zero.
A função NVL converte um valor nulo para outro valor numérico.
		SQL> SELECT ename, sal, sal * 12 + comm SAL_ANUAL FROM emp;
	ENAME
	SAL
	SAL_ANUAL
	-----------
	---------
	---------------
	SMITH
	800
	
	ALLEN
	1600
	19500
	WARD
	1250
	15500
	JONES
	2975
	
	MARTIN
	1250
	16400
	.
	.
	.
	.
	.
	.
	.
	.
	.
	MILLER
	1300
	
SQL> SELECT ename, sal, sal * 12 + NVL(comm,0) SAL_ANUAL FROM emp;
	ENAME
	SAL
	SAL_ANUAL
	--------------------
	---------------
	SMITH
	800
	9600
	ALLEN
	1600
	19500
	WARD
	1250
	15500
	JONES
	2975
	35700
	.
	.
	.
	.
	.
	.
	.
	.
	.
	MILLER
	1300
	15600
A função NVL (comm, 0) substitui por 0 os valores nulos, para que não afetem a soma. Na multiplicação utiliza-se 1 no lugar de um valor nulo para não afete a multiplicação.
CLÁUSULA DISTINCT
A cláusula distinct é utilizada para eliminar os valores duplicados na saída (no resultado).
A) Recuperação não utilizando a cláusula DISTINCT:
Obtenha todos os números dos departamentos da tabela EMP.
SQL> SELECT deptno FROM emp;
	DEPTNO
	--------------
	20
	30
	30
	20
	30
	30
	10
	20
	10
	30
	20
	30
	20
	10
14 rows selected.
A) Recuperação utilizando a cláusula DISTINCT:
Obtenha todos os números dos departamentos da tabela EMP.
SQL> SELECT DISTINCT deptno FROM emp;
	DEPTNO
	-----------
		10
		20
		30
DISTINCT pode ser utilizado para múltiplas colunas.
SQL> SELECT DISTINCT deptno, job FROM emp;
	DEPTNO
	JOB
	-----------
	---------------
	10
	CLERK
	10
	MANAGER
	10
	PRESIDENT
	20
	ANALYST
	20
	CLERK
	20
	MANGER
	30
	SALESMAN
7 rows selected.
CLÁUSULA ORDER BY
A cláusula ORDER BY é utilizada para ordenar (classificar) as linhas da tabela .
A ordem do resultado pode aparecer em ordem decrescente (DESC) ou ascendente (ASC) que é default.
A cláusula order by sempre deve aparecer por último no comando select.
A) Obtenha o nome, a profissão, o solário de todos os empregados em ordem ascendente de nome do empregado.
SQL> SELECT ename, job, sal FROM emp ORDER BY ename;
	ENAME
	JOB
	SAL
	-----------
	------------------
	--------
	ADAMS
	CLERK
	1100
	ALLEN
	SALESMAN
	1600
	BLAKE
	MANAGER
	2850
	CLARK
	MANAGER
	2450
	FORD
	ANALYST
	3000
	JAMES
	CLERK
	950
	JONES
	MANAGER
	2975
	KING
	PRESIDENT
	5000
	MARTIN
	SALESMAN
	1250
	MILLER
	CLERK
	1300
	SCOTT
	ANALYST
	3000
	SMITH
	CLERK
	800
	TURNER
	SALESMAN
	1500
	WARD
	SALESMAN
	1250
14 rows selected.
B) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de nome do empregado.
SQL> SELECT ename, job, sal FROM emp ORDER BY ename DESC;
	ENAME
	JOB
	SAL
	-----------
	--------------
	---------
	WARD
	SALESMAN
	1250
	TURNER
	SALESMAN
	1500
	SMITH
	CLERK
	800
	SCOTT
	ANALYST
	3000
	MILLER
	CLERK
	1300
	MARTIN
	SALESMAN
	1250
	KING
	PRESIDENT
	5000
	JONES
	MANAGER
	2975
	JAMES
	CLERK
	950
	FORD
	ANALYST
	3000
	CLARK
	MANAGER
	2450
	BLAKE
	MANAGER
	2850
	ALLEN
	SALESMAN
	1600
	ADAMS
	CLERK
	1100
14 rows selected.
C) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e crescente de salário.
SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, SAL
	ENAME
	JOB
	SAL
	-----------
	---------------
	---------
	WARD
	SALESMAN
	1250
	MARTIN
	SALESMAN
	1500
	TURNER
	SALESMAN
	1500
	ALLEN
	SALESMAN
	1600
	KING
	PRESIDENT
	5000
	CLARK
	MANEGER
	2450
	BLAKE
	MANAGER
	2850
	JONES
	MANAGER
	2975
	ADAMS
	CLERK
	1100
	MILLER
	CLERK
	1300
	SCOTT
	ANALYST
	3000
	FORD
	ANALYST
	3000
14 rows selected.
D) Obtenha o nome, a profissão e o salário de todos os empregados em ordem decrescente de profissão e salário.
SQL> SELECT ename, job, sal FROM emp ORDER BY job DESC, sal DESC;
	ENAME
	JOB
	SAL
	--------------
	----------------
	-------
	ALLEN
	SALESMAN
	1600
	TURNER
	SALESMAN
	1500
	WARD
	SALESMAN
	1250
	MARTIN
	SALESMAN
	1250
	KING
	PRESIDENT
	5000
	JONES
	MANAGER
	2975
	BLAKE
	MANEGER
	2850
	CLARK
	MANAGER
	2450
	MILLER
	CLERK
	1300
	ADAMS
	CLERK
	1100
	JAMES
	CLERK
	950
	SMITH
	CLERK
	800
	SCOTT
	ANALYST
	3000
	FORD
	ANALYST
	3000
14 rows selected.
CLÁUSULA WHERE
A cláusula WHERE é utilizada para especificar a condição ou condições que as linhas selecionadas devem satisfazer.
As condições da cláusula WHERE podem ser valores de colunas, valores literais (seqüência de caracteres), expressões aritméticas ou funções.
Seqüência de caracteres e datas devem ser englobadas entre aspas simples.
WHERE especifica a condição desejada, que pode assumir os operadores =, !=, >, >=, <, <=; operadores booleanos AND, OR e NOT; e parênteses para indicar uma ordem desejada.
OPERADORES LÓGICOS
A cláusula WHERE pode assumir os seguintes operadores lógicos:
	Igual
	( = )
	Maior
	( > )
	Maior ou Igual
	( >= )
	Menor 
	( < )
	Menor ou Igual 
	( <= )
SQL> SELECT ename, sal, FROM emp WHERE deptno = 10;
	ENAME
	SAL
	--------------
	-------
	CLARK
	2450
	KING
	5000
	MILLER
	1300
SQL> SELECT ename, sal, FROM emp WHERE sal > 2000;
	ENAME
	SAL
	-----------
	------
	JONES
	2975
	BLAKE
	2850
	CLARK
	2450
	SCOTT
	3000
	KING
	5000
	FORD
	3000
6 rows selected.
OPERADORES BOOLEANOS
A cláusula WHERE pode assumir os seguintes operadores booleanos:
AND;
OR;
NOT.
AND - As duas condições devem ser satisfeitas para que a consulta retorne uma ou mais linhas da tabela.
Sintaxe:
SELECT colunas FROM nome-da-tabela WHERE condição1 AND condição2;
Recuperação Qualificada: 
Obtenha os números dos empregados do departamento 20 com salário maior que 1500.
SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 AND sal > 1500;
	ENAME
	SAL
	DEPTNO
	-----------
	------
	-----------
	JONES
	2975
	20
	SCOTT
	3000
	20
	FORD
	3000
	20
OR - Quando uma ou ambas as condições forem satisfeitas.
Sintaxe:
SELECT colunas FROM nome-da-tabela WHERE condição1 OR condição2;
Obtenha os números dos empregados do departamento 20 ou que possuem salário maior que 1500.
SQL> SELECT ename, sal, depto FROM emp WHERE deptno = 20 OR sal > 1500;
	ENAME
	SAL
	DEPTNO
	-----------
	---------
	-----------
	SMITH
	800
	20
	ALLEN
	1600
	30
	JONES
	2975
	20
	BLAKE
	2850
	30
	CLARK
	2450
	10
	SCOTT
	3000
	20
	KING
	5000
	10
	ADAMS
	1100
	20
	FORD
	3000
	20
9 rows selected.
USANDO AND E OR NA MESMA CONSULTA
Ao usar AND e OR na mesma consulta, você geralmente deve usar parêntese para tornar a consulta mais clara. A colocação dos parênteses pode alterar completamente o resultado.
SQL> SELECT ename, job, hiredate, sal, deptno 
FROM emp 
WHERE sal > 500 AND job ='CLERK' AND 
(hiredate='03-DEC-81' OR hiredate='17-DEC-80);
	ENAME
	 JOB
	HIREDATE 
	SAL 
	DEPTNO
	----------
	---------
	--------------
	------
	-----------
	SMITH
	CLERK
	17-DEC-80
	800
	20
	JAMES
	CLERK
	03-DEC-81
	950
	30
2 rows selected.
SQL> SELECT ename, job, hiredate, sal, deptno 
FROM emp
WHERE sal > 500
AND job = 'CLERK'
OR (hiredate = '03-DEC-81' AND hiredate = '17-DEC-80');
	ENAME
	 JOB
	HIREDATE 
	SAL 
	DEPTNO
	----------
	---------
	-------------
	------
	-----------
	SMITH
	CLERK
	17-DEC-80
	800
	20
	ADAMS
	CLERK
	12-JAN-83
	1100
	20
	JAMES
	CLERK
	03-DEC-81
	950
	30
	MILLER
	CLERK
	23-JAN-82
	1300
	10
OPERADORES SQL
A) Operador BETWEEN ...AND ...
O operador BETWEEN..AND... testa os valores da coluna, e recupera os valores que estão entre um valor mínimo e um valor máximo inclusive especificados no BETWEEN.
Obtenha os nomes e salários dos empregados que possuírem salários entre os valores 1000 e 2000.
SQL> SELECT ename, sal
FROM emp
WHERE sal BETWEEN 1000 AND 2000;
	ENAME 
	SAL
	-----------
	-------
	ALLEN
	1600
	WARD
	1250
	MARTIN
	1250
	TURNER
	1500
	ADAMS
	1100
	MILLER
	1300
6 rows selected.
B) Operador INO operador IN testa os valores da coluna, e recupera somente os valores que estão na lista de valores especificados pelo operador IN.
Obtenha os números, os salários e os gerentes dos empregados que possuírem os gerentes com os números 7902, 7566, 7788.
SQL> SELECT empno, sal, mgr
FROM emp
WHERE mgr in (7902, 7566, 7788);
		EMPNO
	SAL
	MGR
	----------
	------
	-------
	7369
	800
	7902
	7788
	3000
	7566
	7876
	1100
	7788
	7902
	3000
	7566
4 rows selected.
Observação
Caso sejam usados caracteres ou data na lista, especificá-los entre aspas simples.
C) Operador LIKE
O operador LIKE permite recuperar linha da tabela sem saber exatamente o valor que está sendo procurado, utilizando para isso os símbolos (%) e ( _ ).
O símbolo de sublinhado ( _ ) é usado para representar um único caractere.
O símbolo de percentagem (%) é usado para representar uma cadeia de caracteres de qualquer tamanho (inclusive zero).
Obtenha os nomes dos empregados que iniciam seu nome com S.
SQL> SELECT ename
FROM emp
WHERE ename LIKE 'S%';
	ENAME
	------------------------------
	SMITH
	SCOTT
2 rows selected.
Observação
O LIKE só pode ser usado com cadeia de caracteres ou com dados gráficos, e não com dados numéricos.
D) Operador IS NULL
O operador IS NULL testa os valores da coluna, e recupera somente as linhas da tabela que possuírem o valor null.
Obtenha os números dos empregados que não possuírem gerente, ou seja o campo número do gerente (mgr) é igual a nulo.
SQL> SELECT ename, mgr
FROM emp
WHERE mgr IS NULL;
	ENAME
	MGR
	--------------
	-------
	KING
	
E) Utilizando a negação (NOT)
Os operadores BETWEEN...AND..., IN, LIKE, e IS NULL podem ser combinados com o operador de negação NOT.
Operador			Significado
NOT BETWEEN		NÃO ENTRE DOIS VALORES ESPECIFICADOS
NOT IN				NÃO ENTRE UMA LISTA DE VALORES
NOT LIKE			NÃO ENTRE UM PADRÃO ESPECIFICADO
IS NOT NULL			NÃO IGUAL AO VALOR NULL
FUNÇÕES INTEGRADAS NO SQL
As funções são utilizadas para manipular campos (itens de dados). Estas funções utilizam um ou mais argumentos, retornando um valor.
Um argumento pode ser uma constante, variável ou uma coluna.
FORMATO
function_name(argumento1, argumento2, ...)
A) FUNÇÕES PARA MANIPULAR CARACTERES
a.1) LOWER
Converte em letras minúsculas.
Exemplo:
Obtenha o nome de todos os departamentos em letras minúsculas.
SQL> SELECT LOWER(dname) 
FROM dept;
a.2) UPPER
Converte em letras maiúsculas.
Exemplo:
Obtenha o nome de todos os departamentos em letras maiúsculas.
SQL> SELECT UPPER(dname)
FROM dept;
a.3) INITCAP
Converte a primeira letra de cada palavra para maiúscula.
Exempo:
Obtenha o nome e a localização de todos os departamentos, mostrando a primeira letra do nome e localização em maiúscula.
SQL> SELECT INITCAP(dname), INITCAP(LOC)
FROM dept;
a.4) LENGTH
Retorna o número de caracteres ou dígitos de uma coluna ou de uma cadeia de caracteres (literal).
Exemplo
Obtenha o número de caracteres do literal 'SQL COURSE', das colunas deptno e dname.
SQL> SELECT LENGTH('SQL COURSE'), LENGTH(deptno), LENGTH(dname)
FROM dept;
B) FUNÇÕES PARA MANIPULAR NÚMEROS
b.1) ROUND
Retorna o arredondamento de colunas, expressões ou valores para n casas decimais. Se n é omitido, nenhuma casa decimal é colocada.
Exemplo:
SQL> SELECT ROUND(45.923,1), ROUND(45.923), ROUND(SAL/32,2)
FROM emp
WHERE deptno = 10;
SQL>SELECT ROUND(45.473), ROUND(45.4634,2)
FROM dual;
b.2) TRUNC
Retorna o truncamento de colunas, expressões ou valores em n casas decimais. Se n é omitido nenhuma casa decimal é truncada.
Exemplo:
SQL> SELECT TRUNC(45.923, 1), TRUNC(45.923), TRUNC(45.452), TRUNC(SAL/32,2)
FROM emp
WHERE deptno = 10;
b.3) SQRT
Retorna a raiz quadrada de uma coluna ou valor. Se a coluna ou valor forem menores que zero, então retorna um valor nulo.
Exemplo:
SQL> SELECT sal, SQRT(sal), comm, SQRT(comm), SQRT(144)
FROM emp
WHERE comm > 0;
b.4) SIGN
Retorna -1 se a coluna, ou expressão, ou valor forem negativos;
Retorna 0 se a coluna, ou expressão, ou valor forem zero;
Retorna 1 se a coluna, ou expressão, ou valor forem positivos;
Exemplo:
SQL> SELECT sal - comm, sign(sal - comm), comm - sal, sign(comm - sal)
FROM emp
WHERE deptno = 30;
b.5) ABS
Retorna o valor absoluto de uma coluna ou valor.
Exemplo:
SQL> SELECT sal, comm, comm-sal, ABS(com-sal), ABS(-35)
FROM emp
WHERE comm > 0;
b.6) MOD (valor1, valor2)
Retorna o resto da divisão do valor1, dividido pelo valor2.
Exemplo:
SQL> SELECT sal, comm, MOD(sal, comm), MOD(100,40)
FROM emp
WHERE deptno=30
ORDER BY comm;
C) FUNÇÕES PARA MANIPULAR DATAS
c.1) SYSDATE
Retorna a data e hora corrente.
Exemplo:
Obtenha a data corrente:
SQL> SELECT SYSDATE 
FROM dual;
OPERAÇÕES ARITMÉTICAS
É possível adicionar e subtrair números constantes de datas.
DATA + Número de dias
DATA - Número de dias
DATA - DATA
DATA + Número/24
Exemplo:
SQL> SELECT hiredate, hiredate+7, hiredate -7, sysdate - hiredate
FROM emp
WHERE hiredate 
LIKE '%JUN%';
SQL> SELECT ename, hiredate 
FROM emp
WHERE ename='TURNER';
SQL> SELECT SYSDATE - hiredate 
FROM emp
WHERE ename='TURNER'; 
c.2) MONTHS_BETWEEN
Retorna o número de meses entre duas datas.
Exemplo:
SQL> SELECT MONTHS_BETWEEN (SYSDATE, hiredate)
MONTHS_BETWEEN('01-JAN-84','05-NOV-88')
FROM emp
WHERE MONTHS_BETWEEN (SYSDATE, hiredate) > 160;
SQL> SELECT MONTHS_BETWEEN('26-AUG-94','26-AUG-94')
FROM dual;
c.3) ADD_MONTHS
Adiciona n número de meses na data, onde n deve ser um inteiro e pode ser negativo.
Exemplo:
SQL> SELECT hiredate ADD_MONTHS(hiredate,3), ADD_MONTHS(hiredate,-3)
FROM emp
WHERE deptno = 20;
c.4) NEXT_DAY
Retorna a data do próximo dia da semana informado.
Exemplo:
SQL> SELECT hiredate, NEXT_DAY(hiredate, 'FRIDAY'), NEXT_DAY(hiredate, 6)
FROM emp
WHERE deptno = 10;
c.5) LAST_DAY
Retorna a data do último dia do mês.
Exemplo:
SQL> SELECT SYSDATE, LAST_DAY(SYSDATE), hiredate, LAST_DAY(hiredate)
FROM emp
WHERE deptno = 20;
D) FUNÇÕES DE CONVERSÃO
d.1) TO_CHAR
Converte um formato de data default para um formato alternativo especificado.
TO_CHAR(date, 'date picture')
default format date = DD-MON-YY
Formatos de datas (date picture)
	Date picture
	Significado
	YYYY
	Ano
	Y, YYY
	Ano com vírgula
	MM
	Mês
	MONTH
	Nome do mês
	MON
	Nome do mês com abreviação
	WW ou W
	Mês do ano ou mês
	DDD ou DD ou D
	Dia do ano, mês ou semana
	DAY
	Nome do dia da semana
	HH ou HH12
	Hora do dia (1 - 12)
	HH24
	Hora do dia (0 - 23)
	MI
	Minuto
	SS
	Segundos
Sufixos
	TH
	Número
	DDTH
	14TH
Exemplo:
SQL> SELECT SYSDATE
FROM dual;
TO_CHAR também é utilizado para converter tipo de dado NUMBER para tipo de dado CHAR (novo formato).
TO_CHAR (number, 'number picture')
FORMATO DE NÚMEROS (Number picture)
	Number Picture
	Significado
	Exemplo
	9
	Numérico (o número de 9's determina o tamanho)
	999999 = 1234
	0
	Mostra zero à esquerda
	099999 = 001234
	$
	Sinal de Dólar
	$999999 = $1234
	.(ponto)
	Especifica posição do ponto decimal
	999999.99 = 1234.00
	, (vírgula)
	Especifica posição da vírgula
	999,999 = 1,234
d.2) TO_NUMBER
Converte o número armazenado para um tipo de dado NUMBER.
Exemplo:
SQL> SELECT ename, job, sal 
FROM emp
WHERE sal > TO_NUMBER('1500');
d.3) TO_DATE
Permite formatar para outros formatos de datas.
Exemplo:
SQL> SELECT ename, hiredate 
FROM emp
WHERE hiredate = TO_DATE('JUNE 9, 1981', 'MONTH DD, YYYY');
Obs.: O formato default da data é: DD-MON-YY
Exemplo: hiredate = 09-JUN-81.
d.4) NVL
Converte um valor nulo para umvalor especificado.
Exemplo:
SQL> SELECT ename, sal comm, sal * 12 + NVL(comm, 0), NVL(comm, 1000)
FROM emp
WHERE deptno = 30;
d.5) GREATEST
Retorna o maior valor de uma lista de valores.
Exemplo:
SQL> SELECT ename, sal, comm, GREATEST(sal, comm), GREATEST(1000,2000)
FROM emp
WHERE deptno = 30;
d.6) LEAST
Retorna o menor valor de uma lista de valores.
Exemplo
SQL> SELECT ename, sal, comm, LEAST(sal, comm), LEAST(1000, 2000)
FROM emp
WHERE deptno = 30;
d.7) VSIZE
Retorna o número de bytes que são utilizados para armazenar internamente os dados (colunas ou valores)
Exemplo:
SQL> SELECT hiredate, VSIZE(hiredate), sal, VSIZE(sal), ename, VSIZE(ename)
FROM emp
WHERE deptno = 30;
E) FUNÇÕES PARA MANIPULAR GRUPOS
As funções de grupos servem para manipular um conjunto de linhas (ROWS) da tabela.
Retornam um resultado baseado num grupo de linhas (ROWS).
Por default todas as linhas (ROWS) da tabela são tratadas com um grupo.
A cláusula GROUP BY é usada para dividir as linhas da tabela em grupos menores.
FUNÇÕES DE GRUPO
e.1) AVG
Retorna a média dos valores da coluna, ignorando valores nulos.
Exemplo:
Obtenha a média de salários de todos os empregados.
SQL> SELECT ename, sal, AVG(sal) 
FROM emp;
SQL> SELECT AVG(sal )
FROM emp;
e.2) COUNT
Retorna o número de ocorrências da expressão, ou coluna. Usando *, retorna todas as ocorrências das linhas incluindo, duplicadas e nulas.
Exemplo:
Obtenha o número de empregados da empresa
SQL> SELCT COUNT(*)
FROM emp;
Exemplo:
Obtenha o número de empregados do departamento 10
SQL> SELECT COUNT(*) 
WHERE deptno = 10;
e.3) SUM
Retorna a soma dos valores da coluna, ignorando valores nulos.
Exemplo:
Obtenha a soma de todos os salários dos empregados.
SQL> SELECT SUM(sal) 
FROM emp;
Exemplo:
Obtenha a soma de todos os salários do departamento 10.
SQL> SELECT SUM(sal) 
FROM emp
WHERE deptno = 10;
e.4) MAX
Retorna o valor máximo de uma coluna.
Exemplo:
Obtenha o maior salário dos empregados.
SQL> SELECT MAX(sal) 
FROM emp;
Exemplo:
Obtenha o maior salário do departamento 20.
SQL> SELECT MAX(sal)
FROM emp
WHERE deptno = 20;
e.5) MIN
Retorna o valor mínimo de uma coluna.
Exemplo:
Obtenha o menor salário dos empregados.
SQL> SELECT MIN(sal) 
FROM emp;
Exemplo:
Obtenha o menor salário do departamento 10.
SQL> SELECT MIN(sal)
FROM emp
WHERE deptno = 10;
CLÁUSULA GROUP BY
A cláusula GROUP BY referencia uma coluna especificada da tabela relacionada na cláusula FROM e agrupa as linhas com base nos valores iguais dessa coluna.
O resultado da cláusula GROUP BY particiona a tabela em um conjunto de grupos, não efetuando qualquer tipo de ordenação.
Exemplo:
Obtenha o maior salário de cada profissão.
SQL> SELECT job, MAX(sal )
FROM emp
GROUP BY job;
Exemplo:
Obtenha o menor salário de cada profissão.
SQL> SELECT job, MIN(sal)
FROM emp
GROUP BY job;
Exemplo:
Obtenha o maior salário da profissão CLERK.
SQL> SELECT MAX(sal)
WHERE job = 'CLERK'
GROUP BY job;
Exemplo:
Obtenha o menor salário da profissão CLERK.
SQL> SELECT MIN(sal)
WHERE job = 'CLERK'
GROUP BY job;
Exemplo:
Obtenha a média salarial por departamento.
SQL> SELECT deptno, AVG(sal)
FROM emp
GROUP BY deptno;
Observação: a cláusula WHERE só pode ser usada em funções de grupos em casos especiais
CLÁUSULA HAVING
A cláusula HAVING especifica uma restrição sobre a tabela agrupada que resultou de uma cláusula GROUP BY anterior e elimina os grupos que não satisfazem a condição estabelecida.
A cláusula HAVING é usada para especificar a qualidade que um grupo deve ter para ser incluído no resultado. Ela efetua para os grupos a mesma função que a cláusula WHERE efetua para as linhas.
A cláusula HAVING é sempre utilizada junto com a cláusula GROUP BY, sendo que o HAVING é especificado sempre após o GROUP BY.
Exemplo:
Obtenha a média de salário dos departamentos que possuem mais de 3 empregados.
SQL> SELECT deptno, AVG(sal) 
FROM emp
HAVING COUNT(*) > 3;
Exemplo:
Obtenha as profissões que o maior salário é igual ou maior que 3000.
SQL> SELECT job, MAX(sal)
FROM emp
HAVING MAX(sal) >= 3000
GROUP BY job;
SUBQUERIES (SUBCONSULTAS)
Uma subquery é uma cláusula SELECT aninhada com outra cláusula SELECT e que retorna um resultado intermediário.
Exemplo:
SELECT coluna1, coluna2, ..........
FROM tabela
WHERE coluna = (SELECT coluna1, coluna2,........
FROM tabela
WHERE condição);
A) SUBQUERY DE UMA LINHA
Exemplo:
Obtenha o nome, profissão e salário do empregado que possui o menor salário.
SQL> SELECT MIN(sal) 
FROM emp;
{recupera o menor salário de todos os empregados}
SQL> SELECT ename, job, sal 
FROM emp
WHERE sal = (SELECT MIN(sal) FROM emp)
{o resultado acima é usado como parâmetro nesta query}
Como as subqueries aninhadas são processadas
Uma subquery é composta por duas cláusulas SELECT, o SELECT principal e SELECT interno.
O comando SELECT interno é executado primeiro, produzindo um resultado. No exemplo acima o resultado é 800.
O SELECT principal é processado usando o valor retornado pelo SELECT interno.
Observação: sempre que o SELECT interno retorna uma linha da tabela podem ser utilizadas os operadores =, !=, <, <=, >, >=.
Exemplo:
Obtenha o nome, profissão, de todos os empregados que possuem a mesma profissão do empregado BLAKE.
SQL> SELECT job 
FROM emp
WHERE ename = 'BLAKE';
SQL> SELECT ename, job
FROM emp
WHERE job = (SELECT job FROM emp WHERE ename = 'BLAKE');
Observação: O SELECT interno retorna a profissão de BLAKE que é MANAGER, e o SELECT externo retorna o nome dos empregados com profissão de MANAGER.
B) SUBQUERIES QUE RETORNAM MAIS DE UMA LINHA DA TABELA
b.1) OPERADOR IN
Relembrando, o operador IN testa (verifica) os valores que estão em uma lista de valores.
Exemplo:
Obtenha o nome, salário e número do departamento dos empregados que recebem o menor salário de cada empregado.
SQL> SELECT MIN(sal) 
FROM emp
GROUP BY deptno;
SQL> SELECT ename, sal, deptno 
FROM emp
WHERE sal in (SELECT MIN(sal) FROM emp GROUP BY deptno)
Observação: Sempre que aparecer uma SUBQUERY, onde, no resultado retornam mais de uma linha da tabela, utiliza-se o operador IN.
b.2) OPERADORES ANY E ALL
Os operadores ANY e ALL são utilizados para subqueries que retornam mais de uma linha da tabela, e requerem o uso dos operadores igual ( = ), menor ( > ), maior ( < ), menor ou igual (>= ), maior ou igual ( <= ) ou diferente ( !=), aplicado ao resultado de uma subconsulta.
ANY
Compara um valor com cada valor da lista de valores que foi retornado pela subquery.
Exemplo:
Obtenha o nome, salário, profissão e número do departamento de todos empregados que recebem um salário maior que o menor salário dos empregados do departamento 30.
SQL> SELECT DISTINCT sal 
FROM emp
WHERE deptno = 30;
SQL> SELECT ename, sal, job, deptno 
FROM emp
WHERE sal > ANY (SELECT DISTINCT sal FROM emp WHERE deptno = 30);
Observação: Quando a ANY é utilizado, frequentemente a cláusula DISTINCT é utilizada para prevenir que a mesma linha seja selecionada várias vezes.
ALL
Compara um valor com todos os valores da lista de valores retornados pela subquery.
Exemplo:
Obtenha o nome, salário, profissão e número do departamento de todos os empregados que recebem um salário maior, que todos os salários dos empregados do departamento 30.
SQL> SELECT DISTINCT sal 
FROM emp
WHERE deptno = 30;
SQL> SELECT ename, sal, job, deptno
FROM emp
WHERE sal > ALL (SELECT DISTINCT sal FROM emp WHERE deptno = 30);
b.3) OPERADOR NOT
O operador NOT também pode utilizado com os operadores IN, ALL e ANY.
Utilização do ORDER BY
Não é possível utilizar o ORDERBY em um SELECT interno, a regra é utilizar somente um ORDER BY em uma subquery e este deve ser o último comando do SELECT, ou seja no SELECT mais externo.
Aninhamento de subqueries
Também é possível aninhar (encadear) várias queries uma dentro da outra, não existindo limites.
C) CLÁUSULA HAVING COM SUBQUERIES ANINHADAS
A cláusula WHERE refere-se a uma linha da tabela e o HAVING a um grupo de linhas especificadas na cláusula GROUP BY.
Exemplo:
Obtenha o número dos departamentos, onde os empregados recebem um salário médio maior que a média salarial do departamento 30.
SQL> SELECT AVG(sal)
FROM emp
WHERE deptno = 30;
SQL> SELECT deptno, AVG(sal)
FROM emp
HAVING AVG(sal) > (SELECT AVG(sal) FROM emp WHERE deptno = 30)
GROUP BY deptno;
Exemplo:
Obtenha a profissão dos empregados que recebem a mais alta média salarial.
SQL> SELECT MAX(AVG(sal)) 
FROM emp
GROUP BY job;
SQL> SELECT job, AVG(sal) 
FROM emp
GROUP BY job 
HAVING AVG(sal) = (SELECT MAX(AVG(sal)) FROM emp GROUP BY job);
Observação: O SELECT interno encontra a média salarial par aos grupos de profissões, e a função MAX encontra a maior média salarial dos grupos, que é o valor (5000), este é utilizado pela cláusula HAVING. A cláusula GROUP BY é necessária no SELECT principal para agrupar as profissões.
EXTRAIR DADOS DE MAIS DE UMA TABELA
Sempre que é necessário extrair dados de mais de uma tabela um JOIN é feito, também conhecido como ENCADEAMENTO.
A finalidade do JOIN é obter informações que não estão em uma única tabela, e é especificado utilizando-se a cláusula WHERE.
SELECT coluna1, coluna2,....FROM tabela1, tabela2,...... WHERE condição de join;
JOIN-EQUI-JOIN (SIMÉTRICO)
O JOIN-EQUI-JOIN ocorre quando a condição de JOIN compara a igualdade entre as colunas da tabela 1 as colunas da tabela 2.
Podemos então observar que o relacionamento entre as duas tabelas (emp e dept) é uma condição de JOIN "EQUI-JOIN", pois os valores da coluna deptno em ambas as tabelas são iguais e o operador igual ( = ) é utilizado.
Exemplo:
Obtenha o nome e a profissão de todos os empregados e o nome dos departamentos em que os empregados estão lotados.
Para resolver este exemplo, é necessário comparar (condição de join) o valor da coluna deptno da tabela emp cpm o mesmo valor da coluna deptno da tabela dept, extraindo os nomes dos departamentos.
SQL> SELECT ename, job, dname
FROM emp, dept
WHERE emp.deptno=dept.deptno;
APELIDOS (ALIAS) PARA TABELAS
Para criar um apelido para uma tabela, defina-o na cláusula FROM. O apelido pode então ser utilizado como qualificador tanto na cláusula WHERE, quanto na cláusula SELECT.
Os apelidos também são chamados de LABELS TEMPORÁRIOS, e podem ter até 30 caracteres, mas quanto menos melhor.
Exemplo:
SQL> SELECT ename, job, D.deptno, dname
FROM emp E, dept D
WHERE E.deptno = D.deptno
ORDER BY D.deptno;
OUTRO MÉTODO DE FAZER JOIN
Fazendo um join da tabela com ela mesma.
SQL> SELECT E.name emp_name, E.sal emp_sal, M.ename mgr_name, M.sal mgr_sal
FROM emp E, emp M
WHERE E.mgr = M.empno AND E.sal < M.sal;
Observação: Os apelidos E e M para a tabela emp significa que, E são os empregados e M são os gerentes.
OPERADORES DE CONJUNTOS (SET OPERATORS)
UNION
A união de duas relações é o conjunto de todas as linhas que estão em uma ou outra relação, ignorando as duplicada, ou seja, retorna a união de dois SELECTs, ignorando as linhas duplicadas.
SQL> SELECT job
FROM emp
WHERE deptno = 10
UNION
SELECT job
FROM emp
WHERE deptno = 30;
INTERSECT
A interseção é o conjunto de todas as linhas que estão simultaneamente em ambas as relações, ou seja, retorna a interseção de dois SELECTs.
SQL> 	 SELECT job
FROM emp
WHERE deptno = 10
INTERSECT
SELECT job
FROM emp deptno = 30;
MINUS
A diferença é o conjunto de todas as linhas que estão em apenas uma das relações, ou seja, retornam a subtração de dois SELECTs.
SQL> 	 SELECT job
FROM emp
WHERE deptno = 10
MINUS
SELECT job
FROM emp
WHERE deptno = 30;
REGRAS PARA UTILIZAR UNION, INTERSECT e MINUS
A cláusula SELECT deve selecionar o mesmo número de colunas.
As colunas correspondentes devem ser o mesmo tipo de dado.
As linhas duplicadas são automaticamente eliminados.
Os nomes das colunas do primeiro SELECT é que aparecem no resultado.
A cláusula ORDER BY deve aparecer no final do comando.
A cláusula ORDER BY somente pode ser usada indicando o número da coluna.
Os operadores UNION, ITERSECT e MINUS podem ser utilizados em subqueries.
As colunas SELECTs são executadas de cima para baixo.
Vários SET OPERATORS podem ser utilizados. Para indicar a seqüência de execução deve-se utilizar-se parêntese.
LINGUAGEM DE DEFINIÇÃO DE DADOS (DDL)
CRIAR UMA TABELA
Para definir e criar uma tabela o comando CREATE TABLE é utilizado com a seguinte sintaxe:
CREATE TABLE nome-da-tabela (
(nome_coluna1 tipo_dado(tamanho)) [NULL/NOT NULL],
(nome_coluna2 tipo_dado(tamanho)) [NULL/NOT NULL],
(nome_coluna3 tipo_dado(tamanho)) [NULL/NOT NULL],
............................
(nome_colunan tipo_dado(tamanho)) [NULL/NOT NULL] )
Se for especificado a restrição NOT NULL, cada linha deve ter valor diferente de NULL para esta coluna. Se não for especificado nada, a opção NULL default é utilizada.
Exemplo:
CREATE TABLE dept (
deptno		NUMBER(2) not null,
dname		CHAR(12),
loc		CHAR(12) )
CRIAR UMA TABELA UTILIZANDO A ESTRUTURA DE OUTRA
Utilizando o CREATE TABLE e SELECT, podemos criar uma tabela utilizando a estrutura de outra tabela e ao mesmo tempo carregar a nova tabela com dados, conforme a condição especificada.
CREATE TABLE nome_tabela
[(nome_coluna1 tipo_dado(tamanho) NULL/NOT NULL],
(nome_coluna2 tipo_dado(tamanho) NULL/NOT NULL,
....................
(nome_colunan tipo_dado(tamanho) NULL/NOT NULL],
AS SELECT comando_select;
A tabela será criada com as colunas e com as linhas especificadas no SELECT;
Caso sejam definidos as colunas no CREATE TABLE, o número de colunas deve ser igual as do comando SELECT.
Exemplo1:
Criar uma tabela com as colunas número, nome, profissão e salário dos empregados do departamento 30.
		 CREATE TABLE TAB30
AS SELECT empno, ename, job, sal
FROM emp WHERE deptno = 30;
Exemplo 2:
Criar uma tabela com as colunas nome, salário e grade salarial de todos os empregados.
CREATE TABLE EMP_SALS(nome, salario, gradesal)
AS SELECT ename, sal, grade
FROM emp WHERE emp.sal BETWEEN losal AND hisal;
DATA MANIPULATION LANGUAGE – DML
A DML é um grupo de comandos SQL utilizados para INSERIR novas linhas, ALTERAR linhas e EXCLUIR linhas.
A) INSERIR LINHAS NA TABELA
O comando INSERT é utilizado para inserir novas linhas (ou ROWS) na tabela.
INSERT INTO nome_da_tabela(nome_coluna1, nome_coluna2, ............)
VALUES(valor1, valor2, .....................................);
Exemplo:
Inserir o departamento número 50, com nome MARKETING e localização SAN JOSÉ.
INSERT INTO dept(depto, dname, loc) VALUES(50, 'MARKETING','SAN JOSÉ');
Observação
Colunas com tipo de dado CHAR e DATE, devem ser colocadas entre aspas simples. Para inserir uma data ou hora em outro formato utiliza-se a função TO_DATE.
A.1) UTILIZANDO SUBSTITUIÇÃO DE VARIÁVEIS
	INSERT INTO dept(deptno, dname, loc) 
	VALUES(&dept_number, '&dept_name','&location');
Observação:
 Sempre que o INSERT é executado, um prompt com o nome da variável é mostrado.
A.2) COPIAR LINHAS (ROWS) DE OUTRA TABELA
INSERT INTO nome_tabela[(coluna1, coluna2,............)] SELECT comando_select;
Exemplo:
INSERT INTO tabd10(empno, ename, sal, job, hiredate) 
SELECT empno, ename, sal, job, hiredate
 FROM emp 
WHERE deptno = 10;
Observação:
 A tabela TABD10 deve ser criada antes.
B) ALTERAR LINHAS DA TABELA - SINTAXE
O comando UPDATE é utilizado para alterarvalores das linhas da tabela.
UPDATE nome_tabela
SET nome_coluna1 = valor/expressão
[ , nome_coluna2 = valor/expressão, ...........]
WHERE condição;
Exemplo:
Alterar a profissão para vendedor e aumentar o salário em 10% para o empregado com nome SCOTT.
UPDATE emp
SET job = 'VENDEDOR'
sal = sal *1.1
WHERE ename = 'SCOTT';
C) EXCLUIR LINHAS DA TABELA - SINTAXE
O comando DELETE é utilizado para excluir/remover uma ou mais linhas da tabela.
DELETE FROM nome_tabela
WHERE condição;
Exemplo:
Excluir todos os empregados que pertencem ao departamento 10.
DELETE FORM emp
WHERE deptno = 10;
Observação: Cuidado - se WHERE for omitido, todas as linhas da tabela são excluídas.