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) frm 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%
1)Update emp set comm=sal*0.1 where comm. Is null;
8.加入一个新员工William，部门表号=10，编号=7980，雇佣日期=2011年11月1日。

1)Insert into emp (ename, deptno, empno, hiredate) values ('William', 10, 7980, '1-11月
-2011');
9.将部门编号为10的员工记录删除。

1)Delete from emp where deptno=10;
10.通过联合查询察看员工的编号，名字，工作和部门名称。

1)Select empno, ename, job, dname from emp, dept where emp.deptno=dept.deptno;
2)SELECT empno, ename, job, dname FROM emp JOIN dept ON
emp.deptno=dept.deptno;。