--高级查询综合实训
--问题：有一个图书借阅系统
--数据库：BookStore
--三张数据表:
--分类表：categories(cid,cname)
--出版商：publishers(pid,pname,address,postcode,linkman)
--图书表：books(bid,bname,author,price,cid,pid)
--读者表：readers(rid,rname,identityid,gender)
--借阅表：lends(rid,bid,lenddate,returndate)
create database BookStore
go
use bookstore
go
if(exists(select * from sysobjects where name='categories' and type='u'))
drop table categories
go
create table categories
(
cid char(3),
cname char(20)
)
go
insert into categories values('001','计算机')
insert into categories values('002','医学')
insert into categories values('003','经济')
insert into categories values('004','会计')
insert into categories values('005','法律')
if(exists(select * from sysobjects where name='publishers' and type='u'))
drop table publishers
go
create table publishers
(
pid char(3),
pname char(20),
address char(40),
postcode char(6),
linkmand char(10)
)
go
insert into publishers values('001','人民邮电出版社','北京','100000','赵青山')
insert into publishers values('002','清华大学出版社','北京','201200','张超')
insert into publishers values('003','电子工业出版社','北京','410012','刘玉芬')
insert into publishers values('004','人民出版社','上海','140020','王永生')
insert into publishers values('005','人民教育出版社','北京','302122','陈陆祥')
go
--图书表：books(bid,bname,author,price,cid,pid)
if(exists(select * from sysobjects where name='books' and type='u'))
drop table books
go
create table books
(
bid char(5),
bname char(40),
author char(20),
price float,
cid char(3),
pid char(3)
)
go
insert into books values('00001','sql server实训','张玉祥',60.5,'001','001')
insert into books values('00002','新手学sql sever 2000','赵海山',25.3,'001','002')
insert into books values('00003','atuocad 2000i','张学',51,'001','003')
insert into books values('00004','电工基础','蔡玉生',10,'001','001')
insert into books values('00005','审计','周玫',74,'004','005')
go
--读者表：readers(rid,rname,identityid,gender)
if(exists(select * from sysobjects where name='readers' and type='u'))
drop table readers
go
create table readers
(
rid char(6),
rname char(40),
identityid char(18),
gender char(2) default '男'
)
go
insert into readers values('010001','张三','440255196502051234','男')
insert into readers values('010002','李四','440255199201021289','男')
insert into readers values('010003','赵小光','440255198106063692','男')
insert into readers values('010004','蔡波','440255197405087451','男')
insert into readers values('010005','黄梦迷','440255198506302356','女')
go
--借阅表：lends(rid,bid,lenddate,returndate)
if(exists(select * from sysobjects where name='lends' and type='u'))
drop table lends
go
create table lends
(
rid char(6),
bid char(5),
lenddate datetime,
returndate datetime
)
go
insert into lends values('010001','00001','2/20/2010','')
insert into lends values('010002','00002','4/20/2010','')
insert into lends values('010003','00001','5/1/2009','')
insert into lends values('010004','00001','3/25/2010','4/24/2010')
insert into lends values('010005','00005','3/26/2010','4/10/2010')
go
----完成如下查询内容：
--***********1.查询books表中全部记录；
select *
from books
--***********2.查询readers表中全部记录；
select *
from readers
--***********3.查询所有读者的借阅情况，只显示rid,bid字段;
select rid,bid
from lends
--4.查询所有读者的借阅情况，显示rid,rname,bid,bname,lenddate,returndate字段;
select readers.rid,rname,books.bid,bname,lenddate,returndate
from lends,readers,books
where readers.rid=lends.rid and lends.bid=books.bid
--5.统计每位读者的借书本数;
select rid,COUNT(bid)
from lends
group by rid
--***********6.查询含有“sql”的书籍记录,显示书籍所有字段信息；
select *from
books where bname like '%sql%'
--7.查询借阅书名中含有"sql"字样的书籍的读者的rid,rname,bid,bname；
select readers.rid,rname,books.bid,bname
from readers,books,lends
where readers.rid=lends.rid and books.bid=lends.bid and bname like '%sql%'
--***********8.查询每类书籍的本数，显示cid,cname和本数;
select categories.cid,cname,COUNT(*)
from categories,books
where categories.cid=books.cid
group by categories.cid,cname
--***********9.查询每类书籍的平均价格，显示cid,cname和平均价格;
select categories.cid,cname,AVG(price)
from categories,books
where categories.cid=books.cid
group by categories.cid,cname
--***********10.查询未借书的读者的rid,rname;
select rid,rname from readers
where rid not in(select rid from lends)
--11.查询每类图书被借阅的本数,显示cid,cname和被借阅的本数;
select *
from categories,books,lends
where categories.cid=books.cid and lends.bid=books.bid
group by categories.cid,cname
--***********12.查询未被借阅的图书bid,bname;
select *from books
where bid not in
(
select bid from lends
)
--13.查询借阅图书最多的读者的rid,rname和借阅本数;
--***********14.删除“张三”、“李四”两位读者的借阅记录；
delete from lends
where rid in
(select rid from readers where rname in('张三','李四'))
--15.查询每类图书最贵的两本bid,bname,price,cid,cname;
--***********16.查询每本书的被借阅的次数；
select rid,count(*)
from lends group by rid
--***********17.查询至少借了两本书的读者的rid;
select rid from lends
group by rid
having COUNT(*)>=2
--18.查询所有读者都借阅的书籍rid,rname;
--19.查询没有借阅“人民出版社”图书的读者rid,rname;
select rid,rname from readers where rid not in
(
select rid
from lends,books,publishers
where lends.bid=books.bid and publishers.pid=books.pid and pname='人民出版社'
)
--20.查询“计算机”类图书价格低于50元的图书的全部信息，按价格降序排序；
select *from categories,books
where categories.cid=books.cid and cname='计算机' and price<50
order by price desc
--***********21.删除读者“010001”借阅"00001"图书的记录。
delete from lends where rid='010001' and bid='00001'