万常选版数据库系统原理与设计第9章课后习题答案

USE ScoreDBGO-9.6 ScoreDB-(1) ClassclassNum(0,50)-Class-CREATE TABLE Class1(classNochar(6)PRIMARY KEY,/*/classNamevarchar(30)NOT NULL,/*/institutevarchar(30)NOT NULL,/*/gradesmallintNOT NULL/*/DEFAULT 0,classNumtinyintNULL/*/CHECK(classNum >=0 AND classNum <= 50)-CREATE TABLE Class2(classNochar(6)PRIMARY KEY,/*/classNamevarchar(30)NOT NULL,/*/institutevarchar(30)NOT NULL,/*/gradesmallintNOT NULL/*/DEFAULT 0,classNumtinyintNULL,/*/CONSTRAINT CK_Class_classNum CHECK(classNum >=0 AND classNum <= 50)-DROP TABLE Class1DROP TABLE Class2GO-(2) Student17-CREATE TRIGGER Student_birthdayON StudentFOR INSERT,UPDATEASIF EXISTS(SELECT * FROM inserted WHERE YEAR(GETDATE()-YEAR(birthday)<17)ROLLBACKGO-(3) CREATE TRIGGER Score_courseNoON ScoreFOR INSERTASIF NOT EXISTS(SELECT * FROM Score WHERE courseNo=(SELECT courseNo FROM Course WHERE priorCourse=(SELECT courseNo FROM inserted)ROLLBACKGO-(4) CREATE TRIGGER Student_studentNoON StudentFOR UPDATEASIF NOT EXISTS(SELECT * FROM inserted WHERE studentNo=(SELECT studentNo FROM deleted)UPDATE ScoreSET studentNo=(SELECT studentNo FROM inserted)WHERE studentNo=(SELECT studentNo FROM deleted)GO-(5) -UPDATE Class SET classNum=(SELECT COUNT(*) FROM Student WHERE classNo=Class.classNo)-DECLARE classNo char(6)=DECLARE classNum int=0-DECLARE CURSOR_Class CURSORFORSELECT classNoFROM Class-OPEN CURSOR_Class-FETCH CURSOR_Class INTO classNoWHILE(FETCH_STATUS=0)BEGINSELECT classNum=(SELECT count(*)FROM StudentWHERE classNo=classNo)UPDATE Class SET classNum=classNum WHERE CURRENT OF CURSOR_ClassFETCH CURSOR_Class INTO classNo-ENDCLOSE CURSOR_Class-DEALLOCATE CURSOR_Class-9.8 ScoreDB-(1) 5user01,user02,user03,user04,user05-A,B,C,D-A. - user01 ScoreDB user01CREATE LOGIN user01 WITH PASSWORD = 123;USE ScoreDB;CREATE USER user01 FOR LOGIN user01;GO-B. - user02 db_datareader user02CREATE LOGIN user02 WITH PASSWORD = 123;USE ScoreDB;CREATE USER user02 FOR LOGIN user02 WITH DEFAULT_SCHEMA = db_datareader;GO-C. - Teacher50 user03USE ScoreDB;CREATE MASTER KEY ENCRYPTION BY PASSWORD = 123;GOCREATE CERTIFICATE Teacher50WITH SUBJECT = Teachers,EXPIRY_DATE = 11/11/2015;GOCREATE USER user03 FOR CERTIFICATE Teacher50;GO-D. - user04 SQL Server user01 user04 USE ScoreDB ;CREATE USER user04 WITHOUT LOGIN ;GRANT IMPERSONATE ON USER:user04 TO user01 ;GO- user04 user01 EXECUTE AS USER = user04 ;GO- user01 REVERT ;GO-CREATE USER user05 WITHOUT LOGIN;GO-(2) Course user01 user05GRANT ALL ON Course TO user01, user05 WITH GRANT OPTION-(3) Class user01 user023GRANT SELECT,UPDATE ON Class(className,grade,institute) TO user01, user02-(4) user01 Courseuser03GRANT SELECT,DELETE ON Course TO user03-(5) user02 Courseuser04GRANT ALL ON Course TO user04 WITH GRANT OPTION-(6) user04 Course user05GRANT SELECT,DELETE ON Course TO user05 WITH GRANT OPTION-(7) user05 Course user02GRANT SELECT ON Course TO user02-(8) user05 CourseREVOKE SELECT,DELETE ON Course FROM user05 CASCADE-(9) user02 REVOKE ALL FROM user02-(10) user02 user03GRANT CREATE TABLE,CREATE PROCEDURE TO user02, user03-9.10 ScoreDB-(1) CREATE PROCEDURE PROC1(cNo char(6)ASSELECT studentName,courseName,ScoreFROM Student INNER JOIN Score ON Student.studentNo=Score.studentNoINNER JOIN Course ON Score.courseNo=Course.courseNoWHERE classNo=cNoORDER BY studentNameGO-EXEC PROC1 IS0801-(2) CREATE PROCEDURE PROC2(cNo char(3), count tinyint OUTPUT, avg numeric(5,1) OUTPUT)ASSELECT count=COUNT(*), avg=AVG(score)FROM ScoreWHERE courseNo=cNoGO-DECLARE count tinyint, avg numeric(5,1)-EXEC PROC2 006, count OUTPUT, avg OUTPUT-SELECT count, avg-(3) -1) CREATE PROCEDURE PROC3ASBEGIN-,CREATE TABLE #myTemp(institutevarchar(30),courseNamevarchar(30),counttinyint,avgnumeric(5,1),PRIMARY KEY(institute,courseName)DECLARE institute varchar(30)-curInstituteDECLARE curInstitute CURSORFORSELECT DISTINCT instituteFROM ClassORDER BY instituteOPEN curInstituteFETCH curInstitute INTO instituteWHILE (FETCH_STATUS=0)BEGINDECLARE courseName varchar(30),count tinyint=0,avg numeric(5,1)=0-curCourseinstituteDECLARE curCourse CURSORFORSELECT courseName,count(*),avg(score)FROM Score,CourseWHERE studentNo IN(SELECT studentNoFROM StudentWHERE classNo IN(SELECT classNoFROM ClassWHERE institute=institute) AND Score.courseNo=Course.courseNoGROUP BY courseNameOPEN curCourseFETCH curCourse INTO courseName,count,avgWHILE(FETCH_STATUS=0)BEGIN-#myTempINSERT INTO #myTemp VALUES(institute,courseName,count,avg)-FETCH curCourse INTO courseName,count,avgENDCLOSE curCourseDEALLOCATE curCourse-curInstituteFETCH curInstitute INTO instituteENDCLOSE curInstituteDEALLOCATE curInstitute-SELECT * FROM #myTemp Order BY instituteENDGO-EXEC PROC3-DROP PROCEDURE PROC3-2) OUTPUT-OUTPUTOUTPUT-OUTPUTVARYINGVARYING-CREATE PROCEDURE PROC3(institute_cursor CURSOR VARYING OUTPUT)ASSET institute_cursor = CURSORFORSELECT institute, courseName, COUNT(*), AVG(score)FROM Class,Student,Score,CourseWHERE Class.classNo=Student.classNoAND Student.studentNo=Score.studentNoAND Score.courseNo=Course.courseNoGROUP BY institute,courseNameORDER BY instituteOPEN institute_cursorGO-DECLARE MyCursor CURSOR;-EXEC PROC3 institute_cursor = MyCursor OUTPUT;-WHILE (FETCH_STATUS = 0)-BEGIN- FETCH NEXT FROM MyCursor;-END;-CLOSE MyCursor;-DEALLOCATE MyCursor;-GO-(4) - 1xxxxxx-xxxxxxxxxxxxxxxx-.-xxx-xxx.xx- 2xxxxxx-xxxxxxxxxxxxxxxx-.-xxx-xxx.xx-CREATE PROCEDURE PROC4ASBEGINSET NOCOUNT ONDECLARE courseNo char(3),courseName varchar(30)DECLARE n tinyint=0DECLARE curCourse CURSORFORSELECT courseNo,courseNameFROM CourseOPEN curCourseFETCH curCourse INTO courseNo,courseNameWHILE(FETCH_STATUS=0)BEGINSET n=n+1PRINT + STR(n,2) + + courseNamePRINT DECLARE studentNo char(7),studentName varchar(20),score numeric(5,1)DECLARE count tinyint=0,sum numeric(5,1)=0,avg numeric(5