Módulo 14

CREATE TABLE representantes_venda(
    id NUMBER,
    nome VARCHAR2(50),
    salario NUMBER,
    comissao NUMBER
);

DROP TABLE representantes_venda;

DROP TABLE representantes_venda;

SELECT * FROM representantes_venda;

ALTER TABLE respresentantes_venda
RENAME TO representantes_venda;

DESC representantes_venda;


--SELECT COM INSERT
INSERT INTO
    (SELECT nome, id,
            salario, comissao
     FROM representantes_venda)
VALUES ('William', 100,
        10000, 0.5);


-- USANDO SUBQUERY COMO SE FOSSE UMA VIEW

SELECT a.last_name, a.salary,
       a.department_id, b.salavg
FROM hr.employees a, (SELECT department_id,
                             AVG(salary) salavg
                     FROM hr.employees
                     GROUP BY department_id) b
WHERE a.department_id = b.department_id
    AND a.salary > b.salavg;

-- UPDATE USANDO SUBQUERY

UPDATE representantes_venda
    SET id = (SELECT employee_id
                     FROM hr.employees
                     WHERE employee_id = 103),
        comissao = (SELECT commission_pct
                     FROM hr.employees
                     WHERE employee_id = 122);

SELECT * FROM representantes_venda;

-- DELETE USANDO SUBQUERY

DELETE FROM representantes_venda
    WHERE id IN (100);

-- WITH CHECK OPTION EM UM DML

INSERT INTO
    (SELECT nome, id,
            salario, comissao
     FROM representantes_venda
     WHERE salario = 10000
     WITH CHECK OPTION) -- VALIDAÇÃO PARA VERIFICAR VALORES QUE NÃO RETORNAM NA SUBQUERY.
VALUES ('William', 100,
        10000, 0.5);

ALTER TABLE representantes_venda
 MODIFY salario NUMBER(10) DEFAULT (500);

INSERT INTO representantes_venda
    (id, nome, salario, comissao)
 VALUES(200, 'TESTE DEFAULT', DEFAULT, NULL); -- DEFAULT EXPLICITO

SELECT * FROM representantes_venda;

UPDATE representantes_venda
SET salario = DEFAULT
WHERE id = 100;

CREATE TABLE hist_salario (
    employee_id         NUMBER(10),
    hire_date           DATE,
    salary              NUMBER(10)
);

CREATE TABLE hist_salario_mng(
    employee_id         NUMBER(10),
    manager_id          NUMBER(10),
    salary              NUMBER(10)
);

--INSERT ALL SEM CONDICIONAL
INSERT ALL
INTO hist_salario       VALUES(EMPID, HIREDATE, SAL)
INTO hist_salario_mng   VALUES(EMPID, MGR, SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
       salary SAL, manager_id MGR
FROM hr.employees
WHERE employee_id > 200;

SELECT * FROM hist_salario_mng;

-- INSERT ALL CONDICIONAL

INSERT ALL
WHEN SAL > 10000 THEN
    INTO hist_salario
        VALUES(EMPID, HIREDATE, SAL)
WHEN MGR > 200 THEN
    INTO hist_salario_mng
        VALUES(EMPID, MGR, SAL)
SELECT employee_id EMPID, hire_date HIREDATE,
       salary SAL, manager_id MGR
 FROM hr.employees
WHERE employee_id > 200;

SELECT * FROM hist_salario;

SELECT * FROM hist_salario_mng;

-- INSERT FIRST

CREATE TABLE sal_especial(
    department_id       NUMBER(10),
    salary              NUMBER(10)
);

CREATE TABLE hist_contratacao_07(
    department_id       NUMBER(10),
    hire_date           DATE
);

DROP TABLE hist_CONTRATACAO_07;
CREATE TABLE hist_contratacao(
    department_id       NUMBER(10),
    hire_date           DATE
);

INSERT FIRST
WHEN SAL > 25000 THEN
  INTO sal_especial
    VALUES(DEPTID, SAL)
WHEN HIREDATE LIKE ('%07%') THEN
  INTO hist_contratacao_07
    VALUES(DEPTID, HIREDATE)
ELSE
  INTO hist_contratacao
    VALUES(DEPTID, HIREDATE)
SELECT department_id DEPTID,
       SUM(salary) SAL,
       MAX(hire_date) HIREDATE
FROM hr.employees
GROUP BY department_id;

SELECT * FROM sal_especial;

SELECT * FROM hist_contratacao_07;

SELECT * FROM hist_contratacao;

-- INSERT COM PIVOT

CREATE TABLE info_vendas_geral(
    employee_id         NUMBER(10),
    semana_id           NUMBER(10),
    vendas_SEG          NUMBER(10),
    vendas_TER          NUMBER(10),
    vendas_QUA          NUMBER(10),
    vendas_QUI          NUMBER(10),
    vendas_SEX          NUMBER(10)
);

INSERT INTO info_vendas_geral
    VALUES (200, 19, 300, 500, 1000, 30, 5000);

SELECT * FROM info_vendas_geral;

CREATE TABLE info_vendas(
    employee_id     NUMBER(10),
    semana_id       NUMBER(10),
    dia_id          NUMBER(2),
    vendas          NUMBER(10)
);

INSERT ALL
    INTO info_vendas
        VALUES(employee_id, semana_id, 1, vendas_SEG)
    INTO info_vendas
        VALUES(employee_id, semana_id, 2, vendas_TER)
    INTO info_vendas
        VALUES(employee_id, semana_id, 3, vendas_QUA)
    INTO info_vendas
        VALUES(employee_id, semana_id, 4, vendas_QUI)
    INTO info_vendas
        VALUES(employee_id, semana_id, 5, vendas_SEX)
SELECT employee_id, semana_id, vendas_SEG, vendas_TER,
        vendas_QUA, vendas_QUI, vendas_SEX
FROM info_vendas_geral;

SELECT * FROM info_vendas;

-- INSTRUÇÃO MERGE

CREATE TABLE empl3
AS
    (SELECT * FROM hr.employees
        WHERE department_id IN (20, 30, 40));

SELECT * FROM empl3
ORDER BY employee_id DESC;

MERGE INTO empl3 c
USING hr.employees e
    ON(c.employee_id = e.employee_id)
WHEN MATCHED THEN
    UPDATE SET
    c.first_name = e.first_name,
    c.last_name = e.last_name,
    c.email = e.email,
    c.phone_number = e.phone_number,
    c.hire_date = e.hire_date,
    c.job_id = e.job_id,
    c.salary = e.salary,
    c.commission_pct = e.commission_pct,
    c.manager_id = e.manager_id,
    c.department_id = e.department_id
WHEN NOT MATCHED THEN
    INSERT VALUES (e.employee_id, e.first_name, e.last_name,
                   e.email, e.phone_number, e.hire_date,
                   e.job_id, e.salary, e.commission_pct,
                   e.manager_id, e.department_id);

-- CONTROLANDO ALTERAÇÕES NO BANCO DE DADOS

SELECT salary FROM empl3
WHERE employee_id = 110;

UPDATE empl3
    SET salary = salary * 1.5
WHERE employee_id = 110;

SELECT salary,
       versions_starttime,
       versions_endtime
FROM empl3
VERSIONS BETWEEN
    SCN MINVALUE AND MAXVALUE
WHERE employee_id = 110;

/* Crie as seguintes tabelas:
1º func_ricos( employee_id number(10), salary NUMBER(10,2)),
2º ger_hist(employee_id(10), salary number(10,2), manager_id number(10))
3º salario (employee_id number(10), hire_date date, salary number(10,2)); */

CREATE TABLE func_ricos (
    employee_id     NUMBER(10),
    salary          NUMBER(10,2)
);

CREATE TABLE ger_hist (
    employee_id NUMBER(10),
    salary NUMBER(10,2),
    manager_id NUMBER(10)
);

CREATE TABLE salario (
    employee_id NUMBER(10),
    hire_date   DATE,
    salary      NUMBER(10,2)
);



/* Agora crie uma consulta que faça o seguinte:
1º Recuper o id do funcionário, data de admissão, salário e o id somente dos gerentes dos funcionários que possuem gerente;
2º Quando o funcionário receber mais de 15.000 ele deve ser incluído na tabela func_ricos;
3º Insira os dados do funcionários que foram admitidos entre os anos de 2002 e 2005 na tabela salario;
4º O restante dos funcionários devem ser inseridos na tabela ger_hist.
Lembrando que deve-se usar apenas uma query para isso. */

INSERT FIRST
 WHEN SAL > 15000 THEN
    INTO func_ricos
      VALUES(EMPD, SAL)
 WHEN HIREDATE LIKE ('%02') OR HIREDATE LIKE ('%05') THEN
    INTO salario
        VALUES(EMPD, HIREDATE, SAL)
 ELSE
    INTO ger_hist
        VALUES(EMPD, SAL, MGR)
SELECT employee_id EMPD, hire_date HIREDATE,
       salary SAL, manager_id MGR
FROM hr.employees
 WHERE manager_id IS NOT NULL;

/* Crie as tabelas vendas com a seguinte estrutura:
1º Tabela vendas semanais vendas_sem (employee_id number(10), semana_id number(10), vendas_seg number(10), vendas_ter number(10), vendas_qua number(10), vendas_qui number(10), vendas_sex number(10));
2º Insira uma linha aleatória que na tabela vendas_sem;
3º Cria a tabela vendas (employee_id number(10), semana_id number(10), qtd_venda number(10), dia_sem number(1));
4º Insira os dados com o insert pivot da tabela vendas_sem na tabela vendas.*/

CREATE TABLE vendas_sem (
    employee_id NUMBER(10),
    semana_id NUMBER(10),
    vendas_seg NUMBER(10),
    vendas_ter NUMBER(10),
    vendas_qua NUMBER(10),
    vendas_qui NUMBER(10),
    vendas_sex NUMBER(10)
);

INSERT INTO vendas_sem VALUES
(1, 12, 300, 400, 500, 600, 1500);

SELECT * FROM vendas_sem;

CREATE TABLE vendas (
    employee_id NUMBER(10),
    semana_id NUMBER(10),
    qtd_venda NUMBER(10),
    dia_sem NUMBER(1)
);

INSERT ALL
    INTO vendas
        VALUES(employee_id, 12, vendas_seg, 1)
    INTO vendas
        VALUES(employee_id, 12, vendas_ter, 2)
    INTO vendas
        VALUES(employee_id, 12, vendas_qua, 3)
    INTO vendas
        VALUES(employee_id, 12, vendas_qui, 4)
    INTO vendas
        VALUES(employee_id, 12, vendas_sex, 5)
SELECT employee_id, semana_id, vendas_seg,
       vendas_ter, vendas_qua, vendas_qui,
       vendas_sex
FROM vendas_sem;

/* 1º Cria a tabela employee_4 com os dados idênticos à tabela employees, com todos os funcionários que ganham menos de 6000;
2º Faça uma consulta que caso o employee_id da tabela employees seja igual ao employee_id da tabela employees_4,
os dados do empregado devem ser atualizado, em caso contrário o funcionário deve ser inserido na tabela employee_4;*/

CREATE TABLE employee_4 AS
    (SELECT * FROM hr.employees
            WHERE salary < 6000);

MERGE INTO employee_4 e4
    USING hr.employees e
       ON (e4.employee_id = e.employee_id)
WHEN MATCHED THEN
    UPDATE
        SET e4.first_name = e.first_name,
            e4.last_name = e.last_name,
            e4.email = e.email,
            e4.phone_number = e.phone_number,
            e4.hire_date = e.hire_date,
            e4.job_id = e.job_id,
            e4.salary = e.salary,
            e4.commission_pct = e.commission_pct,
            e4.manager_id = e.manager_id,
            e4.department_id = e.department_id
            WHERE e.employee_id = e4.employee_id
WHEN NOT MATCHED THEN
  INSERT VALUES (e.employee_id, e.first_name, e.last_name,
                 e.email, e.phone_number, e.hire_date,
                 e.job_id, e.salary, e.commission_pct,
                 e.manager_id, e.department_id);

/* 1º Atualize o salário do funcionário 100 para 25000;
2º Mostre todas as alterações nesse campo desse funcionário e quando eles começaram a ser válidas e quando elas deixaram de ser válidas.*/

UPDATE employee_4
SET salary = 25000
WHERE employee_id = 100;

SELECT salary,
       versions_starttime,
       versions_endtime
FROM employee_4
VERSIONS BETWEEN
    SCN MINVALUE AND MAXVALUE
WHERE employee_id = 100;

/* TIPOS DE SUBCONSULTAS

Emparelhadas - duas ou mais colunas da consulta externa, com duas ou mais consultas de uma subconsulta (consulta interna).

Não Emparelhadas - Repete a consulta interna duas ou mais vezes

Escalares - Subconsulta que retorna somente um registro na consulta interna, por esse motivo ela pode ser utilizada em todas as clausulas do SELECT
menos no GROUP BY e no CONNECT BY, pode ser utilizadas como parte da expressão e da condição DECODE e CASE.

Correlacionadas - subconsulta que pega uma informação da consulta externa e compara com uma informação da consulta interna para trazer o resultado desejado.
Elas são processadas uma vez para cada linha retornada na consulta externa. Basicamente ela segue o seguinte fluxo:

1. Retorna a linha da consulta Externa;
2. Executa consulta interna;
3. verifica se o valor da consulta externa deve ou não retornar.

é possível usar ANY eo ALL neste tipo de subconsulta

*/

--RETORNA CONSULTA DE TODOS OS EMPREGADOS QUE TENHAM O MESMO MANAGER_ID E DEPARTMENT_ID IGUAIS AO EMPLOYEE_ID 104 E 105 E NÃO MOSTRAR OS FUNCIONÁRIOS NESSA PESQUISA.
--Emparelhadas - duas ou mais colunas da consulta externa, com duas ou mais consultas de uma subconsulta (consulta interna).

SELECT employee_id, manager_id, department_id
 FROM hr.employees
    WHERE (manager_id, department_id) IN
                                (SELECT manager_id, department_id
                                 FROM hr.employees
                                 WHERE employee_id IN (104, 105))
                    AND employee_id NOT IN (104, 105);

--RETORNA CONSULTA DE TODOS OS EMPREGADOS QUE TENHAM O MESMO MANAGER_ID E DEPARTMENT_ID IGUAIS AO EMPLOYEE_ID 104 E 105 E NÃO MOSTRAR OS FUNCIONÁRIOS NESSA PESQUISA.
--Não Emparelhadas - Repete a consulta interna duas ou mais vezes

SELECT employee_id, manager_id, department_id
 FROM hr.employees
WHERE manager_id IN
    (SELECT manager_id
     FROM hr.employees
     WHERE employee_id IN (104,105))
 AND department_id IN
    (SELECT department_id
     FROM hr.employees
     WHERE employee_id IN (104,105))
 AND employee_id NOT IN (104,105);


/* Escalares - Subconsulta que retorna somente um registro na consulta interna, por esse motivo ela pode ser utilizada em todas as clausulas do SELECT
menos no GROUP BY e no CONNECT BY, pode ser utilizadas como parte da expressão e da condição DECODE e CASE.*/

SELECT * FROM hr.employees
 WHERE department_id =
        (SELECT department_id FROM hr.departments
         WHERE department_name = 'IT');

/*Correlacionadas - subconsulta que pega uma informação da consulta externa e compara com uma informação da consulta interna para trazer o resultado desejado.
Elas são processadas uma vez para cada linha retornada na consulta externa. Basicamente ela segue o seguinte fluxo:

1. Retorna a linha da consulta Externa;
2. Executa consulta interna;
3. verifica se o valor da consulta externa deve ou não retornar.

é possível usar ANY eo ALL neste tipo de subconsultA*/

SELECT * FROM hr.employees emp
    WHERE salary >
         (SELECT AVG(salary)
          FROM hr.employees
          WHERE department_id = emp.department_id);


-- OPERADOR EXISTS
/* O operador EXISTS é utilizado para verificar se o resultado da consulta interna é retornado
ele é muito mais performático que o IN, uma vez que o EXISTS só retorna TRUE ou FALSE,
enquanto o IN compara com todos os resultados da consulta iterna o que acaba honerando o
banco fazendo com que a consulta seja mais lenta */

SELECT * FROM hr.employees emp
 WHERE EXISTS (SELECT 1
               FROM hr.employees
               WHERE manager_id = emp.employee_id);

SELECT * FROM hr.departments d
    WHERE NOT EXISTS (SELECT employee_id
                      FROM hr.employees
                      WHERE department_id = d.department_id);

-- CLAUSULA WITH
/* WITH se ganha performance dentro de subquerys complexas.

Esta cláusula cria uma tabela temporaria detro do Oracle, com isso armazenamos o resultado de uma consulta nesse espaço físico temporário
e ele funciona como uma tabela à consulta que for utiliza-la, este tipo de recurso auxilia e muito em casos de problemas de performance,
porém devemos tomar cuidado ao utiliza-lo uma vez que ele consome muita memoria do banco e em casos de consultas muito utilizidas
isso pode gerar outros problemas de oneração do hardware.

*/

WITH
    dept_custo AS (
        SELECT d.department_name, SUM(e.salary) AS dept_total
         FROM hr.employees e, hr.departments d
        WHERE e.department_id = d.department_id
        GROUP BY d.department_name), -- SOMA SALARIAL POR DEPARTAMENTO
    avg_custo AS (
        SELECT SUM(dept_total)/COUNT(*) AS dept_avg
         FROM dept_custo) -- MÉDIA SARIAL POR DEPARTAMENTO
    SELECT * FROM dept_custo
    WHERE dept_total >
                   (SELECT dept_avg
                    FROM avg_custo) -- DEPARTAMENTOS QUE RECEBM A MAIOR MÉDIA DOS DEPARTAMENTOS
ORDER BY department_name;