Tema 2. Linguagem SQL basico - Parte2

1

Linguagem SQL 

(Avançado)

2

DISCIPLINA:

BASE DE DADOS 1

O docente:

Hélder MC Muianga

[email protected]

[email protected]

 (msn)

(+258) 82 47 27 160

UEM

DMI

CONTEÚDO:



LINGUAGEM SQL AVANÇADO –

PARTE 1

UEM

DMI

CREATE TABLE (Revisão)



Para criar tabelas, é necessário que o 

usuário tenha o privilégio de CREATE 

TABLE



Sintaxe



CREATE TABLE [schema.]tabela (

  coluna tipo_de_dado [

DEFAULT expr] [, …]

);



Na notação mínima, são especificados:



Nome de tabela



Nome de coluna, tipo de dado da coluna e 

tamanho de coluna

UEM

DMI

Exemplo de CREATE TABLE

(A nível da coluna)

CREATE TABLE

 empregado 

( empregado_id

NUMBER(6)

CONSTRAINT

 emp_empregado_pk 

PRIMARY

KEY

, empregado_nome

VARCHAR2(20) 

CONSTRAINT emp_emp_nome_nn NOT

NULL

, email

VARCHAR2(25)

CONSTRAINT emp_email_nn NOT NULL

CONSTRAINT emp_email_uk UNIQUE

, data_de_contrato

DATE DEFAULT

SYSDATE

, salario

NUMBER(8, 2)

CONSTRAINT emp_sal_ck CHECK

 (salario > 0)

, manajador_id

NUMBER(6)

, departamento_id

NUMBER(4)

CONSTRAINT emp_dept_fk REFERENCES

departamento (departamento_id)

); 

UEM

DMI

Exemplo de CREATE TABLE

 (A nível da tabela)

CREATE TABLE

 empregado 

( empregado_id

NUMBER(6),

 empregado_nome

VARCHAR2(20) 

CONSTRAINT emp_emp_nome_nn NOT

NULL,

email

VARCHAR2(25),

CONSTRAINT emp_email_nn NOT NULL,

data_de_contrato

DATE DEFAULT

SYSDATE,

salario

NUMBER(8, 2)

CONSTRAINT

 emp_sal_ck 

CHECK

(salario > 0)

,

gestor_id

NUMBER(6),

departamento_id

NUMBER(4), 

CONSTRAINT emp_empregado_pk PRIMARY

KEY

 (empregado_id),

CONSTRAINT emp_email_uk UNIQUE

 (email),

CONSTRAINT emp_dept_fk FOREIGN KEY (

departamento_id

)

REFERENCES

departamento 

(

departamento_id

)

UEM

DMI

Eliminação de tabela

DROP TABLE



Todos os dados e estrutura da tabela são 

apagados



Algumas transacções pendentes são 

confirmadas



Todos os índices são removidos



Todos as restrições são removidas



Não se pode fazer ROLLBACK, depois de 

fazer DROP TABLE



Sintaxe



DROP TABLE tabela;



Por exemplo



DROP TABLE emp;

UEM

DMI

View



View é uma tabela lógica que resultante da selecção de uma ou várias 

colunas de uma ou várias tabelas



Restringir acesso de dados



Prover independência de dados



Facilitar consulta complexa



Apresentar visões diferentes dos mesmos dados

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 2­Apr­81 2975 20

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

7698 BLAKE MANAGER 7839 1­May­81 2850 30

7782 CLARK MANAGER 7839 9­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 8­Sep­81 1500 0 30

7876 ADAMS CLERK 7788 23­May­87 1100 20

7900 JAMES CLERK 7698 3­Dec­81 950 30

7902 FORD ANALYST 7566 3­Dec­81 3000 20

7934 MILLER CLERK 7782 23­Jan­82 1300 10

7999 KAZUKI SALESMAN 13­Mar­06 500

EMPNO

ENAME

JOB

7698 BLAKE

MANAGER

7782 CLARK

MANAGER

7788 SCOTT

ANALYST

7839 KING

PRESIDENT

View

UEM

DMI

Criação de views



Uma view é criada  por meio duma consulta (query)



Sintaxe



CREATE [OR REPLACE] 

VIEW nome_de_view

[(outro_nome [, outro_nome]…)]

AS query

[

WITH READ ONLY [CONSTRAINT constrangimento]];



Por exemplo

1.

CREATE VIEW view_emp_10

AS SELECT empno, ename, sal 

      FROM emp 

      WHERE deptno=10;

2.

CREATE VIEW view_sal_10

AS SELECT empno numero_de_id, ename nome, sal*12 an_salario

      FROM emp

      WHERE deptno=10;

3.

CREATE OR REPLACE VIEW view_emp_10

(numero_de_id, nome, an_salario)

AS SELECT empno, ename || ‘  ‘ || job, sal, deptno

      FROM emp

      WHERE deptno=10;

UEM

DMI

Normas de uso de Views



Pode­se efectuar operações de DML (INSERT, 

UPDATE, DELETE) nas views.



Mas não se pode fazer algumas operações na view 

se contiver estes casos:



Não se pode fazer operações de DML numa view 

com opção WITH READ ONLY (excepto select)



Quando se apaga uma view, usa­se DROP VIEW



DROP VIEW nome_de_view;

apagar

modificar inserir

funções agregadas

×

×

×

cláusula de GROUP BY

×

×

×

DISTINCT

×

×

×

coluna foi definida por expressões

×

×

coluna de NOT NULL sem valores de

falta na tabela básica

×

UEM

DMI

Criação de views complexas

Views complexas, são aquelas que resultam da 

selecção de uma ou várias tabelas.



Exemplo

1.

CREATE VIEW view_dept_emp(emp_id, nome, 

depto_nome, depto_loc)

AS SELECT e.empno, e.ename, d.deptno, d.loc 

      FROM emp e, dept d 

      WHERE d.deptno=e.deptno;

2.

CREATE VIEW view_dept_emp(emp_id, nome, 

depto_nome, depto_loc)

AS SELECT e.empno, e.ename, d.deptno, d.loc 

      FROM emp e JOIN dept d 

      ON d.deptno=e.deptno;

UEM

DMI

Cláusula WITH CHECK 

OPTION

Esta cláusula garante que todas operações DML efectuadas sobre a view 

estarão dentro do domínio desta



Analise o seguinte exemplo

1.

CREATE or REPLACE VIEW view_emp_10 

AS SELECT empno, ename, deptno, sal 

      FROM emp 

      WHERE deptno=10;

 INSERT INTO view_emp_10(empno,ename,deptno,sal) VALUES (3,'a',20,222);

View created.



CREATE or REPLACE VIEW view_emp_10 

AS SELECT empno, ename, deptno, sal 

      FROM emp 

      WHERE deptno=10

WITH CHECK OPTION Constraint emp_vu10_ck;

INSERT INTO view_emp_10(empno,ename,deptno,sal) VALUES (3,'a',20,222);

ERROR at line 1:

ORA­01402: view WITH CHECK OPTION where­clause violation

UEM

DMI

Sequences



Uma sequence é um objecto da base de 

dados que cria uma sequência de valores 

numéricos inteiros



Pode gerar números exclusivos (sem ciclo)



Objecto partilhável

UEM

DMI

CREATE SEQUENCE e 

DROP SEQUENCE



Sintaxe



CREATE SEQUENCE sequence

[

INCREMENT BY n]

[

START WITH n]

[{

MAXVALUE n ou NOMAXVALUE}]

[{

MINVALUE n ou NOMINVALUE}]

[{

CYCLE ou NOCYCLE}]

[{

CACHE n ou NOCACHE}];



Por exemplo

1.

CREATE SEQUENCE dept_seq

INCREMENT BY 10

START WITH 120

MAXVALUE

9999

NOCACHE

NOCYCLE;



Para eliminar, use DROP SEQUENCE



DROP SEQUENCE dept_seq;

Até 10^27

Até ­10^26

Quando chegar 

MAXVALUE, 

recomeça.

Quantos bytes 

mantém para fazer o 

acesso rapidamente

UEM

DMI

Como usar Sequences



Usando NEXTVAL e CURRVAL



NEXTVAL devolve o valor seguinte da 

sequência



CURRVAL devolve o valor actual da sequência



Por exemplo

1.

INSERT INTO dept (deptno, dname, loc)

VALUES (dept_seq.NEXTVAL, ‘Support’, 

‘Maputo’);

2.

SELECT dept_seq.CURRVAL FROM dual;

UEM

DMI

Índices (Indexes)



Com índices pode­se procurar dados com 

mais rapidez



Criando índices



Automaticamente



Índices são criados quando se definem constraints do 

tipo PRIMARY KEY ou UNIQUE na criação de tabelas



Manualmente



Pode­se criar explicitamente índices e associá­los à 

uma coluna, para acelerar as consultas

UEM

DMI

CREATE INDEX



Sintaxe



CREATE INDEX índice

ON tabela (coluna [, coluna]…);



Por exemplo

1.

CREATE INDEX emp_ind

ON       emp(ename);



Para eliminar um índice usa­se DROP 

INDEX



DROP INDEX índice;

UEM

DMI

Quando criar índices?

Criam­se índices quando:

uma coluna contém uma extensiva gama de valores

uma coluna contém número grande de null  valores

uma ou mais colunas são frequentamente usadas na cláusula 

WHERE ou na condição JOIN

não se criam índices quando:

as colunas não são frequentemente usadas como condições 

na 

query

a tabela é pequena ou é esperado que a maioria das queries 

não abarquem mais de 2% a 4% dos registos da tabela

a tabela é actualizada com frequência

as colunas indexadas são referenciadas como parte de uma 

expressão

UEM

DMI

Sinónimos



É um 

nome alternativo para os objectos da 

base de dados



Vantagem



Uso de nomes simples para objectos da base de dados



Uso de nomes simples para objectos de outros usuários 

(renomeando a referência ao objecto de tais usuários­

schema)



Tipo de sinónimos



Sinónimo privado : o sinónimo que só é efectivo num 

schema.



Sinónimo público : o sinónimo que é afectivo para toda a 

base de dados Oracle

UEM

DMI

CREATE SYNONYM



Para criação de sinónimos o usuário deve ter 

permissão para tal



Sintaxe



CREATE [PUBLIC] SYNONYM nome_de_sinónimo

FOR nome_de_objecto;



Quando se usa PUBLIC, o sinónimo criado torna­se 

público



Por exemplo

1.

CREATE SYNONYM e_view

FOR emp_sal_view;



Para eliminar um sinónimo usa­se DROP 

SYNONYM



DROP SYNONYM nome_de_sinónimo;

UEM

DMI

Modificação de Objectos



Modificação de Objectos usa­se ALTER, mas 

alguns objectos não há declaração ALTER 

Objecto

Modificação

Tabela

ALTER TABLE

View

CREATE VIEW OR REPLACE

Sequence ALTER SEQUENCE

índice

Deve­se DROP e recria­se isso

Sinónimo

Deve­se DROP e recria­se isso

UEM

DMI

Alteração de Tabelas



Para alterar a estrutura de uma tabela, usa­se a cláusula ALTER 

TABLE para:



Adicionar uma nova coluna



Modificar uma coluna existente



Definir valor default para uma nova coluna



Eliminar uma coluna



Sintaxe



ALTER TABLE tabela

ADD (coluna tipo_de_dado [DEFAULT expr]

         [, coluna tipo_de_dado] …);



ALTER TABLE tabela

MODIFY (coluna tipo_de_dado [DEFAULT expr]

  [, coluna tipo_de_dado] …);



ALTER TABLE tabela

DROP (coluna);



Exemplo



ALTER TABLE dept

ADD (job_id VARCHAR2(9));



ALTER TABLE dept

MODIFY (job_id VARCHAR2(30));



ALTER TABLE dept

DROP COLUMN job_id;

UEM

DMI

Adicionar um Constraint



ALTER TABLE pode ser usado para:



Adicionar e eliminar constraints mas não se pode efectuar 

nenhuma modificação



Activar ou desactivar os constraints



Adicionar constraints NOT NULL usando a cláusula MODIFY



Sintaxe



ALTER TABLE nome_de_tabela

ADD [CONSTRAINT nome_de_constrangimento]

tipo (nome_de_coluna);



Por exemplo

1.

ALTER TABLE emp

MODIFY ename PRIMARY KEY;

2.

ALTER TABLE emp

ADD CONSTRAINT emp_mgr_fk

FOREIGN KEY (mgr)

REFERENCES emp2(empno);

UEM

DMI

Eliminação de Constraints



Exemplo

1.

ALTER TABLE emp

DROP CONSTRAINT emp_mgr_fk;

2.

ALTER TABLE dept

DROP PRIMARY KEY 

CASCADE;

3.

ALTER TABLE emp

DISABLE

 CONSTRAINT emp_dt_fk;

4.

ALTER TABLE emp

ENABLE

 CONSTRAINT emp_dt_fk;

5.

ALTER TABLE emp

DROP COLUMN empno CASCADE CONSTRAINTS;

Ao mesmo tempo, 

elimina FOREIGN KEY

caso não existir

Desactiva

Activa

UEM

DMI

Alteração de Sequences



Se chegar­se limite de MAXVALUE da sua 

sequence, para continuar para usar a sequence, 

modifica­se pelo uso de ALTER SEQUENCE  



Sintaxe 



ALTER SEQUENCE nome_de_sequence

    [

INCREMENT BY n]

    [{

MAXVALUE n ou NOMAXVALUE}]

    [{

MINVALUE n ou NOMINVALUE}]

    [{

CYCLE ou NOCYCLE}]

    [{

CACHE n ou NOCACHE}]



Exemplo

1.

ALTER SEQUENCE dept_deptid_seq

INCREMENT BY 20

MAXVALUE 999999

NOCACHE

NOCYCLE;

UEM

DMI

Exercícios



Conecte a base de dados Oracle usando o schema scott

1.

Crie índice para a coluna ename na tabela emp e elimine de seguida 

esse mesmo índice

2.

Crie uma view com as colunas empno, ename, sal*12 e dname e 

chame­a emp_sal_view. A coluna sal*12 deve usar o nome salario_an. 

Não deve listar os empregado que não tenham mgr.

3.

Exiba essa view

4.

Crie um sinónimo que se chame esv usando a view emp_sal_view

5.

Crie uma sequência que comece de 100 com valor de acréscimo igual 

a 10. Dê a sequência o nome de seq1

6.

Crie uma tabela com os seguintes campos: numero de desporto, nome 

de desporto, número identificador de treinador. Os valores do número 

de desporto são gerados pela sequência seq1 e o número identificador 

de treinador têm relação com o campo empno na tabela emp e chama­

se tabela desporto.

7.

Insira uma linha na tabela desporto

8.

Exiba o conteúdo da tabela desporto

UEM

DMI

Respostas

1

2

3

4

UEM

DMI

Respostas “cont.”

5

6

7

8

UEM

DMI

FLASHBACK



É usado para:



Recuperação de uma tabela eliminada 

Sintaxe



FLASHBACK TABLE tabela TO BEFORE 

DROP;



Exemplo

1.

FLASHBACK TABLE bonus TO BEFORE 

DROP;

UEM

DMI

Operação de várias 

cláusulas



Copiar linhas de outra tabela



Inserir várias tabelas



Funções de análise



Funções de data



DATE e TIMESTAMP



MERGE



Mudança de trajectória



Subquery de cláusula de FROM

UEM

DMI

Copiar linhas de outra tabela



Pode escrever a sua cláusula de INSERT 

com subquery



Sintaxe



INSERT INTO tabela [ coluna (, coluna) ] 

subquery;



Por exemplo



INSERT INTO bonus(ename, job, sal, comm)

SELECT ename, job, sal, comm FROM emp

WHERE sal > 2000;



Não use cláusula de VALUES

UEM

DMI

Inserir várias tabelas



Pode inserir várias tabelas ao mesmo tempo



Por exemplo

1.

CREATE TABLE bonus1 AS SELECT * FROM bonus WHERE 

1=0;

CREATE TABLE bonus2 AS SELECT * FROM bonus WHERE 

1=0;

2.

INSERT ALL 

INTO bonus1 (ename, job, sal) VALUES ('EDSON', 'SALES', 

1000)

INTO bonus2 (ename, job, sal) VALUES ('ZACA', 'MANAGER', 

3000)

SELECT * FROM bonus;

3.

INSERT ALL 

INTO bonus1 (ename, sal) VALUES (ename, sal)

INTO bonus2 (ename, job) VALUES (ename, job)

SELECT * FROM bonus;

Não é inserido nem uma linha. Responda de este equação é sempre falta

UEM

DMI

Actualizando duas colunas 

com um subquery



Quando actualizar job e sal colunas do empregado 

7369 para emparelhar trabalho do empregado 7499 

e salário do empregado 7521



Sintaxe



UPDATE tabela 

SET coluna=subquery [, coluna=subquery, …] 

[WHERE condição]



Por exemplo



UPDATE emp

SET job=(SELECT job FROM emp WHERE empno=7499),

sal=(SELECT sal FROM emp WHERE empno=7521)

WHERE empno=7369;

UEM

DMI

Exercício

1.

Crie uma tabela de CONTAGEM, tem coluna de 

NALUNO (PK), INGLES, PORTUGUES, 

MATEMATICA, FISICA, QUIMICA e DESENHO

2.

Insere linhas pela menos 5

3.

Crie tabela de CONT_ING, CONT_POR, CONT_MAT, 

CONT_FIS, CONT_QUI e CONT_DES que têm 

coluna de NALUNO e CONTAGEM

4.

Insere cada contagem para cada tabela de contagem

UEM

DMI

35

Resposta

NALUNO INGLES PORTUG_UES MATEMA_TICA FISICA QUIMICA DESENH_O

10 53 88 56 32 37 65 20 87 78 36 94 56 88 30 69 80 96 97 65 64 40 90 87 76 62 76 79 50 54 87 59 37 47 38 NALUNO CONTAG_EM 10 53 20 87 30 69 40 90 50 54 NALUNO CONTAG_EM 10 88 20 78 30 80 40 87 50 87 NALUNO CONTAG_EM 10 56 20 36 30 96 40 76 50 59 NALUNO CONTAG_EM 10 32 20 94 30 97 40 62 50 37 NALUNO CONTAG_EM 10 37 20 56 30 65 40 76 50 47 NALUNO CONTAG_EM 10 65 20 88 30 64 40 79 50 38

5

6­1 inglês

6­2 português

6­3matemánica

6­4 física

6­5 química

6­6 desenho

UEM

DMI

Resposta

CREATE TABLE CONTAGEM

(NALUNO

NUMBER(5)

,INGLES

NUMBER(3)

,PORTUGUES

NUMBER(3)

,MATEMATICA

NUMBER(3)

,FISICA

NUMBER(3)

,QUIMICA

NUMBER(3)

,DESENHO

NUMBER(3)

,PRIMARY KEY (NALUNO)

);

INSERT INTO contagem(naluno, ingles, portugues, matematica, fisica, quimica, desenho) 

VALUES( 50, 54, 87, 59, 37, 47, 38);

CREATE TABLE CONT_ING  

(NALUNO

NUMBER(5)

,CONTAGEM

NUMBER(3)

); …

INSERT ALL

INTO cont_ing VALUES (naluno, ingles)

INTO cont_por VALUES (naluno, portugues)

INTO cont_mat VALUES (naluno, matematica)

INTO cont_fis VALUES (naluno, fisica)

INTO cont_qui VALUES (naluno, quimica)

INTO cont_des VALUES (naluno, desenho)

SELECT naluno, ingles, portugues, matematica, fisica, quimica, desenho 

FROM contagem;

SELECT * FROM contagem;

SELECT * FROM cot_ing; …