我在b站学数据库 （十一）：视图的练习与总结

上一篇：我在b站学数据库 （十）：视图

数据准备同前一篇文章
1、查询部门平均薪水最高的部门名称

 #方式一
 select a.deptno,a.dname
 from dept a,
 (
 select *
 from(select *,rank() over(order by avg_sal desc) rn
      from (select deptno,avg(sal) avg_sal from emp group by deptno ))
where rn = 1;
)ttt
where a.deptno = ttt.deptno;



#方式二
select dname from dept  a ,(select deptno,avg(sal) from emp group by deptno order by avg(sal) desc limit 1) b
where a.deptno = b.deptno;  




#方式三
create view test_view1
as
select deptno,avg(sal) avg_sal from emp group by deptno

create view test_view2
as
select *,rank() over(order by avg_sal desc) rn from test_view1

create view test_view3
as
select * from test_view2 tt where rn = 1

select a.deptno,a.dname
from dept a,test_view3 ttt where a.deptno = ttt.deptno; 

2、查询员工比所属领导薪资高的部门名、员工名、员工领导编号

#查询员工比领导工资高的部门号
creat view test_view4
as
select 
	a.ename ename,
	a.sal esal,
	b.ename mgrname,
	b.sal msal,
	a.deptno
from emp a,emp b
where a.mgr = b.empno and a.sal > b.sal;
#将第一步查询出来的部门号和部门表进行链表查询
select * from dept a join test_view4 b on a.deptno = b.deptno;

3、查询工资等级为4级，2000年以后入职的工作地点为上海的员工编号、姓名和工资，并查询出薪资在前三名的员工信息

#视图创建
creat view test_view5
as
select a.deptno,a.dname,a.loc,b.empno,b.ename.b.sal,c.grade
from dept a 
  join rmp b on a.deptno = b.deptno and year(hiradate) > '2000' and a.loc = '上海'
  join salgrade c on grade = 4 and (b.sal between c.losal and c.hisal);
#最终
select * 
from
(select *,rank() over(order by sal desc) rn
from test_view5) t
where rn <= 3;

视图总结：




更新视图