Oracle 模拟考核及答案

十、（15分）Oracle题。

把结果用spool命令存入文件，文件名为学员自己的姓名，路径为d:\

1、（8分）根据Oracle数据库Scott模式下的EMP表和DEPT表，完成下列操作：其中，EMP表结构为EMP（empno,ename,job,mgr,hiredate,sal,comm.,deptno）；dept 表结构为dept(deptno,dname,loc)

(1) 向EMP表插入一条记录，员工号为1111，名字为Oracle，工资为3000，部门号20,

入职日期为2008年5月10日。

（2）查询工资与10号部门某个员工工资相同的员工信息

（3）查询所有员工及其所在部门的信息

（4）将各部门员工的工资修改为该员工所在部门平均工资加1000

2、（7分）使用EXP命令导出Scott用户下的所有数据库对象，存放在文件中，文件名

为：d:\学员姓名

insert into emp (empno,ename,sal,deptno,hiredate) values(1111,'ORACLE',3000,20,'10-5月-2011');

select sal from emp where sal in (select sal from emp where deptno=10);

select ename,emp.deptno, dname from emp, dept where emp.deptno=dept.deptno;

Update emp set sal=1000+(select avg(sal) from emp e where e.deptno=emp.deptno);

declare

avgsal number(7,1);

begin

select avg(sal) into avgsal from emp where deptno=10;

update emp set sal=sal+avgsal where deptno=10;

select avg(sal) into avgsal from emp where deptno=20;

update emp set sal=sal+avgsal where deptno=20;

select avg(sal) into avgsal from emp where deptno=30;

update emp set sal=sal+avgsal where deptno=30;

end;

九、（共15分，每小题3分）Oracle操作题。

emp表的结构：empno(n,4)，ename(c,10)，job(c,10)，mgr(n,4)，hiredate(d)，sal(n,7,2)，comm(n,7,2)，deptno(n,2)。

dept表的结构：deptno(n,2)，dname((c,20)，loc(c,20)。

其中：emp表主键是empno，dept表的主键是deptno。

说明：答题时，表名和字段名要求使用英文。题目中的列与表中字段的对应关系如下：

empno：工号，ename：姓名，job：工种，mgr：领导，hiredate：报到日期，sal：工资，comm：奖金，deptno：部门号，dname：部门名，loc：上班地点。

（1）查找在…NEWYORK‟工作的员工的姓名，部门名，工资和奖金之和并为工资奖金之和起别名为…income‟。(注意：奖金有的为空)

select ename,dname,(sal+nvl(comm,0)) as income

from emp join dept on emp.deptno=dept.deptno where loc='NEW YORK'

（2）为表emp创建索引deptno_job，要求先按部门号升序排列，若部门号相同再按工种降序排列。create index deptno_job on emp (deptno asc,job desc)

（3）修改dept表中dname的数据类型为varchar2（20）。

alter table dept modify dname varchar2(20)

（4）向emp表增加一名员工：工号9001，部门号10，姓名JOHN，其他列任意。insert into emp(empno,deptno,ename) values(9001,10,'john')

（5）把scott用户下的emp表和dept表导出到E盘根目录下，文件名为考号

员工表

以上面两个表为素材进行数据表的创建，删除；数据记录的查询，修改，插入，删除等基本操作

1.选择工资大于3000的员工，将符合条件的记录存放到新表emp3000中

1)create table emp3000 as select * from emp where sal>3000;

2)create table emp10 as select * from emp where deptno=10;

2.将数据表emp3000通过exp命令备份出来，删除数据表emp3000，再用imp命令将

其恢复。

1)启动DOS命令窗口

2)EXP USERID=SCOTT/TIGER FILE=C:\EMP3000.dmp TABLES=EMP3000

ROWS=y 备份数据表

3)Drop table emp3000; 删除数据表

4)IMP USERID=SCOTT/TIGER TABLES=EMP3000 ROWS=Y

FILE=C:\EMP3000 导入数据表3.选择部门编号为10和30的员工

1)Select * from emp where deptno=10 or deptno=20;

2)Select * from emp where deptno in (10,20)

4.将部门编号为30的员工的工资上浮200元

1)Update emp set sal=sal+200 where deptno=30;

5.计算员工的工资+补助，补助没有填写的按0计算。

1)Select ename, sal, comm, sal+nvl(comm,0) from emp;

2)Select ename 名字, sal 工资, comm 补助, sal+nvl(comm,0) “工资+补助”from

emp;

6.按部门进行工资的汇总，并将汇总结果按降序排列。

1)Select deptno, sum(sal) from emp group by deptno having sum(sal)>3000 order by

sum(sal) desc;

2)Select deptno 部门, sum(sal) 工资总计from emp group by deptno having

sum(sal)>3000 order by sum(sal) desc;

7.将补助没有填写的员工的补助更新为其工资的10%