sqlserver 4

use 借还书管理系统库

--1.查询 借书人信息表 中 老师的人数 和学生的人数


select 借书人类型,count(*) as 人数 from 借书人信息表 group by 借书人类型

--2.查询出 最贵的一本书

select * from 图书资料表 where 图书单价 in (select max(图书单价)from 图书资料表)

--3.查询 四月份以前都借出过哪些书。

select 图书名称 from 图书资料表 a inner join 借还书表 b on a.图书编号=b.图书编号 where b.借书日期 <'2007-3-31 23:59:59'


--4.更改 <时尚健康>的图书单价为55元

update 图书资料表 set 图书单价=55 where 图书名称='时尚健康'

select * from 图书资料表


--5.查询 科比 这个操作员 经手办过多少本书


select count(*) from 借还书表 where 操作员编号 =(select 操作员编号 from 操作员资料表 where 操作员姓名='科比')


select 图书名称 from 图书资料表 where 图书编号 in (select 图书编号 from 借还书表 where 操作员编号 =(select 操作员编号 from 操作员资料表 where 操作员姓名='科比'))---列出图书名称

--6.查询 梁天 借过哪些书。



select * from 图书资料表 where 图书编号 in (select 图书编号 from 借还书表 where 借书人编号=(select  借书人编号 from 借书人信息表 where 借书人姓名='梁天'))


--7.查询哪个种类的书最受欢迎。（查询哪种类别的书借阅的次数最多。）


select  top 2  次数 from (select top 1 count(*) as 次数 ,图书类别 from 图书资料表 a inner join 借还书表 b on a.图书编号=b.图书编号 group by 图书类别 order by 次数 desc) temp--子句查询时用到order by 必须用top


select  图书类别, 次数 from (select  count(*) as 次数,图书类别  from 图书资料表 a inner join 借还书表 b on a.图书编号=b.图书编号 group by 图书类别 ) temp where temp.次数=(select top 1 count(*) as 次数 from 图书资料表 a inner join 借还书表 b on a.图书编号=b.图书编号 group by 图书类别 order by 次数 desc)


--8.查询被罚款金额最高的为哪位同学

select 罚款金额 from 罚款记录表  a where a.罚款金额=(select max(罚款金额) from 罚款记录表) ---取得最高金额

select 借还书编号 from 罚款记录表  a where a.罚款金额=(select max(罚款金额) from 罚款记录表)--取得罚款最高的借还书编号


select 借书人姓名 ,借书日期 ,备注 from 借书人信息表 a inner join 借还书表 b on a.借书人编号=b.借书人编号 where b.借还书编号=(select 借还书编号 from 罚款记录表  a where a.罚款金额=(select max(罚款金额) from 罚款记录表))


--9.查询每位同学都是哪个校区的。


select 借书人姓名 as 姓名, 系别名称, 所属校区 from 借书人信息表 a left join 系别信息表 b on a.所属系别编号=b.系别编号


--10.查询出损坏的书的名称及编号



select 图书编号 from 借还书表 where 损坏情况!='无损坏'--取得损坏书的编号

                ---select 图书编号,图书名称 from 图书资料表 a right join 借还书表 b on a.图书编号=b.图书编号 where b.图书编号 in(select 图书编号 from 借还书表 where 损坏情况!='无损坏')  列名 '图书编号' 不明确要指明哪一个表


select b.图书编号,图书名称 from 图书资料表 a right join 借还书表 b on a.图书编号=b.图书编号 where b.图书编号 in(select 图书编号 from 借还书表 where 损坏情况!='无损坏')



--11.查询各个出版社 图书的数量    比如（人民出版社   50本书）


select sum(图书数量) as 数量,图书出版社 from 图书资料表 group by 图书出版社


--12.查询出。学校购买哪本书花的钱最多。

select max(图书单价*图书数量) as 总价 from 图书资料表  --取得最高价图书

select 图书单价*图书数量 as 总价,图书名称 from 图书资料表   --将图书名称，和总价放入一张表

select 图书名称 ,总价 from (select 图书单价*图书数量 as 总价,图书名称 from 图书资料表) temp where 总价=(select max(图书单价*图书数量) as 总价 from 图书资料表)



--13.创建一个视图：要求 包含：借书人编号,借书人姓名,所属系别编号,借还书编号,操作员编号,借书日期,操作员姓名,图书编号, 图书名称,图书数量, 罚款类别,罚款金额


--14.查询每类图书 所拥有的数量


select 图书类别,sum(图书数量)数量 from 图书资料表 group by 图书类别


--15.查询出每个同学 被罚款的总钱数



select sum(罚款金额)as 总钱数,借书人编号 from 借还书表 a left join 罚款记录表 b on a.借还书编号=b.借还书编号 group by 借书人编号 --取得借书人编号，和总钱数

select 借书人姓名,总钱数 from 借书人信息表 a left join (select sum(罚款金额)as 总钱数,借书人编号 from 借还书表 a left join 罚款记录表 b on a.借还书编号=b.借还书编号 group by 借书人编号) b on a.借书人编号= b.借书人编号 where 借书人类型='学生'


--16.查询出所有 姓'梁' 的人的所有信息

select * from 借书人信息表 a left join 系别信息表 b on a.所属系别编号=b.系别编号  where 借书人姓名 like '梁%'-- 将学生信息与院系连接

select c.借还书编号 , 借书人编号, 图书编号 , 借书日期 , 还书日期 , 损坏情况 , 操作员编号 , c.备注 ,ID ,     
         罚款类别 ,
         罚款金额,
         罚款日期,
         经手人 ,
         d.备注 as 罚备注  from 借还书表 c left join 罚款记录表 d on c.借还书编号=d.借还书编号 --将借书信息与罚款信息连接


--  select * from ()e left join () f on e. =f.

select * from (select * from 借书人信息表 a left join 系别信息表 b on a.所属系别编号=b.系别编号  where 借书人姓名 like '梁%')e left join
        (select c.借还书编号 , 借书人编号, 图书编号 , 借书日期 , 还书日期 , 损坏情况 , 操作员编号 , c.备注 ,ID ,     
         罚款类别 ,
         罚款金额,
         罚款日期,
         经手人 ,
         d.备注 as 罚备注 from 借还书表 c left join 罚款记录表 d on c.借还书编号=d.借还书编号) f
on e.借书人编号 =f.借书人编号