Hmdn7TTu

1drop database if exists tema3;
2create database tema3;
3use tema3;
4 
5create table angajati(
6id  INTEGER not null primary key,
7nume VARCHAR(50) NOT NULL, 
8salar_neg decimal(5,2), 
9data_angajarii DATE,
10data_concedierii DATE, 
11nr_copii INT NOT NULL);
12 
13create table salarii(
14 id INTEGER not null primary key, 
15 nr_zile int not null,
16 brut decimal(10,2), 
17 deducere decimal(5,2), 
18 deducere_copii decimal(5,2), 
19 impozit decimal(5,2), 
20 net decimal(6,2));
21 
22  create table deduceri(
23ind int primary key,
24 brut_min decimal(5,2), 
25 brut_max decimal(5,2), 
26 deducere decimal(5,2));
27 
28 create table deduceri_copii(
29 nr_copii int,
30 deducere_copii decimal(5,2));
31 
32 insert into angajati (id, nume, salar_neg, data_angajarii, data_concedierii, nr_copii) values
33(1, 'Moldovan Liana', 134.45, '2010-10-14', null, 2),
34(2, 'Adelina Magdea', 247.56, '2019-10-19', '2020-10-10', 0),
35(3, 'Popescu Alin', 178.45, '2015-10-14', null, 1),
36(4, 'Oprea Valentin', 117.56, '2019-07-19', null, 4),
37(5, 'Iftimie Ella', 187.45, '2010-09-14', '2014-04-10', 1),
38(6, 'Senila Constantin', 297.56, '2019-04-19', null, 2),
39(7, 'Crisina Popescu', 353.45, '2010-04-14', null, 3),
40(8, 'Lupsan Elena', 237.56, '2019-11-26', null, 0);
41 
42insert into deduceri(ind,brut_min, brut_max, deducere) values
43(1,0, 300, 100),
44(2,300.01, 600, 200),
45(3,600.01, 800,300),
46(4,800.01, 999.99, 400);
47 
48insert into deduceri_copii(nr_copii, deducere_copii) values
49(0,0),
50(1, 100),
51(2, 170),
52(3, 220),
53(4,260);
54 
55 alter table salarii
56 add constraint fk_salarii_angajati foreign key(id) references angajati(id);
57 
58 delimiter //
59 create trigger modificare_zile 
60 before update on salarii
61 for each row
62 BEGIN
63 	if (old.nr_zile !=new.nr_zile)
64     then
65 set new.brut=(select salar_neg from angajati where new.id=angajati.id)*new.nr_zile;
66 set new.deducere=(select deducere from deduceri where new.brut between brut_min and brut_max);
67 set new.deducere_copii=(select deducere_copii from deduceri_copii join angajati on angajati.nr_copii=deduceri_copii.nr_copii where angajati.id=new.id);
68 set new.impozit=(new.brut-new.deducere-new.deducere_copii)*0.16;
69 
70 if new.impozit>0 then
71 set new.net=new.brut-new.impozit;
72 else
73 set new.net=new.brut;
74end if;
75end if;
76 end //
77 
78DELIMITER //
79create trigger update_salarii before insert on salarii
80for each row begin
81    set new.brut = new.nr_zile* (select salar_neg from angajati where angajati.id = new.id);
82    set new.deducere=(select d.deducere from deduceri d 
83                                                join angajati a 
84                                                where d.brut_min<=new.brut and d.brut_max>=new.brut and a.id=new.id);
85    set new.deducere_copii=(select deduceri_copii.deducere_copii from deduceri_copii
86                                                                join angajati
87                                                                where angajati.nr_copii=deduceri_copii.nr_copii and angajati.id=new.id);
88    set new.impozit=(new.brut-new.deducere-new.deducere_copii)*0.16;
89    if new.impozit>0 then
90        set new.net=new.brut-new.impozit;
91    else
92        set new.net=new.brut;
93    end if;
94end //
95DELIMITER ;
96 
97insert into salarii values (1,2,null,null,null,null,null);
98insert into salarii values (2,2,null,null,null,null,null);
99insert into salarii values (3,1,null,null,null,null,null);
100insert into salarii values (4,1,null,null,null,null,null);
101 
102  update salarii set nr_zile=2 where id=3;
103  update salarii set nr_zile=4 where id=1;
104  update salarii set nr_zile=4 where id=4;
105 
106 
107  delimiter //
108create trigger modificare_deducere after update on deduceri
109for each row begin
110    update salarii
111    set salarii.deducere=(select deducere from deduceri  where (salarii.brut>=brut_min and salarii.brut<=brut_max)),
112     salarii.impozit=(salarii.brut-new.deducere-salarii.deducere_copii)*0.16;
113	if impozit>0 then
114    update salarii
115    set salarii.net=salarii.brut-salarii.impozit;
116    else
117    update salarii
118    set salarii.net=salarii.brut;
119    end if;
120 
121 
122END //
123delimiter ;
124 drop trigger modificare_deducere; 
125 update deduceri set deducere=100 where brut_min=0.00;
126update deduceri set deducere=500 where brut_min=300.01;
127 
128 delimiter //
129create trigger modificare_deduceri_copii after update on deduceri_copii
130for each row begin
131	update salarii
132	set deducere_copii=(select deducere_copii from deduceri_copii  join angajati on angajati.nr_copii=deduceri_copii.nr_copii and angajati.id=salarii.id);
133 
134	update salarii
135	set impozit=(brut-deducere-deducere_copii)*0.16;
136	if impozit>0 then    
137	update salarii
138	set net=brut-impozit;
139	else  
140	update salarii
141	set net=brut;
142    end if;
143END //
144delimiter ;
145 
146-- drop trigger if exists modificare_deduceri_copii; 
147
148-- update deduceri_copii set deducere_copii=250 where nr_copii=1;
149-- update deduceri_copii set deducere_copii=400 where nr_copii=2;
150 
151delimiter //
152create trigger sterge_concediati
153after update on angajati for each row
154begin
155    delete from salarii where id=(Select angajati.id from angajati where angajati.data_concedierii=new.data_concedierii);
156end //
157delimiter ;
158-- drop trigger if exists sterge_concediati;
159