数据库实验题目及答案.docx

¢ 按照如下方式重新定义四张表：
— 定义新的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 ame%type; --临时存储课程名
--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 o%type) is
select grade
from sc
where cno = var_cno;
--ame游标，根据cur_sc_cno游标中的课程号遍历课程名cname
cursor cur_course_cname(var_cno in 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_o遍历sc表的grade
for record_sc_grade in cur_sc_grade(record_sc_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_o)
loop
cname:=record_course_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 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 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;