Oracle表连接与子查询示例

--求部门中哪些人的薪水最高

select ename,sal from emp

join (select max(sal) max_sal,deptno from emp group by deptno) t

on (emp.sal  = t.max_sal and emp.deptno = t.deptno);

--求部门平均薪水的等级

 select deptno,avg_sal,grade from

 (select deptno,avg(sal) avg_sal from emp group by deptno) t

 join salgrade s on(t.avg_sal between s.losal and s.hisal);

--求部门平均的薪水等级

select deptno, avg(grade) avg_grade from emp

Join salgrade s on(emp.sal between s,losal and s.hisal)

Group by deptno;

--雇员中有哪些是经理人

select ename from emp where empno in 

(select distinct mgr from emp);

--不准用组函数,求薪水的最高值(面试题)

select distinct sal from emp where sal not in

(select distinct e1.sal from emp e1 join emp e2 on(e1.sal < e2.sal));

--求平均薪水最高的部门和部门编号

select deptno,avg_sal from

(select avg(sal) avg_sal,deptno from emp group by deptno)

where avg_sal =

(select max(avg_sal) from (select avg(sal) avg_sal,deptno from emp

group by deptno));

--求平均薪水最高的部门和部门名称

select dname from dept where deptno = 

(

select deptno from 

(select avg(sal) avg_sal,deptno from emp group by deptno)

where avg_sal = 

(select max(avg_sal) from 

(select avg(sal) avg_sal ,deptno from emp group by deptno)

)

);

--求平均薪水的等级最低的部门的部门名称

 select dname ,t1.deptno,grade,avg_sal from

    ( select deptno,grade,avg_sal from

       (select deptno,avg(sal) avg_sal from emp group by deptno) t

      join salgrade s on (t.avg_sal between s.losal and hisal)

    )t1

    join dept on (t1.deptno = dept.deptno)

    where t1.grade =

    (

     select min(grade) from

      (select deptno, avg(sal) avg_sal from emp group by deptno) t

      join salgrade s on(t.avg_sal between s.losal and s.hisal)

  );

方法二:(使用视图)

--求部门经理人中平均薪水最低的部门名称(思考题)

--求比普通员工的最高薪水还要高的经理人名称

--->1.先求普通员工的最高薪水

select max(sal) from emp where empno not in

(select distinct mgr from emp where mgr is not null);

--->2.

select ename from emp 

where empno in 

(select distinct mgr from emp where mgr is not null)

and sal > 

(select max(sal) from emp where empno not in

(select distinct mgr from emp where mgr is not null)

);

--求薪水最高的前5名雇员

select ename, sal from 

(select ename, sal from emp order by sal desc) where rownum <= 5;

--求薪水最高的第六到第十名雇员

select ename, sal
from (
    select ename, sal, rownum r
    from (
        select ename, sal
        from emp
        order by sal desc
    )
) where r >=6 and r <= 10;

--面试题:比较效率(第一个效率比较高)

select * from emp where deptno = 10 and ename like '%A%';

select * from emp where ename like '%A%' and deptno = 10;