Oracle数据库复习题

1、Oracle数据库上机复习题第一题创立数据库和表总分40分1、启动SQL*Plus,在sys帐户下创立学生根本信息表student、课程根本信息表 course和成绩表score,通过限制条件实现数据的完整性,并显示表结构.30分学生根本信息表 stude nt定义列名数据类型大小约束含义snoCHAR9主键学号sn ameCHAR10非空姓名ssexCHAR2为男或女性别birthdayDATE出生日期课程根本信息表 course定义列名数据类型大小约束含义enochar9主键】田千口 i=r. 课程号cn amevarchar250唯一课程名ccreditnu mber1非空学分成绩表sc

2、ore定义列名数据类型大小约束含义snochar9主键,外键学号enochar9主键,外键】田千口 i=r. 课程号gradenu mber3非空成绩2、向表中插入记录.10分1 向学生根本信息表中添加两条记录,并显示表中所有记录. 第一条记录为自己的学号、 姓名、性别和出生日期,另一条记录任意编写.2向课程根本信息表中添加如下五条记录,并显示全部记录.enocn ameccredit12021003H计算机导论312021006HSQL Server数据库技术312021021HOracle数据库技术312021021HLinux操作系统312021021H人工智能33 向成绩表中插入自己的

3、五门课程成绩,并显示全部记录内容.第二题触发器的创立和使用每题20分,任意其一1、 在scott.emp表上创立一个触发器,不允许删除雇员号为7934雇员,并提示“禁止删除 该名雇员.2、在scott.emp表上创立一个触发器,当插入、删除或修改员工信息时,统计各个部门的人 数及平均工资,并输出部门名称.第二题 创立PL/SQL块,结合游标编程.每题20分,任意其一1、 利用带参的显示游标,任意输入一个员工的名字,在scott.emp表中查询该员工信息,并 输出其员工号、工资,所在部门.如果该员工不存在,那么提示该员工不存在.2、 利用带参的显式游标,任意输入一个员工的员工号,在scott.e

4、mp表中查询,并返回某员 工所在部门的平均工资.3、 利用带参的显式游标,任意输入某个工作名称,从scott.emp表中逐条输出从事这一工作 的每位员工的编号、姓名和受雇如期.4、列出scott.emp表中工资高于平均工资的所有雇员的员工号,员工名和工资值.5、 利用带参的显式游标,任意输入一个部门号,逐条输出scott.emp表中该部门每位员工的 编号、姓名和受雇日期.6、输出scott.emp表中10部门所有员工的员工号、员工名、工资和部门名称.第三题 视图、同义词、序列.总分15分,视图和序列二选一1、 创立以下视图：数据源：scott.emp表和scott.dept表.视图名 v_em

5、p_dept,视图列包含雇员号,雇员名,性别,部门号,部门名,并显示视图内容.10分2、 创立序列并利用序列向scott.dept表中插入部门号 80,90,100,部门名和地址见下表,显示插入后的表记录,最后删除序列.10分dep tnodn ameLoc80ComputerCh ina90En glishCh ina100Chin eseChina3、为scott用户的emp表创立一个公有同义词,名为 scottemp,并练习删除.5分第四题 平安治理总分25分步骤1创立以口令认证的数据库用户U1和U2,口令分别为 usera, userb,默认表空间为users,配额为10M,初始帐户为

6、解锁状态.步骤2为U1和U2用户授予 create session权限.步骤3将scott.emp的select权限赋予U1,并允许将该权限间接授予其他用户.步骤4以U1身份登录数据库,查询scott.emp中的数据.并将 scott.emp的select权限授予U2.步骤5禁止用户U1将scott.emp的select权限再转授予其他用户.Oracle数据库上机复习题参考答案第一题创立数据库和表(总分40分)1、 创立学生根本信息表student、课程根本信息表 course和成绩表score,通过限制条件实 现数据的完整性.(1 )创立学生根本信息表.(10分)程序CREATE TABLE

7、 stude nt(sno CHAR(9) PRIMARY KEY ,sn ame 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分)程

8、序CREATE TABLE score(sno CHAR(9) REFERENCES stude nt(s no),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('202152760','梁贵毅','男',to_date ('1991-10-16'

9、;,'yyyy-mm-dd');insert into student values('202152753','张小花','女',to_date ('1990-06-01','yyyy-mm-dd');(2 )向课程根本信息表中添加五条记录.(3分)INSERT INTO course V ALUES('12021003H','计算机导论',3);INSERT INTO course V ALUES('12021006H','SQL Serve

10、r 数据库技术',3);INSERT INTO course V ALUES('12021021H',' Oracle 数据库技术',3);INSERT INTO course V ALUES('12021021H','Linux 操作系统',3);INSERT INTO course V ALUES('12021021H','人工智能',2);(3)向成绩表中插入自己的五门课程成绩.(3分)in sert into score values('202152760','

11、;12021003H',99);in sert into score values('202152760','12021006H',99);in sert into score values('202152760','12021021H',99);in sert into score values('202152760','12021021H',99);in sert into score values'202152760','12021021H',99;显示

12、记录内容2分.select*from stude nt;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.emp no=7934BEGINRAISE_APPLICATI0N_ERR0R-20000,'禁止删除该雇员'

13、END tri_emp_del;丄2 执行触发操作：2分说明：插入、删除或更新操作均可,答案不唯一.delete from scott.emp where emp no=7934;2、在scott.emp表上创立一个触发器,当插入、删除或修改员工信息时,统计各个部门的人 数及平均工资,并输出部门名称.1创立触发器18分程序CREATE OR REPLACE TRIGGER trg_emp_dmlAfter in sert or update or delete on scott.emp declarev_dn ame dept.d name%type;beginfor v_dept in se

14、lect dept no ,co un t* nu m,avgsal avgsal from emp groupby dept noloopselect dn ame into v_dn ame from dept where dept no=v_dept.dept no; dbms_output.put_li nev_dept.dept no'|v_d name'|v_dept .nu m'|v_dept.avgsal;end loop;end trg_emp_dml;/(2)执行触发操作(2分) 插入、更新、删除均可,答案不唯一.delete from scott.

15、emp where emp no=7934;第二题 创立PL/SQL块,结合游标编程.(每题20分,任意其一)说明：程序18分,翻开显示开关 SET SERVEROUTPUT ON并显示执行结果(2分)1、利用带参的显示游标,任意输入一个员工的名字,在scott.emp表中查询该员工信息,并 输出其员工号、工资,所在部门.如果该员工不存在,那么提示该员工不存在.程序DECLAREv_en ame emp.e name%TYPE;CURSOR c_emp (p_e name emp.e name%TYPE) IS SELECT emp no,e name,sal,dept no FROMemp

16、WHERE en ame=p_ v_emp c_emp%ROWTYPE;BEGINv_en ame:=&x;OPEN c_emp(v_e name);FETCH c_emp INTO v_emp;IF c_emp%FOUND THEN LOOPDBMS_OUTPUT.PUT_LINE(v_emp.emp no'|v_emp.e name|v_emp.sal'|v_emp.dept no);FETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;END LOOP;ELSERAISE_APPLICATION_ERROR(-2OO1O,

17、'该员工不存在)；END IF;CLOSE c_emp;END;2、利用带参的显式游标,任意输入一个员工的员工号,在scott.emp表中查询,并返回某员工所在部门的平均工资.执行程序BEGINV_EMPNO:=&X;OPEN c_emp(v_emp no);FETCH c_emp INTO v_emp;select avg(sal) into avgsal from emp havi ng dept no=v_emp.dept no group by dept no; DBMS_OUTPUT.PUT_LINE(avgsal);CLOSE c_emp;END;/执行结果(2分)

18、答案不唯一.3、 利用带参的显式游标,任意输入某个工作名称,从scott.emp表中逐条输出从事这一工作 的每位员工的编号、姓名和受雇如期.程序DECLAREv_emp no emp.emp no%TYPE;CURSOR c_emp(p_emp no emp.emp no%type) ISSELECT dept no,A VG(sal) avgsal FROM emp HA VING dept no IN (SELECT dep tno FROM emp WHERE empn o=v_emp no) GROUP BY dept no;v_emp c_emp%ROWTYPE;BEGINv_emp

19、 no:=&x;OPEN c_emp(v_emp no);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.dept no'|v_emp.avgsal);END LOOP;CLOSE c_emp;END;I4、列出scott.emp表中工资高于平均工资的所有雇员的员工号,员工名和工资值.程序declarecursor c_emp is select emp no,en ame,sal from emp;avgsal emp.sal%type;v_emp c_emp%row

20、type;beginselect avg(sal) into avgsal from emp;ope n c_emp;loopfetch c_emp into v_emp;exit whe n c_emp% notfound;if v_emp.sal>avgsal the ndbms_output.put_li ne(v_emp.e name '|v_emp.emp no'|v_emp.sal);end if;end loop;en d;5、 利用带参的显式游标,任意输入一个部门号,逐条输出scott.emp表中该部门每位员工的 编号、姓名和受雇日期.程序DECLAREv

21、_dept no emp.dept no%TYPE;CURSOR c_emp(p_dept no emp.dept no%type) ISSELECT emp no,e name, hiredate FROM emp WHERE dept no=p_dept no; v_emp c_emp%ROWTYPE;BEGINv_deptno:=&请输入部门号；OPEN c_emp(v_dept no);LOOPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.empno|' '|

22、v_emp.ename|' '|v_emp.hiredate);END LOOP;CLOSE c_emp;END;I6、输出scott.emp表中10部门所有员工的员工号、员工名、工资和部门名称.程序DECLAREv_dn ame dept.d name%TYPE;CURSOR c_emp ISSELECT emp no,e name,sal FROM emp WHERE dept no=10;v_emp c_emp%ROWTYPE;BEGINSELECT dn ame INTO v_dn ame FROM dept WHERE dept no=10;OPEN c_emp;LO

23、OPFETCH c_emp INTO v_emp;EXIT WHEN c_emp%NOTFOUND;DBMS_OUTPUT.PUT_LINE(v_emp.emp no'|v_emp.e name '|v_emp.sal '|v_d name); END LOOP;CLOSE c emp;END;/说明：组卷方案中,触发器和游标分值相同,难度相似,二选一.第三题 视图、同义词、序列.(总分15分,视图和序列二选一)1、 创立以下视图：数据源：emp表和dept表.视图名v_emp_dept,视图列包含雇员号,雇员名,性别,部门号,部门名.显示视图内容并使用DROP VIE

24、W命令删除该视图.2、 创立序列并利用序列向 dept表中插入部门号50、60、70、80,90,100,并删除序列.3、 为scott用户的emp表创立一个公有同义词,名为scottemp,并练习删除.4、 创立簇student_course,并在该簇中创立 student和course表.1、在scott用户下创立视图Create user usera_exer ide ntified by usera default tablespace users quota 10M on users acco unt lock;2、在scott用户下创立序列程序CREATE SEQUENCE dep

25、t_sequenee INCREMENT BY 10 START WITH 80 MAXVALUE 100;INSERT INTO scott.dept(dept no,d name,loc)VALUES(dept_sequence.nextval, ' Computer', 'China');INSERTINTOscott.dept(dept no,d name,loc)VALUES(dept_sequence.nextval, 'English )'China');SELECT * FROM dept;DROP SEQUENCE de

26、pt_seque nee;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_exer ide ntified by usera default t

27、ablespace users quota 10M on users acco unt lock;creater user userb_exer ide ntified by userb default tablespace users quota 10M on users acco unt unl ock;步骤2为U1和U2用户授予 create session权限.5分grant create sessi on to u1,u2;步骤3将scott.emp的select权限赋予U1,并允许将该权限间接授予其他用户.5分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 emp no,en ame,sal,dept no from scott.emp where emp no=7844;显示结果说明,查询和更新权限已经被成功赋予.grant select on emp to u2;以u2帐户连接数据库,验证是否拥有相关权限.conn u2/userbselect * from scott.emp;执行