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

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

select a.ename, a.sal, b.grade from emp a, salgrade bwhere 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 bwhere a.mgr = b.empno;

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

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

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;

  

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

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;

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

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 ) < 2order 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 empminusselect 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;

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