[GABARITO] view_projetoEmpresa

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;