--上机练习5
--1.把10号部门翻译成“十号部门”,20号部门翻译成“二十号部门”,30号部门翻译成“三十号部门”
--方法一
select 
case
  when deptno=10 then '十号部门'
    when deptno=20 then '二十号部门'
      when deptno=30 then '三十号部门'
        else null
            end from emp
--方法二
select  deptno ,decode(deptno,10,'十号部门',20,'二十号部门',30,'三十号部门'） from emp
--2.给所有的10号部门员工加薪10% 20号部门的员工加薪20% 30号员工加薪30% ,其他部门加薪5%
select deptno,
case 
  when deptno=10 then sal*1.1
    when deptno=20 then sal*1.2
      when deptno=30 then sal*1.3
        else sal*1.05
          end 工资 from emp
--3.统计工资级别相应的数量（1600以下 C级,1600-3000 B级,3000以上 A级）
select a ,count(1)from (
select 
case when sal<1600 then 'C级'
     when sal between 1600 and 3000 then 'B级'
      when sal>3000 then 'A级'
        else null end a from emp)  group by a
--实现两种方法的行转列SALESMAN CLERK MANAGER
--方法一
select deptno,
max(case when job='SALESMAN' then sal  else null end )SALESMAN,
max(case when job='MANAGER' then sal else null end ) MANAGER,
max(case when job='CLERK' then sal else null end  ) CLERK
from emp group by deptno
--方法二
select * from (select job,sal,deptno from emp)
pivot(max(sal) for job in ('SALESMAN','MANAGER','CLERK'))

--上机练习6
--1.按照部门编号升序查找所有部门名称，用、隔开
select deptno ,listagg(dname,'、') within group(order by deptno) from dept group by deptno
--2.按照工资降序查找每个部门的员工姓名，用、隔开
select listagg(ename,'、') within group(order by sal desc) ,deptno from emp group by deptno
--3.使用工资偏移计算环比 （sal-lastsal）/sal*100%
select to_char((sal-lag(sal,1,1)over(order by sal))/lag(sal,1,1)over(order by sal)*100,'999990.999')||'%' 环比 from emp
--4.查询员工表中工资最高的前三名
select * from (select sal,row_number()over(order by sal desc) a from emp) where a<4
--5.查询员工表中每个部门的工资第2~3名的员工信息
select * from (select deptno,sal, row_number()over(partition by deptno order by sal desc) a from emp)
 where a between 2 and 3
--6.查询员工姓名、部门及部门平均工资,以及部门内最高工资
select * from (select ename,deptno,sal,avg(sal)over(order by sal),
 row_number()over(partition by deptno order by sal desc) a from emp)
where a=1
--7.每种工作累计求工资和
select job,sal,sum(sal)over(partition by job order by sal) from emp