---------------------------------------------
	-- Criar Tabelas e Relacionamentos
---------------------------------------------
create table tr_emp (
	idEmp 		integer,
	nome 		varchar(50) not null,
	maxSal 		decimal(9,2) not null check (maxSal > 0),
	dth_inc 	timestamp,
	usu_inc		varchar(20),
	dth_atu 	timestamp,
	usu_atu 	varchar(20),
	PRIMARY KEY (idEmp)
);

create table tr_sal_emp (
	idSal_emp 	integer,
	idEmp 		integer references tr_emp(idEmp) not null,
	dtIni 		date not null,
	dtFim 		date null check (dtFim > dtIni),
	sal 		decimal(9,2) not null,
	dth_inc 	timestamp,
	usu_inc 	varchar(20),
	dth_atu 	timestamp,
	usu_atu 	varchar(20),
	PRIMARY KEY (idSal_emp)
);

create table tr_promovido ( 
	anoMes 		integer,
	qtd 		integer check (qtd > 0),
	PRIMARY KEY (anoMes)
);

create table tr_emp_log (
	id	 		serial,
	operacao 	varchar(6), 
	usuario 	varchar(20),
	dataHora 	timestamp,
	maxSalAnt	decimal(9,2),
	maxSalDep 	decimal(9,2),
	PRIMARY KEY	(id)
);
---------------------------------------------
	-- Valores de Testes
---------------------------------------------
INSERT INTO tr_emp VALUES (1, 'Amanda', 1000, now(), 'Yonathan', now(), 'Yonathan');
INSERT INTO tr_emp VALUES (2, 'Carol', 2000, now(), 'Yonathan', now(), 'Yonathan');
INSERT INTO tr_emp VALUES (3, 'Julia', 3000, now(), 'Yonathan', now(), 'Yonathan');
INSERT INTO tr_emp VALUES (20, 'Vanessa', 3000, now(), 'Yonathan', now(), 'Yonathan');

INSERT INTO tr_sal_emp (idSal_emp, idEmp, dtIni, dtFim, sal) VALUES (1, 1, '2015-02-20', '2015-08-20', 850);
INSERT INTO tr_sal_emp (idSal_emp, idEmp, dtIni, dtFim, sal) VALUES (2, 2, '2015-02-20', '2015-08-20', 1500);
INSERT INTO tr_sal_emp (idSal_emp, idEmp, dtIni, dtFim, sal) VALUES (3, 3, '2009-02-20', '2009-08-20', 2500);
INSERT INTO tr_sal_emp (idSal_emp, idEmp, dtIni, dtFim, sal) VALUES (4, 20, '2011-10-20', '2013-08-20', 2500);

UPDATE tr_sal_emp set sal = 850 where idEmp = 1
UPDATE tr_sal_emp set sal = 1300 where idEmp = 2
UPDATE tr_sal_emp set sal = 2700 where idEmp = 3
UPDATE tr_sal_emp set sal = 2800 where idEmp = 20

update tr_promovido set qtd = qtd + 1 where 201511 = anoMes;
---------------------------------------------
	-- Queries fn_01
---------------------------------------------
select * from tr_emp
select * from tr_sal_emp
select * from tr_promovido
select * from tr_log_emp

drop trigger tg_01 on tr_sal_emp;
drop function fn_01();

drop table tr_emp, tr_sal_emp, tr_promovido, tr_log_emp
truncate table tr_emp, tr_sal_emp, tr_promovido, tr_log_emp
---------------------------------------------
	-- Queries fn_02_log
---------------------------------------------
select * from tr_emp
select * from tr_emp_log

INSERT INTO tr_emp VALUES (21, 'Vanessa', 3000, now(), 'Yonathan', now(), 'Yonathan');
UPDATE tr_emp set maxSal = 2500 where idEmp = 21;
DELETE FROM tr_emp where idEmp = 21; 