SQL入门学习SectionA练习1

创建Employee 表，包含所有员工信息，每个员工有其对应的 Id, salary 和 department Id。

create table Employee
(Id INTEGER not null,
 Name VARCHAR(128) not null,
 Salary INTEGER not null,
 DepartmentId VARCHAR(128) not null);
 
START TRANSACTION;
INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES (1, 'Joe', 70000, 1);
INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES (2, 'Henry', 80000, 2);
INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES (3, 'Sam', 60000, 2);
INSERT INTO Employee (Id, Name, Salary, DepartmentId)
VALUES (4, 'Max', 90000, 1);
COMMIT;

select * from Employee;查看运行结果

创建Department 表，包含公司所有部门的信息。

create table Department
(Id INTEGER not null,
 Name VARCHAR(128) not null);
 
START TRANSACTION;
INSERT INTO Department (Id, Name)
VALUES (1, 'IT');
INSERT INTO Department (Id, Name)
VALUES (2, 'Sales');
COMMIT;

select * from Department;查看运行结果

编写一个 SQL 查询，找出每个部门工资最高的员工。例如，根据上述给定的表格，Max 在 IT 部门有最高工资，Henry 在 Sales 部门有最高工资。

select p2.name Department, p1.name as Employee, p1.salary as Salary
from Employee as p1 left join Department as p2 on p1.departmentId = p2.id
where (departmentId, salary) in (
  select departmentId, max(salary)
  from Employee
  group by departmentId
);

运行结果