史上最全Oracle数据库基本操作练习题

1 Oracle 基本操作练习题基本操作练习题 使用表 使用表 员工表 emp empnoNUMBER 4 notnull 员工编号 表示唯一 enameVARCHAR2 10 员工姓名 job VARCHAR2 9 员工工作职位 mgrNUMBER 4 员工上级领导编号 hiredateDATE 员工入职日期 salNUMBER 7 2 员工薪水 commNUMBER 7 2 员工奖金 deptnoNUMBER 2 员工部门编号 部门表 dept deptnoNUMBER 2 notnull 部门编号 dnameVARCHAR2 14 部门名称 locVARCHAR2 13 部门地址 说明 增删改较简单 这些练习都是针对数据查询 查询主要用到函数 运算符 模糊查 询 排序 分组 多变关联 子查询 分页查询等 建表脚本 根据需要使用 建表脚本 txt 2 练习题 练习题 1 找出奖金高于薪水 60 的员工信息 SELECT FROM emp WHERE comm sal 0 6 2 找出部门 10 中所有经理 MANAGER 和部门 20 中所有办事员 CLERK 的详细资料 SELECT FROM emp WHERE JOB MANAGER AND DEPTNO 10 OR JOB CLERK AND DEPTNO 20 3 统计各部门的薪水总和 SELECT deptno SUM sal FROM emp GROUP BY deptno 4 找出部门 10 中所有理 MANAGER 部门 20 中所有办事员 CLERK 以及既不是经理又不是办事员但其薪水大于或等 2000 的 所有员工的详细资料 SELECT FROM emp WHERE JOB MANAGER AND DEPTNO 10 OR JOB CLERK AND DEPTNO 20 OR JOB NOT IN MANAGER CLERK AND SAL 2000 5 列出各种工作的最低工资 SELECT JOB MIN sal FROM emp GROUP BY JOB 3 6 列出各个部门的 MANAGER 经理 的最低薪水 SELECT deptno MIN sal FROM emp WHERE job MANAGER GROUP BY deptno 7 找出收取奖金的员工的不同工作 SELECT DISTINCT JOB FROM EMP WHERE COMM IS NOT NULL 8 找出不收取奖金或收取的奖金低于 300 的员工 SELECT FROM EMP WHERE COMM IS NULL OR COMM 300 9 找出各月最后一天受雇的所有员工 SELECT FROM EMP WHERE HIREDATE LAST DAY HIREDATE 10 显示所有员工的姓名 首字母大写 select initcap ename from emp 11 显示正好为 5 个字符的员工的姓名 select ename from emp where length ename 5 12 显示不带有 R 的员工姓名 select ename from emp where ename not like R 4 13 显示所有员工的姓名的前三个字符 select substr ename 0 3 from emp 14 显所有员工的姓名 用 a 替换所有姓名中的 A select replace ename A a from emp 15 显示所有员工的姓名以及工作 10 年后的日期 select ename ADD MONTHS hiredate 10 12 from emp 16 显示员工的详细资料 按姓名排序 select from emp order by ename 17 列出从事同一种工作但属于不同部门的员工的不同组合 select distinct t1 ename t1 job t1 deptno from emp t1 select job deptno from emp t2 where t1 job t2 job and t1 deptno t2 deptno 18 显示员工姓名 根据其服务年限 将最老的员工排在最前面 select ename from emp order by hiredate 19 列出按年薪排序的所有员工的年薪 select sal nvl comm 0 12 as 年薪 from emp order by sal 5 20 列出最低薪水大于 1500 的各种工作 select job from emp group by job having min sal 1500 21 显示所有员工的姓名和加入公司的年份和月份 将员工入职年月 从低到高排序 Select ename to char hiredate yyyy as year to char hiredate mm as months from emp order by months 22 显示在一个月为 30 天的情况下所有员工的日薪水 取整 select trunc nvl comm 0 sal 30 0 as 日薪 from emp 解释 nvl comm 0 如果是 null 则转换为 0 如果不是 null 这保 持原值 23 找出在 任何年份的 2 月受聘的所有员工 select from emp where to char hiredate mm 2 24 对于每个员工 显示其加入公司的天数 select sysdate hiredate as 天数 from emp 25 列出有员工的部门 同时列出那些没有员工的部门 26 列出至少有一个员工的所有部门 6 select deptno from emp group by deptno having count empno 1 27 列出所有员工的员工姓名 部门名称和薪水 select ename deptno sal from emp 28 列出薪水比 SMITH 多的所有员工 select ename sal from emp where sal select sal from emp where ename SMITH 29 列出所有员工的姓名及其直接上级的姓名 select t1 empno t1 ename t1 mgr t2 ename from emp t1 left join emp t2 on t1 mgr t2 empno 30 列出受雇日期早于其直接上级的所有员工 select t1 empno as 员工编号 t1 ename as 职员 t1 mgr as 经理 编号 t2 ename as 经理 t1 hiredate as 职员入职时间 t2 hiredate as 经理入职时间 from emp t1 left join emp t2 on t1 mgr t2 empno where t2 hiredate t1 hiredate 31 列出部门名称和这些部门的员工信息 同时列出没有员工的部门 select e d deptno d dname from dept d left join emp e on 7 e deptno d deptno 32 列出所有 CLERK 办事员 的姓名及其部门名称 select e ename d dname from emp e dept d where d deptno e deptno and e job CLERK 33 列出在部门 SALES 销售部 工作的员工的姓名 假定不知 道销售部的部门编号 select ename from emp where deptno select deptno from dept where dname SALES 34 列出薪水高于公司平均薪水的所有员工 select ename sal from emp where sal select avg sal from emp 35 列出与 SCOTT 从事相同工作的所有员工 select ename job from emp where job select job from emp where ename SCOTT 36 列出薪水等于部门 30 中员工的薪水的所有员工的姓名和薪水 个人理解为查询其它部门薪水刚好等于部门 30 中任何一个员工的 薪水 8 select ename sal from emp where sal in select sal from emp where deptno 30 and deptno 30 37 列出薪水高于在部门 30 工作的所有员工的薪水的员工姓名和薪 水 select sal ename from emp where sal select max sal from emp where deptno 30 38 列出在每个部门工作的员工数量 平均工资和平均服务期限 select count deptno as 部门员工数量 avg sal as 平均工资 avg sysdate hiredate as 平均服务年限 from emp group by deptno 39 列出从事同一种工作但属于不同部门的员工的一种组合 select distinct t1 deptno t1 job from emp t1 emp t2 where t1 deptno t2 deptno and t1 job t2 job order by t1 job 40 列出所有部门的详细信息和部门人数 select d deptno d dname d loc e 部门人数 from dept d select count deptno as 部门人数 deptno from emp group by deptno e where d deptno e deptno 9 41 列出薪水水平处于第四位的员工 select ename empno sal 排名 from select ename empno sal dense rank over order by sal desc as 排 名 from emp where 排名 4 42 查找 EMP 表中前 5 条记录 select from emp where rownum10 44 查找 EMP 表中薪水第 5 高的员工 select empno from select empno rank over order by sal desc as r from emp where r 5 45 查找 EMP 表部门 30 中薪水第 3