Oracle实战练习(续一)

#非等值连接
#查询出员工的薪水等级
select ename, grade from emp e join salgrade s on (e.sal between s.losal and s.hisal);

 select ename, dname, grade from emp e join dept d on (e.deptno = d.deptno)
join salgrade s on(e.sal between s.losal and s.hisal) where ename not like'_A%';

#查出上司是谁,采用自连接
 select e1.ename, e2.ename from emp e1 join emp e2 on(e1.mgr=e2.empno);
#外连接, 将左边的king也显示出来
select e1.ename, e2.ename from emp e1 left join emp e2 on(e1.mgr=e2.empno);

#查询出员工姓名,所属部门名称
select ename, dname from emp e join dept d on(e.deptno = d.deptno);
#运用右外连接将右边的表的数据显示出来
select ename, dname from emp e right outer join dept d on (e.deptno=d.deptno);
#full join
select ename, dname from emp e full join dept d on (e.deptno=d.deptno);
#求部门平均薪水的等级
#1.首先求部门的平均薪水
 select deptno, avg(sal) from emp group by deptno;
#2.将上面的SQL语句看成是一张表,取别名为t
 select deptno, avg_sal, grade from (select deptno, avg(sal) avg_sal from em
p group by deptno)t join salgrade s on (t.avg_sal between s.losal and s.hisal);

#求部门平均薪水等级
#1.求出每个员工的薪水等级
select deptno,ename, grade from emp join salgrade s on (emp.sal between s.losal and s.hisal);
#2.求出每个部门的薪水等级,并按部门编号分组
select deptno,avg(grade) from (select deptno, ename, grade from emp join sa
lgrade s on(emp.sal between s.losal and s.hisal) )group by deptno;

#雇员中有哪些人是经理人
#1.首先查询出哪些人是经理
 select distinct mgr from emp;
#2.
 select ename from emp where empno in(select distinct mgr from emp);

#不准用组函数,求薪水的最高值(面试题) (使用自连接)
#1.采用自连接, 记录都小于e2表中的每一个记录,那么肯定有一个是连接不上条件的,那么这个就是最大值
 select distinct e1.sal from emp e1 join emp e2 on (e1.sal <e2.sal);
 select sal from emp where sal not in (select distinct e1.sal from emp e1 join emp e2 on (e1.sal<e2.sal));
#求出来的最高薪水值是5000

#求平均薪水最高的部门的部门编号
#1.求平均薪水,按部门编号分组
 select deptno, avg(sal) from emp group by deptno;
#2.求平均薪水最高的
 select max(avg_sal) from (select  avg(sal) as avg_sal from emp group by dep
tno);   # 2916.66667
#3.求平均薪水最高的部门编号(平均薪水in最高薪水)
 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, d
eptno from emp group by deptno));

#求平均薪水最高的部门的部门名称
#1.求平均薪水最高的
select max(avg_sal) from (select avg(sal) avg_sal, deptno from emp group by deptno);
#2.再把部门编号求出来
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));
#3.求出部门名称
select dname, deptno from dept where deptno in(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)));
     
#视图就是一张虚表
create view v$dept_avg_sal_info as (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 dname, t1.deptno, grade, avg_sal from v$dept_avg_sal_info t1 join dept on(t1.deptno = dept.deptno) where t1.grade = (select min(grade) from v$dept_avg_sal_info);

#求部门经理人中平均薪水最低的部门名称
select deptno, empno, ename, mgr from emp where mgr is not null;
select ename, mgr from emp;
select e1.empno, e1.ename,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno);
#1.查出谁是经理人
select e2.deptno, e2.ename,e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno);
create view v$_mgr_sal_info as select e2.deptno,e2.ename, e2.sal from emp e1 join emp e2 on(e1.mgr = e2.empno);
#2.求平均薪水
select deptno, avg(sal) avg_sal from emp group by deptno;
#求经理人的平均薪水
select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno;
#3.求平均薪水最低的
select min(avg_sal) from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno);
#4.求平均薪水最低的部门名称
select deptno, avg_sal  from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno) where avg_sal = (select min(avg_sal) from (select deptno, avg(sal) avg_sal from v$_mgr_sal_info group by deptno));

#比普通员工的最高薪水还要高的经理人名称
#1.经理人不为空的
select distinct mgr from emp where mgr is not null;
#2.选出不是经理人的薪水最高的()
select max(sal) from emp where empno not in(select distinct mgr from emp where mgr is not null);
#
select ename,sal 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));