SQL第四章课后习题答案.docx

SQL第四章课后习题答案USEstudentGOCREATETABLEstud_info(stud_idCHAR(10)NOTNULL,nameNVARCHAR(4)NOTNULL,birthdayDATETIME,genderNCHAR(1),addressNVARCHAR(20),telcodeCHAR(12),zipcodeCHAR(6),markDECIMAL(3,0)-1、利用资源管理器创立表-2.、删除表和创立表usestudentgodroptablestud_infogousestudentgodroptableclassroom_infogousestudentgodroptabledept_codegousestudentgodroptablelesson_infogousestudentgodroptablespecialty_codegousestudentgodroptablestaffroom_infogousestudentgodroptablestud_gradegousestudentgodroptableteacher_infogousestudentgodroptableteacher_schedulegoUSEstudentGOCREATETABLEstud_info(stud_idCHAR(10)NOTNULL,nameNVARCHAR(4)NOTNULL,birthdayDATETIME,genderNCHAR(1),addressNVARCHAR(20),telcodeCHAR(12),zipcodeCHAR(6),markDECIMAL(3,0)USEstudentGOCREATETABLEteacher_info(teacher_idCHAR(10)NOTNULL,nameNVARCHAR(4)NOTNULL,genderNCHAR(1),ageint,tech_titlenchar(5),telephonevarchar(12),salarydecimal(7),course_idchar(10)usestudentgocreatetablestud_grade(stud_idchar(10)notnull,namenvarchar(4)notnull,course_idchar(10),gradenumeric(4)usestudentgocreatetablelesson_info(course_idchar(10)notnull,course_namenvarchar(12)notnull,course_typenchar(2)notnull,course_markintnotnull,course_timedecimal(3)usestudentgocreatetableteach_schedule(course_idchar(10)notnull,course_datedatetime,course_weekint,room_idchar(6),deptcodechar(2),teacher_idchar(6)usestudentgocreatetabledept_code(deptcodechar(2)notnull,deptnamenvarchar(10),)usestudentgocreatetablespecialty_code(speccodechar(10)notnull,specnamenvarchar(10)usestudentgocreatetablestaffroom_info(jysh_idchar(4)notnull,jysh_namenvarchar(10),jysh_typenchar(2),jysh_leadernvarchar(4)usestudentgocreatetableclassroom_info (room_idchar(6)notnull,room_namenvarchar(8),room_typenvarchar(6),room_devicenvarchar(10),room_sizeint)usestudentgocreatetablestud_exam(exam_coursenvarchar(10),exam_typenvarchar(6),exam_quantityint,exam_timedatetime,exam_idchar(10),exam_teachernvarchar(10),exam_classroomnvarchar(8),exam_rulenvarchar(10),exam_jilunvarchar(20)-、分别使用对象资源管理器和T语句修改表构造-(1)、将teacher_info表中的teacher_id列长度从6个字符改变到8个字符altertableteacher_infoaltercolumnteacher_idchar(8)-(2)、在dept_code表中加最后一列dept_memo，其数据类型为可变长度字符型，30位altertabledept_codeadddept_memonvarchar(30)-(3)、向stud_info表中增加“入学时间列，其数据类型为日期型altertablestud_infoaddschooldatedatetime-假设在创立数据表lesson_info时错将课程学分course_mark设计为char(2),现欲修改为decimal(3,1)altertablelesson_infoaltercolumncourse_markdecimal(3,1)-(4).建立约束-(4)1、为student数据库的stud_info表中的mark列建立check约束altertablestud_infoaddconstraintch_markcheck(markbetween560and600);-(4).2、使用T-SQL语句创立表stud_info，设置stud_info为primarykey约束usestudentgodroptablestud_infogoUSEstudentGOCREATETABLEstud_info(stud_idCHAR(10)NOTNULLconstraintpk_stud_idprimarykey,nameNVARCHAR(4)NOTNULL,birthdayDATETIME,genderNCHAR(1),addressNVARCHAR(20),telcodeCHAR(12),zipcodeCHAR(6),markDECIMAL(3,0)-(4).3为表stud_info的telcode列建立UNIQUE约束altertablestud_infoaddconstraintun_telephoneunique(telcode)-(4).4为stud_grade表中的stud_id列建立foreignkey约束altertablestud_gradeaddconstraintfk_stud_idforeignkey(stud_info)referencesstud_info(stud_id)-独立实践：altertablestud_infoadddefault(null)forzipcode-(5).插入数据-(5).1insertintostud_infovalues('0401010811',N'张源','12-05-1986',N'男',N'北京市海淀区','010-*','100080',560)select*fromstud_info-独立实践：USEstudentGOINSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401010811',N'张源','12-05-1986',N'男',N'北京市海淀区','010-*','100080',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401020201',N'赵明','08-06-1986',N'男',N'上海市浦东区','021-*','202100',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401030101',N'王刚','01-02-1986',N'男',N'天津市南开区','022-*','300000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401050201',N'陈红','10-25-1986',N'女',N'武汉市汉口区','027-*','430000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401040112',N'孙强','06-07-1986',N'男',N'重庆市沙坪坝','023-*','400000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401020203',N'李伟','09-01-1986',N'男',N'北京市大兴县','010-*','102600',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401010634',N'钱昆','12-06-1986',N'男',N'广州市海珠区','020-*','510000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401030213',N'郑芳','08-09-1986',N'女',N'江苏省南京市','025-*','210000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401010102',N'袁飞','03-11-1986',N'男',N'湖南省长沙县','0731-*','410000',560);INSERTINTO"STUD_INFO"("STUD_ID","NAME","BIRTHDAY","GENDER","ADDRESS","TELCODE","ZIPCODE","MARK")VALUES('0401040123',N'孔荣','05-31-1986',N'男',N'云南省昆明市','0871-*','650000',600);-(5).2insertintoteacher_infovalues('010101',N'刘娜',N'女',34,N'讲师','020-*',418,'0401010102')select*fromteacher_info-独立实践：USEstudentGOinsertintoteacher_infovalues('010102',N'邵云鹏',N'男',45,N'教授','020-34607811',418,'0401010105');insertintoteacher_infovalues('010103',N'孙乐多',N'男',27,N'助教','020-34602622',418,'0401010106');insertintoteacher_infovalues('010104',N'赵一欧',N'女',26,N'助教','020-34603472',418,'0401010101');insertintoteacher_infovalues('010106',N'王吉林',N'男',32,N'讲师','020-34603232',418,'0401010103');insertintoteacher_infovalues('010105',N'王小悦',N'女',35,N'讲师','020-34603790',418,'0401010104');-(5).3USEstudentGOINSERTINTOclassroom_infoVALUES('120703',N'微机组装与维护',N'实训',N'微机、投影仪',40);INSERTINTOclassroom_infoVALUES('120704',N'计算机网络',N'实验',N'交换机、路由器等',40);INSERTINTOclassroom_infoVALUES('120705',N'数据库',N'计算机机房',N'微机、投影仪',60);INSERTINTOclassroom_infoVALUES('120706',N'软件设计',N'计算机机房',N'微机、投影仪',60);INSERTINTOclassroom_infoVALUES('120707',N'多媒体',N'计算机机房',N'微机、投影仪',60);INSERTINTOclassroom_infoVALUES('120708',N'',N'普通',N'白板、投影仪',120);USEstudentGOINSERTINTOdept_codeVALUES('01',N'计算机工程系');INSERTINTOdept_codeVALUES('02',N'管理工程系');INSERTINTOdept_codeVALUES('03',N'机电工程系');INSERTINTOdept_codeVALUES('04',N'食品工程系');INSERTINTOdept_codeVALUES('05',N'轻化工程系');INSERTINTOdept_codeVALUES('06',N'通信工程系');INSERTINTOdept_codeVALUES('07',N'外语工程系');USEstudentGOINSERTINTOlesson_infoVALUES('0401010101',N'计算机导论',N'考察',30,1.5);INSERTINTOlesson_infoVALUES('0401010102',N'Java程序设计',N'考试',60,3.5);INSERTINTOlesson_infoVALUES('0401010103',N'微型计算机原理',N'考试',60,3.5);INSERTINTOlesson_infoVALUES('0401010104',N'IT市场营销',N'考察',30,1.5);INSERTINTOlesson_infoVALUES('0401010105',N'网络互联设备与配置',N'考察',60,2.0);INSERTINTOlesson_infoVALUES('0401010106',N'多媒体技术',N'考察',60,3.0);USEstudentGOINSERTINTOspecialty_codeVALUES('040101',N'计算机应用技术');INSERTINTOspecialty_codeVALUES('040102',N'计算机网络技术');INSERTINTOspecialty_codeVALUES('040103',N'计算机控制技术');INSERTINTOspecialty_codeVALUES('040104',N'多媒体技术');INSERTINTOspecialty_codeVALUES('040105',N'计算机软件技术');INSERTINTOspecialty_codeVALUES('040106',N'计算机通信技术');INSERTINTOspecialty_codeVALUES('040107',N'计算机管理技术');USEstudentGOINSERTINTOstaffroom_infoVALUES('0101',N'计算机应用',N'专业',N'王二毛');INSERTINTOstaffroom_infoVALUES('0102',N'计算机网络',N'专业',N'李四冲');INSERTINTOstaffroom_infoVALUES('0103',N'计算机软件',N'专业',N'赵一生');INSERTINTOstaffroom_infoVALUES('0104',N'计算机管理',N'专业',N'汪三洋');USEstudentGOINSERTINTOstud_gradeVALUES('0401010811',N'张源','0401010102',90);INSERTINTOstud_gradeVALUES('0401020201',N'赵明','0401010105',89);INSERTINTOstud_gradeVALUES('0401030101',N'王刚','0401010103',87);INSERTINTOstud_gradeVALUES('0401050201',N'陈红','0401010101',91);INSERTINTOstud_gradeVALUES('0401040112',N'孙强','0401010104',83);INSERTINTOstud_gradeVALUES('0401020203',N'李伟','0401010106',86);INSERTINTOstud_gradeVALUES('0401010634',N'钱昆','0401010106',78);INSERTINTOstud_gradeVALUES('0401030213',N'郑芳','0401010106',95);INSERTINTOstud_gradeVALUES('0401010102',N'袁飞','0401010106',95);INSERTINTOstud_gradeVALUES('0401040123',N'孔荣','0401010106',83);INSERTINTOstud_gradeVALUES('0401050127',N'张军','0401010106',84);USEstudentGOINSERTINTOteach_scheduleVALUES('0401010101','08-30-2004','15','120703','01','010104');INSERTINTOteach_scheduleVALUES('0401010102','08-30-2004','15','120704','01','010101');INSERTINTOteach_scheduleVALUES('0401010103','08-30-2004','13','120705','01','010106');INSERTINTOteach_scheduleVALUES('0401010104','08-30-2004','10','120706','01','010105');INSERTINTOteach_scheduleVALUES('0401010105','08-30-2004','19','120707','01','010102');INSERTINTOteach_scheduleVALUES('0401010106','08-30-2004','14','120708','01','010103');-(6).修改数据-(6).1updatestud_gradesetgrade=grade+grade*0.1wherecourse_id='0401010101'-(6).2updatestud_gradesetgrade=nullwheregrade