oracle复杂查询例题

1.查询薪资高于在部门30工作的所有雇员的薪资的雇员姓名和薪资、部门名称、部门位置、部门人数。

2.查询与SCOTT从事相同工作的所有雇员及部门名称、部门人数、部门领导。

第一题
(1)
select sal
from emp
where deptno='30';
#查询部门为30部门的薪资，子句
(2)
select a.ename,a.sal,b.dname,b.loc
from emp a left join dept b
where >all(select deptno
from emp
where deptno='30');
(3)
select deptno,count(empno) temp
from emp
group by deptno;
#查询部门人数
##########################################################################################
#总语句
select a.ename,a.sal,b.dname,b.loc,c.temp
from emp a left join dept b on a.deptno=b.deptno left join (
select deptno,count(empno) temp
from emp
group by deptno
) c on a.deptno=c.deptno
where sal>all(select sal
from emp
where deptno='30');


第二题
(1)
#查询SCOTT的工作职位
select job 
from emp
where ename='SCOTT';
(2)
select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno,b.dname
from emp a left join dept b 
on a.deptno=b.deptno where job=(select job 
from emp
where ename='SCOTT');
(3)
#查询部门人数
select deptno,count(empno)
from emp
group by deptno;  
(4)
#查询部门领导，子句
select empno,deptno,ename
from emp
where job='MANAGER'
group by empno,deptno,ename;
###############################################################################################
#总语句
select a.empno,a.ename,a.job,a.mgr,a.hiredate,a.sal,a.comm,a.deptno,b.dname,c.temp
from emp a left join dept b on a.deptno=b.deptno left join (select deptno,count(empno) temp
from emp
group by deptno) c on a.deptno=c.deptno
where job=(select job 
from emp
where ename='SCOTT');