CREATE DATABASE empresa;

USE empresa;

CREATE TABLE Departamentos
(
	codigo INT NOT NULL PRIMARY KEY,
    nome VARCHAR(60) NOT NULL,
    localizacao VARCHAR(80) NOT NULL
);

INSERT INTO 
	Departamentos(codigo, nome, localizacao) 
VALUES 
	(456, "Tecnologia da Informação", "quadra A-31"), 
    (231, "Financeiro", "quadra A-25"), 
    (556, "Marketing", "quadra A-12"),
    (211, "Jurídico", "quadra A-13"), 
    (120, "Comercial", "quadra B-01"), 
    (466, "Recursos Humanos", "quadra C-14"), 
    (560, "Presidência", "quadra C-11");
    
    
CREATE TABLE Funcionarios
(
	cpf VARCHAR(11) NOT NULL PRIMARY KEY,
    nome VARCHAR(60) NOT NULL,
    endereco VARCHAR(80) NOT NULL,
    telefone VARCHAR(12) NOT NULL,
    salario DECIMAL(10,2) NOT NULL,
    codigo_departamento INT NOT NULL,
    codigo_departamentoGerenciar INT,
    FOREIGN KEY (codigo_departamento) REFERENCES Departamentos (codigo),
    FOREIGN KEY (codigo_departamentoGerenciar) REFERENCES Departamentos (codigo)
);

INSERT INTO 
	Funcionarios (CPF, nome, endereco, telefone, salario, codigo_departamento, codigo_departamentoGerenciar) 
VALUES 
	("10022256232", "Olívia de Paula Brito", "São Paulo", "314430089", 1200.00, 556, NULL),
    ("90856251432", "Fernando Marciel", "Santo Andre", "318907654", 2500.00, 456, NULL), 
    ("3985699025", "Adilson Fernandes Souza", "São Paulo", "314220065", 4500.00, 231, 231), 
    ("29845651111", "Vanessa Brito", "Sorocaba", "312678904", 950.00, 466, NULL), 
    ("10546222142", "Marcos Antonio da Silva", "Jundiai", "319786874", 980.00, 456, NULL);
    

CREATE TABLE Dependentes
(
	codigo INT NOT NULL PRIMARY KEY,
    nome VARCHAR(60) NOT NULL,
    endereco VARCHAR(80),
    parentesco VARCHAR(10) NOT NULL
);

INSERT INTO 
	Dependentes(codigo, nome, parentesco) 
VALUES
	(3768, "Leonardo Silva Marciel", "filho"), 
    (3776, "Mariana Lemos de Souza", "filha"), 
    (2763, "José Francisco Brito", "pai"),
    (3322, "André Silva Marciel", "filho"), 
    (7316, "Leandro Silva Marciel", "filho");
    

CREATE TABLE Projetos
(
	numero INT NOT NULL PRIMARY KEY,
    nome VARCHAR(40) NOT NULL,
    verba VARCHAR(60) NOT NULL,
    codigo_departamento INT NOT NULL,
    cpf_funcionario VARCHAR(11),
	tipo VARCHAR(60) NOT NULL,
    FOREIGN KEY (codigo_departamento) REFERENCES Departamentos (codigo),
	FOREIGN KEY (cpf_funcionario) REFERENCES Funcionarios (cpf)
);

INSERT INTO 
	projetos(numero, nome, tipo, verba, codigo_departamento) 
VALUES
	(56, "Alfa", "integral-2 anos", "5 bolsas integrais IF", 556), 
    (12, "D3RE", "integral-1 anos", "5 bolsas integrais IF",211 ), 
    (46, "X3", "parcial-2 anos", "não determinada", 456),
    (47, "Beta", "parcial-2 anos", "5 bolsas parciais IF", 556), 
    (21, "XR3", "integral-5 anos", "2 bolsas integrais IF", 456);
    
    
CREATE TABLE Funcionarios_Dependentes
(
	cpf_funcionario VARCHAR(11) NOT NULL,
    codigo_dependente INT NOT NULL,
    PRIMARY KEY (cpf_funcionario, codigo_dependente),
    FOREIGN KEY (cpf_funcionario) REFERENCES Funcionarios (cpf),
    FOREIGN KEY (codigo_dependente) REFERENCES Dependentes (codigo)
);

INSERT INTO 
	Funcionarios_Dependentes(cpf_funcionario, codigo_dependente) 
VALUES
	("90856251432", 3322), 
    ("90856251432", 7316),
    ("3985699025", 2763), 
    ("29845651111", 2763), 
    ("10546222142", 3776), 
    ("10546222142", 3768);
    
    
CREATE TABLE Funcionarios_Projetos
(
	cpf_funcionario VARCHAR(11) NOT NULL,
    numero_projeto INT NOT NULL,
    numero_horas INT NOT NULL,
	PRIMARY KEY (cpf_funcionario, numero_projeto),
    FOREIGN KEY (cpf_funcionario) REFERENCES Funcionarios (cpf),
    FOREIGN KEY (numero_projeto) REFERENCES Projetos (numero)
);

INSERT INTO 
	Funcionarios_Projetos(cpf_funcionario, numero_projeto, numero_horas) 
VALUES
	("90856251432", 12, 230), 
    ("90856251432", 56, 300),
    ("3985699025", 56, 120), 
    ("29845651111", 46, 248), 
    ("10546222142", 21, 30), 
    ("10546222142", 47, 20);
    
UPDATE Dependentes SET endereco = "São Paulo";

UPDATE Dependentes SET endereco = "Jundiaí" WHERE codigo = 3768 OR codigo = 3776;

UPDATE Dependentes SET endereco = "Sorocaba" WHERE nome = "José Francisco Brito";

DELETE FROM departamentos WHERE codigo = 120;



# Visão 1: 

# Nesse sistema, vemos que os projetos possuem verbas controladas pelos departamentos, e é comum serem 
# solicitadas informações a esse respeito. Utilizando este recurso, podemos criar a visão que contém 
# essas importantes informa- ções, para que não seja preciso pesquisar a base.

# Visão ‘departamentos_projetos’: 
#	selecione o tipo e a verba dos projetos que os departamentos controlam.

CREATE VIEW 
	departamentos_projetos 
AS 
	SELECT 
		d.codigo, d.nome, p.numero, p.tipo, p.verba 
	FROM 
		departamentos d JOIN projetos p 
	ON 
		d.codigo = p.codigo_departamento;
        
        
SELECT * FROM departamentos_projetos;


# Visão 2:

# Visão ‘funcionarios_projetos_horas’: 
#	selecione o número de horas que os funcionários trabalham em projeto.

CREATE VIEW 
	funcionarios_projetos_horas 
AS 
	SELECT 
		f.cpf, f.nome, f.salario, p.numero, p.tipo, fp.numero_horas 
	FROM 
		funcionarios f JOIN funcionarios_projetos fp JOIN projetos p 
	ON 
		cpf = fp.cpf_funcionario AND p.numero = fp.numero_projeto;
        
SELECT 
	cpf, nome AS Funcionario, salario, numero AS Projeto, tipo, numero_horas 
FROM 
	funcionarios_projetos_horas;


# Visão 3:

# Agora, vamos criar uma visão que contenha informações sobre os funcionários e seus dependentes, 
# acompanhando o código e as informações que seguem.

# Visão ‘funcionarios_dependentes1’: 
#	seleciona os dependentes dos funcionários.

CREATE VIEW 
	funcionarios_dependentes1 
AS 
	SELECT 
		f.CPF, f.nome, f.salario, d.codigo, d.parentesco 
	FROM 
		funcionarios f JOIN dependentes d JOIN funcionarios_dependentes fd 
	ON 
		f.cpf = fd.cpf_funcionario AND d.codigo = fd.codigo_dependente;

SELECT * FROM funcionarios_dependentes1;


# Se quisermos, por exemplo, excluir a visão ‘funcionarios_dependentes1’, devemos executar o seguinte código.
 
DROP VIEW funcionarios_dependentes1;