S2 一本書 Day06上機練習1-4


BEGIN TRANSACTION
DECLARE @errorSum INT
SET @errorSum=0
/*--插入數據--*/
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),90)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(1,1,GETDATE(),70)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),67)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(2,1,GETDATE(),55)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(3,1,GETDATE(),102)--分數違反約束
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(3,4,GETDATE(),90)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),56)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(4,4,GETDATE(),88)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),40)
SET @[email protected]+@@error
INSERT INTO Result(StudentNo,SubjectNo,ExamDate,StudentResult)
VALUES(5,4,GETDATE(),65)
SET @[email protected]+@@error

/*--根據是否有錯誤,確定事務是提交還是撤銷--*/
IF(@errorSum<>0) --如果有錯誤
BEGIN
PRINT '插入失敗,回滾事務'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功,提交事務'
COMMIT TRANSACTION
END
GO

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 @[email protected]+@@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 @[email protected]+@@error

/*--將Student表中所有Y2的學員記錄,保存到新表HistoryStudent*/
SELECT Student.* INTO HistoryStudent
FROM Student INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @[email protected]+@@error

/*--刪除Studet表中所有Y2學員記錄*/
DELETE Student FROM Student
INNER JOIN Grade ON Grade.GradeId=Student.GradeId
WHERE GradeName='Y2'
SET @[email protected]+@@error

/*--根據是否有錯誤,確定事務是提交還是撤銷--*/
IF (@errorSum<>0) --如果有錯誤
BEGIN
PRINT '插入失敗,回滾事務'
ROLLBACK TRANSACTION
END
ELSE
BEGIN
PRINT '插入成功,提交事務'
COMMIT TRANSACTION
END
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,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


USE MySchool
GO

IF 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 = 70
GO

--查詢輸出學生姓名、課程名稱、考試日期和考試成績
SELECT StudentName '學生姓名',SubjectName '課程名稱',ExamDate '考試日期', StudentResult '考試成績' FROM Subject,Student,Result
WITH (INDEX = IX_Result_StudentResult)
WHERE StudentResult BETWEEN 80 AND 90 AND Student.StudentNo = Result.StudentNo AND Subject.SubjectId = Result.SubjectId

0 個評論

要回覆文章請先登錄註冊