数据库实验题目和答案

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

定义新的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 https://www.360docs.net/doc/0b17767123.html,ame%type; --临时存储课程名

https://www.360docs.net/doc/0b17767123.html,o游标，遍历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 https://www.360docs.net/doc/0b17767123.html,o%type) is

select grade

from sc

where cno = var_cno;

https://www.360docs.net/doc/0b17767123.html,ame游标，根据cur_sc_cno游标中的课程号遍历课程名cname

cursor cur_course_cname(var_cno in https://www.360docs.net/doc/0b17767123.html,o%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_https://www.360docs.net/doc/0b17767123.html,o遍历sc表的grade

for record_sc_grade in cur_sc_grade(record_sc_https://www.360docs.net/doc/0b17767123.html,o)

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;

end loop;

--根据record_sc_cno得到当前课程名

for record_course_cname in cur_course_cname(record_sc_https://www.360docs.net/doc/0b17767123.html,o)

loop

cname:=record_course_https://www.360docs.net/doc/0b17767123.html,ame;

end loop;

dbms_output.put_line('课程:'||cname||' 0<成绩<60 '||a||'人');

dbms_output.put_line('课程:'||cname||' 61<成绩<80 '||b||'人');

dbms_output.put_line('课程:'||cname||' 81<成绩<100 '||c||'人');

end loop;

end;

问题2：需要找出课程a的前n名同学以及他们成绩，并按照从高到低排列。其中a和n为输入参数，a表示课程号，n是一个正整数。如果n大于选修课程a 的所有人数，则只输出选该门课程的所有同学以及他们成绩。输出结果应该类似如下：

数据库

1 张三95分

2 李四90分

3 王二85分

解答：

create or replace PROCEDURE select_sc(

var_cno in https://www.360docs.net/doc/0b17767123.html,o%type,

var_n in number) is

num_sum NUMBER:=0; --记录改课程有成绩的学生数n

num_count NUMBER:=0; --计数器，当前输出到第几个学生

--临时存放游标记录

type record_sc is record

(

var_sname student.sname%type,

var_grade sc.grade%type

);

row_sc record_sc;

--游标查找出连接查询得到的姓名和成绩组合

cursor cur_sc is

select student.sname,sc.grade

from student,sc

where student.sno = sc.sno and https://www.360docs.net/doc/0b17767123.html,o = var_cno

order by sc.grade desc;

begin

--计算出游标影响的行数，即每门课程有几个学生选了并有成绩

for record_num in cur_sc

loop

num_sum := num_sum+1;

end loop;

--判断，当查询数大于sc表中该课程的选修数时—>只输出sc表中的选修数目的姓名和成绩

--当查询数小于sc表中改课程的选修数时—>输出查询数目下的姓名和成绩

if num_sum <= var_n then

for record_sc in cur_sc

loop

dbms_output.put_line(record_sc.sname||record_sc.grade||'分'); end loop;

end if;

if num_sum > var_n then

open cur_sc;

fetch cur_sc into row_sc;

while num_count < var_n

loop

num_count:=num_count+1;

dbms_output.put_line(row_sc.var_sname||row_sc.var_grade||'分'); fetch cur_sc into row_sc;

end loop;

close cur_sc;

end if;

exception

when no_data_found then

dbms_output.put_line('没有找到相应课程的记录!');

end;