建立“图书”数据库及如下3个表,并输入实验数据,用SQL语句实现如下查询:
(1)查询每条借阅记录的借阅天数(函数DATEDIFF获取两个日期的差);
select datediff(day,bdate,rdate)
from bm
(2)查询库存数在5到10本之间的图书的图书编号、书名及库存量;
select bid,bname,remain
form bookm
where remain > 5 and remain <10
(3)查询书名包括“英语”的图书信息;
select *
from bookm
where bname=’%英语%’
(4)统计各类图书的平均定价以及库存总数;
select ,avg(price),sum(remain)
from bookm
group by class
(5)统计每本书籍借阅的人数,要求输出图书编号和所借人数,查询结果按人数降序排列;
select bid,count(*)
from bm
group by bid
order by count(*)desc
(6)查询有库存的各类图书信息,要求输出类别名称和借阅数量;
select bookm.class,COUNT(*)
from bookm,bm
having bookm.bid=bm.bid
group by class
(7)查询借阅了“大学英语”一书的读者,输出读者姓名、性别、班级;
select rname,sex ,class
from rm
where rid in (
select rid
from bm
where bid in(
select bid
from bm
where bname='大学英语'
)
)
(8)查询每个读者的读者编号、姓名、所借图书编号以及所借阅日期;(LEFT OUTER JOIN)
select bookm.rid,name,aid,bdate
from rm,bm
left outer join bm on (rm.rid =bm.rid)
(9)查询现有图书中价格最高的图书,输出书名、作者、定价;
select bname ,author ,price
from bookm
where price =(
select MAX(price )
from bookm
)
(10)查询借阅了“大学英语”但没有借阅“C++程序设计”的读者,输出读者姓名、性别、系部;
select name ,sex , company
from rm
where name in(
select name
from bm
where bid in (
select bid
from bookm
where bname ='大学英语' ))
and name not in (
select name
from bm
where bid in (
select bid
from bookm
where bname ='C++程序设计') )
(11)统计借阅了2本以上图书的读者信息;
select *
from rm
where rid in(
select rid
from bm
group by rid
having COUNT(*)>2)
(12)查询借阅了“大学英语”一书或者借阅了“C++程序设计”一书的读者信息;(用集合查询完成)
select *
from rm
where rid in (
select rid
from bm,bookm
where bm.bid=bookm.bid and bname ='大学英语')
union
select *
from rm
where rid in (
select rid
from bm,bookm
where bm.bid=bookm.bid and bname='C++程序设计')
(13)查询既借阅了“大学英语”一书又借阅了“C++程序设计”一书的读者信息;(用集合查询完成)
select *
from rm
where rid in (
select rid
from bm,bookm
where bm.bid =bookm.bid and bname='大学英语')
intersect
select *
from rm
where rid in (
select rid
from bm,bookm
where bm.bid=bookm.bid and bname='C++程序设计')
(14)查询计算机系中比其他系所有读者借书数量都多的读者的信息;
select *
from rm x
where company ='计算机系' and rid in(
select rm.rid
from rm,bm
where rm.rid=bm.rid and rm.rid=x.rid
group by rm.rid
having count(bid)>any (
select count(bid)
from rm,bm
where rm.rid =bm.rid and company <>'计算机系' group by rm.rid ) )
(15)将“C++程序设计”这本书的归还日期增加一个月(函数DATEADD)。
update
bm set Borrowingdate =DATEADD(MONTH,1,rdate)
where bid in(
select bid
from bookm
where bname ='C++程序设计')
(16)查询借阅次数大于2的图书的图书编号、书名、库存数和借阅次数;
select *
from rm
where bid in(
select bid
from bm
group by bid
having count(*)>2)
(17)在读者信息表中插入一条新的记录(读者编号:R10011;姓名:张三;单位:管理学院);
insert
into rm(rid,sex ,company )
values ('R10011','张三','电子系')
(18)将计算机类的所有图书的库存数增加5;
update bookm
set remain+=5
where class=’计算机’
实验数据:
图书信息表:
图书编号 | 类别 | 出版社 | 作者 | 书名 | 定价 | 库存 |
1001 | 计算机 | 机械工业出版社 | 王民 | 数据结构 | 28 | 10 |
1002 | 计算机 | 机械工业出版社 | 张建平 | 计算机应用 | 20 | 5 |
1003 | 计算机 | 电子工业出版社 | 王敏 | 数据库技术 | 15 | 8 |
1004 | 计算机 | 电子工业出版社 | 谭浩强 | C 语言 | 25 | 5 |
1005 | 英语 | 中国人民大学出版社 | 张锦芯 | 应用文写作 | 25 | 8 |
1006 | 管理 | 高等教育出版社 | Robison | 管理学 | 15 | 5 |
1007 | 管理 | 机械工业出版社 | Fayol | 工业管理 | 30 | 4 |
1008 | 数学 | 机械工业出版社 | 李平 | 线性代数 | 20 | 5 |
1009 | 管理 | 机械工业出版社 | Durark | 公司的概念 | 14 | 10 |
1010 | 数学 | 机械工业出版社 | 徐新国 | 统计学 | 15 | 5 |
读者信息表:
读者编号 | 姓名 | 单位 | 性别 | 班级 |
R1001 | 张小航 | 计算机学院 | 男 | 08511 |
R1002 | 王文广 | 化学院 | 男 | 08511 |
R1003 | 李理 | 管理学院 | 女 | 08511 |
R1004 | 李彦宏 | 化学院 | 男 | 08512 |
R1005 | 张丽霞 | 管理学院 | 女 | 08512 |
R1006 | 王强 | 物理学院 | 男 | 07211 |
R1007 | 张宝田 | 计算机学院 | 女 | 07212 |
R1008 | 宋文霞 | 化学院 | 男 | 07611 |
R1009 | 刘芳菲 | 管理学院 | 女 | 08811 |
R1010 | 常江宁 | 计算机学院 | 女 | 08812 |
借阅信息表:
图书编号 | 读者编号 | 借阅日期 | 归还日期 |
10002 | R10003 | 2015-9-20 | 2015-10-20 |
10003 | R10003 | 2015-9-20 | 2015-10-20 |
10004 | R10003 | 2015-9-30 | 2015-10-30 |
10009 | R10003 | 2015-9-30 | 2015-10-30 |
10009 | R10007 | 2015-5-20 | 2015-6-20 |
10010 | R10007 | 2015-5-20 | 2015-6-20 |
10009 | R10009 | 2015-5-30 | 2015-6-30 |
10010 | R10009 | 2015-5-22 | 2015-6-22 |
10002 | R10009 | 2015-5-22 | 2015-6-22 |
10003 | R10009 | 2015-5-30 | 2015-6-30 |