华科数据库上机作业_(含答案)
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
1、创建某图书馆借书系统数据库，完成要求的SQL语句
1）创建表书book
create table book
(bno char(8)primary key,
category char(10),
title varchar(40),
press char(3),
year int,
author varchar(20),
price decimal(5,2),
total int,
remains int
)
2）创建表借书证card
create table card
(
cno char(7)primary key,
name varchar(10),
department varchar(40),
type char(1),
days int
)
3）创建表借书记录borrow，建立与book表、card表的外键关系
create table borrow
(
bno char(8)constraint fk_bno foreign key references book(bno), cno char(7)constraint fk_cno foreign key references card(cno), borrow_date date,
return_date date,
constraint pk_borrow primary key (bno,cno)
)
4）用INSERT语句为每个表插入不少于10条记录；
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200001','文学','水浒传','001',2013,'施耐庵',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains)
values('200002','文学','西游记','001',2013,'吴承恩',35.7,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200003','文学','红楼梦','001',2013,'曹雪芹',68.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200004','文学','三国演义','001',2013,'罗贯中',41.5,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200005','教学','机械设计','002',2013,'张三丰',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200006','教学','机械原理','002',2013,'张无忌',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200007','教育','优化设计','002',2013,'武松',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200008','教育','高等数学','002',2013,'唐三藏',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200009','教育','大学物理','002',2013,'沙和尚',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200010','教育','数学物理方程','002',2013,'白骨精',45.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200011','小说','一个人的旅行','001',2013,'白骨精',35.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains) values('200012','小说','神机妙扇','001',2013,'铁扇公主',15.2,50,49);
insert into
book(bno,category,title,press,year,author,price,total,remains)