oracle之子查询、连接查询、递归查询

--查询与员工SMITH属于同一个部门的员工
select * from emp where deptno=(select deptno from emp where ename=‘SMITH‘)

--查询工作岗位与部门10的工作岗位相同的员工信息 
select * from emp where job in(select job from emp  where deptno=10) and deptno!=10;
--使用exists 存在 岗位相同
select *
  from emp e1
 where exists (select job
          from emp e2
         where deptno = 10
         and e1.job = e2.job
           )
and deptno != 10

--3.查询工作岗位与部门10的工作岗位不相同的员工信息 
select * from emp where job not in(select job from emp where deptno=10)
--使用not exists 存在 岗位不相同
select *
  from emp e1
 where not exists (select job
          from emp e2
         where deptno = 10
         and e1.job = e2.job
           )

--查询跟smith同一个部门,同一个岗位的员工信息
select * from emp where 
deptno=(select deptno from emp where ename=‘SMITH‘) 
AND 
 job=(select JOB from emp where ename=‘SMITH‘)
 -- 
 select *
   from emp
  where (deptno, job) = (select deptno,job from emp where ename = ‘SMITH‘

--将部门30中工资排序前5位员工信息
 select rownum, e.*
   from (select * from emp where deptno = 30 order by sal desc) e
  where rownum <= 5

--将查询所有员工工资排序在6-10位之间的员工信息 每页显示五条。 1-5 6-10
 -- 当前页数pageNo 每页的数据为5条  pageSize
 --开始条书>：0  5   10   （pageNo-1）*pageSize
 --结束条数<=: 5  10  15  pageNo*pageSize  
 --rownum 每个结果集的序号
 select * from (
   select rownum rn, e.*
     from (select * from emp order by sal desc) e
    where rownum <= 2*5 ) e1  where e1.rn>（2-1）*5

--显示工资比部门30的所有员工的工资都要高的员工信息
--第一种实现方式
select * from emp where sal>(select max(sal) from emp where deptno=30)
--第二种 sal要大于结果集中所有的记录
select * from emp where  sal>all(select sal from emp where deptno=30)

--显示工资比部门30的任意员工的工资高的员工信息
   select * from emp where sal>(select min(sal) from emp where deptno=30) 
select * from emp where sal>any(select sal from emp where deptno=30)

--显示10部门下所有员工的部门名称
   select d.dname,e.ename,e.sal from emp e,dept d    --得到dept和emp数据的笛卡尔积
   where e.deptno=d.deptno and e.deptno=10--通过where条件去除笛卡尔积

--exam:查询工资高于自己部门平均工资的员工的姓名和工资信息
   --先找出每个部门的平均工资
   select ename,sal from emp e1,
   (select deptno,avg(sal) dept_sal from emp group by deptno) avge
   where e1.deptno = avge.deptno and e1.sal>avge.dept_sal

select s.stuname,se.examsubject,se.examresult from studentinfo s 
left join
STUDENTEXAM se on s.stuid = se.estuid

select s.stuname,se.examsubject,se.examresult from studentinfo s 
right join
STUDENTEXAM se on s.stuid = se.estuid

select s.stuname, se.examsubject, se.examresult
  from studentinfo s
 inner join studentexam se
    on s.stuid = se.estuid

--和内连接等同
select s.stuname,se.examsubject,se.examresult from studentinfo s 
,STUDENTEXAM se where  se.estuid = s.stuid

--查询员工smith的上级领导的姓名
select a.ENAME, A.MGR, b.ENAME
  from emp a, emp b
 where a.mgr = b.EMPNO
   and a.Ename = ‘SMITH‘;
   select * from emp

--员工编号，上级的员工编号
   select * from emp start with empno=7839 connect by   mgr=prior empno;