SQLize | PHPize | SQLtest

CREATE TABLE Department ( id INT PRIMARY KEY, name VARCHAR(100) ); CREATE TABLE Employee ( id INT PRIMARY KEY, department_id INT, chief_id INT, name VARCHAR(100), salary INT, FOREIGN KEY(department_id) REFERENCES Department(id), FOREIGN KEY(chief_id) REFERENCES Employee(id) ); INSERT INTO Department(id, name) VALUES (1,'First'); INSERT INTO Department(id, name) VALUES (2,'Second'); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (4, 1, 4, '1 chef', 50); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (5, 2, 5, '2 chef', 60); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (1, 1, 4, '1 not', 50); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (2, 1, 4, '2 not', 60); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (3, 2, 5, '3 not', 45); INSERT INTO Employee(id, department_id, chief_id, name, salary) VALUES (6, 2, 5, '6 not', 65); SELECT * FROM Department; SELECT * FROM Employee; SELECT * FROM Employee WHERE id=chief_id; WITH sum_salary AS ( SELECT department_id, sum(salary) salary FROM Employee GROUP BY department_id) select department_id from sum_salary a where a.salary = ( select max(salary) from sum_salary )