最新Oracle精选面试题附答案及分析资料

1、精品文档Oracle精选面试题1. 显示10号部门的所有经理(MANAGER和20号部门的所有职员(CLERK的详细信息答案：Select * from emp where dept no =10 and job=ANAGERor dept no=20 and job=clerk;select * from emp where dept no = 10 and job = MANAGER or dept no = 20 and job =CLERK;2. 显示姓名中没有L字的员工的详细信息或含有SM字的员工信息答案：Select * from emp where en ame note lik

2、e %L%or en ame like %SM%;select * from emp where en ame not like %L% or en ame like %SM%;3. 显示各个部门经理(MANAGER的工资答案：select deptno,emname, salaryfrom emp_wqq where job= MANAGER;4. 显示佣金(COMM)收入比工资(SAL高的员工的详细信息答案：select * from emp where comm sal;5. 把hiredate列看做是员工的生日，求本月过生日的员工答案：select * from emp where t

3、o_char(hiredate, mm) = to_char(sysdate , mm);6. 把hiredate列看做是员工的生日，求下月过生日的员工答案：select * from emp where to_char(hiredate, mm) = to_char(add_ mon ths(sysdate,1) , mm);7. 求1982年入职的员工答案：select * from emp where to_char(hiredate,yyyy) = 1982;8. 求1981年下半年入职的员工答案：select * from emp where hiredatebetween to_d

4、ate(1981-7-1,yyyy-mm-dd) and to_date(1982-1-1,yyyy-mm-dd) - 1;9. 求1981年各个月入职的的员工个数答案：select count(*), to_char(trunc(hiredate,month),yyyy-mm)from emp where to_char(hiredate,yyyy)=1981group by trunc(hiredate,month)order by trunc(hiredate,month);10. 查询各个部门的平均工资答案：select deptno,avg(sal) from emp group b

5、y deptno;11. 显示各种职位的最低工资答案：select job,min(sal) from emp group by job;12. 按照入职日期由新到旧排列员工信息 答案： select * from emp order by hiredate desc;13. 查询员工的基本信息，附加其上级的姓名 答案：select e.*, e2.ename from emp e, emp e2 where e.mgr = e2.empno;14. 显示工资比ALLEN高的所有员工的姓名和工资 答案：select * from emp where sal (select sal from e

6、mp where ename=ALLEN);15. 显示与SCOTT从事相同工作的员工的详细信息 答案：select * from emp where job = (select * from emp where ename=SCOTT);16. 显示销售部(SALES员工的姓名 答案：select ename from emp e, dept d where e.deptno = d.deptno and d.dname=SALES;17. 显示与30号部门MARTIN员工工资相同的员工的姓名和工资 答案：select ename, sal from emp where sal = (sel

7、ect sal from emp where deptno=30 and ename=MARTIN);18. 查询所有工资高于平均工资 (平均工资包括所有员工 )的销售人员 (SALESMAN)精品文档精品文档答案：select * from emp where job=SALESMAN and sal (select avg(sal) from emp);19. 显示所有职员的姓名及其所在部门的名称和工资 答案： select ename, job, dname from emp e, dept d where e.deptno = d.deptno;20. 查询在研发部(RESEARCH)

8、作员工的编号，姓名，工作部门，工作所在地 答案：select empno,ename,dname,loc from emp e, dept dwhere e.deptno = d.deptno and danme=RESEARCH;21. 查询各个部门的名称和员工人数答案：select * from (select count(*) c, deptno from emp group by deptno) einner join dept d on e.deptno = d.deptno;22. 查询各个职位员工工资大于平均工资 (平均工资包括所有员工 )的人数和员工职位 答案：select j

9、ob, count(*) from emp where sal (select avg(sal) from emp) group by job;23. 查询工资相同的员工的工资和姓名答案：select * from emp e where (select count(*) from emp where sal = e.sal group by sal) 1;24. 查询工资最高的 3 名员工信息 答案：select * from (select * from emp order by sal desc) where rownum e.sal)+1 rank from emp e order b

10、y rank;26. 求入职日期相同的 (年月日相同 )的员工 答案：select * from emp e where (select count(*) from emp where e.hiredate=hiredate)1;27. 查询每个部门的最高工资答案：精品文档select deptno, max(sal) maxsal from emp group by deptno order by deptno;28. 查询每个部门 ,每种职位的最高工资 答案： select deptno, job, max(sal) from emp group by deptno, job order

11、by deptno, job;29. 查询每个员工的信息及工资级别 答案： select e.*, sg.grade from emp e, salgrade sg where sal between losal and hisal;30. 查询工资最高的第 6-10 名员工 答案： select * from ( select e.*,rownum rn from (select * from emp order by sal desc) e where rownum 5;31. 查询各部门工资最高的员工信息答案：select * from emp e where e.sal = (sele

12、ct max(sal) from emp where (deptno = e.deptno);32. 查询每个部门工资最高的前 2 名员工 答案： select * from emp e where (select count(*) from emp where sal e.sal and e.deptno = deptno) 2;34. 查询所有大于本部门平均工资的员工信息 答案： select * from emp e where sal (select avg(sal) from emp where (deptno = e.deptno) order by deptno;精品文档精品文档

13、35. 查询平均工资最高的部门信息答案：select d.*, avgsal from dept d, (select avg(sal) avgsal, deptno from emp group by deptno) sewhere avgsal = (select max(avg(sal) from emp group by deptno) and d.deptno = se.deptno;36. 查询大于各部门总工资的平均值的部门信息答案：select d.*,sumsal from dept d, (select sum(sal) sumsal, deptno from emp gro

14、up by deptno) se where sumsal (select avg(sum(sal) from emp group by deptno) and se.deptno = d.deptno;37. 查询大于各部门总工资的平均值的部门下的员工信息答案：select e.*,sumsal from emp e, (select sum(sal) sumsal, deptno from emp group by deptno) se where sumsal (select avg(sum(sal) from emp group by deptno) and se.deptno = e

15、.deptno;38. 查询没有员工的部门信息答案：select d.* from dept d left join emp e on (e.deptno = d.deptno) where empno is null;39. 查询当前月有多少天答案：select trunc(add_months(sysdate,1),month) - trunc(sysdate,month) from dual;40. 列出最低薪金大于 1500 的各种工作及此从事此工作的全部雇员人数答案：SELECT job,COUNT(empno)FROM empGROUP BY job HAVING MIN(sal)

16、1500 ;41. 列出薪金高于公司平均薪金的所有员工，所在部门，上级领导，公司的工资等级答案：SELECT e.empno,e.ename,d.dname,m.ename,s.gradeFROM emp e,dept d,emp m,salgrade sWHERE sal(SELECT AVG(sal) FROM emp) AND e.mgr=m.empno AND d.deptno=e.deptno(+) AND e.sal BETWEEN s.losal AND s.hisal ;42. 精品文档精品文档43. 列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称答案

17、：SELECT e.ename,e.sal,d.dname FROM emp e,dept dWHERE sal ALL (SELECT sal FROM emp WHERE deptno=30) AND e.deptno=d.deptno;44. 列出所有部门的详细信息和部门人数答案：SELECT d.dname,d.loc,dt.countFROM dept d,(SELECT deptno,COUNT(*) count FROM emp GROUP BY deptno) dtWHERE d.deptno=dt.deptno ;45. 显示非销售人员工作名称以及从事同一工作雇员的月工资的总和， 并且要满足从事同一工作的雇 员的月工资合计大于 $5000，输出结果按月工资的合计升序排列答案：SELECT job,SUM(sal) sumFROM empWHERE jobSALESMANGROUP BY job HAVING sum5000ORDER BY sum ;46. 客户表 a(id name address登陆流水表 b(id time)购物流水表 c(id time productid product num)1.求每个客户的最新登陆时间time,姓名name,客户id?答案：select a.id,,d.time as timef