子查询(嵌套查询)

即一个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;