create database jsqz;
create table Employee (Id char(4) not null, Name varchar(20), Salary INTEGER, DepartmentId char(4), PRIMARY key (Id) ); insert into Employee VALUES(‘1’,‘Joe’,70000,‘1’);insert into Employee VALUES(‘2’,‘Herry’,80000,‘2’);insert into Employee VALUES(‘3’,‘Sam’,60000,‘2’);insert into Employee VALUES(‘4’,‘Max’,90000,‘1’);create table Department (Id char(4) not null, Name varchar(20) PRIMARY key (Id) );insert into Department VALUES(‘1’,‘IT’);insert into Department VALUES(‘2’,‘Sales’);
select d.NAME,e.NAME,Salary from Employee e join Department d on e.DepartmentId=d.Id where Salary = (select max(Salary) from Employee where DepartmentId = e.DepartmentId)