Oracle实验20120209

实验１SQL*Plus的使用和基本SELECT语句
实验目的
通过该实验使学员掌握简单查询语句的基本使用方法以及别名的指定方法，能够创建和执行SQL脚本。

实验时间
20分钟
实验要求
（１）掌握SQL*Plus的基本使用
（２）掌握SELECT语句的简单用法。

实验内容
（１）登录到SQL*Plus，初始用户名为：SCOTT，口令为TIGER。

（２）分别显示EMP、DETP、SALGRADE表结构，并察看表中的数据。

（３）创建一个查询显示每个雇员的姓名、工作、受雇日期及雇员编号，并且要将雇员编号作为第一列显示。

最后将该SQL语句保存成文件p1q1.sql，并执行该脚本文件。

（４）将脚本文件p1q1.sql调取到SQL缓冲区，并将列标题设置为Emp #，Employee，Job及Hire Date。

将本次实验保存成exper21.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）SQL*Plus命令是用于访问数据库的吗？
（２）下面的SELECT语句能成功执行吗？
（３）SQL>SELECT ename，job，sal Salary
（４）２FROM emp;
（５）下面的语句有3处错误，请纠正它们。

（６）SQL>SELECT empno，ename
（７）２Salary x 12 ANNUAL SALARY
（８）３FROM emp；
（９）创建一个查询从EMP表中检索出不重复的工作名称。

（１０）用一个逗号和一个空格将姓名和工作连接后作为一个整体输出，显示的列标题为Employee and Title。

（１１）创建一个查询，用逗号将表中所有字段的信息连接作为一个整体输出，输出的列标题为THE_OUTPUT。

（１２）将本次思考练习题的代码保存成exercise1.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验２使用WHERE和ORDER BY子句
实验目的
通过该实验使学员熟练掌握条件查询和排序子句的使用方法及技巧。

实验时间
20分钟
实验要求
（１）掌握WHERE子句的用法。

（２）掌握ORDER BY子句的用法。

实验内容
（１）创建一个查询显示工资大于2850美元的雇员的姓名及工资。

（２）创建一个查询显示工资不在1500到2850美元之间的雇员的姓名及工资。

（３）创建一个查询显示在10和30部门工作并且工资大于1500美元的雇员的姓名和工资，列标题显示为Employee和Monthly Salary。

（４）创建一个查询显示奖金比工资多10％以上的雇员的姓名、工资及奖金。

将本次实验保存成exper2.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）创建一个查询显示雇员编号为7566的雇员的姓名和部门编号。

（２）显示受雇时间在February 20，1981和May 1，1981之间的雇员的姓名、工资、及受雇时间，并以受雇时间升序排列。

（３）显示在10号和30号部门工作的雇员的姓名及其部门编号，并以字母顺序排列。

（４）显示所有受雇于1982年的雇员的姓名和受雇时间。

（５）显示没有上级管理员的雇员的姓名及其工作。

（６）显示能挣得奖金的雇员的姓名、工资、奖金，并以工资和奖金降序排列。

（７）显示姓名中第三个字母为A的雇员的姓名。

（８）显示姓名中两次出现字母L并且在30部门工作或者其管理员编号是7782的雇员的姓名。

（９）显示工作为Clerk或Analyst并且工资不等于$1000、$3000、$5000的雇员的姓名、工资及工资。

将本次思考练习题的代码保存成exercise2.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验３在SELECT语句中使用单行函数
实验目的
通过本实验使学员掌握单行函数的使用方法，重点掌握数值、字符、日期函数。

实验时间
20分钟
实验要求
（１）掌握数值函数的用法。

（２）掌握字符函数的用法。

（３）掌握日期函数的用法。

实验内容
（１）创建一个查询显示雇员的编号、姓名、工资以及工资增长15％后的整数值（以New Salary作为列标题）。

（２）创建一个查询增加一个列Increase显示new salary和salary的差值。

（３）创建一个查询显示雇员姓名并以*为指示符代表工资数额（列标题为EMPLOYEE_AND_THEIR_SALARIES），一个指示符代表一百美元，并以工资数额降序排列。

将本次实验保存成exper3.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）编写一个显示当期日期的查询，列标题为Date。

（２）显示雇员姓名、受雇日期及工资复审日期（复审日期为受雇后6个月后的第一个星期一），复审日期以列标题REVIEW显示，并且显示的日期形式为“Sunday，the Seventh of September，1981.”。

（３）显示每个雇员的姓名并计算出从受雇日期起到目前一共工作了多少个月，以列标题MONTHS_WORKED显示月数（四舍五入），输出以月数升序排列。

（４）编写一个以下面的形式输出的查询：
（５）<employee name> earns <salary> monthly but wants <3 times salary>.
（６）列标题为Dream Salaries。

（７）创建一个查询显示每个雇员的姓名及工资（列标题为SALARY），工资的显示模式为：占15个字符的宽度，对于不够位数的以$填充。

（８）编写一个查询显示名字以J、A、M开头的雇员的姓名及姓名所占的字符数，姓名的显示格式为第一个字母大写其他字母小写，为每个列设置合适的列标题。

（９）显示雇员的姓名、受雇日期及受雇当天是星期几（列标题为DAY），并以DAY升序排列。

（１０）创建一个查询显示雇员姓名及其奖金（列标题为COMM），如果该雇员不能获得奖金则以“No Commission.”显示。

将本次思考练习题的代码保存成exercise3.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验4 多表查询
实验目的
通过本实验使学员掌握表的各种连接，重点掌握内连接。

实验时间
20分钟
实验要求
（１）掌握多表查询。

（２）区别各种连接方式，掌握其特点。

（１）创建一个查询显示姓名中含有字母A的雇员的姓名、部门名称。

（２）创建一个查询显示雇员的姓名（Employee）、编号（Emp#）以及其上级管理员的姓名（Manager）和编号（Mgr#）。

（３）创建一个查询显示所有雇员（即使是没有上级管理员的King）。

将本次实验保存成exper4.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）编写一个查询显示每个雇员的姓名、部门编号及部门名称。

（２）显示30号部门的雇员所从事的不重复的工资名称，并显示30部门的所在地。

（３）编写一个查询显示能获得奖金的雇员的姓名、部门名称、部门所在地。

（４）编写一个查询显示在DALLAS工作的雇员的姓名、工作、部门编号及部门名称。

（５）创建一个查询显示雇员的姓名、部门编号以及与当前雇员在同一部门工作的同事的姓名，为每个输出列设置合适的列标题。

（６）显示SALGRADE的表结构。

创建一个查询显示雇员的姓名、工作、部门名称、工资以及工资等级。

（７）显示比其上级管理员更早进入公司的雇员的姓名（Employee）、受雇日期（Emp Hiredate）以及其上级管理员的姓名（Manager）及受雇日期（Mgr Hiredate）。

将本次思考练习题的代码保存成exercise4.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验５组函数和分组统计
实验目的
通过本实验使学员掌握分组函数。

实验时间
20分钟
实验要求
（１）掌握组函数的用法。

（２）掌握GROUP BY子句的用法。

实验内容
（１）显示所有雇员工资的最高（Maximum）、最低（Minimum）、合计（Sum）和平均值（Average），并对各值按四舍五入进行处理。

（２）按照工作种类进行分组，显示各个组内工资的最高（Maximum）、最低（Minimum）、合计（Sum）和平均值（Average）。

（３）编写一查询显示各个部门的名称（DNAME）、位置（LOC）、部门内的雇员数（Number of People）、部门内的平均工资（Salary）、平均工资四舍五入到小数点后两位。

将本次实验保存成exper5.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

（１）组函数对多个数据行进行处理，每个组产生一个结果，该语句对否，请判断。

（２）组函数计算过程中可以对空值进行处理，该语句对否，请判断。

（３）WHERE字句对数据行的限制优先于分组计算，该语句对否，请判断。

（４）编写一个查询显示从事同一工作的人数。

（５）求出管理员的人数（Number of Managers），而不显示管理员。

（６）编写一查询显示雇员最高工资和最低工资的差值（DIFFERENCE）。

（７）显示管理员编号（管理员编号明确），以及被该管理员所管理的雇员的最低工资（最低工资大于1000美元）。

输出结果按最低工资降序排列。

（８）创建一查询显示所有雇员的数量以及分别在1980、1981、1982、1983年加入公司的雇员数量，设置恰当的列标题。

（９）创建一查询，按工作进行分组，求出各组内分别在10、20、30部门工作的雇员的工资的总数，以及各组的工资的合计。

设置恰当的列标题。

将本次实验保存成exercise5.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验6 子查询
实验目的
通过本实验使学员掌握子查询、相关查询子句的用法。

实验要求
（１）掌握单行和多行子查询的用法。

（２）掌握在子查询中使用组函数。

实验时间
大约60分钟
实验内容
（１）编写一查询，显示姓名中含有字符T的雇员所在的部门内的所有雇员的编号和姓名。

（２）创建一个查询显示工资高于平均工资水平并且与姓名中含有T的雇员在同一部门工作的雇员的编号、姓名及工资。

（３）创建一查询，显示能获得比所有从事CLERK工作的雇员的个人工资更高的其他雇员的姓名、工作及工资，输出按工资降序排列。

将本次实验保存成exper6.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）编写一查询，显示与Blake在同一部门工作的雇员的姓名和受雇日期，但是Blake不包含在结果内。

（２）创建一查询，显示工资大于平均工资水平的雇员的编号及姓名，输出结果按工资降序排列。

（３）显示位置在Dallas的部门内的雇员的姓名、编号及工作。

（４）显示被King直接管理的雇员的姓名及其工资。

（５）显示在Sales部门工作的雇员的部门编号、姓名及工作。

（６）编写一查询，显示与能获得奖金的雇员的编号和工资同时匹配的雇员的编号和工资。

（７）显示与工作在Dallas的雇员的工资及奖金同时匹配的雇员的姓名、部门名称及工资。

（８）创建一查询，显示能获得与Scott一样工资和奖金的其他雇员的姓名、受雇日期及工资。

将本次实验保存成exercise6.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验7 表、创建和管理表、约束
实验目的
通过本实验使学员掌握创建表、约束，并且管理表、约束的用法。

实验要求
（１）掌握数据的插入、更新、删除操作。

（２）掌握事物控制。

（３）掌握表的创建、修改、删除、更名。

（４）掌握创建约束、修改约束、删除约束。

实验时间
大约60分钟
实验内容
（２）从DEPT表中提取部分数据插入到DEPARTMENT表中。

（４）并向该表添加数据，数据参考下表。

（５）显示MY_EMPLOYEE表的结构，记住字段名称、类型及先后顺序。

（６）向MY_EMPLOYEE表中添加首条记录，要求不在INSERT字句中使用字段列表。

（７）添加MY_EMPLOYEE表的第二条记录，要求在INSERT字句中使用字段列表。

（８）验证数据是否添加成功。

（９）使数据的增加成为永久性的。

（１０）修改表EMPLOYEE，使last names字段能容纳50个字符，并验证修改结果。

（１１）验证数据字典中是否存有DEPARTMENT和EMPLOYEE的信息。

（１２）基于EMP表创建一个新表EMPLOYEE2，但只引用EMP表的EMPNO、ENAME、DEPTNO三个列，并且在新表中重新命名这三个字段：ID、LAST_NAME、DEPT_ID。

（１３）删除EMPLOYEE表。

（１４）将表EMPLOYEE2命名为EMPLOYEE。

（１５）为DEPARTMENT和EMPLOYEE表添加适当的注释，并从字典中加以验证。

（１６）为EMPLOYEE表添加表一级的PRIMARY KEY约束，将该约束定义在ID列上，该约束应该在创建表的同时被创建。

（１７）在DEPARTMENT的ID列上定义PRIMARY KEY约束，该约束应该在创建表的同时被创建。

（１８）为EMPLOYEE表增加一外键约束，以确保雇员不会被标以不存在的部门编号。

（１９）通过查询字典表USER_CONSTRAINTS验证约束是否添加成功，注意观察约束的名称及类型。

（２０）通过字典表USER_OBJECTS查询关于EMPLOYE E和DEPARTMENT表的用户对象。

（２１）修改EMPLOYEE表，添加SALARY列，类型为NUMBER，精度为7。

将本次实验保存成exper7.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

思考练习题
（１）下边的语句有什么错误？
DELETE emp；
（２）下边的语句有什么错误？
INSERT INTO emp SELECT * FROM dept；
（３）如果执行下边的语句会有什么结果？
SQL>DELETE * FROM emp；
（４）如果执行下边的语句会有什么结果？
SQL>DELETE FROM emp；
（５）如果执行下边的语句会有什么结果？
SQL>UPDATE emp SET mrg=7698
a)SET job = ‘CLERK’；
（６）ALTER DATABASE语句经常用在修改已有表的结构上，该语句对否，请判断？
（７）DROP TABLE语句与DELETE FROM的作用是相同的，该语句对否，请判断？
（８）可以使用CREATE TABLE命令向数据库中加入一个新表，该语句对否，请判断？
（９）为什么下边的语句是错误的？
（１０）SQL>CREATE TABLE new_table(
（１１）ID NUMBER
（１２）FIELD1 char(40)
（１３）FIELD2 char(80)
（１４）ID char(40))；
（１５）当一个表建立时，谁是它的所有者？
（１６）如果字符型列的长度在不断变化，选择哪种数据类型最合适？
（１７）表名是否可以重复？
（１８）有哪5种约束，各约束的作用是什么？
（１９）哪些约束是定义在表一级的，哪些约束是定义在列一级？
（２０）如何在创建表的同时创建约束？
（２１）如何添加、删除约束？
（２２）如何启用、禁用约束？
（２３）如何查询约束的相关信息？
将本次实验保存成exercise7.sql脚本文件，请注意为每一项添加适当的注释，以说明每个查询所完成的功能。

实验8异常、过程和函数
实验目的
通过本实验使学员熟悉函数、过程、异常的总和使用。

实现要求
（１）掌握存储过程的用法。

（２）掌握存储函数的用法。

（３）掌握异常处理的机制。

实验时间
100分钟
实验内容
创建一个调薪日志表log_tab(ID自动增长列,ex_empno调薪的员工编号,percsal薪水调整幅度)
创建一个或多个存储过程或函数，完成以下功能：
（１）接受两个参数：调薪员工的编号、工资调整幅度百分数（如：-0.2代表降20%的工资）。

（２）对指定的员工按照调整幅度修改工资。

（３）如果工资被调整到500元低保以下，阻止调薪，过程异常终止，并向操作员报告“工资将低于低保线”的异常。

（４）如果调薪成功，请按照日志表的格式登记调薪日志，记录执行调薪的操作员、被调薪的员工编号和薪水调整幅度。

（５）如果登记日志成功，请显示“日志登记成功”的信息，如果登记日志过程失败，请显示“日志登记失败”
的信息。

将本次实验保存成exper8.sql脚本文件，请注意为每一项添加适当的注释。

实验9 数据泵
实验目的
通过本实验加深对数据泵的深入理解。

实验要求
（１）掌握模式导出。

（２）掌握表导出。

（３）掌握数据库导出。

实验时间
大约40分钟
实验内容
（１）创建一个新的用户SHE，将SCOTT模式中的对象映射到新用户SHE中。

（２）创建一个导出脚本：导出SCOTT模式中的所有表，再创建一个导入脚本，将导出的SCOTT的所有表导入到SCOTT模式，并替换SCOTT中同名表。

（３）
将本次实验保存成exper9.sql脚本文件，请注意为每一项添加适当的注释。

实验10 综合实验
实验目的
通过本实验检测学员对总体学习效果。

实验要求
（４）掌握复杂查询。

（５）掌握事物控制。

（６）掌握表和约束的创建与管理。

（７）掌握子过程的用法。

实验时间
大约100分钟
实验内容
本题用到下面三个关系表：
借书卡card(cno卡号,name姓名,class班级)
图书books(bno书号,bname书名,author作者，price 单价,quantity库存册数)
借书记录borrow (cno借书卡号,bno书号,rdate还书日期)
注：限定每人每种书只能借一本；库存册数随借书、还书而改变。

（４）写出建立borrow表的SQL语句，要求定义主码完整性约束和引用完整性约束。

（５）找出借书超过5本的读者,输出借书卡号及所借图书册数。

（６）查询借阅了“水浒”一书的读者，输出姓名及班级。

（７）查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

（８）查询书名包括“网络”关键词的图书，输出书号、书名、作者。

（９）查询现有图书中价格最高的图书，输出书名及作者。

（１０）查询当前借了“计算方法”但没有借“计算方法习题集”的读者，输出其借书卡号，并按卡号降序排序输出。

（１１）将“c01”班同学所借图书的还期都延长一周。

（１２）从books表中删除当前无人借阅的图书记录。

（１３）如果经常按书名查询图书信息，请建立合适的索引。

（１４）在borrow表上建立一个触发器，完成如下功能：如果读者借阅的书名是“数据库技术及应用”，就将该读者的借阅记录保存在borrow_save表中（注：borrow_save表结构同borrow表）。

（１５）建立一个视图，显示“力01”班学生的借书信息（只要求显示姓名和书名）。

（１６）查询当前同时借有“计算方法”和“组合数学”两本书的读者，输出其借书卡号，并按卡号升序排序输出。

（１７）假定在建books表时没有定义主码，写出为books表追加定义主码的语句。

（１８）对card表做如下修改：
（１９）将name最大列宽增加到10个字符（假定原为6个字符）。

（２０）为该表增加1列：dname（系名），可变长，最大20个字符。

将本次实验保存成exper10.sql脚本文件，请注意为每一项添加适当的注释。