Skip to content

Instantly share code, notes, and snippets.

@ivanantunes

ivanantunes/funcionarios.sql

Last active March 3, 2020 22:30
Show Gist options
  • Save ivanantunes/c0c0eec4aa1ff85fbd99a0574b708f4f to your computer and use it in GitHub Desktop.
Save ivanantunes/c0c0eec4aa1ff85fbd99a0574b708f4f to your computer and use it in GitHub Desktop.
Download ZIP
Raw
funcionarios.sql
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);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment