数据库原理实践考卷答案.doc

数据库系统原理实践模拟卷参考答案1、 建库（略）（5分）2、建表。（15分）Create Table 商品(商品号char(8) not null, 商品名char(15) not null, 单价real not null check(单价0), 商品类别char(10) not null, 供应商char(10), primary key(商品号);Create Table 顾客(顾客号char(8) not null, 姓名char(15) not null, 住址char(8), primary key(顾客号);Create Table 购买(编号int not null, 顾客号char(8) not null, 商品号char(8) not null, 购买数量int not null check(购买数量=1 and 购买数量=20), primary key(编号), foreign key(顾客号) references 顾客, foreign key(商品号) references 商品);3、往表中插入数据。（20分）insert into 商品 values(M01,佳洁士,8.00,牙膏,宝洁);insert into 商品 values(M02,高露洁,6.50,牙膏,高露洁);insert into 商品 values( M03,洁诺,5.00,牙膏,联合利华);insert into 商品 values( M04,舒肤佳,3.00,香皂,宝洁);insert into 商品 values( M05,夏士莲,5.00,香皂,联合利华);insert into 商品 values( M06,雕牌,2.50,洗衣粉,纳爱斯);insert into 商品 values( M07,中华,3.50,牙膏,联合利华);insert into 商品 values( M08,汰渍,3.00,洗衣粉,宝洁);insert into 商品 values( M09,碧浪,4.00,洗衣粉,宝洁);insert into 顾客 values( C01,Dennis,海淀);insert into 顾客 values( C02,John,朝阳);insert into 顾客 values( C03,Tom,东城);insert into 顾客 values( C04,Jenny,东城);insert into 顾客 values( C05,Rick,西城);insert into 购买 values(1,C01,M01,3);insert into 购买values(2,C01,M05,2);insert into 购买values(3,C01,M08,2);insert into 购买values(4,C02,M02,5);insert into 购买values(5,C02,M06,4);insert into 购买values(6,C03,M01,1);insert into 购买values(7,C03,M05,1);insert into 购买values(8,C03,M06,3);insert into 购买values(9,C03,M08,1);insert into 购买values(10,C04,M03,7);insert into 购买values(11,C04,M04,3);insert into 购买values(12,C05,M06,2);insert into 购买values(13,C05,M07,8);商品表顾客表购买表4、用SQL语句完成下列查询： 4.1）列出购买了供应商宝洁产品的所有顾客的姓名；(10分，其中SQL正确7分，截图正确3分)select distinct 姓名from 顾客,商品,购买where 顾客.顾客号= 购买.顾客号and 商品.商品号= 购买.商品号 and 供应商=宝洁;4.2）列出各个供应商销售出去的牙膏总量；(10分，其中SQL正确7分，截图正确3分)select 供应商,sum(购买数量) as 销售总量from 商品,购买where 商品.商品号=购买.商品号and 商品类别=牙膏group by 供应商4.3）列出顾客Jenny购买的所有商品的名称和数量。(10分，其中SQL正确7分，截图正确3分)select 商品名,购买数量from 顾客,购买,商品where 顾客.顾客号=购买.顾客号and 商品.商品号=购买.商品号 and 姓名=Jenny5、 将销售总量小于等于5的商品的单价降低10%。（15分，其中SQL正确10分，截图正确5分)update 商品set 单价= 单价*0.9where 商品号in (select 商品号 from 购买 group by 商品号 having sum(购买数量)=5)修改过后的结果：6、 建一个存储过程，以商品号为参数，若该商品从未被购买过，则删除该商品记录并返回0，否则返回该商品的销售总量。分别以M01和M09为参数调用该过程。（15分，其中SQL正确10分，截图正确5分)create procedure prcDeleteProduct(ProductId char(8)as Begindeclare SumQty int if not exists(select * from 购买where 商品号=ProductId) begin delete from 商品where 商品号=ProductId; return 0; end else begin select SumQty=sum(购买数量) from 购买 where 商品号=ProductId; return SumQty; end;end; 用M01调用存储过程：declare returnvalue intexec returnvalue = prcDeleteProduct M01select returnvalue返回结果：用M09调用存储过程：declare returnvalue intexec returnvalue = prcDeleteProduct M09select returnvalue返回结果：数据库系统原理实践试题A参考答案1建库create database libraryon(name =library_data,filename =d:librarya1.mdf,size=50,filegrowth=1)建成后会在左边显示：2建表Create table 借阅者(读者号 varchar(6) constraint pk_reader_id primary key(读者号),姓名 varchar(10) not null,地址 varchar(15),性别 varchar(2),年龄 int constraint ck_age check(16=年龄) and (年龄=80),单位 varchar(10) not null)create table 书籍(书号 varchar(10) constraint pk_book_code primary key(书号),书名 varchar(30) not null,作者 varchar(15),出版社 varchar(30);Create table 借阅(读者号 varchar(6) constraint fk_reader_id foreign key(读者号) references 借阅者(读者号),书号 varchar(10) constraint fk_book_id foreign key(书号) references 书籍(书号),借出日期 datetime,应还日期 datetime,Constraint pk_reader_book primary key(读者号,书号),Constraint ck_borrow_back check(应还日期=dateadd(month,2,借出日期);3、使用Enterprise Manager或SQL语句往表中插入下列数据。插入数据结束后，要求给出下列查询语句结果的界面截图：（20分）insert into 借阅者 values(R001,陈城,长沙,男,21,信息院);insert into 借阅者 values(R002,刘军,长沙,男,20,物理院);insert into 借阅者 values(R003,李响,邵阳,男,20,商学院);insert into 借阅者 values(R004,赵天涯,益阳,男,19,信息院);insert into 借阅者 values(R005,王艳,长沙,女,20,地埋院);insert into 借阅者 values(R006,伍云,衡阳,男,22,商学院);insert into 借阅者 values(R007,雷军,邵阳,男,20,信息院);insert into 借阅者 values(R008,谢娟,长沙,女,20,物理院);insert into 借阅者 values(R009,刘兵,长沙,男,19,商学院);select * from 借阅者;insert into 书籍 values(B00001,Visual c+.net,邓远辉,清华大学出版社);insert into 书籍 values(B00002,Java程序设计,蔡翠平,北方交通大学出版社);insert into 书籍 values(B00003,数据库系统原理与应用,刘先锋,武汉大学出版社);insert into 书籍 values(B00004,Linux基础教程,高新田,清华大学出版社);insert into 书籍 values(B00005,数据库系统概论,萨师煊,高等教育出版社);select * from 书籍；insert into 借阅 values(R001,B00004,2008-1-12,2008-3-12);insert into 借阅 values(R002,B00001,2008-10-12,2008-12-12);insert into 借阅 values(R003,B00002,2008-5-12,2008-7-12);insert into 借阅 values(R003,B00005,2008-05-12,2008-07-12);select * from 借阅；4、 用SQL语句完成下列查询，并给出各查询的SQL代码以及查询结果的界面截图：4.1）列出单位是“信息院”的借阅者的全部信息。（10分）select * from 借阅者 where 单位=信息院;4.2）列出单位是信息院，还没有借过书的读者的全部信息。（10分）select * from 借阅者 where 借阅者.读者号 not in(select 借阅.读者号 from 借阅) and 借阅者.单位=信息院;4.3）列出所有读者号，姓名，所借书籍的书号和书名。（10分）select 借阅者.读者号,借阅者.姓名,书籍.书号,书籍.书名 from 借阅者,书籍,借阅 where 借阅者.读者号=借阅.读者号 and 书籍.书号=借阅.书号;5、 创建一个视图实现功能:将借了清华大学出版社出版的书籍的读者的全部信息,及借出日期,应还日期,书名做为该视图的字段. 给出相应的代码以及查询该视图返回的结果截图.（15分） Create view tsinghua as select 借阅者.*,借阅.借出日期,借阅.应还日期,书籍.书名 from 借阅,借阅者,书籍 where 借阅.书号=书籍.书号 and 借阅.读者号=借阅者.读者号 and 书籍.出版社=清华大学出版社select * from tsinghua;6、 建一个存储过程，以读者号为参数，将该读者2008年5月以前借的书(如果有)延迟一个月归还,2008年5月以后(包括5月1日)借的书(如果有)延迟两个月归还。以R003为参数，调用该存储过程。给出相应的代码以及执行结果的界面截图。（15分）create procedure delay_back(reader_id varchar(8)asbeginupdate 借阅 set 借出日期=DATEADD(month,1,借出日期),应还日期=DATEADD(month,1,应还日期) where 读者号=reader_id and 应还日期=2008-05-01;end ;execute delay_back R003;数据库系统原理实践试题B参考答案要求在MS SQL Server2000环境下完成下列任务：1、 建库。（略）2、 建表。create table 学生(学号 char(8) primary key,姓名 char(10),性别 char(2) constraint ck_sex check(性别 in(男,女),年龄 int constraint ck_age check(16=年龄) and (年龄=30),所在系 char(10)create table 课程(课程号 char(8),课程名 char(10),Constraint pk_kk primary key(课程号)create table 选课(学号 char(8) constraint fk_学号 foreign key(学号) references 学生(学号),课程号 char(8) constraint fk_课程号 foreign key(课程号) references 课程(课程号),成绩 int constraint ck_成绩 check(0=成绩) and (成绩=100)Alter table 选课 add constraint uk_选课 unique(学号,课程号);3、 往表中插入值，并完成表的查询insert into 学生 values(S100,王强,男,20,信息系);insert into 学生 values(S101,王维利,男,22,财会系);insert into 学生 values(S102,李立,男,22,英语系);insert into 学生 values(S103,李明,男,20,计算机系);insert into 学生 values(S104,李小峰,男,19,财会系);insert into 学生 values(S105,许鹏飞,男,26,财会系);insert into 学生 values(S106,赵艳,女,20,英语系);insert into 学生 values(S107,李红,女,19,计算机系);insert into 学生 values(S108,李峰,男,20,计算机系);select * from 学生;Insert into 课程 values(K0001,语文);Insert into 课程 values(K0002,英语);Insert into 课程 values(K0003,数学);Select * from 课程insert into 选课 values(S100,k0001,90);insert into 选课 values(S101,k0001,83);insert into 选课 values(S102,k0001,94);insert into 选课 values(S100,k0002,85);insert into 选课 values(S103,k0001,98);insert into 选课 values(S104,k0001,86);insert into 选课 values(S105,k0001,65);insert into 选课 values(S105,k0003,76);insert into 选课 values(S104,k0002,54);insert into 选课 values(S101,k0002,76);insert into 选课 values(S100,k0003,74);insert into 选课 values(S102,k0002,86);insert into 选课 values(S103,k0003,60);inse