知识点

1. 排除在职的最大值和最小值就是salary大于最小值和salary小于最大值
2. where后面不能跟聚合函数，所以需要建立一个子查询

代码

select avg(salary) as avg_salary 
from salaries
where to_date = '9999-01-01'
and salary < (select max(salary) from salaries where to_date = '9999-01-01')
and salary > (select min(salary) from salaries where to_date = '9999-01-01')