x9R7BD2n

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 auto_increment,
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 (id, nume, salar_neg, data_angajarii, data_concedierii, nr_copii) values
15(1, 'cineva', 234.45, '2010-10-14', null, 2),
16(2, 'altcineva', 547.56, '2019-10-19', null, 1),
17(3, 'Popescu Alin', 678.45, '2015-10-14', null, 0),
18(4, 'Oprea Valentin', 567.56, '2019-07-19', null, 4),
19(5, 'Ber Cineva', 987.45, '2010-09-14', null, 1),
20(6, 'Senila Constantin', 897.56, '2019-04-19', null, 2),
21(7, 'Stanciu Ioan', 453.45, '2010-04-14', null, 3),
22(8, 'Ardelean Radu', 137.56, '2019-11-26', null, 3);
23
24 create table if not exists salarii(
25 id integer  not null primary key, 
26 nr_zile int not null,
27 brut decimal(5,2), 
28 deducere decimal(5,2), 
29 deducere_copii decimal(5,2), 
30 impozit decimal(5,2), 
31 net decimal(5,2));
32 insert into salarii(id,nr_zile) values
33 (1,23),
34 (2,34);
35 
36 create table if not exists deduceri(
37 brut_min decimal(5,2), 
38 brut_max decimal(5,2), 
39 deducere decimal(5,2));
40 
41 create table if not exists deduceri_copii(
42 nr_copii int,
43 deducere_copii decimal(5,2));
44 
45 alter table salarii
46add constraint fk_salarii_angajati foreign key(id) references angajati(id);
47
48delimiter //
49create procedure insert_deducere(brut_min decimal(5,2), brut_max decimal(5,2), deducere decimal(5,2))
50begin
51insert into deduceri(brut_min, brut_max, deducere) values (brut_min, brut_max, deducere);
52-- if brut_min>=0 and brut_max<=300 then
53--	insert into deduceri.deducere values (100);
54-- elseif brut_min>300 and brut_max<=600 then
55--		insert into deducere values (200);
56-- elseif brut_min>600 and brut_max<=800 then
57--		insert into deducere values (300);
58-- elseif brut_min>800 and brut_max<=999.99 then
59--		insert into deducere values (400);
60--    end if;
61end;//
62
63delimiter //
64create procedure insert_deducere_copii(nr_copii int) 
65begin
66if nr_copii=1 then
67	insert into deduceri_copii(nr_copii, deducere_copii) values (nr_copii, 100);
68elseif nr_copii=2 then
69	insert into deduceri_copii(nr_copii, deducere_copii) values (nr_copii, 170);
70elseif nr_copii=3 then
71	insert into deduceri_copii(nr_copii, deducere_copii) values (nr_copii, 220);
72elseif nr_copii=4 then
73	insert into deduceri_copii(nr_copii, deducere_copii) values (nr_copii, 260);
74end if;
75end;//
76
77call tema34.insert_deducere(0, 300, 100);
78call tema34.insert_deducere(300, 600, 200);
79call tema34.insert_deducere(600, 800, 300);
80call tema34.insert_deducere(800, 999.99, 400);
81
82call tema34.insert_deducere_copii(1);
83call tema34.insert_deducere_copii(2);
84call tema34.insert_deducere_copii(3);
85call tema34.insert_deducere_copii(4);
86
87