oracle习题.docx

1、1.选择部门 30 中的所有员工：SELECT * FROM emp WHERE deptno=30;2.列出所有办事员(CLERK)的姓名，编号和部门编号：SELECT ename,empno,deptno FROM emp WHERE UPPER(job)=CLERK;3.列出佣金(comm)高于薪金(sal)的员工：SELECT * FROM emp WHERE commsal;4.找出佣金（comm）高于薪金百分 60 的员工SELECT * FROM emp WHERE comm(sal*0.6);5.找出部门 10 中所有经理(MANAGER) 和部门 20 中所有办事员（CLER

2、K）的详细资料SELECT * FROM emp WHERE (deptno=10 AND job=MANAGER) OR (deptno=20 AND job=CLERK);6.找出部门 10 中所有经理，部门 20 中所有的办事员，既不是经理又不是办事员，但是薪金大于或等于 2000 的所有员工的资料：SELECT * FROM emp WHERE(deptno=10 AND job=MANAGER) OR(deptno=20 AND job=CLERK) OR(job NOT IN(MANAGER,CLERK) AND sal=2000);7.找出收取佣金（comm）的员工的不同工作：D

3、ISTINCT- 消除重复的关键字SELECT DISTINCT job FROM emp WHERE comm IS NOT NULL;8.找出不收取佣金或者佣金小于 100 的员工：SELECT * FROM emp WHERE comm IS NULL OR comm12;11.按照首字母大写的方式显示员工姓名SELECT INITCAP(ename) FROM emp;12.显示正好为 5 个字符的员工的姓名SELECT ename FROM emp WHERE LENGTH(ename)=5;13.显示不带有“R“ 的员工姓名：SELECT ename FROM emp WHERE

4、ename NOT LIKE %R%;14.显示所有员工姓名的前 3 个字符：SELECT SUBSTR(ename,0,3) FROM emp;15.显示所有员工的姓名，并且用“x” 替换替换所有的 “A”；SELECT REPLACE(ename,A,x) FROM emp;16.显示满十年服务年限的员工的姓名和受雇日期：SELECT ename,hiredate FROM emp WHERE MONTHS_BETWEEN(sysdate,hiredate)/12 10;17.显示员工的详细资料，按姓名排序:SELECT * FROM emp ORDER BY ename;18.显示员工的

5、姓名和受雇日期，并根据其服务年限，把资料最老的员工排在第在前面：SELECT ename,hiredate FROM emp ORDER BY hiredate;19.显示所有员工的姓名，工作和薪金，按工作的降序排序，若工作相同则按薪金排序：SELECT ename,job,sal FROM emp ORDER BY job DESC,sal;20.显示所有员工的姓名，加入公司的年份和月份，按受雇日期所在的年排序，若年份相同则讲最早月份的员工排在最前面：使用 TO_CHAR()函数SELECT ename,TO_CHAR(hiredate,yyyy) year,TO_CHAR(hiredate

6、,MM) mon FROM emp ORDER BY year,mon;21.显示在一个月为 30 天的情况所有员工的日薪金，并且忽略余数：ROUND() 四舍五入SELECT ename,ROUND(sal/30) 日薪金 FROM emp;22.找出在（任何年份）的 2 月受聘的所有员工：SELECT * FROM emp WHERE TO_CHAR(hiredate,MM)=2;23.对于每个员工，显示其加入公司的天数：SELECT ROUND(sysdate-hiredate) FROM emp;24.显示姓名字段的任何位置包含“A ”的所有员工姓名：SELECT ename FROM

7、 emp WHERE ename LIKE %A%;25.以年月的方式显示所有员工的服务年限：年：求出总共的月/12 - 产生小数，并不能四舍五入月：对 12 取余 SELECT ename,TRUNC(MONTHS_BETWEEN(sysdate,hiredate)/12) year,TRUNC(MOD(MONTHS_BETWEEN(sysdate,hiredate),12) monFROM emp;/*-复杂查询，子查询，多表关联-*/26.列出至少有三个员工的所有部门和部门信息。!SELECT d.*,ed.count FROM dept d,(SELECT deptno,COUNT(e

8、mpno) count FROM emp GROUP BY deptno HAVING COUNT(empno)3) edWHERE d.deptno=ed.deptno;27.列出薪金比“ALLEN”多的所有员工SELECT sal FROM emp WHERE ename=ALLEN; /子查询SELECT * FROM emp WHERE sal(SELECT sal FROM emp WHERE ename=ALLEN);28.列出所有员工的姓名及其上级的姓名：SELECT e.ename 员工,m.ename 上级FROM emp e,emp mWHERE e.mgr=m.empno

9、(+);由于 KING 并没有上级，所以添加一个(+) 号表示左连接29.列出受雇日期早于直接上级的所有员工的编号，姓名，部门名称SELECT e.ename,e.empno,d.dnameFROM emp e,emp m,dept dWHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.hiredate1500;在按照SELECT e.job,COUNT(empno) FROM emp eWHERE job IN(SELECT job FROM empGROUP BY job HAVING MIN(sal)1500)GROUP BY e.job;33

10、.列出在部门销售部工作的员工姓名，假设不知道销售部的部门编号根据 DEPT 表查询销售部的部门编号(子查询)SELECT deptnoFROM deptWHERE dname=SALES;上述为子查询SELECT ename FROM empWHERE deptno=(SELECT deptnoFROM deptWHERE dname=SALES);34.列出薪金高于工资平均薪金的所有员工，所在部门，上级领导，公司的工资等级。求出平均工资：SELECT AVG(sal) FROM emp;列出薪金高于平均工资的所有雇员信息SELECT * FROM emp WHERE sal(SELECT A

11、VG(sal) FROM emp);和部门表关联，查询所在部门的信息(注意 KING 是没有上级的 注意右连接)SELECT e.*,d.dname,d.loc,m.ename FROM emp e,dept d,emp mWHEREe.mgr=m.empno(+) ANDe.deptno=d.deptno ANDe.sal(SELECT AVG(sal) FROM emp);求出雇员的工资等级SELECT e.*,d.dname,d.loc,m.ename,s.gradeFROM emp e,dept d,emp m,salgrade sWHEREe.mgr=m.empno(+) ANDe.

12、deptno=d.deptno ANDe.sal(SELECT AVG(sal) FROM emp)AND e.sal BETWEEN s.losal AND s.hisal;35.列出和“SCOTT”从事相同工作的所有员工及部门名称：SCOTT 从事的工作SELECT job FROM emp WHERE ename=SCOTT;做子查询SELECT e.*,d.dname FROM emp e,dept dWHERE e.job=(SELECT job FROM emp WHERE ename=SCOTT)AND e.deptno=d.deptno;以上的结果存在 SCOTT，应该去掉SE

13、LECT e.*,d.dname FROM emp e,dept dWHERE e.job=(SELECT job FROM emp WHERE ename=SCOTT)AND e.deptno=d.deptno AND e.ename!=SCOTT;36.列出薪金等于部门 30 中员工薪金的所有员工的姓名和薪金求出部门 30 中的员工薪金SELECT sal FROM emp WHERE deptno=30;子查询SELECT ename,sal FROM empWHERE sal IN (SELECT sal FROM emp WHERE deptno=30)AND deptno!=30;

14、37.列出薪金高于在部门 30 工作的所有员工的薪金的员工姓名和薪金、部门名称在之前的程序进行修改使用ALL ，比最大还大SELECT ename,sal FROM empWHERE sal ALL(SELECT sal FROM emp WHERE deptno=30)AND deptno!=30;再和 dept 关联，求出部门名称SELECT e.ename,e.sal,d.dname FROM emp e,dept dWHERE e.sal ALL(SELECT sal FROM emp WHERE deptno=30)AND e.deptno!=30AND e.deptno=d.dep

15、tno;38.列出每个部门工作的员工数量、平均工资和平均服务期限每个部门工作的员工数量：SELECT d.dname,COUNT(e.empno)FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname;求出平均工资和服务年限SELECT d.dname,COUNT(e.empno),AVG(sal),AVG(MONTHS_BETWEEN(sysdate,hiredate)/12) 年FROM emp e,dept dWHERE e.deptno=d.deptnoGROUP BY d.dname;39.列出所有员工的姓名、部门和工资SEL

16、ECT e.ename,d.dname,e.sal FROM emp e,dept d WHERE e.deptno=d.deptno;40.列出所有部门的相信信息和部门人数列出所有部门的人数SELECT deptno dno,COUNT(empno) cou FROM empGROUP BY deptno;把上表当成临时表：【由于 40 部门没有雇员，所以应该使用 0 表示】SELECT d.*,NVL(ed.cou,0) FROM dept d,(SELECT deptno dno,COUNT(empno) cou FROM empGROUP BY deptno) edWHERE d.deptno=ed.dno(+);41、列出各种工作的最低工资以及从事此工作的雇员姓名：按工作分组求出最低工资