exercicio_darmires.sql

-- Verificando as tabelas no esquema do usuário
SELECT table_name FROM user_tables;

-- Criando as tabelas Referentes a Engenheiro, Projeto e Atuação
CREATE TABLE engenheiro (
	cod_eng NUMBER PRIMARY KEY,
	nome VARCHAR2(30) NULL,
	salario number(15,2) NULL
);

CREATE TABLE projeto (
	cod_proj NUMBER PRIMARY KEY,
	titulo VARCHAR2(30) NOT NULL,
	area VARCHAR2(30) NULL
);

CREATE TABLE atuacao (
	cod_eng NUMBER,
	cod_proj NUMBER,
	funcao VARCHAR2(30),
	CONSTRAINT PK_atuacao PRIMARY KEY(cod_eng, cod_proj)
);

ALTER TABLE atuacao ADD CONSTRAINT FK_eng FOREIGN KEY(cod_eng) REFERENCES engenheiro;
ALTER TABLE atuacao ADD CONSTRAINT FK_proj FOREIGN KEY(cod_proj) REFERENCES projeto;


-- 3) Insira 3 registros em cada tabela.
INSERT INTO engenheiro VALUES (1, 'José', 400.50);
INSERT INTO engenheiro VALUES (2, 'Maria', 800.00);
INSERT INTO engenheiro VALUES (3, 'João', 300.00);
INSERT INTO engenheiro VALUES (4, 'Fernando', 3000.00);

INSERT INTO projeto VALUES (1, 'Ed Ze Ramalho', 'Fundação');
INSERT INTO projeto VALUES (2, 'Escola Raimundinho', 'Pintura');
INSERT INTO projeto VALUES (3, 'Sala Urbana', 'Design');
INSERT INTO projeto VALUES (4, 'Sala Urbana', 'Design');


INSERT INTO atuacao VALUES (1, 2, 'Supervisor');
INSERT INTO atuacao VALUES (2, 3, 'Pedreiro');
INSERT INTO atuacao VALUES (3, 1, 'So olha');
INSERT INTO atuacao VALUES (1, 4, 'So olha');

-- 4) Verifique a média de salário dos engenheiros.
SELECT AVG(salario) Média  FROM engenheiro;

-- 5) Verifique os nomes dos engenheiros que ganham acima da média.
SELECT nome AS "Engenheiro" FROM engenheiro
WHERE salario > (SELECT AVG(salario) Média  FROM engenheiro);
-- a) Não consigo fazer com JOIN, a tabela não possui ao menos um auto referenciamento

-- 6) Mostre de forma agrupada as áreas de projetos e a quantidade de engenheiros por área.
SELECT p.area, (COUNT(a.cod_eng)) AS "Quantidade"
FROM projeto p JOIN atuacao a ON p.cod_proj = a.cod_proj
GROUP BY p.area;

-- 6) a) Refaça a mesma consulta só que considerando agora somente os engenheiros cujo salário seja menor que a media dos salários. Onde você colocou esta condição?
SELECT p.area, (count((SELECT cod_eng FROM engenheiro x WHERE a.cod_eng = x.cod_eng and x.salario < (SELECT AVG(salario) Média  FROM engenheiro) ))) AS "Quantidade"
FROM projeto p JOIN atuacao a ON p.cod_proj = a.cod_proj
GROUP BY p.area;

SELECT p.area, count(*) AS "Quantidade"
FROM projeto p JOIN atuacao a ON p.cod_proj = a.cod_proj
WHERE a.cod_eng IN (SELECT cod_eng FROM engenheiro WHERE salario < (SELECT AVG(salario) Média  FROM engenheiro))
GROUP BY p.area;

-- 7) Verifique o seguinte comando:
-- O que ele faz? Reescreva utilizando JOIN
-- R: Mostra os engenheiros que atuam em projetos cujo as áres são 'BD'

SELECT e.nome 
FROM engenheiro e JOIN atuacao a ON e.cod_eng = a.cod_eng
JOIN projeto p ON a.cod_proj = p.cod_proj
WHERE p.area LIKE 'Design';

-- 8) Verifique o seguinte comando:
-- a) Usando Subquery
SELECT cod_eng FROM engenheiro WHERE cod_eng IN (SELECT cod_eng FROM atuacao) AND salario > 400;
-- b) Depois refaça-o usando JOIN.
SELECT DISTINCT e.cod_eng 
FROM engenheiro e JOIN atuacao a ON e.cod_eng = a.cod_eng
WHERE e.salario > 400;
-- SIM

-- 9) Verifique agora o seguinte comando:
-- Subquery
SELECT cod_eng FROM engenheiro
WHERE cod_eng NOT IN (SELECT cod_eng FROM atuacao);
-- JOIN
SELECT e.cod_eng
FROM engenheiro e LEFT JOIN atuacao a ON e.cod_eng = a.cod_eng
WHERE a.cod_eng IS NULL;

-- 10) Que índices foram criados automaticamente quando a tabela Projeto recebeu constraints?
-- R: Todas as primary keys criaram índices
-- 11) Que índice poderia ser criado agora para a tabela projeto, considerando que ela tem um volume de dados muito grande?? Justifique. 
-- R: Na coluna 'área' poderiamos criar um índice para buscar pelos projetos que fossem de alguma área
-- 12) Crie uma view mostrando os projetos e seus engenheiros.

CREATE OR REPLACE VIEW  eng_proj AS (SELECT e.nome, p.titulo
FROM engenheiro e JOIN atuacao a ON e.cod_eng = a.cod_eng
JOIN projeto p ON a.cod_proj = p.cod_proj);

SELECT * from eng_proj;

-- 13) Conceda o privilégio de select sobre a visão para seu colega. Peça para ele consultar a view. 
GRANT SELECT ON eng_proj TO hr;