Trabalho BD2

Create

CREATE TABLE funcionario(
	id Serial Primary Key,
	nome varchar(64),
	cpf char(9)[],
	salario char(20)[],
	genero char(1),
	data_nascimento date,
	endereco varchar(64)
);

CREATE TABLE departamento(
	id Serial Primary Key,
	nome varchar(64)
);

CREATE TABLE localidade(
	id Serial Primary Key,
	nome varchar(64)
);

CREATE TABLE projeto(
	id Serial Primary Key,
	nome varchar(64),
	id_departamento int references departamento(id),
);

CREATE TABLE dependente(
	id Serial Primary Key,
	parentesco varchar(64),
	genero char(1),
	data_nascimento date
);

CREATE TABLE supervisor(
	id_supervisor int references funcionario(id),
	id_supervisionado int references funcionario(id),
	check (id_supervisor != id_supervisionado)
);

CREATE TABLE dependente_funcionario(
	id_funcionario int references funcionario(id),
	id_dependente int references dependente(id)
);

CREATE TABLE funcionario_alocado_projeto(
	id_funcionario int references funcionario(id),
	id_projeto int references projeto(id),
	horas_trabalhadas int
);

CREATE TABLE local_departamento(
	id_local int references localidade(id),
	id_departamento int references departamento(id)
);

CREATE TABLE funcionario_trabalha_departamento(
	id_funcionario int references funcionario(id),
	id_departamento int references departamento(id)
);

CREATE TABLE funcionario_gerencia_departamento(
	id_funcionario int references funcionario(id),
	id_departamento int references departamento(id),
	data_inicial date
);

CREATE TABLE funcionario_gerencia_departamento(
--	id_funcionario int references funcionario(id),
-- id_departamento int references departamento(id),
	 data_inicial date
);

ALTER TABLE funcionario_gerencia_departamento ADD COLUMN id_funcionario int references funcionario(id);
ALTER TABLE funcionario_gerencia_departamento ADD COLUMN id_departamento int references departamento(id);

Inserts

INSERT INTO departamento (nome) VALUES
('financas'),
('pesquisa'),
('gerencia');

INSERT INTO localidade (nome) VALUES
('sao paulo'),
('vitoria'),
('florianopolis');

INSERT INTO local_departamento(id_local, id_departamento)
(
 (SELECT id FROM localidade WHERE nome='sao paulo'), 
 (SELECT id FROM departamento WHERE nome='financas')
),
(
 (SELECT id FROM localidade WHERE nome='vitoria'), 
 (SELECT id FROM departamento WHERE nome='pesquisa')
),
(
 (SELECT id FROM localidade WHERE nome='florianopolis'), 
 (SELECT id FROM departamento WHERE nome='gerencia')
);

INSERT INTO "funcionario" 
	(nome,cpf,salario,genero,data_nascimento,endereco) VALUES 
	('Judah','000000001' ,2916,'m','20/01/15','P.O. Box 887, 3312 Eu Rd.'),
	('Rachel','000000002' ,2683,'m','29/11/14','843-9587 Euismod Rd.'),
	('Cain','000000003' ,2856,'f','19/12/15','P.O. Box 718, 2386 Sed Ave'),
	('Yen','000000004' ,2925,'m','16/02/16','P.O. Box 929, 4570 Erat Road'),
	('Eric','000000005' ,2637,'m','12/01/16','354 Neque. Ave'),
	('Kasper','000000006' ,2285,'m','07/08/15','2884 Aliquet Avenue'),
	('Caldwell','000000007' ,1275,'f','27/09/15','Ap #340-7439 Scelerisque, Ave'),
	('Drake','000000008' ,2091,'m','24/07/15','569-1405 Accumsan Avenue'),
	('Alexander','000000009' ,2794,'f','10/09/15','168-6312 Dictum. Avenue');

INSERT INTO "dependente" (nome,genero,data_nascimento,parentesco) VALUES 
	('Zelda','m','09/05/2014','pai'),
	('Olivia','m','23/12/2015','filho'),
	('Blair','f','26/06/2014','irmao'),
	('Aquila','m','17/01/2015','irmao'),
	('Alfreda','m','25/11/2014','irmao'),
	('Dacey','f','09/01/2016','pai'),
	('Sasha','f','26/09/2015','irmao'),
	('Clarke','f','30/04/2014','pai'),
	('Tyler','f','20/07/2015','pai');
	 
INSERT INTO dependente_funcionario
	(id_funcionario, id_dependente) VALUES 
	(
		(SELECT id FROM funcionario WHERE nome='Judah'), 
		(SELECT id FROM dependente  WHERE nome='Zelda')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Judah'), 
		(SELECT id FROM dependente  WHERE nome='Olivia')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Judah'), 
		(SELECT id FROM dependente  WHERE nome='Blair')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Rachel'), 
		(SELECT id FROM dependente  WHERE nome='Aquila')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Rachel'), 
		(SELECT id FROM dependente  WHERE nome='Alfreda')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Kasper'), 
		(SELECT id FROM dependente  WHERE nome='Dacey')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Alexander'), 
		(SELECT id FROM dependente  WHERE nome='Sasha')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Alexander'), 
		(SELECT id FROM dependente  WHERE nome='Clarke')
	),
	(
		(SELECT id FROM funcionario WHERE nome='Alexander'), 
		(SELECT id FROM dependente  WHERE nome='Tyler')
	);
	
INSERT INTO projeto
	(nome, id_departamento) VALUES 
	('pj1',  (SELECT id FROM departamento WHERE nome='financas')),
	('pj2',  (SELECT id FROM departamento WHERE nome='gerencia')),
	('pj3',  (SELECT id FROM departamento WHERE nome='financas')),
	('pj4',  (SELECT id FROM departamento WHERE nome='gerencia')),
	('pj5',  (SELECT id FROM departamento WHERE nome='pesquisa')),
	('pj6',  (SELECT id FROM departamento WHERE nome='pesquisa'));
	
INSERT INTO funcionario_alocado_projeto
	(id_funcionario, id_projeto, horas_trabalhadas) VALUES
	((SELECT id FROM funcionario WHERE nome='Judah'), (SELECT id FROM projeto WHERE nome='pj1'), 20),
	((SELECT id FROM funcionario WHERE nome='Rachel'), (SELECT id FROM projeto WHERE nome='pj2'), 30),
	((SELECT id FROM funcionario WHERE nome='Cain'), (SELECT id FROM projeto WHERE nome='pj3'), 70),
	((SELECT id FROM funcionario WHERE nome='Yen'), (SELECT id FROM projeto WHERE nome='pj1'), 90),
	((SELECT id FROM funcionario WHERE nome='Eric'), (SELECT id FROM projeto WHERE nome='pj1'), 10),
	((SELECT id FROM funcionario WHERE nome='Kasper'), (SELECT id FROM projeto WHERE nome='pj2'), 5),
	((SELECT id FROM funcionario WHERE nome='Caldwell'), (SELECT id FROM projeto WHERE nome='pj2'), 25),
	((SELECT id FROM funcionario WHERE nome='Drake'), (SELECT id FROM projeto WHERE nome='pj3'), 37),
	((SELECT id FROM funcionario WHERE nome='Alexander'), (SELECT id FROM projeto WHERE nome='pj3'), 120);
	
INSERT INTO funcionario_trabalha_departamento
	(id_funcionario, id_departamento) VALUES
	((SELECT id FROM funcionario WHERE nome='Judah'), (SELECT id FROM departamento WHERE nome='financas')),
	((SELECT id FROM funcionario WHERE nome='Rachel'), (SELECT id FROM departamento WHERE nome='pesquisa')),
	((SELECT id FROM funcionario WHERE nome='Cain'), (SELECT id FROM departamento WHERE nome='financas')),
	((SELECT id FROM funcionario WHERE nome='Yen'), (SELECT id FROM departamento WHERE nome='pesquisa')),
	((SELECT id FROM funcionario WHERE nome='Eric'), (SELECT id FROM departamento WHERE nome='gerencia')),
	((SELECT id FROM funcionario WHERE nome='Kasper'), (SELECT id FROM departamento WHERE nome='financas')),
	((SELECT id FROM funcionario WHERE nome='Caldwell'), (SELECT id FROM departamento WHERE nome='pesquisa')),
	((SELECT id FROM funcionario WHERE nome='Drake'), (SELECT id FROM departamento WHERE nome='gerencia')),
	((SELECT id FROM funcionario WHERE nome='Alexander'), (SELECT id FROM departamento WHERE nome='financas'));
	
INSERT INTO funcionario_gerencia_departamento 
	(id_funcionario,id_departamento,data_inicial) VALUES 
	((SELECT id FROM funcionario WHERE nome='Judah'), (SELECT id FROM departamento WHERE nome='financas'),   '10/01/2016'),
	((SELECT id FROM funcionario WHERE nome='Rachel'), (SELECT id FROM departamento WHERE nome='pesquisa'), '06/04/2015'),
	((SELECT id FROM funcionario WHERE nome='Drake'), (SELECT id FROM departamento WHERE nome='gerencia'), '08/08/2014');
	
INSERT INTO supervisor
	(id_supervisor, id_supervisionado) VALUES 
	((SELECT id FROM funcionario WHERE nome='Judah'), (SELECT id FROM funcionario WHERE nome='Rachel')),
	((SELECT id FROM funcionario WHERE nome='Judah'), (SELECT id FROM funcionario WHERE nome='Cain')),
	((SELECT id FROM funcionario WHERE nome='Yen'), (SELECT id FROM funcionario WHERE nome='Eric')),
	((SELECT id FROM funcionario WHERE nome='Drake'), (SELECT id FROM funcionario WHERE nome='Alexander')),
	((SELECT id FROM funcionario WHERE nome='Kasper'), (SELECT id FROM funcionario WHERE nome='Caldwell'));

querys

--Quantidade de funcionarios
SELECT count(*) FROM funcionario

--Liste de forma ordenada os nomes dos funcionários do sexo feminino que gerenciam departamentos.
SELECT * 
	FROM funcionario_gerencia_departamento INNER JOIN funcionario ON (funcionario.id = funcionario_gerencia_departamento.id_funcionario)
	WHERE funcionario.genero = 'f'
	ORDER BY funcionario.nome
	
--Liste os nomes dos dependentes e os nomes dos respectivos funcionários associados.
SELECT funcionario.nome AS nome_funcionario, dependente.nome AS nome_dependente  
	FROM dependente
		INNER JOIN dependente_funcionario ON (dependente.id = dependente_funcionario.id_dependente)
		INNER JOIN funcionario ON (dependente_funcionario.id_funcionario = funcionario.id)
--Liste o nome de cada funcionário e o número de dependentes que este possui.
SELECT funcionario.nome AS nome_funcionario, count(dependente.nome) AS quantidade_dependentes
	FROM dependente
		INNER JOIN dependente_funcionario ON (dependente.id = dependente_funcionario.id_dependente)
		INNER JOIN funcionario ON (dependente_funcionario.id_funcionario = funcionario.id)
GROUP BY funcionario.nome

--Liste o nome de cada funcionário e o número de projetos em que ele trabalha que são do mesmo departamento a que ele está alocado.
SELECT funcionario.nome, COUNT(projeto.id_departamento)
FROM funcionario 
	INNER JOIN funcionario_trabalha_departamento ON (funcionario.id = funcionario_trabalha_departamento.id_funcionario)
	INNER JOIN funcionario_alocado_projeto ON (funcionario.id = funcionario_alocado_projeto.id_funcionario)
	INNER JOIN projeto ON (funcionario_alocado_projeto.id_projeto = projeto.id)
WHERE funcionario_trabalha_departamento.id_departamento = projeto.id_departamento
GROUP BY funcionario.nome

--Liste o nome de cada departamento, a soma do salário de seus funcionários e o número de funcionários do departamento
SELECT departamento.nome, sum(funcionario.salario), count(funcionario.id)
FROM departamento
	INNER JOIN funcionario_trabalha_departamento ON (departamento.id = funcionario_trabalha_departamento.id_departamento)
	INNER JOIN funcionario ON (funcionario_trabalha_departamento.id_funcionario = funcionario.id)
GROUP BY departamento.nome


--Liste a soma dos salários e o número de funcionários do sexo masculino que trabalham em projetos do mesmo departamento a que pertence.
SELECT count(funcionario.id), sum(funcionario.salario)
FROM funcionario 
	INNER JOIN funcionario_trabalha_departamento ON (funcionario.id = funcionario_trabalha_departamento.id_funcionario)
	INNER JOIN funcionario_alocado_projeto ON (funcionario.id = funcionario_alocado_projeto.id_funcionario)
	INNER JOIN projeto ON (funcionario_alocado_projeto.id_projeto = projeto.id)
WHERE funcionario_trabalha_departamento.id_departamento = projeto.id_departamento 
	AND funcionario.genero = 'm'