1、1Oracle 基本操作练习题使用表：员工表(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)部门地址)说明：增删改
2、较简单，这些练习都是针对数据查询，查询主要用到函数、运算符、模糊查询、排序、分组、多变关联、子查询、分页查询等。建表脚本(根据需要使用)： 建 表 脚 本 .txt2练习题：1.找出奖金高于薪水 60%的员工信息。 SELECT * FROM emp WHERE commsal*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
3、,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 SAL2000);5.列出各种工作的最低工资。SELECT JOB,MIN(sal) FROM emp GROUP BY JOB;36.列出各
4、个部门的 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 COMM300;9.找出各月最后一天受雇的所有员工。 SELECT * FROM EMP WHERE HIREDATE=LAST_DAY(HIREDA
5、TE); 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%;413.显示所有员工的姓名的前三个字符。 select substr(ename,0,3) from emp;14.显所有员工的姓名，用 a替换所有姓名中的 A。 select replace(ename,A,a) from e
6、mp;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.显示员工姓
7、名，根据其服务年限，将最老的员工排在最前面。 select ename from emp order by hiredate;19.列出按年薪排序的所有员工的年薪。 select (sal+nvl(comm,0)*12 as 年薪 from emp order by sal;520.列出最低薪水大于 1500的各种工作。select job from emp group by job having min(sal)1500;21.显示所有员工的姓名和加入公司的年份和月份，将员工入职年月从低到高排序。 Select ename,to_char(hiredate,yyyy) as year,to_
8、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)
9、as 天数 from emp;25.列出有员工的部门，同时列出那些没有员工的部门。 26.列出至少有一个员工的所有部门。6select 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.列出所有员工的姓名及其直接上级的姓名。sel
10、ect 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.hiredatet1.hiredate;31
11、.列出部门名称和这些部门的员工信息，同时列出没有员工的部门。select e.*,d.deptno,d.dname from dept d left join emp e on 7e.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=(sel
12、ect 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中任何一个员工的薪水)8select en
13、ame,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 平
14、均服务年限 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 d
15、eptno) e where d.deptno=e.deptno;941.列出薪水水平处于第四位的员工。 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 s
16、al desc) as r from emp) where r=5;45.查找 EMP表部门 30中薪水第 3的员工 。select empno from (select empno,rank() over(order by sal desc) as r from emp where deptno=30) where r=3;46.查找 EMP表中每部门薪水前 3的员工。select * from (select e.*,row_number() over(partition by 10e.deptno order by e.sal desc) 排名 from emp e) where 排名=3;
