SQL-Server实验指导-课后答案

目录创建数据库JXSK�...................................................................................................................-1-创建JXSK中的表�...................................................................................................................-1-插入JXSK库中各表值�...........................................................................................................-1-创建数据库JIAOXUEDB�..........................................................................................................-3-创建JIAOXUEDB中的表�..........................................................................................................-3-插入JIAOXUEDB库中各表值�..................................................................................................-3-实验4�习题............................................................................................................................-5-实验5�习题............................................................................................................................-7-实验6�习题............................................................................................................................-7-实验7�习题............................................................................................................................-8-实验8�习题..........................................................................................................................-12-实验9�习题..........................................................................................................................-17-实验10�习题........................................................................................................................-21-实验11�习题........................................................................................................................-24-实验12�习题........................................................................................................................-26-实验13�习题........................................................................................................................-39--1-创建数据库jxsk�CREATEDATABASEjxskON(NAME=jxsk_dat,FILENAME='d:\jxsk_dat.mdf')LOGON(NAME=jxsk_log,FILENAME='d:\jxsk_log.ldf')GO创建jxsk中的表�USEjxskCREATETABLES(SNOCHAR(2)NOTNULL,SNCHAR(8)NOTNULL,SEXCHAR(2)NULL,AGETINYINTNULL,DEPTCHAR(10)NULL)CREATETABLET(TNOCHAR(2)NOTNULL,TNCHAR(8)NOTNULL,SEXCHAR(2)NULL,AGETINYINTNULL,PROFCHAR(10)NULL,SALSMALLINTNULL,COMMSMALLINTNULL,DEPTCHAR(10)NULL)CREATETABLEC(CNOCHAR(2)NOTNULL,CNCHAR(10)NOTNULL,CTTINYINTNULL,ID_TCTINYINTNULL)CREATETABLESC(SNOCHAR(2)NOTNULL,CNOCHAR(2)NOTNULL,SCORETINYINTNULL)CREATETABLETC(TNOCHAR(2)NOTNULL,CNOCHAR(2)NOTNULL)插入jxsk库中各表值�USEjxskINSERTINTOSVALUES('S2','钱尔','男',18,'信息')INSERTINTOSVALUES('S3','孙姗','女',20,'信息')-2-INSERTINTOSVALUES('S4','李思','男',21,'自动化')INSERTINTOSVALUES('S5','周武','男',19,'计算机')INSERTINTOSVALUES('S6','吴丽','女',20,'自动化')INSERTINTOSVALUES('S7','李奇','男',18,'外语')INSERTINTOSVALUES('S8','古明','男',NULL,'计算机')INSERTINTOSVALUES('S9','赵亦','女',17,'计算机')INSERTINTOTVALUES('T5','张兰','女',39,'副教授',1300,2000,'信息')INSERTINTOTVALUES('T4','张雪','女',51,'教授',1600,3000,'自动化')INSERTINTOTVALUES('T3','刘伟','男',30,'讲师',900,1200,'计算机')INSERTINTOTVALUES('T2','王平','女',28,'讲师',800,1200,'信息')INSERTINTOTVALUES('T1','李力','男',47,'教授',1500,3000,'计算机')INSERTINTOCVALUES('C1','程序设计',60,1)INSERTINTOCVALUES('C2','微机原理',80,2)INSERTINTOCVALUES('C3','数字逻辑',60,3)INSERTINTOCVALUES('C4','数据结构',80,4)INSERTINTOCVALUES('C5','数据库',60,5)INSERTINTOCVALUES('C6','编译原理',60,6)INSERTINTOCVALUES('C7','操作系统',60,7)INSERTINTOSCVALUES('S2','C5',57)INSERTINTOSCVALUES('S2','C6',80)INSERTINTOSCVALUES('S2','C7',NULL)INSERTINTOSCVALUES('S3','C1',75)INSERTINTOSCVALUES('S3','C2',70)INSERTINTOSCVALUES('S3','C4',85)INSERTINTOSCVALUES('S4','C1',93)INSERTINTOSCVALUES('S4','C2',85)INSERTINTOSCVALUES('S4','C3',83)INSERTINTOSCVALUES('S5','C2',89)INSERTINTOSCVALUES('S9','C1',90)INSERTINTOSCVALUES('S9','C2',85)INSERTINTOTCVALUES('T2','C5')INSERTINTOTCVALUES('T3','C1')INSERTINTOTCVALUES('T3','C5')INSERTINTOTCVALUES('T4','C2')INSERTINTOTCVALUES('T4','C3')INSERTINTOTCVALUES('T5','C5')INSERTINTOTCVALUES('T5','C7')INSERTINTOTCVALUES('T1','C1')INSERTINTOTCVALUES('T1','C4')-3-创建数据库jiaoxuedb�CREATEDATABASEjiaoxuedbON(NAME=jiaoxuedb_dat,FILENAME='d:\jiaoxuedb_dat.mdf')LOGON(NAME=jiaoxuedb_log,FILENAME='d:\jiaoxuedb_log.ldf')GO创建jiaoxuedb中的表�USEjiaoxuedbCREATETABLEStudent(SnoCHAR(10)NOTNULL,SnameCHAR(10)NOTNULL,SexCHAR(2)NULL,AgeTINYINTNULL,DeptCHAR(10)NULL)CREATETABLETeacher(TnoCHAR(10)NOTNULL,TnameCHAR(10)NOTNULL,SexCHAR(2)NULL,AgeTINYINTNULL,ProfCHAR(10)NULL,SalSMALLINTNULL,CommSMALLINTNULL,DeptCHAR(10)NULL)CREATETABLECourse(CnoCHAR(10)NOTNULL,CnameCHAR(10)NOTNULL,ChourTINYINTNULL,CcreditTINYINTNULL)CREATETABLESC(SnoCHAR(10)NOTNULL,CnoCHAR(10)NOTNULL,SCORETINYINTNULL)CREATETABLETC(TnoCHAR(10)NOTNULL,CnoCHAR(10)NOTNULL)插入jiaoxuedb库中各表值�USEjiaoxuedb/*InsertvaluestoStudentTable*/INSERTINTOStudentVALUES('001101','宋大方','男',19,'计算机')-4-INSERTINTOStudentVALUES('002102','李王','男',20,'信息')INSERTINTOStudent