--(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)查询平均价
实验答案(二)
最新推荐文章于 2024-01-04 20:29:45 发布
这篇博客展示了多个SQL查询示例,涉及学生、借书、图书等信息的统计,包括借书超过2本的学生、借阅了特定书籍的学生、每个班级的借书总数、图书分类的平均价格、过期未还的图书、包含特定关键词的图书、价格最高的图书、借阅特定书籍但未借其他书籍的学生、未借书的学生、各系借书总数、各出版社图书数量及比例分析等。同时提到了在Library数据库中创建和使用函数的概念。
摘要由CSDN通过智能技术生成