T-Sql Server 数据库练习

条件查询：

1. 在GRADE表中查找80-90份的学生学号和分数

select 学号,分数 from grade where 分数 between 80 and 90

2. 在GRADE 表中查找课程编号为003学生的平均分

select avg(分数) from grade where 课程编号=‘003’

3. 在GRADE 表中查询学习各门课程的人数

Select课程编号,count(学号) as 人数from grade group by 课程编号

4. 查询所有姓张的学生的学号和姓名

select 姓名,学号 from student_info where 姓名 like ‘张%’

嵌套查询：
1、查询和学号’0001’的这位同学性别相同的所有同学的姓名和出生年月

select 姓名,出生年月 from student_info where 性别 in(select 性别 from student_infowhere sno=‘0001’)

2、查询所有选修课程编号为0002 和0003的学生的学号、姓名和性别

select 学号,姓名,性别 from student_info where 学号 in(select 学号 from grade where 课程编号=‘0002’ and 学号 in(select 学号 from grade where 课程编号=‘0001’))

3、查询出学号为0001的学生的分数比0002号学生最低分高的课程编号的课程编号和分数

select 课程编号, 分数 from grade where 学号=‘0001’ and 分数>(select min(分数) from gradewhere 学号=‘0002’)

多表查询：
1、查询分数在80-90分的学生的学号、姓名、分数
select student_info.学号,student_info.姓名,grade.分数 from student_info,grade wheregrade.分数 between 80 and 90

2、查询学习了’C语言’课程的学生学号、姓名和分数
select student_info.学号,student_info.姓名,grade.成绩fromstudent_info,grade,curriculum where student_info.学号=grade.学号and grade.课程号=curriculum.课程号and curriculum.课程名=‘C语言’

3、查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选课的学生总成绩为空。
select grade.学号,student_info.姓名,sum(grade.成绩) as 总成绩 from student_info,gradewhere grade.学号=student_info.学号 group by grade.学号,student_info.姓名

本题用到下面三个关系表：
CARD 借书卡: (CNO 卡号，NAME 姓名，CLASS 班级)
BOOKS 图书: (BNO 书号，BNAME 书名,AUTHOR 作者，PRICE 单价，QUANTITY 库存册数 )
BORROW 借书记录: (CNO 借书卡号，BNO 书号，RDATE 还书日期
备注：限定每人每种书只能借一本；库存册数随借书、还书而改变。
要求实现如下处理：

1. 写出自定义函数，要求输入借书卡号能得到该卡号所借书金额的总和

CREATE FUNCTION getSUM(@CNO int)RETURNS intASBEGINdeclare @sum intselect @sum=sum(price) from BOOKS where bno in (select bno from BORROW where cno=@CNO)return @sumENDGO

2． 找出借书超过 5 本的读者,输出借书卡号及所借图书册数。

select CNO,count(BNO) as 借书数量 from BORROW group by CNO having count(BNO)>3

3． 查询借阅了"水浒"一书的读者，输出姓名及班级。

select name,class from card where cno in( select cno from borrow where bno in(select bno from BOOKS where bname=‘水浒’))

4． 查询过期未还图书，输出借阅者（卡号）、书号及还书日期。

select CNO,BNO,RDATE from borrow where getdate()>RDATE

5． 查询书名包括"网络"关键词的图书，输出书号、书名、作者。

select bno,bname,author from books where bname like ‘网络%’

6． 查询现有图书中价格最高的图书，输出书名及作者。

select bname,author from books where price in(select max(price) from books )

7． 查询当前借了"计算方法"但没有借"计算方法习题集"的读者，输出其借书卡号，并按卡号降序排序输出。

select cno from borrow where bno in (select bno from books where bname=‘计算方法’) andcno not in ( select cno from borrow where bno in(select bno from books where bname=‘计算方法习题集’)) order by cno desc

或

SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNO AND b.BNAME=N’计算方法’AND NOT EXISTS(SELECT * FROM BORROW aa,BOOKS bbWHERE aa.BNO=bb.BNOAND bb.BNAME=N’计算方法习题集’AND aa.CNO=a.CNO)ORDER BY a.CNO DESC

8． 将"C01"班同学所借图书的还期都延长一周。

update borrow set rdate=dateadd(day,7,rdate) from BORROW where cno in(select cno fromcard where class=‘一班’)

9． 从 BOOKS 表中删除当前无人借阅的图书记录。

DELETE A FROM BOOKS aWHERE NOT EXISTS(SELECT * FROM BORROWWHERE BNO=a.BNO)

10．如果经常按书名查询图书信息，请建立合适的索引。(这个不确定对不 90%应该是对的 自己看了下书写的)

CREATE CLUSTERED INDEX IDX_BOOKS_BNAME ON BOOKS(BNAME)

11．在 BORROW 表上建立一个触发器，完成如下功能：如果读者借阅的书名是"数据库技术及应用"，就将该读者的借阅记录保存在 BORROW_SAVE 表中（注 ORROW_SAVE 表结构同 BORROW 表）。

CREATE TRIGGER TR_SAVE ON BORROWFOR INSERT,UPDATEASIF @@ROWCOUNT>0INSERT BORROW_SAVE SELECT i.*FROM INSERTED i,BOOKS bWHERE i.BNO=b.BNOAND b.BNAME=N’数据库技术及应用’

12．建立一个视图，显示"力 01"班学生的借书信息（只要求显示姓名和书名）。

CREATE VIEW V_VIEWASselect name,bnamefrom books,card,borrowwhere borrow.cno=card.cno and borrow.bno=books.bno and class=‘一班’

13．查询当前同时借有"计算方法"和"组合数学"两本书的读者，输出其借书卡号，并按卡号升序排序输出。

select a.cno from borrow a,borrow bwhere a.cno=b.cno anda.bno in(select bno from books where bname=‘计算方法’) andb.bno in(select bno from books where bname=‘组合数学’)order by a.cno desc或SELECT a.CNOFROM BORROW a,BOOKS bWHERE a.BNO=b.BNOAND b.BNAME IN(‘计算方法’,‘组合数学’)GROUP BY a.CNOHAVING COUNT(*)=2ORDER BY a.CNO DESC