Help With Triggers And Funcions In Sql

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.
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories