10. 查询出每个部门中工资最高的职工。
create or replace view V10 as
select *
from emp a
where sal >=(select max(sal)
from emp b
where a.deptno=b.deptno)
order by deptno;
11. 查询出每个部门比本部门平均工资高的职工人数。(实验室需要修改)
create or replace view V11 as
select deptno,count(empno) headcount from emp a
where sal >(select avg(sal)
from emp b
where a.deptno = b.deptno)
group by deptno;
12. 列出至少有一个员工的所有部门。
解法 1:
create or replace view V12 as
with total_p(deptno, headcount) as
(select a.deptno,count(empno)
from emp a left outer join dept b
on a.deptno=b.deptno
group by a.deptno)
select deptno,dname,loc,headcount
from total_p natural join dept c;
评论0
最新资源