gistfile1.txt

create table JobHistory (
  jobid integer not null auto_increment,
	jobtitle varchar(32) not null,
	salary int not null,
	startdate date not null,
	enddate date null,
	primary key (jobid)
);

INSERT INTO `JobHistory` (`jobid`, `jobtitle`, `employee`, `salary`, `startdate`, `enddate`)
VALUES
  (1,'Newspaper Delivery','joe',3000,'1994-04-12','1998-08-30'),
	(2,'Restaurant Server','joe',12000,'1998-10-05','1999-05-01'),
	(3,'Restaurant Server','joe',17000,'1999-06-03','1999-08-25'),
	(4,'Research Assistant','joe',13000,'1999-09-06','2000-12-05'),
	(5,'Restaurant Server','joe',14500,'2001-05-29','2001-08-31');

-- Finding Joe's maximum salary is easy
select max(salary)
from JobHistory
where employee = 'joe';

-- How about finding the job where Joe made the most money?
-- Subqueries are lame.
select *
from JobHistory
where employee = 'joe' and salary = (select max(salary) from JobHistory where employee = 'joe');

-- Use a reflexive outer join with an inequality condition
select J1.*
from JobHistory J1 left join JobHistory J2 on (J1.employee = J2.employee and J1.salary < J2.salary)
where J2.jobid is null;