# # Tranzactii # ##Create table(s) CREATE TABLE IF NOT EXISTS departament (id int unique auto_increment primary key, nume char(20), manager_id int) ENGINE = 'innodb'; CREATE TABLE IF NOT EXISTS angajat (id int unique auto_increment primary key, nume char(20), prenume char(20), departament_id int, manager_id int , salariu int, bonus int, angajare date, vechime date, INDEX (departament_id), FOREIGN KEY (departament_id) REFERENCES departament(id), FOREIGN KEY(manager_id) REFERENCES angajat(id)) ENGINE = 'innodb'; INSERT INTO departament (nume, manager_id) VALUES ('R&D', 1), ('QA', 2), ('IT', 3), ('Backend', 4), ('HR', 5); INSERT INTO angajat (nume, prenume, departament_id, manager_id, salariu, bonus,angajare) VALUES ('Popa', 'Ion', 1, NULL, 8000, 0,'2000-1-12'), ('Popescu', 'Maria', 1, 1, 3000, 0, '2003-5-6'), ('Marinescu', 'Vasile', 1, 1, 5000, 0, '2004-6-3'), ('Ionescu', 'Andrei', 1, NULL, 3000, 0, '2002-1-1'), ('Vasilescu', 'Ana', 2, NULL, 2000, 0, '2006-3-3'), ('Dragan', 'Dinu', 2, 5, 2000, 0, '2004-11-12'), ('Mihailescu', 'Adrian', 5, NULL, 2500, 0, '2006-10-12'), ('Teodorescu', 'Matei', 3, NULL, 2000, 0, '2005-1-12'), ('Popescu', 'Vasile', 3, 8, 3000, 0, '2005-9-9'), ('Mateescu', 'Dumitru',3, 8, 3000, 0, '2007-2-5'), ('Calinescu', 'Alin', 4, NULL, 3200, 0, '2005-8-2'), ('Popescu', 'Mihaela',4, 12, 1500, 0, '2005-4-8'), ('Enachescu', 'Ionel', 5, NULL, 4500, 0, '2001-1-12'), ('Dediu', 'Carmen', 4, 12, 2700, 0, '2005-6-5'), ('Antonescu', 'Paul', 2, 5, 2700, 0, '2007-3-22'); # Mariti salariul tuturor angajatilor cu o zecime din media salariului START TRANSACTION; SELECT @AV_SAL:= 0.1 * AVG(salariu) FROM angajat; UPDATE angajat SET salariu = salariu + @AV_SAL; SELECT nume, salariu FROM angajat; COMMIT; # Mariti salariul tuturor angajatilor cu un procent din media salariului, # daca aceasta e mai mica decat pragul maxim in lei DROP PROCEDURE IF EXISTS MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR; DELIMITER // CREATE PROCEDURE MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR(procent FLOAT, prag_maxim FLOAT) BEGIN START TRANSACTION; SELECT @AV_SAL:= procent * AVG(salariu) FROM angajat; IF (@AV_SAL < prag_maxim) THEN BEGIN UPDATE angajat SET salariu = salariu + @AV_SAL; COMMIT; END; ELSE ROLLBACK; END IF; END // DELIMITER ; SELECT nume, prenume, salariu FROM angajat; CALL MARESTE_SALARIU_CU_PROCENT_DIN_MEDIA_SALARIILOR(0.1,500); SELECT nume, prenume, salariu FROM angajat; # Mariti salariul tuturor angajatilor cu o suma fixa, cu conditia ca maximul rezultat # sa fie mai mic decat o suma fixa DROP PROCEDURE IF EXISTS MARESTE_SALARIU_CU_SUMA_FIXA; DELIMITER // CREATE PROCEDURE MARESTE_SALARIU_CU_SUMA_FIXA(marire FLOAT, prag_maxim_rezultat FLOAT) BEGIN START TRANSACTION; UPDATE angajat SET salariu = salariu + marire; SELECT @MAX_SAL:= MAX(salariu) FROM angajat; IF (@MAX_SAL < prag_maxim_rezultat) THEN COMMIT; ELSE ROLLBACK; END IF; END // DELIMITER ; SELECT nume, prenume, salariu FROM angajat; CALL MARESTE_SALARIU_CU_SUMA_FIXA(400,9000); SELECT nume, prenume, salariu FROM angajat; # Micsoram toate salariile care trec de o suma fixa cu un procent dat DROP PROCEDURE IF EXISTS REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT; DELIMITER // CREATE PROCEDURE REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT(prag_maxim FLOAT, procent FLOAT) BEGIN START TRANSACTION; UPDATE angajat SET salariu = (salariu - procent * salariu) WHERE salariu > prag_maxim; COMMIT; END // DELIMITER ; # Micsoram toate salariile care trec de o suma fixa cu un procent dat # Pragul maxim : prag_maxim # Procent: procent #Conditii aditionale: # - reducerea totala sa fie peste parametrul reducere_totala # - media salariilor rezultate sa fie peste paramentrul medie_minima DROP PROCEDURE IF EXISTS REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT_2; DELIMITER // CREATE PROCEDURE REDUCE_SALARIU_PESTE_MAX_CU_PROCENT_DAT_2(prag_maxim FLOAT, procent FLOAT, reducere_totala FLOAT, medie_minima FLOAT) BEGIN select @SUM_SAL_INI:= SUM(salariu) from angajat; START TRANSACTION; UPDATE angajat SET salariu = (salariu - procent * salariu) WHERE salariu > prag_maxim; select @SUM_SAL_END:= SUM(salariu) from angajat; SELECT @medie_end:=AVG(salariu) from angajat; IF ((@SUM_SAL_INI - @SUM_SAL_END > reducere_totala) AND (@medie_end > medie_minima)) THEN COMMIT; ELSE ROLLBACK; END IF; END // DELIMITER ; # Micsorati salariul managerilor cu o suma fixa, daca nu au bonus si au salariu peste medie; # Aceasta regula insa se va aplica numai daca micsorarea este sub 20% din salariul mediu DROP PROCEDURE IF EXISTS MICSOREAZA_SALARIU_MANAGER; DELIMITER // CREATE PROCEDURE MICSOREAZA_SALARIU_MANAGER(micsorare FLOAT) BEGIN START TRANSACTION; SELECT @AVG_SAL:= AVG(salariu) FROM angajat; IF micsorare < 0.2 * @AVG_SAL THEN BEGIN UPDATE angajat SET salariu = salariu - micsorare WHERE salariu > @AVG_SAL AND manager_id IS NULL AND bonus = 0; COMMIT; END; ELSE ROLLBACK; END IF; END // DELIMITER ; SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; CALL MICSOREAZA_SALARIU_MANAGER(400); SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; # Calcul minim a doua valori DROP FUNCTION IF EXISTS MINVAL; DELIMITER // CREATE FUNCTION MINVAL(X FLOAT, Y FLOAT) RETURNS FLOAT BEGIN IF X <= Y THEN RETURN X; ELSE RETURN Y; END IF; END // DELIMITER ; # Acordati bonus toturor non-managerilor cu vechime de peste 2 ani, cu conditia # sa nu cresteti cheltuielile totale cu mai mult de 5000 lei; # bonusul va reprezenta minimul dintre (bonus_performanta) lei si 30% din salariu DROP PROCEDURE IF EXISTS BONUS_PERFORMANTA_ANGAJAT; DELIMITER // CREATE PROCEDURE BONUS_PERFORMANTA_ANGAJAT(bonus_performanta FLOAT) BEGIN START TRANSACTION; SELECT @CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat; UPDATE angajat SET bonus = MINVAL(bonus_performanta, 0.3* salariu) WHERE manager_id IS NOT NULL AND (YEAR(CURRENT_DATE()) - YEAR(angajare)) > 2; SELECT @NOI_CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat; IF (@NOI_CHELTUIELI_TOTALE - @CHELTUIELI_TOTALE) >= 5000 THEN ROLLBACK; ELSE COMMIT; END IF; END // DELIMITER ; SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; CALL BONUS_PERFORMANTA_ANGAJAT(800); SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; # Acordati bonus (parametru) acelor manageri care au cel putin # 2 subalterni cu vechime peste 1 an; # Aceasta regula insa se va aplica numai daca rezultatul va mari # cheltuielile totale cu mai putin de (parametrul 2) lei DROP PROCEDURE IF EXISTS BONUS_PERFORMANTA_MANAGER; DELIMITER // CREATE PROCEDURE BONUS_PERFORMANTA_MANAGER(bonus_performanta FLOAT, total_crestere_cheltuieli FLOAT) BEGIN START TRANSACTION; SELECT @CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat; CREATE TEMPORARY TABLE t1 SELECT A1.id FROM angajat AS A1 JOIN angajat as A2 on A1.id = A2.manager_id WHERE (YEAR(CURRENT_DATE()) - YEAR(A2.angajare)) > 1 GROUP by A2.manager_id HAVING count(*) >= 2; ############# select * from t1; ############# UPDATE angajat SET bonus = bonus + bonus_performanta WHERE id in (SELECT * from t1); DROP TABLE t1; SELECT @NOI_CHELTUIELI_TOTALE:= SUM(salariu)+SUM(bonus) FROM angajat; IF (@NOI_CHELTUIELI_TOTALE - @CHELTUIELI_TOTALE) >= total_crestere_cheltuieli THEN ROLLBACK; ELSE COMMIT; END IF; END // DELIMITER ; SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; CALL BONUS_PERFORMANTA_MANAGER(500, 1000); SELECT nume, prenume, ISNULL(manager_id) AS Manager, salariu,bonus FROM angajat; # Popa -> Popescu (500 lei) START TRANSACTION; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare - 500 WHERE client.nume = 'Popa'; ##----------------------- SELECT @Val:= valoare FROM cont JOIN client ON cont.client_id = client.id WHERE client.nume = 'Popescu'; ##-- UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = @Val + 500 WHERE client.nume = 'Popescu'; ##----------------------- COMMIT; # Marinescu -> Popa (500 lei) START TRANSACTION; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare - 500 WHERE client.nume = 'Marinescu'; ##----------------------- SELECT @Val:= valoare FROM cont JOIN client ON cont.client_id = client.id WHERE client.nume = 'Popa'; ##-- UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = @Val + 500 WHERE client.nume = 'Popa'; ##----------------------- COMMIT; # Popa -> Popescu (500 lei) START TRANSACTION; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare - 500 WHERE client.nume = 'Popa'; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare + 500 WHERE client.nume = 'Popescu'; COMMIT; # Marinescu -> Popescu (500 lei) START TRANSACTION; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare - 500 WHERE client.nume = 'Marinescu'; UPDATE cont JOIN client ON cont.client_id = client.id SET valoare = valoare + 500 WHERE client.nume = 'Popescu'; COMMIT; SELECT nume, prenume, valoare FROM cont JOIN client ON cont.client_id = client.id; INSERT INTO client (nume, prenume, adresa) VALUES ('Popa', 'Ion', 'aaaa'), ('Popescu', 'Maria', 'bbbbb'), ('Marinescu', 'Vasile', 'ccccc'), ('Ionescu', 'Andrei', 'ddddd'); INSERT INTO cont (client_id, valoare) VALUES (1,1000),(2,1500),(3,2000),(4,2000);