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

《SQL第四章课后习题答案.docx》由会员分享，可在线阅读，更多相关《SQL第四章课后习题答案.docx（23页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、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_infogousestudentgodropt

2、abledept_codegousestudentgodroptablelesson_infogousestudentgodroptablespecialty_codegousestudentgodroptablestaffroom_infogousestudentgodroptablestud_gradegousestudentgodroptableteacher_infogousestudentgodroptableteacher_schedulegoUSEstudentGOCREATETABLEstud_info(stud_idCHAR(10)NOTNULL,nameNVARCHAR(4

3、)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)usestudentg

4、ocreatetablestud_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

5、(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)usestudentgocreatetablestaffroo

6、m_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_typenva

7、rchar(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_m

8、emo，其数据类型为可变长度字符型，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表中的m

9、ark列建立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,genderNCH

10、AR(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_i

11、d)-独立实践：altertablestud_infoadddefault(null)forzipcode-(5).插入数据-(5).1insertintostud_infovalues(0401010811,N张源,12-05-1986,N男,N北京市海淀区,010-*,100080,560)select*fromstud_info-独立实践：USEstudentGOINSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401010811,N张源,12-05-1986,N男,

12、N北京市海淀区,010-*,100080,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401020201,N赵明,08-06-1986,N男,N上海市浦东区,021-*,202100,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401030101,N王刚,01-02-1986,N男,N天津市南开区,022-*,300000

13、,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401050201,N陈红,10-25-1986,N女,N武汉市汉口区,027-*,430000,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401040112,N孙强,06-07-1986,N男,N重庆市沙坪坝,023-*,400000,560);INSERTINTOSTUD

14、_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401020203,N李伟,09-01-1986,N男,N北京市大兴县,010-*,102600,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401010634,N钱昆,12-06-1986,N男,N广州市海珠区,020-*,510000,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,B

15、IRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401030213,N郑芳,08-09-1986,N女,N江苏省南京市,025-*,210000,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDRESS,TELCODE,ZIPCODE,MARK)VALUES(0401010102,N袁飞,03-11-1986,N男,N湖南省长沙县,0731-*,410000,560);INSERTINTOSTUD_INFO(STUD_ID,NAME,BIRTHDAY,GENDER,ADDR

16、ESS,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);insertintoteac

17、her_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,040101

18、0104);-(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_infoV

19、ALUES(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轻化工程系);INSERTIN

20、TOdept_codeVALUES(06,N通信工程系);INSERTINTOdept_codeVALUES(07,N外语工程系);USEstudentGOINSERTINTOlesson_infoVALUES(0401010101,N计算机导论,N考察,30,1.5);INSERTINTOlesson_infoVALUES(0401010102,NJava程序设计,N考试,60,3.5);INSERTINTOlesson_infoVALUES(0401010103,N微型计算机原理,N考试,60,3.5);INSERTINTOlesson_infoVALUES(0401010104,NIT市

21、场营销,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计算机控制技术);INSERTIN

22、TOspecialty_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李四冲);

23、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);INSERTINTOs

24、tud_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

25、(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,010

26、101);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