1. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和,并且要满足从事同一工作的雇员的月工资大于$1500,输出结果按月工资的合计升序排列
    select job,sum(sal) from emp where job!='SALESMAN' group by job having min(sal)>1500 order by sum(sal)

  2. 求出平均工资最高的部门名称
    select dname from (select avg(sal) s,deptno from emp group by deptno order by avg(sal) desc) t1,dept t2 where rownum=1 and t1.deptno=t2.deptno

  3. 要求查询出比7654工资要高的全部雇员的信息
    select * from emp where sal >(select sal from emp where empno = 7654)

  4. 要求查询工资比7654高,与7788从事相同工作的全部雇员信息
    select * from emp where sal >(select sal from emp where empno = 7654) and job in(select job from emp where empno = 7788)

  5. 查询出工资最低的雇员姓名,工作,工资
    select ename,job,sal from emp where sal=(select min(sal) from emp)

  6. 查询出各部门工资最低的雇员姓名,工作,工资
    select ename,job,sal from emp t1, (select deptno,min(sal) minsal from emp group by deptno ) t2 where t1.deptno=t2.deptno and t1.sal=t2.minsal

  7. 要求查询出部门名称,部门的员工数,部门的平均工资,部门的最低收入雇员姓名,要求显示所有部门名,如果该部门没有任何员工,则员工书和平均工资需显示0,员工姓名显示null即可。

    select dname,  nvl(empnum,0),nvl(avgsal,0), nvl(emp.ename,null)
    from emp,dept t1, (select  deptno,avg (nvl(sal,0)) avgsal,min (sal) minsal,count (empno) empnum from emp group by deptno ) t2
    where t1.deptno = t2.deptno(+) and emp.sal(+) = t2.minsal

本博客所有文章除特别声明外,均采用 CC BY-SA 4.0 协议 ,转载请注明出处!

ES6语法基础 Previous
Oracle试题3 Next