练习上节[006]学习的SQL
--练习
--1.查看与CLARK相同职位的员工
select * from emp where job = (select job from emp where ename = 'CLARK');
--2.查看低于公司平均工资的员工
select * from emp where sal < (select avg(sal) from emp);
--3.查看与ALLEN同部门的员工
select *
from emp
where deptno = (select deptno from emp where ename = 'ALLEN');
--4.查看平均工资低于20号部门平均工资的部门平均工资
select deptno, avg(sal)
from emp
group by deptno
having avg(sal) < (select avg(sal) from emp where deptno = 20);
--5.查看低于自己所在部门平均工资的员工
select e.*
from emp e, (select deptno, avg(sal) avg_sal from emp group by deptno) s
where e.deptno = s.deptno
and e.sal < s.avg_sal
--6.查看公司工资排名的第1-5名员工
select *
from (select rownum rn, s.*
from (select ename, sal from emp order by sal desc) s
where rownum <= 5);
--7.查看CLERK职位的人数和其他职位的总人数各多少?
select decode(job, 'CLERK', 'CLERK', 'OTHER'), count(*)
from emp
group by decode(job, 'CLERK', 'CLERK', 'OTHER');
--8.查看每个职位的工资排名
select ename,
sal,
job,
row_number() over(partition by job order by sal desc)
from emp;
--9.查看每个职位的工资排名,若工资一致,排名一致
select ename, sal, job, rank() over(partition by job order by sal desc)
from emp;
--10.查看每个职位的工资排名,若工资一致,排名一致,不跳名次
select ename,
sal,
job,
dense_rank() over(partition by job order by sal desc)
from emp;
--11.分别查看:同部门同职位,[同职位],以及所有员工的工资总和
/*select deptno, job, sum(sal)
from emp
group by rollup(deptno, job)
order by deptno, job;*/
select deptno, job, sum(sal)
from emp
group by rollup(job,deptno)
order by deptno, job;
--12.分别查看:同部门同职位,同职位,同部门以及所有员工的工资总和
select deptno, job, sum(sal)
from emp
group by cube(deptno, job)
order by deptno, job;
--13.分别查看同部门同职位和同职位的员工的工资总和
select deptno, job, sum(sal)
from emp
group by grouping sets((deptno, job), job)
order by deptno, job;