第六章上机练习

练习1：批量插入学生考试成绩

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入数据--*/
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(1,1,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(1,1,GETDATE(),70)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(2,1,GETDATE(),67)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(2,1,GETDATE(),55)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(3,1,GETDATE(),102)--分数违反约束
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(3,4,GETDATE(),90)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(4,4,GETDATE(),56)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(4,4,GETDATE(),88)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(5,4,GETDATE(),40)
SET @errorSum=@errorSum+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
            VALUES(5,4,GETDATE(),65)
SET @errorSum=@errorSum+@@error

/*--根据是否有错误，确定事务是提交还是撤销--*/
IF(@errorSum<>0) --如果有错误
  BEGIN
    PRINT '插入失败，回滚事务'
    ROLLBACK TRANSACTION
  END 
ELSE
  BEGIN
    PRINT '插入成功，提交事务'
    COMMIT TRANSACTION  
  END
GO

练习2：办理毕业学生离校手续

BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--查询Result表中所有Y2学员的考试成绩，保存到新表HistoreResult*/
SELECT Result.* INTO HistoreResult
FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error

/*--删除Result表中所有Y2学员的考试成绩*/
DELETE  Result FROM  Result JOIN Student ON Result.StudentNo=Student.StudentNo
INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error

/*--将Student表中所有Y2的学员记录，保存到新表HistoryStudent*/
SELECT Student.* INTO HistoryStudent
FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error

/*--删除Studet表中所有Y2学员记录*/
DELETE Student FROM Student
INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @errorSum=@errorSum+@@error

/*--根据是否有错误，确定事务是提交还是撤销--*/
IF (@errorSum<>0) --如果有错误
  BEGIN
    PRINT '插入失败，回滚事务'
    ROLLBACK TRANSACTION
  END 
ELSE
  BEGIN
    PRINT '插入成功，提交事务'
    COMMIT TRANSACTION  
  END
GO

练习3：查看各学生各学期总成绩

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,SubjectNo,MAX(ExamDate)  ExamDate
         FROM Result
         GROUP BY StudentNo,SubjectNo) tmp
      ON r.ExamDate=tmp.ExamDate
      AND r.SubjectNo = tmp.SubjectNo AND r.StudentNo = tmp.StudentNo
      INNER JOIN Subject sub ON sub.SubjectNo = r.SubjectNo
      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：使用索引查询学生考试成绩
1.USE MySchool 
2.GO 
3. 
4.IF EXISTS (SELECT name FROM sysindexes 
5.          WHERE name = 'IX_Result_StudentResult') 
6.   DROP INDEX Result.IX_Result_StudentResult  --删除索引 
7.GO 
8. 
9.--考试成绩列上创建可重复索引：填充因子为70％-- 
10.CREATE INDEX IX_Result_StudentResult 
11.   ON Result(StudentResult) 
12.       WITH FILLFACTOR = 70 
13.GO 
14. 
15. 
16.--查询输出学生姓名、课程名称、考试日期和考试成绩  
17.SELECT StudentName '学生姓名',SubjectName '课程名称',ExamDate '考试日期', StudentResult '考试成绩' FROM Subject,Student,Result 
18.WITH (INDEX = IX_Result_StudentResult) 
19.WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId