# 第六章
# Section A
# 练习一: 各部门工资最高的员工(难度:中等)
# 创建Employee 表,包含所有员工信息,每个员工有其对应的 Id, salary 和 department Id。
select database();
create table Employee(
Id int primary key auto_increment,
Name varchar(10) not null ,
salary int default null,
departmentId int
)default charset=utf8;
insert into employee values (1,'Joe',70000,1),
(2,'Henry',80000,2),
(3,'Sam',60000,2),
(4,'Max',90000,1);
select * from employee;
# 创建Department 表,包含公司所有部门的信息
create table Department(
Id int primary key ,
Name char(6)
)default charset=utf8;
insert into department values (1,'IT'),(2,'Sales');
select * from department;
# 编写一个 SQL 查询,找出每个部门工资最高的员工。
# 根据上述给定的表格,Max 在 IT 部门有最高工资,Henry 在 Sales 部门有最高工资。
select max(salary) from employee e
left join
department d on e.departmentId = d.Id
group by d.Name;
select Employee.Name,Employee.salary
from employee
where Employee.salary in
(select max(salary) from employee e
left join
department d on e.departmentId = d.Id
group by d.Name);