【正确答案】正确答案：1)CREATE DATABASE学生选课库ON PRIMARY (NAME：学生选课库，FILENAME=‘c：＼msSQL＼data＼学生选课．mdf’ SIZE=4MB，MAX—SIZE：6MB，FILEROWHT：2MB) GO 2)CREATE TABIJE学生(学号CHAR(10)Pill—MARY KEY CIMSTERED，姓名 CHAR(8)，性别CHAR(2)，年龄 SMALLlNT，所在系 VARCHAR(50)) GO CREATE 。TAB[．E 课程(课程号CHAR(IO)PRIMARY KEY CLUSTERED 课程名VARCHAR(50)，先行课 VARCHAR(50))GO CRE—ATE。I'ABLE 选课(学号 CHAR(10)，课程号VARCHAR(50)，成绩 SMALlJNT，CONSTRAINT cPRIMARY KEY(学号课程，课程号)，CONSTRAINT C2 PRIMARY KEY(学号)REFERENCES 学生(学号)，CONSTRAINT FOREIGN KEY(课程号) REFFRENCES课程(课程号)) GO 3)CREATE INDEX student—ind ON 学生(学号)GO CREATE INDEX class—ind ON课程(课程号) GO CREATE INDEX select—ind ON选课(学号．课程号)GO 4)CREATE RULE value～rule ASVAI,UE lN(‘男’，‘女’) GO CREATE DEFAULT性别缺省．AS‘男’GO EXEC sp—bindrule‘value—rude’，‘学生．性别’GO EXEC 8p—binddefault‘性别缺省’，‘学生．性别’GO 5)CREATE TRIGGER sc—insert ON 选课 FORINSEW[’AS IF(SELECT COUNT(*)FROM学生．inserted，课程WHERE学生．学号=inserted．学号AND课程．课程号=in~rted．课程号)=0 ROLLBACK TRANSACTION GO CREATE TRIGGER sc—up—date ON FOR UPDATE AS IF(SELECT COUNT(})FROM学生，updated，课程WHERE学生．学号=updated．学号AND课程．课程号=updated．课程号)=0 ROLLBACK TRANSACTION GO 6)CREATE TRIGGER delete—all ON学生 FORDELETE As DELETE FROM 选课 WHERE选课．学号=deleted．学号GO 7)SELECT选课．学号，学生．姓名，学习情况中：CASE WHEN AVG(成绩)>=85 THEN‘好’ WHEN AVG(成绩)>=70 AND AVG(成绩)<85THEN‘较好’ WHEN AVG(成绩)>=60 AND AVG(成绩)<70 THEN‘一般’ WHEN AVG(成绩)<60 THEN‘较差’ FROM学生选课WHERE学生．学号=选课．学号GROUP BY选课．学号GO