CREATE TABLE cargo (
	CAR_ID int not null,
	CAR_NOME varchar(100),
	CAR_SALARIO_MAX number,
	CAR_DATA_INICIO DATE,
	CAR_CARGO_SUPERIOR int,
	PRIMARY KEY(CAR_ID),
	CONSTRAINT FK_CAR_SUPERIOR FOREIGN KEY(CAR_CARGO_SUPERIOR) REFERENCES cargo(CAR_ID)
);

CREATE TABLE funcionarios (
	FUN_ID int not null,
	FUN_CAR_ID int not null,
	FUN_NOME varchar(100),
	FUN_PROFISSAO varchar(30),
	FUN_DTNASCTO DATE,
	FUN_DTContrato DATE,
	FUN_ESTADO_CIVIL varchar(10),
	FUN_POSSUI_DEPEND number,
	FUN_SALARIO number,
	PRIMARY KEY(FUN_ID),
	CONSTRAINT FK_FUN_CAR_ID FOREIGN KEY(FUN_CAR_ID) REFERENCES cargo(CAR_ID)
);

CREATE TABLE dependentes (
	DEP_ID int not null,
	DEP_FUN_ID int not null,
	DEP_NOME varchar(100),
	DEP_DTNascto date,
	DEP_RELACAO int,
	PRIMARY KEY(DEP_ID),
	CONSTRAINT FK_DEP_FUN_ID FOREIGN KEY(DEP_FUN_ID) REFERENCES funcionarios(FUN_ID)
);


-- Cargo

INSERT INTO CARGO (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES (1, 'DESENVOLVEDOR', 50000, '27/03/2020', NULL);
INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES (2, 'SUPORTE', 10000, '26/02/2020', 1);
INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES (3, 'RH', 7000, '13/02/2020', NULL);
INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES (4, 'FINANCAS', 15000, '11/02/2020',3);

-- Funcionarios

INSERT INTO funcionarios (FUN_ID,FUN_CAR_ID,FUN_NOME,FUN_PROFISSAO,FUN_DTNASCTO,FUN_DTContrato,FUN_ESTADO_CIVIL,FUN_POSSUI_DEPEND,FUN_SALARIO) 
VALUES ('1', '1', 'IVAN', 'Analista de Sistemas', '12/02/2001', '01/02/2020', 'SOLTEIRO', '1', '15000');
INSERT INTO funcionarios (FUN_ID,FUN_CAR_ID,FUN_NOME,FUN_PROFISSAO,FUN_DTNASCTO,FUN_DTContrato,FUN_ESTADO_CIVIL,FUN_POSSUI_DEPEND,FUN_SALARIO) 
VALUES ('2', '2', 'GABRIEL', 'SUPORTE EXTERNO', '13/02/2001', '02/02/2020', 'SOLTEIRO', '1', '5000');
INSERT INTO funcionarios (FUN_ID,FUN_CAR_ID,FUN_NOME,FUN_PROFISSAO,FUN_DTNASCTO,FUN_DTContrato,FUN_ESTADO_CIVIL,FUN_POSSUI_DEPEND,FUN_SALARIO) 
VALUES ('3', '2', 'ARTHUR', 'SUPORTE EXTERNO', '14/02/2001', '03/02/2020', 'SOLTEIRO', '1', '4000');
INSERT INTO funcionarios (FUN_ID,FUN_CAR_ID,FUN_NOME,FUN_PROFISSAO,FUN_DTNASCTO,FUN_DTContrato,FUN_ESTADO_CIVIL,FUN_POSSUI_DEPEND,FUN_SALARIO) 
VALUES ('4', '3', 'DOUGLAS', 'GERENTE', '15/02/2001', '04/02/2020', 'SOLTEIRO', '1', '7000');
INSERT INTO funcionarios (FUN_ID,FUN_CAR_ID,FUN_NOME,FUN_PROFISSAO,FUN_DTNASCTO,FUN_DTContrato,FUN_ESTADO_CIVIL,FUN_POSSUI_DEPEND,FUN_SALARIO) 
VALUES ('5', '4', 'KENIA', 'CONTABILIDADe', '16/02/2001', '05/02/2020', 'SOLTEIRO','1', '10000');


-- Dependentes

INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('1', '1', 'Maria', '31/12/2019', '1');
INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('2', '2', 'Julia', '31/12/2019', '0');
INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('3', '3', 'Ana', '24/12/2019', '2');
INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('4', '4', 'Gabriel', '25/12/2019', '0');
INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('5', '5', 'Lucia', '26/12/2019', '1');


-- Questao 4

SELECT * FROM (SELECT fun.FUN_ID, fun.FUN_NOME, dep.DEP_NOME, car.CAR_ID from funcionarios fun 
INNER join dependentes dep on fun.FUN_ID = dep.DEP_FUN_ID 
INNER JOIN cargo car on car.CAR_ID = fun.FUN_CAR_ID) WHERE CAR_ID = 2;


-- Questao 5

SELECT fun.FUN_NOME AS "NOME - DEP" from funcionarios fun
UNION
SELECT dep.DEP_NOME AS "NOME - DEP" from dependentes dep inner join funcionarios fun on
fun.FUN_ID = dep.DEP_FUN_ID;

-- Questao 6

SELECT fun.FUN_NOME AS NOME FROM funcionarios fun WHERE fun.FUN_NOME LIKE 'Ju%'
UNION ALL
SELECT dep.DEP_NOME AS NOME FROM dependentes dep WHERE dep.DEP_NOME LIKE 'Ju%';

-- Questao 7

SELECT * FROM dependentes dep WHERE (extract(day from dep.dep_dtnascto) = extract(day from sysdate))
and 
(extract(month from dep.dep_dtnascto) = extract(month from sysdate));

-- Questao 8

SELECT * FROM funcionarios fun WHERE FUN.FUN_ID in (SELECT dep.DEP_FUN_ID FROM dependentes dep WHERE dep.DEP_NOME LIKE 'Maria%');

-- Questao 9 

SELECT FUN.FUN_CAR_ID AS "CARGO ID", car.CAR_NOME AS "NOME DO CARGO", count(*) as "Numero de Funcionarios" FROM funcionarios fun 
INNER JOIN cargo car ON car.CAR_ID = fun.FUN_CAR_ID GROUP BY fun.FUN_CAR_ID, car.CAR_NOME order by 2 desc;

-- Questao 10

SELECT * FROM funcionarios fun ORDER BY fun.FUN_SALARIO desc;

-- Questao 11

SELECT fun.FUN_NOME FROM funcionarios fun WHERE fun.FUN_DTNASCTO LIKE '%/02/%'
UNION ALL
SELECT dep.DEP_NOME FROM dependentes dep WHERE dep.DEP_DTNascto LIKE '%/02/%';

-- Questao 12

SELECT fun.FUN_NOME AS "NOME" FROM funcionarios fun WHERE fun.FUN_SALARIO in (SELECT min(fun.FUN_SALARIO) FROM funcionarios fun);