Oracle基础查询练习（三）

--1、找出EMP 中那些工资高于他们所在部门员工平均工资的员工。

select e1.*, e2.dept_avg_sal
  from emp e1,
       (select deptno, avg(emp.sal) dept_avg_sal
          from emp
         group by emp.deptno) e2
 where e1.deptno = e2.deptno
   and e1.sal > e2.dept_avg_sal

-- 2、查询每个部门中每个职位的最高薪水。
select emp.deptno,ename,sal
from emp,
(select deptno,max(sal)maxsal from emp group by deptno) t
where emp.deptno=t.deptno and emp.sal=t.maxsal;
--效率低下，改进
SELECT deptno,ename,sal FROM (
       SELECT deptno,ename,sal,
              LAST_VALUE(sal)
              OVER(PARTITION BY deptno
                   ORDER BY sal
                   ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)maxsal
       FROM emp) WHERE sal=maxsal;

--3、查询员工的最高工资和最低工资的差距，并显示列名为DIFFERENCE

SELECT (MAX(sal)-MIN(sal)) AS DIFFERENCE FROM emp;


--4、查询各职位的员工人数

select count(*),job from emp group by job

--5、查询工资不在4000到5000之间的员工的姓名和工资
select * from emp e where e.sal Between 4000 and 5000

--6查询员工的姓名及其所在部门的名字和城市

select e.ename,d.loc from emp e,dept d where e.deptno = d.deptno

--7查询员工的姓名和他的管理者的姓名

select e.ename, m.ename as "管理者名称"
  from emp e
  left outer join emp m on e.mgr = m.empno;

--8查询各个部门中工资大于1500的员工人数，查询结果如图所示

SELECT deptno,COUNT(*) FROM emp where sal>1500 GROUP BY deptno;
--9查询各个管理者属下员工的最低工资，其中最低工资不能低于800，且没有管理者的员工不计算在内

SELECT mgr,MIN(sal) FROM emp where mgr is not null GROUP BY mgr having MIN(sal) >= 800;