1. 查询emp和dept表,产生笛卡尔积(多表行相乘)
    select emp.*,dept.* from emp,dept

  2. 加where条件过滤查询emp和dept表产生的笛卡尔积
    select emp.*,dept.* from emp,dept where emp.deptno = dept.deptno

  3. 查询emp和dept表,产生笛卡尔积(多表行相乘),并为表取别名
    select t1.*,t2.* from emp t1,dept t2

  4. 查询雇员姓名,所在部门编号和名称
    select emp.ename,emp.deptno,dept.dname from emp,dept where emp.deptno = dept.deptno

  5. 查询所有雇员姓名,工作,领导的姓名
    select t1.ename,t1.job,t2.ename from emp t1,emp t2 where t1.mgr = t2.empno(+)

  6. 查询雇员姓名,工作,领导姓名及部门名称
    select t1.ename,t1.job,t2.ename,dname from emp t1,emp t2,dept where t1.mgr = t2.empno(+) and t1.deptno=dept .deptno

  7. 查询雇员姓名,工作,工资及工资等级
    select ename,job,sal,grade from emp,salgrade where sal between losal and hisal

  8. 查询雇员姓名,工作,工资及工资等级,要求工资等级显示为A B C D E
    select ename,job,sal,decode(grade,1,'A',2,'B',3,'C',4,'D',5,'E') from emp,salgrade where sal between losal and hisal

  9. 查询雇员姓名,年薪(薪水+奖金),按年薪从高到低排序
    select ename,(sal+nvl(comm,0))*12 from emp order by nvl((sal+nvl(comm,0))*12,0) desc

  10. 查询每个部门中工资最高的雇员姓名,工作,工资,部门名称,最后按工资从高到低排序,工资相同的情况下按姓名排升序

    select ename,job,sal,dname 
    from emp t1,dept t2,(select max(sal) maxsal from emp t1,dept t2 where t1.deptno=t2.deptno group by t1.deptno ) t3 
    where t1.deptno=t2.deptno and t1.sal=t3.maxsal order by sal desc,ename
  11. 查询每个部门的部门编号和雇员数量

    select dept.deptno,nvl(emp.empnum,0)
    from dept,(select count(empno) empnum,deptno from emp group by deptno) emp
    where dept.deptno=emp.deptno(+)
  12. 出每个部门的部门名和平均工资(保留2位小数,截断)

    select dname,nvl(emp.avgsal,0)
    from dept,(select trunc(avg(sal),2) avgsal,deptno from emp group by deptno) emp
    where dept.deptno=emp.deptno(+)
  13. 按部门分组,并显示部门的名称,以及每个部门的员工数

    select dname,nvl(emp.empnum,0)
    from dept,(select count(empno) empnum,deptno from emp group by deptno) emp
    where dept.deptno=emp.deptno(+)
  14. 要求显示平均工资大于2000的部门编号和平均工资(保留2位小数,截断)

    select dept.deptno,emp.avgsal
    from dept,(select trunc(avg(sal),2) avgsal,deptno from emp group by deptno) emp
    where dept.deptno=emp.deptno and emp.avgsal>2000

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

Oracle试题4 Previous
Oracle试题2 Next