数据库实验题目和答案

? 按照如下方式重新定义四张表：

? 定义新的books表，其所拥有的属性与属性类型与原有的books表一致 ? 以列级完整性的方式定义属性bookId为主码，同时在定义的时候必须提供

约束名

? 定义属性category为”非空” ，同时在定义的时候必须提供约束名 create table Books(

bookId number(9,0)

constraint BooksKey primary key, title varchar(80), author varchar(40), year integer,

category varchar(15) constraint B1 not null ); commit;

? 定义新的customers表，其所拥有的属性与属性类型与原有的customers表

一致

? 以表级完整性的方式定义属性cid为主码

? 以列级完整性的方式定义属性cname为”唯一”，同时在定义的时候必须提供

约束名

create table Customers ( cid number(9,0), cname char(40)

constraint C1 unique, age integer, primary key(cid) ); commit;

? 定义新的purchases表，其所拥有的属性与属性类型与原有的purchases表一

致

? 定义属性cid与bookId为主码，同时在定义的时候必须提供约束名

? 以列级完整性的方式定义属性bookId为外码，它参照Books表的主码，同

时在定义的时候必须提供约束名。当删除Books表中的某一本书时，级联删除与它相关的购买记录

? 以表级完整性的方式定义属性cid为外码，它参照customers表，同时在定

义的时候必须提供约束名。当删除customers表中的某个顾客时，如果存在与该客户相关的购买记录，拒绝删除。 create table Purchases( cid number(9,0), bookId number(9,0)

constraint bookidFKey references Books(bookid) on delete cascade, pdate date,

pprice number(8,2),

constraint PurchasesKey primary key(cid,bookid),

constraint cidFKey foreign key(cid) references customers(cid) ); commit;

? 定义新的pricing表，其所拥有的属性与属性类型与原有的pricing表一致 ? 以列级完整性的方式定义属性booId为主码，同时在定义的时候必须提供约

束名

? 定义约束，要求当书的format是‘paperback’时，当前价格不能超过50 create table Pricing(

bookId number(9,0)

constraint PricingKey primary key, format varchar(15), price number(9,2),

constraint P1 check (format <>'paperback' OR price<=50) );

commit;

? 创建如下的触发器：

? 定义一个AFTER行级触发器，当修改books表的bookid后，自动修改

purchases和pricing表中与它相关记录中的bookid(只允许使用一个触发器) create trigger alter_bookid after update on books for each row begin

if(:new.bookid<>:old.bookid) then update purchases

set bookid = :new.bookid where bookid = :old.bookid; update pricing

set bookid = :new.bookid where bookid = :old.bookid; end if; end;

? 定义如下的一张表：

? Score(Sno, Chinese, Math, English, Total)

? Sno是主码

? Chinese, Math, English分别表示三门课成绩，并且三门课的成绩取值范围是

0-100且均不能为空值

? Total的取值需要正好是三门课成绩总和

? 定义一个AFTER行级触发器，当修改Score表中某门课的成绩后，自动修改

Total

create table Score(

Sno number(9,0)

constraint SnoKey primary key, Chinese number(9,2)

constraint S1 check(Chinese BETWEEN 0 and 100) constraint S2 not null, Math number(9,2)

constraint S3 check(Math BETWEEN 0 and 100) constraint S4 not null, English number(9,2)

constraint S5 check(English BETWEEN 0 and 100) constraint S6 not null, Total number(9,2),

constraint S7 check (total = chinese+math+english) );

定义触发器

create trigger update_score before update on score for each row begin

:new.total := :new.chinese+:new.math+:new.english; end;

利用书本上的3张表：student, course, sc

问题1：对各门课程成绩进行分段统计，并有类似如下的输出结果

课程: 数据库 0<成绩<60 0人 课程: 数据库 60<成绩<80 0人 课程: 数据库 81<成绩<100 1人 课程: 数学 0<成绩<60 0人 课程: 数学 60<成绩<80 1人 课程: 数学 81<成绩<100 2人 课程: 数据结构 0<成绩<60 0人

课程: 数据结构 60<成绩<80 1人 课程: 数据结构 81<成绩<100 1人

解答

set serveroutput on declare

a number; --0-60分人数 b number; --61-80分人数 c number; --81-100分人数

cname course.cname%type; --临时存储课程名

--sc.cno游标，遍历sc表中的每一门课，得到课程号cno cursor cur_sc_cno is select DISTINCT cno from sc;

--sc.grade游标，根据cur_sc_cno游标中的课程号遍历成绩grade cursor cur_sc_grade(var_cno in sc.cno%type) is select grade from sc

where cno = var_cno;

--course.cname游标，根据cur_sc_cno游标中的课程号遍历课程名cname cursor cur_course_cname(var_cno in course.cno%type) is select cname from course

where cno = var_cno; begin

--遍历sc表的cno

for record_sc_cno in cur_sc_cno loop

a:=0; --初始化a,b,c b:=0; c:=0;

--根据record_sc_cno.cno遍历sc表的grade

for record_sc_grade in cur_sc_grade(record_sc_cno.cno) loop

if record_sc_grade.grade > 0 and record_sc_grade.grade < 61 then a:=a+1; end if;

if record_sc_grade.grade > 60 and record_sc_grade.grade < 81 then b:=b+1; end if;

if record_sc_grade.grade > 80 and record_sc_grade.grade < 101 then c:=c+1; end if;