Last active
March 3, 2020 22:30
-
-
Save ivanantunes/c0c0eec4aa1ff85fbd99a0574b708f4f to your computer and use it in GitHub Desktop.
Revisions
-
ivanantunes revised this gist
Mar 3, 2020 . 1 changed file with 32 additions and 29 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -1,12 +1,12 @@ 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, @@ -16,11 +16,11 @@ CREATE TABLE funcionarios ( 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, @@ -30,50 +30,51 @@ CREATE TABLE dependentes ( 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 @@ -84,27 +85,29 @@ 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); -
Mar 2, 2020 . 1 changed file with 14 additions and 11 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -32,6 +32,7 @@ CREATE TABLE dependentes ( CONSTRAINT FK_DEP_FUN_ID FOREIGN KEY(DEP_FUN_ID) REFERENCES funcionarios(FUN_ID) ) ENGINE = INNODB; -- Cargo INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES ('1', 'DESENVOLVEDOR', '50000', '2020-02-27', NULL); @@ -42,23 +43,25 @@ INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUP -- 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', '2001-02-12', '2020-02-01', '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', '2001-02-13', '2020-02-02', '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', '2001-02-14', '2020-02-03', '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', '2001-02-15', '2020-02-04', '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', '2001-02-15', '2020-02-04', 'SOLTEIRO','1', '10000'); -- Dependentes INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('1', '1', 'Maria', '2019-12-31', '1'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('2', '2', 'Julia', '2019-12-31', '0'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('3', '3', 'Ana', '2019-12-24', '2'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('4', '4', 'Gabriel', '2019-12-25', '0'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('5', '5', 'Lucia', '2019-12-26', '1'); -- Questao 4 @@ -104,4 +107,4 @@ 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); -
Mar 2, 2020 . 1 changed file with 31 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -73,4 +73,35 @@ 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 DEP.DEP_DTNascto = 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 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); -
Feb 28, 2020 . 1 changed file with 8 additions and 0 deletions.There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -66,3 +66,11 @@ SELECT * FROM (SELECT fun.FUN_ID, fun.FUN_NOME, dep.DEP_NOME, car.CAR_ID from fu INNER join dependentes dep on fun.FUN_ID = dep.DEP_FUN_ID INNER JOIN cargo car on car.CAR_ID = fun.FUN_CAR_ID) as d WHERE d.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; -
Feb 28, 2020 .There are no files selected for viewing
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters. Learn more about bidirectional Unicode charactersOriginal file line number Diff line number Diff line change @@ -0,0 +1,68 @@ CREATE TABLE cargo ( CAR_ID int not null, CAR_NOME varchar(100), CAR_SALARIO_MAX decimal(12,2), 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) ) ENGINE = INNODB; 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 bit(1), FUN_SALARIO decimal(10,2), PRIMARY KEY(FUN_ID), CONSTRAINT FK_FUN_CAR_ID FOREIGN KEY(FUN_CAR_ID) REFERENCES cargo(CAR_ID) ) ENGINE = INNODB; 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) ) ENGINE = INNODB; -- Cargo INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES ('1', 'DESENVOLVEDOR', '50000', '2020-02-27', NULL); INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES ('2', 'SUPORTE', '10000', '2020-02-26', '1'); INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES ('3', 'RH', '7000', '2020-02-13', NULL); INSERT INTO cargo (CAR_ID,CAR_NOME,CAR_SALARIO_MAX,CAR_DATA_INICIO,CAR_CARGO_SUPERIOR) VALUES ('4', 'FINANCAS', '15000', '2020-02-11', '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', '2001-02-12', '2020-02-01', 'SOLTEIRO', b'0', '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', '2001-02-13', '2020-02-02', 'SOLTEIRO', b'0', '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', '2001-02-14', '2020-02-03', 'SOLTEIRO', b'0', '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', '2001-02-15', '2020-02-04', 'SOLTEIRO', b'0', '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', '2001-02-15', '2020-02-04', 'SOLTEIRO', b'0', '10000'); -- Dependentes INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('1', '1', 'DESENVOLVIMENTO - 1', '2019-12-31', '1'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('2', '2', 'SUPORTE - 1', '2019-12-31', '2'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('3', '3', 'SUPORTE - 2', '2019-12-24', '3'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('4', '4', 'RH - 1', '2019-12-25', '4'); INSERT INTO dependentes (DEP_ID,DEP_FUN_ID,DEP_NOME,DEP_DTNascto,DEP_RELACAO) VALUES ('5', '5', 'FATURAMENTO - 1', '2019-12-26', '5'); -- 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) as d WHERE d.CAR_ID = 2;