oracl练习题4.docVIP

oracle练习题4 emp员工表(empno员工号/ename员工姓名/job工作/mgr上级编号/hiredate受雇日期/sal薪金/comm佣金/deptno部门编号) dept部门表(deptno部门编号/dname部门名称/loc地点) /*1、选择在部门30中员工的所有信息*/ 3.select * from scott.emp where deptno = 30 4./*2、列出职位为（MANAGER）的员工的编号，姓名 */ 5.select empno, ename from scott.emp where job = MANAGER 6./*3、找出奖金高于工资的员工*/ 7.select * from scott.emp where comm sal 8./*4、找出每个员工奖金和工资的总和 */ 9.select ename, sal + nvl(comm, 0) from scott.emp 10./*5、找出部门10中的经理(MANAGER)和部门20中的普通员工(CLERK) */ 11.select * 12. from scott.emp 13. where deptno = 10 14. and job = MANAGER 15.union 16.select * 17. from scott.emp 18. where job = CLERK 19. and deptno = 20 20./*6、找出部门10中既不是经理也不是普通员工，而且工资大于等于2000的员工 */ 21.select * 22. from scott.emp 23. where job != MANAGER 24. and job != CLERK 25. and sal 2000 26./*7、找出有奖金的员工的不同工作 */ 27.select distinct(job) from scott.emp where comm is not null 28./*8、找出没有奖金或者奖金低于500的员工*/ 29.select * 30. from scott.emp 31. where comm is not null 32. and comm 500 33./*9、显示雇员姓名，根据其服务年限，将最老的雇员排在最前面 */ 34.select ename 35. from scott.emp 36. order by (months_between(sysdate, hiredate) / 12) desc 37. 38. select ename,hiredate from scott.emp order by hiredate 39./*10、找出每个月倒数第三天受雇的员工*/ 40.select * from scott.emp where hiredate = last_day(hiredate) - 2 41./*11、分别用case和decode函数列出员工所在的部门， deptno=10显示部门10, 42. deptno=20显示部门20 43. deptno=30显示部门30 44. deptno=40显示部门40 45. 否则为其他部门*/ 46. select ename, 47. case deptno 48. when 10 then 49. 部门10 50. when 20 then 51. 部门20 52. when 30 then 53. 部门30 54. when 40 then 55. 部门40 56. else 57. 其他部门 58. end 工资情况 59. from scott.emp 60. 61. select ename, 62. decode(deptno, 63. 10, 64. 部门10, 65. 20, 66. 部门20, 67.