oracle数据库多表联查，子查询，分页

--======================================多表关联查询
--查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,及领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级
select * from salgrade;
select * from emp;
select * from dept;

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "salLevel",e1.mgr,e2.ename,e2.deptno,d2.dname,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') "salLevel"
from emp e1,dept d1,salgrade s1,emp e2,dept d2,salgrade s2 
 where e1.deptno = d1.deptno and e1.sal between s1.losal and s1.hisal
and e1.mgr = e2.empno and e2.deptno = d2.deptno and e2.sal between s2.losal and s2.hisal 
--1.查询员工编号,员工姓名,领导编号,领导姓名

--2.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,领导编号,领导姓名,领导部门编号,领导部门名称

--3.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,领导工资等级

--4.查询员工编号,员工姓名,员工部门编号,员工部门名称,员工部门地址,中文显示员工工资等级,领导编号,领导姓名,领导部门编号,领导部门名称,中文显示领导工资等级

select e1.empno,e1.ename,e1.deptno,d1.dname,d1.loc,decode(s1.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级'),e2.empno,e2.ename,e2.deptno,d2.dname,d2.loc,decode(s2.grade,1,'一级',2,'二级',3,'三级',4,'四级',5,'五级') from emp e1,emp e2,dept d1,dept d2,salgrade s1,salgrade s2 where 
e1.mgr = e2.empno and e1.sal between s1.losal and s1.hisal
and e1.deptno = d1.deptno and e2.sal between s2.losal and s2.hisal
and e2.deptno = d2.deptno

--======================================外连接
--1.查询员工编号，姓名，领导编号，领导姓名，包括没领导的
----left join on方式
select e1.empno,e1.ename,e2.empno,e2.ename  from emp e1 left join emp e2 on e1.mgr = e2.empno

----Orcl的(+)方式
select e1.empno,e1.ename,e2.empno,e2.ename from emp e1,emp e2 where e1.mgr = e2.empno(+)

--2.查询出所有部门信息(包括没员工的部门)及部门下的员工信息
select * from emp,dept where emp.deptno(+) = dept.deptno;

--===========子查询
--1.查询比雇员7654工资高，同时从事和7788的工作一样的员工
select * from emp where sal > (select sal from emp where empno = 7654)
and job = (select job from emp where empno = 7788);

--2.查询每个部门最低工资及最低工资的部门名称和雇员名称
select emp.empno,emp.ename,e1.minsal,e1.deptno from (select min(sal) minsal,deptno from emp group by deptno) e1,emp,dept 
where e1.deptno = dept.deptno and emp.deptno = e1.deptno and e1.minsal = emp.sal;

select * from dept;
select * from emp;
--===========课堂练习
--1.找到员工表中工资最高的前三名
select rownum,empno,ename,sal from emp  order by sal desc;

select rownum,e.* from (select emp.* from emp order by sal desc)e where rownum <=3

--2.找到员工表中薪水大于本部门平均工资的所有员工
select emp.empno,emp.ename,e1.avgsal,e1.deptno,emp.sal from (select avg(sal) avgsal,deptno from emp group by deptno) e1,emp 
where e1.deptno = emp.deptno and e1.avgsal < emp.sal

select avg(sal),deptno from emp group by deptno
select * from emp;
--3.统计每年入职的员工个数
select count(*),to_char(hiredate,'yyyy') from emp group by to_char(hiredate,'yyyy');

select sum(hcount) "Total",sum(decode(hdate,'1980',hcount)) "1980",min(decode(hdate,'1981',hcount)) "1981",max(decode(hdate,'1982',hcount)) "1982",avg(decode(hdate,'1987',hcount)) "1987" from (select count(*) hcount,to_char(hiredate,'yyyy') hdate from emp group by to_char(hiredate,'yyyy')) e;
--===========分页查询
--1.查询员工表，将员工工资进行降序查询，并进行分页取出第一页，一页三条记录
select * from (select rownum r,e.* from (select * from emp order by sal desc)e) e1 
where r > 0 and r <= 3
/*
分页公式
pageNo = 1
pageSize = 3
select * from (select rownum r,e.* from (select * from 表名 order by 列名 desc)e) e1 
where r > (pageNo - 1)*pageSize and r <= pageNo*pageSize
*/

--===========集合运算（了解）
--1.查询工资大于1200并且job是SALESMAN(intersect)
select * from emp where sal > 1200
intersect
select * from emp where job = 'SALESMAN'

--2.查询工资大于1200或者job是SALESMAN(union)
select * from emp where sal > 1200 
union
select * from emp where job = 'SALESMAN' 

--3.求工资大约1200和job是SALESMAN的差集(minus)
select * from emp where sal > 1200 
minus
select * from emp where job = 'SALESMAN'

--==========================exists / not exists
/*
select ... where exists(查询语句)
exists：当查询结果不为null，返回true
  当查询结果为null，返回false

*/
--1.查询出有员工的部门

select * from dept where exists(select * from emp where dept.deptno = emp.deptno)

select * from dept where not exists(select * from emp where dept.deptno = emp.deptno)

select * from emp where 1=1;
select * from emp where exists(select * from dept where deptno = 199);