Oracle 常用的复杂查询语句

--1.列出至少有三个员工的所有部门和部门信息。
select d.* from dept d,
(select e.deptno, count(1) n from emp e group by e.deptno) t
where d.deptno=t.deptno and t.n>=3;
--2.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称
select emp_table.empno, emp_table.ename, emp_dept.dname
from emp emp_table, emp mgr_table, dept emp_dept
where emp_table.mgr=mgr_table.empno 
      and emp_table.deptno=emp_dept.deptno
      and emp_table.hiredate>mgr_table.hiredate;
--3.列出职位为“CLERK”的姓名和部门名称，部门人数：
select e.ename, d.dname, t.n 
from emp e, 
     dept d, 
     (select deptno, count(1) n from emp group by deptno) t
where e.job='CLERK' --确定职位为“CLERK”
      and e.deptno=d.deptno
      and e.deptno=t.deptno;
--4.列出和“SCOTT”从事相同工作的所有员工及部门名称：
select e.*, d.dname
from emp e, dept d
where e.job=(select job from emp where ename='SCOTT') 
      and e.deptno=d.deptno;
--5.列出每个部门工作的员工数量、平均工资和平均服务期限（单位为年）
select e.deptno, 
       count(1), 
       avg(e.sal), 
       to_char(sysdate, 'yyyy')-round(avg(to_char(e.hiredate, 'yyyy')))
from emp e
group by e.deptno;
--6、列出各个部门的MANAGER 的最低薪金：
select e.deptno, min(e.sal)
from emp e
where e.job='MANAGER'
group by e.deptno;
--7、给任职日期超过10年的人加薪10%；
update emp t set t.sal=t.sal*1.1
where trunc(months_between(sysdate, t.hiredate)/12) > 10;



--17,统计薪资 大于 薪资最高的员工 所在部门 的平均工资 和 薪资最低的员工所在部门的平均工资 的平均工资 的员工信息。
----1.薪资最高 5000
     select max(sal)  from emp;    
----2.薪资最高的员工的部门编号 10
     select deptno from emp where sal = (select max(sal)  from emp);
----3.部门的平均工资 2916
     select avg(sal) from emp where deptno = ( select deptno from emp where sal = (select max(sal)  from emp));

----4.薪资最低 800
     select min(sal) from emp;
----5.薪资最低的员工所在部门 20
     select deptno from emp where sal = ( select min(sal) from emp);
----6.部门的平均工资 2175
     select avg(sal) from emp where deptno = (select deptno from emp where sal = ( select min(sal) from emp));

----7.查询上面两个部门的平均工资 2545.5  2545
     select (2916+2175)/2 from dual;
     
     select 
            (
              (select avg(sal) from emp where deptno = ( select deptno from emp where sal = (select max(sal)  from emp)))
              +
              (select avg(sal) from emp where deptno = (select deptno from emp where sal = ( select min(sal) from emp)))            
            )/2
     from dual;       

----8.工资大于  上面两个部门的平均工资
select * from emp where sal >  (select 
            (
              (select avg(sal) from emp where deptno = ( select deptno from emp where sal = (select max(sal)  from emp)))
              +
              (select avg(sal) from emp where deptno = (select deptno from emp where sal = ( select min(sal) from emp)))            
            )/2
     from dual);