Arquivos de Categoria: Select

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;

Video

Anúncios

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

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