实验答案（二）

--（8）找出借书超过2本的学生学号、姓名和所借图书册数
select a.sno,a.bname,count(c.sno) as '数量' from student as a , borrow as c where a.sno=c.sno  group by c.sno,a.sno,a.bname having count(c.sno)>=2
select a.sno,a.bname,count(c.sno)as '数量' from student as a join borrow as c on a.sno=c.sno group by a.bname,a.sno having count(c.sno)>=2
--（9）查询借阅了“操作系统”一书的学生，输出学号、姓名、班号
select a.sno,a.bname,a.cno from student as a,borrow as c,book as b where a.sno=c.sno and c.bno=b.bno and b.bname=N'操作系统'
--（10）查询每个班的借书总数
select a.cno,count(c.bno) from student as a,borrow as c where a.sno=c.sno group by a.cno
--（11）用图书编号以前3位数字进行分类，查询每类图书的平均价
select substring(cast(bno as nvarchar(6)),1,3) as '图书类别', avg(price) as '平均价' from book group by substring(cast(bno as nvarchar(6)),1,3)
--（12）查询平均价