Oracle表连接与子查询示例

--求部门平均薪水的等级
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 losal and hisal)

--求部门中哪些人得薪水最高
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) from
(select deptno ,ename ,grade from emp join salgrade s on(emp.sal between s.losal and s.hisal)) t
group by deptno

--雇员中那些人是经理人
select ename from emp where empno in(select 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 deptno ,avg_sal from
(select avg(sal) avg_sal,deptno from emp group by deptno)
where avg_sal =
(select max(avg(sal)) 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 ,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 s.hisal)
)t1
join dept on(t1.deptno = dept.deptno) where t1.grade =
(
select min(grade) 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 s.hisal)
)
)

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

--求比普通员工的最高薪水还要高的经理人的名称
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名雇员
--求薪水最高的第6到第10名雇员
--面试题：比较效率
  select * from emp where deptno = 10 and ename like '%A%';
  select * from emp where ename like '%A%' and deptno = 10;




create or replace procedure p(v_pid article.pid%type ,v_level binary_interger) is
  cursor c is select * from article where pid = v_pid;
  v_preStr varchar2(1024) := '';
begin
  for i in 1..v_level loop
     v_preStr := v_preStr || '*****';
  end loop;

  for v-article in c loop
    dbms_output.put_line(v_preStr || v_article.con);
        if(v_article.isleaf = 0) then
            p(v_article.id,v_level + 1);
  end loop;
end;