ORACLE试题(2011年信息管理与信息系统).doc

ORACLE 试题 50 分 一 判断题 一 判断题 9 分 每小题分 每小题 1 分 分 1 SGA 是针对某一服务器进程而保留的内存区域 它是不可以共享的 对对 2 用户进程可以直接操纵数据库 错错 3 Order by 子句仅对检索数据的显示有影响 并不改变表中行的内部顺序 对对 4 从一个表中删除元组时 delete 和 drop table 命令可以起到同样的效果 对对 5 rowtype 用来保证数据类型和变量的长度与数据库中的数据列相一致 错错 6 与主键不同 惟一键可以是 null 值 错错 7 Delete 可以有选择地删除记录 使用 rollback 语句可以取消这个命令的执行 对对 8 Create table as select 能够创建一个复制表对象 对对 9 使用 having 子句是为了限制 group by 子句返回的行的个数 对对 一 填空 一 填空 4 分 每小题分 每小题 1 分 分 1 数据库系统由 数据库数据库 数据库管理系统数据库管理系统 数据库管理员数据库管理员 用户用户 和和 应用程序应用程序 组成 2 Oracle 10g 产品 数据库组件的安装盘数据库组件的安装盘 Companion CD 客户端客户端 CRS 和和 Gateway 等组成 3 数据库的启动方式有 startupstartup nomountnomount 非安装启动 非安装启动 startupstartup mountmount dbnamedbname 安装启动 安装启动 startupstartup restrictrestrict 约束方式启动 约束方式启动 startupstartup forceforce 强制启动方式 强制启动方式 startupstartup pfile pfile 参数文件名 参数文件名 4 数据库的关闭模式有 shutdownshutdown normanorma 正常方式关闭数据库 正常方式关闭数据库 shutdownshutdown abortabort 直接关闭数据库 直接关闭数据库 shutdownshutdown immediateimmediate 立即方式关闭数据库 立即方式关闭数据库 二 简答题 二 简答题 7 分 每小题分 每小题 1 分 分 1 如何查看已定义表的方案 表空间 存储参数 块空间管理参数等信息 select scheme from table name analyze table emp compute statistics select table name num rows avg row len num rows avg row len from user tables where table name select table name initial extent next extent min extents max extents pct increase from user tables 2 ORACLE 中数据类型 CHAR VARCHAR VARCHAR2 有什么不同 答 答 char n char n 长度为长度为 n n 的定长字符串 的定长字符串 varchar b varchar b 最大长度为最大长度为 n n 的变长字符串 的变长字符串 varchar2 n varchar2 n 变长字符变长字符 型数据类型 最小长度为一个字节 最大长度为型数据类型 最小长度为一个字节 最大长度为 40004000 字节 应用时必须指定它的长度 字节 应用时必须指定它的长度 区别有以下三点 区别有以下三点 1 CHAR 的长度是固定的 而的长度是固定的 而 VARCHAR2 的长度是可以变化的 的长度是可以变化的 比如 存储字符串比如 存储字符串 abc 对于 对于 CHAR 20 表示你存储的字符将占 表示你存储的字符将占 20 个字节个字节 包括包括 17 个空字符个空字符 而同样的 而同样的 VARCHAR2 20 则则 只占用只占用 3 个字节的长度 个字节的长度 20 只是最大值 当你存储的字符小于只是最大值 当你存储的字符小于 20 时 按实际长度存储 时 按实际长度存储 2 CHAR 的效率比的效率比 VARCHAR2 的效率稍高 的效率稍高 3 目前 目前 VARCHAR 是是 VARCHAR2 的同义词 工业标准的的同义词 工业标准的 VARCHAR 类型可以存类型可以存 储空字符串 但是储空字符串 但是 oracle 不这样做 尽管它保留以后这样做的权不这样做 尽管它保留以后这样做的权利 利 Oracle 自己开发了一个数据类型自己开发了一个数据类型 VARCHAR2 这个类型不是一个标准的 这个类型不是一个标准的 VARCHAR 它将在数据库中 它将在数据库中 varchar 列可以存储空字符串的特性改为列可以存储空字符串的特性改为 存储存储 NULL 值 如果你想有向后兼容的能力 值 如果你想有向后兼容的能力 Oracle 建议使用建议使用 VARCHAR2 而不是而不是 VARCHAR 3 什么是 ROWID 有哪几类 如何使用 答 答 ROWIDROWID 是数据库的一个伪列 建立表的时候数据库会自动为每个表建立是数据库的一个伪列 建立表的时候数据库会自动为每个表建立 ROWIDROWID 列列 用来唯一标识一行记用来唯一标识一行记 录 录 OracleOracle 的的 ROWIDROWID 分为两种 物理分为两种 物理 ROWIDROWID 和逻辑和逻辑 ROWIDROWID 它主要记录表内各行的物理地址 以便能够快速定位 它主要记录表内各行的物理地址 以便能够快速定位 并用来唯一标识数据行 并用来唯一标识数据行 OracleOracle 的的 ROWIDROWID 用来唯一标识表中的一条记录 是这条数据在数据库中存放的物理地用来唯一标识表中的一条记录 是这条数据在数据库中存放的物理地 址 索引组织表使用逻辑址 索引组织表使用逻辑 ROWIDROWID 其他类型的表使用物理 其他类型的表使用物理 ROWIDROWID 4 截断表与删除表有何区别 答 表删除包括表的定义和关联对象 规则 索引 约 触发器 主键 等 答 表删除包括表的定义和关联对象 规则 索引 约 触发器 主键 等 截断表命令则仅仅删除了表中所有的数据行 表的结构和所有的索引仍然继续存在 直到你输入删除表的命截断表命令则仅仅删除了表中所有的数据行 表的结构和所有的索引仍然继续存在 直到你输入删除表的命 令 令 5 对 SQL 语句进行优化的常用策略有那些 答 答 1 1 尽可能地减少多表查询 尽可能地减少多表查询 2 2 以不相关子查询替代相关子查询 以不相关子查询替代相关子查询 3 3 只检索需要的列 只检索需要的列 4 4 用带 用带 ININ 的条件子句等价替换的条件子句等价替换 OROR 子句子句 5 5 根据表出现在 根据表出现在 FROMFROM 中的顺序中的顺序 ORDERED ORDERED 使使 ORACLEORACLE 依此顺序对其连接依此顺序对其连接 6 6 将指定表与嵌套的连接的行源进行连接 将指定表与嵌套的连接的行源进行连接 并把指定表作为内部表并把指定表作为内部表 6 当元组含有空值时 空值是否参与聚集操作 答答 空空 NULL NULL 值表示数值未知 空值不同于空白或零值 没有两个相等的空值 比较两个空值或将空值与值表示数值未知 空值不同于空白或零值 没有两个相等的空值 比较两个空值或将空值与 任何其它数值相比均返回未知 这是因为每个空值均为未知 空值通常表示未知 不可用或将在以后添加的数据 任何其它数值相比均返回未知 这是因为每个空值均为未知 空值通常表示未知 不可用或将在以后添加的数据 组函数忽略空值 组函数忽略空值 7 在什么情况下使用层次查询 怎样将表中存储的具有层次关系的数据 以层次的结构方式查询出来 答 当数据之间的关系是层次关系 如有零部件组成的汽车结构和家族图谱用答 当数据之间的关系是层次关系 如有零部件组成的汽车结构和家族图谱用 oracle10goracle10g 可以很方便的可以很方便的 查询各种层次关系 查询各种层次关系 层次查询的语法如下 层次查询的语法如下 ELECTELECT LEVEL column expr LEVEL column expr FROMFROM tabletable WHERE WHERE condition condition STARTSTART WITHWITH conditioncondition CONNECTCONNECT BYBY PRIOR PRIOR column1column1 column2column2 column1column1 PRIORPRIOR column2 column2 其中伪列其中伪列 LEVELLEVEL 用于返回层次结构的层次 用于返回层次结构的层次 1 1 根行 根行 2 2 第二级行 第二级行 3 3 第三级行 第三级行 STARTSTART WITHWITH 子句用于指定层次查询的根行 子句用于指定层次查询的根行 CONNECTCONNECT BYBY 子句用于执行父行和子行之间的关系 子句用于执行父行和子行之间的关系 当定义父行和子行的关系时 必须使用当定义父行和子行的关系时 必须使用 PRIOR PRIOR 关键字 并且关键字 并且 column1column1 和和 column2column2 对应于父键列或者子键列 对应于父键列或者子键列 当使用层次查询显示层次结构的表行数据时 可以采用从顶向下或者从底向上两种方式显示数据 当使用层次查询显示层次结构的表行数据时 可以采用从顶向下或者从底向上两种方式显示数据 三 操作题 写出相关操作的三 操作题 写出相关操作的 SQL 语句 语句 20 分 每小题分 每小题 5 分 分 1 表的创建表的创建 学生成绩管理项目中每个关系的属性可作以下定义 部门信息 DEPT 部门编号 varchar2 8 PK 部门名称 varchar2 40 部门经理 varchar2 8 地址 varchar2 80 部门人数 number 答 答 Create table dept Dept id varchar2 8 primary key Dept name varchar2 40 Dept manger varchar2 8 Dept ads varchar2 80 Dept num number 5 学生基本信息 STUDENT 部门编号 varchar2 8 FK 学 号 varchar2 12 PK 姓 名 varchar2 20 not null 性 别 varchar2 1 or char 学生类型 varchar2 1 or char 入学日期 date 籍 贯 varchar2 20 身份证号 varchar2 18 详细地址 varchar2 60 出生日期 date 所学专业 varchar2 20 民 族 varchar2 20 电 话 varchar2 20 缴费总计 number 备 注 varchar2 80 答 答 Create table student Dept id varchar2 8 Foreign key dept id references dept dept id Stu num varchar2 12 primary key Stu name varchar2 20 not null Stu gender varchar2 1 Stu type varchar2 1 Stu startdate date Stu native varchar2 20 Stu id varchar2 18 Stu ads varchar2 60 Stu born date Stu major varchar2 20 Stu nation varchar2 20 Stu tel varchar2 20 Stu cost number 5 Stu ps varchar2 80 课程信息 COURSE 课 程 号 varchar2 8 PK 课程名称 varchar2 40 not null 开设年级 varchar2 8 学 时 数 number 答 答 Create table course Course id varchar2 8 primary key Course name varchar2 40 not null Course class varchar2 8 Course time number 5 成绩单 REPORT CARD 学 号 varchar2 12 FK 课 程 号 varchar2 8 FK 学 期 varchar2 8 成 绩 number 5 2 备 注 varchar2 80 答 答 Create table report card Stu num varchar2 12 Foreign key stu num references student stu num Course id varchar2 8 foreign key course id references course course id Term varchar2 8 Mark number 5 2 Card ps varchar2 80 学号 课程号 为 PK 写出完成下列操作的正确的 SQL 语句 1 创建学生成绩管理项目中四个基表 部门信息表 dept 学生基本信息表 student 课程信息表 courses 和 成绩单表 report card 2 修改 courses 表 修改表名为 courses1 Alter table course rename to courses1 增加字段 pcourses no 类型 varchar2 长度为 10 Insert into courses1 Values pcourses no varchar2 10 修改字段 pcourses no 长度为 8 Alter table courses1 modify pcourses no varchar2 8 删除字段 pcourses no Alter table course1 drop pcourses no 3 删除表 STUDENT Drop table student 2 表记录的操作表记录的操作 在 DEPT 表中插入数据 写出 SQL 语句 101 信息工程系 null 校 3 号教学楼 48 102 电子商务系 null 校 4 号教学楼 null 103 管理系 null 校 5 号教学楼 null 201 校长办公室 null 校办公北楼 4 楼 null 202 校人事处 null 校办公北楼 3 楼 null 203 校教务处 null 校办公南楼 2 楼 null 答 答 Insert into dept dept id dept name dept manger dept ads dept num Values 101 信息工程系信息工程系 null 校校 3 号教学楼号教学楼 48 Insert into dept dept id dept name dept manger dept ads dept num Values 102 电子商务系电子商务系 null 校校 4 号教学楼号教学楼 null Insert into dept dept id dept name dept manger dept ads dept num Values 103 管理系管理系 null 校校 5 号教学楼号教学楼 null Insert into dept dept id dept name dept manger dept ads dept num Values 201 校长办公室校长办公室 null 校办公北楼校办公北楼 4 楼楼 null Insert into dept dept id dept name dept manger dept ads dept num Values 202 校人事处校人事处 null 校办公北楼校办公北楼 3 楼楼 null Insert into dept dept id dept name dept manger dept ads dept num Values 203 校教务处校教务处 null 校办公北楼校办公北楼 2 楼楼 null 在 STUDENT 表中插入数据 写出 SQL 语句 101 2002101001 李丽 1 1 sysdate 山西 14240119840101001 山西太原 19840101 信息管理与信息系统 汉 0351 2222222 null null 101 2002101002 王娜 1 1 sysdate 山西 14240119840501001 山西大同 19840501 信息管理与信息系统 汉 0351 3333333 null null 101 2002101003 张大虎 1 1 sysdate 山西 14240119840815021 山西运城 19840815 计算机科学与技术 汉 0731 2222222 null null 答 答 InsertInsert intointo student dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born sstudent dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born s tu major stu nation stu tel stu cost stu ps tu major stu nation stu tel stu cost stu ps Values 101 2002101001 Values 101 2002101001 李丽李丽 1 1 sysdate 1 1 sysdate 山西山西 14240119840101001 14240119840101001 山西太原山西太原 19840101 19840101 信息管信息管 理与信息系统理与信息系统 汉汉 0351 2222222 null null 0351 2222222 null null InsertInsert intointo student dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born sstudent dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born s tu major stu nation stu tel stu cost stu ps tu major stu nation stu tel stu cost stu ps Values 101 2002101002 Values 101 2002101002 王娜王娜 1 1 sysdate 1 1 sysdate 山西山西 14240119840501001 14240119840501001 山西大同山西大同 19840501 19840501 信息管信息管 理与信息系统理与信息系统 汉汉 0351 3333333 null null 0351 3333333 null null InsertInsert intointo student dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born sstudent dept id stu num stu num stu gender stu type stu stardate stu native stu id stu ids stu born s tu major stu nation stu tel stu cost stu ps tu major stu nation stu tel stu cost stu ps Values 101 2002101003 Values 101 2002101003 张大虎张大虎 1 1 sysdate 1 1 sysdate 山西山西 14240119840815021 14240119840815021 山西运城山西运城 19840815 19840815 计算计算 机科学与技术机科学与技术 汉汉 0731 2222222 null null 0731 2222222 null null 在 COURSES 表中插入数据 写出 SQL 语句 101001 数据库系统设计 48 101000 101002 软件需求工程 36 101000 101003 软件工程导论 36 101000 答 答 Insert into courses couse id course name course class course time Values 101001 数据库系统设计数据库系统设计 48 101000 Insert into courses couse id course name course class course time Values 101002 软件需求工程软件需求工程 36 101000 Insert into courses couse id course name course class course time Values 101003 软件工程导论软件工程导论 36 101000 在 report card 表中插入数据 写出 SQL 语句 2002101001 101001 null 88 null 2002101001 101002 null 78 null 2002101001 101003 null 94 null 2002101002 101003 null 92 null 2002101002 101001 null 84 null 2002101003 101002null 74 null 答 答 Insert into report card stu num course id term mark card ps Values 2002101001 101001 null 88 null Insert into report card stu num course id term mark card ps Values 2002101001 101002 null 78 null Insert into report card stu num course id term mark card ps Values 2002101001 101003 null 94 null Insert into report card stu num course id term mark card ps Values 2002101002 101003 null 92 null Insert into report card stu num course id term mark card ps Values 2002101002 101001 null 84 null Insert into report card stu num course id term mark card ps Values 2002101003 101002null 74 null 将课程号为 101002 课程的成绩普提 5 分 写出 SQL 语句 答 答 Update report card Set mark mark 5 Where course id 101002 将 report card 中学期为空的记录全部设置为 20060701 写出 SQL 语句 答 答 Update report card Set term 20060701 Where term is null 删除掉系代号为 101 并且姓名为 张大虎 的学生记录 写出 SQL 语句 答 答 Delete from student Where dept id 101 and stu name 张大虎张大虎 删除 student 表中的全部数据 写出 SQL 语句 答 答 Drop table student 3 表的查询 写出表的查询 写出 SQL 语句 语句 1 简单查询 查询部门信息表 DEPT 的信息 Select from dept 查询部门信息表 DEPT 中部门编号与部门名称 Select dept id dept name From dept 查询学生基本信息表 STUDENT 中学生的学号 姓名和年龄 Select stu num stu name stu age From student 查询部门编号为 201 或 202 的部门部门名称 部门经理 地址 Select dept name dept manger dept ads From dept Where dept id 201 or dept id 202 查询 1984 年出生的学生的学生信息 Select From student Where stu born 1984 查询山西学生的学号 姓名 年龄及所在系 Select stu num stu name stu age stu major From student Where stu native 山西山西 查询学生的学号 姓名 出生日期及所在系 输出结果先按照部门编号升序排序 部门编号相同按照出 生日期降序排序 Select stu num stu name stu born stu major From student Group by dept id Order by dept id desc 查询各院系人数 Select count stu num From student 查询院系人数多于 500 的院系及其人数 Select dept name count stu num From student Where count stu num 500 2 子查询与连接查询 查询信息工程系的学生的学号 姓名 性别 入学日期及籍贯 Select stu num stu name stu gender stu stardate stu native From student dept Where dept name in select dept name From dept Where dept name 信息工程系信息工程系 查询其他系比 101 系某一学生年龄小的学生学号 姓名 性别 入学日期和年龄 Select stu num stu name stu gender stu stardate stu age From student Where stu age any select stu age From student Where dept id 101 查询其他系比 101 系所有学生年龄都要小的学生学号 姓名 性别 入学日期和年龄 Select stu num stu name stu gender stu stardate stu age From student Where stu ageSELECT student no 101 FROM STUDENT WHERE DEPT NO 101 UNION SELECT student no 101001 FROM report card WHERE course no 101001 INTERSECT SQL SELECT student no 101 FROM STUDENT WHERE DEPT NO 101 INTERSECT SELECT student no 101001 FROM report card WHERE course no 101001 4 数据库的复杂查询数据库的复杂查询 本实验以 SCOTT 方案中的表为例来实现 1 横向小计与纵向小计查询 查询雇员表 emp 统计不同部门 deptno 不同岗位 job 的平均工资 同时生成横向小计统计 部门的平均 工资 SELECT deptno job avg sal from scott emp GROUP BY ROLLUP deptno job 在 基础上生成纵向小计 统计岗位的平均工资 SELECT deptno job avg sal from scott emp GROUP BY CUBE deptno job 有些统计结果只会用到 deptno 列 有些统计结过只会用到 job 列 查看每项统计结果到底使用了哪些列 2 外连接查询 查询部门名称以及该部门中的人员姓名 有些部门没有雇员 select dept name ename from scott emp scott dept where e dept id d dept id 查询数据库中是否存在部门没有雇员 或者雇员不曾分配到各部门的情况 select dept name ename from scott emp scott dept where e dept id d dept id 3 多列子查询 查询与 SMITH 部门和岗位完全相同的所有雇员信息 Select From scott emp Where job select job From scott dept Where dept name SMITH 显示工资 补助与 SCOTT 完全一致的所有雇员名 工资 补助 Select ename sal supply From scott emp Where dept name in Select dept name From scott dept Where dept name SCOTT 4 层次查询 通过层次查询显示除 CLERK 岗位外其他雇员之间的上下级关系 select ename job from scott emp start with mgr is null without dept name clerk connect by mgr prior empno 通过层次查询 MARTIN 的所有领导姓名 SELECT LPAD 3 LEVEL 1 ename ename LPAD 3 LEVEL 1 job job From scott emp START WITH ename MARTIN CONNECT BY empno PRIOR mgr 查询课程表中各课程之间的先修关系 Selectlpad Selectlpad 3 level 1 course no 3 level 1 course no 课程编号课程编号 lpad lpad 3 level 1 course name 3 level 1 course name 课程名课程名 称称 fromfrom coursescourses STAR