yBmX0HsW

1DROP DATABASE IF EXISTS humanResources;
2CREATE DATABASE humanResources;
3USE humanresources;
4CREATE TABLE departments(
5   id TINYINT UNSIGNED PRIMARY KEY,
6   name CHAR(12) NOT NULL,
7   min_salary SMALLINT UNSIGNED NOT NULL,
8   max_salary SMALLINT UNSIGNED NOT NULL
9) ENGINE=InnoDB;
10
11
12CREATE TABLE users(
13   id SMALLINT UNSIGNED PRIMARY KEY,
14   name VARCHAR(255) NOT NULL,
15   salary SMALLINT UNSIGNED NOT NULL,
16   department_id TINYINT UNSIGNED,
17   FOREIGN KEY (department_id) 
18                REFERENCES departments(id)
19) ENGINE=InnoDB;
20
21CREATE TABLE userSkills(
22   user_id SMALLINT UNSIGNED ,
23   year INT NOT NULL,
24   month INT NOT NULL,
25   performance SMALLINT UNSIGNED NOT NULL,
26   FOREIGN KEY (user_id) 
27                REFERENCES users(id)
28) ENGINE=InnoDB;
29 -- zad 1
30DELIMITER $
31CREATE TRIGGER insertSalaryCheck BEFORE INSERT ON users
32FOR EACH ROW
33BEGIN
34DECLARE minSalary SMALLINT DEFAULT 0 ;
35DECLARE maxSalary SMALLINT DEFAULT 0;
36DECLARE invalidSalary CONDITION for  SQLSTATE '45000';
37SELECT min_salary,max_salary   INTO minSalary , maxSalary from departments WHERE id =NEW.department_id ;
38IF(NEW.salary>maxSalary || NEW.salary<minSalary )
39THEN
40SIGNAL   invalidSalary SET MESSAGE_TEXT ='Invalid salaru input!';
41END IF;
42END
43$
44DELIMITER ;
45INSERT INTO departments VALUES(1,'SAP',900,5000);
46INSERT INTO users VALUES(1,'Daniel',1000,1);
47INSERT INTO users VALUES(2,'Valentin',4900,1);
48INSERT INTO users VALUES(3,'Gosho',4500,1);
49INSERT INTO users VALUES(4,'Nelko',2000,1);
50
51INSERT INTO userSkills VALUES(1,4,2018,900);
52INSERT INTO userSkills VALUES(2,4,2018,990);
53INSERT INTO userSkills VALUES(3,4,2018,750);
54INSERT INTO userSkills VALUES(4,4,2018,300);
55
56-- zad 2
57DELIMITER $
58CREATE PROCEDURE infoDepartment(IN depId INT)
59BEGIN
60DECLARE maxSalaryInDep SMALLINT;
61DECLARE minSalaryInDep SMALLINT;
62DECLARE averageSalaryInDep DOUBLE;
63DECLARE maxSalaryOfPerson SMALLINT;
64DECLARE minSalaryOfPerson SMALLINT;
65DECLARE departmentName VARCHAR(255);
66DECLARE finished INT;
67
68
69SELECT departments.name ,AVG(users.salary), MAX(users.salary), MIN(users.salary) INTO departmentName,averageSalaryInDep,maxSalaryOfPerson,minSalaryOfPerson
70 FROM users JOIN departments ON 
71 department_id=depId GROUP by department_id;
72
73SELECT max_salary, min_salary INTO maxSalaryInDep,minSalaryInDep FROM departments WHERE id=depId;
74DROP TABLE IF EXISTS departmentInfo;
75CREATE TEMPORARY table departmentInfo(
76departmentId INT NOT NULL,
77departmentName VARCHAR(255) NOT NULL,
78description VARCHAR(255) NOT NULL,
79value SMALLINT NOT NULL
80);
81
82INSERT INTO departmentInfo VALUES(depId,departmentName,'Max salary of person',maxSalaryOfPerson);
83INSERT INTO departmentInfo VALUES(depId,departmentName,'Min salary of person',minSalaryOfPerson);
84INSERT INTO departmentInfo VALUES(depId,departmentName,'Averager salary in department',averageSalaryInDep);
85INSERT INTO departmentInfo VALUES(depId,departmentName,'Max salary in department',maxSalaryInDep);
86INSERT INTO departmentInfo VALUES(depId,departmentName,'Min salary in department',minSalaryInDep);
87
88SELECT * FROM departmentInfo;
89DROP TABLE departmentInfo;
90END
91$
92DELIMITER ;
93
94call infoDepartment(1);
95
96-- zadacha 3
97DELIMITER $
98DROP PROCEDURE IF EXISTS giveBonuses$
99CREATE PROCEDURE giveBonuses()
100BEGIN
101DECLARE maxSalary INT;
102DECLARE userSalary INT;
103DECLARE finalSalary INT;
104DECLARE userPerform INT;
105DECLARE userName VARCHAR(255);
106DECLARE userId INT;
107DECLARE bonus INT;
108DECLARE finished INT;
109DECLARE userCursor CURSOR FOR
110SELECT user_id, users.name,users.salary ,performance FROM userSkills JOIN users ON users.id=user_id 
111WHERE performance<= (SELECT MAX(performance) FROM userSkills ) GROUP BY performance DESC LIMIT 3 ;
112DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished=1;
113DROP TABLE IF EXISTS usersInfo;
114CREATE TEMPORARY TABLE usersInfo(
115id INT NOT NULL,
116name VARCHAR(255) NOT NULL,
117performance INT NOT NULL,
118salary INT NOT NULL,
119bonus DOUBLE NOT NULL,
120deptMax INT NOT NULL,
121finalSalary DOUBLE NOT NULL
122);
123
124SET finished=0;
125OPEN userCursor;
126userLoop: LOOP
127FETCH userCursor INTO userId,userName,userSalary,userPerform;
128IF(finished=1)
129THEN
130LEAVE userLoop;
131ELSE
132SELECT max_salary INTO maxSalary FROM departments WHERE id= 
133(SELECT department_id FROM users WHERE users.id=userId);
134SET bonus= userSalary*0.2;
135SET finalSalary= userSalary+bonus;
136IF(finalSalary>maxSalary)
137THEN
138SET finalSalary=maxSalary;
139END IF;
140INSERT INTO usersInfo VALUES(userId,userName,userPerform,userSalary,bonus,maxSalary,finalSalary);
141END IF;
142END LOOP;
143CLOSE userCursor;
144SELECT * FROM usersInfo;
145DROP TABLE usersInfo;
146END
147$
148DELIMITER ;
149
150call giveBonuses();