p5t2hpyF

1drop database if exists Tema34;
2
3CREATE DATABASE IF NOT EXISTS Tema34;
4use Tema34;
5
6Create table if not exists angajati(
7id  INTEGER primary key,
8nume VARCHAR(50) NOT NULL, 
9salar_neg decimal(5,2), 
10data_angajarii DATE,
11data_concedierii DATE, 
12nr_copii INT NOT NULL);
13
14insert into angajati values(1, 'Constantin', 150.00, '2016-12-27', '2020-08-29', 3);
15insert into angajati values(2, 'Andrei',205.00, '2019-11-18', null, 4);
16insert into angajati values(3, 'Cozonac Andrei', 131.00, '2019-03-18', null, 1);
17insert into angajati values(4, 'Cozma', 200.00, '2012-02-19', '2019-11-11', 3);
18insert into angajati values(5, 'Senila', 170.00, '2013-05-16', '2019-07-29', 2);
19insert into angajati values(6, 'Bordea', 180.00, '2014-09-20', null, 4);
20insert into angajati values(7, 'Daniel', 230.00, '2010-05-17', null, 1);
21
22 create table if not exists salarii(
23 id integer primary key, 
24 nr_zile int(3) not null,
25 brut decimal(5,2), 
26 deducere decimal(5,2), 
27 deducere_copii decimal(5,2), 
28 impozit decimal(5,2), 
29 net decimal(5,2),
30 constraint fk_id foreign key (id)references angajati (id));
31 
32 create table if not exists deduceri(
33 brut_min decimal(5,2), 
34 brut_max decimal(5,2), 
35 deducere decimal(5,2));
36 
37 insert into deduceri values(0.00,300.00,100.00);
38insert into deduceri values(300.01,600.00,200.00);
39insert into deduceri values(600.01,800.00,300.00);
40insert into deduceri values(800.01,999.99,400.00);
41
42 create table if not exists deduceri_copii(
43 nr_copii int,
44 deducere_copii decimal(5,2));
45 
46insert into deduceri_copii values(1,100);
47insert into deduceri_copii values(2,170);
48insert into deduceri_copii values(3,220);
49insert into deduceri_copii values(4,260);
50 
51
52DELIMITER //
53create trigger update_salarii before insert on salarii
54for each row begin
55	set new.brut = new.nr_zile* (select salar_neg from angajati where angajati.id = new.id);
56    set new.deducere=(select d.deducere from deduceri d 
57												join angajati a 
58                                                where d.brut_min<=new.brut and d.brut_max>=new.brut and a.id=new.id);
59    set new.deducere_copii=(select deduceri_copii.deducere_copii from deduceri_copii
60																join angajati
61																where angajati.nr_copii=deduceri_copii.nr_copii and angajati.id=new.id);
62    set new.impozit=(new.brut-new.deducere-new.deducere_copii)*0.16;
63    if new.impozit>0 then
64		set new.net=new.brut-new.impozit;
65	else
66		set new.net=new.brut;
67	end if;
68end //
69DELIMITER ;
70
71insert into salarii values (1,2,null,null,null,null,null);
72insert into salarii values (2,2,null,null,null,null,null);
73insert into salarii values (3,1,null,null,null,null,null);
74insert into salarii values (4,4,null,null,null,null,null);
75insert into salarii values (5,2,null,null,null,null,null);
76insert into salarii values (6,3,null,null,null,null,null);
77insert into salarii values (7,1,null,null,null,null,null);
78
79-- 3
80
81
82