Banco de Dados II – Aula 10

VIEWS

  • É um objeto lógico que se baseia em uma SELECT, armazenado no banco de dados;
  • Pode ser simples (apenas utilizando uma tabela) ou complexa (podendo ter vários relacionamentos e tipos de joins, grupos, funções de grupo, etc);
  • Podem ter colunas virtuais;
  • Pode fazer restrição de dados (não colocando a coluna desejada na SELECT)
  • Em Views simples, podemos executar comandos DML (insert, update, delete);
  • Em Views complexas, na maioria dos casos, não é possível executar comandos DML (possuem funções, grupos);
  • Recomendado fazer toda a SELECT primeiramente para depois ser encapsulada na VIEW;
  • Sintaxe: CREATE [OR REPLACE] VIEW nome_da_view AS SELECT …
  • Quando utilizado o REPLACE, caso já tenha uma VIEW com o mesmo nome, a mesma irá ser sobrescrita;
  • Com o parâmetro FORCE, pode-se criar a VIEW sem ter necessariamente as tabelas físicas no banco de dados. (na execução, irá dar erro);
  • Com o parâmetro NOFORCE (padrão), pode-se criar a VIEW com tabelas existentes no banco de dados;
  • Com o parâmetro WITH CHECK OPTION, apenas as linhas retornadas na VIEW podem ser alteradas;
  • Com o parâmetro WITH READ ONLY, somente pode ser executada a VIEW sem a possibilidade de executar comandos DML;

EXEMPLOS:

CREATE OR REPLACE VIEW VW_SALARIO_PROG AS
SELECT  EMPLOYEE_ID,
        FIRST_NAME,
        LAST_NAME,
        SALARY * 13 AS SAL_CLT_ANUAL
FROM    EMPLOYEES
WHERE   JOB_ID = 'IT_PROG';

–MOSTRANDO A ESTRUTURA

DESC VW_SALARIO_PROG;

–EXECUTANDO A VIEW

SELECT *
FROM VW_SALARIO_PROG;

 

EXEMPLO 2

CREATE OR REPLACE VIEW VW_SALARIO_PROG (CODIGO,NOME,SOBRENOME,SALARIO_ANUAL) AS
SELECT  EMPLOYEE_ID,
        FIRST_NAME,
        LAST_NAME,
        SALARY * 13 AS SAL_CLT_ANUAL
FROM    EMPLOYEES
WHERE   JOB_ID = 'IT_PROG';

 

VIEWS COMPLEXAS

  • As VIEWS complexas geralmente possuem relacionamento com duas ou mais tabelas (Compostas de queries complexas);
  • Na maioria das vezes, não é possível executar instruções DML;
  • Quando a select possui funções de grupo, GROUP BY, DISTINCT, ROWNUM, Expressões (colunas virtuais), colunas NOT NULL não retornadas na VIEW, NÃO podemos executar comandos DML;
  • O parâmetro WITH CHECK OPTION é possível executar instruções INSERT e UPDATE, somente com as linhas que a view pode retornar;
  • Também serve para evitar que os dados seja deletados da tabela, sendo somente deletados pela VIEW que possui o WITH CHECK OPTION;
  • O parâmetro WITH READ ONLY cria uma VIEW que não será possível executar nenhuma instrução DML, sendo somente executada para leitura de dados;
  • DROP VIEW faz a eliminação da view do banco de dados;

EXEMPLO:

CREATE VIEW SALARIOS_FUNC(FUNCAO, MIN_SALARIO, MAX_SALARIO, MED_SALARIO)
AS
SELECT B.JOB_TITLE,
       MIN(A.SALARY),
       MAX(A.SALARY),
       AVG(A.SALARY)
FROM   EMPLOYEES A
       NATURAL JOIN JOBS B
GROUP BY B.JOB_TITLE;

 
SELECT *
FROM SALARIOS_FUNC;

SELECT * FROM DEPARTMENTS;

CREATE OR REPLACE VIEW VW_DEPTO_60
AS
SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 60
WITH CHECK OPTION CONSTRAINT CK_DEPTO_60;


SELECT *
FROM VW_DEPTO_60;


--IRA ATUALIZAR
UPDATE VW_DEPTO_60
SET SALARY = SALARY + 1
WHERE DEPARTMENT_ID = 60;

SELECT * FROM VW_DEPTO_60;

 

--NAO IRÁ ATUALIZAR
UPDATE VW_DEPTO_60
SET DEPARTMENT_ID = 20
WHERE EMPLOYEE_ID = 103;

 

CREATE OR REPLACE VIEW VW_DEPTO_20 (ID, NOME, CARGO)
AS
SELECT EMPLOYEE_ID,
       FIRST_NAME,
       JOB_ID
FROM   EMPLOYEES
WHERE  DEPARTMENT_ID = 20
WITH READ ONLY;


SELECT *
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 20;


DELETE FROM VW_DEPTO_20
WHERE ID = 202;

 

 

ÍNDICES

  • Índice serve para que dados sejam organizados para melhorar o processo de procura em tabelas;
  • É um dos objetos do banco de dados, que pode ser criado pelo usuário ou pelo Oracle;
  • Para campos do tipo Primary Key ou Unique, as chaves são criadas automaticamente pelo Oracle;
  • Os índices ocupam mais espaço no banco de dados, sendo marcadores que permitem que os dados sejam acessados rapidamente;
  • Pode-se criar ou eliminar índices em uma tabela a qualquer momento;
  • Quando uma tabela é excluída, todos os índices dessa tabela também é excluída;
  • Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
  • Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
  • Os índices exclusivos são criados exclusivamente pelo Oracle, quando definimos as colunas PRIMARY KEY ou UNIQUE. Podem ser criados manualmente, mas é recomendado que o Oracle faça esse gerenciamento;
  • Índices NÃO EXCLUSIVOS são criados pelos usuários, a qual tem a finalidade de aumentar a performance das consultas;
  • Um índice pode ter mais de uma coluna;
  • A ordem das colunas influenciam na performance do retorno das linhas;
  • A utilização correta dos índices influencia também na performance de retorno das linhas (duas ou mais colunas);

SINTAXE:

CREATE INDEX NOME_INDICE ON NOME_TABELA(COLUNA1, ...);

DROP INDEX NOME_INDICE;

 

  • Aconselhável criar um índice quando:
    • Quando NÃO existirem muitos valores nulos na coluna consultada;
    • Quando as colunas forem muito utilizadas na cláusula WHERE;
    • Quando o retorno de registros representa em torno de 2% a 4% (para tabelas com grande quantidade de registros);
    • Colunas que possuem faixas de valores, como datas (que são utilizadas no WHERE);
  • Aconselhável a NÃO criar um índice quando:
    • Quando a tabela sofre comandos DML frequentemente (por exemplo, quando registros são inseridos, todos os índices da tabela também precisarão ser atualizados);
    • Tabelas com poucos registros;
    • Quando o retorno de registros na maioria das consultas equivalerem a menos de 4% dos registros;
  • Sempre que criar índices, é necessário avaliar se haverá mais consultas ou mais inserções/atualizações na tabela;

Exemplo:

CREATE INDEX IDX_PRIMEIRONOME ON EMPLOYEES(FIRST_NAME);
SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Valli';

DROP INDEX IDX_PRIMEIRONOME;

CREATE INDEX IDX_PRIMEIROSEGUNDONOME ON EMPLOYEES (FIRST_NAME,LAST_NAME);

SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Bruce';


SELECT *
FROM EMPLOYEES
WHERE FIRST_NAME = 'Bruce'
AND LAST_NAME = 'Ernst';
 

DROP INDEX IDX_PRIMEIROSEGUNDONOME;

 

SEQUENCES

  • Sequence é um dos objetos utilizados para armazenamento de números sequenciais em uma determinada ordem definida na sua criação;
  • Geralmente, é utilizado na criação de PRIMARY KEYS, em número sequenciais;
  • A utilização desse recurso garante que as chaves primárias não se repetirão;
  • É um objeto independente de qualquer tabela;
  • Geralmente, é associado a uma tabela para seguir uma sequencia, porém nada impede de ser utilizado em mais de uma tabela;
  • A sintaxe de criação da SEQUENCE é:
CREATE SEQUENCE NOME_SEQUENCE INCREMENT BY QUANTIDADE

[MAXVALUE VALOR_MAXIMO]
[MINVALUE VALOR_MINIMO]
[CYCLE]
[CACHE QUANTIDADE];
  • O incremento padrão é de 1 em 1;
  • O parâmetro CYCLE é utilizado quando a numeração chega ao valor máximo (maxvalue) e o número é reiniciado novamente;
  • O padrão na criação da SEQUENCE é NOCYCLE;
  • O parâmetro CACHE já define uma determinada quantidade de números reservados, para que seja mais rápido a utilização das sequencias (mais performance). O padrão é quantidade de 20;
  • Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas NEXVAL, que retorna o próximo número disponível da sequencia; Mesmo que tenha várias sessões diferentes utilizando a mesma sequence (ainda em transação), o valor nunca se repete;
  • Quando utilizamos SEQUENCES, podemos utilizar as pseudocolunas CURRVAL, que retorna o valor atual da sequence daquela seção; Só irá funcionar depois de ter utilizado o NEXTVAL na mesma sessão;
  • O Oracle não sabe qual é o valor corrente da sessão antes de ter executado o NEXTVAL;
  • Para se utilizar o NEXTVAL e CURRVAL:
    • Pode ser utilizada em uma SELECT, a menos que ela seja uma subquery;
    • Pode ser utilizada no comando VALUES do INSERT;
    • Pode ser utilizada no comando SET do UPDATE;
    • No SELECT de uma subquery para realizar um INSERT;
  • NÃO se pode utilizar o NEXTVAL e CURRVAL:
    • Em um SELECT em uma VIEW;
    • Em um SELECT, com DISTINCT, ORDER BY, GROUP BY ou HAVING;
    • Em uma coluna definida com o valor DEFAULT;
    • Em uma subquery em um DELETE, UPDATE ou em um SELECT;
  • Quando utilizamos o parâmetro CACHE QUANTIDADE, o Oracle coloca em cache essa quantidade para que se tenha mais performance na utilização da sequence. O padrão é com a quantidade de 20;
  • É indicado quando se tem tabelas que sofram grande quantidade de inserções de dados;
  • Depois que a sequence é gerada, o numero não é reaproveitado.
  • As sequences são geradas independentes que a transação termine com COMMIT ou ROLLBACK;
  • Falhas no sistema do Oracle também podem perder os valores que estão no cache;
  • Quando utilizamos uma sequence para várias tabelas, os números não irão ser sequencias para as tabelas utilizadas;
  • Podemos utilizar o comando ALTER e DROP para se fazer alteração e exclusão da sequence respectivamente;
  • Quando alteramos uma sequence, apenas os próximos números são alterados;
  • Não é possível alterar o START WITH de uma sequence, porém pode-se dar drop e create novamente;
  • Apenas quem tem permissão de DROP ANY SEQUENCE e o dono podem alterar as sequences;

EXEMPLOS:

CREATE TABLE TESTE_SEQUENCIA (
  CODIGO NUMBER(10) NOT NULL PRIMARY KEY,
  NOME   VARCHAR(30)
);

DESC TESTE_SEQUENCIA;

SELECT * FROM TESTE_SEQUENCIA;

CREATE SEQUENCE SQ_TESTE_01
INCREMENT BY 10
START WITH 100
MAXVALUE 300
NOCACHE
NOCYCLE;

 

–UTILIZANDO O NEXVAL

INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME)
VALUES (SQ_TESTE_01.NEXTVAL, 'SAITO');

--VERIFICANDO O VALOR INSERIDO NA TABELA
SELECT SQ_TESTE_01.CURRVAL FROM DUAL;
SELECT * FROM TESTE_SEQUENCIA;

INSERT INTO TESTE_SEQUENCIA (CODIGO, NOME)
VALUES (SQ_TESTE_01.NEXTVAL, 'ANCHIETA');

--VERIFICANDO O VALOR DA SEQUENCE
SELECT SQ_TESTE_01.CURRVAL FROM DUAL;

--ELIMITANDO UMA SEQUENCE
DROP SEQUENCE SQ_TESTE_01;

 

SINONIMOS

  • SINONIMO é um dos objetos utilizados para se fazer referencia a outro objeto. Geralmente, grande nomes de tabelas podem ser referenciadas com um nome menor ou referencia a objetos de outros usuários podem ser feitos acessos mais facilmente;
  • Sinônimo público pode ser acesso por qualquer usuário e o sinônimo privado pode ser acesso somente pelo seu criador;
  • Os sinônimos não podem acessar objetos que estão dentro de uma PACKAGE;
  • Para sinônimos privados, o nome deve ser único, diferente de outros objetos deste mesmo usuário;

SINTAXE:

CREATE SYNONYM NOME_SINONIMO
FOR NOME_OBJETO

DROP SYNONYM NOME_SINONIMO

EXEMPLOS:

--com erro, pois o usuário hr nao terá privilégio
CREATE PUBLIC SYNONYM TESTE_SIN FOR TESTE_TABELA ;

--excluindo o sinonimo publico
DROP PUBLIC SYNONYM TESTE_SIN;

--criacao do sinonimo privado
CREATE SYNONYM TESTE_SIN FOR TESTE_TABELA;

SELECT * FROM TESTE_SIN;

--excluindo do sinonimo
DROP SYNONYM TESTE_SIN;

 

 

Exercícios

 

  1. Crie uma View chamada VW_EMPREGADOS_DEPTOS, que mostre os dados da tabela employees, tendo os campos código, nome e sobrenome concatenado (espaço entre eles), e email, onde os JOB_IDS sejam IT_PROG e PU_CLERK;
  1. Prove se é possível fazer inserção de dados através da view criada no exercício 1; Explique o porque de conseguir ou não conseguir;
  1. Crie uma view chamada VW_EMPREGADOSPORTEMPO, CONTENDO o código do empregado, nome, data de admissão que estejam admitidos entre os damos de 2003 e 2005. Crie com o parâmetro WITH CHECK OPTION;
  1. Insira 3 linhas de dados pela view criada no exercício 3, e explique se você conseguiu inserir ou não esses dados;
  1. Crie uma view chamada EMPREGADOS_DEP, que utilize uma tabela com a seguinte estrutura: DEPENDENTES(EMPLOYEE_ID NUMBER(6), COD_DEP NUMBER(2), NOME_DEP VARCHAR(30), DATA_NASC DATE), onde EMPLOYEE_ID E COD_DEP são chaves primárias. A visão deve conter o código de dependente, o nome e a data de nascimento do dependente. Atenção: A tabela ainda não existe. NÃO CRIE A TABELA. Utilize o parâmetro correto para se fazer a view;
  1. Crie uma sequence chamada SQ_NUMERO_DEP, iniciando pelo numero 10, incremento de 5 em 5, tamanho mínimo 10, tamanho máximo de 500, com 30 números em cache;
  1. Crie a tabela dependentes do exercício 5, e faca a inserção de 5 dependentes, utilizando a sequence do exercício 6;
  1. Crie um índice chamado IDX_DEP_NOME na tabela dependentes, no campo NOME_DEP;

 

Anúncios

Banco de Dados II – Aula 09

Criando uma table space e usuário no Oracle 11 XE

Necessário entrar com a permissão de system, ou admin diretamente pelo SQL Plus.

Pode-se salvar o arquivo diretamente no diretório home do usuário oracle e executar o script através do SQL Plus*

create tablespace banco1 datafile '/u01/app/oracle/oradata/XE/banco1.dbf' size 10m autoextend on next 5m maxsize 50m online permanent extent management local autoallocate segment space management auto;

create user banco1 identified by banco1 default tablespace banco1 temporary tablespace temp;

grant create session, connect, resource to banco1;

alter user banco1 quota unlimited on banco1; 

connect banco1/banco1;

Até o momento, foi criado a table space chamada de banco1, um usuário banco1 com a senha banco1 e dados permissões de acesso ao usuário banco1

Após a conexão, podemos criar a as tabelas abaixo, fazendo as inserções de dados abaixo como exemplo:

CREATE TABLE DEPTO (
  CDDEPTO CHAR(02) PRIMARY KEY,
  NMDEPTO VARCHAR(30),
  RAMAL NUMBER(3)
);

CREATE TABLE CARGO (
  CDCARGO CHAR(02) PRIMARY KEY,
  NMCARGO VARCHAR(30),
  VRSALARIO NUMBER(8,2)
);

CREATE TABLE FUNCIONARIO (
  NRMATRIC NUMBER(04) PRIMARY KEY,
  NMFUNC   VARCHAR(30),
  DTADM    DATE,
  SEXO     CHAR(01),
  CDCARGO  CHAR(02),
  CDDEPTO  CHAR(02),
  CONSTRAINT FK_FUNC_CDCARGO FOREIGN KEY(CDCARGO) REFERENCES CARGO(CDCARGO),
  CONSTRAINT FK_FUNC_CDDEPTO FOREIGN KEY(CDDEPTO) REFERENCES DEPTO(CDDEPTO)
);


INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C1','COZINHEIRA',350);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C3','AUX ESCRITORIO',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C7','VIGIA',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C2','MECANICO',750);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C5','GERENTE',2300);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C4','ESCRITURARIO',600);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C8','PROGRAMADOR',NULL);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C9','ANALISTA_DE_SISTEMAS',2100);
COMMIT;


INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D1','ADMINISTRACAO',221);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D2','OFICINA',235);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D3','SERVICOS GERAIS',243);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D4','VENDAS',258);
COMMIT;


INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1001,'JOAO SAMPAIO','10-AUG-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1004,'LUCIO TORRES','02-MAR-94','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1034,'ROBERTO PEREIRA','23-MAY-92','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1021,'JOSE NOGUEIRA','10-NOV-94','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1029,'RUTH DE SOUZA','05-JAN-93','F','C1','D3');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1095,'MARIA DA SILVA','03-SEP-92','F','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1023,'LUIZ DE ALMEIDA','12-JAN-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1042,'PEDRO PINHEIRO','29-JUL-94','M','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1048,'ANA SILVEIRA','01-JUN-93','F','C5','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) 
	VALUES (1015,'PAULO RODRIGUES','17-AUG-92','M','C2','D2');
COMMIT;

OBJETOS DO ORACLE

TABELA: tem a principal função armazenar dados;

VIEW: objeto que encapsula uma instrução select, podendo ter uma ou mais tabelas;

SEQUENCE: Gera uma sequencia numérica;

INDICE: Utilizado para melhorar a performance em consultas;

SINONIMO: apelido para objetos de dados;

TABELAS

É possível fazer a criação online;

Após ter sido criada, é possível modificar a sua estrutura;

Importante ter uma estimativa de tamanho para evitar que o banco de dados trave;

Nomes devem ter até 30 caracteres, começando com uma letra;

Nomes das tabelas podem ser: a-z, A-Z, 0-9, _ (underline);

Os caracteres # e $ podem também serem utilizados em nomes de tabelas, porém não recomendados pela Oracle;

É possível ter nomes iguais de objetos desde que sejam de usuários diferentes;

Não se deve utilizar palavras reservadas do Oracle (CREATE, TABLE, ALTER, USER, INSERT, etc);

Em tabelas, não há case-sensitive;

CREATE TABLE

Para se criar tabelas, é necessário que o usuário conectado tenha privilégios ;

CREATE TABLE CLIENTES (
  COD_CLI NUMBER(5),
  NOME_CLI VARCHAR2(30)
);

TIPOS DE DADOS

As colunas de uma tabela precisa ser definida com um tipo de dados, pois será tratado posteriormente pelo banco de dados;

VARCHAR2: utilizado para armazenar tipos de caracteres alfa-numéricos (aceitando letras e números), podendo ser definido de 1 a 4000 caracteres; A quantidade de bytes utilizada é da quantidade de caracteres + 1 (especifica o tamanho da string)

CHAR: permite armazenar caracteres alfa-numéricos, podendo ser definido de 1 a 2000 caracteres, com tamanho fixo (exemplos: CPF, CEP, CNPJ);

NUMBER: permite armazenar números inteiros ou decimais (precisão e escala m,n), que varia de 1 a 38. Exemplo NUMBER(10,2), pode armazenar 8 inteiros e 2 decimais;

DATE: permite armazenar data e hora que pode variar de 01/01/4712 AC até 31/12/9999 DC;

LONG: permite armazenar caracteres de até 2 GB (evite usar LONG – somente para compatibilidades passadas). Utilize LOB;

CLOB: permite armazenar caracteres de até 4 GB;

RAW(tamanho): permite armazenar dados binários brutos;

LONG RAW: armazena dados binários de tamanho variável de até 2 GB;

BLOB: Dados binários de até 4 GB;

BFILE: Armazena arquivos binários em arquivos externos ao banco de dados de até 4 GB;

ROWID: permite armazenar o endereço físico exclusivo de uma linha em uma tabela correspondente. Toda a tabela tem um ROWID, controlado pelo ORACLE. Pode-se acessar executando SELECT ROWID;

LONG: O tipo de dados LONG não pode ser copiado para outra tabela através de uma subquery; A coluna LONG não pode ser incluída no GROUP BY ou ORDER BY; É limitado a 1 coluna LONG por tabela; Não pode ser definida como CONSTRAINTS; Utilize CLOB em vez de LONG;

TIMESTAMP: É uma extensão do tipo DATE, armazenando até o segundo e fração de segundos;Pode especificar o fuso horário;
Exemplo:
TIMESTAMP WITH LOCAL TIME ZONE;
TIMESTAMP WITH TIME ZONE;

INTERVAL YEAR TO MONTH: É usado para armazenar diferença entre dois valores de datas/horários, levando-se em conta somente o mês e o ano;
Exemplo:
INTERVAL ’12-5’ YEAR(2) TO MONTH;
Grava um intervalo de doze anos e cinco meses;

INTERVAL YEAR TO MONTH
Exemplo:
INTERVAL ’231’ MONTH(2);
Grava um intervalo de 231;

INTERVAL ’543’ YEAR; Retorna erro, pois não foi definido o ano e o padrão para ano são de 2 dígitos;

INTERVAL DAY TO SECOND: armazena um período em dias, horas, minutos e segundos
Exemplo:
INTERVAL ’2 3:20:10.222’ DAY TO SECOND(3);
Grava 2 dias, 3 horas, 20 minutos, 10 segundos e 222 milhares de segundo;

INTERVAL ’7 5:12’ DAY TO MINUTE;
Grava 7 dias, 5 horas e 12 minutos;
Exemplo:
INTERVAL ’235 5 DAY(3) TO HOUR;
Grava 235 dias e 5 horas;

INTERVAL ’11:12:10.2222222’ HOUR TO SECOND(7);
Grava 11 horas, 12 minutos e 10,2222222 segundos;

EXEMPLOS DE CRIAÇÃOD DE TABELA USANDO OS TIPOS DE DADOS

CREATE TABLE TESTE_DADOS (
  CODIGO      NUMBER(10),
  NOME        VARCHAR(30),
  CEP         CHAR(8),
  NASCIMENTO DATE,
  SALARIO     NUMBER(10,2)
);

INSERT INTO TESTE_DADOS (CODIGO, NOME, CEP, NASCIMENTO, SALARIO)
VALUES
(1,'TESTE DE NOME','12548963',TO_DATE('07/15/2010','MM/DD/YYYY'),1501.45);

SELECT * FROM TESTE_DADOS;

CREATE TABLE TESTE_TABELA (
  HORA_ATUAL TIMESTAMP(7)
);

INSERT INTO TESTE_TABELA(HORA_ATUAL)
VALUES (SYSDATE);

SELECT * FROM TESTE_TABELA;

CREATE TABLE TABELA_INTERVALOS(
  INTERVALO_ANO INTERVAL YEAR(3) TO MONTH,
  INTERVALO_DIA INTERVAL DAY(3) TO SECOND
);

INSERT INTO TABELA_INTERVALOS(INTERVALO_ANO, INTERVALO_DIA)
VALUES (INTERVAL '120' MONTH(3),
        INTERVAL '180' DAY(3)
);

SELECT INTERVALO_ANO, INTERVALO_DIA
FROM TABELA_INTERVALOS;

SELECT TO_CHAR(SYSDATE+INTERVALO_ANO,'DD-MON-YYYY'),
               SYSDATE+INTERVALO_DIA AS intervalo_dia
FROM TABELA_INTERVALOS;

CONSTRAINTS

São regras que são impostas no nível de tabela (ou coluna), que no geral, impedem a inserção, atualização e deleção de dados indevidos;

As mais importantes são: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK;

Sempre que possível, nomeie as CONSTRAINS. Caso não, o ORACLE dá o nome à constraint de “SYS_…”;

Aconselhável criar as constraints logo na criação da tabela, porém podem ser criadas posteriormente;

Podem ser definidas em nível de tabela ou coluna;

CONSTRAINT NOT NULL

Serve para que uma determinada coluna não tenha valores nulos;
Por padrão, as colunas são NULL;
EXEMPLO:
CREATE TABLE NOME_TABELA(
NOME_COL TIPO_COL NOT NULL
);

CONSTRAINT UNIQUE

Serve para que uma determinada coluna (ou colunas) tenha valores únicos, podendo ter valores nulos;

As PRIMARY KEYS implicitamente são UNIQUE;

CONSTRAINT PRIMARY KEY

Serve para que uma coluna (ou colunas) tenham valores diferente entre tuplas, sendo valores únicos e não nulos;
É limitada a ser criado uma única PK por tabela, que pode ter várias colunas (todas as colunas precisam ser não nulas);
É automaticamente criado um índice, para melhor pesquisar os registros;

CONSTRAINT FOREIGN KEY

Serve para garantir a integridade dos relacionamentos entre as tabelas;
Para ser criada, é necessário ter uma PK ou UNIQUE de outra tabela;
Pode-se utilizar os comandos abaixo para se fazer exclusão de dados em cascata:
ON DELETE CASCADE;
ON DELETE SET NULL;

CONSTRAINT CHECK

Serve para fazer a checagem de um determinado dado antes da ação na tabela;
Algumas das expressões NÃO podem ser utilizadas com CHECK: SYSDATE, UID, USER e USERENV, ou a pseudocolunas como: CURRVAL, NEXTVAL, LEVEL e ROWNUM e consultas que fazem referencia a outros valores em outra linhas;

EXEMPLOS:

–nivel coluna

CREATE TABLE EMPREGADOS (
COD NUMBER(6) CONSTRAINT COD_EMP_PK PRIMARY KEY,
NOME VARCHAR2(30)
);

EXEMPLO:

–nivel tabela

CREATE TABLE EMPREGADOS (
COD NUMBER(6),
NOME VARCHAR2(30),
CONSTRAINT COD_EMP_PK PRIMARY KEY(COD)
);


CREATE TABLE TABELA_CONSTRAINTS (
ID NUMBER(10),
COD_EMP NUMBER(10),
NAO_NULO NUMBER(10) NOT NULL,
COLUNA_UNICA DATE,
COLUNA_CHECK NUMBER(10,2),
CONSTRAINT ID_PK PRIMARY KEY(ID),
CONSTRAINT UK_UNIQUE01 UNIQUE(COLUNA_UNICA),
CONSTRAINT FK_COD_EMP FOREIGN KEY(COD_EMP)
REFERENCES EMPLOYEES(EMPLOYEE_ID) ON DELETE CASCADE,
CONSTRAINT CK_COL01 CHECK (COLUNA_CHECK > 10)
);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, NULL, NULL,10);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, 10, NULL,10);

INSERT INTO TABELA_CONSTRAINTS VALUES (10,100, 3,TO_DATE('15/10/2015','DD/MM/YYYY') ,11);

SELECT * FROM TABELA_CONSTRAINTS;

CONSTRAINTS DEFAULT

Define um valor padrão de uma coluna quando não for especificado nenhum valor (null implícito);
Pode ser usado funções de linhas como SYSDATE e o USER;
Não pode usar o nome de outra coluna como referencia;
O valor padrão precisa ter o mesmo tipo da coluna;

EXEMPLO DE DEFAULT

CREATE TABLE TESTE_DEFAULT (
ID NUMBER(10),
DATA DATE DEFAULT SYSDATE
);

INSERT INTO TESTE_DEFAULT (ID) VALUES (1);
INSERT INTO TESTE_DEFAULT (ID) VALUES (2);
INSERT INTO TESTE_DEFAULT (ID) VALUES (3);
INSERT INTO TESTE_DEFAULT (ID) VALUES (4);
INSERT INTO TESTE_DEFAULT (ID) VALUES (5);

SELECT * FROM TESTE_DEFAULT

CRIANDO UMA TABELA COM UMA SUBQUERY

Serve para criar rapidamente uma tabela com dados selecionado pela query;
Quando se tem expressões, é obrigatório o uso de alias;

EXEMPLO DE CREATE SELECT

CREATE TABLE TESTE_SUBSELECT AS
SELECT EMPLOYEE_ID, FIRST_NAME, HIRE_DATE, SALARY * 12 AS SAL
FROM EMPLOYEES
WHERE DEPARTMENT_ID = 90;

DESC TESTE_SUBSELECT;

ALTER TABLE

Serve para fazer manutenção em tabelas já existentes, podendo modificar colunas, criar novas colunas, eliminar colunas, adicionar valor default às colunas;

EXEMPLO ALTER TABLE

ALTER TABLE TESTE_DEFAULT
ADD NOME_USER VARCHAR2(30);

UPDATE TESTE_DEFAULT SET NOME_USER = USER;

SELECT * FROM TESTE_DEFAULT;

ALTER TABLE TESTE_DEFAULT
MODIFY NOME_USER VARCHAR2(20) NOT NULL;

ALTER TABLE TESTE_DEFAULT
RENAME COLUMN NOME_USER TO NOME_USER2;

ALTER TABLE TESTE_DEFAULT
RENAME TO TESTE_DEFAULT2

SELECT * FROM TESTE_DEFAULT2

DROP TABLE

Serve para eliminar uma tabela com todos os seus dados e índices;
Após ser executado, transações pendentes são submetidos a um COMMIT, efetivando as transações;

EXEMPLO DE DROP TABLE

DROP TABLE TESTE_DEFAULT2

EXERCICIOS

Faça um script que contenha a criação de uma tablespace (schema), usuário para o schema criado, associação do usuário com o schema, chamando a tablespace de banco999 e o usuário de banco999, fazendo com que o usuário banco999 seja dono de outros objetos criados;

Crie os seguites objetos dentro desse schema:

ScreenHunter_519 Apr. 23 14.00

Posteriormente faça as seguintes inserções de dados:

ScreenHunter_520 Apr. 23 14.00

.

Banco de Dados II – Aula 06

SUBQUERIES

  • Subqueries são divisões de uma instrução SELECT, a qual estarão separados entre parentes;
  • Servem geralmente para resolver problemas que teriam que ser feitas com 2 consultas;
  • Também utilizada quando um resultado encontrado é base de condição para uma outra consulta;
  • A ordem de execução da SUBQUERY depende de como ela é escrita;
  • Para as SUBQUERIES mais simples, são executadas as mais internas para depois ser executado a mais externa. Esse tipo de subquerie também é conhecida como NÃO-CORRELACIONADA;
  • Há também SUBQUERIES quer dependem tanto da query externa quando interna para ser executada.
  • É quando existe um relacionamento entre as QUERIES, ou seja, para cada linha da query mais externa, depende do resultado de uma linha (ou mais) de uma query mais interna. É também chamada de CORRELACIONADA;
  • Não é necessário utilizar order by em queries mais internas;
  • As subconsultas devem estar entre parênteses;
  • Tome cuidado com subconsultas que retornam várias linhas utilizando operadores que suporte somente uma única linha;
  • Exemplo de um problema que exigiria 2 consultas:
    • Qual(is) funcionário(s) que ganham igual ou mais que o funcionário Bruce do departamento 60?
SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     DEPARTMENT_ID = 60;
  • No método tradicional, primeiro precisamos saber o quanto o funcionário Bruce ganha:
SELECT  FIRST_NAME,
        SALARY,
        DEPARTMENT_ID
FROM      EMPLOYEES
WHERE   FIRST_NAME = 'Bruce';
  • Depois de encontrar o valor do salário de Bruce, colocamos o valor na cláusula SELECT:
SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     SALARY >= 6000
          AND DEPARTMENT_ID = 60;
  • Podemos resolver da seguinte forma:
SELECT    FIRST_NAME,
          SALARY,
          DEPARTMENT_ID
FROM      EMPLOYEES
WHERE     DEPARTMENT_ID = 60
          AND SALARY >= (SELECT  SALARY
                         FROM    EMPLOYEES
                         WHERE   FIRST_NAME = 'Bruce');

TIPOS DE SUBQUERIES

  • Subqueries podem retornar uma única linha, várias linhas e várias colunas;
  • Quando a subquerie retorna uma única linha, podemos utilizar os operadores =, <, >, <>, <= ou >=;
  • Quando a subquerie retorna várias linhas, temos que utilizar o IN, ALL ou ANY (uma ou mais linhas)
  • As subqueries (internas) podem também utilizar funções de grupo (min, max, avg, …), com isso, a subquerie retornará um única linha;
  • Subqueries são recursos poderosos do comando SELECT, que basicamente podemos ter retornar qualquer informação do banco de dados;
  • Basicamente, é possível utilizar subquerie em qualquer cláusula de uma SELECT;
  • Caso a subconsulta não retorna registros, nenhum valor será retornado para toda a SELECT;

EXEMPLO 1:

SELECT  FIRST_NAME, JOB_ID,SALARY,
        DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   JOB_ID =     (SELECT JOB_ID
                      FROM EMPLOYEES
                      WHERE EMPLOYEE_ID = 111)
        AND SALARY > (SELECT SALARY
                      FROM   EMPLOYEES
                      WHERE  EMPLOYEE_ID = 110);

EXEMPLO 2:

SELECT  FIRST_NAME, JOB_ID,SALARY,
        DEPARTMENT_ID
        FROM    EMPLOYEES
        WHERE   JOB_ID      = (SELECT JOB_ID
                               FROM EMPLOYEES
                               WHERE EMPLOYEE_ID = 111)
                AND SALARY <= (SELECT SALARY
                               FROM   EMPLOYEES
                               WHERE  EMPLOYEE_ID = 110);

EXEMPLO 3

SELECT  FIRST_NAME,         JOB_ID,
        SALARY,         DEPARTMENT_ID
FROM    EMPLOYEES
WHERE   SALARY =  (SELECT MIN(SALARY)
                   FROM   EMPLOYEES);

EXEMPLO 4:

SELECT   JOB_ID, AVG(SALARY)
FROM     EMPLOYEES
GROUP BY JOB_ID
HAVING   AVG(SALARY) = (SELECT  MIN(AVG(SALARY))
                        FROM   EMPLOYEES
                        GROUP BY JOB_ID);

EXEMPLO 5 (ERRO)

SELECT  EMPLOYEE_ID, LAST_ANME
FROM    EMPLOYEES
WHERE   SALARY = (SELECT MIN(SALARY)
                  FROM    EMPLOYEES
                  GROUP BY DEPARTMENT_ID);

EXEMPLO 6 (RETORNO NULO)

SELECT  EMPLOYEE_ID, LAST_NAME
FROM    EMPLOYEES
WHERE   COMMISSION_PCT = (SELECT COMMISSION_PCT
                          FROM    EMPLOYEES
                          WHERE FIRST_NAME = 'Santiago');

SUBQUERIES DE VÁRIAS LINHAS

  • Quando temos em uma Subquerie que retorna várias linhas, temos que verificar qual o operador que podemos utilizar. O operador precisa ser compatível com a quantidade de retorno;
  • Quando temos essa situação, temos que utilizar os operadores IN, ANY e ALL;

EXEMPLO DE IN

SELECT  JOB_ID, FIRST_NAME, SALARY
FROM    EMPLOYEES
WHERE   SALARY IN (SELECT MIN(SALARY)
                   FROM EMPLOYEES
                   GROUP BY JOB_ID)
ORDER BY JOB_ID;

EXEMPLO DE ANY

SELECT    EMPLOYEE_ID,   FIRST_NAME,
          JOB_ID, SALARY
FROM    EMPLOYEES
WHERE   SALARY < ANY (SELECT SALARY
                      FROM EMPLOYEES
                      WHERE JOB_ID = 'IT_PROG')
        AND JOB_ID <> 'IT_PROG'
ORDER BY SALARY DESC;

EXEMPLO DE ALL

SELECT    EMPLOYEE_ID,   FIRST_NAME,
          JOB_ID,   SALARY
FROM      EMPLOYEES
WHERE     SALARY > ALL (SELECT SALARY
                        FROM EMPLOYEES
                        WHERE JOB_ID = 'IT_PROG')
              AND JOB_ID <> 'IT_PROG'
ORDER BY SALARY DESC;

OPERADORES DE CONJUNTO

  • São utilizados geralmente para se fazer a união de duas ou mais queries
  • Trará um único resultado da consulta obtida;
  • Geralmente, precisam ser compatíveis os tipos e quantidade de colunas;
  • Podemos utilizar os seguintes comandos de operadores de conjunto: UNION, UNION ALL, INTERSECT, MINUS;

UNION: Retorna todas as linhas distintas das consultas que estarão envolvidas, precisando ter quantidade de colunas e tipos iguais; UNION ALL: Retorna todas as linhas (inclusive as duplicadas) das consultas que estarão envolvidas, precisando ter quantidade de quantidade de colunas e tipos iguais; Se possível, utilize o UNION ALL ao invés de UNION, pois ele tem mais performance no retorno de dados;

INTERSECT: Retorna todas as linhas das consultas que estarão envolvidas, presentes em ambas consultas (ou demais consultas); MINUS: Retorna todas as linhas que estão na primeira consulta que não estão na segunda consulta;

ORDEM DE EXECUÇÃO DOS OPERADORES

  • Para o ORACLE, não há um operador que tenha prioridade de primeira execução;
  • A ordem padrão de execução é de cima para baixo;
  • A ordem de execução pode ter colocação de prioridade colocando parênteses;

UNION

  • Retorna o resultados de duas ou mais consultas, eliminando resultados repetidos;
  • A repetição de resultados é considerada toda a tupla;
  • Os tipos de dados e quantidade de colunas devem ser os mesmos entre as queries;
  • Não é necessário ter o mesmo nome de colunas entre as queries;
  • Valores duplicados não serão ignorados (o comando irá retirar valores repetidos)
  • O operador IN é executado antes do operador UNION;
  • Será feito um ORDER BY automático da primeira coluna;

EXEMPLO DE UNION

SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
UNION
SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

UNION ALL

  • Retorna o resultados de duas ou mais consultas, INCLUSIVE resultados repetidos;
  • Possui as mesmas diretivas do UNION;
  • NÃO ordena o resultado pela primeira coluna;
  • O operador DISTINCT não pode ser utilizado;
  • O retorno é mais rápido que a utilização do UNION;

EXEMPLO DE UNION ALL

SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
UNION ALL
SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

SELECT    EMPLOYEE_ID,
JOB_ID,
DEPARTMENT_ID
FROM    EMPLOYEES

UNION ALL
SELECT    EMPLOYEE_ID,
JOB_ID,
DEPARTMENT_ID
FROM    JOB_HISTORY;

INTERSECT

  • Retorna as linhas que estejam em duas ou mais consultas, ou seja, somente os resultados iguais das consultas são retornados pelo operador;
  • Faz a intersecção dos resultados de todas as consultas;
  • É necessário ter o mesmo número e tipos de colunas;
  • A ordem das consultas não influenciam no resultado;
  • O INTERSECT não ignora valores nulos;

EXEMPLO DE INTERSECT
pre class=”brush: sql; title: ; notranslate” title=””>
SELECT  EMPLOYEE_ID, JOB_ID
FROM    EMPLOYEES
INTERSECT SELECT  EMPLOYEE_ID, JOB_ID
FROM    JOB_HISTORY;

 

MINUS

  • Retorna as linhas da primeira consulta que NÃO estão na segunda consulta;
  • Faz a “subtração” de linhas entre a primeira e segunda consulta;
  • Caso tenha mais de duas consultas, a resolução é feita de cima para baixo;
  • É necessário ter o mesmo número e tipo de colunas nas consultas;
  • A alteração das consultas influencia no resultado final;
  • Todas as colunas da cláusula WHERE precisam estar na cláusula SELECT;
  • EXEMPLO DE MINUS
    SELECT  EMPLOYEE_ID, JOB_ID
    FROM    EMPLOYEES
    MINUS
    SELECT  EMPLOYEE_ID, JOB_ID
    FROM    JOB_HISTORY;
    

  • DIRETRIZES GERAIS

  • Para qualquer operador, o número de colunas e os tipos de dados devem ser equivalentes entre as consultas;
  • A utilização de parênteses altera a sequencia de execução dos operadores;
  • ORDER BY só pode ser usada no final da instrução;
  • ORDER BY aceitará somente os nomes das colunas da primeira consulta ou a notação posição;
  • O nome das colunas são considerados os da primeira consulta;
  • Tuplas duplicadas são eliminadas automaticamente, com exceção de UNION ALL;
  • Por padrão, as saídas são ordenadas em ordem crescente, com exceção de UNION ALL;
  • Quando o número de colunas não são correspondentes entre as consultas, é possível resolver com NULL, ou com uma constante do mesmo tipo equivalente;
  • É possível adicionar uma coluna virtual para ordenação de linhas entre as consultas;
  • EXEMPLO DE TIPOS DE DADOS
    SELECT  DEPARTMENT_ID, NULL AS LOCATION,
    HIRE_DATE
    FROM    EMPLOYEES
    UNION
    SELECT  DEPARTMENT_ID,  LOCATION_ID,
    TO_DATE(NULL)
    FROM    DEPARTMENTS;
    

    EXEMPLO DE ORDEM

    SELECT    DEPARTMENT_ID, HIRE_DATE,
    2 AS ORDEM
    FROM    EMPLOYEES
    UNION
    SELECT    DEPARTMENT_ID,  TO_DATE(NULL),
    1 AS ORDEM
    FROM    DEPARTMENTS
    ORDER BY ORDEM;
    

Banco de Dados II – Aula 05

JOINS

  • Utilizamos JOINS quando precisamos consultar tabelas relacionadas numa única sentença SELECT;
  • Os JOINS são muito utilizados em banco de dados relacionais, pois grande partes das consultas utilizam mais de uma tabela;
  • O ORALCE 11g utiliza o padrão SQL 99, possuindo algumas sintaxes para diferentes tipos de JOINS;

NATURAL JOINS

  • Une duas ou mais tabelas que tenham colunas do mesmo nome e do mesmo tipo;
  • Caso se tenha o tipo de dado diferente, o ORACLE irá retornar um erro;
  • Quando há mais de um campo com o mesmo nome e tipo de dados em tabelas diferentes que estão sendo avaliados pelo NATURAL JOIN, todos esses campos são considerados;
  • Quando utilizamos o USING, NÃO podemos usar ALIAS em nomes de colunas e tabelas
  • EXEMPLO DE NATURAL JOINS
SELECT  *
FROM    EMPLOYEES;

SELECT  *
FROM    DEPARTMENTS;

SELECT  FIRST_NAME, LAST_NAME, DEPARTMENT_NAME
FROM    EMPLOYEES
        NATURAL JOIN DEPARTMENTS
WHERE   DEPARTMENT_NAME IN ('IT','Finance','Sales');

JOIN COM USING

  • Utilizamos USING quando precisamos relacionar uma coluna entre diferentes tabelas (diferente do NATURAL, que irá obrigatoriamente fazer o relacionamento com todas);
  • Quando utilizamos o USING, NÃO podemos usar ALIAS em nomes de colunas e tabelas (DENTRO DO USING);
  • EQUIJOIN é um join interno, a qual é uma alternativa de utilização do USING e NATURAL JOIN, sendo muito utilizado para ligar tabelas com chaves primárias e estrangeiras;
  • Aconselhável utilização de ALIAS, mas não são obrigatórios;
  • ALIAS podem ter até 30 caracteres.
  • Se o ALIAS for especificado, é necessário utilizá-lo em todo comando;
  • EXEMPLO DE USING
SELECT  EMPLOYEES.EMPLOYEE_ID,
        EMPLOYEES.LAST_NAME,
        DEPARTMENTS.LOCATION_ID,
        DEPARTMENT_ID
FROM    EMPLOYEES
        JOIN DEPARTMENTS
            USING (department_id) ;

--ERRO
SELECT  EMPLOYEES.EMPLOYEE_ID,
        EMPLOYEES.LAST_NAME,
        DEPARTMENTS.LOCATION_ID,
        DEPARTMENTS.DEPARTMENT_ID
FROM EMPLOYEES
     JOIN DEPARTMENTS
         USING (department_id) ;

--ERRO
SELECT  E.EMPLOYEE_ID,
        E.LAST_NAME,
        D.LOCATION_ID,
        DEPARTMENT_ID
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
           USING (D.department_id);

--EQUIJOIN
SELECT L.CITY, D.DEPARTMENT_NAME
FROM   LOCATIONS L
       JOIN DEPARTMENTS D
           USING (LOCATION_ID)
WHERE  LOCATION_ID = 1400;

CLÁUSULA ON

  • Utilizado em conjunto com JOIN;
  • As colunas não precisam ter o mesmo nome entre tabelas, porém precisam ter o mesmo tipo (ou compatibilizar os tipos);
  • Deixa os códigos mais legíveis, comparando com outros comandos de JOIN;
  • Possível fazer SELF-JOIN, quando uma tabela precisa fazer referencia com ela mesma;
  • No SELF-JOIN, é necessário criar um alias na segunda tabela, para que seja possível fazer o auto-relacionamento;
  • É possível utilizar conectores AND ou OR em JOIN’s, possivelmente fazendo o mesmo papel da cláusula WHERE;
  • No padrão SQL 99, JOIN’s são executados da esquerda para a direita;
  • Quando unimos três ou mais tabelas, também são conhecidos como JOIN’s tridimensionais;

EXEMPLO DE CLÁUSULA ON

SELECT  E.EMPLOYEE_ID,
        E.FIRST_NAME,
        E.DEPARTMENT_ID,
        D.LOCATION_ID
FROM EMPLOYEES E
     JOIN DEPARTMENTS D
         ON (E.DEPARTMENT_ID =   D.DEPARTMENT_ID);

EXEMPLO DE SELF-JOIN

SELECT  E.LAST_NAME AS EMP,   M.LAST_NAME AS MGR
FROM    EMPLOYEES E
        JOIN EMPLOYEES M
            ON (E.MANAGER_ID =   M.EMPLOYEE_ID);

EXEMPLO DE RESTRIÇÃO NO ON

SELECT  E.EMPLOYEE_ID,
        E.FIRST_NAME,
        E.DEPARTMENT_ID,
        D.LOCATION_ID
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
          ON (E.DEPARTMENT_ID = D.DEPARTMENT_ID)
          AND E.MANAGER_ID = 149;

EXEMPLO DE LIGAÇÃO DE 3 TABELAS

SELECT  EMPLOYEE_ID,
        CITY,
        DEPARTMENT_NAME
FROM    EMPLOYEES E
        JOIN DEPARTMENTS D
          ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
        JOIN LOCATIONS L
          ON D.LOCATION_ID = L.LOCATION_ID;

JOIN INTERNOS E EXTERNOS

  • NO EQUI-JOIN são quando temos que utilizar JOIN’s, porém não contém o operador de igualdade;
  • Não são muito utilizados, pois deixam o código menos legível;
  • EXEMPLO
SELECT      E.LAST_NAME,
            E.SALARY,
            J.JOB_TITLE
FROM        EMPLOYEES E
            JOIN  JOBS J
              ON E.SALARY BETWEEN   J.MIN_SALARY
              AND J.MAX_SALARY;
  • Sendo os JOIN’S INTERNOS, o NATURAL JOIN, USING e ON retornam o resultado de apenas linhas iguais entre as tabelas que estão envolvidas;
  • Sendo os JOIN’s EXTERNOS, LEFT, RIGHT e FULL JOIN, servem para retornar resultado que não tenham apenas linhas iguais;

LEFT OUTER JOIN ou LEFT JOIN

  • Retorna todas as linhas que estão na esquerda do JOIN (como se fosse a tabela principal), sem necessariamente ter uma linha correspondente no lado oposto do JOIN;
  • Para melhor visualizar, coloque em uma única linha as tabelas dos lados do JOIN;
  • EXEMPLO DE LEFT OUTER JOIN
SELECT  E.FIRST_NAME,
        E.LAST_NAME,
        E.DEPARTMENT_ID,
        D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        LEFT OUTER JOIN DEPARTMENTS D
          ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

RIGHT OUTER JOIN ou RIGHT JOIN

    • Retorna todas as linhas que estão na direita do JOIN (como se fosse a tabela principal), sem necessariamente ter uma linha correspondente no lado oposto do JOIN;
    • Para melhor visualizar, coloque em uma única linha as tabelas dos lados do JOIN;
    • EXEMPLO DE RIGHT OUTER JOIN
      SELECT  E.FIRST_NAME,
              E.LAST_NAME,
              E.DEPARTMENT_ID,
              D.DEPARTMENT_NAME
      FROM    EMPLOYEES E
              RIGHT OUTER JOIN DEPARTMENTS D
                ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

FULL OUTER JOIN OU FULL JOIN

  • Retorna todas as linhas que estão na direita do JOIN e na esquerda do JOIN (ao mesmo tempo);
  • FULL OUTER JOIN é diferente de ter na mesma expressão SQL o LEFT e RIGHT JOIN;
  • EXEMPLO DE FULL OUTER JOIN
SELECT  E.FIRST_NAME,
        E.LAST_NAME,
        E.DEPARTMENT_ID,
        D.DEPARTMENT_NAME
FROM    EMPLOYEES E
        FULL OUTER JOIN DEPARTMENTS D
          ON E.DEPARTMENT_ID = D.DEPARTMENT_ID;

CROSS JOIN

    • Produtos cartesianos são quando todas as linhas da primeira tabela são relacionadas com todas as linhas da segunda tabela;
    • Tem pouca utilização no dia a dia;
    • Pode-se fazer massa de dados para testes, dependendo dos dados que a tabela tenha;
    • Irá retornar a quantidade de linhas resultado da multiplicação do número de linhas das duas tabelas;
    • Pode-se fazer CROSS JOIN com quaisquer tabelas, porém, a combinação dos dados não teria muito sentido;
    • EXEMPLO DE CROSS JOIN
      SELECT  E.FIRST_NAME,
              E.LAST_NAME,
              E.DEPARTMENT_ID,
              D.DEPARTMENT_NAME
      FROM    EMPLOYEES E
              CROSS JOIN DEPARTMENTS D;

 

EXERCÍCIOS

Utilize o schema HR do banco de dados Oracle 11g XE

  1. Selecione o código do departamento, nome do departamento e quantidade de funcionários que trabalham no respectivo departamento;
  2. Selecione o código do departamento, nome do departamento e a média de salário do respectivo departamento;
  3. Selecione o código do serviço (job_id), a descrição do serviço, o maior valor de salário, o menor valor de salário e a média de salário por job_id;
  4. Selecione a soma de todos os salários dos funcionários subordinados à Steven King (inclusive o próprio Steven King)
  5. Selecione o nome e sobrenome dos empregados a qual o seu salário seja maior que a média dos salários do departmamento de TI (IT_PROD)

 

Banco de Dados II – Aula 04

FUNÇÕES DE CONVERSÃO

  • Existem 2 tipos de funções de conversão: implícitas e explícitas;
  • Implícitas o ORACLE converte para o tipo apropriado;
  • Explicitas o usuário faz a utilização manualmente;
  • Sempre que possível, utilizar conversões explícitas;

CONVERSÃO IMPLÍCITAS:

  • O ORACLE automaticamente faz:
    • VARCHAR2 ou CHAR para NUMBER;
    • VARCHAR2 ou CHAR para DATE;
    • NUMBER para VARCHAR2;
    • DATE para VARCHAR2;
  • EXEMPLO:… WHERE NUMERO = ‘25’;… WHERE DATANASC = ’10/23/2000’;

conversoesoracle

http://oracledbagirl.blogspot.com.br/2014/11/funcoes-de-conversao.html

TO_CHAR PARA DATE

  • Tanto TO_CHAR quanto TO_DATE, tem praticamente a mesma formatação;
  • Necessário estar limitado entre aspas simples;
  • É CASE SENSITIVE;

FORMATOS DE DADOS MAIS UTILIZADOS

Formato Significado
YYYY Ano completo com 4 dígitos
YEAR Ano por extenso (em inglês)
MM Mês com 2 dígitos em formato numérico
MONTH Nome do mês por extenso (em inglês)
MON Mês abreviado com 2 dígitos (em inglês)
DY Dia da semana abreviado por extenso (em inglês)
DAY Dia da semana por extenso (em inglês)
DD Dia com 2 dígitos por extenso (em inglês)

OUTROS FORMATOS DE DATAS

Formato Significado
SCC ou CC Acrescenta o A.C. no século
Anos em datas YYYY ou SYYYY Ano; Acrescenta o A.C.
YYY, YY ou Y Últimos 3, 2 ou 1 dígito do ano
Y.YYY Ano com formatação de ponto
IYYY, IYY ou IY Últimos 3, 2 ou 1 dígito do ano no padrão ISSO
SYEAR ou YEAR Ano por extenso, quando tem o S inclui prefixo A.C.
BC ou AD Indica o ano AC ou DC
B.C. ou A.D. Indica o ano A.C. ou D.C. (com pontos)
Q Trimestre do ano
MM Mês com 2 dígitos

OUTROS FORMATOS DE DATAS

 Formato Significado
MONTH Mês por extenso, com 9 dígitos, preenchidos com espaços em branco
MON Mês com abreviação com 3 dígitos
RM Mês em numeral romano
WW ou W Semana do ano ou mês
DDD, DD ou D Dia do ano, do mês ou da semana
DAY Dia por extenso, com 9 dígitos, preenchidos com espaços em branco
DY Dia com abreviação com 3 dígitos
J Dia juliano, desde 31/12/4713 A.C. (número de dias)

FORMATOS DE HORÁRIO

 Formato Significado
AM ou PM Indica o meridiano
A.M. ou P.M. Indica o meridiano com pontos
HH ou HH12 ou HH24 Hora do dia, hora de 1-12 ou hora de 0 a 24
MI Minutos de 0-59
SS Segundos de 0-59
SSSSS Segundos após meia-noite de 0-86399

OUTROS FORMATOS DE DATAS

 Formato Significado
/ , . Pontuação será produzida no resultado
“of the” String será produzida no resultado
TH Numero ordinal (3TH, 4TH)
SP Número por extenso (FOUR, FIVE)
SPTH ou THSP Número ordinal por extenso (FORURTH, FIVETH)

TO_CHAR PARA DATE

  • O formato de data RR é semelhante ao formato YY, porém é específico para distinguir séculos diferentes;
  • Para o século atual, o ORACLE considera os anos de 00 a 49;
  • Para o século anterior, o ORACLE considera os anos de 50 a 99;
  • Exemplos
SELECT SYSDATE FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'DAY-MONTH-YEAR') FROM DUAL;

SELECT TO_CHAR(SYSDATE,'mm/dd/yyyy HH24:MI:SS') FROM DUAL;

 

alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

SELECT TO_DATE('01/01/97','DD/MM/YY') FROM DUAL;

SELECT TO_DATE('01/01/97','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/49','DD/MM/RR') FROM DUAL;

SELECT TO_DATE('01/01/50','DD/MM/RR') FROM DUAL;

select trunc(to_date('27-Jul-1987'),'YYYY') FROM dual;

select trunc(to_date('27-Jul-1987'),'RRRR') FROM dual;

TO_CHAR PARA NÚMEROS

  • O tipo de dados é de NUMBER para VARCHAR2;
  • Muito utilizado para concatenação de dados;
  • O ORACLE também faz conversão implícita de VARCHAR2 para NUMBER, quando possível;
  • Exemplos
SELECT FIRST_NAME, SALARY FROM   EMPLOYEES;

SELECT FIRST_NAME, TO_CHAR(SALARY,'L99999D99') AS SALARY FROM   EMPLOYEES;

SELECT TO_CHAR(1234,'099999') FROM DUAL;

SELECT TO_CHAR(-1234,'9999MI') FROM DUAL;

SELECT TO_CHAR(1234,'S9999') FROM DUAL;

SELECT TO_CHAR(-1234,'S9999') FROM DUAL;

TO_CHAR PARA DATE

  • PRINCIPAIS ELEMENTOS
Caracter Significado
9 Representa um número
0 Impõe a exibição de um zero
$ Insere um sinal de dólar
L Usa o símbolo da moeda local
. Imprime uma casa decimal
, Imprime uma virgula como indicador de milhar
D Retorna caracter especial na posição especificada (por padrão é ,)
MI Sinal de – à direita (valores negativos)

ORDEM DE EXECUÇÃO DAS FUNÇÕES

  • A ordem de execução é feita de dentro para fora, quando existe mais de uma função na mesma expressão;
  • Exemplo
SELECT INITCAP( CONCAT( SUBSTR( UPPER(FIRST_NAME),1,3),'_ORACLE') 
FROM EMPLOYEES

FUNÇÕES COM VALORES NULOS

  • Existem funções que podem fazer o tratamento de campos com valores nulos;
  • O valor NULO não é espaço em branco ou zeros, portanto, precisam ter funções especiais para que sem manipulados corretamente;

Comando NVL

  • Faz a conversão de um valor nulo em outro valor;
  • Sintaxe:
    • NVL(expr1,expr2)
  • Exemplo
SELECT first_name, 
       NVL(commission_pct,0), 
       commission_pct + 12 as "COMISSAO ANUAL" 
FROM   EMPLOYEES;

Comando NVL2

  • Compara se a expressão 1 não for nula, retorna a expressão 2; se a expressão 1 for nula, retorna a expressão 3;
  • Sintaxe:
    • NVL2(expr1,expr2, expr3)
  • Exemplo
SELECT first_name, 
       salary, 
       commission_pct, 
       NVL2(commission_pct, 'SALARIO / COMISSAO','SALARIO') 
FROM   employees;

Comando NULLIF

  • Compara se as duas expressões tem valores iguais. Caso sim retorna o valor NULL, e caso não, retorna o valor da expressão 1;
  • Sintaxe:
    • NULLIF(expr1,expr2)
  • Exemplo
SELECT first_name,
       last_name,
       LENGTH(first_name),   
       LENGTH(last_name),   
       NULLIF(LENGTH(first_name),        
       LENGTH(last_name))
FROM   employees;

Comando COALESCE

  • Retorna o primeiro valor NÃO NULO de uma lista de expressões;
  • Sintaxe:
    • COALESCE(expr1,expr2,expr3,… exprN);
  • Exemplo
SELECT last_name,
       manager_id,
       commission_pct,
       COALESCE(manager_id,
                commission_pct,
                -1)
FROM   employees
ORDER BY commission_pct DESC;

EXPRESSÕES CONDICIONAIS

  • Permitem utilizar IF.. THEN.. ELSE nas expressões SQL;
  • Podemos utilizar o CASE (padrão ANSI) e o DECODE (exclusivo do ORACLE)

Comando CASE

  • Praticamente tem a mesma função de expressões condicionais nas demais linguagens de programação (IF … THEN…ELSE)
  • Sintaxe do Comando CASECASE expr1WHEN comp_expr1 THEN retorno_expr1
    WHEN comp_expr2 THEN retorno_expr2
    WHEN comp_expr3 THEN retorno_expr3
    ELSE retorno_else
    END;
    
  • Exemplo de CASE
SELECT last_name, job_id, salary,
       CASE job_id
         WHEN 'IT_PROG' THEN salary * 1.20
         WHEN 'ST_CLERCK' THEN salary * 1.30
         WHEN 'SA_REP' THEN salary * 1.50
         ELSE salary
       END "NOVO SALARIO"
FROM   employees;

Comando DECODE

  • É uma versão simplificada do CASE (em termos de parâmetros), tendo a mesma finalidade;
  • Sintaxe: DECODE (expr1, busca1, resultado1, busca2, resultado2, …, retorno_default);
  • Exemplo
SELECT last_name, job_id, salary,
       DECODE (job_id, 'IT_PROG', salary * 1.20,
       'ST_CLERCK', salary * 1.30,
       'SA_REP', salary * 1.50,
       salary) "NOVO SALARIO"
FROM   employees;

FUNÇÕES DE GRUPO

  • Geralmente são utilizados para obter resultados sumarizados (resultados agrupados);
  • Pode ser aplicado na tabela inteira, ou em um determinado grupo;
  • Utiliza-se a função de grupo na sentença SELECT;
  • Na cláusula GROUP BY é onde será a determinação de qual dado será agrupado;

COMANDO AVG

  • Retorna a média aritmética de um determinado campo dividido pelo número de linhas de retorno da tabela;
  • Sintaxe: AVG([DISTINCT | ALL] n)
  • EXEMPLO DE COMANDO AVG
SELECT  AVG(SALARY)
FROM    EMPLOYEES;

SELECT  AVG(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT  AVG(NVL(COMMISSION_PCT,0))
FROM    EMPLOYEES;

COMANDO COUNT

  • Retorna o número de linhas ou quantidade de campos não nulos de uma tabela;
  • Sintaxe: COUNT(* | DISTINCT | ALL expr)
  • EXEMPLO DE COMANDO COUNT
SELECT  COUNT(*)
FROM    EMPLOYEES;

SELECT  COUNT(COMMISSION_PCT)
FROM    EMPLOYEES;

SELECT COUNT(DISTINCT JOB_ID)
FROM   EMPLOYEES;

COMANDO MAX

  • Retorna o valor máximo da expressão, ignorando os valores nulos;
  • Sintaxe: MAX([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MAX
SELECT    MIN(START_DATE),   MAX(START_DATE)
FROM      JOB_HISTORY;

COMANDO MIN

  • Retorna o valor mínimo da expressão, ignorando os valores nulos;
  • Sintaxe: MIN([DISTINCT | ALL] expr)
  • EXEMPLO COMANDO MIN E MAX
SELECT    MIN(FIRST_NAME),   MAX(FIRST_NAME)
FROM      EMPLOYEES;

COMANDO SUM

  • Retorna a soma de valores de uma expressão, ignorando os valores nulos;
  • Sintaxe: SUM([DISTINCT | ALL] expr)
  • EXEMPLO DE COMANDO SUM
SELECT  SUM(SALARY)
FROM      EMPLOYEES;

COMANDO STDDEV

  • Retorna o desvio padrão, ignorando os valores nulos;
  • Sintaxe: STDDEV([DISTINCT | ALL] expr)

COMANDO VARIANCE

  • Retorna a variação, ignorando os valores nulos;
  • Sintaxe: VARIANCE([DISTINCT | ALL] expr)

FUNÇÕES DE GRUPO COM AGRUPAMENTO

  • O agrupamento irá fazer com que dados sejam agrupados e geralmente terá uma função de grupo associada;
  • A cláusula GROUP BY precisa estar na sentença do comando SQL, contendo todos os campos que estão no SELECT, menos as próprias funções de grupo;
  • O agrupamento deve ser aplicado a dados que tenham repetição, ou que se consiga fazer operações matemáticas;
  • Não há muito sentido em fazer agrupamentos, por exemplo em campos que são chave primária, pois o conteúdo nunca se repete;
  • Os resultados retornarão uma ou mais linhas, com resultados sumarizados;
  • Não é possível utilizar apelidos das colunas na cláusula GROUP BY.
  • EXEMPLOS DE GROUP BY
SELECT    JOB_ID, COUNT(*)
FROM      EMPLOYEES
GROUP BY  JOB_ID
ORDER BY  COUNT(*) DESC;

SELECT    DEPARTMENT_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
ORDER BY  DEPARTMENT_ID NULLS FIRST;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID

SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

SELECT    DEPARTMENT_ID, MIN(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID;

FUNÇÕES DE GRUPO COM HAVING

  • A cláusula HAVING sempre será utilizada com GROUP BY;
  • Ela serve para fazer limitação de linhas, a qual é aplicada no GROUP BY;
  • Dê preferencia para fazer limitações de linhas sempre que possível na cláusula WHERE;
  • A sequencia que o ORACLE executa um comando SQL quando se tem um HAVING:
    • 1 – As linhas agrupadas;
    • 2 – A função de grupo é aplicada;
    • 3 – os grupos filtradas na cláusula HAVING são exibidos;
  • A ordem de função de grupo tem a mesma ordem das outras funções, ou seja, funções mais internas são executadas primeiro;
  • É possível ter no máximo 2 funções de grupos na mesma sentença;
  • EXEMPLO DE HAVING
SELECT    DEPARTMENT_ID, MAX(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    DEPARTMENT_ID, AVG(SALARY)
FROM      EMPLOYEES
GROUP BY  DEPARTMENT_ID
HAVING    MAX(SALARY) > 10000;

SELECT    JOB_ID, SUM(SALARY)
FROM      EMPLOYEES
GROUP BY  JOB_ID
HAVING    SUM(SALARY) > 10000
ORDER BY  SUM(SALARY);

SELECT  MAX(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY DEPARTMENT_ID;

SELECT  SUM(AVG(SALARY))
FROM   EMPLOYEES
GROUP BY JOB_ID;

 

Vídeo 01

Vídeo 02

Banco de Dados II – Aula 03

VARIÁVEIS DE SUBSTITUIÇÃO

  • Geralmente utilizadas para executar uma instrução SELECT mais de uma vez;
  • Servirá como se fosse parâmetro;
  • Somente utilizadas na cláusula WHERE, com o símbolo : (para apex);
  • O símbolo & para sqlplus, pode ser utilizado em mais cláusulas;
  • As variáveis de substituição servem para guardar temporariamente valores;
  • EXEMPLOS
Exemplo 01
SELECT 	* 
FROM 	employees
WHERE 	employee_id = :employee_id;

Exemplo 02
SELECT 	* 
FROM 	employees
WHERE	first_name = :first_name
    		AND salary >= :salary;
 

Exemplo 03
SELECT * 
FROM employees
WHERE last_name like '&last_name';

SELECT * 
FROM &tabela

(no SQLDeveloper, pressione  + , e ele pedirá o valor a ser digitado)

FUNÇÕES DE LINHAS

  • Podem ser utilizadas para tipos de dados character, data, números em uma instrução SELECT;
  • Funcionam como conversão, formatação de dados (datas e números), manipulam strings, podem ter ou não dados de entrada (ou parâmetros), mas sempre possuem dados de saída;
  • As maioria das funções apresentadas aqui são do Oracle;
  • Possuem 2 tipos:
    • por Linha: só retornam um resultado por linha (funções de character, número, data e conversão);
    • de Grupo: manipulam grupos de linhas para retornar apenas um resultado;
  • Caracteristicas das functions de linha;
    • Podem receber argumentos (podendo ser uma constant, variável, nome de coluna, expressão);
    • Podem ser usados na cláusulas SELECT, WHERE, ORDER BY;
    • Podem ser encadeadas;
    • Funções de data: entradas do tipo DATE e retornam um tipo DATE, exceto MONTHS_BETWEEN, que retorna um tipo número;
    • Funções numéricas: entradas do tipo NUMERIC e retornam um tipo NUMERIC;
    • Funções de character: entrada do tipo caracteres e retornam caracteres ou numeros;
    • Funções de conversão: convertem um tipo de dado em outro;
    • Outras funções: NVL, NVL2, NULLIF, COALESCE, CASE, DECODE;

FUNÇÕES DE LINHAS – CARACTERES

  • Manipulação de caixa alta e baixa (maiúsculo e minúsculo):
    • UPPER: retornar todos os valores em letras maiúsculas;
    • LOWER: retornar todos os valores em letras minúsculas;
    • INITCAP: a primeira letra de cada palavra em maiúscula e o restante minúsculas;
  • Manipulação de caracter:
    • CONCAT: Une dois valores;
    • SUBSTR: Extrai uma string de determinado tamanho;
    • LENGTH: Retorna o tamanho de uma string em um valor numérico;
    • INSTR: Localiza uma posição numérica de um character nomeado;
    • LPAD: Preenche com valor do character à esquerda;
    • RPAD: Preenche com valor do character à direita;
    • TRIM: Retira caracteres em branco à direita e à esquerda de uma string;
    • Exemplos
SELECT  UPPER('rodrigo saito') 
FROM    DUAL;

SELECT  LOWER('RODRIGO SAITO') 
FROM    DUAL;

SELECT  INITCAP('rodrigo saito') 
FROM    DUAL;

SELECT  CONCAT('Rodrigo','Saito') 
FROM    DUAL;

SELECT  SUBSTR('Teste de caracteres no Oracle',1,5) 
FROM    DUAL;

SELECT  SUBSTR('Teste de caracteres no Oracle',6) 
FROM    DUAL;

SELECT  LENGTH('Teste de caracteres no Oracle') 
FROM    DUAL;

SELECT  INSTR('Teste de caracteres no Oracle','s') 
FROM    DUAL;

SELECT  LPAD('Teste de caracteres no Oracle',40, '0')
FROM    DUAL;

SELECT  RPAD('Teste de caracteres no Oracle',40, '0')
FROM    DUAL;

SELECT REPLACE('rodrigo anchieta', 'anchieta', 'saito')
FROM DUAL;

SELECT TRIM('R' FROM 'RODRIGO') 
FROM DUAL;

FUNÇÕES DE NÚMEROS

  • Tem como entrada dados do tipo numérico e sempre retornam dados do tipo numérico;
  • ROUND: arredonda o valor para cima (caso o decimal seja maior que 5);
  • TRUNC: retorna o valor a ser truncado;
  • MOD: retorna o resto da divisão;

ROUND

  • SINTAXE: ROUND(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido;
  • Exemplos
SELECT ROUND(10.5) 
FROM DUAL;

SELECT ROUND(10.5,1) 
FROM DUAL;

SELECT ROUND(10.51,1) 
FROM DUAL;

SELECT ROUND(10.55,1) 
FROM DUAL;

SELECT ROUND(10.60,1) 
FROM DUAL;

SELECT ROUND(10.554,2) 
FROM DUAL;

SELECT ROUND(10.555,2) 
FROM DUAL;

TRUNC

  • SINTAXE: TRUNC(VALOR,N): N é opcional, sendo o número de casas decimais a ser definido. O comando basicamente irá cortar o número definido;
  • Exemplos
SELECT TRUNC(10.1) FROM DUAL;

SELECT TRUNC(10.1,1) FROM DUAL;

SELECT TRUNC(10.15,1) FROM DUAL;

SELECT TRUNC(10.155,2) FROM DUAL;

SELECT TRUNC(10.155,-1) FROM DUAL;

SELECT TRUNC(10.155,-2) FROM DUAL;

SELECT TRUNC(100.155,-2) FROM DUAL;

MOD

  • SINTAXE: MOD(M,N): M PRIMEIRO VALOR, E N é o segundo valor. O comando retornar o resto de M por N.
  • Exemplos
SELECT MOD(10,2) FROM DUAL;

SELECT MOD(10,3) FROM DUAL;

SELECT MOD(11,3) FROM DUAL;

SELECT MOD(19,5) FROM DUAL;

FUNÇÕES DE DATA

  • Datas são armazenadas internamente em formato numérico, tendo a representação de século, ano, mês, dia, hora, minuto e segundos;
  • Padrão do Oracle é MM/DD/YYYY;
  • Data válidas são 01/01/4712 A.C. a 31/12/9999 D.C.;
  • Na inserção do dado do tipo data em um campo, internamente o Oracle armazena o século da função SYSDATE, nas para exibição esse dado é ocultado por padrão;
  • Date armazena internamente 4 dígitos para o ano (dois dígitos para século e dois dígitos para ano);
  • Apesar da exibição ser de 2 dígitos para o ano, internamente são 4 dígitos;
  • De 1951 a 2050 mostra os 2 últimos dígitos do século atual;

SYSDATE

  • Retorna a data e horário do século atual, podendo ser usado em qualquer coluna.
  • É possível utilizar na tabela de sistema DUAL;\
  • Exemplo
select SYSDATE from DUAL

(será exibido por padrão somente a data)

SELECT SYSDATE + 5 FROM DUAL;

SELECT SYSDATE - 5 FROM DUAL;

SELECT SYSDATE - (SYSDATE - 10) FROM DUAL;

SELECT SYSDATE - TO_DATE('01/01/2015','mm/dd/yyyy') FROM DUAL;

select TO_CHAR(sysdate,'mm/dd/yyyy hh:mi') from dual;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi'),'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/24,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 3/24,'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:01','dd/mm/yyyy hh24:mi') + 1/(24*60),'dd/mm/yyyy hh24:mi') FROM DUAL;

SELECT TO_CHAR(TO_DATE('01/01/2016 09:00:00','dd/mm/yyyy hh24:mi:ss') + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss') FROM DUAL;

SELECT TO_CHAR(SYSDATE + 20/(24*60*60),'dd/mm/yyyy hh24:mi:ss')  FROM DUAL;

SELECT TO_CHAR(SYSDATE,'dd/mm/yyyy hh24:mi') FROM DUAL;

CALCULOS DE DIAS COM DATA

  • É possível fazer adição e subtração de dias em datas, além de fazer a diferença de dias entre datas, considerando os dias, meses e anos;
  • Para se fazer operações com horas, é necessário dividir um dia pela quantidade de horas (ou seja, 24);
  • Para se fazer operações com minutos, é necessário saber o quanto 1 minuto representa em 1 dia (1440);

OUTRAS FUNÇÕES DE DATA

  • Tem como entrada de parâmetros do tipo data e retornam valores do tipo data ou numérico;

MONTHS_BETWEEN(data1,data2)

  • Retorna o número de meses entre as datas do parâmetro;
  • Exemplos
SELECT MONTHS_BETWEEN( TO_DATE('01/01/2016'), TO_DATE('01/01/2015')) FROM DUAL;

SELECT MONTHS_BETWEEN(SYSDATE, TO_DATE('01/01/2015')) FROM DUAL;

ADD_MONTHS(data1,n)

  • Retorna uma data, adicionando a quantidade de meses especificadas no parâmetro em data1;
  • Exemplos
SELECT ADD_MONTHS(SYSDATE, 3) FROM DUAL;

SELECT SYSDATE + 90 FROM DUAL;

NEXT_DAY (data1,numeric)

  • Retorna o próximo dia da semana, sendo representando pelos números de 1 a 7 (1 é domingo a 7 que é sábado);
  • Exemplos
SELECT NEXT_DAY(SYSDATE,1) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,4) FROM DUAL;

SELECT NEXT_DAY(SYSDATE,-3) FROM DUAL;

LAST_DAY (data1)

  • Retorna o último dia do mês especificado;
SELECT LAST_DAY(SYSDATE) FROM DUAL;

SELECT LAST_DAY(TO_DATE('05/04/2015')) FROM DUAL;

ROUND (data1[,valor])

  • Retorna uma data arredondada, sendo o parâmetro valor opcional, e o padrão equivalente ao parâmetro ‘day’;
  • Exemplos
SELECT ROUND(SYSDATE) FROM DUAL;

SELECT ROUND(SYSDATE,'DAY') FROM DUAL;

SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

SELECT ROUND(SYSDATE,'YEAR') FROM DUAL;

SELECT ROUND(TO_DATE('01/15/2016 11:59:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL;

SELECT ROUND(TO_DATE('01/15/2016 12:01:00','mm/dd/yyyy hh24:mi:ss')) FROM DUAL;

SELECT ROUND( TO_DATE('01/15/2016','mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT ROUND( TO_DATE('01/16/2016','mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT ROUND(TO_DATE('06/30/2016','mm/dd/yyyy'),'YEAR') FROM DUAL;

SELECT ROUND(TO_DATE('07/01/2016','mm/dd/yyyy'),'YEAR') FROM DUAL;

TRUNC (data1[,valor])

  • Retorna uma data truncada (cortada), sendo o parâmetro valor opcional;
  • Exemplos
SELECT TRUNC(SYSDATE) FROM DUAL;

SELECT TRUNC(SYSDATE,'DAY') FROM DUAL;

SELECT TRUNC(SYSDATE,'MONTH') FROM DUAL;

SELECT TRUNC(SYSDATE,'YEAR') FROM DUAL;

SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy')) FROM DUAL;

SELECT TRUNC( TO_DATE('07/15/2016', 'mm/dd/yyyy'),'MONTH') FROM DUAL;

SELECT TRUNC(TO_DATE('07/15/2016', 'mm/dd/yyyy'),'YEAR') FROM DUAL;

Vídeo 1

Vídeo 2

Banco de Dados II – Aula 02

LINHAS DUPLICADAS

  • Geralmente, o problema de linhas duplicadas (ou valores duplicados) no retorno da instrução SELECT deve-se à má modelagem de dados;
  • O ideal seria que não existissem valores duplicados em tabelas;
  • Podemos utilizar o commando DISTINCT para fazer a distinção desses valores;
  • Utilizamos quando temos valores repetidos entre as tuplas;
  • Não se coloca geralmente a chave primária na cláusula SELECT;
  • O DISTINCT é aplicado para todas as colunas na cláusula SELECT;
  • SINTAXE:
SELECT   DISTINC [NOME_COLUNA]
FROM   [NOME_TABELA]
  • EXEMPLO:
SELECT   DISTINCT DEPARTMENT_ID
FROM     employees

SELECT   DISTINCT DEPARTMENT_ID,   JOB_ID
FROM     employees
  • Aconselhado utilizar somente para análise de dados;
  • É um comando que consome muito processamento;
  • O uso intensivo pode causar degradação do processador;

RESTRINGINDO E CLASSIFICANDO DADOS

A CLÁUSULA WHERE SERVE PARA:

  • Limitar linhas recuperadas de uma consulta;
  • Aconselhado sempre utilizar o que for possível para se fazer os filtros de retorno de linhas;
  • É especificado após a cláusula FROM;
  • Suporta comparer colunas, literais, variáveis, expressões aritméticas ou funções;
  • É necessário ter: o nome_da_coluna, condição_de_comparação; Nome_da_coluna, constant ou lista de valores;
  • É opcional na SELECT, porém aconselhável utilizar;
  • Caso não seja utilizada, é tornado todas as linhas da tabela, podendo causar grande lentidão no retorno de dados;
  • EXEMPLO:
  • SELECT  * FROM  employees (será retornado todas linhas da tabela employees)
  • EXEMPLO:
  • SELECT  *FROM  employeesWHERE  department_id = 60;(será retornado somente as linhas que contém o department_id = 60)

CONDIÇÕES DE COMPARAÇÃO

  • Servem para fazer comparações de valores dentro da cláusula WHERE;
  • Os possíveis operadores que podem ser utilizados: =, >, =, <=, ou !=, BETWEEN AND, IN, LIKE, IS NULL
  • SINTAXE:Geralmente é utilizado:… WHERE [campo] [operador] [valor];
  • Podendo ser também:… WHERE [valor] [operador] [campo]; ou… WHERE [valor] [operador] [valor]; ou… WHERE [campo] [operador] [campo]; etc
  • EXEMPLOS:… WHERE job_id = ‘AD_VP’… WHERE1000 >= salary;… WHERE department_id != 80;… WHERE hire_date  < ’17-jun-03’;
  • BETWEEN
    • Serve para retornar linhas entre faixas de valores;
    • Os dados podem ser do tipo, numérico, data e caracteres;
    • Substitui as comparações “>= AND <=“
    • EXEMPLO:…WHERE employee_idBETWEEN 1 AND 200;seria o mesmo que:…WHERE employee_id >= 1AND employee_id <= 200;
    • EXEMPLO:…WHERE hire_date BETWEEN’15-JAN-03’ AND ’30-JAN-03’;… WHERE last_name BETWEEN‘A’ AND ‘E’;
  • CONDIÇÃO IN
    • Retorna os valores que estão dentro da lista do IN;
    • Pode ser usado por qualquer tipo de dado;
    • Substitui os comandos [CAMPO1] = [VALOR1] OR [CAMPO1] = [VALOR2] OR …;
    • EXEMPLO:SELECT  first_name, last_nameFROM   employeesWHERE   first_name IN (‘Steven’,’Neena’);
    • EXEMPLO:SELECT  first_name, last_name, employee_idFROM   employeesWHERE   employee_id IN (1, 100, 200, 300);
  • A CONDIÇÃO LIKE:
    • Serve para retornar linhas que sejam parecidas (fazendo parte) com a string desejada;
    • Geralmente usado quando não sabemos exatamente o que estamos procurando, mas sabemos pelo menos parte da string;
    • Possui 2 símbolos (coringas) que geralmente são utilizados em conjunto com o LIKE:
    • % –> representado por zero ou mais caracteres;
    • _ –> representado por um character;
    • Quando queremos procurar uma string que contenha um dos símbolos coringa, temos que utilizar a opção ESCAPE;
    • EXEMPLO:SELECT  first_name, last_nameFROM  employeesWHERE  first_name LIKE ‘A%’(retorna todos os first_name iniciados com A)
    • EXEMPLO:SELECT  first_name, last_nameFROM  employeesWHERE  last_name LIKE ‘%a’(retorna todos os last_name terminados com A)
    • EXEMPLO:SELECT  first_name, last_nameFROM  employeesWHERE  last_name LIKE ‘%th%’(retorna todos os last_name que tenham “th” em qualquer parte da string)
    • EXEMPLO:SELECT  first_name, last_nameFROM  employeesWHERE  first_name LIKE ‘_t%’(retorna todos os first_name que tenham qualquer character na primeira posição da string, contenham “t” na segunda posição da string e que terminem com qualquer caracter)
    • EXEMPLO:SELECT  first_name, last_name, job_idFROM  employeesWHERE  job_id LIKE ‘IT\_%’ ESCAPE ‘\’;(retorna todos os job_id tenham IT_ no inicio e terminem com qualquer/quaisquer character(es). O _ após o símbolo \ está sendo interpretado como um character comum, e não um character coringa)
  • CONDIÇÕES NULL
    • Não é possível utilizar = ou para se comparar NULL;
    • Utiliza-se IS NULL ou IS NOT NULL;
    • EXEMPLO:… WHERE commission_pct IS NOT NULL;… WHERE hire_date IS NULL;… WHERE phone_number IS NOT NULL;

OPERADORES LÓGICOS E REGRAS DE PRECEDÊNCIA

OPERADORES LÓGICOS

  • Combina duas ou mais condições na cláusula WHERE;
  • O retorno da SELECT será sempre o resultado verdadeiro;
  • São eles: AND, NOT e OR;

OPERADOR AND

  • Significa E;
  • Retornará linhas na SELECT se todas as condições forem verdadeiras;
  • Uma única condição falsa invalída esse operador;
  • EXEMPLO DE ANDSELECT   *FROM   employeesWHERE   salary >= 5000AND job_id = ‘AC_ACCOUNT’;

OPERADOR OR

  • Significa OU;
  • Retornará linhas na SELECT se todas ao menus uma condição for verdadeiras;
  • Uma única condição falsa talvez não invalíde esse operador;
  • EXEMPLO DE ANDSELECT   *FROM   employeesWHERE   salary >= 5000OR job_id = ‘AC_ACCOUNT’;

OPERADOR NOT

  • Significa NÃO;
  • Utiliza em conjunto com as condições de comparação BETWEEN, LIKE, IS NULL e IN;
  • O operador irá retornar o que NÃO for atendido pela condição dada na SELECT;
  • Pode ser interpretado como: o que não estiver em (IN), o que não estiver entre (BETWEEN), o que não for como (LIKE);
  • EXEMPLO DE NOTSELECT   *FROM   employeesWHERE   first_name NOT LIKE ‘Bruce’;SELECT   *FROM   employeesWHERE   job_id NOT IN   (‘IT_PROG’,’AD_VP’,’FI_ACCOUNT’);
  • EXEMPLO DE NOTSELECT   *FROM   employeesWHERE   salary NOT BETWEEN5000 AND 20000;
  • EXEMPLO DE NOTSELECT   *FROM   employeesWHERE   NOT salary  > 10000;

ORDEM DE PRECEDÊNCIA DE OPERADORES

  • Obedecem a sequencia na instrução SELECT que será resolvida as expressões;
  • Pode ser sobreposta a ordem com parenteses;
  • As expressões são resolvidas da esquerda para a direita;

ScreenHunter_316 Feb. 26 00.02

  • EXEMPLO DE ORDEM DE PRECEDÊNCIA:
  • SELECT   *FROM   employeesWHERE   salary  > 5000AND job_id = ‘IT_PROG’;
  • SELECT   *FROM   employeesWHERE   job_id = ‘IT_PROG’OR job_id = ‘SA_MAN’;
  • SELECT   *FROM   employeesWHERE   first_name = ‘Bruce’OR manager_id = 100AND job_id = ‘SA_MAN’;

CLÁUSULA ORDER BY

  • Serve para ordenar as linhas recuperadas;
  • O padrão é ordem crescente (ascendente);
  • A ordenação é a última cláusula de uma SELECT a ser executada;
  • Podem ser usados ASC (opcional), pois é o padrão, ou DESC (descendente) em conjunto com a cláusula ORDER BY;
  • Pode ser utilizado para ser ordenado mais de uma coluna, inclusive de diferente ordens na mesma instrução;
  • EXEMPLOS DE ORDER BY
  • SELECT   first_name,last_name,job_idFROM   employeesORDER BY   first_name;
  • SELECT   first_name,last_name,job_idFROM   employeesORDER BY   last_name DESC;
  • SELECT   first_name,last_name,job_idFROM   employeesORDER BY   first_name ASC,last_name DESC;

Vídeo 01

Video 02

Banco de Dados II – Aula 01

REVISÃO DE INSTRUÇÕES BÁSICAS DO COMANDO SELECT

SELECT POSSUI 3 RECURSOS BASICAMENTE:

  • PROJEÇÃO: Escolha das colunas que serão retornadas;
  • SELEÇÃO: Limitação de linhas que irão ser retornados;
  • JOIN: Junção entre tabelas de acordo com o modelo relacional;

SELECT BÁSICO PARA O ORACLE:

SELECT [NOME_DA_COLUNA]
FROM   [NOME_DA_TABELA]

(Obrigatório ter uma tabela para retorno)

INSTRUÇÕES BÁSICAS DO COMANDO SELECT

  • Ao utilizar o caracter “*” após o comando SELECT, será retornado todas as colunas da tabela;
  • Caso esteja utilizando JOIN, será retornado todas as colunas de todas as tabelas;

SELECIONANDO COLUNAS ESPECÍFICAS

  • Necessário declarar dentro do SELECT
SELECT [NOME_COL1], [NOME_COL2], …
FROM   [NOME_TABELA];

INSTRUÇÕES SELECT

  • NÃO SÃO CASE-SENSITIVE;
  • Os dados SÃO CASE-SENSITIVE;
  • Podem ocupar uma ou mais linhas;
  • Para melhor indentação, as cláusulas são colocadas em linhas diferentes;
  • Para o iSQL-Plus, o terminador é “;” (opcional) para um único comando;
  • Para o SQL-Plus, o terminador é “;” (obrigatório)

FORMATAÇÃO SUGESTIVA

SELECT   [NOME_COL1],
         [NOME_COL2],
         [NOME_COL3], …
FROM     [NOME_TABELA]
WHERE    [CONDIÇÃO1]
         AND  [CONDIÇÃO2]
         OR  [CONDIÇÃO3];

EXPRESSÕES ARITMÉTICAS

  • Podemos utilizar os operadores aritméticos dentro da instrução SELECT, basicamente para manipular dados do tipo numéricos e datas;
  • Basicamente são:
    • + –>  soma
    • – –> subtração
    • * –> multiplicação
    • / –> divisão
  • Utilizado para modificar (em uma SELECT, somente em visualização) os dados no retorno de uma SELECT;
  • Podem ser utilizadas na cláusula SELECT, WHERE, ORDER BY, GROUP BY (EXCETO na cláusula FROM)EXEMPLO DE UTILIZAÇÃO
SELECT   first_name,
         salary,
         salary + 300                    --> CAMPO VIRTUAL
FROM     employees;

ORDEM DOS OPERADORES

  • Caso ocorra de uma expressão aritmética conter vários operadores, a ordem é seguida pela regra matemática;
    • * e / –> primeira procedência;
    • + e – –> segunda procedência;
    • Operadores com a mesma prioridade, a operação é resolvida da esquerda para a direita;
    • Pode-se inverter a prioridade dos operadores utilizando os simbolos parênteses “(” e “)”;
    • EXEMPLOS
      10 + 300 / 50 = 16
      (10 + 300) / 50 = 6,2
      200 + 300 / 5 *4 = 440
      (200 + 300) / 5 * 4 = 400

 VALORES NULOS

  • É diferente do valor 0 (zero);
  • É diferente de “ ” (espaço em branco);
  • É a ausência total de um dado;
  • Não conseguimos fazer operações (soma, multiplicação, divisão) com valores nulos;
  • Qualquer tipo de dado pode ser nulo;
  • É possível impeder valores nulos com CONSTRAINTS (restrições);
  • As CONSTRAINTS são PRIMARY KEY e NOT NULL;
  • O ideal seria que não existisse valores nulos em nenhum campo, porém maioria das regras de negócio impedem essa teoria;
  • A utilização de campos com valores nulos em expressões aritméticas resultará em nulo;
  • Mesmo em tipos de variáveis booleanas, o nulo não pode ser comparado, ou seja, um valor nulo é diferente de TRUE e FALSE;

ALIAS

    • É um apelido que podemos dar para uma coluna, ou seja, renomeamos em tempo de execução os nomes de colunas para melhor serem apresentados ao usuário;
    • Utilizado geralmente em colunas que contém cálculo;
    • O ALIAS NÃO altera o nome real da coluna que está na tabela;
    • Coloca-se o ALIAS depois do nome da coluna e antes da virgula (que separa colunas na cláusula SELECT), opcionalmente com a palavra “AS”;
    • Em caso do alias precisar utilizer espaço em branco ou caracteres especiais (“#” ou “$”), é necessário colocar entre aspas duplas (“ ”);
    • EXEMPLO:
      SELECT  first_name AS Nome,
      salary * 12 "Salario Anual",   --> CONTÉM UM ESPAÇO NO TEXTO ENTRE ASPAS
      last_name as “sobre#nome”
      FROM   employees;

CONCATENAÇÃO

  • Serve para fazer a junção de colunas com outras colunas, e/ou variáveis, e/ou constantes;
  • É criado uma coluna virtual, que o retorno será uma string, resultando uma única coluna;
  • Concatentando uma string com nulo, o resultado será somente a string;
  • Utilize os caracteres PIPE “||” entre as colunas ou os valores que se deseja concatenar;
  • Pode-se concatenar campos com diferentes tipos (aconselhado fazer cast de dados para string);
  • EXEMPLO:
SELECT  first_name || ' ' || last_name
        AS NOME_COMPLETO
FROM    employees;

STRING / LITERAIS

  • É um campo de qualquer tipo (número, string, data) concatenado com uma string de uma SELECT;
  • Campos do tipo caractere ou data é necessário utilizar aspas simples;
  • Cada string de caractere é retornada em todas as linhas retornadas da SELECT;

DELIMITADORES

      • Quando queremos mostrar um character de comando dentro de uma string, utilizamos o delimitadores; Por exemplo, o caracter ” ‘ ” (aspas simples);
      • Para isso, utilizamos o (q), e um delimitador podendo ser [ ], , { }, (), etc.
      • EXEMPLO:
        SELECT first_name || ' tem o código da função ' || job_id
        FROM   employees;
        
        SELECT first_name || ' tem o código da função ' || job_id     
               as "Concatenação de string"
        • EXEMPLO COM QUOTA:
    SELECT job_id || q'['s]'
    FROM   employees;
    
    SELECT job_id || q'['s é a funcao de ]' || first_name
    FROM   employees;
    
  • Exercícios – Funções com Strings

    Utilize o schema HR do banco de dados Oracle 11g XE

    Exercícios – Funções com Strings
    Utilize o schema HR do banco de dados Oracle 11g XE

    1. Selecione o nome e sobrenome dos funcionários em um único campo chamado nome completo, o email todo em minusculo com a extensão @orcl.com.br chamado email_corporativo de todos os funcionários que estejam entre a letra A e M, considerando a primeira letra do nome, por ordem do nome completo;

    2. Selecione o sobrenome dos funcionários, a quantidade de caracteres do sobrenome, que a quantidade de caracteres do sobrenome esteja entre 3 a 6;

    3. Selecione o nome dos funcionários, o email do funcionário em minusculo, o salário do funcionário, a qual a primeia letra do email seja “a” e que tenha mais de 3 caracreres o email;

    4. Selecione o nome, o job_id e o salário de todos os funcionários que tenha o job_id st_man e st_clerk, mostrando a seguinte frase exemplo:Daniel, que tem a função de Fi_Account, tem o salário de $9.000,00, colocando um apelido mais amigável para esta coluna;

    5. Selecione o nome e sobrenome dos funcionários (em um único campo), a data de admissão adicionada mais 1 ano colocando um apelido de férias_previstas, mostrando a seguinte frase: Michael Rogers tem a previsão de férias programadas para 12/12/2008;

 

Vídeo1

Video 2

Video 3

Using T-SQL in a relational way

(P. 5) Como mencionado, o T-SQL é baseado em SQL, que por sua vez é baseado no modelo relacional. Contudo,há várias maneiras pelas quais o SQL e, portanto, o T-SQL, se desviam do modelo relacional. Mas o T-SQL oferece ferramentas suficientes para que, se você entenda o modelo relacional, e você pode usar a linguagem de forma relacional e, assim, escrever um código mais correto.

Lembre-se de que uma relação tem um título e um corpo. O título é um conjunto de atributos e o corpo é um conjunto de tuplas. Lembre-se de que um conjunto deve ser considerado como um todo.

(P. 6) O que isso se traduz em T-SQL é que você deveria escrever consultas que interagem com as tabelas como um todo. Você deve tentar evitar o uso de construções iterativas como cursores e loops que iteram pelas linhas uma de cada vez. Você também deve tentar evitar pensar em termos iterativos porque esse tipo de pensamento é o que leva a soluções iterativas.Para pessoas com um pouco de programação processual, a maneira natural de interagir com dados (em um arquivo, conjunto de registros ou leitor de dados) é com iterações. Então, usar cursores e outras construções iterativas no T-SQL é, de certo modo, uma extensão para o que eles já conhecem. No entanto, a maneira correta da perspectiva do modelo relacional não é interagir com as linhas uma de cada vez, em vez disso, usar operações relacionais e retornar um resultado relacional. Isso, no T-SQL, se traduz em questões da escrita.

Executando uma Select através do Excel

É possível utilizar o Excel para retornar resultados através de uma select.

No exemplo seguinte, iremos utilizar o banco de dados de exemplo AdventureWorks2008R2, que pode ser encontrado facilmente na internet.

No SSMS, executaremos a seguinte select (simples)

SELECT W.WorkOrderID, P.Name, W.OrderQty, P.StandardCost,  
       W.OrderQty * P.StandardCost as SubTotal 
FROM   Production.WorkOrder W
       INNER JOIN Production.Product P
         ON W.ProductID = P.ProductID

O resultado da consulta será o seguinte:

 

ScreenHunter_446 Jan. 23 10.58

No Excel 2013 ou 2016, abra uma nova planilha, clique no menu Dados, De Outas Fontes, Do SQL Server

ScreenHunter_447 Jan. 23 11.01

Preencha corretamente os dados de conexao, conforme o assistente de conexão pede:

Captura de tela 2018-01-23 11.03.05

Escolha do banco de dados AdventureWorks2008R2, tire a marcação de “Conectar a uma tabela específica”, e clique em [avançar]

Captura de tela 2018-01-23 11.04.27

Caso queira, altere o nome do arquivo de conexão, descricao e nome amigãvel, e clique em [Concluir]

Captura de tela 2018-01-23 11.06.32

Temporariamente, escolha uma visão a ser selecionada e clique em [OK]

Captura de tela 2018-01-23 11.08.25

Clique na opcao Proriedades, para que o Excel abra as Propriedades da Conxão.

Captura de tela 2018-01-23 11.08.50

Nas propriedades da Conexão, na aba Uso, é possível configurar por exemplo, a cada X minutos que irá ser atualizada.

Captura de tela 2018-01-23 11.14.29

Na aba Definição, altere o tipo de comando para SQL, e o Texto de comando, substitua para a Select que foi desenvolvida:

Captura de tela 2018-01-23 11.15.57

Clique em [OK] para finalizar.

A mensagem seguinte poderá ser exibida. CLique em [Sim]

Captura de tela 2018-01-23 11.16.31

O Excel voltará para a tela de inportar dados. Selecione a celula =$A$1

Captura de tela 2018-01-23 11.18.28

Dessa forma, temos os dados exibidos em planilha, a qual estes são atualizados dinamicamente, de tempos em tempos conforme configuração da conexão ou clicando sobre o botão Atualizar.

ScreenHunter_448 Jan. 23 11.20

O ideal é fazer a criação de uma View dentro do SQL Server e fazer com que o usário tenha acesso m´nimo de permissão, não deixando que usuários comuns tenham acesso a qualquer outro tipo de objeto (a não ser a view cirada)

Após importado os dados, é possível criar as formulas normalmente do Excel.

 

 

 

Utilizando CASE com IN no SQL Server

No SQL Server, é possível utilizar o recurso IN dentro de um Case.

Para ilustrar o exemplo, utilizamos o banco de dados Adventure Works 2008, e primeiro podemos observar o uso típico do case:

SELECT	ProductID, 
	Name, 
	Color,
	case Color 
	    when 'Red'    then 'Cor Primaria'
	    when 'Blue'   then 'Cor Primaria'
	    when 'Yellow' then 'Cor Primaria'
	    else 'Cor Secundaria'
	end as TesteCase
FROM	Production.Product
WHERE	Color is not null

Temos o seguinte resultado:

ScreenHunter_440 Jan. 17 08.49

Observamos que para o caso das cores serem Red, Blue e Yellow, os resultados seriam os mesmos.

Podemos fazer a alteração utilizando o comando IN dentro do Case, como o código seguinte:

SELECT	ProductID, 
	Name, 
	Color,
	case WHEN 
	    Color in ('Red','Blue','Yellow') then 'Cor Primaria'
	    else 'Cor Secundaria'
	end as TesteCase
FROM	Production.Product
WHERE	Color is not null

Observamos que conseguimos otimizar a escrita do código, não ficando tão repetitivo, tendo o mesmo resultado:

ScreenHunter_440 Jan. 17 08.49

Banco de Dados I – Aula 16A

Exercícios SQL

A empresa ABC VENDASCAR LTDA é uma emprea que atua em vendas de carros seminovos e usados multimarcas, principalmente em São Paulo (Capital) e no grande ABC.

Atualmente a empresa implantou o seguinte sistema para melhor controle seus orcamentos e suas vendas. Como a empresa NÃO faz vendas exclusiva de um fabricante, ela optou por trabalhar com veículos dos fabricantes Volkswagem, Chevrolet (GM), Ford e Fiat, para que não fique preso à uma única marca.

O cliente pode ser tanto pessoa física ou juridica, a qual na compra de mais de um carro em uma única negociação, é possível ter descontos mais vantajosos.

Primeiro é necessário que o cliente tenha um pré-cadastro no sistema para que os vendedores apresentem um orcamento prévio. Caso seja aprovado o orcamento, aí sim o orcamento se transforma em uma venda efetiva.

A empresa tem o seguinte esquema de tabelas em seu banco de dados:

FABRICANTE (COD_FAB, DESC_FAB)

MODELO (COD_MOD, DESC_MOD, COD_FAB)

COR (COD_COR, DESC_COR)

VEICULO (COD_VEI, COD_MOD, ANO_FABRICACAO, ANO_MODELO, VALOR_CUSTO, VALOR_VENDA, COD_COR)

CIDADE (COD_CID, DESC_CID, UF)

PLACA_VEICULO (COD_VEI, PLACA, COD_CID)

CLIENTE (COD_CLI, NOME_CLI, TIPO_PESSOA, COD_CID)

ORCAMENTO (COD_ORC, DATA_ORC, VALIDADE_ORC, COD_CLI)

ITEM_ORCAMENTO (COD_ORC, ITEM_ORC, COD_VEI, QUANTIDADE, VALOR_ORC, VALOR_DESCONTO)

VENDA (COD_VENDA, COD_ORC, DATA_VENDA)

ITEM_VENDA (COD_VENDA, ITEM_VENDA, COD_VEI, QUANTIDADE, VALOR_VENDA)

 

Baseado no cenário descrito, faça os exercícios em SQL e também crie o schema VENDASCAR, com as tabelas descritas acima. Por enquanto, as tabelas irão estar sem os relacionamentos necessários, porém tenha ciência de que os dados precisam ter relação entre si. Aconselhável fazer em dupla, onde 1 aluno pode fazer a criação do schema e tabelas e outro pode fazer as consultas SQL.

  1. Selecione o nome do veículo que tenha menor custo do estoque; (função de grupo)
  2. Liste todos os nomes de fabricantes de veículos que tiveram orcamentos feitos no período de 01/06/2017 a 30/06/2017;
  3. Selecione o cliente que mais orçou veículos no período de 01/01/2017 a 30/06/2017;
  4. Selecione as cores dos 5 veículos mais orçados período de 01/01/2017 a 30/06/2017; (pesquisar sobre limitação de registros)
  5. Liste as cidades dos carros vendidos dos fabricantes Volkswagem e Chevrolet (GM), no período de venda de 01/01/2017 a 31/12/2017;
  6. Liste os veículos (placa, cidade, fabricante, modelo) da cidade de Jundiaí, Campinas e São Paulo que foram vendidos entre 01/07/2017 a 31/12/2017;
  7. Selectione os orcamentos feitos (Código do orcamento, data, fabricante, modelo, quantidade, valor, desconto, subtotal (quantidade * valor – desconto) feitos no período do ano de 2016;
  8. Selecione os 5 veículos mais vendidos no período do ano de 2016; (use funções de Grupo)
  9. Selecione os orcamentos feitos no período de 01/01/2017 a 31/03/2017, porém que não foram fechados;
  10. Selecione a média de vendas de junho, julho e agosto de 2017; (função de grupo)

 

Vídeo 01

 

Vídeo 02

 

Banco de Dados I – Aula 15A

FUNÇÕES SQL

As funções são um recurso avançado do SQL e podem ser usadas para realizar:

– Executar cálculos usando dados;

– Modificar itens de dados individuais;

– Manipular saída para grupos de linhas;

– Formatar datas e números para exibição;

– Converter tipos de dados de coluna;

As funções SQL podem aceitar argumentos e sempre retornar um valor;

Há dois tipos de funções:

– Funções de uma única linha;

– Funções de várias linhas;

FUNÇÕES DE UMA ÚNICA LINHA

Essas funções operam somente linhas únicas e retornam um resultado por linha. Há dois tipos diferentes de funções de uma única linha.

– Caractere;

– Número;

– Data;

– Conversão;

Essas funções manipulam grupos de linha a fim de obter um resultado por grupo de linhas.

– Manipulam itens de dados;

– Aceitam argumentos e retornam um valor;

– Agem em cada linha retornada;

– Retornam um resultado por linha;

– Podem modificar o tipo de dados;

– Podem ser aninhadas;

Um argumento pode ser:

– Constante fornecida pelo usuário;

– Valor variável;

– Nome da coluna;

– Expressão;

Os recursos de funções de uma única linha:

– Atuam em cada linha retornada na consulta;

– Retornam um resultado por linha;

– Podem retornar um valor de dados de um tipo diferente do mencionado;

– Podem esperar um ou mais argumentos;

– Podem ser usados em cláusulas SELECT, WHERE e ORDER BY; podem ser aninhados;

– Funções de caractere: aceitam entrada de caractere e podem retornar valores de número e caractere;

– Funções numéricas: aceitam entrada numérica e reotrnam valores numéricos;

– Funções de data: operam sobre valores do tipo de dados data (todas as funções de data retornam data, exceto a função MONTHS_BETWEEN, que retorna um número)

– Funções de conversão: Convertem um valor de tipo de dados para outro;

– Funções gerais:

  • Função NVL;
  • Função DECODE;

FUNÇÕES DE CARACTERE

Podem ser divididas em duas características:

–Funções de conversão de maiúscula e minúscula;

  • LOWER, UPPER e INITCAP

–Funções de manipulação de caractere;

  • CONCAT, SUBSTR, LENGTH, INSTR, LPAD, TRIM

FUNÇÕES DE CONVERSÃO DE MAIÚSCULAS / MINÚSCULAS

– LOWER: Converte string de caracteres com letras maiúsculas e minúsculas ou só maiúsculas para letras minúsculas;

– UPPER: Converte string de caracteres com letras maiúculas e minúsculas ou só minúsculas para letras maiúsculas;

– INITCAP: Converte a primeira letra de cada palavra para maiúscula e mantém as outras letras em minúsculas;

SQL> SELECT UPPER(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT LOWER(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT INITCAP(NMDEPTO)
2  FROM DEPTO;

SQL> SELECT UPPER(NMDEPTO)
2  FROM DEPTO
3  WHERE UPPER(NMDEPTO) LIKE '%A%';

FUNÇÕES DE MANIPULAÇÃO DE CARACTERE

– CONCAT, SUBSTR, LENGTH, INSTR, LPAD e TRIM são as seis funções de manipulação de caractere;

– CONCAT: Une valores de junção (Voce está limitado a usar dois parâmetros com CONCAT);

– SUBSTR: Extrai uma string de determinado tamanho;

– LENGTH: Exibe o tamanho de uma string como um valor numérico;

– INSTR: Localiza a posição numérica do caractere nomeado;

– LPAD: Preenche o valor do caractere justificado à direita;

– RPAD: Preenche o valor de caractere justificado à esquerda;

– TRIM: Organiza cabeçalho ou caracteres de fim de linha (ou os dois) a partir de uma string de caractere;

SQL> SELECT CONCAT(NRMATRIC, NMFUNC),
2          LENGTH(NMFUNC),
3          INSTR(NMFUNC,’A’)
4  FROM FUNCIONARIO
5  WHERE SUBSTR(NMDEPTO,1,2) = 'JO';

FUNÇÕES NUMÉRICAS

ROUND: Arredonda valor para determinado decimal;

  •   ROUND(45.926,2) –> 45.93

TRUNC: Trunca valor para determinado decimal;

  •   ROUND(45.926,2) –> 45.92

MOD: Retorna o restante da divisão;

  •   MOD(1600,300) –> 100

USANDO A FUNÇÃO ROUND

SQL> SELECT ROUND(45.923,2),
2       ROUND(45.923,0),
3       ROUND(45.923,-1)
4  FROM DUAL;

USANDO A FUNÇÃO TRUNC

SQL> SELECT TRUNC(45.923,2),
2       TRUNC(45.923),
3       TRUNC(45.923,-1)
4  FROM DUAL;

USANDO A FUNÇÃO MOD

SQL> SELECT MOD(VRSALARIO,2)
2  FROM CARGO;

TRABALHANDO COM DATAS

O Oracle armazena datas em um formato numérico interno: século, ano, mês, dia, horas, minutos, Segundo;

O formato de data default é DD-MON-YY;

SYSDATE é uma função de retorno de data e hora;

DUAL é uma tabela fictícia usada para visualizar SYSDATE;

EXIBINDO A DATA ATUAL

SQL> SELECT SYSDATE
2  FROM DUAL;

ARITMÉTICA COM DATAS

Adicionar ou subtrair um número de, ou para, uma data para um valor de data resultante;

Subtrair duas datas a fim de localizer o número de dias entre estras datas;

Adicionar horas por uma data dividindo o número de horas por 24;

ARITMÉTICA COM DATAS

ScreenHunter_298 Nov. 11 19.31

USANDO OPERADORES ARITMÉTICOS COM DATAS

SQL> SELECT NMFUNC,
2    (SYSDATE – DTADM) / 7 SEMANA
3  FROM FUNCIONARIO;

FUNÇÕES COM DATAS

ScreenHunter_299 Nov. 11 19.32

FUNÇÕES COM DATAS

MONTHS_BETWEEN('01-SEP-95','11-JAN-94')
--> 19.6774194

ADD_MONTHS('11-JAN-96',6)
--> ’11-JUL-94’

NEXT_DAY('01-SEP-95','FRIDAY')
--> '08-SEP-95'

LAST_DAY('01-SEP-95')
--> '30-SEP-95'

ROUND('25-JUL-95','MONTH')
--> 01-AUG-95

ROUND('25-JUL-95','YEAR')
--> 01-JAN-96

TRUNC('25-JUL-95','MONTH')
--> 01-JUL-95

TRUNC('25-JUL-95','YEAR')
--> 01-JAN-95

 

Vídeo 01

Banco de Dados I – Aula 14C

EXERCÍCIOS: Utilize o Schema HR do Oracle para resolver os problemas abaixo:

hr

1 – Selecione todos os ids, nomes e sobrenomes de empregados, que estejam nas faixas de salários de 0 a 2000 e maiores ou iguais a 5000. Dê 3 tipos de consultas para resolver esse problema)

2 – Selecione todos os ids e nomes de departamentos, o id do gerente do departamento que estejam nos id de locais entre 1500 a 2000;

3 – Selecione todos os nomes de departamentos e nomes dos gerentes de departamentos onde a cidade de localização seja Sao Paulo (faça pela antiga e nova sintaxa. Se possível, resolva também em algebra relacional)

4 – Selecione todos os ids, nomes e sobrenome de empregados que possuem salario maior que 5000 (Dê duas soluções para este problema. Se possível, resolva também em algebra relacional)

5 – Selecione todos ids, nomes e sobrenomes (como um único campo – nome completo) de empregados, a data de admissao (hire date),data de inicio e fim (end_date) dos determinados serviços (jobs). Obs: um empregado pode ter trabalhado em mais de um servico em um período de tempo; (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)

6 – Selecione todos o nome da região, o nome do pais que os nomes de paises iniciem com as letras A,B,J (Dê 2 soluções para esse problema. Se possível, resolva também em algebra relacional)

7 – Selecione todos os id´s e nomes dos gerentes, ids e nomes de departamentos gerenciados por esses gerentes. (De 3 soluções para esse problema)

8 – Selecione todos os ids e nomes de departamentos, a qual não possuem nenhum empregado alocado (dê 2 soluções para esse problema);

9 – Selecione todos os ids e nomes de empregados, o nome do departamento, o nome do pais e o nome da região, onde as regiões devem ser iguais a Europe e Asia (dê 3 soluções para esse problema);

Vídeo 01

 

Banco de Dados I – Aula 14B

USANDO O SCHEMA HR DO ORACLE

hr

JUNÇÃO DE TABELA (SINTAXE ANTIGA)

SQL> SELECT tabela1.coluna, tabela2.coluna
  2  FROM tabela1, tabela2
  3  WHERE tabela1.coluna = tabela2.coluna;

 

JUNÇÃO DE TABELA (SINTAXE NOVA)

SQL> SELECT tabela1.coluna, tabela2.coluna
  2  FROM tabela1
  3    INNER JOIN tabela2
  4      ON tabela1.coluna =
  5                  tabela2.coluna;

 

GERANDO UM PRODUTO CARTESIANO (SINTAXE ANTIGA)

Gera-se um produto cartesiano caso uma condição de junção seja omitida.

SQL> SELECT FIRST_NAME,
  2         DEPARTMENT_NAME
  3  FROM   EMPLOYEES,
  4         DEPARTMENTS;

 

GERANDO UM PRODUTO CARTESIANO (SINTAXE NOVA)

SQL> SELECT FIRST_NAME,
  2         DEPARTMENT_NAME
  3  FROM   EMPLOYEES
  4         CROSS JOIN DEPARTMENTS;

 

GERANDO REGISTROS COM JUNÇÕES IDÊNTICAS (SINTAXE ANTIGA)

SELECT  FIRST_NAME,
        EMPLOYEES.DEPARTMENT_ID,
        DEPARTMENTS.DEPARTMENT_ID,
        DEPARTMENT_NAME
FROM    EMPLOYEES, DEPARTMENTS
WHERE   EMPLOYEES.DEPARTMENT_ID =
                  DEPARTMENTS.DEPARTMENT_ID

 

GERANDO REGISTROS COM JUNÇÕES IDÊNTICAS (SINTAXE NOVA)

SELECT  FIRST_NAME,
        EMPLOYEES.DEPARTMENT_ID,
        DEPARTMENTS.DEPARTMENT_ID,
        DEPARTMENT_NAME
FROM    EMPLOYEES
        INNER JOIN DEPARTMENTS
          ON EMPLOYEES.DEPARTMENT_ID =
            DEPARTMENTS.DEPARTMENT_ID

 

CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND (SINTAXE ANTIGA)

SELECT   EMPLOYEE_ID, FIRST_NAME,
         DEPARTMENT_NAME
FROM     EMPLOYEES, DEPARTMENTS
WHERE    EMPLOYEES.DEPARTMENT_ID =
           DEPARTMENTS.DEPARTMENT_ID
         AND FIRST_NAME = 'Diana';

 

CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND (SINTAXE NOVA)

SELECT   EMPLOYEE_ID, FIRST_NAME,
         DEPARTMENT_NAME
FROM     EMPLOYEES
         INNER JOIN DEPARTMENTS
           ON EMPLOYEES.DEPARTMENT_ID =
             DEPARTMENTS.DEPARTMENT_ID
WHERE   FIRST_NAME = 'Diana';

 

UNINDO MAIS DE DUAS TABELAS (SINTAXE ANTIGA)

SELECT   E.FIRST_NAME,
         J.JOB_TITLE,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         JOBS J,
         DEPARTMENTS D
WHERE    E.JOB_ID = J.JOB_ID
         AND E.DEPARTMENT_ID =   D.DEPARTMENT_ID;

 

UNINDO MAIS DE DUAS TABELAS (SINTAXE NOVA)

SELECT   E.FIRST_NAME,
         J.JOB_TITLE,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E
         INNER JOIN JOBS J
           ON E.JOB_ID = J.JOB_ID
         INNER JOIN DEPARTMENTS D 
           ON E.DEPARTMENT_ID =   D.DEPARTMENT_ID;

 

JUNÇÕES NÃO-IDÊNTICAS (SINTAXE ANTIGA)

SELECT    E.FIRST_NAME, E.LAST_NAME,
          E.SALARY, J.MIN_SALARY,   J.MAX_SALARY, J.JOB_TITLE
FROM      EMPLOYEES E, JOBS J
WHERE     E.SALARY
          BETWEEN J.MIN_SALARY AND   J.MAX_SALARY;

 

JUNÇÕES NÃO-IDÊNTICAS (SINTAXE NOVA)

SELECT    E.FIRST_NAME, E.LAST_NAME,
          E.SALARY, J.MIN_SALARY,   J.MAX_SALARY, J.JOB_TITLE
FROM      EMPLOYEES E
            JOIN JOBS J
              ON E.SALARY BETWEEN      J.MIN_SALARY AND J.MAX_SALARY;

 

EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE ANTIGA)

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         DEPARTMENTS D
WHERE    E.DEPARTMENT_ID(+) =
                       D.DEPARTMENT_ID;

 

EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE NOVA)

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         LEFT JOIN DEPARTMENTS D
           ON E.DEPARTMENT_ID =
              D.DEPARTMENT_ID;

 

EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE ANTIGA)

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         DEPARTMENTS D
WHERE    E.DEPARTMENT_ID =
           D.DEPARTMENT_ID(+);

 

EXEMPLO DE JUNÇÕES EXTERNAS (SINTAXE NOVA)

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E
           RIGHT JOIN DEPARTMENTS D
             ON E.DEPARTMENT_ID =
                           D.DEPARTMENT_ID;

 

EXEMPLO DE AUTOJUNÇÕES (SINTAXE ANTIGA)

SELECT   WORKER.FIRST_NAME
         ||' TRABALHA PARA ' ||
         MANAGER.FIRST_NAME
FROM     EMPLOYEES WORKER,
         EMPLOYEES MANAGER
WHERE    WORKER.MANAGER_ID =
                MANAGER.EMPLOYEE_ID;

 

EXEMPLO DE AUTOJUNÇÕES (SINTAXE NOVA)

SELECT   WORKER.FIRST_NAME
         ||' TRABALHA PARA ' ||
         MANAGER.FIRST_NAME
FROM     EMPLOYEES WORKER
           LEFT JOIN EMPLOYEES MANAGER
             ON WORKER.MANAGER_ID =
                MANAGER.EMPLOYEE_ID;

Video 01

Banco de Dados I – Aula 14A

EXIBINDO DADOS DE VÁRIAS TABELAS

Há situações em que precisamos buscar dados de várias tabelas, onde um select simples não consegue obter esses dados;

Nesses casos, é necessário utilizar dados a partir de uma ou mais tabelas;

Para isso, precisamos fazer junção de tabelas;

O QUE É JUNÇÃO?

Use a junção para consultar dados a partir de uma ou mais tabelas;

Criar uma condição de junção na cláusula WHERE;

Prefixar o nome da coluna com o nome da tabela quando o mesmo nome da coluna aparecer em mais de uma tabela

Exemplo de junção

SQL> SELECT tabela1.coluna, tabela2.coluna
2  FROM tabela1, tabela2
3  WHERE tabela1.coluna = tabela2.coluna;

 

USANDO O SCHEMA HR DO ORACLE

hr

PRODUTO CARTESIANO

Um produto cartesiano é formado quando:

– Uma condição de junção estiver omitida;

– Uma condição de junção estiver inválida;

– Todas as linhas na primeira tabela estão unidas a todas as linhas da segunda tabelas;

Para evitar um produto Cartesiano, sempre inclua uma condição de junção válida em uma cláusula WHERE;

GERANDO UM PRODUTO CARTESIANO

Gera-se um produto cartesiano caso uma condição de junção seja omitida.

SQL> SELECT FIRST_NAME,
  2           DEPARTMENT_NAME
  3    FROM   EMPLOYEES,
  4           DEPARTMENTS;

 

TIPOS DE JUNÇÃO

Há dois tipos principais de condições de junção:

– Junção idênticas;

– Junção não-idêntcias;

Métodos de junção adicional incluem:

– Junções externas;

– Autojunções;

– Operadores de conjunto;

 

JUNÇÃO IDÊNTICA

ScreenHunter_393 Nov. 06 12.59

RECUPERANDO REGISTROS COM JUNÇÕES IDÊNTICAS

SQL>SELECT  FIRST_NAME,
            EMPLOYEES.DEPARTMENT_ID,
            DEPARTMENTS.DEPARTMENT_ID,
            DEPARTMENT_NAME
    FROM    EMPLOYEES, DEPARTMENTS
    WHERE   EMPLOYEES.DEPARTMENT_ID =
                      DEPARTMENTS.DEPARTMENT_ID;

QUALIFICANDO NOMES DE COLUNA AMBÍGUAS

Use os prefixos de tabela para qualificar nomes de colunas que estão em várias tabelas;

Diferencie colunas que possuem nomes idênticos, mas que residam em tabelas diferentes usando apelidos de coluna;

CONDIÇÕES DE PESQUISA ADICIONAIS USANDO O OPERADOR AND

Além da junção, é possível ter critérios para a cláusula WHERE;

SELECT   EMPLOYEE_ID, FIRST_NAME,
         DEPARTMENT_NAME
FROM     EMPLOYEES, DEPARTMENTS
WHERE    EMPLOYEES.DEPARTMENT_ID =
                       DEPARTMENTS.DEPARTMENT_ID
         AND FIRST_NAME = 'Diana';

USANDO APELIDOS DE TABELA

Apelidos de tabelas podem ter um tamanho de até 30 caracteres;

Se um apelido de tabela for usado para um determinado nome de tabela na cláusula FROM, deve-se utilize-lo para toda a instrução SELECT;

Apelidos devem ser significativos;

O apelido de tabela é válido somente para a instrução SELECT atual;

 

USANDO APELIDOS DE TABELA

SELECT   E.FIRST_NAME,
         J.JOB_TITLE,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         JOBS J,
         DEPARTMENTS D
WHERE    E.JOB_ID = J.JOB_ID
         AND E.DEPARTMENT_ID =   D.DEPARTMENT_ID;

 

JUNÇÕES NÃO-IDÊNTICAS

O relacionamento de junção não-idênticas  significa que não há nenhum coluna correspondente diretamente entre as duas tabelas;

SELECT    E.FIRST_NAME, E.LAST_NAME,
          E.SALARY, J.MIN_SALARY,   J.MAX_SALARY,
          J.JOB_TITLE
FROM      EMPLOYEES E, JOBS J
WHERE     E.SALARY
          BETWEEN J.MIN_SALARY AND   J.MAX_SALARY;

 
Se uma linha não satisfazer uma condição de junção, a linha não aparecerá no resultado da consulta.

ScreenHunter_394 Nov. 06 13.03

Use uma junção externa para consultar também todas as linhas que em geral não atendem à condição de junção.

O operador de junção externo é um sinal de adição (+);

EXEMPLO DE JUNÇÕES EXTERNAS

SELECT   E.FIRST_NAME,
         D.DEPARTMENT_NAME
FROM     EMPLOYEES E,
         DEPARTMENTS D
WHERE    E.DEPARTMENT_ID(+) =
                          D.DEPARTMENT_ID

 
O operador da junção externa pode aparecer somente de um lado da expressão – o lado que possui informações ausentes. Ele retorna estas linhas de uma tabela que não possui correspondênciadireta em outra tabela;

Uma condição envolvendo uma junção externa não pode usar o operador IN ou vincular a outra condição pelo operador OR;

 

AUTOJUNÇÕES

Algumas vezes será necessário unir uma tabela a ela mesma.

ScreenHunter_395 Nov. 06 13.05

EXEMPLO DE AUTOJUNÇÕES

SELECT   WORKER.FIRST_NAME
         ||' TRABALHA PARA ' ||
         MANAGER.FIRST_NAME
FROM     EMPLOYEES WORKER,
         EMPLOYEES MANAGER
WHERE    WORKER.MANAGER_ID =
                       MANAGER.EMPLOYEE_ID;

 

Vídeo 01

Banco de Dados I – Aula 12A

SOBRE O PL/SQL

O PL/SQL (Procedural Language/SQL) é uma extensão do SQL com recursos de design de linguagem de programação, sendo uma extensão de linguagem procedural da Oracle;

Incorpora muitos recursos avançados feitos em linguagem de programação projetadas durante as décadas de 70 e 80;

É uma tecnologia empregada pelo Oracle Server e por algumas ferramentas Oracle;

SCRIPT PARA SER EXECUTADO VIA LINHA DE COMANDO NO SQLPLUS

Salvar em arquivo texto dentro do linux e executar via sqlplus

create tablespace banco1 datafile '/u01/app/oracle/oradata/XE/banco1.dbf' size 10m autoextend on next 5m maxsize 50m online permanent extent management local autoallocate segment space management auto;

create user banco1 identified by banco1 default tablespace banco1 temporary tablespace temp;

grant create session, connect, resource to banco1;

alter user banco1 quota unlimited on banco1;

connect banco1/banco1;

CREATE TABLE DEPTO (
  CDDEPTO CHAR(02) PRIMARY KEY,
  NMDEPTO VARCHAR(30),
  RAMAL NUMBER(3)
);

CREATE TABLE CARGO (
  CDCARGO CHAR(02) PRIMARY KEY,
  NMCARGO VARCHAR(30),
  VRSALARIO NUMBER(8,2)
);

CREATE TABLE FUNCIONARIO (
  NRMATRIC NUMBER(04) PRIMARY KEY,
  NMFUNC   VARCHAR(30),
  DTADM    DATE,
  SEXO     CHAR(01),
  CDCARGO  CHAR(02),
  CDDEPTO  CHAR(02),
  CONSTRAINT FK_FUNC_CDCARGO FOREIGN KEY(CDCARGO) REFERENCES CARGO(CDCARGO),
  CONSTRAINT FK_FUNC_CDDEPTO FOREIGN KEY(CDDEPTO) REFERENCES DEPTO(CDDEPTO)
);

INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C1','COZINHEIRA',350);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C3','AUX ESCRITORIO',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C7','VIGIA',450);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C2','MECANICO',750);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C5','GERENTE',2300);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C4','ESCRITURARIO',600);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C8','PROGRAMADOR',NULL);
INSERT INTO CARGO (CDCARGO, NMCARGO, VRSALARIO) VALUES ('C9','ANALISTA_DE_SISTEMAS',2100);

COMMIT;

INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D1','ADMINISTRACAO',221);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D2','OFICINA',235);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D3','SERVICOS GERAIS',243);
INSERT INTO DEPTO (CDDEPTO, NMDEPTO, RAMAL) VALUES ('D4','VENDAS',258);
COMMIT;

INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1001,'JOAO SAMPAIO','10-AUG-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1004,'LUCIO TORRES','02-MAR-94','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1034,'ROBERTO PEREIRA','23-MAY-92','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1021,'JOSE NOGUEIRA','10-NOV-94','M','C3','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1029,'RUTH DE SOUZA','05-JAN-93','F','C1','D3');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1095,'MARIA DA SILVA','03-SEP-92','F','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1023,'LUIZ DE ALMEIDA','12-JAN-93','M','C2','D2');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1042,'PEDRO PINHEIRO','29-JUL-94','M','C4','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1048,'ANA SILVEIRA','01-JUN-93','F','C5','D1');
INSERT INTO FUNCIONARIO (NRMATRIC, NMFUNC, DTADM, SEXO, CDCARGO, CDDEPTO) VALUES (1015,'PAULO RODRIGUES','17-AUG-92','M','C2','D2');
COMMIT;

INSTRUÇÃO SELECT BÁSICA

SELECT [DISTINCT] {*, coluna [apelido],…}
FROM tabela;

Uma cláusula SELECT especifica as colunas a serem exibidas;

Uma cláusula FROM especifica a tabela que contém as colunas listadas na cláusula SELECT

Na sintaxe:

SELECT –> é a lista de uma ou mais colunas

DISTINCT –> suprime os itens duplicados

* –> selectiona todas as colunas

coluna –> seleciona a coluna nomeada

apelido –> fornece cabeçalhos diferentes às   colunas selecionadas

FROM –> tabela especifica a tabela contend   as colunas

CRIANDO INSTRUÇÕES SQL

Instruções SQL sem distinção entre maiusculas / minusculas;

Instruções SQL podem estar em uma ou mais linhas;

Palavras-chave não podem ser abreviadas ou divididas entre as linhas;

Normalmente, as cláusulas são colocadas em linhas separadas;

Guias e indentações são usadas para aperfeiçoar a legibilidade;

SELECIONANDO COLUNAS

SQL> SELECT *
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO, RAMAL
  2  FROM DEPTO;

SQL> SELECT CDDEPTO, NMDEPTO
  2  FROM DEPTO;

AJUSTANDO CABEÇALHO DAS COLUNAS

SQL> COL "CDDEPTO" FORMAT "A15";

SQL> SELECT *
  2  FROM DEPTO;

A15 –>quantidade de caracteres a ser apresentado

DEFAULTS DE CABEÇALHOS DE COLUNA

Justificativa default

Esquerda: dados de caractere e data

Direita: Dados numéricos

Exibição default: Letra maiúscula

SQL> SELECT cddepto, nmdepto
  2  FROM DEPTO; 

Obs –> mesmo digitando os campos selecionandos em minúsculo, será exibido em maiúsculo

EXPRESSÕES ARITMÉTICAS

Criar expressões com dados NUMBER e DATE usando operadores aritméticos.

OPERADOR DESCRIÇÃO
+ Adicionar
Subtrair
* Multiplicar
/ Dividir

USANDO OPERADORES ARITMÉTICOS

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  VRSALARIO + 1000
   4  FROM CARGO;

PRECEDENCIA DE OPERADORES

A multiplicação e a divisão têm prioridade sobre a adição e a substração;

Os operadores com a mesma prioridade são avaliados da esquerda para a direita;

Os parênteses são usados para forçar a avaliação priorizada e para esclarecer as instruções;

Exemplo 1:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * VRSALARIO + 100
   4  FROM CARGO;

Exemplo 2:

SQL> SELECT CDCARGO, NMCARGO,
   2  VRSALARIO,
   3  12 * (VRSALARIO + 100)
   4  FROM CARGO;

DEFININDO UM VALOR NULO

Um valor nulo não está disponível, não é atribuído, é desconhecido ou não é aplicável;

Um valor nulo não é o mesmo que um zero ou um espaço em branco;

SQL> SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO;

VALORES NULOS NAS EXPRESSÕES ARITMÉTICAS

Se qualquer valor da coluna em uma expressão aritmética for nulo, o resultado será nulo;

SQL> SELECT CDCARGO, NMCARGO, 
  2  VRSALARIO + 100
  3  FROM CARGO;

DEFININDO UM APELIDO (ALIAS) PARA COLUNA

Renomeia um cabeçalho de coluna;

É útil para cálculos;

Segue imediatamente o nome da coluna;

Palavra-chave “as” é opcional entre o nome da coluna e o apelido;

Necessita de aspas duplas caso contenha espaço ou caracteres especiais ou faça a distinção entre maiúscula e minúscula;

USANDO UM APELIDO DE COLUNA

SQL> SELECT CDCARGO AS CODIGO_DO_CARGO, 
   2 NMCARGO AS "Nome do Cargo", 
   3 VRSALARIO VALOR$SALARIO
   4 FROM CARGO;

Observação: Os caracteres especiais # e $ são permitidos serem utilizados em alias sem utilização das aspas duplas;

OPERADOR DE CONCATENAÇÃO

Concatena colunas ou strings de caractere a outras colunas;

É representado por duas barras verticais “||” (pipe);

Cria uma coluna resultante que é uma expressão de caracteres

USANDO UM OPERADOR DE CONCATENAÇÃO

SQL> SELECT CDCARGO || NMCARGO 
   2	AS codigo_nome 
   3  	FROM CARGO;

STRING LITERAIS DE CARACTERES

Uma literal é um caractere, um número ou uma data incluída na lista SELECT;

Valores literais de caractere e data devem estar entre aspas simples;

Cada string de caractere é gerada um vez para linha retornada;

LINHAS DUPLICADAS

A exibição das consultas é de todas as linhas, incluindo linhas duplicadas:

SQL> SELECT CDCARGO
   2  FROM FUNCIONARIO;

ELIMINANDO LINHAS DUPLICADAS

Elimine as linhas duplicadas usando a palavra chave DISTINC na cláusula SELECT:

SQL> SELECT DISTINCT CDCARGO
   2  FROM FUNCIONARIO;

O DISTINCT atua sobre a linha do select, não sendo possível ter 2 DISTINCTS na mesma SELECT

LIMITANDO LINHAS SELECIONADAS

Restringe as linhas retornadas usando a cláusula WHERE:

A cláusula WHERE pode comparer valores em colunas, valores literais, expressões aritméticas ou funções. É formada por tres elementos:

– Nome da coluna;
– Operadores de comparação;
– Nome da coluna, constant ou lista de valores;

USANDO A CLÁUSULA WHERE

SQL> SELECT NRMATRIC, NMFUNC
   2  FROM FUNCIONARIO
   3  WHERE CDCARGO = 'C2';

STRINGS DE CARACTERES E DATAS

As strings de caractere e valores de dados aparecem entre aspas simples;

Os valores de caractere fazem distinção entre maiusculas e minúculas e o valores de data diferenciam formatos;

O format de data default é DD-MON-YY.

Exemplo:

SQL> SELECT NRMATRIC, NMFUNC, DTADM
   2 FROM FUNCIONARIO
   3 WHERE SEXO = 'F' 
   4 AND DTADM = '01-JUN-93';

OPERADORES DE COMPARAÇÃO

OPERADOR SIGNIFICADO
= Igual
> Maior do que
>= Maior do que ou igual a
< Menor
<= Menor ou igual a
Diferente de

UTILIZAÇÃO DOS OPERADORES DE COMPARAÇÃO

Use os operadores de comparação em condições que comparam uma expressão a outra.

Eles são usados na cláusula WHERE seguindo os exemplos:

WHERE hiredate = '01-JAN-95'

WHERE SAL >= 500

WHERE ENAME = 'SMITH'

OUTROS OPERADORES DE COMPARAÇÃO

OPERADOR SIGNIFICADO
BETWEEN … AND… Entre dois valores (inclusive)
IN (list) Vincula qualquer um de uma lista de valores
LIKE Vincula um padrão de caracteres
IS NULL É um valor nulo

USANDO O OPERADOR BETWEEN

Use o operador BETWEEN apra exibir linhas baseadas em uma faixa de valores

SQL>SELECT NMCARGO, VRSALARIO
  2 FROM CARGO
  3 WHERE VRSALARIO BETWEEN 450 AND 750;

USANDO O OPERADOR IN

Use o operador IN para testar os valores de uma lista

SQL>SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
  2 FROM FUNCIONARIO
  3 WHERE CDCARGO IN ('C1','C3','C4');

USANDO O OPERADOR LIKE

Use o operador LIKE para executar pesquisas curinga com valores de string válidas;

As condições de pesquisa podem conter caracteres literais ou números;

– % denota zero ou muitos caracteres;

– _ (underline) denota um caractere;

Voce pode combiner caracteres de vinculação de padrão;

SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE 'JO%';
SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '%RO%'
SQL> SELECT NMFUNC, CDCARGO, CDDEPTO
   2 FROM FUNCIONARIO
   3 WHERE NMFUNC LIKE '_O%'

USANDO A OPÇÃO ESCAPE

Quando for necessário ter uma correspondencia exata para os caracteres ‘%’ e ‘_’, use a opção ESCAPE

SQL> SELECT CDCARGO, NMCARGO
   2 FROM CARGO
   3 WHERE NMCARGO LIKE '%@_%' ESCAPE '@';

USANDO O OPERADOR IS NULL

O operador IS NULL testa valores que são nulos. Um valor nulo significa que o valor não está disponível, não-atribuído, desconhecido ou não aplicável. Assim, não é possível testar com “=” porque um valor nulo não pode ser igual ou desigual a qualquer valor.

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NULL;

OPERADORES LÓGICOS

OPERADOR SIGNIFICADO
AND Retorna TRUE se as condições de componmentes forem TRUE
OR Retorna TRUE se cada condição de componente for TRUE
NOT Retorna TRUE se a condição seguinte for FALSE

Um operador lógico combina o resultado de duas condições de componente para produzir um único resultado com base neles ou inverter o resultado para a condição única. Três operadores lógicos estão disponíveis no SQL: AND, OR e NOT

USANDO O OPERADOR AND

AND exige que ambas as conduições sejam TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2   FROM CARGO
   3   WHERE CDCARGO = 'C5'
   4   AND VRSALARIO >= 2000;

TABELA VERDADE AND

AND TRUE FALSE NULL
TRUE TRUE FALSE NULL
FALSE FALSE FALSE FALSE
NULL NULL FALSE NULL

USANDO O OPERADOR OR

OR exige que cada condição seja TRUE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C5'
   4 OR VRSALARIO >= 2000;

TABELA VERDADE OR

OR TRUE FALSE NULL
TRUE TRUE TRUE TRUE
FALSE TRUE FALSE NULL
NULL TRUE NULL NULL

USANDO O OPERADOR NOT

NOT nega o operador usado na operação

O operador NOT pode ser utilizado também com outros operadores SQL, como BETWEEN e LIKE;

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE VRSALARIO IS NOT NULL;

TABELA VERDADE NOT

NOT TRUE FALSE NULL
TRUE FALSE TRUE NULL

EXEMPLO DO OPERADOR NOT

... WHERE CDDEPTO NOT IN ('D1','D3');

... WHERE VRSALARIO NOT BETWEEN 50 AND 500;

... WHERE NMCARGO NOT LIKE 'VIG%';

... WHERE RAMAL IS NOT NULL;

REGRAS DE PRECEDÊNCIA

ORDEM DE AVALIAÇÃO OPERADOR
1 Todos os operadores de comparação
2 NOT
3 AND
4 OR

Sobreponha regras de precedência usando parênteses

REGRAS DE PRECEDÊNCIA

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 WHERE CDCARGO = 'C1'
   4 OR  CDCARGO = 'C7'
   5 AND VRSALARIO >= 350

A ordem de execução usando regras de precedencia é:

A primeira condição é que o CDCARGO seja igual a ‘C7’ e o VRSALARIO SEJA >= 350

A segunda condição é que o cargo CDCARGO seja igual a ‘C1’;

CLÁUSULA ORDER BY

ORDER BY Classifica as linhas de 2 formas:

–ASC: ordem crescente, default (do menor para o maior);

–DESC: ordem decrescente (do maior para o menor);

SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY NMCARGO;
SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2  FROM CARGO
   3  ORDER BY CDCARGO ASC;
SQL> SELECT CDCARGO, NMCARGO, VRSALARIO
   2 FROM CARGO
   3 ORDER BY VRSALARIO DESC;

CLASSIFICANDO POR APELIDO DE COLUNA

SQL> SELECT NMCARGO, VRSALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY SAL_ANUAL

CLASSIFICANDO POR POSIÇÃO DE COLUNA

SQL> SELECT NMCARGO, VR_SALARIO,
   2 VRSALARIO * 12 AS SAL_ANUAL
   3 FROM CARGO
   4 ORDER BY 2

CLASSIFICANDO POR VÁRIAS COLUNAS

SQL> SELECT NMFUNC, SEXO, DTADM
   2 FROM FUNCIONARIO
   3 ORDER BY NMFUNC,DTADM DESC;

CONECTANDO DO SHELL DO LINUX PARA O SQL/PLUS

Como SYSDBA

[oracle@oracle ~]$ sqlplus / as sysdba

Como usuário comum

[oracle@oracle ~]$ sqlplus banco1/banco1

CONSULTANDO TABELAS DO SCHEMA NO SQL/PLUS

SQL> SELECT TABLE_NAME
   2 FROM USER_TABLES;

CONSULTANDO A ESTRUTURA DE UMA TABELA NO SQL/PLUS

SQL> DESC FUNCIONARIO;

SAINDO DO SQL/PLUS

SQL> exit;

 

Vídeo – Parte 01

 

Vídeo – Parte 02

 

Banco de Dados I – Aula 11A

INTRODUÇÃO À ÁLGEBRA RELACIONAL – RESUMO

  • Como o nome indica, a álgebra relacional (AR) é uma álgebra de expressões envolvendo relações. A partir de uma ou mais relações da BD tomadas como operandos, outras relações podem ser progressivamente construídas através de operadores especiais, sendo resultado da consulta sobre a BD.
  • Uma consulta é, portanto, uma expressão da consulta sobre a BD.
  • Alguns operadores básicos foram propostos por CODD. Se lembrarmos de que relações são conjuntos matemáticos (de tuplas) esses operadores essencialmente manipulam conjuntos e estão em quatro categorias:
    • Operadores convencionais sobre conjuntos: união, subtração, interseção e produto cartesiano;
    • Operadores que removem parte de uma relação; um operador que elimina linhas (seleção) e um operador que elimina colunas, ou seja, atributos (projeção)
    • Operadores que combinam linhas de duas relações de acordo com condições booleanas diversas (essas operações selecionam subconjuntos do produto cartesiano das duas relações);
    • Um operador especial p (Rô) permite renomear o esquema de uma relação, essencialmente para permitir expressar de forma não ambígua operações envolvendo auto-relacionamentos;

Notação

– Letras do final do alfabeto para denotar relações (R, S, T etc)

– Letras do inicio do alfabeto para denotar atributos (A, B, C etc)

– Letra grega ScreenHunter_253 Oct. 19 20.13 (téta) é  usada para denotar um dos seis operadores de comparação:

UNIÃO

ScreenHunter_254 Oct. 19 20.24: é uma relação que contém as tuplas que estão em R, em S ou em ambas; se uma tupla está presente em R e em S, ela só aparece uma vez na união.

Esta operação primitiva também requer como operandos tabelas união-compatíveis. Produz como resultado uma tabela que contém todas as linhas da primeira tabela seguidas de todas as linhas da segunda tabela. A tabela resultante possui a mesma quantidade de colunas que as tabelas originais, e tem um número de linhas que é no máximo igual à soma das linhas das tabelas fornecidas como operandos, já que as linhas que são comuns a ambas as tabelas aparecem uma única vez no resultado.

ScreenHunter_255 Oct. 19 20.26

INTERSECÇÃO

ScreenHunter_256 Oct. 19 20.27 : é a relação que contém as tuplas que estão em ambas R e S.

Esta é uma operação adicional que produz como resultado uma tabela que contém, sem repetições, todos os elementos que são comuns às duas tabelas fornecidas como operandos. As tabelas devem ser união-compatíveis.

ScreenHunter_257 Oct. 19 20.28

O mesmo efeito pode ser obtido fazendo-se uma combinação de diferenças entre conjuntos

ScreenHunter_258 Oct. 19 20.29

ou com uniões e diferenças

ScreenHunter_259 Oct. 19 20.30

Diferença

ScreenHunter_260 Oct. 19 20.31: contém as tuplas que estão em R porém não estão em S. Observe que R – S é diferente de S – R

É uma operação primitiva que requer como operandos duas tabelas união-compatíveis, ou seja, estruturalmente idênticas. O resultado é uma tabela que possui todas as linhas que existem na primeira tabela e não existem na segunda.

ScreenHunter_261 Oct. 19 20.32

EXEMPLO PARA ESSAS OPERAÇÕES

ScreenHunter_263 Oct. 19 21.38.jpg

PROJEÇÃO

Projeção: ScreenHunter_264 Oct. 19 21.40   constrói uma nova relação a partir de R contendo apenas os atributos  . Observe que tuplas duplicadas devem ser eliminadas ao se fazer a projeção. Exemplo:

ScreenHunter_265 Oct. 19 21.40.jpg

Geralmente indicada na literatura por ScreenHunter_266 Oct. 19 21.42.jpg  (a letra grega pi) produz um conjunto onde há um elemento para cada elemento do conjunto de entrada, sendo que a estrutura dos membros do conjunto resultante é definida nos argumentos da operação.

Pode ser entendida como uma operação que filtra as colunas de uma tabela. Por operar sobre apenas um conjunto de entrada, a projeção é classificada como uma  operação unária.

Ex.: ScreenHunter_266 Oct. 19 21.42  NmFunc (funcionário)

Essa expressão produz um conjunto contendo um elemento para cada funcionário, e cada elemento contém apenas a informação referente a NmFunc da relação funcionário original

ScreenHunter_267 Oct. 19 21.43

Agora estamos interessados em identificar todos os funcionários de sexo masculino existentes no banco de dados. É uma situação que não podemos resolver com projeções apenas, uma vez que deveremos descartar elementos do conjunto inicial. Para casos desse tipo existe uma operação relacional chamada Seleção.

 

SELEÇÃO (OU RESTRIÇÃO)

Seleção: seja uma expressão booleana C do tipo R.A ScreenHunter_253 Oct. 19 20.13C, onde C é uma constante do tipo de A que queremos comparar com o atributo A de uma linha de R. Então,

ScreenHunter_268 Oct. 19 21.45 c(R) é uma relação que inclui unicamente as linhas de R para as quais C é verdadeiro.

Exemplo:

ScreenHunter_269 Oct. 19 21.46

Expressões booleanas mais complexas envolvendo “ands” ou “ors” de operandos do tipo R.A  c podem ser avaliadas através de operações de união e interseção, mas, para simplificar a notação, vamos admitir em C expressões booleanas arbitrariamente complexas.

Se quisermos, podemos compor quaisquer operações, por exemplo:

ScreenHunter_270 Oct. 19 22.01

Indicada por   ScreenHunter_268 Oct. 19 21.45  (a letra grega sigma), é uma operação que para um conjunto inicial fornecido como argumento, produz um subconjunto estruturalmente idêntico, mas apenas com os elementos do conjunto original que atendem a uma determinada condição (também chamada de predicado). A seleção pode ser entendida como uma operação que filtra as linhas de uma tabela, e é também uma operação unária, já que opera sobre um único conjunto de dados.

Ex.:  ScreenHunter_268 Oct. 19 21.45 Sexo = ‘M’ (funcionário)

Produz o conjunto dos elementos de funcionário que atendem ao predicado [Sexo = ‘M’], ou seja, representa um subconjunto dos funcionários para o qual essa condição é avaliada como verdadeira.

ScreenHunter_271 Oct. 19 22.03.jpg

No caso de querermos descobrir o nome completo e a data de admissão de todos os funcionários do sexo feminino existentes na empresa, será necessário combinar uma projeção com uma seleção. Isso porque se decidirmos projetar as colunas desejadas diretamente a partir da relação funcionário, estaremos considerando também os elementos do sexo masculino, o que não queremos.

Como a projeção não permite descartar linhas, apenas colunas, deveremos fornecer a essa operação o subconjunto resultante de uma filtragem (seleção) da relação de funcionários original, como mostram as duas figuras a seguir, que representam as relações e as operações de duas maneiras diferentes.

ScreenHunter_272 Oct. 19 22.04.jpg

 

ScreenHunter_273 Oct. 19 22.05

Assim, a expressão que atende nossos objetivos nesse caso é

ScreenHunter_266 Oct. 19 21.42 NmFunc, DtAdm ( ScreenHunter_268 Oct. 19 21.45 Sexo = ‘F’ (funcionário))

cabendo observar que devido ao aninhamento das operações está implícito que primeiro será executada a seleção e depois a projeção, sendo que nesse exemplo não poderíamos inverter essa ordem.

Você poderia explicar porquê?

Por esse motivo, dizemos que a álgebra relacional é uma linguagem procedural, já que requer alguma definição quanto à ordem em que as operações serão realizadas. Linguagens em que apenas  mencionamos o resultado desejado, sem fazer menção alguma à forma como isso deve ser feito são chamadas de linguagens não-procedurais.

Suponha agora que precisamos obter o nome completo, a data de admissão e o salário de cada funcionário cadastrado.

Para essa consulta temos um fato novo, que é a referência a colunas de mais de uma tabela, uma vez que o nome e a data de admissão fazem parte da relação funcionário, enquanto que o salário existe apenas em cargos.

Isso é problemático, pois as duas operações que conhecemos até o momento são unárias, e temos necessidade de combinar os dados de mais de uma relação. Para situações como essa existe uma operação chamada Produto Cartesiano.

Produto Cartesiano

Produto Cartesiano: R x S : é uma relação obtida concatenando-se cada linha de R com cada linha de S, isto é, os atributos do produto cartesiano são os atributos de R, seguidos pelos atributos de S (se houver coincidência nos nomes de alguns atributos de R e S, a forma conveniente de distingui-los deve ser usada, como por exemplo R.C e S.C). Se R possui n atributos e K linhas e S possui m atributos e 1 linha então R x S possui n + m atributos e k x l linhas.

Exemplo de Produto Cartesiano

ScreenHunter_274 Oct. 19 22.08A notação geralmente adotada (na forma ‘conjunto1 x conjunto2’) para representar essa operação binária indica bem a sua natureza: o resultado do produto cartesiano de duas tabelas é uma terceira tabela contendo todas as combinações possíveis entre os elementos das tabelas originais.

Essa tabela resultante possuirá um número de colunas que é igual à soma das quantidades de colunas das duas tabelas iniciais, e um número de linhas igual ao produto do número de suas linhas.

Portanto, se fizermos o produto cartesiano de uma tabela A que possua 4 colunas e 10 linhas com uma tabela B onde existem 3 colunas e 7 linhas, a tabela resultante terá 4+3= 7 colunas e 10*7= 70 linhas. Assim, cada linha dessa tabela corresponderá à concatenação de uma linha da primeira tabela com uma linha da segunda.

O produto cartesiano não é muito usado como um fim em si mesmo, ou seja, dificilmente estaremos interessados em saber quais são todas as combinações possíveis entre as linhas de duas tabelas, pois a utilidade prática desse tipo de conhecimento é muito discutível.

Entretanto, é a única forma primitiva de que dispomos para fundir informações de duas tabelas heterogêneas para posterior processamento.

Nesse caso, tipicamente será necessário executar uma Seleção sobre o resultado do Produto Cartesiano, de maneira a descartar as combinações inválidas entre as linhas das tabelas originais.

Ex.: ScreenHunter_266 Oct. 19 21.42 NmFunc, DtAdm, VrSalário  ScreenHunter_268 Oct. 19 21.45(funcionário.CdCargo = cargo.CdCargo (funcionário x cargo))

Observe que primeiro é produzido o produto cartesiano correspondente a todas as combinações possíveis entre funcionários e cargos.

Essa relação vai conter linhas onde um funcionário estará associado a cargos que não são o seu, e devemos então aplicar um filtro (uma seleção) para gerar um subconjunto apenas com as combinações logicamente válidas (aquelas em que a chave estrangeira CdCargo de funcionário tem valor igual á chave primária CdCargo de cargo).

Como temos nesse subconjunto duas colunas com o mesmo nome (CdCargo que veio de funcionário e CdCargo proveniente de cargo), sempre que precisarmos mencionar uma delas será necessário especificar exatamente a qual das duas colunas estamos nos referindo, senão teremos uma situação ambígua, formalmente inaceitável.

Dizemos, nesse caso, que é necessário qualificar a coluna, e isso é feito escrevendo o nome da relação original antes do nome da coluna, separando-os por um ponto, ou seja, <nome-da-relação>.<nome-da-coluna>. É por esse motivo que escrevemos o predicado da seleção como sendo funcionário.CdCargo = cargo.CdCargo. Finalmente, a projeção é realizada a partir desse subconjunto, fornecendo os dados inicialmente desejados.

ScreenHunter_275 Oct. 19 22.11.jpg

ScreenHunter_276 Oct. 19 22.12.jpg

ScreenHunter_277 Oct. 19 22.13

ScreenHunter_278 Oct. 19 22.13.jpg

Caso desejemos obter uma variação ligeiramente diferente dessa consulta, acrescentando a restrição de que precisamos dos dados apenas dos funcionários do sexo masculino, teríamos a seguinte expressão algébrica:

ScreenHunter_279 Oct. 19 22.14.jpg

onde o símbolo “^” presente no predicado representa o conectivo lógico “E”. Portanto, com apenas 3 operações relacionais básicas foi possível extrair do banco de dados de exemplo várias informações importantes, representativas de uma grande parcela das consultas que um sistema gerenciador de bancos de dados deve processar.

As consultas realizadas foram:

  1. obter o nome completo de todos os funcionários;
  2. identificar todos os funcionários do sexo masculino;
  3. obter o nome completo e a data de admissão de todos os funcionários do sexo feminino;
  4. obter o nome completo, a data de admissão e o salário de todos os funcionários;
  5. descobrir o nome completo, a data de admissão e o salário de todos os funcionários do sexo masculino.

JUNÇÃO

Junção ScreenHunter_253 Oct. 19 20.13: nesta operação comparamos duas colunas do mesmo tipo de R e S, por exemplo R.A   S.B. Se a comparação der verdadeira, selecionamos a linha correspondente do produto cartesiano RxS. A junção ScreenHunter_253 Oct. 19 20.13é denotada por:

R | X | S

R.A ScreenHunter_253 Oct. 19 20.13S.B

A junção ScreenHunter_253 Oct. 19 20.13de duas relações é, portanto, um subconjunto do seu produto cartesiano e pode ser expressa pela identidade:

R | X | S = ScreenHunter_268 Oct. 19 21.45 (R.A ScreenHunter_253 Oct. 19 20.13S.B) (R X S)

R.A ScreenHunter_253 Oct. 19 20.13S.B

Exemplo: vamos utilizar outra relação para exemplificar a junção ScreenHunter_253 Oct. 19 20.13 Seja a relação T com esquema T(B,C,D) e uma instancia de T abaixo:

ScreenHunter_280 Oct. 19 22.18.jpg

ScreenHunter_281 Oct. 19 22.19

Para isso, temos a seguinte explicação:

Produto cartesiano entre R  X  T

ScreenHunter_282 Oct. 19 22.20

Posteriormente, comparamos as linhas de R.B que sejam >= a TD, tendo o resultado apresentado.

É uma operação que produz uma combinação entre as linhas de uma tabela com as linhas correspondentes de outra tabela, sendo em princípio correspondente a uma seleção pelos atributos de relacionamento sobre um produto cartesiano dessas tabelas:

ScreenHunter_283 Oct. 19 22.21

A operação de junção foi criada justamente porque esse tipo de combinação de tabelas é de uso muito comum, facilitando com isso a escrita de expressões.

A tabela resultante de uma junção tem todas as colunas da primeira tabela e todas da segunda tabela.

Isso faz com que os valores dos campos utilizados como critério para a correspondência entre as linhas apareça duplicado, já que um vem da primeira tabela e outro da segunda.

Existe uma variação da junção, chamada junção natural, que fornece o mesmo resultado, mas sem essa repetição de valores: uma das colunas correspondentes aos atributos de relacionamento é descartada.

JUNÇÃO NATURAL

Junção natural: quando ScreenHunter_253 Oct. 19 20.13é o operador de igualdade, o resultado do produto cartesiano terá duas colunas idênticas, R.A e S.B. Nesse caso é conveniente projetar fora uma dessas colunas e o resultado final é chamado de junção natural de R com S. Se as colunas sobre as quais fizermos a junção tiverem o mesmo nome em R e S, então se pode simplificar a notação escrevendo apenas:

R | X | S

Para indicar esta operação, ficando implícitas as colunas sobre as quais é feita a comparação. Exemplo: a junção das tabelas R e T acima, sobre as colunas R.B e T.B seria expressa por:

ScreenHunter_284 Oct. 19 22.23

Obs: A comparação é feita sobre o produto cartesiano.

 

RESUMO

ScreenHunter_285 Oct. 19 22.24

Exercícios – Parte 1

Com base nas tabelas Funcionário, Cargo e Depto apresentadas neste material, elaborar as expressões da álgebra relacional que obtenham:

ScreenHunter_286 Oct. 19 22.25

Exemplo de Dados:

ScreenHunter_287 Oct. 19 22.26

1 – Todos os funcionários do departamento ‘D1’.

2 – O nome e a matrícula de todos os funcionários do departamento ‘D1’.

3 – A matrícula e o nome do respectivo departamento de todos os funcionários.

4 – O nome dos funcionários que ganham mais de $500.

5 – O ramal do funcionário ‘ANA SILVEIRA’.

6 – Os nomes de todos os funcionários com cargo de ‘MECANICO’.

7 – Os nomes de todos os funcionários que trabalham no mesmo departamento que ‘JOSE NOGUEIRA’.

8 – Os nomes dos departamentos que possuem tanto funcionários como funcionárias.

 

Exercício – Parte 2

Considere as Relações F e D que representam os CEs Funcionários e Dependentes, respectivamente, com o seguinte esquema simplificado: F(fumf, nomef) e D(numf, nomef, par), onde numf: número do funcionário, nomef: nome do funcionário, nomed: nome do dependnete e par: parentesco, que pode ser um dentre: “filho”, “filha”, “esposa/o” etc. Observe que em D, numf é a chave estrangeira que referencia a chave primária numf de F. Abaixo é mostrada uma possível instância dessas relações:

ScreenHunter_288 Oct. 19 22.28

Vamos obter agora a junção natural F | x | D (ela vai usar a coluna numf para junção)

ScreenHunter_289 Oct. 19 22.29

Observe que a junção natural apresenta para cada funcionário que possui dependentes, os dados do funcionário e dos seus dependentes. Como o funcionário 04 não possui dependentes, ele não aparece no resultado da junção. Vamos agora obter expressões da álgebra relacional que respondem as seguintes consultas:

1.Quais os nomes e parentescos de todos os dependentes?

2.Quais os funcionários(numero de) possuem dependentes filhas?

3.Quais funcionários não possuem dependentes?

4.De os nomes dos funcionários que possuem algum dependente.

5.Dê o nome de cada funcionário que possui uma dependente chamada Alice

6.Quais funcionários possuem mais de um dependente?

Referência Bibliográfica

  • GUIMARÃES, Célio Cardoso.  FUNDAMENTOS DE BANCOS DE DADOS: modelagem, projeto e linguagem SQL. 1. ed. Campinas: Universidade de Campinas. 2003.

 

 

Vídeo

 

Banco de Dados I – Aula 10E

Crie no Microsoft Access para cada exercício um banco de dados dos exercícios resolvidos

Formas Normais – Formulário de manutenção técnica 

1FN (ITENS DE REPETICAO, OU MULTIVALORADOS TRANSFORMAR EM OUTRA TABELA)

EMPRESA (COD_EMP, RAZAO, ENDERECO, CIDADE, UF, CEP, FONE)

FUNCIONARIO (COD_FUN, NOME, …)

SITUACAO_SERV (TIPO)

FUNCIONARIO_CLI (RG_FUN_CLI, NOME_FUN_CLI, COD_EMP)

OBS (FUNCIONARIO_CLI. COD_EMP à CLIENTE.COD_EMP)

ACAO_SERV (TIPO, RG_FUN_CLI)

OBS (ACAO_SERV.TIPO à SITUACAO_SERV.TIPO)

OBS (ACAO_SERV.RG_FUN_CLI à FUNCIONARIO_CLI.RG_FUN_CLI)

CLIENTE (COD_EMP_CLI, RAZAO, ENDERECO, BAIRRO, COMPLEMENTO, CIDADE, UF, CEP)

TELEFONE_CLI (COD_EMP_CLI, TELEFONE)

OBS (TELEFONE_CLI.COD_EMP_CLI à CLIENTE.COD_EMP_CLI)

SOLUCAO (COD_SOL, DESC_SOL)

SERVICO (COD_SERV, DESC_SERV)

ATENDIMENTO (NR_ORDEM, DATA_ABERTURA, HORA_ABERTURA, DATA_PREVISTA, HORA_PREVISTA, DATA_SOL, HORA_SOL, COD_EMP_CLI, COD_EMP, COD_FUN)

OBS (ATENDIMENTO.COD_EMP_CLI à CLIENTE.COD_EMP_CLI)

OBS (ATENDIMENTO.COD_EMP à EMPRESA.COD_EMP)

OBS (ATENDIMENTO.COD_FUN à FUNCIONARIO.COD_FUN)

SOL_PRESTADA (NR_ORDEM, COD_SOL)

OBS (SOL_PRESTADA.NR_ORDEM à ATENDIMENTO.NR_ORDEM)

OBS (SOL_PRESTADA.COD_SOL à SOLUCAO.COD_SOL)

SERV_PRESTADO (NR_ORDEM, COD_SERV)

OBS (SERV_PRESTADO.NR_ORDEM à ATENDIMENTO.NR_ORDEM)

OBS (SERV_PRESTADO.COD_SERV à SERVICO.COD_SERV)

 

Banco de Dados I – Aula 10D

Crie no Microsoft Access para cada exercício um banco de dados dos exercícios resolvidos

 

Formas Normais – Pedido de Compras 

PEDIDO (NR_PEDIDO, DATA, DEPTO_ORIGEM, FUNC_SOLICITANTE, DEPTO_DESTINO, FUNC_RESPONSAVEL, TOTAL_QTD)

 ITEM_PEDIDO (NR_PEDIDO, ITEM, MATERIAL, QUANTIDADE)

 

2NF (DEPENDENCIA PARCIAL DOS CAMPOS NÃO CHAVE, TRANSFORMAR EM OUTRA TABELA)

PEDIDO (NR_PEDIDO, DATA, DEPTO_ORIGEM, FUNC_SOLICITANTE, DEPTO_DESTINO, FUNC_RESPONSAVEL, TOTAL_QTD)

ITEM_PEDIDO (NR_PEDIDO, ITEM, MATERIAL, QUANTIDADE)

OBS: ITEM_PEDIDO.COD_MATERIAL à MATERIAL.COD_MATERIAL)

 

3FN (ANALISAR OS CAMPOS NÃO CHAVES SE SÃO DEPENDENTES DE OUTROS CAMPOS NÃO CHAVE. CASO SIM, TRANSFORMAR EM OUTRA TABELA)

PEDIDO (NR_PEDIDO, DATA, COD_DEPTO_ORIGEM, COD_FUNC_SOL, COD_DEPTO_DESTINO, COD_FUNC_RESP)

OBS: PEDIDO.COD_DEPTO_ORIGEM à DEPARTAMENTO.COD_DEPTO)

OBS: PEDIDO.COD_FUNC_SOL à FUNCIONARIO.COD_FUNC)

OBS: PEDIDO.COD_DEPTO_DESTINO à DEPARTAMENTO.COD_DEPTO)

OBS: PEDIDO.COD_FUNC_RESP à FUNCIONARIO.COD_FUNC)

DEPARTAMENTO (COD_DEPTO, DEPARTAMENTO)

 FUNCIONARIO (COD_FUNC, FUNCIONARIO)

 ITEM_PEDIDO (NR_PEDIDO, ITEM, COD_MATERIAL, QUANTIDADE)

OBS: ITEM_PEDIDO.COD_MATERIAL à MATERIAL.COD_MATERIAL)

 MATERIAL (COD_MATERIAL, MATERIAL)

Video

Banco de Dados I – Aula 10C

Crie no Microsoft Access para cada exercício um banco de dados dos exercícios resolvidos

Estudo de Caso 2 – Gerência Acadêmica de uma Universidade 

PROFESSOR (COD_PROF, NOME, INSCRICAO_GA, COD_DEPTO)

OBS: PROFESSOR.COD_DEPTO à DEPARTAMENTO.COD_DEPTO

PROFESSOR_HABILITADO (COD_PROF, CFE)

DEPARTAMENTO (COD_DEPTO, NOME_DEPTO)

CURSO (COD_CURSO, NOME, NR_TOTAL_HORAS, COD_DEPTO)

OBS (CURSO.COD_DEPTO à DEPARTAMENTO.COD_DEPTO)

DISCIPLINA (COD_DISC, DESCRICAO, DESC_CURRICULAR, PRE_REQUISITO, COD_DEPTO, COD_PROF)

OBS (PRE_REQUISITO à COD_DISC)

OBS (DISCIPLINA.COD_DEPTO à DEPARTAMENTO.COD_DEPTO)

OBS (DISCIPLINA.COD_PROF à PROFESSOR_HABILITADO.COD_PROF)

DISCIPLINA_OBRIGATORIA (COD_DISC, HORA_OBRIGATORIA)

COMPOR (COD_CURSO, COD_DISC, TIPO_DISCIPLINA)

ALUNO (NR_MATRICULA, TIPO_ADMISSAO, NOME, ENDERECO, COD_CURSO)

OBS (ALUNO.COD_CURSO à CURSO.COD_CURSO)

HISTORICO (NR_MATRICULA, COD_DISC, DATA_DISC_CURSADA, NOTA)

OBS (HISTORICO.NR_MATRICULA à ALUNO.NR_MATRICULA)

OBS (HISTORICO.COD_DISC à DISCIPLINA.COD_DISC)

Video

Banco de Dados I – Aula 10B

Criando um banco de dados no Microsoft-Access

 

Para ilustrar a criação simples de banco de dados, iremos utilizar os exemplos normalizados:

Primeiramente, crie um banco de dados vazio no Microsoft Access;

Untitled

Dê um nome ao banco de dados:

Untitled2

Como criamos um banco de dados vazio, o MS-Access irá pedir os dados para a nova tabela;

Classificando o tipo de dados

 

Usaremos os tipos básicos de dados que o MS-Access oferece. Os tipos de dados servem para classificarmos os mesmos. Determinado dado tem certa característica(s). Por exemplo: “DQF-2134”, “15/07/2000”, “16”, “Ana Cristina da Silva”, “13.200-015”, “São Paulo”. Podemos observar que os dados possuem caracteres numéricos e alfanuméricos.  Observando os valores dos dados, podemos classificar o seu tipo.

O MS-Access possui alguns outros tipos de dados:

Untitled3

Iremos criar as tabelas abaixo, conforme exercício passado:

ESTOQUE_ITENS(NR_CONTROLE,ITEM, COD_PECA, QUANTIDADE, TIPO_MOVIMENTACAO, COD_SETOR)

PECA(COD_PECA, DESCRICAO_PECA)

SETOR(COD_SETOR, DESC_SETOR)

ESTOQUE(NR_CONTROLE, DATA, COD_FUNC_REP, COD_FUNC_RESP)

FUNCIONARIO(COD_FUNC, NOME_FUNC)

Criando tabelas no MS-ACCESS

Tabelas a serem criadas:

Untitled4

Untitled5

Untitled6

Untitled7

Realize após a construção das tabelas testes de inserção de dados. Perceba que ainda não haverá nada que impeça o usuário colocar um dado que não exista nas tabelas bases.

Relacionamento entre tabelas

Para fazer o relacionamento no ACCESS, vá no menu “Ferramentas de Banco de Dados” / Relações

Untitled8

Selecione todas as tabelas e pressione “Adicionar”

Untitled9

Após a inclusão de todas as tabelas, aparecerá as tabelas “não-relacionadas”, conforme figura abaixo:

Untitled10

Arraste o campo “NR_CONTROLE” da tabela “ESTOQUE” para o campo “NR_CONTROLE” da tabela “ESTOQUE_ITENS”. A seguinte tela aparecerá:

Untitled11

Faça o mesmo processo para os demais itens do banco de dados, deixando igual a figura abaixo:

Untitled12

Teste novamente a inserção de dados;

Vídeo

Banco de Dados I – Aula 10A

PROJETANDO BANCO DE DADOS

  • Segundo OLIVEIRA, (2002, p.21), antes de utilizarmos os comandos SQL, vamos identificar a forma de planejar a criação do banco de dados. Esse planejamento é extremamente importante para a estabilidade de todo o sistema. Estudos indicam que quanto maior o tempo despendido no projeto do banco de dados, menor será o tempo despendido na manutenção do modelo.
  • OLIVEIRA (2002, p.21) explica ainda que podemos comparar a criação de um sistema com a construção de um edifício. O projeto de banco de dado está para o sistema da mesma forma que a estrutura do prédio está para o edifício. Se não for dada a devida atenção ao desenho do banco de dados, pode-se comprometer todo o desenvolvimento do sistema.
  • É como construir um edifício utilizando uma base inadequada: um dia o edifício cairá. De outra forma, quanto maior for o tempo dedicado ao estudo das necessidades de informação do sistema em desenvolvimento, maior será o tempo economizado no desenvolvimento do sistema.
  • O sistema terá melhor qualidade, e será mais fácil, no futuro, implementar novas rotinas, procedimentos e agregar novas informações necessárias.
  • O processo de análise dos dados pressupõe três fases distintas e integradas, como apresenta a figura a seguir

ScreenHunter_249 Oct. 16 19.54

  • Segundo o autor e colunista Ricardo Rezende, da revista especializada em desenvolvimento da Devmedia, o sistema de banco de dados deve garantir uma visão totalmente abstrata do banco de dados para o usuário.
  • Para o usuário do banco de dados pouco importa qual unidade de armazenamento está sendo usada para guardar seus dados, contanto que os mesmos estejam disponíveis no momento necessário.

 

  • Esta abstração se dá em três níveis–Nível de visão do usuário: as partes do banco de dados que o usuário tem acesso de acordo com a necessidade individual de cada usuário ou grupo de usuários;–Nível conceitual: define quais os dados que estão armazenados e qual o relacionamento entre eles;–Nível físico: é o nível mais baixo de abstração, em que define efetivamente de que maneira os dados estão armazenados

ScreenHunter_250 Oct. 16 19.59

  • Todo bom sistema de banco de dados deve apresentar um projeto, que visa a organização das informações e utilização de técnicas para que o futuro sistema obtenha boa performance e também facilite infinitamente as manutenções que venham a acontecer.
  • O projeto de banco de dados se dá em duas fases:
    • Modelagem conceitual;
    • Projeto lógico.
  • Estas duas etapas se referem a um sistema de banco de dados ainda não implementado, ou seja, que ainda não exista, um novo projeto. Para os casos em que o banco de dados já exista, mas é um sistema legado, por exemplo, ou um sistema muito antigo sem documentação, o processo de projeto de banco de dados se dará através da utilização de uma técnica chamada de Engenharia Reversa, que será visto em outra oportunidade.

 

Modelo Conceitual

  • É a descrição do BD de maneira independente ao SGBD, ou seja, define quais os dados que aparecerão no BD, mas sem se importar com a implementação que se dará ao BD. Desta forma, há uma abstração em nível de SGBD.
  • Uma das técnicas mais utilizadas dentre os profissionais da área é a abordagem entidade-relacionamento (ER), onde o modelo é representado graficamente através do diagrama entidade-relacionamento (DER)

Exemplo 01

ScreenHunter_251 Oct. 16 20.15

O modelo acima, entre outras coisas, nos traz informações sobre Alunos e Turmas. Para cada Aluno, será armazenado seu número de matrícula, seu nome e endereço, enquanto para cada turma, teremos a informação de seu código, a sala utilizada e o período.

Já no modelo abaixo, exemplo de MER contendo assunto referente a locadora.

Exemplo 02

Untitled

Modelo Lógico

  • Descreve o BD no nível do SGBD, ou seja, depende do tipo particular de SGBD que será usado. Não podemos confundir com o Software que será usado. O tipo de SGBD que o modelo lógico trata é se o mesmo é relacional, orientado a objetos, hierárquico, etc.
  • Abordaremos o SGBD relacional, por serem os mais difundidos. Nele, os dados são organizados em tabelas

Untitled2

  • O modelo lógico do BD relacional deve definir quais as tabelas e o nome das colunas que compõem estas tabelas.

 

  • Para o nosso exemplo, poderíamos definir nosso modelo lógico conforme o seguinte:

Aluno(mat_aluno, nome, endereco)
Turma (cod_turma, sala, periodo)

  • É importante salientar que os detalhes internos de armazenamento, por exemplo, não são descritos no modelo lógico, pois estas informações fazem parte do modelo físico, que nada mais é que a tradução do modelo lógico para a linguagem do software escolhido para implementar o sistema.
  • GUIMARÃES (2003, p.32) defende que como muitas aplicações de engenharia, o projeto de uma base de dados através da técnica top down ou de refinamento sucessivos é largamente utilizado. Ele começa pela análise dos requisitos dos usuários finais da BD e da visão externa que eles têm sobre os dados.
  • Esta visão e requisitos dependem da aplicação pretendida da BD, variam de um usuário para outro dentro da organização, e refletem suas necessidades para o trabalho diário. Ela é comumente informal e incompleta, em graus que variam com o nível de informatização da aplicação (ou da organização).
  • Os objetivos finais dessa análise são: (i) obter uma visão unificada de todos os dados da aplicação, e (ii) definir os procedimentos funcionais para operar com os dados.
  • É portanto, uma sistemática similar à análise de sistemas convencional.
  • Esta visão unificada dos dados é comumente chamada de modelagem de dados e corresponde a uma abstração do mundo real contendo o conjunto de informações sobre o mesmo que julgamos importante armazenar e manipular.
  • O projeto top down da BD através de modelagem de dados consiste em especificar os dados através de refinamento sucessivos, mapeando os dados definidos num nível mais alto e abstrato para o nível seguinte, menos abstrato e mais detalhado.
  • No nível mais alto a visão e requisitos da BD ainda é informal e é normalmente apresentada sob a forma de documentos textuais. Vamos denominá-la de visão externa de dados.
  • O próximo nível consiste na especificação lógica dos dados num formato de projeto lógico de dados e, dependendo do SGBD escolhido, pode ser de nível suficientemente alto para esconder a maioria dos detalhes de implementação.
  • O último nível é denominado de projeto físico dos dados e corresponde à organização interna do armazenamento dos dados pelo SGBD e à definição de estruturas de dados auxiliares visando uma maior eficiência na recuperação e manipulação dos dados. Dependendo do SGBD uma parte considerável do nível físico fica escondida das aplicações.

ScreenHunter_252 Oct. 16 21.10

A linguagem SQL

  • Segundo GUIMARÃES (2003, p.99), o modelo relacional desenvolvido por [Codd70] definiu as metalinguagens álgebra relacional e cálculo relacional, que implementam os conceitos básicos do modelo. Elas tiveram grande influencia no desenvolvimento subseqüente de propótipos do modelo relacional.
  • SQL (Structured Query Language) é uma linguagem de definição e de manipulação de dados relacionais, desenvolvida nos laboratórios da IBM nos anos 70 e hoje padronizada pelos comitês ISO/ANSI.
  • OLIVEIRA (2002, p.18), complementa que SQL é um conjunto de comandos de manipulação de banco de dados utilizado para criar e manter a estrutura desse banco de dados, além de incluir, excluir, modificar e pesquisar informações nas tabelas dele. A linguagem SQL não é uma linguagem de programação autônoma; poderia ser chamada de “sublinguagem”.
  • A linguagem SQL não é procedural, logo é possível especificar o que deve ser feito, e não como deve ser feito. Dessa forma, um conjunto de linhas (set) será atingido pelo comando e não cada uma das linhas, como é feito no ambiente procedural. Portanto, não é necessário entender o funcionamento interno do banco de dados e como e onde estão armazenados fisicamente os dados.
  • Teoricamente deveria ser possível transferir facilmente os comandos SQL de um banco de dados para outro. Contudo, isso não é possível. Naturalmente, boa parte do trabalho poderá ser aproveitado, mas deve-se fazer adaptações em função do banco de dados que está sendo utilizado.

 

Divisão da linguagem SQL

  • DDL (Data Definition Language): permite a criação dos componentes do banco de dados, como tabelas, indicies, etc. Os principais comandos são: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, ALTER INDEX, DROP INDEX;
  • DML (Data Manipulation Language): permite a manipulação dos dados armazenados no banco de dados. Comandos DML: INSERT, DELETE, UPDATE;
  • DQL (Data Query Language): permite extrair dados do banco de dados. Comando: SELECT
  • DCL (Data Control Language): provê segurança interna do banco de dados: Comandos: CREATE USER, ALTER USER, GRANT, REVOKE, CREATE SCHEMA;
  • Com o advento da SQL-99, a linguagem SQL passou a incorporar comandos procedurais (Begin, IF, funções, procedimentos) que, na prática, já existiam como extensões da linguagem.
  • Essas extensões, até hoje, são específicas de cada banco de dados e, portanto, a Oracle tem a sua própria linguagem procedural que estende a SQL, que é a PL/SQL.
  • A Microsoft incorporou no SQLServer o Transact-SQL com o mesmo objetivo. A idéia é que, num futuro próximo, exista um padrão de programação em todos os banco de dados.

Vídeo

Banco de Dados I – Aula 08B

FORMAS NORMAIS

  • Segundo BATTIST (2005, P. 16), o objetivo da normalização é evitar os problemas provocados por falhas no projeto do Banco de Dados, bem como eliminar a “mistura” de assuntos e as correspondentes redundâncias de dados.
  • A normalização de tabelas é utilizada para tentar detectar erros no projeto das tabelas e atributos de cada tabela e corrigir estes erros, antes da criação e utilização do Banco de Dados.
  • É bem mais fácil (e barato), corrigir os erros na fase de projeto do que depois que o Banco de Dados já estiver em uso.
  • Uma “Regra de ouro” que devemos observar quanto ao projeto de banco de dados é a de “não misturar assuntos em uma mesma tabela”.
  • GUIMARAES (2003, p.81) complementa que existe um considerável aparato teórico por trás dos conceitos de normalização de relações. As razões para estudá-los, no entanto, são de ordem prática. Eles nos vão ajudar a projetar base de dados com menos possibilidade de inconsistências e menos redundâncias de informação.
  • Eles também vão ajudar a determinar com mais precisão certos tipos de restrições sobre atributos de uma relação assim como a determinação das suas possíveis chaves.
  • Por exemplo, na tabela Clientes, devemos colocar somente campos relacionados com o assunto Clientes. Não devemos misturar campos relacionados com outros assuntos, tais como pedidos, produtos etc. Essa “mistura de assuntos” em uma mesma tabela acaba por gerar repetição desnecessária dos dados bem como inconsistências dos dados.
  • O processo de normalização aplica uma série de regras sobre as tabelas de banco de dados, para verificar se estas estão corretamente projetadas. Embora existam cinco formas normais (ou regras de normalização), na prática usamos um conjunto de três formas normais.
  • Frequentemente, após a aplicação das regras de normalização, algumas tabelas acabam sendo divididas em duas ou mais, o que no final gera um número maior de tabelas do que o número de tabelas originalmente projetado.
  • Este processo causa a simplificação dos atributos de uma tabela, colaborando significativamente para a estabilidade do projeto de banco de dados, reduzindo-se as necessidades de manutenção e alterações, após o banco ter sido colocado em produção.

Primeira Forma Normal

  • BATTIST diz que a Regra: “Uma tabela está na Primeira Forma Normal quando seus atributos não contem grupos de repetição”. Por isso dizemos que uma tabela que possui grupos de repetição não está na Primeira Forma Normal.
  • Exemplo de uma tabela que não está normalizada na 1 FN (BATTIST, 2005, p.17)

ScreenHunter_238 Sep. 26 21.36

  • GUIMARAES (p.84) diz que a 1FN é considerada como a própria definição do MR: Toda relação está em 1FN, isto é, não possui atributos multivalorados nem relações aninhadas e será tomada como implícita.
  • Podemos notar que uma tabela com esta estrutura apresenta diversos problemas. Por exemplo, se um casal tiver mais do que um filho, teríamos que digitar o nomes do pai e da mãe diversas vezes, tantas quantos forem os filhos. Isso forma um grupo de repetição.
  • Pode ser que, por erro de digitação, o nome dos pais não apareça exatamente igual todas as vezes, o que pode acarretar problemas na hora de fazer pesquisas ou emitir relatórios. Este problema ocorre porque misturamos assuntos em uma mesma tabela. Colocamos as informações dos pais e dos filhos em uma mesma tabela. (BATTIST, p. 17)
  • A solução para este problema é simples: criamos uma tabela separada para a informação dos pais e relacionamos a tabela Pais com a tabela Filhos através de um relacionamento do tipo Um para Muitos, ou seja, um casal pode ter vários filhos.
  • As Tabelas Pais e Filhos estão na Primeira Forma Normal (BATTIST, 2005, p.17)

ScreenHunter_239 Sep. 26 21.38

  • As duas tabelas resultantes da aplicação da Primeira Forma Normal, Pais e Filhos, estão na Primeira Forma Normal. A tabela original, a qual misturava informações de pais e filhos, não está na Primeira Forma Normal.
  • OLIVEIRA (2002, p.53) complementa que a solução para este caso é que devemos separar a informação que se repete em uma nova entidade. Devemos ainda levar a chave primaria da entidade original para a nova entidade gerada (caso contrário, não haverá como relacionar as informações das duas entidades). Feito isso, criaremos a nova chave para a nova entidade.
  • Normalmente podemos localizar um campo que, unido à chave da entidade original, formará a chave da nota tabela (nesse caso, chave concatenada), ou podemos criar um campo para esse fim, caso não exista, Há a possibilidade de criarmos simplesmente uma nova chave não concatenada e independente da entidade original. É uma questão de preferência.
  • Exemplo:

ScreenHunter_240 Sep. 26 21.39

  • Utiliza-se o campo Numero da Faixa como atributo-chave junto com o CodigoDoCD, pois não poderá haver duas faixas com o mesmo número em um único CD. Essa é, portanto, uma chave concatenada.

Segunda Forma Normal

  • Podemos aplicar a Segunda Forma Normal quando tivermos uma chave primária composta. Neste caso, devemos observar se todos os campos, que não fazem parte da chave primária composta, dependem de todos os campos que compõem a chave primária composta. Se algum campo depender somente de parte da chave primária composta, então este campo deve pertencer a outra tabela. (BATTIST, 2005, P.17)
  • Tabela que não está na segunda forma normal (BATTIST, 2005, p.18)

ScreenHunter_241 Sep. 26 21.41

  • A chave primária composta é formada pela combinação dos campos NúmeroDaMatricula e CódigoDoCurso.
  • O campo Avaliação depende tanto do CódigoDoCurso quanto Do NúmeroDaMatricula (cada aluno – representado por sua matricula, tem uma nota em cada disciplina – representada pelo campo CódigoDoCurso), porém o campo DescriçãoDoCurso depende apenas do CódigoDoCurso (a descrição do curso não tem relação com o NúmeroDaMatricula).
  • Com isso, temos um campo que não faz parte da chave primária composta e depende apenas de um dos campos que compõem a chave primária composta. Assim podemos dizer que esta tabela não está na Segunda Forma Normal. (BATTIST, 2005, P.18)
  • A resolução para este problema também é simples: dividimos a tabela, que não está na Segunda Forma Normal, em duas outras tabelas, conforme indicado pela figura abaixo, sendo que as duas tabelas resultantes estarão na segunda forma normal.
  • Duas tabelas que estão na Segunda Forma Normal:

ScreenHunter_242 Sep. 26 21.43

  • OLIVEIRA (2002, p.56) diz  que uma entidade está na segunda forma normal quando todos os seus atributos não chave dependem unicamente da chave. Assim, deve-se perguntar a cada atributo, que não seja a chave da entidade, se ele depende apenas da chave da entidade. O que se procura com isso é medir o grau de dependência entre os atributos.
  • Apenas coisas semelhantes podem ser unidas em grupos semelhantes (entidades nada mais são do que um grupo ou conjunto). A solução sugerida para esse problema é ao identificarmos uma situação como a descrita anteriormente, devemos separar os atributos independentes e criar ou identificar dentre os atributos separados uma nova chave para esta nova entidade.
  • Essa chave deve ser mantida na entidade original como o atributo de relacionamento entre ambas as entidades. Dessa forma, não se perde qualquer informação no modelo.
  • No exemplo anterior, a gravadora, o Autor e a Musica são independentes de suas entidades, CD e Item_CD. Veja que já uma enorme vantagem ao separarmos esses atributos independentes, visto quem se não o fizéssemos, cada alteração em uma das informações deveria ser estendida a todas as linhas da entidade.
  • É muito mais fácil fazer isso apenas na nova entidade criada. Como a relação se dá por meido do campo-chave, não há necessidade de alterar todas as ocorrências em CD ou Item_CD, apenas nas entidades Autor, Gravadora ou Musica.
  • A inclusão de novos atributos também é facilitdada. Atributos que venham a ser relavantes serão acrescentados direamente na entidade Autor (como Data de Nascimento) ou Gravadora (como Endereço e Home Page)

ScreenHunter_243 Sep. 26 21.46

Terceira Forma Normal

  • Segundo BATTIST (p.21), na definição dos campos de uma tabela podem ocorrer casos em que um campo não seja dependente diretamente da chave primária, ou de parte dela, mas sim dependente de um outro atributo constante na tabela, atributo este que não seja a chave primária.
  • Quando isso ocorre, dizemos que a tabela não está na Terceira Forma Normal, conforme indicado pela figura seguinte:
    • Uma tabela que não está na Terceira Forma Normal:

ScreenHunter_244 Sep. 26 21.47

  • Observe que o campo DescriçãoDoCurso depende apenas do campo CodigoDoCurso, o qual não faz parte da chave primária. Por isso dizemos que esta tabela não está na Terceira Forma Normal.
  • A solução para este caso também é simples. Novamente basta dividir a tabela em duas outras, conforme indicado pela figura abaixo. As duas tabelas resultantes estão na Terceira Forma Normal.
    • Duas tabelas que estão na Terceira Forma Normal:

ScreenHunter_245 Sep. 26 21.48

  • OLIVEIRA (p.60) diz que uma entidade está terceira forma normal quando todos os seus atributos não chave não dependem de nenhum outro atributo não chave. Utilizando palavras mais simples, pode-se dizer que um atributo não deve depender de outro atributo. Isso ocorre normalmente em cálculos matemáticos ou em atributos “perdidos” na entidade errada.
  • Se guardamos eles são resultados de uma operação entre dois atributos. Se guardarmos esse valor, estaremos apenas ocupando espaço e abrindo a possibilidade de termos uma informação inconsistente no banco de dados;
  • Exemplo: No nosso exemplo, o atributo TempoTotal é dependente de outro atributo não chave: Tempo, na entidade musica. Ao somarmos o tempo de cada faixa do CD, teremos o tempo total de gravação do CD. Esse campo deve ser eliminado, sem perda de informação para o modelo de dados.

ScreenHunter_246 Sep. 26 21.49

Exercício – Formas Normais

  • Dado a seguinte figura, defina as tabelas utilizando as seguintes formas normais: 1FN, 2FN e 3FN:

ScreenHunter_247 Sep. 26 21.50

Video

GitHub

https://github.com/rodrigoksaito/anchieta/tree/master/BancoDados_I/Aula08

 

Banco de Dados I – Aula 08A

ASPÉCTO TEMPORAL

 

Modelo deve refletir o aspecto temporal

  • Certas aplicações exigem que o BD guarde o histórico de alterações de informações. Por exemplo, um BD de uma seguradora.
  • Pode ser necessário conhecer não só o segurado atual de uma apólice, mas também os do passado.
  • O modelo de um BD que armazena somente os valores atuais de uma informação é diferente do modelo do BD que armazena o histórico da informação. Portanto, é necessário considerar o aspecto temporal na modelagem de dados.
  • Não há regras gerais de como proceder neste caso, mas é possível identificar alguns padrões que se repetem frequentemente na prática.

 

Atributos cujos valores modificam ao longo do tempo

  • Alguns atributos de uma entidade, normalmente aqueles que não são identificadores da entidade, podem ter seus valores alterados ao longo do tempo (por exemplo, o endereço de um cliente pode ser modificado).
  • Algumas vezes, por necessidades futuras de informações, ou até mesmo por questões legais, o banco de dados deve manter um registro histórico das informações.
  • Um exemplo é o valor do salário do empregado.
  • Num sistema de pagamento, não interessa saber apenas o estado atual, mas também o salário durante os últimos meses, por exemplo, para emitir uma declaração anual de rendimentos de cada empregado.
  • Assim, o salário não pode ser modelado como atributo, mas sim como uma entidade.
  • Exemplo

ScreenHunter_229 Sep. 26 20.32

 

ScreenHunter_231 Sep. 26 20.53

 

Relacionamentos que modificam ao longo do tempo

  • Assim como atributos podem ter seus valores modificados ao longo do tempo, também relacionamentos podem ser modificados (instâncias dos relacionamentos podem ser modificados, adicionadas/removidas) e também neste caso pode ser requerido que o banco de dados mantenha um registro histórico das alterações.
  • Quando é considerada a história de suas alterações, relacionamentos 1:1 ou 1:n são transformados n:n.

ScreenHunter_232 Sep. 26 21.05

ScreenHunter_233 Sep. 26 21.06

 

Modelando a dimensão temporal de relacionamentos 1:1

  • Para exemplificar, consideramos o relacionamento LOCAÇÃO (a). Este relacionamento possui cardinalidade 1:1, ou seja, cada empregado está alocado a no máximo uma mesa e cada mesa tem ela alocado no máximo um empregado.
  • Este modela está correto, caso deseje-se armazenar no banco de dados apenas a alocação atual de cada mesa. Entretanto, caso deseje-se armazenar também a história das alocações, isto é, que empregados estiveram alocados a que mesas ao longo do tempo, é necessário modificar o modelo para (b).
  • O relacionamento para a ter cardinalidade N:N, já que, ao longo do tempo um empregado pode ter sido alocado a diversas mesas e uma mesa pode ter sido a ela alocados a muitos empregados.
  • Como um mesmo empregado pode ter sido alocado a mesma mesa múltiplas vezes, torna-se necessário um atributo identificador do relacionamento, a fim de distinguir uma alocação de um determinado empregado a uma mesa, das demais alocações deste emprega à mesma mesa. Com isso surge o atributo identificador data.

ScreenHunter_234 Sep. 26 21.07

ScreenHunter_235 Sep. 26 21.08

  • A figura anterior apresenta uma situação semelhante agora considerando um relacionamento de cardinalidade 1:N. Se quisermos considerar a história das lotações de empregados ao longo do tempo, é necessário transformar o relacionamento para a cardinalidade N:N, já que ao longo do tempo um empregado pode ter atuado em diferentes departamentos.
  • Neste caso pode ocorrer também que atributos da entidade EMPREGADO migrem para o relacionamento. No caso do exemplo, isso ocorrer com o atributo nr doc lotação. Este atributo, que, na primeira versão, migra, na nova versão, para o relacionamento, já que na nova versão um empregado pode estar relacionado com múltiplos departamentos.

 

Modelando a dimensão temporal de relacionamentos N:N

  • Na figura seguinte apresenta mais outro exemplo relacionamento temporal, agora um relacionamento de cardinalidade N:N. Modela-se a inscrição de participantes nos cursos oferecidos por uma empresa de treinamento.
  • Na primeira versão, considera-se apenas os cursos nos quais uma pessoa está inscrita em um determinado instante no tempo. Na segunda versão, considera-se todas as inscrições, inclusive as do passado.
  • A modificação de uma versão para a outra consta da transformação do atrubuto data em atributo identificador. Isso ocorre porque, na segunda versão, um participante pode aparecer relacionamento múltiplas vezes a um determinado curso (caso ele tenha se inscrito múltiplas vezes no curso).
  • O atributo para a distinguir uma inscrição de uma pessoa em um curso, das demais inscrições desta pessoa no mesmo curso.

ScreenHunter_236 Sep. 26 21.10

ScreenHunter_237 Sep. 26 21.10

 

Consultas a dados referentes ao passado

  • Muitas vezes, para evitar o crescimento desmedido do banco de dados, informações referentes ao passado são eliminadas.
  • Entretanto, estas informações podem ser necessárias no futuro, por exemplo, por motivos legais, para realização de auditorias ou para tomada de decisões. Portanto, é necessário planejar, desde a modelagem, por quanto tempo as informações ficarão armazenadas no banco de dados.
  • Caso informações antigas fiquem no banco de dados, podem ser necessários atributos para indicar o status da informação, se atual ou antiga.

 

Planejar o arquivamento de informações antigas

  • Para as informações que serão retiradas do banco de dados e armazenadas em arquivos convencionais, é necessário fazer um planejamento de como estas informações serão acessadas no futuro, caso venham a ser necessárias.
  • Uma solução que poderia ser considerada, é a de reincluir as informações no banco de dados, quando elas forem necessárias no futuro. Isso permite que, para buscar as informações passadas, sejam usados os mesmos procedimentos que são usados para acessar as informações atuais.
  • Entretanto, é necessário considerar que as informações em um banco de dados estão normalmente relacionadas a outras. Casos as ocorrências de entidade que se deseja devolver à base de dados estejam relacionadas a outras ocorrências, é necessário que estas estejam presentes.
  • Se elas também tiverem sido excluídas, deverão ser igualmente devolvidas à base de dados. Essa inclusão pode ser propagada em cascata para outras entidades.

 

Planejar informações estatísticas

  • Em alguns casos, informações antigas são necessárias apenas para tomada de decições. Neste caso, muitas vezes deseja-se apenas dados resultantes de cálculos ou estatísticas sobre as informações, como totais, contagens, médias, etc.
  • Assim pode ser conveniente manter o banco de dados estas informações compiladas e eliminar as informações usadas na compilação.

GitHub

learningdatabase.com.br

Tecnologias em Banco de Dados Relacionais, Modelagem de dados dimencionais, tecnologias SQL Servere e Oracle

Aprendendo Programação

Algorítmos, Linguagem C, C++,Pascal, Python, R

WikiDBA

by Virendra Yaduvanshi - Microsoft SQL Server Database Architect | Consultant | Blogger | Specialist | DBA | Speaker

Blog - Fabiano Neves Amorim

SELECT * FROM [Coisas Da Minha Cabeça] WHERE dbo.fn_TempoParaPost() < dbo.fn_TempoLivre()

ROMANO DBA

Administração de Bancos de Dados

Tércio Costa, Oracle Developer, OCE SQL, ACE Associate

Guia de estudos para certificação ORACLE SQL(1Z0-047, 1Z0-051, 1Z0-061 e 1Z0-071) e PL/SQL(1Z0-144, 1Z0-146 e 1Z0-148)

Strate SQL

Data Adventures with an Architect