Módulo 10

CREATE OR REPLACE VIEW VW_PROGRAMADORES (id, nome, salario_anual)
AS
SELECT employee_id, first_name, salary*12
FROM hr.employees
WHERE job_id = 'IT_PROG';

DESC VW_PROGRAMADORES;

SELECT * FROM VW_PROGRAMADORES;

-- MAIOR SALARIO, MENOR SALARIO, E MEDIA SALARIAL DE TODOS OS CARGOS

CREATE VIEW calc_salary (titulo_job, sal_maior, sal_menor, media_sal)
AS
SELECT j.job_title,
       MAX(e.salary),
       MIN(e.salary),
       AVG(e.salary)
FROM hr.employees e
INNER JOIN hr.jobs j
ON j.job_id = e.job_id
GROUP BY j.job_title;

--WITH CHECK OPTION
--SÓ PERMITE ALTERAR CASO A CLAUSULA WHERE SEJA CORRESPONDIDA
CREATE OR REPLACE VIEW vw_depto50
AS
SELECT * FROM hr.employees
WHERE department_id = 50
WITH CHECK OPTION CONSTRAINT ck_dp50;

UPDATE vw_depto50
SET salary = salary + 10
WHERE department_id = 50;

--WITH READ ONLY
-- PERMITE APENAS LEITURA DA VIEW
CREATE OR REPLACE VIEW vw_depto20 (id, nome, cargo)
AS
SELECT employee_id, first_name, job_id
FROM hr.employees
WHERE department_id = 20
WITH READ ONLY;

--INDICES
CREATE INDEX nome_empregado
    ON hr.employees(first_name);

DROP INDEX nome_empregado;

-- SEQUENCE
CREATE SEQUENCE sq_teste_01
INCREMENT BY 15
START WITH 34
MAXVALUE 99999999
NOCACHE
NOCYCLE;

INSERT INTO teste_01 (teste_numero, teste_char)
VALUES (sq_teste_01.NEXTVAL, 'Lucas');

SELECT sq_teste_01.CURRVAL FROM dual;

SELECT * FROM teste_01;

DROP SEQUENCE sq_teste_01;

-- SINÔNIMOS(ALTERAR O NOME DA TABELA PARA CONSULTA)
--PUBLICO (TODOS OS USUARUIOS) E PRIVADO (APENAS QUEM CRIOU, POR PADRÃO)

CREATE PUBLIC SYNONYM teste_tablea
FOR teste_01;

CREATE SYNONYM teste_tablea
FOR teste_01;

SELECT * FROM teste_tablea;

DROP SYNONYM teste_tablea;

DROP PUBLIC SYNONYM teste_tablea;

/* Faça uma VIEW que retorne o nome do funcionário, o id, data de entrada na empresa e salário, de todos os funcionários com o cargo de TI,
com o nome vw_programadores. As colunas dessa view devem ter o mesmo nome dos representados nessa questão.*/

CREATE OR REPLACE VIEW VW_PROGRAMADORES (last_name, employee_id, hire_date)
AS
SELECT last_name, employee_id, hire_date
FROM hr.employees
WHERE job_id = 'IT_PROG';

SELECT * FROM vw_programadores;

/* Utilize a view vw_programadores para retornar todos os programadores que recebem mais de 2500 dólares.*/

SELECT * FROM vw_programadores v
INNER JOIN hr.employees e
ON v.employee_id = e.employee_id
WHERE salary > 2500;

/* Crie uma view que retorne o nome do cargo, o salário máximo, salário mínimo,
a soma dos salários e a média de salário dos funcionário. Sendo que o nome do cargo deve ser retornado da tabela JOBs.*/

CREATE OR REPLACE VIEW info_salary (nome_cargo, max_sal,
                                    min_sal, soma_sal,
                                    media_sal)
AS
SELECT j.job_title,
       MAX(e.salary),
       MIN(e.salary),
       SUM(e.salary),
       AVG(e.salary)
FROM hr.employees e
INNER JOIN hr.jobs j
ON j.job_id = e.job_id
GROUP BY j.job_title;

SELECT * FROM info_salary;

/* Cria uma SEQUENCE que comece em 500, seja incrementada de 1 em 1, tenha o valor máximo de 9999 o nome dessa SEQUENCE deve ser sq_teste_employes*/

CREATE SEQUENCE sq_teste_employes
START WITH 500
INCREMENT BY 1
MAXVALUE 9999
NOCYCLE
NOCACHE;

/* Insira um registro na tabela employees utilizando a sequence sq_teste_employes, após isso selecione o valor atual dessa sequence.*/

INSERT INTO teste_01 (teste_numero, teste_char)
VALUES (sq_teste_employes.NEXTVAL, 'Sequence');

SELECT sq_teste_employes.CURRVAL FROM dual;

/* Crie um índice na coluna hire_date da tabela employees*/

CREATE INDEX data_admissao
    ON hr.employees(hire_date);

/* crie um sinônimo tabela jobs com o nome JB*/

CREATE SYNONYM JB
FOR hr.jobs;

SELECT * FROM JB;