连接查询及分组查询强化练习

谁践踏了优雅 2022-08-03 14:36 264阅读 0赞

\-- 求每个雇员的姓名，工资，工资等级

select a.ename, a.sal, b.grade from emp a, salgrade b
    where a.sal between b.losal and b.hisal;

\-- 求每个员工的姓名，部门名，部门号

select a.ename, b.dname, b.deptno
      from emp a, dept b
     where a.deptno = b.deptno;

\-- 求每个人的名字和他的经理人的名字，用外连接把KING也取出来

select a.ename, b.ename mgrname from emp a, emp b
    where a.mgr = b.empno;

![SouthEast][]

select a.ename, b.ename mgrname from emp a, emp b
    where a.mgr = b.empno(+);

![SouthEast 1][]

\-- 求公司中谁的薪水最高

select * from emp a where a.sal = (select max(sal) from emp);

\-- 求薪水在部门的平均薪水之上的雇员姓名和及薪水

select a.ename, a.sal, a.deptno
      from emp a, (select avg(sal) avgsal, deptno from emp group by deptno) b
     where a.sal > b.avgsal
       and a.deptno = b.deptno;

\-- 求每个部门中薪水最高的雇员编号，雇员姓名，部门编号，部门名

第一步，求每个部门的最高薪资

select max(sal) maxsal, deptno from emp group by deptno;

![SouthEast 2][]

第二步，匹配每个部门的最高工资，查询最高工资员工姓名

select a.empno, a.ename, b.deptno, c.dname
      from emp a,
           (select max(sal) maxsal, deptno from emp group by deptno) b,
           dept c
     where a.deptno = b.deptno
       and b.deptno = c.deptno
       and a.sal = b.maxsal;

![SouthEast 3][]

\-- 求部门平均薪水的等级

select a.sal, b.grade
      from (select avg(sal) sal, deptno from emp group by deptno) a, salgrade b
     where a.sal between b.losal and b.hisal;

\-- 求部门平均的薪水等级

后续补上sql

\-- 雇员中有哪些人是经理人

select * from emp where job in ('MANAGER', 'PRESIDENT', 'ANALYST');

\-- 求比普通员工的最高薪水还要高的经理人名称

select ename, sal
      from emp
     where sal > (select max(sal)
                    from emp
                   where job not in ('MANAGER', 'PRESIDENT', 'ANALYST'))
       and job in ('MANAGER', 'PRESIDENT', 'ANALYST');

\-- 求薪水最高的前5名雇员

select c.ename, c.sal
      from (select b.ename, b.sal, rownum rn
              from (select a.ename, a.sal from emp a order by sal desc) b) c
     where c.rn <= 5;

\-- 求薪水最高的6到10名的雇员姓名

select c.ename, c.sal
      from (select b.ename, b.sal, rownum rn
              from (select a.ename, a.sal from emp a order by sal desc) b) c
     where c.rn between 6 and 10;

\-- 求最后入职的5名员工

select c.ename, c.hiredate
      from (select b.ename, b.hiredate, rownum rn
              from (select a.ename, a.hiredate from emp a order by hiredate desc) b) c
     where c.rn <= 5;

\-- 求每个部门中薪水最高的前两名雇员

select * from emp e 
    where ( select count(*) from emp where sal > e.sal 
    and deptno = e.deptno ) < 2
    order by e.deptno,e.sal desc;

\-- 不用组函数求最高工资

select a.sal from (select * from emp order by sal desc) a where rownum = 1;
    select e.sal from emp e where e.sal not in(select e2.sal from emp e1,emp e2 where e1.sal>e2.sal);
    select distinct sal from emp
    minus
    select distinct e2.sal from emp e1,emp e2 where e1.sal>e2.sal;

\-- 求平均薪水最高的部门名称

select a.dname, b.deptno
      from dept a,
           (select avg(sal) avgsal, deptno from emp group by deptno) b,
           (select max(a.avgsal) maxsal
              from (select avg(sal) avgsal, deptno from emp group by deptno) a) c
     where a.deptno = b.deptno
       and b.avgsal = c.maxsal;

以上题目均自己所做，如有错误，请及时指正，谢谢。

[SouthEast]: /images/20220731/c8da9a243e494ae3895631309c68bccb.png
[SouthEast 1]: /images/20220731/0ddc5b77fa7c4586ac6b8d26c731c6a1.png
[SouthEast 2]: /images/20220731/0096e3a784e3426e9bf4ba7d879be6ca.png
[SouthEast 3]: /images/20220731/f54940031e1040fab9330965bf6d98f7.png