oracle第六章子查询

桃扇骨 2022-05-14 10:22 246阅读 0赞

第六章 子查询  
嵌套查询（一个子查询的结果是另外一个子查询的条件）

查询工资比Smith工资高的员工信息  
1.查询Smith的工资  
  select salary from s\_emp where last\_name='Smith';   
2.工资比Smith工资高的员工信息  
  select id,last\_name,salary from s\_emp where salary>(select salary from s\_emp where last\_name='Smith');  
查询平均工资比41号部门的平均工资高的部门中的员工信息  
  select last\_name,salary,dept\_id 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));

把查询的结果作为一张表处理  
  select avg(salary) from s\_emp where dept\_id=41;  
   
查询平均工资比1247.5高的部门中员工信息  
1.查询部门平均工资比1247.5高的部门有哪些  
select dept\_id from s\_emp group by dept\_id having avg(salary)>1247.5  
2.select last\_name,salary from s\_mep where dept\_id in();  
案列(把查询的结果作为一张表处理)  
查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资  
1.查询41号部门的平均工资  
select avg(salary) from s\_emp where dept\_id=41      
2.查询部门平均工资比41高的部门有哪些  
select dept\_id,avg(salary) avg from s\_emp group by dept\_id having avg(salary)>()   
3.显示员工信息及平均工资  
select s.last\_name,s.salary,m.avg from s\_emp s,() m where s.dept\_id=m.dept\_id  
查询平均工资比 41号部门的平均工资 高的部门中员工的信息,并且显示出当前部门的平均工资,同时显示出部门的名字  
1.查询41号部门的平均工资  
select avg(salary) from s\_emp where dept\_id=41      
2.查询部门平均工资比41高的部门有哪些  
select dept\_id,avg(salary) avg from s\_emp group by dept\_id having avg(salary)>()   
3.显示员工信息及平均工资  
select s.last\_name,s.salary,m.avg,d.name from s\_emp s,() m,s\_dept d where s.dept\_id=m.dept\_id  
and m.dept\_id=d.id  
查询员工信息,这些员工的工资要比自己所在部门的平均工资高  
1.查询所有部门的平均工资  
select    avg(salary) avg,dept\_id from s\_emp     group by dept\_id;  
2.些员工的工资要比自己所在部门的平均工资高  
select s.last\_name,s.salary,m.avg from s\_emp s,() m where s.dept\_id=m.id and s.salary>m.avg  
查询员工信息,这些员工的工资要比自己所在部门的平均工资高,同时显示部门的名称以及所在地区  
 select dept\_id,avg(salary) from s\_emp group by dept\_id  
 select s.last\_name,s.salary,s.dept\_id,d.name,r.region from s\_emp s,s\_dept d,() m   
 where s.dept\_id=d.id and d.id=m.dept\_id and d.region\_id=r.id;  
 select s.last\_name,s.salary,s.dept\_id,d.name,r.region from s\_emp s,s\_dept d,s\_region r  
 (select dept\_id,avg(salary) from s\_emp group by dept\_id) m   
 where s.dept\_id=d.id and d.id=m.dept\_id and d.region\_id=r.id;  
查询工资比 Ngao所在部门平均工资 要高的员工信息,同时这个员工所在部门的平均工资 也要   
比Ngao所在部门的平均工资要高,显示当前部门的平均工资以及部门的名字和所在地区  
 select dept\_id from s\_emp where last\_name='Ngao'  
 select avg(salary) from s\_emp where dept\_id=(select dept\_id from s\_emp where last\_name='Ngao');  
 select dept\_id,avg(salary) 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 s.last\_name,m.avg(salary),r.name,s.salary from s\_emp s,s\_dept d,s\_region r,( select dept\_id,avg(salary) 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'))) m where salary>m.avg(salary) and m.dept\_id=s.dept\_id and d.region.id=r.id;   
 组装select s.last\_name,m.avg,r.name,s.salary from s\_emp s,s\_dept d,s\_region r,  
 (select dept\_id,avg(salary) avg 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'))) m where s.salary>(select avg(salary) from s\_emp   
 where dept\_id=(select dept\_id from s\_emp where last\_name='Ngao')) and m.dept\_id=s.dept\_id   
 and d.region\_id=r.id and s.dept\_id=d.id;   
查询s\_emp表中最大的工资数,并且显示出这个最大工资的员工的名字  
 select last\_name,salary from s\_emp where salary=(select max(salary) from s\_emp);  
查询s\_emp表每个部门的最大工资数,并且显示出这个最大工资的员工名字以及该部门  
    的名字和该部门所属区域,并且使用部门编号进行排序  
 select max(salary) max,dept\_id from s\_emp group by dept\_id  
 select s.last\_name,d.name,r.name,m.max,s.dept\_id from s\_emp s,s\_dept d,s\_region r,(select max(salary)   
 max,dept\_id from s\_emp  group by dept\_id) m where s.dept\_id=m.dept\_id and s.salary=m.max   
 and m.dept\_id=d.id and d.region\_id=r.id;