Manual Usuario SQL

MANUAL DO USUÁRIOSQL

C O M A N D O S

B Á S I C O S

Título do Documento / Descrição

Manual de Comandos Básicos – SQLManual do Usuário

Página

Nome do Arquivodocument.doc 2 de 27

ÍNDICE

1. Introdução................................................................................................................................32. Seleção de Dados....................................................................................................................43. Manipulando Dados...............................................................................................................124. Tabelas e Estrutura de dados................................................................................................165. Executando Cálculos com Dados..........................................................................................176. Mostrando dados de múltiplas tabelas..................................................................................26



Página


1. Introdução

O presente trabalho visa orientar o consultor a utilizar os comandos básicos do SQL e SQL*PLUSEste manual está definido em tópicos, conforme descrito abaixo:

Mostrar diferenças entre SQL e SQL*PLUS; Comandos SQL;

Este capítulo mostrará como criar, acessar e manipular o Oracle utilizando o SQL (Structured Query Languague ) e o SQL*Plus que é um software de propriedade da Oracle Corporation.

Escopo do SQL e SQL*PLUS

O SQL é uma linguagem para comunicação de qualquer ferramenta ou aplicação com o Oracle Server.O SQL*PLUS é uma ferramenta Oracle que executa comandos SQL. O mesmo não é uma extensão do SQL.

SQL

O SQL trabalha com um buffer que guarda apenas um comando por vez. Estes comandos podem ser editados no SQL*PLUS.

Comando SQL Buffer SQL

Tarefas que podemos fazer com o SQL:

Selecionar dados de uma Base de Dados; Adicionar, modificar e remover dados. Criar, modificar, e remover Estruturas de dados (tabelas por exemplo).

Comandos mais utilizados.

Para seleção de dados:

SELECT

Para manipulação de dados:

INSERTUPDATEDELETE COMMIT – depois de alterar algo, utilizar esse comando para efetivar a alteração, se não atualiza. ROLBACK – é para retornar o comando anterior.



Página


2. Seleção de Dados

O processo de seleção de dados (SELECT) aparece na fase de produção no ciclo de vida de desenvolvimento de um sistema .

Entrando no SQL*PLUS

Para se “logar” no SQL*PLUS você deve ser um usuário de banco de Oracle, ou seja, possuir um nome DE USUÁRIO E SENHA.

Mostrando a estrutura de uma tabela

Para mostrar a estrutura de uma tabela ,ou seja, suas colunas e características das mesmas, basta digitar o comando:

SQL> DESC nome da tabela.

Exemplo: SQL > DESC SAFX07

A coluna NULL mostra se deve ser preenchido algum valor nesta coluna, caso um registro seja inserido na tabela.

Mostrando dados com o comando SELECT

A função do comando SELECT é buscar e mostrar dados armazenados, em forma de tabelas, dentro do Banco de Dados.

SINTAXE:

SELECT colunas FROM nome da tabelaWHERE condiçõesORDER BY expresssão

Onde: SELECT - clausula que especifica quais colunas, expressões ou constantes serão lidas.FROM – clausulas que especifica qual (is) tabela(s) possuem estas colunas.WHERE – clausula que especifica critérios de seleção de linhas (opcional), se não colocada mostrará todos os dados da tabela.ORDER BY – clausula que dita a ordem que os dados devem ser mostrados. (opcional).

Mostrando todos os dados de uma tabela:

SQL> SELECT * FROM nome da tabelaExemplo: SELECT * FROM SAFX07;



Página


Para selecionar colunas em especifico:

SINTAXE: SELECT nome da coluna1, nome da coluna 2, ...FROM nome da tabela

Exemplo:

SQL> SELECT cod_empresa, cod_estab, movto_e_s FROM safx07;

Mostrando valores distintos:

Uma coluna pode possuir vários valores repetidos, se desejarmos ver estes valores sem repetição basta adicionarmos antes do nome da coluna a clausula DISTINCT.

SINTAXE:

SELECT [DISTINCT] nome da coluna 1, nome da coluna 2FROM nome da tabela

Exemplo:

SQL> SELECT DISTINCT cod_empresa FROM safx07;

Utilizando Alias (apelidos) em colunas

Dentro do comando SELECT podemos substituir (só na saída do resultado) o cabeçalho da coluna. Para isto basta inserir na frente do nome da coluna o alias (apelido) desejado.

Exemplo:

SQL> SELECT DISTINCT cod_empresa código_empresa, cod_estab código_estabelecimento FROM safx07;

CODIGO_EMPRESA CODIGO_ESTABELECIMENTO

AFT001 OSA

OBS: Quando você desejar que a escrita saia como você digitou, ou se existirem espaço em branco entre as palavras, o alias deve ser colocado entre “aspas duplas”. Caso contrario o SQL*PLUS utiliza o default.



Página


Clausula WHERE

A clausula WHERE, como explicado anteriormente, serve para selecionarmos linhas em uma tabela de acordo com a condição estabelecida.

Sintaxe:

SELECT {* [ nome da coluna] }FROM nome da tabelaWHERE condição

Esta condição pode ser, por exemplo, cod_empresa = 001 ou cod_estab <> ‘AFT ““.A tabela abaixo apresenta os operadores de comparação que podem ser utilizados na clausula WHERE.

= IGUAL A<> DIFERENTE DE

> MAIOR QUE< MENOR QUE< = MENOR IGUALBETWEEN ... AND ... ENTRE DOIS VALORESNOT BETWEEN … AND FOR A DA FAIXA ESTABELECIDAIN(lista) Igual a um dos valores da listaNOT IN DIFERENTE DOS VALORES DA LISTALIKE SUPRESSÃO DE CARACTERESIS NULL VALORES NULOSIS NOT NULL Valores diferentes de nulo

A seguir mostraremos cada condição:

Igual (=)

Retorna as linhas onde os valores de uma coluna são iguais aos estabelecidos.

Exemplo:

SQL> SELECT cod_empresa, cod_estab from safx07 WHERE cod_empresa = ‘001’;

Para comparações com valores em caracteres devemos tomar o cuidado de descrever o valor tal como foi armazenado, alem de colocar este valor entre ‘aspas simples’. Quando comparamos colunas que armazenam datas devemos respeitar a máscara default do ORACLE que é DD-MON-YYYY.

Exemplo com data:

SQL> SELECT data_saida_rec from x07_doctor_fiscal where data_saida_rec = ’01-jan-2000’ ;

Os valores da data também devem estar entre ‘aspas simples’.



Página


Diferente de (<>):

Retorna as linhas com valores diferentes do valor estipulado na clausula WHERE.

SELECT cod_empresa, cod_estab from safx07WHERE cod_empresa <> ‘001’;

Maior que (>)

Retorna linhas que possuam valores maiores que o estipulado na coluna envolvida na clausula WHERE.

Exemplo:

SQL> SELECT cod_empresa, cod_estab, num_docfis FROM safx07 WHERE vlr_contabil > 1500 ;

Podemos comparar data e caracteres também.

Maior ou igual a (> =)

Retorna linhas que possuam valores maior ou igual ao determinado.

SQL> SELECT cod_empresa, cod_estab from safx07 WHERE vlr_contabil >= 10000;

Menor que (<):

Retorna linhas que possuam valores menores que o estabelecido.

Exemplo:

SELECT cod_empresa, cod_estab from x07_docto_fiscalWHERE data_saida_rec < ’01-jan-2000’;

Menor igual a ( < = )

Retorna linhas que possuam valores, na coluna envolvidos, menores ou iguais ao valor de comparação.

SQL> SELECT cod_empresa, cod_estab, num_docfis from x07_docto_fiscal WHERE data_saída_rec <= ’01-jan-2000’;

Entre dois valores ( BETWEEN ... AND ... )

Todas as linhas que possuam valores dentro da faixa determinada serão listadas.Exemplo :

SQL > SELECT cod_empresa, cod_estab from x07_docto_fiscal WHERE data_saida_rec between ’01-jan-2000’ and ’31-jan-2000’ ;Todas as NF’s emitidas entre as datas listadas na clausula WHERE serão listadas.



Página


Fora da faixa estabelecida (NOT BETWEEN... AND...):

Retorna todas as linhas que se encontram fora da faixa discriminada na cláusula WHERE.

SQL> SELECT cod_empresa, cod_estab from safx07 WHERE num_docfis NOT BETWEEN ‘101’ and ‘150’ ;

Igual a um dos valores da lista ( IN (lista)):

Usando o comparador IN na cláusula WHERE, podemos determinar valores de comparação.

SQL> SELECT cod_empresa, cod_estab from safx07 WHERE cod_empresa = ‘001’ AND cod_estab in ( ‘AFT’ , ‘OSA’) ;

Todas as NF’s que pertençam à empresa 001 e todos os estabelecimentos com código igual a um dos valores da lista serão mostrados.

Diferente dos valores da lista ( NOT IN ( lista) ) :

Retorna as linhas que possuem valores diferentes dos valores descritos na lista.

SQL> SELECT cod_empresa, cod_estab from safx07 WHERE cod_empresa = ‘001’ AND cod_estab not in ( ‘AFT’, ‘OSA’);

Todas as NF’s que não pertençam aos estabelecimentos serão listados.

Supressão de caracteres (LIKE)

Para mostrar linhas que possuem parte dos valores comuns, devemos utilizar o operador LIKE. Este operador pode ter um dos dois símbolos listados abaixo:

% Qualquer número de caracteres (análogo ao * do DOS) _ Um único caráter (análogo ao ? do DOS)

SQL > SELECT * FROM safx01 WHERE cod_conta like '089.0001.1101%' ;

Podemos utilizar também em datas:

SQL > SELECT * FROM x07_docto_fiscal WHERE data_saida_rec like '%-FEB-02' ;

Todas as NF’s com data de Fevereiro de 2002 serão listados.

O exemplo abaixo utiliza o outro símbolo ( _ ) .

Exemplo:



Página


Listar os funcionários que o sobrenome começa com a letra ‘S’, e termine com a letra ‘h’ e possua 5 letras no nome.

SQL> Select last_name FROM s_emp WHERE last_name LIKE ‘S_____h’;

Obs: Podemos comparar os dois símbolos em uma mesma comparação.

Valores nulos ( IS NOT NULL)

As colunas que se encontram vazias, para o banco de dados elas possuem valores chamadas NULL. Este valor possui características específicas que geram a necessidade de comando particular para estes casos.Se desejarmos visualizar as linhas onde uma determinada coluna está vazia devemos utilizar o IS NULL. Veja no exemplo abaixo.

Exemplo:

SQL> SELECT * FROM safx08 WHERE data_fiscal IS NULL;

O exemplo acima mostrará todas as NF’s que não possuem data fiscal relacionados.

Valores diferentes de NULL ( IS NOT NULL)

Da mesma forma poderíamos visualizar só as linhas que possuem dados ( quaisquer ) em uma coluna.

Exemplo:

SQL > SELECT * FROM safx04 WHERE cod_fis_jur IS NOT NULL;

Complexidade nas condições

A cláusula WHERE pode ter mais que uma condição no mesmo comando. Para isto incluiremos os operados AND e OR onde:

AND mostra a linha se todas as condições subpostas no AND são verdadeiros.OR mostra a linha se uma das condições da cláusula for verdadeiras.

Exemplo:

SQL > SELECT cod_emp, cod_estab 2 FROM SAFX07 3 WHERE cod_empresa = '001' 4 AND cod_estab = 'RJ0015' ;

Apenas as NF’s da empresa 001 e estabelecimento RJ0015 serão listados.

Exemplo:

SQL> SELECT *



Página


FROM safx07 AND cod_empresa = ‘001’ OR cod_fis_jur = ‘1’ ;

Apenas NF’s que pertençam à empresa 001 ou código física jurídica = 1 (mesmo de outras empresas) serão listadas.A combinação de duas ou mais condições de busca obedecem a uma regra de procedentes. O resultado da pesquisa depende desta ordem.

Ordem OperadorTodos os operadores ( = , < , > , between, ...)AND OR

Para quebrar esta precedência, basta utilizar parênteses.

Exemplo:

SQL> SELECT nome, salario, depto FROM emp WHERE salário >= 1000 and ( depto = 44 or depto = 42) ;

Neste caso apenas funcionários do departamento 44 e 42 com salário maior ou igual a 1000 serão listados.OBS: Teste o exemplo acima retirando os parênteses.

Ordenação

A ordenação de um resultado não influencia na ordem de gravação dos dados.Podemos definir dois sentidos de ordenação ascendentes e descendentes.A cláusula utilizada para ordenação é ORDER BY.

Sintaxe:

SELECT { * [ nome da coluna ] }FROM nome da tabela WHERE condição ( opcional)ORDER BY nome da coluna ASC

Exemplo:

SQL> SELECT razao_social , data_x04 FROM safx04 WHERE data_x04 = '20000101' ORDER BY razao_social ;

Razao_social data_atualizacao

ABCD Contabilidade 01-jan-2002Beta Comunicações 11-feb-2002Telesp Celular 31-mar-2002Exemplo:

SQL> SELECT razao_social , data_x04 FROM safx04



Página


WHERE data_x04 = '20000101' ORDER BY razao_social DESC;

Razao_social data_atualizacao

Telesp Celular 31-mar-2002Beta Comunicações 11-feb-2002ABCD Contabilidade 01-jan-2002

OBS1: Note que apenas o operador ascendente deve ser explicitado.OBS2: Não é necessária a coluna utilizada no ORDER BY estar no SELECT.

Quando utilizamos mais que uma coluna no ORDER BY o resultado é dado em função do subconjunto utilizado.

Exercícios:

Tabela: SAFX01

Selecionar conta, indicador (débito / crédito) e valor

1) Onde valor maior que 1000 select conta_deb_cred, ind_deb_cre, vlr_lancto from safx01 where vlr_lancto > 1000;

2) Retornar somente os créditosselect conta_deb_cred, ind_deb_cre, vlr_lancto from safx01 where ind_deb_cre = 'C';

3) Data do lançamento entre 1 e 6 de janeiro de 2005select conta_deb_cred, ind_deb_cre, vlr_lancto from safx01 where data_operacao between '20050101’and '20050106';

4) Listar somente as contas 15000600 e 150006051select conta_deb_cred, ind_deb_cre, vlr_lancto from safx01 where conta_deb_cred in ('15000600', 150006051');

Faça-os separadamente e por último junte os quatro exercícios num só.

select conta_deb_cred, ind_deb_cre, vlr_lancto from safx01 where vlr_lancto > 1000 and ind_deb_cre = 'C' and data_operacao between '20050101’ and '20050106' and conta_deb_cred in ('15000600', 150006051');



Página


3. Manipulando Dados

Objetivos:

Adicionar registros em uma tabela Gerar números seqüenciais automaticamente Modificar um registro existente Remover um registro de uma tabela Efetivar mudanças no banco de dados

Introdução

A manipulação de dados consiste de cinco comando básicos. São eles:

INSERT - Insere uma nova linha em uma tabelaUPDATE Modifica linhas existentes.DELETE Remove linhas de uma tabelaCOMMIT Confirma mudanças no Banco de dadosROLLBACK Descarta mudanças no banco de dados

Inserção de dados:

Para adicionar uma nova linha a uma tabela utilizamos o comando INSERT

Sintaxe:

INSERT INTO nome da tabela [ (nome da coluna [ , nome da coluna , ...]) ]VALUES ( valor1, [, valor 2 ...])

Onde os valores são dados correspondentes às colunas da tabela em questão.

Exemplo:

SQL> INSERT INTO empresa (cod_empresa,razao_social, senha) VALUES ( '099', 'MASTERSAF', 'MSAF');

Obs 1 : Quando as todas as colunas são preenchidas não é necessário listá-las

Obs 2 : Os valores que recebem caracteres ou datas devem estar entre aspas simples.

Quando nem todas as colunas possuem valores definidos devemos descrever os nomes das colunas.

Exemplo:

SQL> INSERT INTO func ( cod_func, nome_func, dt_admissao, cargo, salario) VALUES ( ‘001’, ‘Adriana’, ’01-ago-1970’, SYSDATE, ‘consultor’, ‘3000’);

Note que a tabela func possui outras colunas não listadas que receberão valor NULL.O SYSDATE é uma função que retorna a data corrente do sistema.O valor NULL poder ser explicitado na listagem de valores, se desejado.Exemplo:



Página


SQL> INSERT INTO func ( cod_func, nome_func, dt_admissao, cargo, salario) VALUES (‘001’, ‘Adriana’, ’01-ago-1970’, SYSDATE, ‘consultor’, NULL);

Note que podemos tanto escrever NULL como utilizar ‘’ (aspas simples).Porém devemos saber que as aspas simples só servem para colunas tipo caracter ou data.

Modificando dados

Para modificar dados existentes em uma tabela utilizamos o comando UPDATE.

Sintaxe;

UPDATE nome da tabelaSET nome da coluna = novo valor, [ nome da coluna = novo valor ] WHERE condiçãoExemplo:

SQL> UPDATE X07_docto_fiscal SET data_saida = ’01-jan-2000’ , serie_docfis = ‘1’ WHERE num_docfis = ‘123’ ;

Exemplo:

SQL> UPDATE X07_docto_fiscal SET movto_e_s = ’1’ WHERE data_saída = ’10-jan-2000’ ;

A cláusula WHERE é muito importante neste comando porque ela determina qual ou quais linhas serão modificadas.

No comando acima todos as NF’s cuja data seja 10 de janeiro terão o valor da coluna movto_e_s modificada para 1.

Exemplo:

SQL> UPDATE SAFX07 SET movto_e_s = ’1’

Já neste exemplo todas as linhas da tabelas terão seus valores alterados.

Removendo dados:

Sintaxe:

DELETE FROM nome da tabelaWHERE condição



Página


Exemplo:

SQL> DELETE FROM safx07 WHERE NUM_DOCFIS = ‘123’

O registro que possui a coluna NUM_DOCFIS igual a 123 será removido.Nesse comando a cláusula WHERE é muito importante, pois podemos remover TODOS os dados caso não colocarmos esta clausula.

Exemplo:

SQL> DELETE FROM safx07;

Todos os dados da tabela safx07 serão removidos.



Página


Controle de Transações:

A forma de controlar as transações dentro de um Banco de dados é utilizando os comandos COMMIT e ROLLBACK. Uma transação é um conjunto de comandos ( insert, delete, updates).

A função do Commit é finalizar a transação corrente tornando permanente todas as modificações.

Já o Rollback apenas finaliza a transação corrente sem transferir para os arquivos de armazenamento as modificações efetuadas.

Insert....Insert ...Delete..ROLLBACKUpdate…Update COMMIT (CUIDADO COM ESTE COMANDO, PORQUE AO EXECUTAR ESSE COMANDO O QUE TEM ACIMA DELE IRÁ ATUALIZAR, ENTÃO A CADA ALTERAÇÃO EXECUTAR O COMMIT).

É importante destacar que todo dado modificado durante a transação só será efetivado quando aquela transação for confirmada (COMMIT).

Estado do dado antes do COMMIT ou ROLLBACK

Os comandos de manipulação de dados inicialmente afetam o database bufferO usuário corrente (que efetuou a mudança) pode visualizar suas modificações através do comando SELECT.Outros usuários não enxergam estas modificações.As linhas afetadas nos comandos”lockadas”. Nenhum outro usuário pode modificar estas linhas.

Estado do dado após um comando COMMIT

As mudanças são retiradas do database buffer e escritas em arquivos.Todos os usuários podem visualizar as modificaçõesOs “locks” nas linhas afetadas são liberados.

Estado do dado após um comando ROLLBACK

As modificações são descartadas. Os arquivos continuam como estavam.Os “locks’ na linha afetadas pelas modificações são liberadas.



Página


4. Tabelas e Estrutura de dados

Na fase do ciclo de vida de desenvolvimento de um sistema temos a necessidade de criar tabelas para armazenamento de dados e estruturas auxiliares de acordo com o projeto em questão.

Tabela: Onde podemos depositar com segurança e eficiência, os dados da instituição e dele extrair informações com a devida presteza e confiabilidade.

Tipos de dados no Oracle:

Ao determinar um tipo de dados, pode-se escolher entre três características básicas: Caracter, Numérico, ou Data. Estas características são abaixo detalhadas:

CHARACTEROs datatypes char e varchar2 armazena dados alfanuméricos. Estes datatypes podem também armazenar caracteres especiais

CHAR – armazena string de caracteres de largura fixa, aceita uma largura de coluna entre 2 e 255 ( em bytes não caracteres). Espaços em branco são utilizados para preencher o espaço restante, no caso do valor ter tamanho inferior ao Maximo determinado para aquela coluna.VARCHAR2, ao contrario, armazena somente os caracteres significativos (o que inclui brancos anteriores à seqüência de caracteres e entre palavras) até 2048 bytes.

NUMBEREste tipo de dados armazena números com casas decimais fixas ou flutuantes.Oracle garante portabilidade de números com uma precisão igual ou inferior a 38 dígitos.Pode-se determinar um numero total de digito e uma quantidade de decimais.

DATEValores em forma de datas. Faixa se 1/1/4712 AC até 31/12/4712 DC

LONG É similar ao Varchar2, mas com tamanho máximo de 2 gigabytes.



Página


5. Executando Cálculos com Dados

Executar cálculos com números usando operadores aritméticos e funções Executar cálculos com datas usando operadores aritméticos e funções Formatação de números e datas Concatenação e manipulação de caracteres string Executar cálculos com função de agrupamento.

Executando cálculos aritméticos

Para modificar e apresentar dados numa variedade de caminhos podemos utilizar as funções aritméticas e padronização do SQL.

Para executar cálculos com números podemos utilizar os operadores aritméticos. São eles

+ Adição - Subtração * Multiplicação / Divisão

Os operadores aritméticos podem ser utilizados em quaisquer cláusulas do comando SELECT exceto a cláusula FROM.

Exemplo:

SQL> SELECT nome, salario, perc_comissao, salario * perc_comissao / 100 COMISSAO FROM funcionario WHERE funcao = ‘Vendedor’ AND salario * perc_comissao / 100 > 200 ORDER BY salario * perc_comissao / 100;

O resultado seria:

NOME SALARIO PERC_COMISSAO COMISSAO------------------ -------------- ------------------------- -----------------Nair 1525 15 228.75Silva 1459 17.5 253.75

Para utilizar os operadores aritméticos devemos obedecer às regras de precedência.

Regra:

1 Multiplicação e divisão ( * , / )2 Adição e subtração ( + , - )



Página


Exemplo:

SQL> SELECT nome , salario (salario + 100 ) * 12 “Bonus_anual” FROM funcionario WHERE cod_depto = 41;

NOME SALARIO bonus_anualSimone 1450 18600Andréa 1200 15600Julio 1400 18000Silvia 940 12480

Funções numéricas

As funções numéricas são padrão SQL e só tem efeito sobre números ou colunas do tipo number.

Funções ROUND

Esta função arredonda valores aos mostrá-los. Note que os valores no Banco de dados continuam intactos.

Exemplo:

SQL> SELECT nome , salario , salario/22, ROUND(salario/22,0) FROM funcionario WHERE cod_depto = 50 ;

NOME SALARIO SALARIO/22 ROUND(SALARIO/22,0)

Simone 2500 113.636364 114Andréa 1550 70.4545455 70

Função TRUNC:

Esta função mostra o numero truncado

Exemplo:

SQL> SELECT nome , salario , salario/22, TRUNC(salario/22,0) FROM funcionario WHERE cod_depto = 50 ;

NOME SALARIO SALARIO/22 TRUNC(SALARIO/22,0) Simone 2500 113.636364 113Andréa 1550 70.4545455 70



Página


Função MOD:

Esta função determina o valor referente ao resto da divisão de um numero.

Exemplo:

SQL> SELECT nome , data_final , SYSDATE , SYSDATE – data_final “total” TRUNC( ( SYSDATE – data_final ) / 7 , 0 ) SEMANAS, ROUND( MOD ( SYSDATE – data_final)_ , 7 ) , 0 ) DIAS FROM funcionário WHERE cod_depto = 50 ;

O resultado seria:

NOME DATA_FINAL SYSDATE total SEMANAS DIAS

Simone 09-FEB-1992 15-DEC-1993 675.833993 96 4Andréa 18-JAN-1992 15-DEC-1993 697.833993 99 5Junior 22-JAN-1992 15-DEC-1993 693.833993 99 1Mauro 07-FEB-1992 15-DEC-1993 677.833993 96 6

Cálculos com valor NULL

Qualquer calculo efetuado com um valor NULL o resultado será sempre NULL. Portanto para obter algum valor ou evitar divisão por um valor desconhecido, o que geraria um erro, utilizamos a função NVL que transforma o valor NULL em um número ou string desejado.

Sintaxe:

NVL ( expr1, expr2 )

Onde:

Expr1 – colunas que contem valores nulos. Expr2 – valor o qual os valores nulos serão transformados.

Exemplo:

SQL> SELECT nome , salario , NVL(perc_comissao,0) PORCENTAGEM, Salario * NVL( PERC_COMISSAO,0) / 100 COMISSAO FROM funcionario WHERE salário >= 1500;

NOME SALARIO PORCENTAGEM COMISSAO

Andréa 2500 0 0 Simone 1550 0 0Junior 1515 10 151.5Mauro 1525 15 228.75

Executando cálculos com datas



Página


Podemos utilizar os operadores aritméticos também nas datas

Expressão valida:

Data + numero – Soma um numero a uma data e devolve uma data posterior

Data – numero – Subtrai um numero da data e devolve uma data anterior.

Data – data – Subtrai uma data de oura data e retorna o numero de dias entre uma data e outra.

Exemplo;

SQL> SELECT nome , data_final , data_final + 90 “Aprovacao” FROM funcionario WHERE cod_depto = 42 ;

NOME DATA_FINAL AprovacaoMary 14-MAY-1990 12-AUG-1990Jorge 09-FEB-1991 10-MAY-1991Nelson 06-AUG-1991 04-NOV-1991

Funções de formatação

Elemento DescriçãoDD Dia do mês (1-31)DY As três primeiras letras do dia da

semana (FRI)DAY Dia da semana (MONDAY)DDSP Numero de dias do mês (TWELVE)MM Numero do mês (1-12)MON As três primeiras letras do mês (JAN)MONTH Nome do mes (JANUARY)

YY Dois dígitos do ano (03)

YYYY Quatro dígitos do ano (2003)

HH:MM:SS Hora minuto e segundos (10:00: 33)

Fm Supressão de espaços em branco

HH24 Hora de 0-23

TH Numero Ordinal

AM ou PM Para hora padrão ericano

Para modificar o formato de datas devemos utilizar a função TO_CHAR.

Sintaxe:



Página


TO_CHAR (coluna tipo data, máscara).

Exemplo

SQL> SELECT num_docto, TO_CHAR(data_fiscal, ‘MM/YYYY’) DATA_NF FROM safx08 WHERE cod_empresa = ‘001’;

Resultado:

Num_docto DATA_NF45670 02/200387644 03/200398374 05/2003

SQL> SELECT num_docfis, TO_CHAR( data_fiscal, ‘fmDD MONTH YYYY’) data_nf FROM safx08 WHERE cod_empresa = ‘001’;

Num_docfis DATA_NF45670 10 February 200387644 23 March 200398374 15 May 2003

Concatenação de caracteres string

Para concatenar caracteres string utilizamos o símbolo ||.

Exemplo:

SQL > SELECT num_docfis || ‘ - ‘ || serie_docfis nota_fiscal 2 FROM safx07 3 WHERE cod_empresa = ‘001’;

NOTA_FISCAL1234 - 189777 - 483646 - 9

Funções com caracteres

Mostra um caracter string com a primeira letra maiúscula.

Exemplo:

SQL > SELECT INITCAP( nome) nome_alterado , nome FROM x15_funcionario WHERE municipio LIKE 'RIO%';

NOME_ALTERADO NOMELuisa Brunet Da Silva LUISA BRUNET DA SILVACarla Perez De Oliveira Machado CARLA PEREZ DE OLIVEIRA MACHADOMaria Das Dores Silva Araujo MARIA DAS DORES SILVA ARAUJO



Página


UPPERTransforma todos os caracteres em maiúsculos.

Exemplo:

SQL> SELECT nome FROM x15_funcionario WHERE UPPER(nome) like '%SILVA%';

NOMELUISA BRUNET DA SILVAMARIA DAS DORES SILVA ARAUJOJOAO PEDRO DA SILVA JUNIOR

SUBSTRRetorna parte de uma string

Sintaxe

SUBSTR( STRING, m [ , n ] )

Onde: String: palavra ou coluna do tipo caracter que deve ser cortada. M : Especifica a posição inicial N : Especifica quantos carateres serão cortados a partir da posição inicial.

Exemplo:

SQL> SELECT descricao FROM safx2013 WHERE SUBSTR(descricao,1,3) = ‘Cad’;

PRODUTO-----------------CadernoCadeira

LENGTHRetorna o comprimento, em caracteres, de um string.

Exemplo:

SQL> SELECT num_docfis, LENGTH (num_docfis) “num_caracteres” FROM safx07 WHERE cod_empresa = '001’ NUM_DOCFIS Num_caracteres109876 64398764356 10



Página


Função de substituição (REPLACE)

Substitui um ou mais caracteres numa string:

SQL> UPDATE SAFX07 SET SERIE_DOCFIS = REPLACE(SERIE_DOCFIS, ‘-‘, ‘’) WHERE NUM_DOCFIS = ‘000123’;

Função DECODE

Tem a mesma função do comando “IF” (condição), porém só pode ser utilizado numa instrução SQL. Por exemplo, pode ser utilizado para apresentar o conteúdo de uma coluna de uma forma mais “amigável”.

SQL> SELECT DECODE(IND_PRODUTO, ‘1’, ‘PRODUTO ACABADO’‘2’, ‘INSUMO’,

‘3’, ‘EMBALAGEM’, ‘OUTROS’) IND_PRODUTO,

COD_PRODUTO FROM SAFX2013;

Funções de agrupamento

As funções de agrupamento são médias (AVG), valor máximo (MAX), valor mínimo (MIN), e soma (SUM)

SQL> SELECT AVG(salario) Media, MAX(salario) Máximo MIN(salario) Minimo, SUM (salario) Soma FROM funcionario WHERE UPPER (cargo) like ‘DIRETOR%’;

Media Máximo Mínimo Soma1476 1525 1400 7380

Contando linhas de uma tabela (MUITO USADO)

A função de agrupamento que retorna o número de linhas existentes em uma tabela é o COUNT.

SQL> SELECT count(1) TOTAL from safx01

TOTAL---------- 256



Página


Cláusula GROUP BY

Quando desejamos obter resultados agrupados o SQL nos fornece a cláusula GROUP BY que permite que os valores singulares possam ser combinados com valores agrupados na cláusula SELECT .

Sintaxe:

SELECT nome da coluna singular 1 [ , nome da coluna singular2, ...], função de agrupamento [, função de agrupamento...]FROM nome da tabelaWHERE condição ( opcional)GROUP BY nome da coluna singular 1 [ , nome da coluna singular2, ...]

Exemplo

SQL> SELECT cod_empresa, COUNT(*) “tot_emp” FROM safx08 GROUP BY cod_empresa

Cod_empresa tot_empAFT 330OSA 2344

SQL> SELECT cod_empresa, cod_estab , COUNT(*) total FROM safx08 GROUP BY cod_empresa, cod_estab;

Cod_empresa cod_estab totalAFT 001 330AFT 002 450OSA 001 2344

Cláusula HAVING

Esta cláusula restringe linhas mostradas pela cláusula GROUP BY. Podemos comparar analogamente a cláusula HAVING com a cláusula WHERE do SELECT.

Sintaxe:

SELECT nome da coluna singular 1 [ , nome da coluna singular2, ...], função de agrupamento [, função de agrupamento...]FROM nome da tabelaWHERE condição ( opcional)GROUP BY nome da coluna singular 1 [ , nome da coluna singular2, ...]



Página


Exemplo

SQL> SELECT X02.COD_EMPRESA EMP, X02.COD_ESTAB EST, X02.DATA_SALDO DATA, X02.COD_CONTA IDCONTA, COUNT(*) FROM SAFX02 X02 GROUP BY X02.COD_EMPRESA , X02.COD_ESTAB , X02.DATA_SALDO , X02.COD_CONTA HAVING COUNT(*) > 1

Exercícios:

Tabela: SAFX03

1) Somar valor de movimentação agrupando pelo indicador de débito e crédito (listar as colunas indicador e valor)SELECT SUM (VLR_MOVTO), IND_DEB_CRE FROM SAFX03 GROUP BY IND_DEB_CRE;

2) Listar a menor data de emissão existente na tabelaSELECT MIN (DATA_EMISSAO) FROM SAFX03;

3) Listar o maior número de nota fiscal existente na tabelaSELECT MAX (NUM_DOCFIS) FROM SAFX03;

4) Contar a quantidade de registros para cada código de pessoa física / jurídica (listar quantidade e código).SELECT COD_FIS_JUR, COUNT (1) FROM SAFX03 GROUP BY COD_FIS_JUR;



Página


6. Mostrando dados de múltiplas tabelas

Objetivos:

Selecionando dados de múltiplas tabelas

Para selecionar dados que se encontram em mais de uma tabela utilizamos uma regra chamada JOIN CONDITION. Esta regra aconselha que as tabelas devem estar relacionadas.

Por exemplo:Mostre o numero da nota fiscal e todos os itens.

Para executar esta query devemos fazer um JOIN entre a tabela SAFX07 E SAFX08 utilizando como comparação os campos chaves das tabelas.

EQUI JOIN

É a comparação simples entre colunas relacionadas.

Sintaxe

SELECT tabela1.coluna, tabela1.coluna , tabela2.coluna , tabela2.colunaFROM tabela1, tabela2WHERE tabela1.coluna_chave = tabela2.coluna_chave

Exemplo:

SELECT X07.COD_EMPRESA EMP, X07.COD_ESTAB ESTAB, X07.DATA_FISCAL DATA, X07.MOVTO_E_S E_S, X07.IDENT_FIS_JUR PFJ, X07.NUM_DOCFIS NF, X07.SERIE_DOCFIS SER, X07.VLR_TOT_NOTA VL_NF, X07.VLR_PRODUTO VL_PROD, SUM(X08.VLR_CONTAB_ITEM) VL_ITEMFROM X07_DOCTO_FISCAL X07, X08_ITENS_MERC X08WHERE X07.COD_EMPRESA = X08.COD_EMPRESA AND X07.COD_ESTAB = X08.COD_ESTABAND X07.DATA_FISCAL = X08.DATA_FISCALAND X07.MOVTO_E_S = X08.MOVTO_E_SAND X07.NORM_DEV = X08.NORM_DEVAND X07.IDENT_DOCTO = X08.IDENT_DOCTOAND X07.IDENT_FIS_JUR = X08.IDENT_FIS_JURAND X07.NUM_DOCFIS = X08.NUM_DOCFISAND X07.SERIE_DOCFIS = X08.SERIE_DOCFIS GROUP BY X07.COD_EMPRESA , X07.COD_ESTAB , X07.DATA_FISCAL , X07.MOVTO_E_S , X07.IDENT_FIS_JUR , X07.NUM_DOCFIS , X07.SERIE_DOCFIS , X07.VLR_TOT_NOTA , X07.VLR_PRODUTO



Página


UNIÕES

Podemos eventualmente unir duas linhas de consultas simplesmente utilizando a palavra reservada UNION.

Exemplo:Liste todos os clientes/fornecedores que tenham indicador = 3 e seja de SP ou que tenham indicador = 5 e seja de RJ

SELECT * FROM X04_PESSOA_FIS_JURWHERE IND_FIS_JUR = 5AND IDENT_ESTADO = 69UNIONSELECT * FROM X04_PESSOA_FIS_JURWHERE IND_FIS_JUR = 3AND IDENT_ESTADO = 76;

SUB-CONSULTAS

Uma sub-consulta é um comando "select" que é aninhado dentro de outro "select" e que devolve resultados intermediários.

Exemplo:

Listar todos os itens de nota que estejam cadastrado no Mastersaf como embalagem.

SELECT COD_EMPRESA, COD_ESTAB, DATA_FISCAL, NUM_DOCFIS, SERIE_DOCFISFROM X08_ITENS_MERCWHERE IDENT_PRODUTO IN (SELECT IDENT_PRODUTO FROM X2013_PRODUTO WHERE IND_PRODUTO = 3);

Documents

Manual Usuario SQL