-- 1 创建视图 show_max_sal_name_view 显示工资最高的员工姓名及他的工资

create or replace view show_max_sal_name_view as

select ename,sal from scott.emp where sal=(select max(sal) from scott.emp);

select * from show_max_sal_name_view;

--2 创建视图 show_min_sal_dname 显示平均工资最低的部门的部门名及平均工资

    --先按部门统计平均工资

    select deptno,avg(sal) from scott.emp group by deptno;

    --获得最低的平均工资

    select min(avg(sal))from scott.emp group by deptno;

    --获得最低平均工资及其部门号

    select * from

    (select deptno,avg(sal) 平均工资 from scott.emp  group by deptno) t

    where t.平均工资 =

    (select min(avg(sal)) from scott.emp group by deptno);

    --获得最低平均工资及其部门名  创建视图

    create or replace view show_min_sal_dname as

    select * from

    (select deptno,avg(sal) 平均工资 from scott.emp  group by deptno) t

    where t.平均工资 =

    (select min(avg(sal)) from scott.emp group by deptno);

--3 创建视图 show_leader_name 显示所有员工的名字和他们上级领导名。 （难）

     --使用mgr字段进行emp表的“自关联”

     select e1.ename as 员工名字,e2.ename as 领导名字 from scott.emp e1,scott.emp e2

     where e1.mgr=e2.empno

     union

     select ename,'无领导'from scott.emp where mgr is null;

=========================================================================================================

-- 把空替换成0

  update scott.emp set comm = '0' where comm is null;

  --查出下级比上级 工资+奖金 高的员工编号，员工姓名

  create view show_higher_empee as

  select * from(

  select e1.empno,e1.ename as 本人姓名,e2.ename as 领导姓名,e1.sal+nvl(e1.comm,'0') as 本人总收入,

  e2.sal+nvl(e2.comm,'0') as 领导总收入

    from scott.emp e1,scott.emp e2 where e1.mgr=e2.empno

    ) where 本人总收入>领导总收入;

      --采用join连接

      select * from

      (

      select e1.empno,e1.ename as 本人姓名,e2.ename as 领导姓名,

      e1.sal+nvl(e1.comm,'0') as 本人总收入,

      e2.sal+nvl(e2.comm,'0') as 领导总收入

      from scott.emp e1 inner join scott.emp e2 on e1.mgr=e2.empno

      )

      where 本人总收入>领导总收入;

  select * from show_higher_empee;