数据库期中考试试题.doc

/最后三题每题12分，其他每题4分。答案执行成功后，拷贝至对应题目之后，各题目只要求写出所需SQL命令，不需写出执行结果。1. 查询emp表中，ename列第三个字母为A、第五个字母为R的员工的姓名。SQL select ename from emp 2 where ename like _A_R%;未选定行2. 使用to_char函数查询emp表中1981年及1982年入职的人数，相关列为hiredate。1 select count(ename) from emp 2* where to_char(hiredate,yyyy) in(1981 ,1982)SQL /COUNT(ENAME)- 103. 查询每个员工与emp表的最高工资之间的差距。select ename,(select max(sal) from emp)-sal 差距 from emp4. 查询emp表中，哪些员工的补贴(comm列)比工资还高，若补贴为null，则视其值为0。select ename,sal,nvl(comm,0) from empwhere commsal5. 查询emp表中分别是哪些员工的工资高于其所在部门的平均工资。select ename from empwhere salall(select avg(sal) from emp group by deptno)/6. 查询emp表中，哪个部门的最高工资与最低工资的差距最大，要求列出满足条件的部门名称(而不是列出部门编号)。 1 select max(a.max_sal-b.min_sal) from 2 ( 3 select max(sal) max_sal,deptno from emp group by deptno 4 )a 5 ,( 6 select min(sal) min_sal,deptno from emp group by deptno 7 )b 8* where a.deptno=b.deptnoSQL /MAX(A.MAX_SAL-B.MIN_SAL)- 37007. 查询10号部门与30号部门平均工资的差距。select (select avg(sal) from emp group by deptno having deptno=10)-(select avg(sal) from emp group by deptno having deptno=30) as 工资差from dual/8. 查询emp表中，超过其所在部门的平均工资的员工姓名。9. 查询每个员工的姓名及其所在的部门地址(即dept表的loc字段)。SQL select e.ename,d.loc from emp e,dept d 2 where e.deptno=d.deptno;10. 用自连接查询每个员工的姓名及其经理的工资与补贴之和，若补贴为空，则视其值为0。 1 select e1.ename,e1.sal+m ,nvl(m,0)from emp e1,emp e2 2* where e1.mgr=e2.empnoSQL /ENAME E1.SAL+E1.COMM NVL(E1.COMM,0)- - -SMITH 0ALLEN 1900 300WARD 1750 500JONES 0MARTIN 2650 1400BLAKE 0CLARK 0TURNER 1500 0JAMES 0FORD 0MILLER 011. 把emp表中，低于平均工资的员工的工资增长20%。update emp set sal=sal*1.2 where sal update emp set comm=500 where comm is null;13. 在emp表中，如果员工的工资比其经理的工资高，则把其工资降低10%。 1 update emp set sal=sal*0.9 2 where ename=(select e1.ename from emp e1,emp e2 where e1.mgr=e2.empno 3* and e1.sale2.sal)SQL /已更新 1 行。14. 查询数据库中哪些表空间的以分配空间占总空间的百分比高于于80%。 1 select a.tablespace_name 2 from 3 ( 4 select tablespace_name,sum(bytes) sum_bytes 5 from dba_free_space 6 group by tablespace_name 7 ) a, 8 ( 9 select tablespace_name,sum(bytes) sum_bytes 10 from dba_data_files 11 group by tablespace_name 12 ) b 13 where a.tablespace_name=b.tablespace_name 14* and (b.sum_bytes-a.sum_bytes)/b.sum_bytes0.8 15 /TABLESPACE_NAME-SYSAUXSYSTEM15. 以system用户查询scott的emp表的数据分布在哪些数据文件上，给出文件名称即可。 1 select file_name,file_id from dba_data_files where 2 file_id in 3 ( 4 select distinct file_id from dba_extents 5 where segment_name=EMP 6* )SQLSQL /FILE_NAME FILE_ID- -E:APPMINEORADATAORCLUSERS01.DBF 4E:APPMINEORADATAORCLSYSTEM01.DBF 116. 查询emp表在哪个列上附加了主键约束，要求给出约束名称、列名、约束类型。SQL col column_name for a10SQL col constraint_name for a10SQL col empno for a10SQL select cols.column_name,cons.constraint_name,cons.constraint_type from 2 dba_constraints cons,dba_cons_columns cols 3 where cons.table_name=cols.table_name and cons.owner=cols.owner 4 and cons.owner=SCOTT and cons.table_name=EMP 5 and cons.constraint_type=P 6 /COLUMN_NAM CONSTRAINT C- - -EMPNO PK_EMP PDEPTNO PK_EMP P17. 写出外键约束的作用，然后设计实验过程，验证外键约束的作用，写出每个步骤及所需的SQL命令。外键约束，列值要匹配于其指向的主表的相应列值。SQL create table p 2 ( 3 a int, 4 b int, 5 constraint pk_a primary key(a) 6 ) 7 /表已创建。create table c ( x int, y int, constraint fk_c foreign key (x) references p(a) )C表上的x列的值要匹配于p表中a列的值，而不能超出其范围。C表作为子表，P为父表，外键对子表的限制主要体现在insert和update的操作上。先给p表增加两行值SQL insert into p values(1,10);已创建 1 行。SQL insert into p values(2,20);已创建 1 行。这时候如果给c表的x列增加值，而值没有匹配于p表中a列的值，就不能创建成功SQL insert into c values(3,20);insert into c values(3,20)*第 1 行出现错误:ORA-02291: 违反完整约束条件 (SCOTT.FK_C) - 未找到父项关键字匹配于p表中a列的值就可以添加成功SQL insert into c values(2,20);已创建 1 行。修改x值也不行，应该此时x值已经引用了p表a列的值SQL update c set x=200 where y=20;update c set x=200 where y=20*第 1 行出现错误:ORA-02291: 违反完整约束条件 (SCOTT.FK_C) - 未找到父项关键字设置x值为null就可以SQL update c set x=null where y=20;已更新 1 行。外键对父表的限制主要体现在delete、update和drop的操作上。SQL delete from p where a=2;delete from p where a=2*第 1 行出现错误:ORA-02292: 违反完整约束条件 (SCOTT.FK_C) - 已找到子记录SQL update p set a=3 where b=20;update p set a=3 where b=20*第 1 行出现错误:ORA-02292: 违反完整约束条件 (SCOTT.FK_C) - 已找到子记录SQL drop table p 2 /drop table p *第 1 行出现错误:ORA-02449: 表中的唯一/主键被外键引用18. 设计实验过程，验证对表的某个列附加唯一约束时，Oracle是否会自动在此列上创建索引，写出每个步骤所需的SQL命令。提示：可以使用user_ind_columns查询关于索引和其所在列的信息。19. 除了不附加条件的delete操作可以清空表中的数据外，truncate table操作也可以清空表中的记录，如清空emp表的记录，可以执行： truncate table emp，但与delete操作不同，truncate table操作会释放表所占用的空间。请设计实验过程，验证truncate table操作会释放表的空间，写出每个步骤所需的SQL命令。先创建一个表，然后分析计算统计表的使用情况，查询t表占用的数据块数量，分配给t表但从未使用过的数据块数量。SQL create table t 2 as 3 select * from all_objects 4 /表已创建。SQL analyze table t compute statistics;表已分析。SQL select table_name,blocks,empty_blocks from user_tables 2 where table_name=T;TABLE_NAME BLOCKS EMPTY_BLOCKS- - -T 825 71此时执行truncate操作SQL truncate table t;表被截断。再执行查询表的使用空间，还是上述结果，是因为还没进行刷新统计。SQL select table_name,blocks,empty_blocks from user_tables 2 where table_name=T;TABLE_NAME BLOCKS