十一、查询平均工资高于8000的部门和其平均工资
select department_id '部门id',AVG(salary) '平均资薪' from employees GROUP BY department_id HAVING AVG(salary) > 8000


十二、查询换过工作员工换工作的次数
select employee_id '员工id',count(*) '换工作次数' from job_history GROUP BY employee_id 


十三、查询在95，96，97，98年各进公司多少人
select YEAR(hire_date) '年份',count(*) from employees where YEAR(hire_date) between 1995 AND 1998 GROUP BY YEAR(hire_date)


十四、输出员工名称，员工id，员工所属部门
select emp.first_name,emp.last_name,department_name from employees emp join departments dep on emp.department_id=dep.department_id


十五、输出员工信息，包括employee_id, first_name, department_id, department_name location_id city
select e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,l.city FROM departments d join employees e on d.department_id=e.department_id join  locations l on d.location_id=l.location_id

十六、在 EMPLOYEES 表中薪水的最低级别和最高级别分别是什么？
select emp.salary  '资薪',j.grade_level '等级'  from employees emp join job_grades j on emp.salary BETWEEN  lowest_sal and highest_sal where emp.employee_id=
(select employee_id from employees emp join job_grades j on emp.salary BETWEEN  lowest_sal and highest_sal ORDER BY salary desc limit 1) or 
emp.employee_id=(select employee_id from employees emp join job_grades j on emp.salary BETWEEN  lowest_sal and highest_sal ORDER BY salary  limit 1)

十七、查询所有有奖金的员工的姓名，部门，地址，城市
select e.employee_id,e.first_name,d.department_id,d.department_name,l.location_id,city FROM departments d join employees e on d.department_id=e.department_id join  locations l on d.location_id=l.location_id where e.employee_id in (select employee_id from employees where commission_pct >= 0)

十八、查询last_name为'Ki' 的员工的经理信息
select * from employees where last_name='Ki' and job_id='AC_MGR'

十九、查询公司工资最低的员工信息
SELECT * from employees  GROUP BY salary LIMIT 1

二十、查询1999年进入公司的员工的最高工资的员工
select * from employees where YEAR(hire_date)='1999' GROUP BY salary desc limit 1

二十一、查询曾经做过ST_CLERK的员工信息
select e.first_name,e.last_name,h.job_id from job_history h LEFT JOIN employees e on  e.employee_id=h.employee_id where h.job_id='st_clerk'