--两个表之间进行的连接(99语法)
select * from emp join dept on emp.deptno=dept.deptno;
select * from emp join dept on dept.deptno=emp.deptno;
等价于(92语法)
select * from emp,dept where emp.deptno=dept.deptno;

--左外连接 以右边为万能行  （确保左边的数据能在右边找到）

select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.mgr=e2.empno;

--右外连接 以左边为万能行
select * from emp;
select * from emp e1 right join emp e2 on e1.mgr=e2.empno;
等价于
select e1.ename,e1.empno ,e1.deptno from emp e1 left join emp e2 on e1.empno=e2.mgr;(左外连接)

--全外连接 保证左边和右边的记录都被找到
select * from emp e1 full join emp e2 on e1.mgr=e2.empno;

92语法关于左外连接 在右边加上一个"+"
select * from emp,dept where emp.deptno=dept.deptno(+);

反过来右外连接 在左边加上一个"+"
select * from emp,dept where emp.deptno(+)=dept.deptno;

-使用伪字段:rownum，----------------------
------------------------------------------------------
--用来标识每条记录的行号，行号从1开始，每次递增1

select a.* ,rownum from emp a;
或者
select emp.* ,rownum from emp ;

--oracle下rownum只能使用 < <=， 不能使用 = > >= 等比较操作符，

--当rownum和order by 一起使用时，会首先选出符合rownum条件的记录，然后再排序
--例如，当我们要求薪水最高的前5个人时，最直接的想法可以这样写：

select sal from emp order by sal desc;
  
select ename ,sal from
(select ename,sal from emp order by sal desc)
where rownum<=5
  //取出薪水在排名在6-10的人
  select *  from(
  select t.*,rownum r from(
  (select sal from emp order by sal desc)t
  )where rownum<=10)
  where r>=6
   
--------------------
--不准用组函数(即MAX()),求薪水的最高值(面试题)
--第一种解决办法:
--1,先把所有薪水按照倒序排列

select sal from emp order by sal desc;

--2,再取第一行
select sal from
(select sal from emp order by sal desc)
where rownum=1

--第二种解决办法:
思路：将同一个表复制成两份，要求某一个表中的sal小于另外一个表中的sal,只有工资最高的不可能小，
再利用 not in 进行筛选从而起得最大值

--1,先跨表查询自己,先求出的结果中,e1.sal不可能出现最大数
  select  e1.sal from emp e1 join emp e2 on e1.sal<e2.sal

--2,然后再not in

select sal from emp where sal not in(select  e1.sal from emp e1 join emp e2 on e1.sal<e2.sal)

-----------------------------
--求平均薪水最高的部门的部门编号
--第一种解决办法:
--1,先求出每个部门的平均薪水,
    select deptno,avg(sal) from emp group by deptno;

--2,再求每个部门的平均薪水的最高值,
   select max(avg_sal) from(select avg(sal) avg_sal from emp group by deptno)                             

--3,最后再求第一步结果中avg_sal = 最高薪水的记录.
  select deptno,avg_sal from
  (select deptno,avg(sal) avg_sal from emp group by deptno)
  where avg_sal=
  (select max(avg_sal)from(select avg(sal) avg_sal from emp group by deptno) )
 

--这种写法没有考虑并列第一的情况
select deptno from
(select deptno,avg(sal) avg_sal from emp group by deptno order by avg(sal) desc)
where rownum<=1