Oracle数据库复习题

Oracle数据库上机复习题

第一题 创建数据库和表（总分40分）

1、启动SQL*Plus，在sys帐户下创建学生基本信息表student、课程基本信息表course和成绩表score，通过限制条件实现数据的完整性，并显示表结构。（30分）

学生基本信息表student定义 列名 sno sname ssex birthday 列名 cno cname ccredit 列名 sno cno grade 数据类型 CHAR CHAR CHAR DATE 数据类型 char varchar2 number 数据类型 char char number 大小 9 10 2 大小 9 50 1 大小 9 9 3 约束 主键 非空 为男或女 约束 主键 唯一 非空 约束 主键，外键 主键，外键 非空 含义 学号 姓名 性别 出生日期 含义 课程号 课程名 学分 含义 学号 课程号 成绩 课程基本信息表course定义 成绩表score定义 2、向表中插入记录。（10分）

（1）向学生基本信息表中添加两条记录，并显示表中所有记录。第一条记录为自己的学号、姓名、性别和出生日期，另一条记录任意编写。

（2）向课程基本信息表中添加如下五条记录，并显示全部记录。 cno 12011003H 12012006H 12012009H 12012010H 12012021H cname 计算机导论 SQL Server数据库技术 Oracle数据库技术 Linux操作系统 人工智能 ccredit 3 3 3 3 3 （3）向成绩表中插入自己的五门课程成绩，并显示全部记录内容。

第二题 触发器的创建和使用（每题20分，任意其一）

1、在scott.emp表上创建一个触发器，不允许删除雇员号为7934雇员，并提示“禁止删除该名雇员”。

2、在scott.emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出部门名称。

绥化学院计算机学院 第 1 页 共 9 页

第二题 创建PL/SQL块，结合游标编程。（每题20分，任意其一）

1、利用带参的显示游标，任意输入一个员工的名字，在scott.emp表中查询该员工信息，并输出其员工号、工资，所在部门。如果该员工不存在，则提示该员工不存在。

2、利用带参的显式游标，任意输入一个员工的员工号，在scott.emp表中查询，并返回某员工所在部门的平均工资。

3、利用带参的显式游标，任意输入某个工作名称，从scott.emp表中逐条输出从事这一工作的每位员工的编号、姓名和受雇如期。

4、列出scott.emp表中工资高于平均工资的所有雇员的员工号，员工名和工资值。 5、利用带参的显式游标，任意输入一个部门号，逐条输出scott.emp表中该部门每位员工的编号、姓名和受雇日期。

6、输出scott.emp表中10部门所有员工的员工号、员工名、工资和部门名称。

第三题 视图、同义词、序列。（总分15分，视图和序列二选一）

1、创建下列视图：数据源：scott.emp表和scott.dept表。视图名v_emp_dept，视图列包含雇员号，雇员名，性别，部门号，部门名，并显示视图内容。（10分）

2、创建序列并利用序列向scott.dept表中插入部门号80,90,100，部门名和地址见下表，显示插入后的表记录，最后删除序列。（10分） deptno 80 90 100 dname Computer English Chinese Loc China China China 3、为scott用户的emp表创建一个公有同义词，名为scottemp，并练习删除。（5分）

第四题 安全管理（总分25分）

步骤1 创建以口令认证的数据库用户U1和U2，口令分别为usera，userb，默认表空间为users，配额为10M，初始帐户为解锁状态。 步骤2 为U1和U2用户授予create session权限。

步骤3 将scott.emp的select权限赋予U1，并允许将该权限间接授予其他用户。

步骤4 以U1身份登录数据库，查询scott.emp中的数据。并将scott.emp的select权限授予U2。

步骤5 禁止用户U1将scott.emp的select权限再转授予其他用户。

绥化学院计算机学院 第 2 页 共 9 页

Oracle数据库上机复习题参考答案

第一题 创建数据库和表（总分40分）

1、创建学生基本信息表student、课程基本信息表course和成绩表score，通过限制条件实现数据的完整性。

（1）创建学生基本信息表。（10分） 程序 CREATE TABLE student (sno CHAR(9) PRIMARY KEY, sname CHAR(10) NOT NULL, ssex CHAR(2) CHECK(ssex in(‘男’,’女’)), birthday DATE); （2）创建课程基本信息表。（8分）

程序 CREATE TABLE course (cno CHAR(9) PRIMARY KEY, cname VARCHAR2(50) UNIQUE, ccredit NUMBER(1) NOT NULL ); （3）创建成绩表，学号和课程号为外键，二者联合构成主键，成绩不能为空，成绩值在0到100之间。（10分） 程序 CREATE TABLE score (sno CHAR(9) REFERENCES student(sno), cno CHAR(9) REFERENCES course(cno), grade NUMBER(3) NOT NULL, PRIMARY KEY(sno,cno)); 全部创建成功2分，显示表结构2分。 2、向表中插入记录。（10分）

（1）向学生基本信息表中添加两条记录，并显示。(2分 )

insert into student values('201052760','梁贵毅','男',to_date ('1991-10-16','yyyy-mm-dd')); insert into student values('201052753','张小花','女',to_date ('1990-06-01','yyyy-mm-dd')); （2）向课程基本信息表中添加五条记录。(3分 )

INSERT INTO course VALUES('12011003H','计算机导论',3);

INSERT INTO course VALUES('12012006H','SQL Server数据库技术',3); INSERT INTO course VALUES('12012009H',' Oracle数据库技术',3); INSERT INTO course VALUES('12012010H','Linux操作系统',3); INSERT INTO course VALUES('12012021H','人工智能',2); （3）向成绩表中插入自己的五门课程成绩。(3分 ) insert into score values('201052760','12011003H',99); insert into score values('201052760','12012006H',99); insert into score values('201052760','12012009H',99); insert into score values('201052760','12012010H',99);

绥化学院计算机学院 第 3 页 共 9 页

insert into score values('201052760','12012021H',99); 显示记录内容2分。 select*from student; select*from course; select*from score;

第二题触发器的创建和使用（每题20分，任意其一）

1、在scott.emp表上创建一个触发器，不允许删除雇员号为7934雇员，并提示“禁止删除该名雇员”。

（1）创建触发器（18分） 程序 CREATE OR REPLACE TRIGGER trg_emp_del BEFORE delete ON scott.emp FOR EACH ROW WHEN (old.empno=7934) BEGIN RAISE_APPLICATION_ERROR(-20000,'禁止删除该雇员'); END tri_emp_del; / （2）执行触发操作：（2分）

说明：插入、删除或更新操作均可，答案不唯一。

delete from scott.emp where empno=7934;

2、在scott.emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出部门名称。 （1）创建触发器（18分） 程序 CREATE OR REPLACE TRIGGER trg_emp_dml After insert or update or delete on scott.emp declare v_dname dept.dname%type; begin for v_dept in (select deptno,count(*) num,avg(sal) avgsal from emp group by deptno) loop select dname into v_dname from dept where deptno=v_dept.deptno; dbms_output.put_line(v_dept.deptno||' '||v_dname||' '||v_dept.num||' '||v_dept.avgsal); end loop; end trg_emp_dml; / 绥化学院计算机学院 第 4 页 共 9 页

（2）执行触发操作（2分）

插入、更新、删除均可，答案不唯一。 delete from scott.emp where empno=7934;

第二题 创建PL/SQL块，结合游标编程。（每题20分，任意其一）

说明：程序18分，打开显示开关SET SERVEROUTPUT ON并显示执行结果（2分） 1、 利用带参的显示游标，任意输入一个员工的名字，在scott.emp表中查询该员工信息，并

输出其员工号、工资，所在部门。如果该员工不存在，则提示该员工不存在。 程序 DECLARE v_ename emp.ename%TYPE; CURSOR c_emp (p_ename emp.ename%TYPE) IS SELECT empno,ename,sal,deptno FROM emp WHERE ename=p_ v_emp c_emp%ROWTYPE; BEGIN v_ename:=&x; OPEN c_emp(v_ename); FETCH c_emp INTO v_emp; IF c_emp%FOUND THEN LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno||' '||v_emp.deptno); FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; END LOOP; ELSE RAISE_APPLICATION_ERROR(-20010,'该员工不存在'); END IF; CLOSE c_emp; END;

2、利用带参的显式游标，任意输入一个员工的员工号，在scott.emp表中查询，并返回某员工所在部门的平均工资。 执行程序

程序 DECLARE v_empno emp.empno%TYPE; avgsal emp.sal%TYPE; CURSOR c_emp(p_empno emp.empno%type) IS SELECT deptno FROM emp where empno=p_empno; v_emp c_emp%ROWTYPE; 绥化学院计算机学院 第 5 页 共 9 页

'||v_emp.ename||' '||v_emp.sal||'

本文来源：https://www.bwwdw.com/article/xhet.html