Tema 2. Linguagem SQL basico Parte1

1

DISCIPLINA:

BASE DE DADOS 1

O docente:

Hélder MC Muianga



helder.muianga@uem.mz



Heddy.moz@hotmail.com

(msn)

(+258) 82 47 27 160

UEM

DMI

CONTEÚDO:

UEM

DMI

3

Objectivos do Presente

Objectivos do Presente

Módulo

Módulo

Ao fim deste módulo o participante deve ser capaz de:

Ao fim deste módulo o participante deve ser capaz de:



Fazer Consultas Usando a Linguagem SQL



Através do uso da cláusula SELECT



Através do uso de funções



Usar as Linguagens: DML, DCL e DDL



Através do uso de: INSERT, UPDATE, DELETE, TRUNCATE, COMMIT,

ROLLBACK, SAVEPOINT, CREATE, DROP.



Conhecer e Manipular os Diferentes Objectos da Base de Dados



tabelas, views, sequências, indexes, sinónimos



Conhecer e Manipular a Estrututura do Diccionário de Dados

Usando Views



Usar o SQL*Plus e iSQL*Plus

UEM

DMI

Conteúdos



Revisão



Bases de dados relacionais



Plataforma Oracle Internet



Linguagem SQL



SQL e iSQL*Plus



Uso de comando SELECT



Uso do iSQL*PLUS



Sintaxe básica do comando SELECT



Cláusulas SQL



Uso da cláusula WHERE



Uso dos operadores AND, OR e NOT

UEM

DMI

5

Conteúdos (2)



Cláusulas SQL (Cont.)



Comandos do iSQL*Plus



Pseudónimos



Exercícios



Funções que retornam uma única linha



Funções de manipulação de texto



Funções numéricas



Funções de manipulação de datas



Funções de conversão



Funções gerais



Outras funções



Exercícios

UEM

DMI

Conteúdos (3)

Conteúdos (3)



Funções agregadas



Count, Min, Max, Sum, Avg



Funções agregadas compostas



Exercícios



SubConsultas



Sintaxe de uma Sub Consulta



Operadores de subconsultas (IN, ANY, ALL)



Exercícios



Junção de várias tabelas



Sintaxe



Tipos de junção:



JOIN, CROSS JOIN, NATURAL JOIN, INNER JOIN e OUTER JOIN,

RIGHT JOIN E LEFT JOIN

UEM

DMI

7

Conteúdos (4)



DML (Linguagem de Manipulação de Dados)



Insert, Update, Delete e Truncate



Exercícios



Conceito de Transação



DCL-Data Control Language (Linguagem de Controle de Dados)



Commit, Rollback e SavePoint



Exercícios



Objectos da Base de Dados



Tabelas, Views, Sequências, Indexes e Sinónimos



DDL – Data Definition Language



Create, Drop



Criar tabelas e views usando create e usando Sub Consultas



Definição de restrições



Pryimary Key, Foreign Key, Unique, Not Null, Check



Exercícios

UEM

DMI

Conteúdos (5)



Manuseando Dados Usando as Views do

Dicionário de Dados



O dicionário de dados



Estrutura do dicionário de dados



Como usar o dicionário de dados



Informações sobre tabelas, sequências, views,

colunas, constraints, sinónimos

UEM

DMI

9

Característica de Bases de

Dados Relacionais (Revisão)



Relacionamento entre Tabelas



Os dados numa base de dados relacional são expressos em

tabelas (relações), que se apresentam em linhas e colunas;



Entre as tabelas existem relacionamentos



Sistemas de Gestão de Bases de Dados Relacionais

(SGBDR)



Os SGBDR fornecem dados solicitados pelas aplicações cliente



Uso da Linguagem SQL



Para facilmente manipular os dados numa base de dados

UEM

DMI

Tabela (Revisão)



A Tabela possui linhas e colunas



A Coluna exprime o tipo de dados



A Linha exprime um registo (conjunto de dados)

Linha

campo

UEM

DMI

11

Relacionamento Entre Tabelas

(Revisão)



Uma BDR possui diversos relacionamentos entre as

tabelas



Um relacionamento é feito ligando-se um campo de uma

tabela X a um campo de outra tabela Y



Tipos de relacionamentos:



Um para um

(

1 para 1

)



Um para muitos

(

1 para N

)



Muitos para muitos

(

N para N

), que não pode ser

implementado directamente no modelo relacional e

tem que ser construído com uma tabela auxiliar.

UEM

DMI

Relacionamento Entre

Tabelas (Cont.)



Conectar tabelas



Eliminar redundância de dados

Funcionário

Pertence

Exprime

Tabela

Exprime

Relacionamento

Tabela Departamento

Departamento

Tabela Funcionário

UEM

DMI

13

Atributo de Uma Coluna



Cada coluna tem atributo (s)



Qual é o tipo de dados envolvidos?



Números, caracteres, data e mais



Grande, pequeno, longo, curto

Dado Ti po

Descr i pção

NUMBER( p, s)

Númer o que t em um númer o de máxi mo de p dí gi t os,

com dí gi t os com s dí gi t os à di r ei t a do pont o

VARCHAR2( s)

Val or de car át er de var i ável - compr i ment o de

t amanho máxi mo s

DATE

Dat a ent r e j anei r o de 1, 4712 A. C. , e 31 de

dezembr o de 9999 D. C.

UEM

DMI

Linguagem SQL



O SQL é uma linguagem usada para manipular dados

numa base de dados relacional



A Linguagem SQL possui as seguintes cláusulas



DML (SELECT, INSERT, UPDATE, DELETE)



DDL (CREATE, ALTER)

UEM

DMI

15

Plataforma Oracle Internet

Cliente

Aplicações de Internet

Base de dados

_{de Aplicações}

Servidor

SQL

PL/SQL

Java

Ferramentas

de Desenvolvimento

Lógica de negócio

e dados

Apresentação e

lógica de negócio

Sistema

UEM

DMI

SQL *Plus e iSQL *Plus



O SQL *Plus usa-se para executar o SQL



A partir do MS-DOS console



A partir de uma Aplicação Windows

UEM

DMI

17

Uso de SQL *Plus a Partir da

Consola Windows



Windows [Start] – [Run] escrever “cmd”



Começar a MS-DOS prompt



Escrever “sqlplus username/password@SID”

UEM

DMI

Uso de SQL *Plus a Partir da

Aplicação



Windows [Start] – [OracleClient] – [Application

Development] – [SQL *Plus]

UEM

DMI

19

Uso de iSQL *Plus



Começar o navegador da Internet Explorer



Escrever a seguinte URL:



http://

[servidor endereço]

:5560/isqlplus



Username : scott



Password : password

UEM

DMI

Cláusula SELECT



Para a visualização de dados



Para a obtenção de dados residentes numa base de

dados

Dado1

Dado2

_Dado3

Dado4

_Dado5

Dado6

UEM

DMI

21

Sintaxe da Cláusula SELECT



Selecção duma linha duma tabela



Sintaxe



SELECT

_coluna

[,

_coluna

] FROM

_tabela

;



Por exemplo

1.

SELECT enpno, enname FROM emp;

2.

SELECT * FROM emp;



“*” significa que todos os coluna

3.

SELECT DISTINCT(job) FROM emp;



suprime linhas duplicadas

4.

SELECT * FROM cat;



Tabela de cat é para ver tabelas que já existem

5.

SELECT 10-1 FROM DUAL;



DUAL é uma tabela especial



Pense noutros comandos SELECT



Não esqueça a terminação ponto-e-vírgula “;”

UEM

DMI

Exercícios

1.

Exiba todas tabelas existentes na base de dados

2.

Exiba os nomes das colunas para cada tabela

DESC

nome_da_tabela

;

1.

Exiba os nome dos departamentos e sua localização

2.

Exiba os nomes dos empregados e seus salários

UEM

DMI

23

Cláusulas de SQL



Cláusula WHERE



BETWEEN, IN, LIKE e Condição IS NULL



Operador AND, OR e NOT



Cláusula ORDER BY

UEM

DMI

Cláusula WHERE



Sintaxe



SELECT

_coluna

[,

_coluna

] FROM

_tabela

[

_{WHERE condição}

];



A cláusula WHERE restringe a quantidade de linhas retornadas

pelo comando Select



Por exemplo

1.

SELECT ename, sal FROM emp WHERE SAL = 3000



Os Nomes que recebem salário igual a 3000.

EMPNO

ENAME

J OB

MGR

HIREDATE

SAL

COMM DEPTNO

7369 SMITH

CLERK

7902 17- DEC- 80

800

20

7499 ALLEN

SALESMAN

7698 20- FEB- 81

1600

300

30

7521 WARD

SALESMAN

7698 22- FEB- 81

1250

500

30

7566 J ONES

MANAGER

7839 02- APR- 81

2975

20

7654 MARTIN SALESMAN

7698 28- SEP- 81

1250

1400

30

7698 BLAKE

MANAGER

7839 01- MAY- 81

2850

30

7782 CLARK

MANAGER

7839 09- J UN- 81

2450

10

7788 SCOTT

ANALYST

7566 19- APR- 87

3000

20

7839 KING

PRESIDENT

17- NOV- 81

5000

10

7844 TURNER SALESMAN

7698 08- SEP- 81

1500

0

30

UEM

DMI

25

Operadores da Cláusula

WHERE

1.

SELECT * FROM emp WHERE ename=‘MARTIN’;

2.

SELECT ename FROM emp WHERE SAL > 3000;

3.

SELECT ename FROM emp WHERE SAL < 3000;

4.

SELECT ename FROM emp WHERE SAL >= 3000;

5.

SELECT ename FROM emp WHERE SAL <= 3000;

6.

SELECT ename FROM emp WHERE ename=‘KING’;

Operador Descrição

=

Igual a

>

Maior que

<

Menor que

>=

Maior ou igual que

<=

Menor ou igual que

<>

Diferente

UEM

DMI

Exercícios

1.

Seleccione os nomes dos empregados que

tenham salário menor ou igual a 1000.

2.

Seleccione todos os dados do empregado

“SCOTT”.

3.

Seleccione os nomes dos empregados cujo

gerente chama-se “BLAKE”.

4.

Liste os nomes dos empregados cujo número do

departamento é 20 ou 30.

5.

Liste os nomes dos empregados cujo número do

UEM

DMI

27

Operadores AND, OR e NOT

Operador

Operador

Significado

Significado

AND

Devolve-nos VERDADE se as duas condições

forem VERDADEIRAS

OR

Devolve-nos VERDADE se uma das condições

for VERDADEIRA

NOT

Altera o valor lógico da proposição/condição

seguinte

Condições Lógicas

As operacoes lógicas combinam os resultados de duas condicoes de

componentes para produzir um resultado único baseado nessas condicoes

ou inverter os resultados de uma única condicao.

UEM

DMI

Operadores AND, OR e NOT

(Cont.)

Condição1

Condição2

AND

Condição1

_OR

Condição2

Operador Exemplo

AND

condição1

AND

condição2

OR

condição1

OR

condição2

UEM

DMI

29

Exemplos

1.

SELECT ename, job, sal FROM emp

WHERE job=‘CLERK’

AND

sal >= 1000;

2.

SELECT ename, job FROM emp WHERE

job=‘SALESMAN’

OR

job=‘MANAGER’;

3.

SELECT ename FROM emp WHERE

NOT

JOB=‘PRESIDENT’;

UEM

DMI

Condição BETWEEN



A condição BETWEEN é usada para mostrar dados dentro de

um intervalo.



Ela é usada com a cláusula WHERE e a condição lógica AND.



Sintaxe



SELECT … FROM …

WHERE

coluna

BETWEEN

expressão

AND

expressão



Exemplo

1.

SELECT ename, sal FROM emp

WHERE sal

BETWEEN

1000

AND

3000;

2.

SELECT ename, sal FROM emp

WHERE sal

NOT BETWEEN

1000

AND

3000;

3.

SELECT empno, ename FROM emp

UEM

DMI

31

Condição IN



O IN é usado para verificar se um elemento pertence ou não a um

dado conjunto.



É também conhecido como

_{membership condition}



É usado dentro da cláusula WHERE;



Sintaxe



SELECT … FROM …

WHERE

coluna

IN (

expressão1

,

expressão2

, …);



Por exemplo

1.

SELECT empno, ename FROM emp

WHERE empno IN (7369, 7499, 7521);

2.

SELECT empno, ename FROM emp

WHERE empno NOT IN (7369, 7499, 7521);

3.

SELECT empno, ename FROM emp

WHERE ename IN (‘KING’, ‘SMITH’);

UEM

DMI

Condição LIKE



A condição LIKE serve para realizar procuras wildcard de strings

de valores válidos

(valid search strings values)



Ela é usada dentro da cláusula WHERE



Pode ser usada também para realizar uma procura vaga



Sintaxe



SELECT … FROM …

WHERE

coluna

LIKE ‘[% ou _]

expressão

[% ou _]’;



Utilizam-se dois

_WildCards

Wildcard

Significado

%

Qualquer conjunto de zero ou mais caracteres

UEM

DMI

33

Condição LIKE (Cont.)



Por exemplo

1.

SELECT ename FROM emp WHERE ename LIKE ‘S%’;

2.

SELECT ename, job FROM emp WHERE job LIKE ‘%AN%’;

3.

SELECT ename FROM emp WHERE ename LIKE ‘_ _RD’;

ENAME

SMITH

SCOTT

ENAME

WARD

FORD

ENAME

JOB

ALLEN

SALESMAN

WARD

SALESMAN

JONES

MANAGER

MARTIN

SALESMAN

BLAKE

MANAGER

CLARK

MANAGER

SCOTT

ANALYST

TURNER

SALESMAN

FORD

ANALYST

1

₂

3

UEM

DMI

Condição NULL ou

NOT NULL



A condição NULL inclui as condições

IS NULL

and

IS NOT NULL

.



O valor NULL significa valor não existente,

desconhecido ou inaplicável.



Ela é usada dentro da cláusula WHERE



Quando os dados não existem designam-se NULL



Uso de IS NULL ou IS NOT NULL



Por exemplo

1.

SELECT * FROM emp WHERE mgr IS NULL;

UEM

DMI

35

Precedência dos

Operadores



Em SQL os operadores

têm diferentes

precedências



A precedência

decresce no sentido da

seta, quer isto dizer

que os operadores que

estão mais acima são

executados primeiro

Parêntesis

( )

Multiplicação / Divisão

ª /

Adição / Subtracção

+

-NOT

AND

OR

UEM

DMI

Exemplo de Precedência



Vamos comparar dois comandos seguinte:

1.

SELECT ename, job, sal FROM emp WHERE sal >= 2500

OR sal < 1500 AND job='SALESMAN‘;

2.

SELECT ename, job, sal FROM emp WHERE

(

sal >= 2500

OR sal < 1500

)

AND job='SALESMAN‘;

ENAME JOB SAL

WARD SALESMAN 1250

JONES MANAGER 2975

MARTIN SALESMAN 1250

BLAKE MANAGER 2850

SCOTT ANALYST 3000

KING PRESIDENT 5000

FORD ANALYST 3000

resultado1

ENAME JOB SAL

WARD SALESMAN 1250

MARTIN SALESMAN 1250

resultado2

OR

AND

UEM

DMI

37

Cláusula ORDER BY



O ORDER BY ordena uma ou várias colunas.



Pode ser usada com os seguintes operadores:



ASC: Ascendente (crescente) – ordenamento

_default



DESC: Descendente (decrescente)



Ela deve ser a última cláusula do comando SQL



Sintaxe



SELECT …

FROM …

...

ORDER BY

coluna1

, [

coluna2

, …]

ASC

DESC

UEM

DMI

Exemplo

1.

SELECT * FROM emp

ORDER BY ename;

2.

SELECT ename, sal FROM emp

WHERE job=‘SALESMAN’

ORDER BY sal DESC;

3.

SELECT ename, job, sal FROM emp

ORDER BY job, sal;

4.

SELECT ename, job, sal FROM emp

UEM

DMI

39

Variáveis de Substituição



Variáveis de substituição ‘&’ ou ‘&&’



Podem-se usar variáveis de substituição para guardar

temporariamente valores e servem de auxílio a:



condição WHERE



Cláusula ORDER BY



Expressões das Colunas



Nomes das tabelas



Comando SELECT



Exemplo



SELECT * FROM emp

WHERE empno=&empno;



SELECT ename, sal*12 FROM emp

WHERE job=‘&job_title’;



SELECT empno, job, &column_name FROM emp

WHERE &condition ORDER BY &order_column;



SELECT && column_nome FROM emp

ORDEY BY &column_nome;

UEM

DMI

Comandos de iSQL*Plus



O Comando DEFINE é usado para criar e atribuir

valores à variáveis.



O Comando UNDEFINE é usado para remover

variáveis.



O Comando VERIFY é usado para garantir a

exibição dos valores das variáveis de substituição

antes e depois do iSQL*Plus atribuir os valores às

variáveis.



SET VERIFY ON

UEM

DMI

41

Uso de Pseudónimos Para

Colunas e Tabelas

Podem-se usar pseudónimos para referenciar colunas

e tabelas.



Sintaxe



SELECT

_coluna

[AS ou

_espaço

]

_pseudónimo

FROM

tabela

[AS ou

espaço

]

pseudónimo;



Por exemplo use AS ou espaço

1.

SELECT empno

AS

eno FROM emp

WHERE eno=7900;

2.

SELECT empno eno FROM emp

WHERE eno=7900;

3.

SELECT empno eno, ename ena FROM emp e

WHERE ena=‘KING’;

UEM

DMI

Exercícios

1.

Crie um relatório que apresente os dados completos

dos trabalhadores que auferem um

salário maior que $35,000

por ano.

1.

Crie um relatório que exiba o

nome do empregado

e o

número do

departamento

do

empregado número 7788

.

2.

Crie um relatório que exiba os dados dos empregados cujo

salário

não está no

intervalo de $1,000

para

$3,000

.

3.

A Empresa precisa de um relatório que exiba o

nome

e

data do

contrato

de todos os empregados que foram

contratados em 1981

.

4.

Crie um relatório que exiba o

nome

e a

categoria

dos empregados

que

não tem gerente

(MANAGER).

5.

Exiba o

nome

,

salário

e

comissões dos empregados

que ganham

UEM

DMI

43

Exercícios (cont.)

1.

Seleccione e ordene por nome de empregados os trabalhadores

subordinados ao gestor número 7839.

2.

Seleccione e ordene por salário dos empregados os trabalhadores

subordinados ao gestor número 7698.

3.

Seleccione e ordene por número de empregados os trabalhadores

subordinados ao gestor número 7566.

4.

Exiba os nome dos empregados que tem como terceiro caracter do

seu nome a letra A.

5.

Exiba nome dos empregados que tem (ambas) as letras A e E em

seus nomes.

6.

Exiba o nome, o trabalhador e salário dos empregados, cujos

trabalho é “SALESMAN” ou “CLERK”, e cujos salário não é igual a

800, 1250, 1300

UEM

DMI

Respostas de tabelas

ENAME SAL*12 JONES 35700 SCOTT 36000 KING 60000 FORD 36000 ENAME DEPTNO SCOTT 20 ENAME SAL SMITH 800 KING 5000 JAMES 950 ENAME HIREDATE MARTIN 28-Sep-81 CLARK 9-Jun-81 KING 17-Nov-81 TURNER 8-Sep-81 JAMES 3-Dec-81 FORD 3-Dec-81 ENAME KING

ENAME SAL COMM

ALLEN 1600 300

WARD 1250 500

MARTIN 1250 1400

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7698 BLAKE MANAGER 7839 1-May-81 2850 30

7782 CLARK MANAGER 7839 9-Jun-81 2450 10

7566 JONES MANAGER 7839 2-Apr-81 2975 20

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7900 JAMES CLERK 7698 3-Dec-81 950 30

7521 WARD SALESMAN 7698 22-Feb-81 1250 500 30

7654 MARTIN SALESMAN 7698 28-Sep-81 1250 1400 30

7844 TURNER SALESMAN 7698 8-Sep-81 1500 0 30

7499 ALLEN SALESMAN 7698 20-Feb-81 1600 300 30

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7788 SCOTT ANALYST 7566 19-Apr-87 3000 20

7902 FORD ANALYST 7566 3-Dec-81 3000 20

UEM

DMI

45

Respostas de comandos

1.

SELECT ename, sal * 12 FROM emp

WHERE sal * 12 >= 35000;

2.

SELECT ename, deptno FROM emp

WHERE empno=7788;

3.

SELECT ename, sal FROM emp

WHERE NOT sal BETWEEN 1000 AND 3000

4.

SELECT ename, hiredate FROM emp

WHERE hiredate BETWEEN '01-JUN-81‘ AND '31-DEC-81';

5.

SELECT ename FROM emp

WHERE mgr IS NULL

6.

SELECT ename, sal, comm FROM emp

WHERE comm IS NOT NULL AND comm <> 0

7.

SELECT * FROM emp

WHERE mgr=7839 ORDER BY ename

8.

SELECT * FROM emp

WHERE mgr=7698 ORDER BY sal

9.

SELECT * FROM emp

WHERE mgr=7566 ORDER BY empno

10.

SELECT ename FROM emp

WHERE ename LIKE ‘_ _A%’

11.

SELECT ename FROM emp

WHERE ename LIKE ‘%A%’ AND ename LIKE ‘%E%’

12.

SELECT ename, job, sal FROM emp

WHERE (job=‘SALESMAN’ OR job=‘CLERK’) AND NOT sal IN (800, 1250, 1300)

UEM

DMI

RESUMO



Cláusula WHERE



SELECT ... FROM … WHERE

_condição

;



condição BETWEEN, IN, LIKE e IS NULL



WHERE

_coluna

BETWEEN

_{limite baixo}

AND

_{limite alto}

WHERE

_coluna

IN

(

_condição1

, [

_{condição2,…}

])



WHERE

_coluna

LIKE

_{’uso de}

%

ou

_’

WHERE

_coluna

IS [NOT] NULL



Operador AND, OR e NOT



WHERE

_condição1

AND

_condição2

WHERE

_condição1

OR

_condição2

WHERE NOT

_condição1



Cláusula ORDER BY



SELECT … FROM … WHERE … ORDER BY

_coluna1

[

_{, coluna2, …}

]

Pode-se usar ASC e DESC depois de coluna



Variáveis de substituição

UEM

DMI

47

Linha Única e Linha Múltipla

(Single-row and Multiple-row)

Funções

Funções

de

linha única

Funções

de

agregação

carácter

geral

número

data

conversão

UEM

DMI

Uso da Tabela DUAL



Tabela dual é uma tabela do sistema, usada

para exibir o resultado do próprio sistema



Por exemplo

1.

SELECT 1+1 FROM dual;

UEM

DMI

49

Funções de Linha Única

(single-row functions)



Caracter



aceita entrada de caracter e pode devolver caracter e valor

(número)



Número



Aceita entrada de valor numérico e pode devolver valores

numéricos



Data



Opera em valores do tipo de dados DATE



Conversão



Converte um valor de um tipo de dado para o outro



Geral



NVL, NVL2, HULLIF, COALESCE, CASE e DECODE

UEM

DMI

Funções de Caracter



Funções de manipulação de tamanho de texto



LOWER



UPPER



INITCAP



Funções de manipulação de texto



CONCAT



SUBSTR



LENGTH



INSTR



LPAD | RPAD



TRIM

UEM

DMI

51

Tabelas de Funções de

Caracteres

função propósito por exemplo resultado

LOWER(coluna) converte caráter alfa avalia a minúscula LOWER('SQL Course') sql course UPPER(coluna) converta caráter alfa avalia a maiúscula UPPER('SQL Course') SQL COURSE INITCAP(coluna) converta caráter alfa avalia a maiúscula para o

primeiro carater de cada paravla

INITCAP('SQL Course') Sql Course CONCAT(coluna1,

coluna2)

concatena(junta) primeiro valor do carácter para segundo valor docarácter. Pode usar operador (||) igual a CONCAT

CONCAT('Hello', 'World') HelloWorld

SUBSTR(coluna, m, n) retorna o caráter especificado que começa da posição m no caracter, com comprimento n.

SUBSTR('HelloWorld', 1, 5) Hello LENGTH(coluna) retorna número de carácteres da expressão LENGTH('HelloWorld') 10 INSTR(coluna, 'string', m,

n)

retorna posição númerica do caracter especificado(m).

INSTR('HelloWorld', 'W') 6 LPAD(coluna, n, 'string')

RPAD(coluna, n, 'string')

aloca o valor de caracter à esquerda (direita)-justificado para uma largura total de n

caracteres

LPAD(salary, 10, '*') RPAD(salary, 10, '*')

*****24000 24000***** TRIM(leading|trailing|both,

trim_character FROM trim_source)

Remove um certo caracter dentro de uma cadeia de caracteres

TRIM('H' FROM 'HelloWorld') elloWORLD

REPLACE(text, search_string, replacement_string)

substitua search_string por replacement_string no text

REPLACE('JACK and JUE', 'J', 'BL') BLACK and BLUE

Coluna ou expressão

UEM

DMI

Funções Numéricas



ROUND



Arredondamento



TRUNC



Retira a parte inteira de um valor fraccionário



MOD

UEM

DMI

53

Tabela de Funções

Numéricas

função

propósito

por exemplo

resultado

ROUND(coluna, n)

Arredonda valor a decimal especificado

ROUND(45.926, 2)

45.93

TRUNC(coluna, n)

Trunca valor a decimal especificado

TRUNC(45.926, 2)

45.92

MOD(m, n)

devolve resto de divisão

MOD(1600, 300)

100

Coluna ou expressão

UEM

DMI

Funções de Datas



Operações Aritméticas com Datas



Com Datas podemos efectuar operações de:



Adição



Subtracção

Mas temos de atender ao resultado de tipo

de dado

data + número data acrescenta vários dias a uma data data - número data subtrai vários dias de uma data data - data número de data subtrae um dia de outro

UEM

DMI

55

Tabela de Funções

de Datas

função propósito por exemplo resultado

SYSDATE exiba data de agora SELECT SYSDATE FROM dual; 10-Feb-06

função propósito por exemplo resultado

MONTHS_BETWEEN(date1, date2) número de meses entre duas datas MONTHS_BETWEEN('01-SEP-95', '11-JAN-94') 19.6774194 ADD_MONTHS(date, n) acrescenta calendário de meses para datar ADD_MONTHS('11-JAN-94', 6) '11-JUL-94' NEXT_DAY(date, 'char') dia que vem da data especificada NEXT_DAY('01-SEP-95', 'FRIDAY') '08-SEP-95'

LAST_DAY(date) último dia do mês LAST_DAY('01-FEB-95') '28-FEB-95'

função resultado ROUND(sysdate, 'MONTH') 01-AUG-03 ROUND(sysdate, 'YEAR') 01-JAN-04 TRUNC(Sysdate, 'MONTH') 01-JUL-03 TRUNC(Sysdate, 'YEAR') 01-JAN-03

Assuma

SYSDATE = ’25-JUL-03’

UEM

DMI

Conversão Explícita de Tipo

de Dados



Caso queiramos tratar um número como

sendo um caracter, temos de fazer uma

conversão de dados:

caracter

número

data

TO_CHAR

TO_NUMBER

TO_DATE

UEM

DMI

57

Conversão Explícita de Tipo

de Dados(Cont.)



Sobre formato de data

função propósito

TO_CHAR(número ou data, [formato], [nlsparâmetros]) número»carácter, data»carácter TO_NUMBER(carácter, [formato], [nlsparâmetros]) carácter»número

TO_DATE(carácter, [formato], [nlsparâmetros]) carácter»data



Sobre formato de tempo

elemento descripção

YYYY ano completo em números YEAR ano soletrado em Inglês

MM valor de dois-dígito durante mês MONTH nome completo do mês

MON três-letras abreviação do mês

DY três-letras abreviação do dia da samana DAY nome completo do dia da semana DD dia numérico do mês

elemento descripção

AM ou PM indicador meridiano

A.M. ou P.M. indicador meridiano com período HH ou HH12 ou HH24 hora de dia, ou hora(1-12), ou (0-23)

MI minuto(0-59)

SS segundo(0-59)

SSSSS segundo última meia-noite(0-86399)

UEM

DMI

Usando a Função TO_CHAR

com Números

•

_{TO_CHAR (número, ‘formato’)}

Este é o formato usado quando se pretende a usar função TO_CHAR

para visualizar um número como um caracter.

Eis alguns elementos usados para formatação:

elemento resultado

9

Representa um número

0

Força a visualização do zero

$

Coloca o símbolo do Dólar

L

Usa o símbolo da moeda local

UEM

DMI

59

Usando a Função TO_CHAR

com Números (Cont.)

•

_{Mais elementos usados para formatação}

elemento exemplo resultado

9 999999 1234

0 99999 001234

$ $999999 $1234

L L999999 FF1234

D 99D99 99.99

. 999999.99 1234.00

G 9G999 9,999

, 999,999 1,234

MI 999999MI

1234-PR 999999PR <1234> EEEE 99.999EEEE 1.234E+03

U U9999 €1234

V 9999V99 123400

S S9999 -1234 ou +1234

B B9999.99 1234.00

UEM

DMI

Por exemplo

1.

SELECT ename, TO_CHAR(hiredate, 'DD

Month YYYY') FROM emp;

2.

SELECT TO_CHAR(sal, '$99,999.00')

FROM emp WHERE ename=‘KING’;

3.

SELECT ename, hiredate From emp

UEM

DMI

61

Funções de uso Geral



Funções que devolvem um valor

_{(Single-Row Functions) – Estas}

funções operam numa única linha e retornam um único valor por

linha.

Estas funções:

•

Manipulam dados (caracteres, numeros, data, conversão)

•

Aceitam argumentos e devolvem apenas um valor

•

Actuam em apenas uma linha que é devolvida

•

Podem modificar o tipo de dado

•

Podem ser combinadas

•

Aceitam argumentos que podem ser uma coluna ou uma

expressão

nome_da_funcao[(arg1, arg2, ...)]

UEM

DMI

Funções de uso Geral (Cont.)

Algumas funções que devolvem um valor

apenas:



NVL(expr1, expr2)



NVL2(expr1, expr2, expr3)



NULLIF(expr1, expr2)



COALESCE(expr1, expr2, …)

função descripção

NVL convertido um valor null para um valor actual NVL2 Se expr1 não for null, NVL2 retorna expr2.

Se expr1 for null, NVL2 retorna expr3.

NULLIF compara duas expressões e retorna nulo se eles forem iguais; retorna a primeira expressão se eles não forem iguais

UEM

DMI

63

Exemplo

1.

NVL

1.

NVL(comm, 0)

2.

NVL(hiredate, ’01-JAN-80’)

3.

NVL(job, ‘No job Yet’)

2.

NVL2

1.

NVL2

(comm, ‘TEM COMM’, ‘NÃO TEM COMM’)

1.

Se o valor de

comm

não for

null

, retorna

TEM COMM

Se o valor de

comm

for

null

, retorna NÃO

TEM COMM

3.

NULLIF

1.

NULLIF

(job, ‘CLERK’)

1.

Retorna

null

, se valor de

job

for

CLERK

4.

COALESCE

1.

COALESCE

(comm, mgr, ‘falta’)

a.

Se

comm

não for

null

, exibe valor de

comm

Se

mgr

não for

null

exibe valor de

mgr

c.

Falta não é

null

, portanto exibe falta

UEM

DMI

Outras Funções



CASE



Sintaxe



CASE expr WHEN expr1 THEN exprA

[WHEN expr2 THEN exprB

WHEN expr3 THEN exprN

ELSE else_expr]

END



Se combinar expr1, retorna exprA



Por exemplo

1.

SELECT ename, job, sal,

CASE job WHEN 'CLERK' THEN 1.10*sal

WHEN 'SALESMAN' THEN 1.12*sal

ELSE sal END

UEM

DMI

65

Outras Funções (Cont.)



DECODE



Função DECODE facilita as consultas operando como

sendo se fosse a expressão CASE ou condição

IF-THEN-ELSE



Sintaxe



DECODE

(coluna | expressão, pesquisa1, resultado1

[, pesquisa2, resultado2, …,]

[, default ] )



Por exemplo

1.

SELECT ename, job, sal,

DECODE

(job, 'CLERK', 1.10*sal, 'SALESMAN',

1.20*sal, sal) FROM

emp;

UEM

DMI

Exercícios



Escreva um trecho que exiba a data actual



Pretende-se aumentar o salário de cada empregado.

Empregados que não têm

comm

auferirão um

aumento de 15%, os restantes 20%. Exiba resultado

dos empregados que auferiram tal aumento.



Exiba

ename

e comprimento de nome de cada

empregado



Exiba uma tabela com o nome do empregado e o

tempo de serviço na empresa (em meses)



Crie frase que “

_ename

recebe

_sal

por mês, mas

UEM

DMI

67

RESUMO



Funções de caracteres



Funções de manipulação de

tamanho de texto

1.

LOWER

UPPER

INITCAP



Funções de manipulação de

texto

1.

CONCAT

SUBSTR

LENGTH

INSTR

5.

LPAD | RPAD

TRIM

7.

REPLACE



Funções de número



Funções de uso Geral



NVL



NVL2



NULLIF



COALESCE

UEM

DMI

Funções Agregadas



COUNT



Retorna o total das linhas



MIN



Retorna o valor mínimo num conjunto de valores



MAX



Retorna o valor máximo num conjunto de valores



SUM



Retorna o somatório de um conjunto de valores



AVG

UEM

DMI

69

Sintaxe de Funções

Agregadas



SELECT [

_coluna

, ]

_{função_agregada}

(

_coluna

), …

FROM

tabela

[WHERE

condução

]

[GROUP BY

coluna

]

[HAVING

condução

]

[ORDER BY

coluna

];

UEM

DMI

Função COUNT

COUNT

(*) – Retorna o número total de linhas numa

tabela:

1.

SELECT COUNT(*) FROM emp

WHERE deptno=30;

Exibe o número total de linhas cujo

deptno

é 30

COUNT

(expr) – Retorna o total de linhas cujo valor de

expr é diferente de NULL

1.

SELECT COUNT(comm) FROM emp

WHERE deptno=20;

UEM

DMI

71

Função COUNT (Cont.)

Usando a palavra reservada DISTINCT:

COUNT (

DISTINCT

expr) – Retorna todos valores diferentes de

NULL para expr, sem no entanto repetir as ocorrências

Por exemplo, para listar todos departamentos que estão associados

à tabela emp, faremos:

SELECT COUNT(

DISTINCT

deptno) FROM emp;



DISTINCT elimina dados repetidos



Exibe cada número (deptno) que ocorre na tabela emp uma

única vez independentemente do número de vezes que possa

estar repetido na tabela.

UEM

DMI

Função AVG

1.

SELECT

AVG

(comm) FROM emp;



Funções agregadas ignoram o valor

NULL

2.

SELECT

AVG

(NVL(comm, 0)) FROM emp;



A função NVL obriga funções agregadas a

incluirem valor

NULL



No exemplo acima, a função NVL garante que

todo

NULL

encontrado seja substituído por 0.

AVG(COMM)

550

AVG(NVL(COMM,0))

UEM

DMI

73

Cláusula GROUP BY



Esta cláusula, divide as linhas na tabela em

grupos com uma característica comum. Podemos

a partir desta cláusula podemos obter informações

relativas a cada grupo.

SELECT

deptno

, AVG(sal)

FROM emp

GROUP BY

deptno

ORDER BY deptno;

O

ORDER BY

, permite visualizar os dados em ordem

crescente ou decrescente. Quando não está explícito se é

crescente ou decrescente a ordem

default

é crescente.

UEM

DMI

Cláusula GROUP BY (Cont.)

A coluna que é usada no

GROUP BY

, não

precisa aparecer na cláusula SELECT.

SELECT AVG (sal)

FROM emp

UEM

DMI

75

Cláusula GROUP BY (Cont.)

Usando o

GROUP BY

para mais de uma

coluna:

SELECT empno, deptno, job_id

FROM emp

GROUP BY

deptno, job_id;

UEM

DMI

Consultas Ilegais Usando

GROUP BY

Qualquer coluna ou expressão na expressão SELECT que não for

uma função agregada

DEVE

aparecer na cláusula GROUP

BY:

SELECT deptno, count(ename)

FROM emp;

SELECT deptno, count (ename)

*

Error at line 1:

ORA-00937: not a single-group group function

UEM

DMI

77

Consultas Ilegais Usando

GROUP BY (Cont.)

•

Não se pode usar a cláusula WHERE para grupos restritos

•

Usa-se a cláusula HAVING para grupos restritos

•

Não se pode usar funções agregadas na cláusula WHERE

SELECT deptno, AVG(sal)

FROM emp

WHERE AVG(sal) > 8000

GROUP BY deptno;

WHERE AVG(sal) > 8000

*

Error at line 3:

ORA-00934: group function is not allowed here

Erro cometido:

não se pode usar a cláusula WHERE para grupos restritos

UEM

DMI

Cláusula HAVING



Se acrescentarmos alguma condição nas funções

agregadas, temos de verificá-la com recurso à

cláusula

HAVING.

1.

SELECT deptno, MAX(sal) FROM emp

GROUP BY deptno

HAVING MAX(sal) > 3000;

2.

SELECT job, SUM(sal) FROM emp

WNERE job NOT LIKE ‘%REP%’

GROUP BY job

UEM

DMI

79

Restrições de Resultados de

Grupos na Cláusula HAVING

Quando usamos a cláusula HAVING, o Oracle Server restringe os

grupos da seguinte forma:

•

As linhas são agrupadas;

•

A função de grupos é aplicada;

•

Os grupos que satisfazem a condição na cláusula HAVING são

exibidas.

SELECT

coluna, funcao_agregada

FROM

tabela

[WHERE

condicao

]

[GROUP BY

expressao_de_grupo

]

[HAVING

condicao_de_grupo

]

[ORDER BY

coluna

]

UEM

DMI

Funções Agregadas

Compostas

O seguinte trecho, mostra o máximo salário

médio:

SELECT MAX (AVG(sal))

FROM emp

GROUP BY deptno;

UEM

DMI

81

Exercícios

1.

Para cada

Job

exiba: o salário máximo, mínimo,

total e média dos salários;

2.

Para saber o número total de empregados em

cada Job.

3.

Quantos empregados são Gestores? Use coluna

de mgr

4.

Qual é o valor da diferença entre o salário máximo

e mínimo?

5.

Qual é o

Job

cujo salário máximo é superior a

2000?

UEM

DMI

Soluções (tabelas)

JOB MAX(SAL) MIN(SAL) SUM(SAL) AVG(SAL)

ANALYST 3000 3000 6000 3000

CLERK 1300 800 4150 1037.5

MANAGER 2975 2450 8275 2758.33333

PRESIDENT 5000 5000 5000 5000

SALESMAN 1600 1250 5600 1400

UEM

DMI

83

Soluções (comandos)

1.

SELECT job, MAX(sal), MIN(sal), SUM(sal), AVG(sal)

FROM emp GROUP BY job;

2.

SELECT job, COUNT (*)

FROM emp GROUP BY job;

3.

SELECT COUNT(DISTINCT(mgr)) FROM emp;

4.

SELECT MAX(sal) - MIN(sal) FROM emp;

5.

SELECT job FROM emp

GROUP BY job

HAVING MAX(sal) > 2000;

UEM

DMI

Resumo



Funções agregadas



COUNT



MIN



MAX



SUM



AVG



Cláusulas

UEM

DMI

85

Sub Consultas (SubQueries)

Quem aufere um salário mais alto que o salário

de Abel?

Consulta principal:

Quais os nomes dos empregados que auferem um salário

maior que o salário de Abel?

Sub Consulta:

$$$?

Qual é o salário do Abel?

UEM

DMI

Sub Consultas (Cont.)

•

Como se efectuam?



Quando quisermos saber quantas pessoas

recebem salário acima da média



No começo, temos de conhecer o salário médio



A seguir, comparar cada salário com o salário médio



Quando quisermos saber o nome do chefe de um

determinado empregado



No começo, temos de conhecer o número do chefe do

referido empregado



A seguir, procurar o nome do chefe que ostenta esse

UEM

DMI

87

Sintaxe de Sub Consultas



SELECT lista_de_select

FROM tabela

WHERE expressao operador

(SELECT

lista_de_select

FROM

tabela

)

;



Sub consulta (consulta interior) é executada uma

vez antes da consulta principal (consulta exterior)



O resultado da sub consulta é usado pela consulta

principal

consulta exterior

consulta interior

UEM

DMI

Sub consulta

Consulta principal

Usando Sub Consultas

Para resolver a questão colocada no início, teremos de fazer o

seguinte:



SELECT ename FROM emp

WHERE sal >

(SELECT sal FROM emp

WHERE ename=´Abel´ );



SELECT ename FROM emp

WHERE sal >

(SELECT sal FROM emp

WHERE empno=7499);



Com este comando pretendemos conhecer os nomes dos

empregados auferem um salário superior ao do empregado

com empno=7499

UEM

DMI

89

Operadores de

Sub Consultas



Quando a sub consulta devolve várias linhas,

temos de usar os seguintes operadores:

Operador

significado

IN

igual a qualquer membro na lista

ANY

compara valor a cada valor devolvido pela sub consulta

ALL

compara valor a todo valor devolvido pela sub consulta

UEM

DMI

IN, ANY e ALL

1.

SELECT empno, ename, job, sal FROM emp

WHERE sal

IN

(SELECT sal FROM emp WHERE job=‘CLERK’);

2.

SELECT empno, ename, job, sal FROM emp

WHERE sal

< ANY

(SELECT sal FROM emp WHERE job=‘CLERK’);

3.

SELECT empno, ename, job, sal FROM emp

WHERE sal

> ALL

(SELECT sal FROM emp WHERE job=‘CLERK’);

SAL

800 1100 950 1300

EMPNO ENAME JOB SAL

7369 SMITH CLERK 800

7900 JAMES CLERK 950

7876 ADAMS CLERK 1100

7521 WARD SALESMAN 1250 7654 MARTIN SALESMAN 1250

EMPNO ENAME JOB SAL

7499 ALLEN SALESMAN 1600 7566 JONES MANAGER 2975 7698 BLAKE MANAGER 2850 7782 CLARK MANAGER 2450 7788 SCOTT ANALYST 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 1500

7902 FORD ANALYST 3000

EMPNO ENAME JOB SAL

7369 SMITH CLERK 800

7876 ADAMS CLERK 1100

7900 JAMES CLERK 950

7934 MILLER CLERK 1300

1

2

3

UEM

DMI

91

Junção De Tabelas



Uma junção é usada para visualizar

informação de várias tabelas

UEM

DMI

Junção de Várias Tabelas

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

7369 SMITH CLERK 7902 17-DEC-80 800 20

7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 19-APR-87 3000 20

7839 KING PRESIDENT 17-NOV-81 5000 10

7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30

7876 ADAMS CLERK 7788 23-MAY-87 1100 20

7900 JAMES CLERK 7698 03-DEC-81 950 30

7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

DEPTNO DNAME LOC

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

EMPNO DEPTNO DNAME

7369 20 RESEARCH 7499 30 SALES 7521 30 SALES 7566 20 RESEARCH 7654 30 SALES 7698 30 SALES 7782 10 ACCOUNTING 7788 20 RESEARCH 7839 10 ACCOUNTING 7844 30 SALES 7876 20 RESEARCH 7900 30 SALES

UEM

DMI

93

Juntar tabelas Que Usam

SQL:1999 sintaxe



SELECT tabela1.coluna, tabela2.coluna

FROM tabela1

[CROSS JOIN tabela2]

[NATURAL JOIN tabela2]

ou

[JOIN tabela2 USING nome_de_coluna]

ou

[JOIN tabela2 ON

(tabela1.nome_de_coluna=tabela2.nome_de_coluna)]

ou

[LEFT ou RIGHT ou FULL OUTER JOIN tabela2

ON

(tabela1.nome_de_coluna=tabela2.nome_de_coluna)]

UEM

DMI

CROSS JOIN



SELECT empno, dname

FROM emp

CROSS JOIN

dept;



SELECT empno, dname FROM emp, dept;



Cada linha da tabela EMP é cruzada com

todas as linhas da tabela DEPT



Por exemplo, se tabela EMP tiver 20 linhas e

UEM

DMI

95

Junção Natural (Natural

Join)



SELECT ename, deptno, dname

FROM emp NATURAL JOIN dept;



SELECT ename, deptno, dname

FROM emp, dept WHERE emp.deptno=dept.deptno;



NATURAL JOIN

faz a junção de tabelas baseado em colunas como o

mesmo nome e o mesmo domínio



Selecciona linhas das tabelas que tenham valores iguais nas suas colunas

correspondentes



A vantagem do NATURAL JOIN é que não precisa de especificar

explicitamente o nome de coluna

NOTA:

A junção Natural ocorre somente nas colunas que tenham o mesmo nome

e o mesmo de tipo de dados em ambas as tabelas. Se as colunas tiverem o

mesmo nome mas diferentes tipos de dados, a sintaxe causará ERRO!

UEM

DMI

USING JOIN



Se várias colunas tiverem o mesmo nome mas diferentes tipos

de dados, a cláusula NATURAL JOIN pode ser modificada pela

clásula USING, para especificar as colunas que devem ser

usadas para a equi-junção



Use a cláusula USING para ligar somente uma coluna quando

mais do que uma coincidem



Não use o nome duma tabela ou um pseudónimo nas colunas

referenciadas



As cláusulas NATURAL JOIN e USING são mutuamente

exclusivas



SELECT c.name , c.item , s.class

FROM customer c JOIN student s USING (id);



SELECT c.name, c.item, s.class

FROM customer c, student s WHERE c.id=s.id;

UEM

DMI

97

JOIN ON



Quando se quiser fazer a junção de tabelas a partir

de colunas com nomes diferentes mas com o

mesmo tipo de dados, usa-se a cláusula JOIN ON



SELECT c.name

，

c.addr

，

o.item_id

FROM customer c

JOIN

Order o

ON

c.cust_no = o.cust_id

；



SELECT c.name, c.addr, o.item_id

FROM customer c, order o

WHERE c.cust_no=o.cust_id;



Use JOIN ON, quando quiser especificar a coluna

de junção

UEM

DMI

OUTER JOIN



A junção de duas tabelas que retorna somente

linhas correspondentes é chamada INNER JOIN.



A junção entre duas tabelas que retornam o

resultado duma INNER JOIN bem como as linhas

não correspondentes da tabela à esquerda ou à

direita, é chamada OUTER JOIN



Tipos de OUTER JOIN

1.

LEFT OUTER JOIN

2.

RIGHT OUTER JOIN