子查询(嵌套查询)

即一个select语句中嵌套了另外的一个或者多个select语句

例子：
1.查询工资比Simth工资高的员工信息
//查Simth的工资
select last_name,salary 
from s_emp
where lower(last_name)='smith';
//查询工资比Simth工资高的员工信息
select last_name,salary
from s_emp 
where salary>(
select salary 
from s_emp
where lower(last_name)='smith'
);

2.查询平均工资比 41号部门的平均工资高的部门中员工的信息
//41号部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id
having dept_id=41;

//平均工资比41号部门高的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
group by dept_id
having dept_id=41
);

//这些部门中的员工信息
select last_name,salary
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
group by dept_id
having dept_id=41
)
);


3.查询平均工资比41号部门的平均工资高的部门中
员工的信息,并且显示出当前部门的平均工资,同时显
示出部门的名字

//41号部门的平均工资
select avg(salary)
from s_emp
where dept_id=41;

//平均工资比41号部门的平均工资高的部门
select dept_id,avg(salary)
from s_emp
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=41
);

//当前部门的平均工资
select avg(salary)
from s_emp
group by dept_id;

select s1.last_name,s1.salary,temp.avgsal,sd.name
from s_emp s1,(
select avg(salary) avgsal,dept_id
from s_emp
group by dept_id
)temp,s_dept sd
where s1.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
)
) and temp.dept_id=s1.dept_id
and s1.dept_id=sd.id;

4.查询员工信息,这些员工的工资要比自己所在部门的平均工资高,
 同时显示部门的名称以及所在地区

//每个部门的平均工资
select dept_id,avg(salary)
from s_emp
group by dept_id;

//比自己所在部门工资高的员工
select s1.last_name,s1.salary,s1.dept_id,temp.avgsal
from s_emp s1,(
select dept_id,avg(salary) avgsal
from s_emp
group by dept_id
)temp
where s1.dept_id=temp.dept_id
and s1.salary>temp.avgsal;

//显示名称和区域
select s1.last_name,s1.salary,s1.dept_id,temp.avgsal,sd.name,sr.name
from s_emp s1,(
select dept_id,avg(salary) avgsal
from s_emp
group by dept_id
)temp,s_dept sd,s_region sr
where s1.dept_id=temp.dept_id
and s1.salary>temp.avgsal
and sd.region_id=sr.id
and s1.dept_id=sd.id;

5.查询工资比Ngao所在部门平均工资要高的员工信息,
 同时这个员工所在部门的平均工资也要比Ngao所在部
 门的平均工资要高,显示当前部门的平均工资以及部门的
 名字和所在地区

//Ngao所在部门
select last_name,dept_id
from s_emp
where last_name='Ngao';

//Ngao所在部门部门平均工资
select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
);

//平均工资比Ngao所在部门部门平均工资高的部门
select dept_id
from s_emp 
group by dept_id
having avg(salary)>(
select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
)
);


//
select s1.id,s1.last_name,s1.dept_id,temp.avgsal,sd.name,sr.name
from s_emp s1,(
select dept_id,avg(salary) avgsal
from s_emp
group by dept_id
)temp,s_dept sd,s_region sr
where s1.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=(
select dept_id
from s_emp
where last_name='Ngao'
)
)
) and temp.avgsal>(
select avg(salary)
from s_emp
where dept_id=(
select dept_id
from s_emp
where last_name='Ngao'
)
) and s1.dept_id=temp.dept_id
and sd.region_id=sr.id
and s1.dept_id=sd.id;