Apostila-SQL

Prévia do material em texto

Índice
 Conteúdo 3
I - Introdução 4
1.1 - Objetivos do curso 5
1.2 - O que é SQL*Plus 6
II - Comandos SQL e SQL*Plus 7
II-Conteúdo 8
11.1 - Ativação do SQL*Plus 9
11.2 - Criação de tabelas 10
11.3 - Manipulação de tabelas 12
11.4 - Formatação de relatórios 16
11.5 - Edição de comandos 17
11.6 - Outros comandos 22
II - Exercícios 23
III - Consultas básicas 24
111-Conteúdo 25
111.1 - Seleção de colunas 26
111.2- Uso de expressões 27
111.3 - Seleção de linhas 29
111.4 - Ordenação de resultados 31
111.5 - Agrupamento 33
111.6 - Operações de conjunto 35
111.7 - Junção de tabelas 39
III - Exercícios 44
IV- Criação e uso de tabelas 45
1V-Conteúdo 46
1V.1 -CREATETABLE 47
IV.2 - Tipos de colunas 51
IV.3 - Alteração de uma tabela 52
lV.4 - O comando INSERT 54
lV.5 - O comando DELETE 56
IV.6 - O comando UPDATE 57
IV.7 - Controle de transações 59
lV.8 - Manipulação de visões 67
IV.9 - Criação de seqüências 72
IV - Exercícios 73
V -Geração de Relatórios 75
V - Conteúdo 76
V.1 - Formatação de colunas 77
V.2 - Formatação de títulos 80
V.3 - BREAK e COMPUTE 84
V.4 - Parâmetros de controle 89
V.5 - Utilização de variáveis 93
V.6 - Relatórios avançados 98
1
 
 V - Exercícios 107
VI - Expressões e funções 110
VI- Conteúdo 111
VI.1 - Operadores 112
Vl.2 - Funções numéricas 114
VI.3 - Funções de caracteres 116
VI.4 - Funções de grupo 119
VI.5 - Conversão entre tipos 120
VI.6 - Funções de datas 123
Vl.7 - Outras funções 124
VI – Exercícios 125
VII - Consultas avançadas 126
VII-Conteúdo 127
VII.1 - Pseudo-colunas 128
VII.2 - Junção externa 129
VII.3 - Auto-Junção 132
VII.4 - Consultas encaixadas 133
VII.5 - Consultas hierárquicas 138
VII - Exercícios 141
VIII - Tópicos avançados 142
VIII - Conteúdo 143
VIII.1 - Manutenção de usuários 144
VllI.2 - Privilégios de acesso 146
VIII.3 - Acesso concorrente 149
VIII.4 - lndices 153
VIII.5 - Clusters 157
VIII.6 - Dicionário de dados 162
VIII - Exercícios 168
Ap. A- Tabelas utilizadas 169 
Ap. B- Sumário dos Comandos 174
Ap. C- Resposta dos exercícios 178
2
Conteúdo
I – Introdução
II – Comando SQL e SQL*Plus
III – Consultas básicas
IV – Criação e uso de tabelas
V – Geração de relatórios
VI – Expressão e Funções
VII – Consultas avançadas
VIII – Tópicos avançados
Apêndice A – Tabelas utilizadas
Apêndice B – Sumário dos comandos
3
 
 I – Introdução
4
I.1 – Objetivos do Curso
• Neste curso você aprenderá a usar o SQL*Plus para:
- Definir e alterar a estrutura de um banco de dados
- Consultar, inserir, alterar ou remover informações de um 
banco de dados
- Gerar relatórios a partir de consultas
- Gerenciar a segurança e o desempenho de um banco de 
dados 
I – Introdução
5
comando 
do controle
I.2 – O que é SQL*Plus
• É a interface básica para manipulações genéticas de um banco 
de dados ORACLE
 O SQL + SQL
 BD
 ORACLE
 
• Características principais:
- interpretador de comando SQL
- possui extensões para formatação de relatórios
I – Introdução
6
SQL*PLUS
Parâmetros de
Controle de
formatação
 II – Comandos SQL e SQL*Plus
7
II - Conteúdo
1. Ativação do SQL*Plus
2. Criação de tabelas
3. Manipulação de tabelas
4. Formatação de relatórios
5. Edição de comandos
6. Outros comandos
II – Comandos SQL e SQL*Plus
8
II.1 – Ativação do SQL*Plus
• Para entrar no SQL*Plus, digite:
 ou
• Para sair do SQL*Plus, digite:
II – Comandos SQL e SQL*Plus
9
$ sqlplus <ENTER>
SQL*Plus: Version 3.0.6.5.1 – Production on Wed Apr 3 08:56...
Copyright (c) ORACLE Corporation 1979, 1989. All...
Enter user-name: ora1
Enter password: ____
Connected to: ORACLE RDBMS V6.0.27.9.2, with transaction...
PL/SQL V1.0.29.1.0 – Beta
SQL>
a senha não é exibida
$ sqlplus ora 1/senha1
SQL*Plus: Version 3.0.6.5.1 – Production on Wed Apr 3 08:56 ...
Copyright (c) ORACLE Corporation 1979, 1989. All ... 
Connected to: ORACLE RDBMS V6.0.27.9.2, with transaction ...
PL / SQL V1.0.29.1.0 – Beta
SQL >
a senha é exibida
SQL > exit
$
II.2 – Criação de tabelas
• O que é uma tabela?
II – Comandos SQL e SQL*Plus
10
N_EMP NOME_EMP CARGO CHEFE DATA_ADM SAL COM N_DEP
103 SAMANTA ANALISTA 110 17-MAR-85 150000 20
110 UBIRATAN DIRETOR 175 20-APR-85 300000 20
175 PAULO PRESIDENTE 27-FEB-86 500000 10
189 RITA DIRETOR 175 25-DEC-85 330000 5000 30
201 EVERALDO VENDEDOR 189 28-SEP-86 200000 3000 30
208 SILVIO VENDEDOR 189 30-OCT-86 150000 9000 30
230 ANA SECRETARIA 175 01-MAY-89 120000 10
276 RENATO ANALISTA 110 30-APR-86 100000 20 
 
 
Coluna
Nome da coluna Linha (ou registro)
Campos
• Para criar a tabela de empregados
II – Comandos SQL e SQL*Plus
11
SQL > create table emp (
2 n_emp number (4) not null,
3 nome_emp char (10),
4 cargo char (10)
5 chefe number (4),
6 data_adm date,
7 sal number (10,2),
8 com number (10,2),
9 n_dep number (2) );
“.” Separa as 
definições
das colunas
“.” Executa o 
comando
Indica a continuação do 
comando
N_EMP NOME_EMP CARGO CHEFE DATA_ADM SAL COM N_DEP
Somente o 
cabeçalho
II.3 Manipulação de tabelas
• Para inserir informações na tabela EMP:
II – Comandos SQL e SQL*Plus
12
SQL > insert into emp values
2 ( 103, `SAMANTA`, `ANALISTA`, 110,
 3 ´17-MAR-85´, 150000, NULL, 209;
SQL > insert into emp values
2 ( 110, ´UBIRATAN´, ´DIRETOR´, 175, ´20-APR-85´,
3 300000, NULL, 20);
N_EMP NOME_EMP CARGO CHEFE DATA_ADM SAL COM N_DEP
103 SAMANTA ANALISTA 110 17-MAR-85 150000 20
110 UBIRATAN DIRETOR 175 20-APR-85 300000 20
Nome da coluna
• Para recuperar as informações da tabela EMP:
• Para recuperar apenas o nome dos empregados que são 
analistas:
II – Comandos SQL e SQL*Plus
13
SQL > select * from emp;
N_EMP NOME_EMP CARGO CHEFE DATA_ADM SAL COM N_DEP
 
103 SAMANTA ANALISTA 110 17-MAR-85 150000 20
110 UBIRATAN DIRETOR 175 20-APR-85 300000 20
175 PAULO PRESIDENTE 27-FEB-86 500000 10
189 RITA DIRETOR 175 25-DEC-85 330000 5000 30
201 EVERALDO VENDEDOR 189 28-SEP-86 200000 3000 30
208 SILVIO VENDEDOR 189 30-OCT-86 150000 9000 30
230 ANA SECRETARIA 175 10-MAY-89 120000 10
276 RENATO ANALISTA 110 30-APR-86 100000 20 
 
SQL > select nome_emp
2 from emp3 where cargo = ´ANALISTA`;
NOME_EMP
SAMANTA
RENATO
Condição a ser satisfeita
Maiúsculas e minúsculas 
somente
fazem diferença
• Para remover o empregado de número 110:
• Para remover todos os empregados admitidos a partir de 1989:
II – Comandos SQL e SQL*Plus
14
SQL > delete from emp
 2 where n_emp = 110;
SQL > delete from emp
 2 where data_adm > = `01-JAN-89´;
• Para atualizar o salário do funcionário 110:
II – Comandos SQL e SQL*Plus
15
N_EMP NOME_EMP ... SAL ...
110 UBIRATAN 300000
SQL > update emp
2 set sal = 350000
3 where n_emp= 110;
N_EMP NOME_EMP ... SAL ...
110 UBIRATAN 350000
II.4 Formatação de relatórios
• Comandos SQL x Comandos SQL*Plus
- SQL – acesso ao banco de dados
- SQL*Plus – formato de apresentação dos resultados, 
basicamente.
• Para produzir um relatório com o nº, nome e salário de todos os 
funcionários:
 
16
SQL > column n_emp heading NUMERO
SQL > column nome_emp heading NOME
SQL > column sal format 999999.99 –
> heading SALARIO
SQL > select n_emp, nome_emp, sal from emp;
Comandos SQL*Plus não
Precisam de “;”
Para continuar um 
comando,
Use o caractere “_”
NUMERO NOME SALARIO
103 SAMANTA 150000.00
110 UBIRATAN 300000.00
175 PAULO 500000.00
189 RITA 330000.00
201 EVERALDO 200000.00
208 SILVIO 150000.00
230 ANA 120000.00
276 RENATO 100000.00
II – Comandos SQL e SQL*Plus
II.5 – Edição de comandos
• O ultimo comando SQL (não SQL*Plus) fica armazenado no 
buffer de edição do SQL*Plus
• Para listar o conteúdo do buffer:
• Para apagar a linha corrente:
17
SQL > list
ou
SQL > I
1 select
2 n_emp, nome_emp
3 from emp
4* where n_emp =110
Qualquer em desses comando
Poder ser utilizado
“*” indica a linha corrente
SQL > del
SQL > I
1 select
2 n_emp, nome_emp
3* from emp
II – Comandos SQL e SQL*Plus
• Para inserir linha após a linha corrente:
• Para acrescentar caracteres no fim da linha corrente:
18
SQL > list 2
 2* n_emp, nome_emp
SQL > input
ou
SQL > I
3 cargo,
4 sal
5 
SQL > list
1 select
2 n_emp, nome_emp
3 cargo,
4 sal
5* from emp
Deixe uma linha em branco
Para terminar a inserção
SQL > list 2
 2* n_emp, nome_emp
SQL > append, chefe,
ou
SQL > a, chefe,
SQL > list
1 select
2 n_emp, nome_emp, chefe,
3 cargo,
4 sal
5* from emp
II – Comandos SQL e SQL*Plus
• Para substituir caracteres da linha corrente:
• Para executar o comando existente no buffer:
19
SQL > list 2
 2* n_emp, nome_emp, chefe,
SQL > change/chefe/sal/
ou
SQL > c/chefe/sal/
SQL > list
1 select
2 n_emp, nome_emp, sal,
3 cargo,
4 sal
5* from emp
SQL > run
ou
 SQL > r
ou
SQL > /
II – Comandos SQL e SQL*Plus
• O SQL*Plus permite a utilização de outros buffers além do 
buffer de comandos SQL
• Para ativar um buffer chamado “TESTE”, por exemplo:
• Para saber qual é o buffer corrente:
• Para modificar o conteúdo do buffer corrente com um editor 
externo:
 
• O editor externo pode ser especificado pelo usuário da seguinte 
forma: 
20
SQL > set buffer teste
SQL > show buffer
buffer TESTE
SQL > edit
SQL > define _ editor = vi
II – Comandos SQL e SQL*Plus
• para salvar o buffer corrente num arquivo do Sistema 
Operacional:
• Para recuperar um comando SQL armazenado num arquivo: 
• Para executar um seqüência de comando (SQL ou SQL*Plus) 
armazenada num arquivo:
• Para editar um arquivo qualquer do sistema operacional:
 
21
SQL > save arquivo
nome do arquivo; a extensão .sql é
acrescentada automaticamente
SQL > get arquivo
SQL > start arquivo
ou
SQL > @arquivo
SQL > edit en21
É assumida a extensão “.sql”
II – Comandos SQL e SQL*Plus
II.6 – Outros comandos
• Para executar um comando qualquer do sistema operacional:
• Para obter ajuda sobre um comando SQL ou SQL*Plus:
• Para saber todos os comandos disponíveis:
22
SQL > host
ou
SQl > !
total 20
drwxr-xr-x 2 curso oracle 128 Jan 3 18:02 .
drwxr-xr-x 3 curso oracle 1024 Jan 3 17:59 ..
-rw-r-r- 1 curso oracle 800 Jan 3 17:59 ex21.sql
-rw-r-r- 1 curso oracle 838 Jan 3 17:59 ex22.sql
-rw-r-r- 1 curso oracle 1152 Jan 3 18:01 ex23.sql
-rw-r-r- 1 curso oracle 1616 Jan 3 18:01 ex24.sql
-rw-r-r- 1 curso oracle 1796 Jan 3 18:01 ex25.sql
SQL > help select
nome do comando
SQL > help commands
II – Comandos SQL e SQL*Plus
II - Exercícios
1. Crie uma tabela chamada “ALUNO” com as seguintes colunas:
NOME CHAR( 30 ) NOT NULL
ENDER CHAR( 50 )
CIDADE CHAR( 25 )
ESTADO CHAR( 2 )
CEP NUMBER( 5 )
ANIVERSARIO DATE
dica: veja um exemplo na página 11
2. Insira dados sobre você e algum membro da sua família na tabela 
ALUNO
(dica: exemplos na página 12)
3. Consulte o nome e a data de aniversário das pessoas cadastradas
(dica: exemplo na página 13)
4. Consulte o seu (e somente o seu ) endereço completo
5. Salve o comando anterior no arquivo “ex24.sql”, saia do SQL*Plus, 
entre novamente e execute o comando contido no arquivo gerado 
23
II – Comandos SQL e SQL*Plus
III – Consultas básicas
24
III - Conteúdo
1. Seleção de colunas
2. Uso de expressões
3. Seleção de linhas
4. Ordenação de resultados
5. Agrupamento
6. Operação de conjunto
7. Junção de tabelas
III – Consultas básicas
25
III.1 – Seleção de colunas
• Para indicar as colunas a serem recuperadas de uma tabela:
• A ordem em que as colunas são especificadas muda a 
apresentação do resultado:
III – Consultas básicas
26
SQL > select n_emp, nome_emp
2 from emp
3 where n_emp < 200;
N_EMP NOME_EMP
103 SAMANTA
110 UBIRATAN
175 PAULO
189 RITA
SQL > select nome_emp, n_emp
2 from emp
3 where n_emp < 200;
NOME_EMP N_EMP
SAMANTA 103
UBIRATAN 110
PAULO 175
RITA 189
III.2 – Uso de expressões
• Para saber o salário anual dos empregados
III – Consultas básicas
27
NOME_EMP CARGO SAL
SAMANTA ANALISTA 150000
UBIRATAN DIRETOR 300000
PAULO PRESIDENTE 500000
RITA DIRETOR 330000
EVERALDO VENDEDOR 200000
SILVIO VENDEDOR 150000
ANA SECRETARIA 120000
RENATO ANALISTA 100000
SQL > select nome_emp, 12* sal from emp;
NOME_EMP 12*SAL
SAMANTA 1800000
UBIRATAN 3600000
PAULO 6000000
RITA 3960000
EVERALDO 2400000
SILVIO 1800000
ANA 1440000
RENATO 1200000
• Para saber o nº total de caracteres nas colunas NOME_EMP E 
CARGO
III – Consultas básicas 
28
NOME_EMP CARGO SAL
SAMANTA ANALISTA 150000
UBIRATAN DIRETOR 300000
PAULO PRESIDENTE 500000
RITA DIRETOR 330000
EVERALDO VENDEDOR 200000
SILVIO VENDEDOR 150000
ANA SECRETARIA 120000
RENATO ANALISTA 100000
SQL > select
2 length ( nome_emp ) +
3 length ( cargo ) “Comprimento Total”
4 from emp;
Comprimento Total
 
 15
 15
 15
 11
 16
 14
 13
 14
III.3 – Seleção de linhas
• Para saber os nomes dos empregados que trabalham no 
departamento 20:
29
N_EMP NOME_EMP CARGO ... N_DEP
103 SAMANTA ANALISTA ... 20
110 UBIRATAN DIRETOR ... 20
175 PAULO PRESIDENTE ... 10
189 RITA DIRETOR ... 30
201 EVERALDO VENDEDOR ... 30
208 SILVIO VENDEDOR ... 30
230 ANA SECRETARIA ... 10
276 RENATO ANALISTA ... 20
SQL > select nome_emp
3 from emp
4 where n_dep = 20;
NOME_EMP
SAMANTA
UBIRATAN
RENATOIII – Consultas básicas
• Para saber o tempo de serviço dos funcionários cuja comissão é 
maior que 5% do salário:
30
SQL > select nome_emp,
2 months_between( sysdate, data_adm ) MESES
3 from emp
4 where com > sal * 0.05;
NOME_EMP MESES
SILVIO 48.689726
III – Consultas básicas
III.4 – Ordenação de resultados
• Para listar os funcionários em ordem alfabética:
31
SQL > select nome_emp, cargo, sal
2 from emp
3 order by nome_emp;
NOME_EMP CARGO SAL
ANA SECRETARIA 120000
EVERALDO VENDEDOR 200000
PAULO PRESIDENTE 500000
RENATO ANALISTA 100000
RITA DIRETOR 330000 
SAMANTA ANALISTA 150000
SILVIO VENDEDOR 150000
UBIRATAN DIRETOR 300000
III – Consultas básicas
• Para listar os empregados em ordem decrescente de salário, por 
cargo:
• Pode-se usar expressões ou referencias à posição de expressões 
no SELECT como argumento do ORDER BY:
32
SQL > select nome_emp, cargo, sal
2 from emp
3 where cargo in ( `ANALISTA`, ´VENDEDOR`,
4 `DIRETOR`, ´SECRETARIA` )
5 order by cargo, sal desc; 
NOME_EMP CARGO SAL
SAMANTA ANALISTA 150000
RENATO ANALISTA 100000
RITA DIRETOR 330000
UBIRATAN DIRETOR 300000
ANA SECRETARIA 120000
EVERALDO VENDEDOR 200000
SILVIO VENDEDOR 150000
SQL > select nome_emp, cargo, length( cargo ), sal
2 from emp
3 where cargo in (`ANALISTA`, `VENDEDOR`,
4 `DIRETOR`, `SECRETARIA`)
5 order by 3, sal – nvl(com, 0 ) desc;
Indica a 3ª expressão do
SELECT, que é
“length(cargo)”
III – Consultas básicas
III.5 - Agrupamento
• Para saber quantos funcionários trabalham em cada 
departamento:
• Para saber quantos funcionários trabalham em cada 
departamento:
22222222222jhglkjhgflkgjhfdlghfg ,lhdlfgn v
• Nota : omitir a cláusula “GROUP BY” significa agrupar toda a 
tabela
33
N_EMP NOME_EMP CARGO CHEFE DATA_ADM SAL COM N_DEP
230 ANA SECRETARIA 175 01-MAY-89 120000 10
175 PAULO PRESIDENTE 27-FEB-86 500000 10
103 SAMANTA ANALISTA 110 17-MAR-85 150000 20
276 RENATO ANALISTA 110 30-APR-86 100000 20
110 UBIRATAN DIRETOR 175 20-APR-85 300000 20
189 RITA DIRETOR 175 25-DEC-85 330000 5000 30
201 EVERALDO VENDEDOR 189 28-SEP-86 200000 3000 30
208 SILVIO VENDEDOR 189 30-OCT-86 150000 9000 30
SQL > select n_dep, count(*)
2 from emp
3 group by n_dep;
2
3
3
coluna do agrupamento “todas as linhas”
Função de grupo
critério de agrupamento
N_DEP COUNT(*)
10 2
20 3
30 3
III – Consultas básicas
• Para saber a soma dos salários para os cargos onde a média de 
salários é maio que 150000:
34
N_EMP NOME_EMP CARGO ... SAL ...
103 SAMANTA ANALISTA ... 150000 ...
276 RENATO ANALISTA ... 100000 ...
110 UBIRATAN DIRETOR ... 300000 ...
189 RITA DIRETOR ... 330000 ...
175 PAULO PRESIDENTE ... 500000 ...
230 ANA SECRETARIA ... 120000 ...
201 EVERALDO VENDEDOR ... 200000 ...
208 SILVIO VENDEDOR ... 150000 ...
AVG = 125000
AVG = 315000 
AVG = 120000
AVG = 175000
SQL > select cargo, sum( sal ), max( sal )
2 from emp
3 group by cargo
4 having avg( sal ) > 150000;
seleciona os 
grupos
(“where” de 
função de grupo
CARGO SUM(SAL) MAS(SAL)
DIRETOR 630000 330000
PRESIDENTE 500000 500000
VENDEDOR 350000 200000
III – Consultas básicas 
III.6 – Operações de conjunto
• Uma tabela pode ser encerada como um conjunto
• Os elementos do conjunto são as linhas da tabela:
Col. 1 Col. 2
A 1
B 1
D 2
Tabela Conjunto
• Existem três operações básicas de conjunto em SQL:
- união (UNION) 
 
- intersecção (INTERSECT)
- diferença (MINUS)
35
 (A, 1)
 (B, 1)
 (D, 2)
elementos
do
conjunto
III – Consultas básicas
• União:
 TABELAS CONJUNTOS
 
 TAB1 TAB2 Cj1 Cj2 
Col. 1 Col. 2
A 1
B 1
D 2
 
 colunas com tipo igual 
 select col1, col2 from tab1
 Cj1 U Cj2
 union
 select col1, col2 from tab2 
Col. 1 Col. 2
A 2
B 1
E 2
D 2
Col. 1 Col. 2
A 1
A 2
B 1
D 2
E 2
36
(A, 1)
(B, 1)
(D, 2)
 (A, 2)
(B, 1)
(E, 2)
(D, 2)
(A, 1)
(A, 2)
(B, 1)
(D, 2)
(E, 2)
III – Consultas básicas
• Intersecção:
 TABELAS CONJUNTOS
 
 TAB1 TAB2 Cj1 Cj2 
Col. 1 Col. 2
A 1
B 1
D 2
 
 colunas com tipo igual 
 select col1, col2 from tab1
 Cj1 inter Cj2
 intersect
 select col1, col2 from tab2 
Col. 1 Col. 2
A 2
B 1
E 2
D 2
Col. 1 Col. 2
B 1
D 2
37
(A, 1)
(B, 1)
(D, 2)
 (A, 2)
(B, 1)
(E, 2)
(D, 2)
(B, 1)
(D, 2)
III – Consultas básicas
• Diferença:
 TABELAS CONJUNTOS
 
 TAB1 TAB2 Cj1 Cj2 
Col. 1 Col. 2
A 1
B 1
D 2
 
 colunas com tipo igual 
 select col1, col2 from tab1
 Cj1 - Cj2
 minus
 select col1, col2 from tab2 
Col. 1 Col. 2
A 2
B 1
E 2
D 2
38
(A, 1)
(B, 1)
(D, 2)
 (A, 2)
(B, 1)
(E, 2)
(D, 2)
III – Consultas básicas
III.7 – Junção de tabelas
• A junção de tabelas corresponde à operação de produtos 
cartesiano entre conjuntos:
 
 X
 tab1 tab2
col1 col2 col3 col4 col5
A 3 B 2 E
Col. 1 Col. 2
A 1
39
(A, 1)
A,
B, C 1, 2
(A, 1), (A, 2),
(B,1), (B,2),
(C,1), (C, 2)
select col1, col2, col3, col4, col5
from tab1, tab2;
Col1 col2 col3 col4 col5
A 3 B 2 E
III – Consultas básicas
• Para saber o nome do departamento onde cada empregado 
trabalha:
40
SQL > select nome_emp, nome_dep
2 from emp, dep
3 where emp.n dep = dep.n_dep; 
produto cartesiano
critério de seleção da junção 
para diferenciar coluna 
de
mesmo nome usa-se o 
no-
NOME_EMP NOME_DEP
PAULO ADMINISTRAÇÃO 
ANA ADMINISTRAÇÃO
SAMANTA PESQUISA
UBIRITAN PESQUISA
RENATO PESQUISA
RITA VENDAS
SILVIO VENDAS
EVERALDO VENDAS
III – Consultas básicas
41
NOME_EMP NOME_DEP EMP.N_DEP DEP.N_DEP
SAMANTA ADMINISTRAÇÃO 20 10
UBIRATAN ADMINISTRAÇÃO 20 10
UBIRATAN ADMINISTRAÇÃO 20 10
PAULO ADMINISTRAÇÃO 10 10
RITA ADMINISTRAÇÃO 30 10
EVERALDO ADMINISTRAÇÃO 30 10
SILVIO ADMINISTRAÇÃO 30 10
ANA ADMINISTRAÇÃO 10 10
RENATO ADMINISTRAÇÃO 20 10
SAMANTA PESQUISA 20 20
UBIRATAN PESQUISA 20 20
PAULO PESQUISA 10 20
RITA PESQUISA 30 20
EVERALTO PESQUISA 30 20
SILVIO PESQUISA 30 20
ANA PESQUISA 10 20
RENATO PESQUISA 20 20
SAMANTA VENDAS 20 30
UBIRATAN VENDAS 20 30
PAULO VENDAS 10 30
RITA VENDAS 30 30
EVERALDO VENDAS 30 30
SILVIO VENDAS 30 30
ANA VENDAS 10 30
RENATO VENDAS 20 30
SAMANATA PRODUÇÃO 10 40
PAULO PRODUÇÃO 10 40
RITA PRODUÇÃO 30 40
EVERALDO PRODUÇÃO 30 40
SILVIO PRODUÇÃO 30 40
ANA PRODUÇÃO 10 40
RENATO PRODUÇÃO 20 40
III – Consultas básicas
• Dada a tabela FAIXA_SAL,que classifica faixas de salários:
• Para saber a faixa salarial de todos os empregados:
42
SALMIN SALMAX F
1 99999 A
 100000 149999 B
 150000 199999 C
 200000 399999 D
 400000 800000 E
SQL > select nome_emp, sal, faixa
2 from emp, faixa_sal
3 where sal between salmin and salmax;
NOME_EMP SAL F
ANA 120000 B
RENATO 100000 B
SAMANTA 150000 C
SILVIO 150000 C
UBIRATAN 300000 D
RITA 300000 D
EVERALDO 200000 D
PAULO 500000 E
 
especifica um tipo 
de junção
III – Consultas básicas
• Pode-se combinar diversas opções no mesmo comando SQL:
43
SQL > select nome_dep, faixa, count( * )
2 from emp, dep, faixa_sal
3 where emp.n_dep = dep.n_dep and
4 sal between salmin and salmax
5 group by nome_dep, faixa
6 having sum( sal ) > 120000
7 order by nome_dep, count( * ) desc
NOME_DEP F COUNT(*)
ADMINISTRAÇÃO E 1
PESQUISA C 1
PESQUISA D 1
VENDAS D 2
VENDAS C 1
III – Consultas básicas
III - Exercícios
1. Consulte o cargo, o nome e o salário por hora (assumindo 160 
horas/mês) de todos os funcionários da tabela EMP; salve seu 
exercício no arquivo “ex31.sql”.
2. Consulte o nome e o total mensal (salário mais comissão) 
recebido pelos vendedores da tabela EMP.
3. Carregue o conteúdo do arquivo “ex31.sql” no buffer corrente e 
modifique-o de forma a ordenar o resultado em ordem 
decrescente do salário por hora; salve seu exercício novamente, 
no mesmo arquivo. 
4. Descubra o menor salário de todos os empregados. Dica: vela a 
nora da página 33.
5. Descubra o menor, o maior e a média dos salários de cada 
departamento, para aqueles departamentos que tem mais de um 
empregado. Dica: veja a página 34.
 
6. Consulte o nome e o departamento de todos os empregados que 
trabalham em São Paulo ou Campinas; salve sei exercícios no 
arquivo “ex36.sql”. Dica: faça uma junção da tabela EMP com a 
tabela DEP (página 42), e recupere somente as linhas onde a 
localidade do departamento é São Paulo ou Campinas.
44
III – Consultas básicas
IV – Criação e uso de tabelas
45
IV – Conteúdo
1. CREATE TABLE
2. Tipos de colunas
3. Alteração de uma tabela
4. O comando INSERT
5. O comando DELETE
6. O comando UPDATE
7. Controle de transações
8. Manipulação de visões
9. Criação de seqüências
46
IV – Criação e uso de tabelas
IV.1 – CREATE TABLE
• O comando CREATE TABLE é usado para criar novas tabelas 
no banco de dados
- Forma 1:
 
» nome_da_tabela nome da tabela a ser criada
» coluna_1,... colunas da tabela
» tipo1, tipo2,... tipos das respectivas colunas
» tamanho tamanho para um tipo
» restrições uma combinação de zero ou mais de:
>NULL ou NOT indica que a coluna pode conter 
valores nulos (opção “default”) ou não
>DEFAULT expr determina o valor “default” da coluna 
como sendo expr
47
CREATE TABLE nome_da_tabela
 (
coluna_1 tipo1,
coluna_2 tipo2( tamanho ),
coluna_3 tipo3 restrições,
coluna_4 tipo4( tamanho ) restrições
restrições
 );
especificação do tamanho
entre parênteses
Definição das 
colunas separadas 
por “,”
>UNIQUE indica que não podem existir valores 
duplicados para esta coluna. Não pose 
ser usado com PRIMARY KEY
>PRIMARY KEY indica que esta coluna é chave primaria. 
Não pode ser usado com UNIQUE.
IV – Criação e uso de tabelas
>REFERENCES indica que o valor desta coluna, 
se existir, 
/ FOREINGN deve existir também na coluna 
col da tabela
 KEY tab (col) primária tab. As colunas 
referenciadas na tabela primária 
devem ser PRIMARY KEY ou 
UNIQUE. 
 
>CHECK determina que o valor desta coluna 
deve condição obedecer á condição lógica 
especificada
>CONSTRAINT determina que o valor desta coluna 
deve 
nome_restr obedecer à restrições nome_restr
 OBS: Embora todas as restrições acima possam ser definidas, o 
RDBMS ORACLE versão 6 só garante a primeira: NULL ou 
NOT NULL. Entretanto, o SQL*Forms 3.0 é capaz de gerar 
código automaticamente para vários tipos de restrições. 
 Exemplo:
48
SQL > CREATE TABLE emp
 2 ( n_emp number(4) NOT NULL PRIMARY KEY,
 3 nome_emp char(10) NOT NULL,
 4 cargo char(10),
 5 chefe number(4) REFERENCES emp(n_emp),
 6 data_adm date DEFAULT sysdate
7 CHECK (data_adm < = sysdate),
8 sal number(10,2),
9 com number(10,2) CHECK(com > 0),
10 n_dep number(2) REFERENCES dep(n_dep) );
IV – Criação e uso de tabelas
- Forma 2:
- Cria uma tabela igual ao resultado da consulta executada 
com o comando “SELECT” 
- Exemplo:
49
CREATE TABLE tabelas AS
SELECT col1, col2, ... coln
FROM ...
SQL > create table sal_médio as
2 select nome_dep, avg( sal ) “Media_Salario”
3 from dep, emp
4 where emp.n_dep = dep.n_dep
5 group by nome_dep;
SQL > select * from sal_médio;
NOME_DEP Media_Salário
ADMINISTRAÇÃO 310000
PESQUISA 183333.333
VENDAS 226666.667
IV – Criação e uso de tabelas
• Para verificar a estrutura de uma tabela
• Para remover uma tabela:
• Para mudar o nome de uma tabela: 
50
SQL > describe emp
Ou
SQL > desc emp
Name Null? Type
N_EMP NOT NULL NUMBER(4)
NOME_EMP NOT NULL CHAR(10)
CARGO CHAR(10)
CHEFE NUMBER(4)
DATA_ADM DATE
SAL NUMBER(10,2)
COM NUMBER(10,2)
N_DEP NUMBER(2)
SQL > drop table faixa_sal;
SQL > rename dep to departamento;
IV – Criação e uso de tabelas
IV.2 – Tipos de colunas
• O Oracle suporta os seguintes tipos:
 CHAR [(tamanho)]
 VARCHAR [(tamanho)] (sinônimo de CHAR)
 CHARACTER [(tamanho)] (sinônimo de CHAR)
 DATE
 NUMBER [(tamanho [, decimais] )]
 DECIMAL [(tamanho [, decimais] )]
 INTEGER, INT (sinônimos de NUMBER)
 NUMERIC, DEC (sinônimo de DECIAMAL)
 FLOAT [(tamanho)]
 REAL
51
 DOBLE PRECISION
 LONG
 RAW (tamanho), LONG RAW
IV – Criação e uso de tabelas
IV.3 – Alteração de uma tabela
• Para acrescentar uma coluna à tabela DEP:
• Para aumenta o tamanho da coluna NOME_DEP:
52
SQL > desc dep
Name Null? Type
N_DEP NOT NULL NUMBER(2)
NOME_DEP CHAR(14)
LOCAL_DEP CHAR(13)
SQL > alter table dep
 2 add ( diretor number( 4 ) ); tabela a ser modificada
nome da coluna Tipo da coluna
SQL > alter table dep
 2 modify ( nome_dep char( 30 ) );
coluna a ser modificada
novo tipo
SQL > desc dep
Name Null? Type
N_DEP NOT NULL NUMBER(2)
NOME_DEP CHAR(30)
LOCAL_DEP CHAR(13)
DIRETOR NUMBER(4)
IV – Criação e uso de tabelas
• Restrições para a alteração da estrutura de uma tabela:
- Não se pode adicionar colunas não nulas (NOT NULL) 
em tabelas que já possuem linhas.
- Só é possível diminuir o tamanho de uma coluna, ou 
mudar seu tipo, se todos os valores desta coluna forem 
nulos.
- Só é possível alterar o tipo de uma coluna para “NOT 
NULL” se nenhum dos seus valores for nulo.
- Não é possível remover uma coluna de uma tabela com o 
comando ALER TABLE. Para conseguir este efeito deve-
se criar outra tabela sem a coluna a ser removida. Por 
exemplo. Para apagar a coluna DIRETOR:
53
SQL > create table dep_temp as
2 select n_dep, nome_dep, local_dep
3 from dep;
SQL > drop table dep;
SQL > rename dep_temp to dep;
IV – Criação e uso de tabelas
IV.4 – O comando INSERT
• O comando INSERT é usado para inserir linhas com uma tabela.
- Forma 1:
» tabela nome da tabela onde os dados serão inseridos
»col1, col2, ... coln lista de colunas nas quais serão inseridos os 
valores. Será inserido o valor NULL nas colunas não 
especificadas.Se nenhuma coluna for especificada, é 
assumida uma lista com todas as colunas da tabela na 
ordem apresentada pelo comando DESCRIBE.
» val1, val2, ... valn lista de valores a serem inseridos. Os valores 
devem concordar em nº, ordem e tipo com as colunas 
especificadas no comando INSERT
 - Exemplo:
54
INSERT INTO tabela [( cil1, col2, ... coln)]
VALUES ( val1, val2, ... valn)
SQL > Insert into emp
2 (nome_emp, n_emp, cargo, chefe, data_adm, sal, n_dep)
3 values ( ´SAMANTA`, 103, `ANALISTA`, 110, sysdate-30,
4 150000, 20);
ou
SQL > Insert into emp
2 values ( 103, `SAMANTA`, `ANALISTA`, 110, sysdate-30 
 3 150000, NULL, 20); 
IV – Criação e uso de tabelas
- Forma 2:
- Insere todas as linhas retornadas pela consulta executada pelo 
comando SELEC
- As expressões exp1, exp2, expn devem concordar em nº, 
ordem e tipo com as colunas especificadas no comando 
INSERT.
- Exemplo:
55
INSERT INTO tabela ( col, col2, ... coln )
SELECT exp1, exp2, ... expn
FROM ...
SQL > insert into faixa_sal(salmin, salmax, faixa)
2 select min( sal ), max( sal ), substr( cargo, 1, 1 )
3 from emp
4 group by substr( cargo, 1, 1 );
IV – Criação e uso de tabelas
IV.5 – Comando DELETE
• O comando DELETE é usado para remover linhas de uma 
tabela:
 »tabela nome da tabela de onde serão removidas as linhas
 »condição condição que especifica as linhas a serem removidas. 
Opcional.
• Exemplos
 - Para remover o empregado de nº 189: 
- Para remover todos empregados do departamento 20:
56
DELETE FROM tabela WHERE condição
SQL > delete from emp where n_emp = 189;
SQL > delete from emp where n_dep = 20;
- Para remover todos os empregados:
IV – Criação e uso de tabelas
IV.6 – O comando UPDATE
• O comando UPDATE é usado para alterar valores já 
armazenados em tabelas:
ou
 »tabela nome da tabela a ser atualizada
 »col1, col2, ... coln colunas cujos valores serão alterados
 »exp1, exp2, ... expn expressões cujos resultados serão atribuídos às 
colunas col1, ... coln respectivamente
 »consulta consultas que deve retornar o mesmo número 
de colunas especificas na cláusula SET
57
SQL > delete from emp;
UPDATE tabela
 SET col1 = exp1, col2, = exp2, ... coln = expn
WHERE condição
UPDATE tabela
 SET ( col1 [, col2 ...] ) = ( consulta )
WHERE condição
 »condição especifica as linha que serão atualizadas
IV – Criação e uso de tabelas
• Exemplos
- Para colocar 1000 de comissão para o funcionário de número 
208:
- Para dobrar o salário de todos empregados do departamen20 e 
transferi-los para o departamento 30:
- Para atualizar o salário do funcionário 208 como sendo o 
salário de seu chefe:
 
58
SQL > update emp
2 set com = 1000
3 where n_emp = 208;
SQL> update emp
2 set sal = 2 * sal, n_dep = 30
3 where n_dep = 20;
SQL > update emp e 1
2 set sal = ( select sal from emp e2
3 where e2.n:emp = e1.chefe ) where e1.n_emp = 208;
IV – Criação e uso de tabelas
IV.7 – Controle de transações
• Uma transação é uma seqüência “indivisível” de comandos SQL 
que modifica o conteúdo de banco de dados.
• Por exemplo, para transferir uma certa quantia em dinheiro de 
uma conta A para uma conta B, devemos:
- retirar o dinheiro da conta A
- depositar o dinheiro n conta B
• Para que a operação acima seja segura, ou os dois passos são 
executados ou nenhum deles pode ocorrer. 
59
IV – Criação e uso de tabelas
• O início de uma transação é determinado pelo primeiro 
comando de manipulação de dados (INSERT, UPDATE, 
DELETE) desde o fim da última transação ou inicio da seção 
corrente.
• O fim de uma transação é determinada de uma das seguintes 
formas:
- execução do comando COMMIT, todas as modificações 
são efetivadas no banco de dados
- execução do comando ROLLBACK; nenhuma 
modificação é efetivada
- execução de comando de definição de dados (CREATE, 
ALTER, ...); equivale ao comando COMMIT
- término da sessão corrente: equivale ao comando 
COMMIT
60
- quando uma transação é interrompida por fatores externos 
(queda do sistema) ou internos (“deadlock”); equivale ao 
comando ROLLBACK 
IV – Criação e uso de tabelas 
• Exemplo:
 
61
$ sqlplus ora 1/senha 1
SQL*Plus: version 3.0.6.5.1 – Production on Wed Apr 3 08:56 ...
Copyright (c) ORACLE Corporation 1979, 1989. All ...
Connected to: ORACLE RDBMS V6.0.27.9.2, with transaction ...
PL/SQL V1.0.29.1.0 – Beta
SQL > set feedback off
SQL > select n_dep, nome_dep from dep;
N_DEP NOME_DEP
10 ADMINISTRAÇÃO
20 PESQUISA
30 VENDAS
40 PRODUÇÃO
SQL > insert into dep values( 50, `MARKETING`. `SAO PAULO` );
SQL > insert into dep values( 60, `INFORMATICA`, `CAMPINAS`);
SQL > select n_dep, nome_dep from dep;
N_DEP NOME_DEP
10 ADMINISTRAÇÃO
20 PESQUISA
30 VENDAS
40 PRODUÇÃO
50 MARKETING
60 INFORMATICA
SQL > commit;
Commit complete.
inibe algumas mensagens de
aviso
O próprio usuário vê os dados, mas 
eles ainda não foram efetivados
fim da transação, com
efetivação dos dados
Início de uma transação
IV – Criação e usa de tabelas
62
SQL > update dep set nome_dep = `COMERCIAL`
2 where n_dep = 30;
SQL > select nome_dep from dep where n_dep = 30;
NOME_DEP
COMERCIAL
SQL > rollback;
Rollback complete.
SQL > select nome_dep from dep where n_dep = 30;
NOME_DEP
VENDAS
SQL > delete from emp where n_emp = 103;
SQL > insert into emp values
2 ( 103, `SAMANTA`, `DIRETOR`, 175, `17-MAR-85`,
3 200000, NULL, 20);
SQL > commit;
Commit complete.
SQL > update emp set cargo = `DIRETOR`, chefe = 175, sal = 200000
2 where n_emp =103;
SQL > commit;
Commit complete.
SQL > delete from emp
2 where n_dep = 10;
SQL > exit
Início de 
outra
transação
fim da transação; 
as
atualizações foram
Produzem o
Mesmo 
efeito
IV – Criação e usa de tabelas
• Observações
- Uma transação ou é executada totalmente ou não é 
executada; não existe transação parcialmente executada, 
por definição.
- As modificações efetuadas no decorrer de uma transação 
por um usuário só são visíveis a outros usuários após a 
efetivação da transação (COMMIT).
63
inicio da 
ultima
transação
Fim da ultima transação
Com COMMIT implícito
 USUÁRIO 1 USUÁRIO 2 
SQL > select sal from emp SQL > select sal from emp
 2 where n_emp = 189; 2 where n_emp = 189;
 SAL SAL
 330000 330000 
SQL > update emp SQL >
2 set sal = 400000
3 where n_emp = 189;
SQL > select sal from emp SQL > select sal from emp
 2 where n_emp = 189; 2 where n_emp = 189;
 SAL SAL
 400000 330000 
SQL > commit; SQL > 
Commit coplete.
SQL > select sal from emp SQL > select sal from emp
 2 where n_emp = 189; 2 where n_emp = 189;
 SAL SAL
 400000 400000 
 
IV – Criação e usa de tabelas
• O comando SET AUTOCOMMIT ON do SQL*Plus faz com 
que o commit seja executado automaticamente a cada 
comando de manipulação de dados:
64
SQL > set feedback on
SQL > set autocommit on
SQL > delete from dep
2 where n_emp = 10;
Commmit complete.
1 record deleted.
SQL > insert into emp values( 103, `SAMANTA`, `ANALISTA`,
 2 110, `17-MAR-85`, 150000, NULL, 20);
Commit complete.
1 record created.
SQL > set autocommit off
SQL > delete from emp
2 where n_emp = 189;
1 record deleted.
SQL > rollback;
Rollback complete.
IV – Criação e usa de tabelas
• É possível determinar pontos de salvamento intermediáriosem transações:
65
SQL > update conta
2 set saldo = saldo – 10
3 where codigo = 1;
SQL > savepoint ja_tirou;
SQL > update conta
2 set saldo = saldo + 10
3 where codigo = 2;
SQL > rollback to ja_tirou;
determina um ponto de
salvamento
desfaz as modificações feitas após
o ponto de salvamento “ já_tirou” 
IV – Criação e usa de tabelas
• Para garantir que vários comandos SELECT recuperem 
sempre os mesmo dados, independentemente de transações 
concorrentes:
66
SQL > set transction read only;
SQL > select sal
2 from emp
3 where n_emp = 110;
 SAL
 300000 
SQL > 
SQL > select sal
2 from emp
3 where n_emp = 110;
 SAL
 300000 
os resultados serão
sempre iguais
IV – Criação e usa de tabelas
IV.8 – Manipulação de visões
• Uma visão é como uma janela que permite visualizar ao 
modificar seletivamente informações armazenadas em 
tabelas.
• Visões são utilizadas por:
- segurança : pode-se restringir o acesso a informações 
contidas em tabelas
 Tabela Emp
 
 Visão Emp_10
 
67
N_EMP NOME_EMP ...N_DEP
103 SAMANTA ... 20
110 UBIRATAN ...20
175 PAULO ...10
189 RITA ...30
201 EVERALDO ...30
208 SILVIO ...30
230 ANA ...10
276 RENATO ...20
N_EMP NOME_EMP ...N_DEP
175 PAULO ... 10 
230 ANA ...10 
IV – Criação e usa de tabelas
- conveniência: consultas complexas podem ser simplificadas 
com a criação de visões
 Emp
 Dep
 
 Emp_Dep
68
as colunas (nome e definição) 
são
herdadas da tabela origem
SQL > create view emp_10 as
2 select * from emp
3 where n_dep = 10;
 
N_EMP NOME_EMP ...N_DEP
103 SAMANTA ...20
110 UIRATAN ...20
175 PAULO ...10
189 RITA ...30
201 EVERALDO ...30
208 SILVIO ...30
230 ANA ...10
276 RENATO ...20 
N_DEP NOME_DEP
10 ADMINISTRAÇÃO
20 PESQUISA
30 VENDAS
40 PRODUÇÃO
NOME NUM NUM_DEPTO NOME_DEPTO
SAMANTA 103 20 PESQUISA
UBIRATAN 110 20 PESQUISA
PAULO 175 10 ADMINISTRAÇÃO 
RITA 189 30 VENDAS
EVERALDO 201 30 VENDAS
SILVIO 208 30 VENDAS
ANA 230 10 ADMINISTRAÇÃO
RENATO 276 20 PESQUISA
indicações para o
dep. 20 apenas,
por motivos de
clareza
SQL > create view emp_dep ( nome, num, num_epto, nome_depto )
2 as select nome_emp, n_emp, dep.n_dep, nome_dep
3 from emp, dep
4 where emp.n_dep = dep.n_dep; o nome das colunas pode ser modificado
IV – Criação e usa de tabelas
• Uma visão é considerada uma tabela virtual, isto é, funciona 
como uma tabela mas não contem dados. Os dados de uma visão 
são calculados a partir de outras tabelas (ou visões!).
• Para descrever a estrutura de uma visão:
• Para consultar uma visão:
69
SQL > desc emp_dep
Name Null? Type 
NOME CHAR(10)
NUM NOT NULL NUMBER(4)
NUM_DEPTO NOT NULL NUMBER(2)
NOME_DEP CHAR(14)
SQL > select * from emp_dep;NOME NUM NUM_DEPTO NOME_DEPTO
SAMANTA 103 20 PESQUISA
UBIRATAN 110 20 PESQUISA
PAULO 175 10 ADMINISTRAÇÃO
RITA 189 30 VENDAS
EVERALDO 201 30 VENDAS
SILVIO 208 30 VENDAS
ANA 230 10 ADMINISTRAÇÃO
RENATO 276 20 PESQUISA
IV – Criação e usa de tabelas
• Uma visão pode ser criada em temo de execução:
70
SQL > select ep.nome_emp, dp.nome_dep
 1 from emp ep, ( select de.n_dep, de.nome_dep
2 from dep de ) dp
3 where ep.n_dep = dp.n_dep
nome_emp nome_dep
SAMANTA PESQUISA
UBIRATAN PESQUISA
PAULO ADMINISTRAÇÃO
RITA VENDAS
...
IV – Criação e usa de tabelas
• Somente é possível atualizar (INSERT, DELERE, UPDATE) 
informações em visões que:
- estão baseadas em uma única tabela
- fazem referência a todas as colunas não nulas (NOT 
NULL) da tabela
- recuperam todas as colunas diretamente, sem nenhuma 
expressão
• A visão EMP_10 pode ser atualizada, ao contrário da visão 
EMP_DEP, que é baseada em duas tabelas
• Para visualizar a consulta que define uma visão: 
71
SQL > set long 500
SQL > select tex from eser_views where view_name = `EMP_DEP`;
TEXT
select nome_emp, n_emp, dep.n_dep, nome_dep
from emp, dep
where emp.n_dep = dep.n_dep
faz o SQL*Plus mostrar até 500 caracteres de
colunas do tipo LONG
• Para remover uma visão:
IV – Criação e usa de tabelas
IV.9 – Criação de seqüências 
• É possível definir seqüências numéricas a serem geradas 
automaticamente pelo OROCLE:
• Para criar uma seqüência chamada “seq_emp” :
 
72
SQL > drop view emp_dep;
CREATE SEQUENCE nome_seq
[INCREMENT BY {1 / num}]
[START WITH num]
[MAXVALUE num / NOMAXVALUE]
[MINVALUE num / NOMINVALUE]
[CYCLE / NOCYCLE]
[CACHE {20 / num} / NOCACHE]
[ORDER / NOORDER]
SQL > CREATE SEQUENCE seq_emp
 2 START WITH 1 NOMAXVALUE ORDER;
• Para usá-la:
IV – Criação e usa de tabelas
IV - Exercícios
1. Crie uma tabela EMPREG que contém os dados das colunas 
N_EMP, NOME_EMP E SAL da tabela EMP. Dica: use a forma 
2 do comando CREATE TABLE (página 49).
2. mude o nome da tabela EMPREG para TEMP e depois a 
remova.
3. altere a estrutura da tabela ALUNO criada no exercício a da 
parte II, de forma a:
a) aumenta o tamanho da coluna CIDADE para 35 caracteres
b) acrescentar o campo SAL_DESEJADO, com tipo 
numérico de 9 dígitos na parte inteira e 2 casas decimais. 
Dica: lembre-se que o tamanho de um numero é dado pela 
73
SQL > INSERT INTO( nome_emp, n_emp, ...)
 2 VALUES ( `PROXIMO CLIENTE`, seq_emp.NEXTVAL, ...);
soma dos dígitos na parte inteira com os dígitos na parte 
decimal do numero.
4. Insira os seguintes dados de um(a) amigo(a) seu(sua) na tabela 
ALUNO: NOME, ANIVERSARIO e SAL_DESEJADO
5. Atualize o seu salário desejado e o endereço completo da pessoa 
cadastrada no exercício anterior.
6. Remova o atributo SAL_DESEJADO da tabela ALUNO. Dica: 
veja a página 54.
7. Remova os dados da pessoa cadastrada no exercício 4 e efetive 
sua transação.
IV – Criação e usa de tabelas
8. Crie uma visão chamada DEP_SAL baseada nas tabelas EMP e 
DEP, contendo as seguintes informações:
- Nome do departamento (NOME_DEP)
- Numero de funcionários (NUM_FUNC)
- Salário mínimo (SAL_MIN)
- salário médio (SAL_MED)
- salário Maximo (SAL_MAX)
dica: primeiro faça uma consulta que retorna os dados acima, 
para depois criar uma visão baseada nessa consulta
9. Verifique a estrutura da visão DEP_SAL com o comando 
DESCRIBE e depois consulte todos os seus dados.
10.Atualize o salário de SAMANTA para 300000 e consulte a visão 
DEP_SAL novamente.
74
11.Desfaça a última atualização realizada. 
IV – Criação e usa de tabelas
V – Geração de Relatórios
75
 
V – Conteúdo
1. Formatação de colunas
2. Formatação de títulos
3. BREAK e COMPUTE
4. Parâmetros de controle
76
5. Utilização de variáveis
6. Relatórios avançados
 
V – Geração de Relatórios
V.1 – Formatação de colunas
• Os comandos de formatação de colunas (formatação em geral) 
do SQL*Plus são acumulativos
• Sintaxe do comando COLUMN:
77
COLUMN coluna
ALIAS sinônimo 
CLEAR
DEFAULT
FOLD_A[FTER]
FOLD_B[EFORE]
FORMAT formato
HEADING texto
JUSTIFY
LEFT
CENTER
RIGHT
LIKE coluna
NEWLINE
NEW_VALUE var
OLD_VALUE var
NULL nulo
NOPRINT
PRINT
ON
OFF
WRAPPED
WORD_WRAPPERD
TRUNCATED
V – Geração de Relatórios
»coluna nome de uma coluna ou expressão especificada no 
comando SELECT, tal qual aparece no cabeçalho do 
resultado do comando.
»sinônimo nome de uma outra coluna da qual serão copiadas asespecificações de formatação
»formato formato de apresentação do conteúdo de uma coluna
»texto texto a ser apresentado como cabeçalho de uma 
coluna
»nulo texto a ser exibido quando o conteúdo de uma coluna 
for nulo
»var nome de uma variável (será explicado adiante)
78
V – Geração de Relatórios
• Exemplos de formatos disponíveis para o comando COLUMN:
 
79
Formato Valor Resultado Observação
A20 Empregado Empregado
A5 Empregado Empre corta o resto
999.99 56.478 56.48 arredonda
999V99 56.478 5648 alinha pelo “V”
9,999 8410 8,410
99999 607 607
09999 607 00607
999 -5609 -5609
9999MI -5609 5609-
9999PR -5609 <5609>
B999 564
B999 0 brancos imprime brancos quando zero
99.99 124.98 # #.# # estouro
DATE 2441453 12/23/80 valor = data Juliana
EDATE 2441453 23/12/80
• O comando COLUMN, seguido de um nome de coluna (ou 
sozinho), exibe as definições daquela coluna (ou de todas as 
coluna)
• O comando CLEAR COLUMN elimina todas as definições de 
uma coluna
V – Geração de Relatórios
• Exemplos
80
SQL > column 12*sal format 999999999.99
SQL > column nome_emp format a20 heading `Empreg.`word_wrap
SQL > column nome_dep like nome_emp heading `Departamento`
SQL > column com heading `comissão | adicional`format 9999-99
SQL > column 12*sal g~heading `salario| anual`
SQl > column 12*sal
column 12*sal ON
heading `salario | anual`headsep `|`
format 999999999.99
SQL > select nome_dep, nome_emp, 12*sal,com
2 from emp, dep
3 where emp.n_dep = dep.n_dep
 salário comissão
Departamento Empreg. anual adicional
ADMINISTRAÇÃO PAULO 6000000.00
ADMINISTRAÇÃO ANA 1440000.00
PESQUISA SAMANTA 1800000.00
PESQUISA UBIRATAN 3600000.00
PESQUISA RENATO 1200000.00
VENDAS RITA 3960000.00 5000.00
VENDAS SILVIO 1800000.00 9000.00
VENDAS EVERALDO 2400000.00 3000.00
definições acumulativas
Pula uma linha
exibe as definições 
associadas
à coluna “12*sal”
V – Geração de Relatórios
V.2 – Formatação de títulos
• É possível definir o cabeçalho (TTITLE) e o rodapé (BTITLE) 
das páginas de uma consulta:
- TTITLE (BTITLE) “texto”
o define o texto como cabeçalho (rodapé), podendo 
conter “|” para pular linhas
o este formato acrescenta data e numeração de página 
automaticamente
o só existe para manter compatibilidade com versões 
antigas
- TTITLE (BTITLE)
81
o mostra a definições corrente
- TTITLE (BTITLE) OFF
o desabilita a exibição do cabeçalho (rodapé)
- TTIBLE (BTITLE) ON
o habilita a exibição do cabeçalho (rodapé)
V – Geração de Relatórios
- TTITLE (BTITLE) param1 param2 ...
o paramN :
» “texto” texto livre que aparecerá no cabeçalho
»&variável imprime o valor de uma variável no cabeçalho
»COLn faz o próximo caracter na coluna n
»SKIPn pula n linhas
»LEFT alinha o texto seguinte à esquerda
»CENTER centraliza o texto seguinte
»RIGHT alinha à direita o texto seguinte
»SQL.PNO imprime o número da página corrente
»SQL.LNO imprime o número da linha corrente 
82
V – Geração de Relatórios
• Exemplo:
83
SQL > ttitle center `25 de dezemvro de 1990`skip col 6 –
> `Relatório de empregados – Departamento de Pessoal`
SQL > select n_emp, nome_emp, sal
2 from emp
3 where n_dep = 30;
25 de dezembro de 1990
Relatório de Empregados – Departamento de Pessoal
N_EMP NOME_EMP SAL
189 RITA 330000
201 EVERALDO 200000
208 SILVIO 150000
SQL > ttitle
Ttitle on and is the follwing 95 characters:
Center `25 de dezembro de 1990` skip col 6 –
`Relatório de Empregados – Departamento de Pessoal`
V – Geração de Relatórios
V.3 – BREAK e COMPUTE
• O comando BREAK permite organizar grupos de linhas de um 
relatório
• É possível definir ações a serem executadas no fim de cada 
grupo controlado pelo comando BREAK:
»controleN grupo ação repetição
»grupo nome de uma coluna, expressão, ROW ou 
REPORT
»ação SKIP n, onde n é um número de linhas a serem 
puladas, SKIP PAGE ou PAGE; este parâmetro 
pode ser omitido 
84
BREAK ON controle1 ON controle2 ... ON controleN
»repetição NODUPLICATES (padrão) ou DUPLICATES: 
controla se os valores de grupo duplicados são 
exibidos ou não
V – Geração de Relatórios
• Exemplo:
85
SQL > break on nome_dep page on cargo skip 1
SQL > select nome_dep, cargo, nome_emp
2 from dep, emp
3 where dep.n_dep = emp.n_dep
4 order by nome_dep, cargo;
é importante ordenar o resultado 
de
acordo com os grupos formados!NOME_DEP CARGO NOME_EMP
ADMINISTRAÇÃO PRESIDENTE PAULO
SECRETARIA ANA
NOME_DEP CARGO NOME_EMP
PESQUISA ANALISTA SAMANTA
REANATO
DIRETOR UBIRATAN
NOME_DEP CARGO NOME_EMP
VENDAS DIRETOR RITA
VENDEDOR SILVIO
EVERALDO
mudança
de página
V – Geração de Relatórios
• Para mostrar as definições correntes:
• Para eliminar as definições de grupo:
86
SQL > brek
break on nome_dep page 1 nodup
 on cargo skip 1 nodup
SQL > clear break
• O comando BREAK não é acumulativo côo o comando 
COLUMN
V – Geração de Relatórios
• O comando COMPUTE permite realizar cálculos sobre os 
valores de um grupo organizado pelo comando BREAK:
87
SQL > break on n_dep on nome_dep skip 1
SQL > compute sum of sal on nome_dep
SQL > select dep.n_dep, nome_dep, nome_emp, sal
2 from dep, emp
3 where dep.n_dep = emp.n_dep
4 oeder by dep.n_dep;
N_DEP NOME_DEP NOME_EP SAL
10 ADMINISTRAÇÃO PAULO 500000.00
 ANA 120000.00
****************
sum 620000.00
20 PESQUISA SAMANTA 150000.00
RENATO 100000.00
UBIRATAN 300000.00
***************
sum 550000.00
30 VENDAS RITA 330000.00
EVERALDO 200000.00
SILVIO 150000.00
***************
sum 680000.00
V – Geração de Relatórios
• Sintaxe do comando:
»opearaçãoN uma das seguintes operações: COUNT, SUM, AVG, 
MIN, MAX,STD, NUM, VAR
»operaçãoN coluna, expressão entre aspas ou sinônimo, sobre o 
qual as operações serão efetuadas. Devem ser 
especificados no comando SELECT
88
COMPUTE operação1 ... OF operando1 ... ON
 evento_break
»evento_break especifica o evento que o SQL*Plus irá utilizar como 
“quebra” . Pode ser o nome de uma coluna, expressão 
entre aspas, sinônimo, ROW ou REPORT 
V – Geração de Relatórios
V.4 – Parâmetros de controle
• O SQL*Plus possui uma série de parâmetros para controle de 
formatação.
• O comando SET é usado para alterar o valor de um parâmetro.
• O comando SHOW é usado para visualizar o valor atual de um 
parâmetro. 
89
V – Geração de Relatórios
• Os comandos SET e SHOW podem ser aplicados aos seguintes 
parâmetros:
ARRAYSIZE n | 20 número de linhas recuperadas 
simultaneamente do banco de dados
AUTOCOMMIT ON | OFF faz com que cada alteração feita na base 
de dados seja efetivada imediatamente
BUFFER buffer | SQL torna buffer o buffer de edição corrente
CMDSEP c | `;` | ON | OFF caractere de separação entre vários 
comandos SQL digitados em uma 
mesma linha
90
SQL > set pagesize 25
SQL > show pagesize
pagesize 25 
COMPATIBILITY V5 | V6 força compatibilidade com o RDMS 
ORACLE V5 ou V6 
CONCAT c | `.` | ON | OFF caractere de concatenação para 
utilização de variáveis
COPYCOMMIT n | 0 tamanho do lote de registros que deve 
ser processado pelo comando COPY 
antes da execução de um COMMIT
CRT crt define o crt padrão para o comando 
RUNFORM
DCLSEP c | `! ` caractere de separação de comandos do 
sistema operacional
DEFINE c | `&` | ON | OFF caractere que precede a utilização de 
variáveis
DOCUMENT ON | OFF interpreta ou não o texto dentro do 
comando DOCUMENT
ECHO ON | OFF exibe ou não os comandos SQL*Plus 
quando são executadosde um arquivo 
de comandos 
EMBEDDED ON | OFF força ou não que todo relatório comece 
em uma nova página
ESCAPE c | ON | OFF caractere de “escape” para permitir a 
inserção de caracteres especiais
V – Geração de Relatórios
FEEDBACK n | 6 | ON | OFF número mínimo de linhas que precisam 
ser selecionadas para haver impressão 
do total de linhas
HEADING ON | OFF permite ou não a impressão de 
cabeçalho de colunas
HEADSEP c | `I`| ON | OFF caractere para pular linha em cabeçalhos 
(formato antigo)
LINESIZE n | 80 número de caractere por linha
LONG n | 80 número máximo de caracteres 
visualizados para valores do tipo LONG
91
MAXDATE n tamanho máximo de uma linha que o 
SQL*Plus pode processar
NEWPGE n | 1 número de linhas brancas a serem 
impressas a cada página nova (0 indica a 
impressão de um “pulo de página”)
NULL texto texto a ser exibido quando um valor é 
nulo
NUMFORMAT formato formato padrão para exibição de número 
(formato numérico do comando 
COLUMN)
NUMWIDTH n | 10 largura padrão para exibição de números
PAGESIZE n | 14 número de linhas por página
PAUSE texto | ON | OFF antes de começar cada página, exibe o 
texto e espera (ou não) que o usuário 
tecle ENTER
SCAN ON | OFF liga ou desliga p procedimento de 
substituição de variáveis
SHOWMODE ON | OFF exibe ou não os valores antigo e atual de 
parâmetros modificados com o comando 
SET
SPACE n | 1 número de espaço entre colunas
SQLCONTINUE c | “>” caractere exibido quando da continuação 
de um comando SQL*Plus 
V – Geração de Relatórios
SQLNUMBER ON | OFF liga ou desliga a numeração de linhas 
quando da continuação de comandos 
SQL
SQLPREFIX c | `#` caractere de prefixo para a execução de 
comandos SQL durante a edição de um 
outro comando
SQLPROMPT texto | “SQL>” texto exibido no início de cada linha de 
edição
SQLTERMINATOR c |`;`| ON | OFF caractere que indica o término de um 
comando SQL
92
SUFFIX texto | “SQL” texto que será usado como sufixo para 
execução e edição de arquivos
TAB ON | OFF permite ou não a utilização de caracteres 
TAB na impressão de brancos
TERMOUT ON | OFF permite ou não a exibição de comando 
sendo executados
TIME ON | OFF exibe ou não a hora antes de cada linha 
de edição
TIMING ON | OFF exibe ou não estatísticas de tempo para 
cada comando executado
TRIMOUT ON | OFF inibe ou não impressão de brancos no 
final de cada linha
TRUNCATE ON | OFF permite ou não o “corte” de uma linha 
se ela for muito grande
UNDERLINE c|`-`| ON | OFF caractere que será utilizado para 
sublinhas o cabeçalho de colunas
VERIFY ON | OFF exibe ou não comandos SQL antes e 
depois da substituição de variáveis
WRAP ON | OFF é o inverso do comando TRUNCATE
• O comando SHOW ALL mostra o valor de todos ps parâmetros 
V – Geração de Relatórios
V.5 – Utilização de variáveis
• O SQL*Plus permite a definição e uso de variáveis genéricas:
 
93
SQL > define v_dep = 20
SQL > define v_nomerel = “relatorio de utilidades”
• Para recuperar o valor de uma variável em uma comando SQL, 
deve-se escrever o nome da variável precedido de `&`:
V – Geração de Relatórios
• O SQL*Plus requisita o valor de uma variável se esta não tiver 
sido previamente definida:
94
SQL > define v_dep = 20
SQL > select * from dep
 2 where n_dep = &v_dep
SQL > select * from emp
2 where sal > &limite;
Enter value for limite: 200000
old 2: where sal > &limite
new 2: where sal > 200000
N_EMP NOME_EMP
110 UBIRATAN
175 PAULO
189 RITA
• após a utilização de uma variável não previamente definida, 
seu valor (fornecido pelo usuário) é descartado
V – Geração de Relatórios
• Para manter o valor fornecido pelo usuário para futuras 
utilizações, deve-se substituir o `&`por `&&`:
95
SQL > select nome_emp, sal
2 from emp
3 where n_dep = &&v_dep
Enter value for v_dep: 30
old 3: where n_dep = &&v_dep
new 3: where n_dep = 30
NOME_EMP SAL
RITA 330000
EVERALDO 200000
SILVIO 150000
SQL > run
1 select nome_emp, sal
2 from emp
3 where n_dep = &&v_dep
old 3: where n_dep = &&v_dep
new 3: where n_dep = 30
NOME_EMP SAL
RITA 330000
EVERALDO 200000
SILVIO 150000
interação para ler 
o
valor da variável
Não há interação: a 
variável
Está permanentemente
V – Geração de Relatórios
• O comando ACCEPT possibilita iniciar variáveis valores 
fornecidos pelo usuário:
96
SQL > accept v_dep NUMBER PROMPT `Departamento: `
Departamento: 30
SQL > select nome_emp, sal
2 from emp
3 where n_dep = &v_dep;
oldo 3: where n_emp = &v_dep
new 3: where n_emp = 30
NOME_EMP SAL
RITA 330000
EVERALDO 200000
SILVIO 150000
especifica o tipo da variável; 
podendo
ser NUMBER ou CHAR
• O comando PROMPT permite exibir informações na tela:
V – Geração de Relatórios
• Pode-se também especificar parãmentros para a ativação de um 
arquivo de comandos SQL*Plus através do comando START:
97
SQL > prompt Relatorio de Empregados
Relatórios de Empregados
SQL >
SQL > start listaemp 10 100000
nome do 
arquivo
de comandos
Parâmetros posicionais
• Onde o arquivo LISTAEMP.SQL contém os seguintes 
comandos:
V – Geração de Relatórios
V.6 – Relatórios avançados
• Os relatórios produzidos até aqui foram sempre apresentados 
na tela; é possível imprimi-los e armazena-los em arquivo do 
sistema operacional:
98
rem O primeiro parametro determina o departamento onde os
rem empregados selecionados trabalham
rem O segundo parametro determina o menor salário que os 
rem empregados selecionados recebem. 
select n_emp, nome_emp
from emp
where n_emp = & 1 and
sal > = &2;
linhas de comando
primeiro e 
segundo
parâmetro 
inicia armazenamento do 
resultado
no arquivo “relat1.lst”
V – Geração de Relatórios
• Se o comando SPOOL OUT for substituído por SPOOL OFF, o 
arquivo de saída não será impresso.
• Para evitar que os próprios comandos e mensagens do 
SQL*Plus sejam enviadas para o arquivo juntamente com o 
relatório.
99
SQL > apool relat1
SQL > start listaemp 10 100000
SQL > rem O primeiro parametro determina o departamento onde os
SQL > rem empregados selecionados trabalham
SQL > rem O segundo parâmetro determina o salário minino que os
SQL > rem empregados selecionados recebem.
SQL > select n_emp, nome_emp
2 from emp
3 where n_dep = &1 and
4 sal > = &2;
old 3: where n_dep = &1 and
new 3: where n_deo = 10 and
old 4: sal > = &2;
new 4: sal > = 100000;
 N_EMP NOME_EMP
175 PAULO
230 ANA
2 records selected.
SQL > spool out
termina o armazenamento e
imprime o resultado
SQL > set echo off
SQL > set verify off
SQL > set feedback off
SQL > spool relat1
SQL > start listaemp 10 10000
N_EMP NOME_EMP
175 PAULO
230 ANA
SQL > spool out
V – Geração de Relatórios
• No caso do comando COMPUTE, para eliminar os textos “sum” 
e “******” que aparecem como indicadores do cálculo 
executado:
100
SQL > collumn aux_dep noprint
SQL > column sal format $999,999,999.99
SQL > break on n_dep on nome_dep on aux_dep skip1
SQL > compute sum of sal on aux_dep
SQL > select dep.n_dep aux_dep, dep.n_dep, nome_dep,
2 nome_emp, sal
3 from dep, emp
 4 where dep.n_dep = emp.n_dep
 5 order by dep.n_dep; 
V – Geração de Relatórios
• As opções NEW_VALUE E OLD_VALUE do comando 
COLUMN podem ser utilizadas pararecuperar dados em uma 
consulta e usá-los em uma outra consulta:
 
101
N_EMP NOME_DEP NOME_EMP SAL
10 ADMINISTRAÇÃP PAULO $500,000.00
ANA $120,000.00
$620,000.00
20 PESQUISA SAMANTA $150,000.00
RENATO $100,000.00
UBIRATAN $300,000.00
$550,000.00
30 VENDAS RITA $330,000.00
EVERALDO $200,000.00
SILVIO $150,000.00
$680,000.00
SQL > set echo off
SQL > set verify off
SQL > set feedback off
SQL > column PROCENT format 999.99
SQL > column sum(sal) new_value soma
SQL > rem calcula o valor de “soma”
SQL > select sum(sal) from emp;
SQL > rem calcula porcentagens dos salarios utilizando “soma”
SQL > select nome_emp, sal, 100 * sal / &soma “PORCENT”
2 from emp
3 order by 3 desc; Indica a terceira expressão do
SELECT
 SUM
1850000
NOME_EMP SAL PORCENT
PAULO 500000 27.03
RITA 330000 17.84
UBIRATAN 300000 16.22
EVERALDO 200000 10.81
SAMANTA 150000 8.11
SILVIO 150000 8.11
ANA 120000 6.49
RENATO 100000 5.41
V – Geração de Relatórios
• vamos agora considerar o seguinte relatório:
102
23/11/90 Relatorio de Empregados Pág. 3
Departamento: VENDAS
 Num Nome Empregado Salário Comissão Porc.
 208 SILVIO $150,000.00 $9,000.00 5.66
 189 RITA $330,000.00 $5,000.00 1.49
 201 EVERALDO $200,000.00 $5,000.00 1.48
Departamento: VENDAS
data de hoje
número da página
departamento dos empregados
apresentados nesta página
V – Geração de Relatórios
• Para produzir o relatório da página anterior, deve-se acompanhar 
os seguintes passos:
1. Definir a consulta a ser realizada:
103
select nome_dep, n_emp, nome_emp, sal, nvl(com, 0) comissão,
(nvl( com, 0 ) / ( sal + nvl( com, 0) ) * 100) procnt_com,
to_char( sysdate, `DD/MM/YY` ) data
from dep, emp
where dep.n_dep = emp.n_dep
order bu nome_dep, 6 desc; 
2. Definir os formatos das colunas
3. Definir o agrupamento por departamento:
4. Definir duas variáveis que armazenarão o valor novo e o 
antigo da coluna NOME_DEP durante a quebra do grupo:
V – Geração de Relatórios
5. Definir uma variáve para armazenar a data sendo recuperado 
pela consulta:
 
6. Esconde as colunas NOME_DEP e DATA:
104
column n_emp format 9999 heading `Num`
column nome_emp format A 15 heading `Nome Empregado`
column sal format $999,999,999.99 heading `Salario`
column comissao format $999,999,999.99 heding `Comissao`
column porcent_com format 99.99 heading `Porc.`
column data format A8
break on nome_dep skip page
column nome_dep old_value vardep_ant new_value vardep_novo
column data new_value varhoje 
column nome_dep noprint
column data noprint
7. Definir os títulos:
8. Definir o tamanho da página:
9. Em caso de erro, interromper o processamento do SQL*Plus 
retornando o código do erro:
V – Geração de Relatórios
• Os comandos necessários à geração do relatório sendo 
especificado poderiam (ou deveriam) estar armazenadas em um 
arquivo do sistema operacional, RELATORIO.SQL:
105
ttitle left varhoje center `Relatorio de Empregados` -
right `Pag. `sql.pno skip 1 –
left `Departamento: `vardep_novo skip 2
btitle left `Departamento: `vardep_ant 
set pagasize 20
set linesize 75
set newpage 0
Wherenever sqlerror exit SQL.SQLCODE;
set echo off
set pagasize 20
set linesize 75
set newpage 0
whenever sqlerror exit sql.sqlcode;
column n_emp format A 15 heading `Nome empregado`
column sal format $999,999,999.99 heading `Salario`
column comissao format $999,999,999.99 heading `Comissao`
column porcent_com format 9.99 heading `Porc.`
column data format A8
break on nome_dep skip page
column nome_dep old_value vardep_ant new_value vardep_novo
column data new_value varhoje noprint
column nome_dep noprint
ttitle left varhoje center `Relatorio de Empregados` -
righe `Pag.`sql.pno skip 1 left `Departamento: `vardep_novo skip 2 
btitle left `Departamento: `vardep_ant
spool relatorio
select nome_dep, n_emp, sal, nvl(com,0) comissão,
 (nvl(com, 0) / (sal + nvl(com,0) ) * 100) porcent_com,
 to_char( sysdate, `DD/MM/YY` ) data
from dep, emp where dep.n_dep = emp.n_dep
order by nome_dep, 6 desc;
spool out
V – Geração de Relatórios
• Dessa forma, o comando:
106
$ sqlplus –s ora 1/senha1 @relatorio
- teria como efeito gerar o relatório na impressora do 
sistema
V – Geração de Relatórios
V - Exercícios
1. Recupere o comando contido no arquivo “ex31.sql” e formate 
seu resultado da seguinte forma:
a) mude o cabeçalho da coluna que indica o salário por hora 
para “Sal/hora”
107
b) coloque um titulo centralizado indicando “Salário por hora 
dos Empregados”
c) mude o formato da coluna “Sal/hora” para um número 
com quatro casas inteiras e duas decimais.
d) Alinhe o cabeçalho da coluna CARGO pela direita
e) Verifique as definições de coluna existentes 
V – Geração de Relatórios
2. Faça um arquivo de comando SQL*Plus chamado “ex52.sql” de 
forma que sua execução exiba o seguinte resultado:
108
Relatorio de Empregados
Departamento Nome Cargo Salário
ADMINISTRAÇÃO PAULO PRESIDENTE 500000
ANA SECRETARIA 120000
***************
avg 310000
sum 620000
PESQUISA SAMANTA ANALISTA 150000
UBIRATAN DIRETOR 300000
RENATO ANALISTA 100000
**************
avg 183333.333
sum 550000
VENDAS RITA DIRETOR 330000
SILVIO VENDEDOR 150000
EVERALDO VENDEDOR 200000
**************
avg 226666.667
sum 680000
Dica: comece fazendo a consulta; depois, acrescenta as definições da 
coluna, comandos BREAK e COMPUTE; finalmente, ajuste o 
tamanho da página e o tamanho da linha com o co,mando SET.
V – Geração de Relatórios
3. Altere o arquivo “ex52.sql” de forma que:
a) O menor salário a ser considerado na consulta seja 
passado como parâmetro (veja página 99)
b) O maior salário a ser considerado na consulta seja entrado 
pelo usuário através de um comando ACCEPT
109
c) Somente o resultado da consulta seja armazenado no 
arquivo “ex.53.lst”
d) Desapareçam os textos “sum”, “avg” e *********” 
exibidos pelo SQL`Plus (veja página 102)
V – Geração de Relatórios
110
VI – Expressões e funções
111
VI - Conteúdo
1. Operadores
2. Funções numéricas
3. Funções de caracteres
4. Funções de grupo
5. Conversão entre tipos
6. Funções de datas
7. Outras funções
VI – Expressões e funções
112
VI.1 - Operadores
• Operadores de valor:
+ , - indicador de sinal WHERE X = -1
* , / multiplicação e divisão SELECT 2 * X...
+ , - soma e subtração SELECT X + Y...
| | concatena “strings” SELECT `EMPREGADO`| | 
NOME_EMP
• Operadores lógicos
= teste igualdade WHERE SAL = 100000
! =, < >, ^ = testa desigualdade WHERE SAL ! = 100000
>, > = testa maior (ou igual) WHERE SAL > 100000
<, < = testa menor (ou igual) WHERE SAL < = 100000
113
VI – Expressões e funções
[NOT] IN teste de inclusão ou CARGO IN (`ANALISTA`, VENDEDOR`)
exclusão um conjunto
[NOT] teste de intervalo SAL BETWEEN 10000 AND 200000
BETWEEN …
AND
[ NOT ] LIKE testa casamento de WHERE NOME_EMP LIKE `_X%`
padrão
IS [ NOT ] testa valor nulo WHERE COM IS NULL
NULL
NOT inverte resusltado WHERE NOR ( SAL = 100000 )
lógico de expressões
AND operação lógica e WHERE SAL < 100000 AND CARGO =
`ANALISTA`
OR operação lógica ou WHERE COM IS NULL OR SAL > 100000
ANY deve casar com WHERE SAL = ANY (SELECT SAL
qualquer dos valores FROM EMP WHERE N_DEP = 30)
retornados por uma
lista ou consulta
ALL deve casar com todos WHERE (SAL,COM) > = ALL (
os valores retornados (150000. 30000), (2000000, 0) )
por uma lista ou
consulta
114
VI – Expressões e funções
VI.2 – Funções numéricas
• Em geral, funções podem ser usadas em qualquer lugar onde 
variáveis ou expressões domesmo tipo são usadas.
• Legenda para as próximas tabelas:
- T : tipo retornado pela função:
o `C`indica caractere
o `D`indica data
o `N`indica número
 
o `R`indica Rowld
o `W`indica “raw”
 - Argumentos para funções:
o `*` : tipo depende do contexto da função
o m, n : expressões numéricas
o c, c1, c2, conj_c, de_c, para_c: expressões que retornam 
cadeias de caracteres
o d, d1: expressões que retorna datas
o expr, caso, ret, ret_outro: expressões de qualquer tipo 
115
VI – Expressões e funções
• Funções numéricas:
T função Descrição/Exemplo 
N ABS(n) valor absoluto de n
ABS(-5) = 5
N CEIL(n) arredonda para cima
CEIL(5.1) = 6
CEIL(5) = 5
N FOOR(n) arredonda para baixo
CEIL(5.1) = 5
CEIL(5) = 5
N MOD(m, n) resto da divisão de m por n
MOD(5,2) = 1
N POWER(m,n) m elevado a n; n deve ser inteiro 
POWER(2,3) = 8
N ROUND(m,[n]) arredonda m para n casas decimais
ROUND(2.3456) = 2.35
ROUND(1989, -3) = 2000
N SIGN(n) sinal de n
SIGN(-2) = -1
SIGN(0) = 0
SIGN(2) = 1
N SQRT(n) raiz quadrada de n
SQRT(4) = 2
SQRT(-4) = NULL
N TRUNC(n,[m]) trunca n para m casas decimais
TRUNC(2.3456,2) = 2.34
TRUNC(1989, -3) = 1000
 
116
VI – Expressões e funções
VI.3 – Funções de caracteres
T NOME Descrição/Exemplo
N ASCII(c) código ASCII do primeiro caractere de 
c
ASCII( `Água`) = 65
C CHR(n) caractere de código ASCII igual a n
CHR(65) = `A`
C INITCAP(c) retorna c com o primeiro caractere de 
cada palavra em maiúscula 
INITCAP( `SAO PAULO`) = `Sao Paulo`
N INSTR(c1, c2[,m[, n]]) a posição da n-ésima ocorrência de c2
em c1, iniciando a busca na posição m.
O valor 1 é assumido para m e n em 
caso de omissão 
INSTR( `SAO PAULO`, `A`, 1, 2) = 6
N LENGTH(c) número de caracteres em c
LENGTH( `SAO PAULO`) = 9
C LOWER(c) converte todos os caracteres de c para 
minúsculas 
LOWER( `SAO PAULO` ) = `são paulo` 
C LPAD(c1, n[, c2])concatena c2 à esquerda de c1, o suficiente
para que c1 atinja o tamanho n.
O caractere ``é assumido caso c2 seja
omitido
LPAD( `SP`, 5, `-`) = `-SP`
117
VI – Expressões e funções
C LTRIM(c, conj_c) remove todos os caracteres de c até o 
primeiro caractere que não estiver presente 
em conj_c
LTRIM(`AGUA`, `AG`) = `UA`
N NLSSORT(c) valor de c na “linguagem nacional” 
(National Language).
NLSSORT(`Q`) = 5100
C REPLACE(c, de_c troca todas as ocorrências de de_c em c
para_c]) para c 
R E P L A C E( `X U XU`, `X`, `C H`) =
`CHUCHU`
C RPAD concatena c2 à direita de c1, o suficiente
para que c1 atinja o tamanho n. 
O caractere ``é assumido caso c2 seja 
omitido.
RPAD(`SP`, 5, `-`) = `SP-`
C RTRIM(c, conj_c) remove todos os caracteres de c, a partir do 
último, até encontrar o primeiro caractere 
que não estiver presente em conj_c
RTRIM(`AGUA`, `UA`) = `AG` 
C SOUNDEX(c) uma cadeia de caracteres que representa o 
som das palavras em c
SOUNDEX( `SAO PAULO`) = `S140`
C SUBSTR(c, m[, n]) a subseqüência de c com início na posição 
m com n caracteres. Caso n seja omitido, 
retorna da posição m até o fim
SUBSTR(`São Paulo`, 5 ) = `Paulo` 
118
VI – Expressões e funções
C TRANSLATE(c, traduz c e substitui todos caracteres de c 
de_c, para_c) existentes em de_c para o 
correspondente em para_c
TRANSLATE( `AGUA`, `AEIO`, `12345`) 
= `1G51`
C UPPER(c) converte todos os caracteres de c_para 
maiúsculas
UPPER( `Sao Paulo`) = `SAO PAULO`
C USERENV(c) c pode assumir os seguintes valores:
`ENTRYID` = novo identificador para o 
propósito de auditoria
`SESSIONID`= identificador da sessão de 
auditoria
`TERMINAL` = identificador do terminal
`LANGUAGE`= língua em uso
 
119
VI – Expressões e funções
VI.4 – Funções de grupo
T Nome Descrição
N AVG([distinct | all] n) media de n; ignora valores nulos
N COUNT([distinct | all] número de linhas para as quais 
expr | *) expr não é nulo. * faz contar todas as 
linhas selecionadas
N MAX([distinct | all] expr) maior valor de expr
N MIN([distinct | all] expr) menor valor de expr
N STDDEV([distinct | all] n) desvio padrão de n; ignora valores 
nulos
N SUM([distinct | all] n) soma dos valores de n
N VARIANCE([distinct|all] n) variância de n; ignora valores nulos
120
VI – Expressões e funções
VI.5 – Conversão entre tipos 
T Nome Descrição
C CHARTOROWID(char) converte char para um “rowid”
W HEXTORAW(char) converte char contendo dígitos 
hexadecimais para um valor binário, 
conveniente para ser inserido em uma 
coluna do tipo RAW
C RAWTOHEX(raw) converte raw para a cadeia e 
caracteres contendo números 
hexadecimais
C ROWIDTOCHAR(rowid) converte um rowid para uma cadeia 
de 18 caracteres
C TO_CHAR(n[,fmt]) ou converte o número n ou a data d para
 TO_CHAR(d[,fmt]) uma cadeia de caracteres, de acordo 
com o formato fmt
D TO_DATE(char[,fmt]) converte char para uma data, de 
acordo com o formato fmt
N TO_DATE(n[,fmt]) converte char para uma data, de 
acordo com o formato fmt; este 
formato deve corresponder a um 
número (J, MM etc.)
N TO_NUMBER(char) converte char para um número; char 
deve conter um número válido
121
VI – Expressões e funções
• Conversão automática de tipos:
- 123 > `39` = 132 > to_number(`39`)
- `39` > 123 = `39` < to_char(123)
- `39` + 123 = to_number(`39`) + 123
- 123 | | `39` = to_char(123) | | `39`
• Se uma operação pode ser usada com vários tipos de dados, o 
tipo do primeiro operando determina o tipo dominante:
- 123 > `39`
• Se uma operação só pode ser usada com um tipo de dados, 
esse fica sendo o tipo dominante:
- `39` + 123
122
VI – Expressões e funções
• a tabela a seguir mostra os principais formatos de datas 
existentes (parâmetro fmt utilizado nas funções de conversão) 
aplicados à data 13:07:10 de 16/01/91
- OBS: qualquer combinação dos formatos acima pode ser 
feita para formar o parâmetro fmt (EX: `HH24:MI:SS-
DD/MON/YYYY`)
123
formato resultado descrição
SCC ou CC 20 século
SYYYY ou YYYY 1991 ano
YYY, YY e Y 991, 91 e 1 últimos 3, 2 e 1 dígitos do ano
SYEAR ou YEAR nineteen-ninety-one ano por extenso
MM 01 mês
MON jan nome do mês abreviado
MONTH january nome do mês
WW e W 03, 3 semana do ano e do mês
DDD, DD e D 016, 16, 4 dias do ano, mês e semana
DAY wednesday nome do dia
J 2448273 data Juliana
HH ou HH12 01 hora do dia (1-12)
HH24 13 hora do dia (0-23)
MI 07 minutos
SS 10 segundos
SSSSS 42730 segundos após a meia-noite
(0-83399)
Month, YYYY January , 1991 os tamanhos são fixos
FmMonth, YYYY January, 1991 os tamanhos são variáveis 
(“fill-mode”)
VI – Expressões e funções
VI.6 – Funções de datas
 
T Nome Descrição
D ADD_MONTHS(d,n) adiciona n meses à data d
ADD_MONTHS( `13-AUG-91`8) =
13-APR-92
D LAST_DAY(d) data do último dia do mês da data d
LAST_DAY( `13-FEB-92`) = 
29-FEB-92
N MONTHS_BEETWEEN(d,e) o número de meses existentes antes 
as datas d e e
MONTHS_BETWEEN( sysdate,
`01-JAN-80`) = 133.466529
D NEXT_DAY(d, char) devolve o primeiro dia da semana 
determinado por char (MONDAY, 
FRIDY etc.) com data igual ou maior 
que d
NEXT_DAY(`13-FEB-91 `,` MONDAY`) 
= 18-FEB-91
N TRUNC(d [, fmt]) devolve a data d “truncada” para o 
formato fmt. Se fmt não for 
especificado, devolve a data d sem as 
horas
TRUNC( to_date(`13-JAN-91 11:34`,
`DD-MONY-YY HH:MI`) ) = 13-JAN-91
N ROUND( d, fmt) devolve a data d arredondada para o 
formato fmt
ROUBD( to_date(`13-jan-91`), `YEAR`)
= 01-JAN-91
124
VI – Expressões e funções
VI.7 – Outras funções
T Nome Descrição
* DECODE(expr, s1, r1, se expr for igual a algum s, então 
 ...[padrão]) devolve o r seguinte; caso contrário 
devolve padrão. Caso padrão seja 
omitido e não haja “casamento” o 
valor