Banco de Dados Oracle

1

Captulo 1: Conceitos de Banco de Dados

1.1 Objetivos deste captulo

Introduzir os conceitos bsico de sistema gerenciador de banco de dados, banco de dados

relacional, lgebra relacional, e a arquitetura do Oracle.

1.2 Sistema de Gerenciamento de Banco de Dados

O Sistema de Gerenciamento de Banco de Dados (SGBD) um software que controla o

armazenamento, as modificaes, e os acessos s informaes do banco de dados.

O SGBD atua como interface entre os usurios e as informaes armazenadas. Todas as

solicitaes relativas aos dados do banco de dados so interceptadas, interpretadas, e

executadas pelo SGBD. Nenhum acesso aos dados pode ser feito de outra maneira.

Desta forma, o SGBD isola o usurio de todos os detalhes particulares do hardware ou do

sistema operacional sendo utilizado, tais como mtodo de acesso e blocagem de disco. A

forma de acesso do usurio ao banco de dados no deve mudar quando o hardware ou o

sistema operacional mudam.

Existem modelos alternativos para se implementar um SGBD, sendo que os mais comuns

so hierrquico, rede, listas invertidas e relacional.

1.3 Banco de dados relacional

O modelo predominante atualmente o relacional. Um banco de dados relacional pode ser

definido como um banco de dados que aparece ao usurio como uma coleo de tabelas

relacionadas, e nada alm de tabelas.

Os trs elementos de um banco de dados relacional so:

tabelas

colunas

linhas

2

Os operadores relacionais so:

Seleo/Restrio uma operao que recupera e mostra dados de uma

tabela. possvel recuperar todas as linhas, ou apenas

algumas que satisfaam uma determinada condio ou a

vrias condies. Esta operao chamada tambm de

subconjunto horizontal.

Projeo uma operao que recupera e mostra dados de apenas

algumas colunas, e portanto chamada de subconjunto

vertical.

Unio mostra todas as linhas que aparecem em duas tabelas.

Interseo mostra todas as linhas que aparecem nas duas tabelas.

Diferena mostra todas as linhas que aparecem em apenas uma das

tabelas

Produto o resultado obtido pela concatenao de duas tabelas.

Todas as linhas da primeira tabela so concatenadas com

todas as linhas da segunda tabela.

Juno o resultado obtido pela concatenao de duas tabelas de

acordo com condies especficas. Apenas as linhas que

atendem as condies estabelecidas so concatenadas.

Seleo/Restrio

Projeo

3

Unio

Interseo

Diferena

4

Produto

Joo x Pintor = Joo Pintor

Jos Escritor Joo Escritor

Manoel Jos Pintor

Jos Escritor

Manoel Pintor

Manoel Escritor

Juno

Joo 10 10 Vendas Joo 10 10 Vendas

Jos 10 20 Pesq. Jos 10 10 Vendas

Maria 20 30 Cont. Maria 20 20 Pesq.

Ana 30 40 Secret. Ana 30 30 Cont.

1.4 Arquitetura do Oracle

Background Processes

System Global Area

Servers

Users

Redo Log Files

Data Files

Control Files

Banco de Dados

O SGBD:

Estruturas de memria:

System Global Area (SGA) uma regio compartilhada de memria alocada pelo

SGBD Oracle, que contm dados e informaes de controle. Quanto maior for a SGA

melhor ser o desempenho do Oracle. Os dados da SGA so compartilhados por

5

todos os usurios que esto acessando o banco de dados. As informaes

armazenadas na SGA esto divididas por tipo de estrutura de memria, incluindo

database buffers, redo log buffers e shared poll.

Program Global Area (PGA) uma rea de memria que contm dados e

informaes de controle para um processo servidor. Uma PGA criada pelo Oracle

quando um processo servidor iniciado.

Processos:

Os processos so divididos em Processos Usurio e Processos do Oracle. Cada

usurio conectado possui um Processo Usurio, atravs do qual se comunica com o

Oracle. Entre os processos do Oracle esto os Processos Servidores, que executam as

solicitaes emitidas pelo Processos Usurios, e os Processos Background.

O Banco de dados:

Data Files Contm todos os dados do banco de dados.

Redo Log Files Contm todas as informaes relativas s

alteraes efetuadas no banco de dados

para permitir a recuperao.

Control Files Registra a estrutura fsica do banco de dados.

6

Captulo 2: Introduo s Consultas


Este captulo faz uma introduo linguagem de consulta utilizada para acessar o Banco de

Dados Oracle. Em particular, so discutidas as declaraes utilizadas para:

realizar clculos

manusear valores nulos corretamente

nomes alternativos para ttulos das colunas

concatenar colunas

ordenar linhas

fornecer critrios de pesquisa

2.2 O utilitrio SQL*PLUS

O SQL*PLUS uma interface atravs da qual os comandos SQL podem ser entrados e

executados. O SQL*PLUS possui outros comandos, que permitem formatar a sada dos

comandos SQL, alm de fornecer facilidades para editar e salvar comandos SQL.

Para ativar o SQL*PLUS digite SQLPLUS na linha de comando do MS-DOS, e pressione a

tecla Enter. Quando o SQLPLUS solicitar para entrar com o nome do usurio digite

ALUNO1, e quando for solicitada a senha fornea ALUNO1 novamente.

As linhas de comando do SQL*PLUS so prefixadas por: SQL>. As linhas de continuao

so numeradas. Todo o curso est baseado na utilizao desta ferramenta pelos participantes.

Os comandos podem ser escritos em uma ou mais linhas.

2.3 Construo bsica de uma consulta

O comando SELECT recupera informaes do banco de dados, implementando todos os

operadores da lgebra relacional.

Em sua forma mais simples deve incluir:

a. A clusula SELECT, que lista as colunas a serem envolvidas (essencialmente a Projeo,

conforme definido na lgebra relacional).

b. A clusula FROM, que especifica as tabelas envolvidas.

Para listar os nmeros de todos os departamentos, nomes dos empregados e nmeros dos

gerentes da tabela EMP, digita-se:

7

SQL> SELECT DEPTNO, ENAME, MGR

2 FROM EMP

DEPTNO ENAME MGR

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

20 SMITH 7902

30 ALLEN 7698

30 WARD 7698

20 JONES 7839

30 MARTIN 7698

30 BLAKE 7839

10 CLARK 7839

20 SCOTT 7566

10 KING

30 TURNER 7698

20 ADAMS 7788

30 JAMES 7698

20 FORD 7566

10 MILLER 7782

14 rows selected.

possvel selecionar todas as colunas da tabela colocando-se um "*" (asterisco) aps a

palavra SELECT, como visto abaixo:

SQL> SELECT *

2 FROM EMP

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

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

7369 SMITH CLERK 7902 13-JUN-83 800 20

7499 ALLEN SALESMAN 7698 15-AUG-83 1600 300 30

7521 WARD SALESMAN 7698 26-MAR-84 1250 500 30

7566 JONES MANAGER 7839 31-OCT-83 2975 20

7654 MARTIN SALESMAN 7698 05-DEC-83 1250 1400 30

7698 BLAKE MANAGER 7839 11-JUN-84 2850 30

7782 CLARK MANAGER 7839 14-MAY-84 2450 10

7788 SCOTT ANALYST 7566 05-MAR-84 3000 20

7839 KING PRESIDENT 09-JUL-84 5000 10

7844 TURNER SALESMAN 7698 04-JUN-84 1500 0 30

7876 ADAMS CLERK 7788 04-JUN-84 1100 20

7900 JAMES CLERK 7698 23-JUL-84 950 30

7902 FORD ANALYST 7566 05-DEC-83 3000 20

7934 MILLER CLERK 7782 21-NOV-83 1300 10

14 rows selected.

2.4 Expresses aritmticas

Uma expresso a combinao de um ou mais valores, operadores e funes, que resultam

em um valor calculado.

As expresses aritmticas podem conter nomes de colunas, constantes numricas, e

operadores aritmticos. Os operadores so + - * e / , para soma, subtrao, multiplicao e

diviso, respectivamente.

No exemplo abaixo calcula-se o salrio anual multiplicando-se o salrio mensal por doze.

SQL> SELECT ENAME, SAL*12, COMM

2 FROM EMP

8

ENAME SAL*12 COMM

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

SMITH 9600

ALLEN 19200 300

WARD 15000 500

JONES 35700

MARTIN 15000 1400

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000 0

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

Se a expresso aritmtica contiver mais de um operador, multiplicao e diviso so de

mesma precedncia, porm com precedncia superior a adio e subtrao, que tambm so

de mesma precedncia. No caso de operadores com a mesma precedncia, o da esquerda

processado primeiro.

No exemplo abaixo, a multiplicao (250*12) realizada primeiro, e depois somada ao

salrio.

SQL> SELECT ENAME, SAL+250*12

2 FROM EMP

ENAME SAL+250*12

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

SMITH 3800

ALLEN 4600

WARD 4250

JONES 5975

MARTIN 4250

BLAKE 5850

CLARK 5450

SCOTT 6000

KING 8000

TURNER 4500

ADAMS 4100

JAMES 3950

FORD 6000

MILLER 4300

Parnteses podem ser utilizados para especificar a ordem na qual os operadores sero

executados. Se, por exemplo, for necessrio somar 250 ao salrio antes de multiplicar por

12, devemos escrever:

SQL> SELECT ENAME, (SAL+250)*12

2 FROM EMP

ENAME (SAL+250)*12

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

SMITH 12600

ALLEN 22200

WARD 18000

JONES 38700

MARTIN 18000

9

BLAKE 37200

CLARK 32400

SCOTT 39000

KING 63000

TURNER 21000

ADAMS 16200

JAMES 14400

FORD 39000

MILLER 18600

2.5 Nomes alternativos para ttulos de colunas

Para mostrar o resultado de uma consulta, o SQL*PLUS normalmente utiliza o nome da

coluna como ttulo da coluna. Em muitos casos, isto torna o ttulo sem significado. Pode ser

alterado o ttulo da coluna usando-se um Alis.

O Alis especificado escrevendo-se um texto aps item a ser mostrado na clusula

SELECT. Por padro, o nome do Alis convertido para letras maisculas, e no pode

conter espaos em branco, a menos que seja colocado entre aspas.

Para mostrar como ttulo da coluna SALARIO ANUAL no lugar de SAL*12, podemos

escrever:

SQL> SELECT ENAME NOME, SAL*12 SALARIO ANUAL, COMM 2 FROM EMP

NOME SALARIO ANUAL COMM

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

SMITH 9600

ALLEN 19200 300

WARD 15000 500

JONES 35700

MARTIN 15000 1400

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000 0

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

14 rows selected.

Nota: somente na clusula SELECT o Alis pode ser usado, nas outras clusulas no.

2.6 O operador de concatenao

O operador de concatenao (||) permite juntar colunas, expresses aritmticas, ou valores

constantes, para criar uma expresso do tipo caracter. Colunas dos dois lados do operador

so combinadas para formar uma s coluna.

Para combinar o nmero do empregado com o nome do empregado em uma coluna apenas,

utilizando EMPREGADO como ttulo, devemos escrever:

SQL> SELECT EMPNO||ENAME EMPREGADO

2 FROM EMP

10

EMPREGADO

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

7369SMITH

7499ALLEN

7521WARD

7566JONES

7654MARTIN

7698BLAKE

7782CLARK

7788SCOTT

7839KING

7844TURNER

7876ADAMS

7900JAMES

7902FORD

7934MILLER

14 rows selected.

2.7 Literais

Um literal qualquer caracter, expresso, ou nmero, incluido na clusula SELECT, que no

um nome ou Alis da coluna.

O literal da clusula SELECT listado em todas as linhas mostradas pelo comando. Literais

dos tipos data e caracter devem ser envoltos por apstrofos ('). Literais numricos no

precisam de apstrofos.

O comando abaixo contm literal concatenado com colunas, e um Alis para a primeira

coluna.

SQL> COLUMN EMPREGADO FORMAT A20

SQL> SELECT EMPNO||'-'||ENAME EMPREGADO,

2 'TRABALHA NO DEPARTAMENTO',

3 DEPTNO

4 FROM EMP

EMPREGADO 'TRABALHANODEPARTAMENTO' DEPTNO

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

7369-SMITH TRABALHA NO DEPARTAMENTO 20

7499-ALLEN TRABALHA NO DEPARTAMENTO 30

7521-WARD TRABALHA NO DEPARTAMENTO 30

7566-JONES TRABALHA NO DEPARTAMENTO 20

7654-MARTIN TRABALHA NO DEPARTAMENTO 30

7698-BLAKE TRABALHA NO DEPARTAMENTO 30

7782-CLARK TRABALHA NO DEPARTAMENTO 10

7788-SCOTT TRABALHA NO DEPARTAMENTO 20

7839-KING TRABALHA NO DEPARTAMENTO 10

7844-TURNER TRABALHA NO DEPARTAMENTO 30

7876-ADAMS TRABALHA NO DEPARTAMENTO 20

7900-JAMES TRABALHA NO DEPARTAMENTO 30

7902-FORD TRABALHA NO DEPARTAMENTO 20

7934-MILLER TRABALHA NO DEPARTAMENTO 10

14 rows selected.

2.8 Manuseio de valores nulos

11

Se em uma linha no existir valor para uma determinada coluna, este valor dito como

sendo nulo.

Um valor nulo um valor que no est disponvel, desconhecido, ou no aplicvel. Um

valor nulo no o mesmo que zero. Zero um nmero.

Se o valor de qualquer coluna envolvida em uma expresso for nulo, o resultado da

expresso tambm ser nulo. No exemplo abaixo, somente aparecem os salrios anuais dos

vendedores, pois somente estes tem comisso no nula.

SQL> SELECT ENAME, SAL*12+COMM SALARIO_ANUAL

2 FROM EMP

ENAME SALARIO_ANUAL

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

SMITH

ALLEN 19500

WARD 15500

JONES

MARTIN 16400

BLAKE

CLARK

SCOTT

KING

TURNER 18000

ADAMS

JAMES

FORD

MILLER

14 rows selected.

Para calcular o resultado corretamente, deve ser utilizada a expresso NVL, que converte um

valor nulo em um valor no nulo, conforme mostrado abaixo:

SQL> SELECT ENAME, SAL*12+NVL(COMM, 0) SALARIO_ANUAL

2 FROM EMP

ENAME SALARIO_ANUAL

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

SMITH 9600

ALLEN 19500

WARD 15500

JONES 35700

MARTIN 16400

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

14 rows selected.

A funo NVL espera dois argumentos:

a) uma expresso

12

b) um valor no nulo

Podem ser convertidos valores nulos de data, nmeros, ou caracteres:

NVL(ColunaData, 01-jan-84')

NVL(ColunaNumero, 9)

NVL(ColunaCaracter, alfanumrico')

2.9 Eliminao de linhas duplicadas

A no ser que seja especificado o contrrio, as linhas duplicadas no so eliminadas dos

resultados das consultas.

SQL> SELECT DEPTNO

2 FROM EMP

DEPTNO

-------

20

30

30

20

30

30

10

20

10

30

20

30

20

10

Para eliminar as linhas duplicadas, deve ser includa a palavra DISTINCT aps o SELECT.

SQL> SELECT DISTINCT DEPTNO

2 FROM EMP

DEPTNO

-------

10

20

30

Mltiplas colunas podem ser especificadas aps a palavra DISTINCT.

SQL> SELECT DISTINCT DEPTNO, JOB

2 FROM EMP

DEPTNO JOB

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

10 CLERK

10 MANAGER

10 PRESIDENT

20 ANALYST

20 CLERK

20 MANAGER

13

30 CLERK

30 MANAGER

30 SALESMAN

Acima esto mostradas todas as combinaes diferentes de departamentos e cargos.

2.10 Ordenao das linhas

Normalmente, a ordem das linhas retornadas por uma consulta indefinida. A clusula

ORDER BY pode ser usada para ordenar linhas. Se for usada, ORDER BY deve ser sempre

a ltima clusula de um comando SELECT.

Para ordenar pelo nome do empregado, usamos:

SQL> SELECT ENAME, JOB, SAL*12, DEPTNO

2 FROM EMP

3 ORDER BY ENAME

ENAME JOB SAL*12 DEPTNO

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

ADAMS CLERK 13200 20

ALLEN SALESMAN 19200 30

BLAKE MANAGER 34200 30

CLARK MANAGER 29400 10

FORD ANALYST 36000 20

JAMES CLERK 11400 30

JONES MANAGER 35700 20

KING PRESIDENT 60000 10

MARTIN SALESMAN 15000 30

MILLER CLERK 15600 10

SCOTT ANALYST 36000 20

SMITH CLERK 9600 20

TURNER SALESMAN 18000 30

WARD SALESMAN 15000 30

14 rows selected.

Ordem padro de ordenao:

- Nmeros: menores primeiro

- Data: mais cedo primeiro

- Caracteres: ordem alfabtica

Para reverter esta ordem, pode ser utilizada a palavra DESC aps o nome da coluna, na

clusula ORDER BY.

Para listarmos os funcionrios mais novos antes dos mais antigos fazemos:

SQL> SELECT ENAME, JOB, HIREDATE

2 FROM EMP

3 ORDER BY HIREDATE DESC

ENAME JOB HIREDATE

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

JAMES CLERK 23-JUL-84

KING PRESIDENT 09-JUL-84

BLAKE MANAGER 11-JUN-84

TURNER SALESMAN 04-JUN-84

ADAMS CLERK 04-JUN-84

CLARK MANAGER 14-MAY-84

14

WARD SALESMAN 26-MAR-84

SCOTT ANALYST 05-MAR-84

MARTIN SALESMAN 05-DEC-83

FORD ANALYST 05-DEC-83

MILLER CLERK 21-NOV-83

JONES MANAGER 31-OCT-83

ALLEN SALESMAN 15-AUG-83

SMITH CLERK 13-JUN-83

possvel efetuar a ordenao por mais de uma coluna, sendo algumas em ordem

ascendente e outras em ordem descendente.

SQL> SELECT DEPTNO, JOB, ENAME

2 FROM EMP

3 ORDER BY DEPTNO, SAL DESC

DEPTNO JOB ENAME

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

10 PRESIDENT KING

10 MANAGER CLARK

10 CLERK MILLER

20 ANALYST SCOTT

20 ANALYST FORD

20 MANAGER JONES

20 CLERK ADAMS

20 CLERK SMITH

30 MANAGER BLAKE

30 SALESMAN ALLEN

30 SALESMAN TURNER

30 SALESMAN WARD

30 SALESMAN MARTIN

30 CLERK JAMES

Exerccio: verificar como ficam os valores nulos aps a ordenao.

2.11 Critrios de pesquisa

Os critrios de pesquisa so informados na clusula WHERE, que corresponde ao operador

Restrio da lgebra relacional. Nesta clusula esto estabelecidos os critrios necessrios

para uma linha poder ser selecionada.

A clusula WHERE pode comparar valores em colunas, valores literais, expresses

aritmticas, ou funes. Trs elementos so sempre necessrios:

- Um nome de coluna

- Um operador de comparao

- Um nome de coluna, constante, ou lista de valores

Existem dois tipos de operadores: lgicos e SQL.

Os operadores lgicos testam as seguintes condies:

= igual

> maior

>= maior ou igual

< menor

15

diferente (^= ou !=)

Para listar os nomes, nmeros, cargos e departamentos de todos os funcionrios com cargo

CLERK, usamos:

SQL> SELECT ENAME, EMPNO, JOB, DEPTNO

2 FROM EMP

3 WHERE JOB = CLERK'

ENAME EMPNO JOB DEPTNO

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

SMITH 7369 CLERK 20

ADAMS 7876 CLERK 20

JAMES 7900 CLERK 30

MILLER 7934 CLERK 10

Para listar o nome e o nmero dos departamentos com nmero maior que 20, usamos:

SQL> SELECT DNAME, DEPTNO

2 FROM DEPT

3 WHERE DEPTNO > 20

DNAME DEPTNO

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

SALES 30

OPERATIONS 40

Para sabermos que funcionrios tem comisso superior ao salrio, usamos:

SQL> SELECT ENAME, SAL, COMM

2 FROM EMP

3 WHERE COMM > SAL

ENAME SAL COMM

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

MARTIN 1250 1400

Existem quatro operadores SQL, que operam sobre todos os tipos de dados.

BETWEEN...AND... entre dois valores (inclusive)

IN(lista) idnticos a uma lista de valores

LIKE semelhante a um modelo de caracteres

IS NULL valor nulo

O operador BETWEEN testa valores no intervalo ou idnticos aos limites.

SQL> SELECT ENAME, SAL

2 FROM EMP

3 WHERE SAL BETWEEN 1000 AND 2000

ENAME SAL

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

ALLEN 1600

WARD 1250

MARTIN 1250

TURNER 1500

ADAMS 1100

MILLER 1300

16

O operador IN testa os valores especificados em uma lista.

SQL> SELECT EMPNO, ENAME, SAL, MGR

2 FROM EMP

3 WHERE MGR IN ( 7902, 7566, 7788 )

EMPNO ENAME SAL MGR

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

7369 SMITH 800 7902

7788 SCOTT 3000 7566

7876 ADAMS 1100 7788

7902 FORD 3000 7566

Se caracteres ou datas forem utilizados, devem ser envoltos por apstrofos.

Usando o operador LIKE possvel selecionar linhas de acordo com modelo fornecido. O

smbolo "%" representa qualquer seqncia de zero ou mais caracteres. O smbolo "_"

(sublinhado) representa qualquer um caracter, mas apenas um, no mnimo e no mximo.

Para listarmos todos os empregados cujos nomes comeam com a letra "S", usamos:

SQL> SELECT ENAME

2 FROM EMP

3 WHERE ENAME LIKE 'S%'

ENAME

----------

SMITH

SCOTT

Para listar os nomes de todos os empregados que contm exatamente 4 letras, usamos:

SQL> SELECT ENAME

2 FROM EMP

3 WHERE ENAME LIKE '____' (quatro caracteres de sublinhado)

ENAME

----------

WARD

KING

FORD

O operador IS NULL serve especificamente para testar valores nulos.

Para listar os funcionrios que no possuem gerente, usamos:

SQL> SELECT ENAME, MGR

2 FROM EMP

3 WHERE MGR IS NULL

ENAME MGR

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

KING

17

Os operadores mostrados abaixo podem ser utilizados para testes de negao:

!= ^= no igual

NOT NomeDaColuna = no igual a

NOT NomeDaColuna > no maior do que

NOT BETWEEN fora da faixa especificada

NOT IN fora da lista especificada

NOT LIKE no semelhante ao modelo

IS NOT NULL no nulo

Para listar os empregados com salrios fora da faixa de 1000 a 2000, usamos:

SQL> SELECT ENAME, SAL

2 FROM EMP

3 WHERE SAL NOT BETWEEN 1000 AND 2000

ENAME SAL

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

SMITH 800

JONES 2975

BLAKE 2850

CLARK 2450

SCOTT 3000

KING 5000

JAMES 950

FORD 3000

8 rows selected.

Para listar os empregados cujos cargos no comeam com a letra M, usamos:

SQL> SELECT ENAME, JOB

2 FROM EMP

3 WHERE JOB NOT LIKE 'M%'

ENAME JOB

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

SMITH CLERK

ALLEN SALESMAN

WARD SALESMAN

MARTIN SALESMAN

SCOTT ANALYST

KING PRESIDENT

TURNER SALESMAN

ADAMS CLERK

JAMES CLERK

FORD ANALYST

MILLER CLERK

Para listar os empregados com gerentes, usamos:

SQL> SELECT ENAME, MGR

2 FROM EMP

3 WHERE MGR IS NOT NULL

ENAME MGR

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

SMITH 7902

18

ALLEN 7698

WARD 7698

JONES 7839

MARTIN 7698

BLAKE 7839

CLARK 7839

SCOTT 7566

TURNER 7698

ADAMS 7788

JAMES 7698

FORD 7566

MILLER 7782

x NULL e x = NULL

sempre falso. Nulo nunca igual a nada nem diferente de nada, nem a outro nulo.

2.12 Consultas com condies mltiplas

Os operadores AND e OR podem ser utilizados para criar expresses lgicas compostas.

O predicado AND espera que todas duas condies sejam verdadeiras.

O predicado OR espera que uma (ou as duas) condies sejam verdadeiras.

Nos exemplos abaixo as condies so as mesmas, porm os predicados so diferentes.

Para listar todos os CLERKs com salrio entre 1000 e 2000, usamos:

SQL> SELECT EMPNO, ENAME, JOB, SAL

2 FROM EMP


4 AND JOB = 'CLERK'

EMPNO ENAME JOB SAL

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

7876 ADAMS CLERK 1100

7934 MILLER CLERK 1300

Para listar os funcionrios que so CLERK, ou que recebem entre 1000 e 2000, usamos:

SQL> SELECT EMPNO, ENAME, JOB, SAL

2 FROM EMP


4 OR JOB = CLERK'

EMPNO ENAME JOB SAL

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

7369 SMITH CLERK 800

7499 ALLEN SALESMAN 1600

7521 WARD SALESMAN 1250

7654 MARTIN SALESMAN 1250

7844 TURNER SALESMAN 1500

7876 ADAMS CLERK 1100

7900 JAMES CLERK 950

7934 MILLER CLERK 1300

Quando aparecem tanto ANDs quanto ORs em uma expresso lgica, todos os ANDs so

processados antes que os ORs sejam processados.

19

Uma vez que AND tem precedncia sobre OR, a consulta abaixo retorna todos os gerentes

com salrio maior do que 1500, e todos os vendedores.

SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

2 FROM EMP

3 WHERE SAL > 1500

4 AND JOB = 'MANAGER'

5 OR JOB = 'SALESMAN'

EMPNO ENAME JOB SAL DEPTNO

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

7499 ALLEN SALESMAN 1600 30

7521 WARD SALESMAN 1250 30

7566 JONES MANAGER 2975 20

7654 MARTIN SALESMAN 1250 30

7698 BLAKE MANAGER 2850 30

7782 CLARK MANAGER 2450 10

7844 TURNER SALESMAN 1500 30

Para selecionar todos os vendedores e gerentes com salrio superior a 1500, usamos:

SQL> SELECT EMPNO, ENAME, JOB, SAL, DEPTNO

2 FROM EMP

3 WHERE SAL > 1500

4 AND ( JOB = 'MANAGER' OR JOB = SALESMAN' )

EMPNO ENAME JOB SAL DEPTNO

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

7499 ALLEN SALESMAN 1600 30

7566 JONES MANAGER 2975 20

7698 BLAKE MANAGER 2850 30

7782 CLARK MANAGER 2450 10

Os parnteses especificam a ordem em que os operadores devem ser calculados.

2.13 Precedncia dos operadores

a) os operadores de comparao e os operadores SQL tem precedncias iguais.

=, !=, , =, BETWEEN...AND..., IN, LIKE, IS NULL

b) NOT (para reverter o resultado lgico da expresso, WHERE NOT(sal>2000))

c) AND

d) OR

Para listar todos os gerentes, de qualquer departamento, e os CLERK do departamento 10

apenas, usamos:

SQL> SELECT *

2 FROM EMP

3 WHERE JOB = MANAGER' OR (JOB = CLERK' AND DEPTNO = 10)


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


20



7934 MILLER CLERK 7782 21-NOV-83 1300 10

Neste caso os parnteses so desnecessrios, uma vez que o AND processa primeiro.

2.14 Resumo do comando SELECT

SELECT [DISTINCT] {* | coluna [alis],....}

FROM tabela

WHERE condies

ORDER BY {coluna | expresso} [ASC|DESC]

2.15 Exerccios

a) Selecionar todas as informaes da tabela SALGRADE

GRADE LOSAL HISAL

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

1 700 1200

2 1201 1400

3 1401 2000

4 2001 3000

5 3001 9999

b) Selecionar todas a informaes da tabela EMP


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

7369 SMITH CLERK 7902 13-JUN-83 800 20

7499 ALLEN SALESMAN 7698 15-AUG-83 1600 300 30

7521 WARD SALESMAN 7698 26-MAR-84 1250 500 30


7654 MARTIN SALESMAN 7698 05-DEC-83 1250 1400 30





7844 TURNER SALESMAN 7698 04-JUN-84 1500 0 30

7876 ADAMS CLERK 7788 04-JUN-84 1100 20

7900 JAMES CLERK 7698 23-JUL-84 950 30

7902 FORD ANALYST 7566 05-DEC-83 3000 20

7934 MILLER CLERK 7782 21-NOV-83 1300 10

14 rows selected.

c) Listar todos os funcionrios com salrio entre 1000 e 2000

ENAME DEPTNO SAL

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

ALLEN 30 1600

WARD 30 1250

MARTIN 30 1250

TURNER 30 1500

ADAMS 20 1100

MILLER 10 1300

6 rows selected.

d) Listar os nmeros e os nomes dos departamentos, ordenados pelo nome do departamento

21

DEPTNO DNAME

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

10 ACCOUNTING

40 OPERATIONS

20 RESEARCH

30 SALES

e) Listar os diferentes tipos de cargo

JOB

---------

ANALYST

CLERK

MANAGER

PRESIDENT

SALESMAN

f) Listar os detalhes dos empregados dos departamentos 10 e 20 em ordem alfabtica de

nome.


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

7876 ADAMS CLERK 7788 04-JUN-84 1100 20


7902 FORD ANALYST 7566 05-DEC-83 3000 20



7934 MILLER CLERK 7782 21-NOV-83 1300 10


7369 SMITH CLERK 7902 13-JUN-83 800 20

g) Listar os nomes e os cargos de todos os CLERK do departamento 20.

ENAME JOB

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

SMITH CLERK

ADAMS CLERK

h) Listar os nomes de todos os empregados onde aparece TH ou LL no nome.

ENAME

----------

SMITH

ALLEN

MILLER

i) Listar os seguintes detalhes dos funcionrios que tem gerente.

ENAME JOB SAL

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

SMITH CLERK 800

ALLEN SALESMAN 1600

WARD SALESMAN 1250

JONES MANAGER 2975

MARTIN SALESMAN 1250

BLAKE MANAGER 2850

CLARK MANAGER 2450

SCOTT ANALYST 3000

TURNER SALESMAN 1500

ADAMS CLERK 1100

JAMES CLERK 950

22

FORD ANALYST 3000

MILLER CLERK 1300

j) Mostrar o nome e a remunerao total de todos os empregados.

ENAME REMUNERACAO

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

SMITH 9600

ALLEN 19500

WARD 15500

JONES 35700

MARTIN 16400

BLAKE 34200

CLARK 29400

SCOTT 36000

KING 60000

TURNER 18000

ADAMS 13200

JAMES 11400

FORD 36000

MILLER 15600

k) Mostrar todos os empregados que foram admitidos em 1983.

ENAME DEPTNO HIREDATE

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

SMITH 20 13-JUN-83

ALLEN 30 15-AUG-83

JONES 20 31-OCT-83

MARTIN 30 05-DEC-83

FORD 20 05-DEC-83

MILLER 10 21-NOV-83

l) Mostrar o nome, o salrio anual e comisso, de todos os vendedores com salrio mensal

maior do que a comisso. O resultado deve ser ordenado com os maiores salrios na frente.

Se dois ou mais empregados tiverem o mesmo salrio, deve ser ordenado pelo nome do

funcionrio.

ENAME SALARIO_ANUAL COMM

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

ALLEN 19200 300

TURNER 18000 0

WARD 15000 500

m) Selecione os dados para reproduzir a sada mostrada abaixo.

QUEM, O QUE, QUANDO

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

SMITH TEM A POSICAO DE CLERK NO DEPARTAMENTO 20 DESDE 13-JUN-83

ALLEN TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 15-AUG-83

WARD TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 26-MAR-84

JONES TEM A POSICAO DE MANAGER NO DEPARTAMENTO 20 DESDE 31-OCT-83

MARTIN TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 05-DEC-83

BLAKE TEM A POSICAO DE MANAGER NO DEPARTAMENTO 30 DESDE 11-JUN-84

CLARK TEM A POSICAO DE MANAGER NO DEPARTAMENTO 10 DESDE 14-MAY-84

SCOTT TEM A POSICAO DE ANALYST NO DEPARTAMENTO 20 DESDE 05-MAR-84

KING TEM A POSICAO DE PRESIDENT NO DEPARTAMENTO 10 DESDE 09-JUL-84

TURNER TEM A POSICAO DE SALESMAN NO DEPARTAMENTO 30 DESDE 04-JUN-84

ADAMS TEM A POSICAO DE CLERK NO DEPARTAMENTO 20 DESDE 04-JUN-84

JAMES TEM A POSICAO DE CLERK NO DEPARTAMENTO 30 DESDE 23-JUL-84

FORD TEM A POSICAO DE ANALYST NO DEPARTAMENTO 20 DESDE 05-DEC-83

MILLER TEM A POSICAO DE CLERK NO DEPARTAMENTO 10 DESDE 21-NOV-83

23

2.16 Respostas dos exerccios

a) SQL> SELECT *

2 FROM SALGRADE

b) SQL> SELECT *

2 FROM EMP

c) SQL> SELECT ENAME, DEPTNO, SAL

2 FROM EMP


d) SQL> SELECT DEPTNO, DNAME

2 FROM DEPT

3 ORDER BY DNAME

e) SQL> SELECT DISTINCT JOB

2 FROM EMP

f) SQL> SELECT *

2 FROM EMP

3 WHERE DEPTNO IN(10,20)

4 ORDER BY ENAME

g) SQL> SELECT ENAME, JOB

2 FROM EMP

3 WHERE JOB = CLERK' 4 AND DEPTNO = 20

h) SQL> SELECT ENAME

2 FROM EMP

3 WHERE ENAME LIKE '%TH%'

4 OR ENAME LIKE '%LL%'

i) SQL> SELECT ENAME, JOB, SAL

2 FROM EMP

3 WHERE MGR IS NOT NULL

j) SQL> SELECT ENAME, SAL*12+NVL(COMM,0) REMUNERACAO

2 FROM EMP

k) SQL> SELECT ENAME, DEPTNO, HIREDATE

2 FROM EMP

3 WHERE HIREDATE LIKE '%83'

l) SQL> SELECT ENAME, SAL*12 SALARIO_ANUAL, COMM

2 FROM EMP

3 WHERE SAL > COMM

4 AND JOB = 'SALESMAN'

5 ORDER BY SAL DESC, ENAME

m) SQL> SELECT ENAME||' TEM A POSICAO DE '||JOB||

2 ' NO DEPARTAMENTO '||DEPTNO||

3 ' DESDE '||HIREDATE "QUEM, O QUE, QUANDO"

4 FROM EMP

24

Captulo 3: Variveis de Substituio


Descrever as variveis de substituio e como estas so usadas nas declaraes SQL.

3.2 Variveis de substituio com um "&"

A varivel de substituio pode ser utilizada em uma declarao SELECT, representando um

valor a ser fornecido quando o comando for executado.

SQL> SELECT EMPNO, ENAME, SAL

2 FROM EMP

3 WHERE DEPTNO = &NUMERO_DO_DEPARTAMENTO

Enter value for numero_do_departamento: 10

old 3: WHERE DEPTNO = &NUMERO_DO_DEPARTAMENTO

new 3: WHERE DEPTNO = 10

EMPNO ENAME SAL

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

7782 CLARK 2450

7839 KING 5000

7934 MILLER 1300

3.3 Variveis de substituio com dois "&"

Quando so utilizados dois "&" na varivel, o SQL*PLUS solicita o valor da varivel apenas

uma vez, guardando este valor para uso subseqente.

Exemplo: &&NUMERO_DO_DEPARTAMENTO

25

Captulo 4: Funes Numricas e de Caracteres


Este captulo mostra as funes do Oracle que manipulam nmeros e sequncias de

caracteres.

4.2 Funes que manipulam caracteres

4.2.1 LOWER(coluna | literal)

Transforma letras maisculas em minsculas.

SQL> SELECT LOWER(DNAME), LOWER('CURSO DE SQL')

2 FROM DEPT

LOWER(DNAME) LOWER('CURSODESQL')

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

accounting curso de sql

research curso de sql

sales curso de sql

operations curso de sql

4.2.2 UPPER(coluna | literal)

Transforma letras minsculas em maisculas.

SQL> SELECT ENAME

2 FROM EMP

3 WHERE ENAME = UPPER('smith')

ENAME

----------

SMITH

4.2.3 INITCAP(coluna | literal)

Transforma a primeira letra da palavra em maiscula e as demais minsculas.

SQL> SELECT INITCAP(DNAME), INITCAP(LOC)

2 FROM DEPT

INITCAP(DNAME) INITCAP(LOC)

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

Accounting New York

Research Dallas

Sales Chicago

Operations Boston

4.2.4 LPAD(coluna | literal, tamanho, 'caracter')

Preenche a sequncia de caracteres, esquerda, com o caracter e o tamanho informados. Se

o caracter de preenchimento no for informado, ser utilizado o caracter de espao.

26

No exemplo abaixo, as trs colunas so preenchidas esquerda at um total de 20 caracteres.

A primeira coluna foi preenchida com asteriscos, a segunda com espaos, e a terceira com

pontos. Note que a terceira coluna numrica.

SQL> SELECT LPAD(DNAME, 20, *'), LPAD(DNAME, 20), LPAD(DEPTNO, 20,'.') 2 FROM DEPT

LPAD(DNAME,20,*') LPAD(DNAME,20) LPAD(DEPTNO,20,'.') -------------------- -------------------- --------------------

**********ACCOUNTING ACCOUNTING ..................10

************RESEARCH RESEARCH ..................20

***************SALES SALES ..................30

**********OPERATIONS OPERATIONS ..................40

4.2.5 RPAD(coluna | literal, tamanho, 'caracter')

Preenche a seqncia de caracteres, direta, com o caracter e o tamanho informados. Se o

caracter de preenchimento no for informado, ser utilizado o caracter de espao.

SQL> SELECT RPAD(DNAME, 20, *'), RPAD(DNAME, 20), RPAD(DEPTNO, 20,'.') 2 FROM DEPT

RPAD(DNAME,20,'*') RPAD(DNAME,20) RPAD(DEPTNO,20,'.')

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

ACCOUNTING********** ACCOUNTING 10..................

RESEARCH************ RESEARCH 20..................

SALES*************** SALES 30..................

OPERATIONS********** OPERATIONS 40..................

4.2.6 SUBSTR(coluna | literal, posio, comprimento)

Retorna uma seqncia de caracteres, a partir da posio indicada, com o comprimento

especificado.

SQL> SELECT SUBSTR('ORACLE',2,4), SUBSTR(DNAME,2), SUBSTR(DNAME,3,5)

2 FROM DEPT

SUBSTR('ORACLE',2,4) SUBSTR(DNAME,2) SUBSTR(DNAME,3,5)

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

RACL CCOUNTING COUNT

RACL ESEARCH SEARC

RACL ALES LES

RACL PERATIONS ERATI

4.2.7 INSTR(coluna | literal, seqncia de caracteres', posio, n)

Encontra a posio da primeira ocorrncia da seqncia de caracteres na coluna ou literal, se

os dois ltimos parmetros forem omitidos.

Encontra a posio da n-sima ocorrncia a partir da posio indicada, se os quatro

parmetros forem fornecidos.

SQL> SELECT DNAME,

2 INSTR(DNAME,'A'), INSTR(DNAME,'ES'), INSTR(DNAME,'C',1,2)

3 FROM DEPT

27

DNAME INSTR(DNAME,'A') INSTR(DNAME,'ES') INSTR(DNAME,'C',1,2)

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

ACCOUNTING 1 0 3

RESEARCH 5 2 0

SALES 2 4 0

OPERATIONS 5 0 0

4.2.8 LTRIM(coluna | literal, 'caracteres')

Remove os caracteres indicados esquerda da coluna ou do literal. Se nenhum caracter for

especificado, sero eliminados os espaos esquerda.

SQL> SELECT DNAME,

2 LTRIM(DNAME,'A'), LTRIM(DNAME,'AS'), LTRIM(DNAME,'ASOP')

3 FROM DEPT

DNAME LTRIM(DNAME,'A') LTRIM(DNAME,'AS') LTRIM(DNAME,'ASOP')

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

ACCOUNTING CCOUNTING CCOUNTING CCOUNTING

RESEARCH RESEARCH RESEARCH RESEARCH

SALES SALES LES LES

OPERATIONS OPERATIONS OPERATIONS ERATIONS

No exemplo acima, na segunda coluna foram removidas as letras "A" esquerda dos nomes

dos departamentos. Na terceira coluna foram removidas tanto a letra "A" quanto a letra "S".

Na quarta coluna foram removidas as letras "A", "S", "O" e "P" esquerda.

4.2.9 RTRIM(coluna | literal, 'caracteres')

Remove os caracteres indicados direita da coluna ou do literal. Se nenhum caracter for

especificado, sero eliminados os espaos direita.

SQL> SELECT DNAME,

2 RTRIM(DNAME,'G'), RTRIM(DNAME,'GHS'), RTRIM(DNAME,'N')

3 FROM DEPT

DNAME RTRIM(DNAME,'G') RTRIM(DNAME,'GHS') RTRIM(DNAME,'N')

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

ACCOUNTING ACCOUNTIN ACCOUNTIN ACCOUNTING

RESEARCH RESEARCH RESEARC RESEARCH

SALES SALES SALE SALES

OPERATIONS OPERATIONS OPERATION OPERATIONS

4.2.10 SOUNDEX(coluna | literal)

Retorna uma seqncia de caracteres que representa o som das palavras contidas na coluna

ou no literal.

SQL> SELECT ENAME, SOUNDEX(ENAME)

2 FROM EMP

3 WHERE SOUNDEX(ENAME) = SOUNDEX('FRED')

ENAME SOUNDEX(ENAME)

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

FORD F630

28

4.2.11 LENGTH(coluna | literal)

Retorna o nmero de caracteres (ou dgitos) na coluna ou literal especificados.

SQL> SELECT LENGTH('CURSO DE SQL'), LENGTH(DEPTNO), LENGTH(DNAME)

2 FROM DEPT

LENGTH('CURSODESQL') LENGTH(DEPTNO) LENGTH(DNAME)

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

12 2 10

12 2 8

12 2 5

12 2 10

4.2.12 TRANSLATE(coluna | literal, de, para)

Substitui os caracteres do argumento "de" pelos caracteres do argumento "para", na coluna

ou literal especificados. Todas as ocorrncias dos caracteres do parmetro "de" so

substitudas pelos caracteres no parmetro "para". Caracteres do parmetro "de" sem

correspondncia no parmetro "para" so removidos.

No exemplo abaixo foram trocados os caracteres "C" por "P" nos nomes dos funcionrios.

SQL> SELECT ENAME, TRANSLATE(ENAME,'C','P')

2 FROM EMP

3 WHERE DEPTNO = 10

ENAME TRANSLATE(ENAME,'C','P')

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

CLARK PLARK

KING KING

MILLER MILLER

No exemplo abaixo foram trocados "A" por "I", e "R" por "T", nos cargos.

SQL> SELECT JOB, TRANSLATE(JOB,'AR','IT')

2 FROM EMP

3 WHERE DEPTNO = 10

JOB TRANSLATE(JOB,'AR','IT')

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

MANAGER MINIGET

PRESIDENT PTESIDENT

CLERK CLETK

4.2.13 REPLACE(coluna | literal, de, para)

Substitui a seqncia de caracteres "de" pela seqncia de caracteres "para", na coluna ou

literal especificados.

No exemplo abaixo SALESMAN substitudo por VENDEDOR:

SQL> SELECT JOB, REPLACE(JOB,'SALESMAN','VENDEDOR')

2 FROM EMP

29

JOB REPLACE(JOB,'SALESMAN','VENDEDOR')

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

CLERK CLERK

SALESMAN VENDEDOR

SALESMAN VENDEDOR

MANAGER MANAGER

SALESMAN VENDEDOR

MANAGER MANAGER

MANAGER MANAGER

ANALYST ANALYST

PRESIDENT PRESIDENT

SALESMAN VENDEDOR

CLERK CLERK

CLERK CLERK

ANALYST ANALYST

CLERK CLERK

14 rows selected.

No exemplo abaixo, "CO" foi substitudo por "PX":

SELECT ENAME, REPLACE(ENAME,'CO','PX')

FROM EMP

ENAME REPLACE(ENAME,'CO','PX')

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

SMITH SMITH

ALLEN ALLEN

WARD WARD

JONES JONES

MARTIN MARTIN

BLAKE BLAKE

CLARK CLARK

SCOTT SPXTT SELECT DNAME, LENGTH(DNAME),

2 LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A'))

3 FROM DEPT

DNAME LENGTH(DNAME)LENGTH(DNAME)-LENGTH(TRANSLATE(DNAME,'AS','A'))

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

ACCOUNTING 10 0

RESEARCH 8 1

SALES 5 2

OPERATIONS 10 1

30

4.3 Funes numricas

4.3.1 ROUND(coluna | literal, n)

Arredonda o valor da coluna ou do literal na n-sima casa decimal. Se n for negativo

eqivale s dezenas. Se n for omitido eqivale a um nmero inteiro.

SQL> SELECT ROUND(45.923,1), ROUND(45.923), ROUND(42.323,-1),

ROUND(SAL/32,2)

2 FROM EMP

3 WHERE DEPTNO = 10

ROUND(45.923,1) ROUND(45.923) ROUND(42.323,-1) ROUND(SAL/32,2)

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

45.9 46 40 76.56

45.9 46 40 156.25

45.9 46 40 40.63

4.3.2 TRUNC(coluna | literal, n)

Trunca o valor da coluna ou do literal na n-sima posio.

SQL> SELECT TRUNC(45.923,1), TRUNC(45.923), TRUNC(42.323,-1),

TRUNC(SAL/32,2)

2 FROM EMP

3 WHERE DEPTNO = 10

TRUNC(45.923,1) TRUNC(45.923) TRUNC(42.323,-1) TRUNC(SAL/32,2)

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

45.9 45 40 76.56

45.9 45 40 156.25

45.9 45 40 40.62

4.3.3 CEIL(coluna | literal)

Retorna o menor inteiro maior ou igual ao valor da coluna ou do literal.

SQL> SELECT CEIL(SAL), CEIL(99.9), CEIL(101.76), CEIL(-11.1)

2 FROM EMP


CEIL(SAL) CEIL(99.9) CEIL(101.76) CEIL(-11.1)

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

3000 100 102 -11

5000 100 102 -11

3000 100 102 -11

4.3.4 FLOOR(coluna | literal)

Retorna o maior inteiro menor ou igual ao valor da coluna ou literal.

SQL> SELECT FLOOR(SAL), FLOOR(99.9), FLOOR(101.76), FLOOR(-11.1)

2 FROM EMP

3 WHERE FLOOR(SAL) BETWEEN 3000 AND 5000

31

FLOOR(SAL) FLOOR(99.9) FLOOR(101.76) FLOOR(-11.1)

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

3000 99 101 -12

5000 99 101 -12

3000 99 101 -12

4.3.5 POWER(coluna | literal, n)

Eleva a coluna ou literal a n-sima potncia.

SQL> SELECT SAL, POWER(SAL,2), POWER(SAL,3), POWER(50,5)

2 FROM EMP

3 WHERE DEPTNO = 10

SAL POWER(SAL,2) POWER(SAL,3) POWER(50,5)

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

2450 6002500 14706125000 312500000

5000 25000000 125000000000 312500000

1300 1690000 2197000000 312500000

4.3.6 EXP(coluna | literal)

Eleva e (2.71828183...) coluna ou literal.

SELECT EXP(4)

FROM DUAL

EXP(4)

----------

54.59815

1 row selected.

4.3.7 LOG(base, coluna | literal)

Calcula o logaritmo da coluna ou do literal na base especificada.

SELECT LOG(10,100)

FROM DUAL

LOG(10,100)

-----------

2

1 row selected.

4.3.8 LN(coluna | literal)

Calcula o logaritmo neperiano da coluna ou do literal.

SELECT LN(95)

FROM DUAL

LN(95)

----------

4.55387689

4.3.9 SQRT(coluna | literal)

Retorna a raiz quadrada da coluna ou do literal.

32

SQL> SELECT SAL, SQRT(SAL), SQRT(40), SQRT(COMM)

2 FROM EMP

3 WHERE COMM > 0

SAL SQRT(SAL) SQRT(40) SQRT(COMM)

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

1600 40 6.324555320336759 17.32050807568877

1250 35.35533905932738 6.324555320336759 22.3606797749979

1250 35.35533905932738 6.324555320336759 37.41657386773941

4.3.10 SIGN(coluna | literal)

Retorna -1 se o valor da coluna ou do literal for negativo, retorna 0 se for igual a zero, ou

retorna 1 se for maior do que zero.

SQL> SELECT SAL-COMM, SIGN(SAL-COMM), COMM-SAL, SIGN(COMM-SAL)

2 FROM EMP

3 WHERE DEPTNO = 30

SAL-COMM SIGN(SAL-COMM) COMM-SAL SIGN(COMM-SAL)

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

1300 1 -1300 -1

750 1 -750 -1

-150 -1 150 1

1500 1 -1500 -1

Salrios maiores do que a comisso podem ser listados conforme mostrado abaixo:

SQL> SELECT ENAME, SAL, COMM

2 FROM EMP

3 WHERE SIGN(SAL-COMM) = 1

ENAME SAL COMM

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

ALLEN 1600 300

WARD 1250 500

TURNER 1500 0

4.3.11 ABS(coluna | literal)

Retorna o valor absoluto da coluna ou do literal.

SQL> SELECT SAL, COMM, COMM-SAL, ABS(COMM-SAL)

2 FROM EMP

3 WHERE DEPTNO = 30

SAL COMM COMM-SAL ABS(COMM-SAL)

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

1600 300 -1300 1300

1250 500 -750 750

1250 1400 150 150

2850

1500 0 -1500 1500

950

4.3.12 MOD(valor1, valor2)

Retorna o resto da diviso de valor1 por valor2.

33

SQL> SELECT SAL, COMM, MOD(SAL,COMM), MOD(100,40)

2 FROM EMP

3 WHERE DEPTNO = 30

4 ORDER BY COMM

SAL COMM MOD(SAL,COMM) MOD(100,40)

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

1500 0 1500 20

1600 300 100 20

1250 500 250 20

1250 1400 1250 20

2850 20

950 20

4.3.13 Funes trigonomtricas

Calculam o seno (SIN), coseno (COS), e tangente (TAN) da coluna ou literal.

SELECT SIN(30*3.141592654/180),

COS(60*3.141592654/180),

TAN(45*3.141592654/180)

FROM DUAL

SIN(30*3.1 COS(60*3.1 TAN(45*3.1

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

.5 .5 1

4.3.14 Funes hiperblicas

Calculam o seno hiperblico (SINH), coseno hiperblico (COSH) e tangente hiperblica

(TANH) da coluna ou literal.

SELECT SINH(1), COSH(0), TANH(.5)

FROM DUAL

SINH(1) COSH(0) TANH(.5)

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

1.17520119 1 .462117157

4.4 Exerccios

a) Listar o nmero do departamento, o nome do funcionrio, e o salrio com aumento de

15% expresso em nmero inteiro.

DEPTNO ENAME PCTSAL

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

20 SMITH 920

30 ALLEN 1840

30 WARD 1438

20 JONES 3421

30 MARTIN 1438

30 BLAKE 3278

10 CLARK 2818

20 SCOTT 3450

10 KING 5750

30 TURNER 1725

20 ADAMS 1265

30 JAMES 1093

20 FORD 3450

10 MILLER 1495

34

b) Gerar a listagem mostrada a seguir:

EMPREGADO_E_CARGO

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

SMITH CLERK

ALLEN SALESMAN

WARD SALESMAN

JONES MANAGER

MARTIN SALESMAN

BLAKE MANAGER

CLARK MANAGER

SCOTT ANALYST

KING PRESIDENT

TURNER SALESMAN

ADAMS CLERK

JAMES CLERK

FORD ANALYST

MILLER CLERK

14 rows selected.

c) Listar uma lista contendo todos os empregados com um identificador composto pelas duas

primeiras letras de seus cargos, os dois dgitos do meio de seus nmeros de matrcula, e o

cdigo sonoro de seus nomes.

NOME CODIGO

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

SMI

Documents

Banco de Dados Oracle