xHzUVnDC

1CREATE DATABASE Employee;
2USE Employee;
3
4CREATE TABLE IF NOT EXISTS employee(
5    id int NOT NULL,
6    department_id int NOT NULL,
7    chief_id int NOT NULL,
8    name varchar(20) NOT NULL,
9    position varchar(20) NOT NULL,
10    salary float NOT NULL,
11    PRIMARY KEY(id),
12    FOREIGN KEY (chief_id) REFERENCES employee(id)
13	);
14
15INSERT INTO employee VALUES
16(1, 2, 1, "name1", "position1", 1.0),
17(2, 2, 1, "name2", "position2", 2.0),
18(3, 2, 1, "name3", "position3", 3.1),
19(4, 2, 1, "name4", "position4", 4.0),
20(5, 2, 1, "name3", "position3", 5.3),
21(6, 2, 1, "name2", "position2", 6.2),
22(7, 2, 1, "name1", "position1", 1.4),
23(8, 2, 1, "name5", "position5", 4.1),
24(9, 3, 1, "name2", "position2", 3.6),
25(10, 3, 1, "name3", "position3", 5.9);
26
27/*Запрос 1*/
28SELECT DISTINCT a.name, b.position FROM employee a, employee b WHERE a.department_id = b.department_id;
29
30/*Запрос 2*/
31SELECT name FROM employee a WHERE a.salary = 2 * (SELECT AVG(salary) from employee b where b.chief_id = a.id);