-- Exercicio PMR 2490/PMR3304

-- modelo conceitual do BD

use test;

CREATE TABLE dept
	(  id INT  NOT NULL PRIMARY KEY,
	   nome VARCHAR(25),
	   region INT
	);

CREATE TABLE s_emp
	(  id INT  NOT NULL PRIMARY KEY,
	   nome  VARCHAR(25),
       	   dept INT NOT NULL,
       		 FOREIGN KEY (dept) REFERENCES dept(id),
	   salary DECIMAL(8,2)
	);

-- populando a tabela “dept com dados
-- no caso da tabela “dept” colocamos valores em todos os campos e portanto
-- não é preciso usar o comando SET no querry de inserção

INSERT into dept
VALUES ( 1, "compras", 1);

INSERT into dept
VALUES ( 2, "compras", 2);

INSERT into dept
VALUES ( 3, "compras", 5);

INSERT into dept
VALUES ( 4, "pessoal", 2);

INSERT into dept
VALUES ( 5, "compras_a_pagar", 1);

INSERT into dept
VALUES ( 6, "marketing", 2);

INSERT into  dept
VALUES ( 7, "inovacao_tecnologica", 3);

INSERT into dept
VALUES ( 8, "inovacao_tecnologica", 1);


INSERT into dept
VALUES ( 9, "meio_ambiente", 1);

INSERT into dept
VALUES ( 10, "pesquisa", 2);

INSERT into dept
VALUES ( 11, "pesquisa", 1);

INSERT into  dept
VALUES ( 12, "pesquisa", 3);


-- populando a tabela de empregados

INSERT into s_emp
VALUES ( 92, "Totonho Castrado", 1, 1027.30);

INSERT into s_emp
VALUES ( 19, "Coriolando Varicela", 1, 623.19);

INSERT into s_emp
VALUES ( 35, "Melquizedeque Miro", 1, 877.25);

INSERT into s_emp
VALUES ( 41, "Astrogildo Campanario", 2, 921.50);

INSERT into s_emp
VALUES ( 22, "Bolipedes Gonzaga", 2, 877.25);

INSERT into s_emp
VALUES ( 21, "Artebaldo Gonzaga", 3, 602.44);

INSERT into s_emp
VALUES ( 25, "Marcolino Entevaldo", 3, 577.89);

INSERT into s_emp
VALUES ( 68, "Mia Gataz", 4, 1023.24);

INSERT into s_emp
VALUES ( 94, "Felicia Alegre", 4, 767.22);

INSERT into s_emp
VALUES ( 72, "Borigenes Antonio", 4, 987.43);

INSERT into s_emp
VALUES ( 32, "Ze das Couves", 5, 700.00);

INSERT into s_emp
VALUES ( 77, "Shun Khun Pa", 5, 923.14);

INSERT into s_emp
VALUES ( 52, "Carlos Cassapa", 8, 857.21);

INSERT into s_emp
VALUES ( 82, "Coriolando Antunes", 12, 1870.45);

INSERT into s_emp
VALUES ( 86, "Pintacilgo Soares", 12, 459.22);

INSERT into s_emp
VALUES ( 15, "Corina Carrappato", 9, 1204.39);

INSERT into s_emp
VALUES ( 44, "Antussa Rodrigues", 9, 1021.14);

INSERT into s_emp
VALUES ( 55, "Casimiro Morcego", 11, 977.25);

INSERT into s_emp
VALUES ( 79, "Simone Carioca", 10, 1920.54);

INSERT into s_emp
VALUES ( 84, "Assunta Tavares", 7, 449.88);

INSERT into s_emp
VALUES ( 100, "Trajano Bombacha", 6, 744.56);

INSERT into s_emp
VALUES ( 104, "Silvio Quintana", 6, 2034.22);

INSERT into s_emp
VALUES ( 120, "Policarpo Quaresma", 8, 2245.74);

INSERT into s_emp
VALUES ( 122, "Domitila Zuada", 11, 576.24);

INSERT into s_emp
VALUES ( 212, "Caravelio Remada", 10, 2029.44);


INSERT into s_emp
VALUES ( 220, "Shigero Ossada", 10, 1279.56);

INSERT into s_emp
VALUES ( 252, "Amadeo Capeta", 12, 2820.19);

INSERT into s_emp
VALUES ( 227, "Beluza Garcia", 8, 987.34);