- --上机1
- use MySchool
- go
- begin transaction
- declare @errornum int
- set @errornum=0
- insert into Result values('23219',1,90,'2013-09-12')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2013-09-13')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2014-09-12')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2013-09-11')
- set @errornum+=@@ERROR
- insert into Result values('23219',1,90,'2003-09-12')
- set @errornum+=@@ERROR
- if @errornum<>0
- begin
- print '操作失败,回滚事务'
- rollback transaction
- end
- else
- begin
- print '操作成功,保存事务'
- commit transaction
- end
- --上机2
- go
- begin transaction
- declare @errornum int
- set @errornum=0
- select * into historyresult from Result
- where StudentNo in(select StudentNo from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2'))
- set @errornum+=@@ERROR
- delete from Result
- where StudentNo in(select StudentNo from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2'))
- set @errornum+=@@ERROR
- select * into historystudent from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2')
- set @errornum+=@@ERROR
- delete from Student
- where GradeId=(select GradeId from Grade where GradeName='Y2')
- set @errornum+=@@ERROR
- if @errornum<>0
- begin
- print '操作失败,回滚事务'
- rollback transaction
- end
- else
- begin
- print '操作成功,保存事务'
- commit transaction
- end
- --上机3
- go
- CREATE VIEW vw_student_result_info
- AS
- SELECT 姓名=StudentName,学号=Student.StudentNo,
- 联系电话=Phone,学期=GradeName,成绩=Total
- FROM Student
- LEFT OUTER JOIN (
- SELECT r.StudentNo,GradeName,SUM(StudentResult) Total
- FROM Result r
- INNER JOIN (
- SELECT StudentNo,SubjectId,MAX(ExamDate) ExamDate
- FROM Result
- GROUP BY StudentNo,Subjectid) tmp
- ON r.ExamDate=tmp.ExamDate
- AND r.Subjectid = tmp.Subjectid AND r.StudentNo = tmp.StudentNo
- INNER JOIN Subject sub ON sub.Subjectid = r.Subjectid
- INNER JOIN Grade g ON g.GradeId = sub.GradeId
- GROUP By r.StudentNo,GradeName ) TmpResult2
- ON Student.StudentNo = TmpResult2.StudentNo
- GROUP BY StudentName,Student.StudentNo,Phone,GradeName,Total
- GO
- SELECT * FROM vw_student_result_info
- --上机4
- go
- create nonclustered index index_result
- on result(studentresult)
- select studentname,Subjectname,ExamDate,StudentResult from Result
- with(index=index_result)
- inner join Student on Student.StudentNo=Result.StudentNo
- inner join Subject on Subject.SubjectId=Result.SubjectId
- where StudentResult between 80 and 90
第六章:事务,视图和索引
最新推荐文章于 2021-04-12 22:23:12 发布