Lista 4 - Resolvida (Script Consultas - JOIN, NATURAL JOIN, LEFT JOIN, UNION)

Prévia do material em texto

-- -----------------------------------------------------
-- EXERCICIO Nº1
-- -----------------------------------------------------
Selecione o nome e o endereço do empregado de nome ‘Luciana S. Santos’.
SELECT NomeFunc, Endereco 
FROM Funcionario
WHERE NomeFunc = 'Luciana S. Santos';
-- -----------------------------------------------------
-- EXERCICIO Nº2
-- -----------------------------------------------------
Selecione o nome e o salário dos empregados que nasceram na década de 60, do sexo feminino
e que ganham menos de 1000.
SELECT NomeFunc, Salario
FROM Funcionario
WHERE (DataNasc >=1960/01/01 AND 1969/31/12) AND Sexo = 'F' and Salario <1000;
-- -----------------------------------------------------
-- EXERCICIO Nº3
-- -----------------------------------------------------
Selecione o nome dos dependentes do empregado de nome ‘Luciana S. Santos’.
SELECT NomeDep
FROM Dependente D
JOIN Funcionario F 
WHERE (D.ID_Func = F.ID_Func AND NomeFunc = 'Frank T. Santos');
-- -----------------------------------------------------
-- EXERCICIO Nº4
-- -----------------------------------------------------
SELECT NomeProj
FROM Funcionario f, Trabalha t, Projeto p
WHERE f.NomeFunc = 'Frank T. Santos'AND p.ID_Proj = t.ID_Proj and t.id_func=f.id_func;
SELECT NomeProj
FROM Funcionario F 
JOIN Trabalha T on F.ID_Func = T.ID_Func and F.NomeFunc = 'Frank T. Santos'
JOIN Projeto P on (P.ID_Proj = T.ID_Proj);
-- -----------------------------------------------------
-- EXERCICIO Nº5
-- -----------------------------------------------------
SELECT NomeFunc
FROM Funcionario F Join (
Trabalha T JOIN (
Projeto P JOIN Departamento D 
ON (D.NomeDepto = 'Construção' and D.ID_Depto = P.ID_Depto)) 
ON (T.ID_Proj = P.ID_Proj))
WHERE F.ID_Func = T.ID_Func;
-- OUTRA FORMA DE FAZER (UTILZANDO INNER JOIN)
SELECT NomeFunc 
FROM Funcionario F
INNER JOIN Trabalha T on F.ID_Func = T.ID_Func
INNER JOIN Projeto P on P.ID_Proj = T.ID_Proj
INNER JOIN Departamento D on D.ID_Depto = P.ID_Depto
WHERE D.NomeDepto = 'Construção';
-- -----------------------------------------------------
-- EXERCICIO Nº6
-- -----------------------------------------------------
SELECT NomeFunc
FROM Funcionario f JOIN
(SELECT ID_Func from Funcionario where NomeFunc = 'Frank T. Santos') P
WHERE P.ID_Func = f.ID_superv;
-- outra maneira de fazer
SELECT Func.NomeFunc
FROM Funcionario AS Func, Funcionario AS Sup
WHERE Func.ID_Superv = Sup.ID_Func AND Sup.NomeFunc = 'Frank T. Santos';
-- SEM SELECT ANINHADO
SELECT E.NomeFunc
FROM Funcionario E JOIN Funcionario S 
ON (E.ID_Superv = S.ID_Func) 
WHERE S.NomeFunc = 'Frank T. Santos';
-- -----------------------------------------------------
-- EXERCICIO Nº7
-- -----------------------------------------------------
SELECT DISTINCT NomeFunc, Endereco 
FROM Funcionario
WHERE ID_Func NOT IN (SELECT ID_Func from Dependente);
SELECT nomeFunc, Endereco
FROM Funcionario F left outer Join Dependente D on F. id_func = D.id_func
WHERE nomeDep is null;
-- -----------------------------------------------------
-- EXERCICIO Nº8
-- -----------------------------------------------------
SELECT NomeFunc
FROM Funcionario NATURAL JOIN Departamento
WHERE NomeDepto = 'Pesquisa'
UNION
(SELECT NomeFunc
FROM Funcionario F JOIN 
Trabalha T natural JOIN
Projeto P 
ON P.NomeProj = 'N. Benefícios'
WHERE F.ID_Func = T.ID_Func);
-- OUTRA MANEIRA DE FAZER 
SELECT NomeFunc
FROM Funcionario NATURAL JOIN Departamento
WHERE NomeDepto = 'Pesquisa'
UNION
(SELECT NomeFunc
FROM Funcionario F
JOIN(
SELECTt ID_Func
FROM Trabalha T NATURAL JOIN Projeto P
WHERE NomeProj = 'N. Beneficios') S
ON S.ID_Func = F.ID_Func);
-- OUTRA FORMA DE FAZER
SELECT NomeFunc
FROM Funcionario NATURAL JOIN Departamento
WHERE NomeDepto = 'Pesquisa'
UNION
(SELECT NomeFunc
FROM Funcionario	
NATURAL JOIN Trabalha
JOIN(SELECT *
FROM Departamento
NATURAL JOIN Projeto
WHERE NomeProj = 'N. Beneficios') F ON F.ID_Proj = Trabalha.ID_Proj);
-- OUTRA FORMA DE FAZER
SELECT NomeFunc
FROM Funcionario NATURAL JOIN Departamento
WHERE NomeDepto = 'Pesquisa' OR NomeFunc
IN(
SELECT NomeFunc
FROM Funcionario F JOIN 
Trabalha T natural join 
Projeto P 
ON P.NomeProj = 'N. Benefícios'
WHERE F.ID_Func = T.ID_Func);
-- -----------------------------------------------------
-- EXERCICIO Nº9
-- -----------------------------------------------------
Selecione o nome dos empregados que trabalham em ALGUM projeto controlado pelo
departamento cujo gerente é o empregado de nome ‘Frank T. Santos’.
SELECT NomeFunc, ID_Func
FROM Funcionario 
WHERE ID_Func = some
(SELECT T.ID_Func
FROMTrabalha T 
JOIN Projeto P on P.ID_Proj = T.ID_Proj 
JOIN Departamento D ON D.ID_Depto = P.ID_Depto
JOIN Funcionario F ON F.ID_Func = D.ID_Gerente AND NomeFunc = 'Frank T. Santos');
-- -----------------------------------------------------
-- EXERCICIO Nº10
-- -----------------------------------------------------
Selecione o nome dos empregados que trabalham em TODOS projeto controlado pelo
departamento cujo gerente é o empregado de nome ‘Frank T. Santos’;
SELECT NomeFunc, ID_Func
FROM Funcionario 
WHERE ID_Func = all
(SELECT T.ID_Func
FROM Trabalha T 
JOIN Projeto P on P.ID_Proj = T.ID_Proj 
JOIN Departamento D ON D.ID_Depto = P.ID_Depto
JOIN Funcionario F ON F.ID_Func = D.ID_Gerente and NomeFunc = 'Frank T. Santos');
-- -----------------------------------------------------
-- EXERCICIO Nº11
-- -----------------------------------------------------
SELECT NomeFunc, NomeDep
	FROM Funcionario F LEFT OUTER JOIN Dependente D ON (F.id_func = D.id_func);
-- -----------------------------------------------------
-- EXERCICIO Nº12
-- -----------------------------------------------------
SELECT count(ID_Func)
	FROM Trabalha NATURAL JOIN Projeto
	WHERE NomeProj = 'Prodz';
-- -----------------------------------------------------
-- EXERCICIO Nº13
-- -----------------------------------------------------
SELECT ID_Func, nomeFunc, count(ID_Proj)
	FROM Funcionario NATURAL JOIN Trabalha
	WHERE numHoras > 10
	GROUP BY id_func, nomeFunc;
-- -----------------------------------------------------
-- EXERCICIO Nº14
-- -----------------------------------------------------
SELECT id_Func, nomeFunc, count(ID_Func)
	FROM Funcionario F NATURAL JOIN Trabalha T
	GROUP BY id_func, nomeFunc
	HAVING count(ID_Proj)>1;
-- -----------------------------------------------------
-- EXERCICIO Nº15
-- -----------------------------------------------------
(SELECT D.ID_Depto
	FROM Departamento D NATURAL JOIN Projeto
	GROUP BY D.ID_Depto
	HAVING COUNT(*) > 1); 
	SELECT D.ID_depto,count(NomeFunc), ID_Func, nomeDepto, SUM(salario)
	FROM Funcionario F NATURAL JOIN Departamento D
	WHERE D.ID_Depto IN (SELECT D.ID_Depto
				FROM Departamento D NATURAL JOIN Projeto
				GROUP BY D.ID_Depto
				 having count(*)> 1)
	GROUP BY D.ID_Depto, nomeDepto;
-- -----------------------------------------------------
-- EXERCICIO Nº18
-- -----------------------------------------------------
SELECT NomeFunc, ID_Func
FROM Funcionario 
WHERE ID_Func > all
(SELECT F.ID_Func from
Funcionario F, Trabalha T, Projeto P, Departamento D
WHERE (F.ID_Func= T.ID_Func AND T.ID_Proj = P.ID_Proj AND P.ID_Depto = D.ID_Depto AND NomeDepto = 'Construção')
HAVING MAX(F.salario) > 1000.00);
-- -----------------------------------------------------
-- EXERCICIO Nº19
-- -----------------------------------------------------
-- -----------------------------------------------------
-- EXERCICIO Nº20
-- -----------------------------------------------------
UPDATE Funcionario 
	SET Salario = salario * 1.10
	WHERE id_func IN (SELECT id_func
				FROM Trabalha
				GROUP BY id_func
				HAVING count(*) > 1);
-- -----------------------------------------------------
-- EXERCICIO Nº21
-- -----------------------------------------------------DELETE FROM Projeto
	WHERE id_proj NOT IN (SELECT id_proj
				FROM Trabalha);
-- -----------------------------------------------------
-- EXERCICIO Nº22
-- -----------------------------------------------------
-- CRIA VISÃO:
		CREATE VIEW Exerc22 AS
		SELECT id_depto, nomeDepto, nomeFunc, salario
		FROM Funcionario F JOIN Departamento D ON D. id_gerente = F.id_func;
	
-- Exemplo do uso da visão:
		SELECT *
		FROM Exerc22;