1、创建某图书馆借书系统数据库，完成要求的sql语句。
书book借书证card
借书记录borrow
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)
values('200013','**','除却巫山不是云','001',2013,'扈三娘',25.2,50,49);
insert into book(bno,category,title,press,year,author,price,total,remains)
values('200014','**','麻辣烫','001',2013,'孙二娘',11.2,50,49);
insert into card(cno,name,department,type,days)
values('s000001','孙悟空','花果山','s',30);
insert into card(cno,name,department,type,days)
values('s000002','林冲','东京','s',30);
insert into card(cno,name,department,type,days)
values('t000001','赤脚大仙','天庭','t',30);
insert into card(cno,name,department,type,days)
values('s000003','鲁智深','五台山','s',30);
insert into card(cno,name,department,type,days)
values('s000004','贾宝玉','大观园','s',30);
insert into card(cno,name,department,type,days)
values('s000005','贾连','大观园','s',30);
insert into card(cno,name,department,type,days)
values('s000006','林黛玉','大观园','s',30);
insert into card(cno,name,department,type,days)
values('s000007','薛宝钗','大观园','s',30);
insert into card(cno,name,department,type,days)
values('s000008','袭人','大观园','s',30);
insert into card(cno,name,department,type,days)
values('s000009','猪八戒','高老庄','s',30);
insert into card(cno,name,department,type,days)
values('s000010','令狐冲','华山','s',30);
insert into borrow values('200001','s000002','2015-05-12',null);
insert into borrow values('200003','s000002','2015-05-12',null);
insert into borrow values('200005','s000002','2015-05-12',null);
insert into borrow values('200007','s000007','2015-05-12',null);
insert into borrow values('200008','s000005','2015-05-12',null);
insert into borrow values('200007','s000008','2015-05-12',null);
数据库上机作业
语句查询数据 二 汇总查询 实验内容 1 打开 sql server management studio 窗口。2 单击 标准 工具栏的 新建查询 按钮，打开 查询编辑器 窗口3 在窗口中输入以下sql查询命令并执行 a.在kc表中，统计每学期的总分数。usexscjselect开课学期，count...
数据库上机作业
语句查询数据 二 汇总查询。打开 窗口。单击 标准 工具栏的 新建查询 按钮，打开 查询编辑器 窗口。在窗口中输入以下查询命令并执行 在表中，统计每学期的总分数。开课学期，学分 总学分 在表中统计每个学生的选修课程的门数。学号，课程号 选修的课程门数。统计表中的总学分，并显示明细信息。总学分。按开课...
数据库上机作业
2011年11月21日上机作业。1利用t sql语句，对教学库完成下列查询。1 检索所有姓王的学生的姓名和年龄。2 检索成绩为空值的学生学号和课程号。3 统计有学生选修的课程门数。4 统计每门课程的学生选修人数，超过3人的课程才统计，要求输出课程号和选修人数。5 检索选修2门以上课程的学生平均成绩 ...