I have created the following tables and the following inserts:
create table employee
(
id_user number primary key,
hire_date date,
salary float,
bonus float
)
create table personal
(
id_user number primary key,
fname char(30),
lname char(30),
birth_date date
)
create table prod_per_year
(
id_user number primary key,
profit float,
years int
)
create table total_product
(
id_user number primary key,
total_profit float
)
create table status
(
id_user number primary key,
family_status char(40),
children int,
disability_rate float
)
insert into employee values (001,'12-03-2006',2000,350)
insert into employee values (002,'21-03-2006',1700,200)
insert into employee values (003,'22-02-2007',2400,650)
insert into employee values (004,'10-08-2008',2100,450)
insert into employee values (005,'12-03-2007',1900,400)
insert into personal values (001,'Dimitris','Ksaplopoulos','05-04-1982')
insert into personal values (002,'Giannis','Koimisis','20-02-1984')
insert into personal values (003,'Iasonas','Tempelhanidis','23-12-1986')
insert into personal values (004,'Triantafyllos','Kourasmenakis','14-08-1979')
insert into personal values (005,'Manolis','Aplas','02-05-1984')
insert into prod_per_year values (001,450,4)
insert into prod_per_year values (002,300,4)
insert into prod_per_year values (003,500,4)
insert into prod_per_year values (004,730,4)
insert into prod_per_year values (005,550,4)
insert into status values(001, 'normal',2,0)
insert into status values(002, 'normal',2,0)
insert into status values(003, 'normal',3,0)
insert into status values(004,'divorced',2,0)
insert into status values(005, 'normal',1,10)
insert into total_product values(001,3500)
insert into total_product values(002,2500)
insert into total_product values(003,3700)
insert into total_product values(004,4100)
insert into total_product values(005,3000)
i have some trouble of doing the 1. 2. 3. 4. if anyone can help i would appreciate it
1. we have to create a funcion in which if in the table employee the salary of an employee is less than 500 add 200 more.
2. Increase by 50 bonus for employees who have salary <= 1400 but the last year brought profit> = 1000 (using functions)
3. Bonus reduction at 150 for employees who the last 4 years brought profit of less than 3000 per year (using functions)
4. During data entry on the list on the table prod_per_year for each employee make sure to refresh the table total_product automatically using trigger (trigger based).
thanks in advance for the help.