Oracle数据库复习题.doc

《Oracle数据库复习题.doc》由会员分享，可在线阅读，更多相关《Oracle数据库复习题.doc（9页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle数据库上机复习题第一题 创建数据库和表（总分40分）1、启动SQL*Plus，在sys帐户下创建学生基本信息表student、课程基本信息表course和成绩表score，通过限制条件实现数据的完整性，并显示表结构。（30分）学生基本信息表student定义列名数据类型大小约束含义snoCHAR9主键学号snameCHAR10非空姓名ssexCHAR2为男或女性别birthdayDATE出生日期课程基本信息表course定义列名数据类型大小约束含义cnochar9主键课程号cnamevarchar250唯一课程名ccreditnumber1非空学分成绩表score定义列名数据类型大

2、小约束含义snochar9主键，外键学号cnochar9主键，外键课程号gradenumber3非空成绩2、向表中插入记录。（10分）（1）向学生基本信息表中添加两条记录，并显示表中所有记录。第一条记录为自己的学号、姓名、性别和出生日期，另一条记录任意编写。（2）向课程基本信息表中添加如下五条记录，并显示全部记录。cnocnameccreditH计算机导论3HSQL Server数据库技术3HOracle数据库技术3HLinux操作系统3H人工智能3（3）向成绩表中插入自己的五门课程成绩，并显示全部记录内容。第二题 触发器的创建和使用（每题20分，任意其一）1、在scott.emp表上创建一个

3、触发器，不允许删除雇员号为7934雇员，并提示“禁止删除该名雇员”。2、在scott.emp表上创建一个触发器，当插入、删除或修改员工信息时，统计各个部门的人数及平均工资，并输出部门名称。第二题 创建PL/SQL块，结合游标编程。（每题20分，任意其一）1、利用带参的显示游标，任意输入一个员工的名字，在scott.emp表中查询该员工信息，并输出其员工号、工资，所在部门。如果该员工不存在，则提示该员工不存在。2、利用带参的显式游标，任意输入一个员工的员工号，在scott.emp表中查询，并返回某员工所在部门的平均工资。3、利用带参的显式游标，任意输入某个工作名称，从scott.emp表中逐条输

4、出从事这一工作的每位员工的编号、姓名和受雇如期。4、列出scott.emp表中工资高于平均工资的所有雇员的员工号，员工名和工资值。5、利用带参的显式游标，任意输入一个部门号，逐条输出scott.emp表中该部门每位员工的编号、姓名和受雇日期。6、输出scott.emp表中10部门所有员工的员工号、员工名、工资和部门名称。第三题 视图、同义词、序列。（总分15分，视图和序列二选一）1、创建下列视图：数据源：scott.emp表和scott.dept表。视图名v_emp_dept，视图列包含雇员号，雇员名，性别，部门号，部门名，并显示视图内容。（10分）2、创建序列并利用序列向scott.dept

5、表中插入部门号80,90,100，部门名和地址见下表，显示插入后的表记录，最后删除序列。（10分）deptnodnameLoc80ComputerChina90EnglishChina100ChineseChina3、为scott用户的emp表创建一个公有同义词，名为scottemp，并练习删除。（5分）第四题 安全管理（总分25分）步骤1 创建以口令认证的数据库用户U1和U2，口令分别为usera，userb，默认表空间为users，配额为10M，初始帐户为解锁状态。步骤2 为U1和U2用户授予create session权限。步骤3 将scott.emp的select权限赋予U1，并允许将

6、该权限间接授予其他用户。步骤4 以U1身份登录数据库，查询scott.emp中的数据。并将scott.emp的select权限授予U2。步骤5 禁止用户U1将scott.emp的select权限再转授予其他用户。Oracle数据库上机复习题参考答案第一题 创建数据库和表（总分40分）1、创建学生基本信息表student、课程基本信息表course和成绩表score，通过限制条件实现数据的完整性。（1）创建学生基本信息表。（10分）程序CREATE TABLE student(sno CHAR(9) PRIMARY KEY,sname CHAR(10) NOT NULL,ssex CHAR(2)

7、 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),gra

8、de NUMBER(3) NOT NULL,PRIMARY KEY(sno,cno);全部创建成功2分，显示表结构2分。2、向表中插入记录。（10分）（1）向学生基本信息表中添加两条记录，并显示。(2分 )insert into student values(,梁贵毅,男,to_date (1991-10-16,yyyy-mm-dd);insert into student values(,张小花,女,to_date (1990-06-01,yyyy-mm-dd);（2）向课程基本信息表中添加五条记录。(3分 )INSERT INTO course VALUES(H,计算机导论,3);INSE

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

10、re values(,H,99);insert into score values(,H,99);显示记录内容2分。select*from student;select*from course;select*from score;第二题触发器的创建和使用（每题20分，任意其一）1、在scott.emp表上创建一个触发器，不允许删除雇员号为7934雇员，并提示“禁止删除该名雇员”。（1）创建触发器（18分）程序CREATE OR REPLACE TRIGGER trg_emp_delBEFORE delete ON scott.empFOR EACH ROWWHEN (old.empno=793

11、4)BEGINRAISE_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_dmlAfter insert or update or delete on scott.empdec

12、larev_dname dept.dname%type;beginfor v_dept in (select deptno,count(*) num,avg(sal) avgsal from emp group by deptno)loopselect 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;/（2）执行触发操作（2分）插入、

13、更新、删除均可，答案不唯一。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,e

14、name,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.ename| |v_emp.sal| |v_emp.deptno); FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; END LOOP; ELSE RAISE_APP

15、LICATION_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; BEGIN V_EMPNO:=&X; OPEN

16、c_emp(v_empno); FETCH c_emp INTO v_emp; select avg(sal) into avgsal from emp having deptno=v_emp.deptno group by deptno; DBMS_OUTPUT.PUT_LINE(avgsal); CLOSE c_emp; END; /执行结果(2分)答案不唯一。3、利用带参的显式游标，任意输入某个工作名称，从scott.emp表中逐条输出从事这一工作的每位员工的编号、姓名和受雇如期。程序DECLAREv_empno emp.empno%TYPE;CURSOR c_emp(p_empno e

17、mp.empno%type) ISSELECT deptno,AVG(sal) avgsal FROM emp HAVING deptno IN (SELECT deptno FROM emp WHERE empno=v_empno) GROUP BY deptno;v_emp c_emp%ROWTYPE;BEGINv_empno:=&x;OPEN c_emp(v_empno);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.deptno| |v_emp.avgsal);END LOO

18、P;CLOSE c_emp;END;/4、列出scott.emp表中工资高于平均工资的所有雇员的员工号，员工名和工资值。程序declare cursor c_emp is select empno,ename,sal from emp; avgsal emp.sal%type; v_emp c_emp%rowtype; begin select avg(sal) into avgsal from emp; open c_emp; loop fetch c_emp into v_emp; exit when c_emp%notfound; if v_emp.salavgsal then dbms

19、_output.put_line(v_emp.ename| |v_emp.empno| |v_emp.sal); end if; end loop;end;5、利用带参的显式游标，任意输入一个部门号，逐条输出scott.emp表中该部门每位员工的编号、姓名和受雇日期。程序DECLAREv_deptno emp.deptno%TYPE;CURSOR c_emp(p_deptno emp.deptno%type) ISSELECT empno,ename, hiredate FROM emp WHERE deptno=p_deptno;v_emp c_emp%ROWTYPE;BEGINv_dept

20、no:=&请输入部门号;OPEN c_emp(v_deptno);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename| |v_emp.hiredate);END LOOP;CLOSE c_emp;END;/6、输出scott.emp表中10部门所有员工的员工号、员工名、工资和部门名称。程序DECLAREv_dname dept.dname%TYPE;CURSOR c_emp IS SELECT empno,ename,sal FROM emp WHE

21、RE deptno=10;v_emp c_emp%ROWTYPE;BEGINSELECT dname INTO v_dname FROM dept WHERE deptno=10;OPEN c_emp;LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename| |v_emp.sal| |v_dname);END LOOP;CLOSE c_emp;END;/说明：组卷方案中，触发器和游标分值相同，难度相似，二选一。第三题 视图、同义词、序列。（总分15分，视

22、图和序列二选一）1、创建下列视图：数据源：emp表和dept表。视图名v_emp_dept，视图列包含雇员号，雇员名，性别，部门号，部门名。显示视图内容并使用DROP VIEW命令删除该视图。2、创建序列并利用序列向dept表中插入部门号50、60、70、80,90,100，并删除序列。3、为scott用户的emp表创建一个公有同义词，名为scottemp，并练习删除。4、创建簇student_course，并在该簇中创建student和course表。1、在scott用户下创建视图Create user usera_exer identified by usera default table

23、space users quota 10M on users account lock; 2、在scott用户下创建序列程序CREATE SEQUENCE dept_sequence INCREMENT BY 10 START WITH 80 MAXVALUE 100;INSERT INTO scott.dept(deptno,dname,loc) VALUES(dept_sequence.nextval,Computer,China);INSERTINTOscott.dept(deptno,dname,loc)VALUES(dept_sequence.nextval,English,Chin

24、a);SELECT * FROM dept;DROP SEQUENCE dept_sequence;3、在系统用户sys下创建同义词程序CREATE PUBLIC SYNONYM scottemp FOR scott.emp;SELECT * FROM scottemp;DROP PUBLIC SYNONYM scottemp;第四题 安全管理（总分25分）步骤1 创建以口令认证的数据库用户U1和U2，口令分别为usera，userb，默认表空间为users，配额为10M，初始帐户为解锁状态。（5分）以系统帐户sys或system连接数据库创建用户U1和U2。Create user usera

25、_exer identified by usera default tablespace users quota 10M on users account lock; creater user userb_exer identified by userb default tablespace users quota 10M on users account unlock;步骤2 为U1和U2用户授予create session权限。（5分）grant create session to u1,u2; 步骤3 将scott.emp的select权限赋予U1，并允许将该权限间接授予其他用户。（5分

26、）grant select on scott.emp to u1 with grant option; 步骤4 以U1身份登录数据库，查询scott.emp中的数据。并将scott.emp的select权限授予U2。（5分）以u1账户连接数据库，验证是否拥有关于scott.emp表的查询权限。conn u1/usera;select empno,ename,sal,deptno from scott.emp where empno=7844;显示结果表明，查询和更新权限已经被成功赋予。grant select on emp to u2;以u2帐户连接数据库，验证是否拥有相关权限。conn u2/userbselect * from scott.emp;执行结果表明，查询和更新权限已经被成功赋予。步骤5 禁止用户U1将scott.emp的select权限再转授予其他用户。（5分）conn sys/jsj as sysdbarevoke select on emp from u1;