《SQL_Server_2000_实验指导》课后作业

《《SQL_Server_2000_实验指导》课后作业》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。

1、实验4：习题基于jiaoxuedb实验1、对个表创建实体完整性;USEjiaoxuedbALTERTABLEStudentADDCONSTRAINTPK_SNOPRIMARYKEY(Sno)ALTERTABLETeacherADDCONSTRAINTPK_TNOPRIMARYKEY(Tno)ALTERTABLECourseADDCONSTRAINTPK_CNOPRIMARYKEY(Cno)ALTERTABLEStudentADDCONSTRAINTUNIQUE_SUNIQUE(Sno)ALTERTABLETeacherADDCONSTRAINTUNIQUE

2、_TUNIQUE(Tno)ALTERTABLECourseADDCONSTRAINTUNIQUE_CUNIQUE(Cno)2.创建表之间的参照完整性。USEjiaoxuedbALTERTABLESCWITHNOCHECKADDCONSTRAINTFK_S_SCFOREIGNKEY(Sno)ONDELETECASCADEREFERENCESStudent(Sno)ALTERTABLESCWITHNOCHECKADDCONSTRAINTFK_C_SCFOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETECASCADEALTER

3、TABLETCWITHNOCHECKADDCONSTRAINTFK_T_TCFOREIGNKEY(Tno)REFERENCESTeacher(Tno)ONDELETECASCADEALTERTABLETCWITHNOCHECKADDCONSTRAINTFK_C_TCFOREIGNKEY(Cno)REFERENCESCourse(Cno)ONDELETECASCADE3、用不同的方法创建下面约束。(1)“姓名”字段取唯-值:USEjiaoxuedbALTERTABLEStudentADDCONSTRAINTUNIQUE_SNAUNIQUE(Sname)ALT

4、ERTABLETeacherADDCONSTRAINTUNIQUE_TNAUNIQUE(Tname)(1)“性别”字段収值为：'男'或妝;默认值：'男USEjiaoxuedbALTERTABLETeacherWITHCHECKADDCONSTRAINTCHECK_T_SEXCHECK(SEX=1SfORSEX=‘女')ALTERTABLETeacherADDCONSTRAINTDF_T_SEXDEFAULT■男'FORSexALTERTABLEStudentWITHCHECKADDCONSTRAINTCHECK_S_SEXCHECK(SEX=1SfORSE

5、X=‘女')ALTERTABLEStudentADDCONSTRAINTDF_S_SEXDEFAULT•男'FORSex(2)“年龄”和“分数”字段取值限定为：0700之间。USEjiaoxuedbALTERTABLEStudentWITHCHECKADDCONSTRAINTCHECK_S_AGECHECK(AGE>0ANDAGE<100)ALTERTABLETeacherWITHCHECKADDCONSTRAINTCHECK_T_AGECHECK(AGE>0ANDAGE<100)实验5:习题1、对教师衣Teacher中的教师号Tno创建聚族索引，并按降序

6、排列。USEjiaoxuedbCREATECLUSTEREDINDEXIND_TNOONTeacher(TnoDESC)2.对学生选课表SC,先按课号Cn。升序排列，再按成绩Score降序排列。Cno升序：USEjiaoxuedbCREATECLUSTEREDINDEXIND_SC_CNOONSC(CnoASC)Score降序：USEjiaoxuedbCREATECLUSTEREDINDEXIND_SC_SCOREONSC(ScoreDESC)3、对表Student中的学号Sno创建唯一索引,并按升序排列。USEjiaoxuedbCREATEUNIQUEI

7、NDEXIND_S_SNOONStudent(SnoASC)实验6：习题基于jiaoxuedb实验1、创建成绩视图SCOREVIEW,包含学号SNO,姓名SN,课程名CN,成绩SCORE。USEjiaoxuedbGOCREATEVIEWSCORE_VIEWASSELECTStudent・Sno,Sname,Cname,ScoreFROMStudentzCourse,SCWHEREStudent.Sno=SC・SnoANDSC.Cno=Course.CnoGO2、创建一个计算机系学生名单视图S_VIEW,包含学号SNO,姓名SN,性别SEX。USEjiaox

8、uedbGOCREATEVIEWS_VIEWASSELECTSno