第一种方式
1.查询工资比Smith 工资高的员工信息
（1）查询Smith的工资
select salary
from s_emp
where last_name = 'Smith';
（2）比较
select *
from s_emp
where salary >940;
（3）合并
select*
from s_emp
where salary >(
select salary
from s_emp
where last_name = 'Smith'
);
2.查询与Ngao所在部门是同一个部门的员工
（1）查询Ngao所在的部门
select dept_id
from s_emp
where last_name = 'Ngao';
（2）查询部门为41的员工
select last_name
from s_emp
where dept_id =41;
（3）合并
select last_name
from s_emp
where dept_id =(
select dept_id
from s_emp
where last_name = 'Ngao'
);
第二种方式
1. 查询员工信息，这些员工比自己所在部门平均工资要高
（1）求所有部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id；
（2）求员工的的工资比自己所在部门的平均工资高的
select emp.last_name,emp.salary,ss.dept_id,ss.sa
from s_emp
emp,(select dept_id,avg(salary) sa
from s_emp
group by dept_id) ss
where emp.dept_id = ss.dept_id;
2. 求满足条件的员工所在部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;
合并
select sa.last_name,sa.salary
from (select dept_id,avg(salary)
from s_emp
group by dept_id) ss,( select *
from s_emp
where dept_id in(
select dept_id
from s_emp
group by dept_id
having avg(salary) > (
select avg(salary)
from s_emp
where dept_id = 41
)
)) sa
where sa.dept_id =ss.dept_id;
