SQL查询语句测试题

根据Oracle自带的两张表出的4道题

根据emp表和dept表进行查询:
select * from dept;
select * from emp;

1. 使用两种函数, 计算员工的实发工资.ollpo  
   结果集要求显示: 员工编号, 员工姓名, 工资, 奖金, 实发工资, 部门名称 

select 
e.empno 员工编号, e.ename 员工姓名,e.sal 工资,
e.comm 奖金, nvl(e.sal+e.comm, e.sal) 实发工资 ,
d.dname 部门名称 
from emp e join dept d on e.deptno=d.deptno;

2. 统计每个部门的平均工资, 总工资, 总人数, 最高工资, 最低工资.
   结果集要求显示: 部门名称, 总人数, 平均工资, 总工资, 总人数, 最高工资, 最低工资
select 
d.dname 部门名称,
avg(nvl(e.sal+e.comm,e.sal))平均工资,
sum(nvl(e.sal+e.comm,e.sal))总工资,
count(e.empno)总人数, 
max(nvl(e.sal+e.comm,e.sal))最高工资, 
min(nvl(e.sal+e.comm,e.sal))最低工资 
from emp e join dept d on e.deptno=d.deptno 
group by d.dname;

3. 查询员工的直属经理
   结果集要求显示: 员工编号, 员工姓名, 部门名称, 经理姓名, 经理编号, 经理部门名称
select e.empno 员工编号, e.ename 员工姓名, ed.dname 员工的部门名称,
       m.empno 经理编号, m.ename 经理姓名, md.dname 经理的部门名称
from emp e
left join emp m
on e.mgr = m.empno
left join dept ed
on e.deptno = ed.deptno
left join dept md
on m.deptno = md.deptno;

4. 查询实发工资总排名前十的员工, 将十人按照部门分组排名显示, 排名可并列, 排名不空出
   结果集要求显示: 员工编号, 员工姓名, 部门名称, 实发工资, 部门排名, 总排名
select dense_rank() over(partition by tbl.部门名称 order by tbl.实发工资 desc) 部门排名,
       tbl.*
from (
      select dense_rank() over(order by t.实发工资 desc) 总排名,
       t.*
       from (
            select e.empno 员工编号, e.ename 员工姓名, d.dname 部门名称, 
            nvl(e.sal+e.comm, e.sal) 实发工资
            from emp e
            inner join dept d
            on e.deptno = d.deptno
            ) t
     ) tbl
where tbl.总排名 <= 10