数据库设计第六章上机题

来源：互联网发布：日本饺子知乎编辑：程序博客网时间：2024/06/06 17:39

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

BEGIN TRANSACTIONDECLARE @errorSum INTSET @errorSum=0/*--插入数据--*/INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(1,1,GETDATE(),90)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(1,1,GETDATE(),70)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(2,1,GETDATE(),67)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(2,1,GETDATE(),55)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(3,1,GETDATE(),102)--分数违反约束SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(3,4,GETDATE(),90)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(4,4,GETDATE(),56)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(4,4,GETDATE(),88)SET @errorSum=@errorSum+@@errorINSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)            VALUES(5,4,GETDATE(),40)SET @errorSum=@errorSum+@@errorINSERT 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     ENDGO

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

BEGIN TRANSACTIONDECLARE @errorSum INTSET @errorSum=0/*--查询Result表中所有Y2学员的考试成绩，保存到新表HistoreResult*/SELECT Result.* INTO HistoreResult FROM Result INNER JOIN Student ON Result.StudentNo=Student.StudentNoINNER 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.StudentNoINNER JOIN Grade ON Grade.GradeId=Student.GradeId WHERE GradeName='Y2'SET @errorSum=@errorSum+@@error/*--将Student表中所有Y2的学员记录，保存到新表HistoryStudent*/SELECT Student.* INTO HistoryStudentFROM 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     ENDGO

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

CREATE VIEW vw_student_result_infoAS  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,TotalGOSELECT * FROM vw_student_result_info

练习4：使用索引查询学生考试成绩

USE MySchoolGOIF EXISTS (SELECT name FROM sysindexes          WHERE name = 'IX_Result_StudentResult')   DROP INDEX Result.IX_Result_StudentResult  --删除索引GO--考试成绩列上创建可重复索引：填充因子为70％--CREATE INDEX IX_Result_StudentResult   ON Result(StudentResult)       WITH FILLFACTOR = 70GO--查询输出学生姓名、课程名称、考试日期和考试成绩 SELECT StudentName '学生姓名',SubjectName '课程名称',ExamDate '考试日期', StudentResult '考试成绩' FROM Subject,Student,ResultWITH (INDEX = IX_Result_StudentResult)WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId

0 0