网络数据库技术教材相关章节答案

有一些实验内容较为简单，或在课堂上讲过的题目并没有提供答案，请自行完成没有提供答案的各章习题。P943.实验内容和步骤（4）建立约束独立实践：为表stud_info的zipcode列建立default约束usestudentgoaltertablestud_infoaddconstraintzipcode_dfltdefault'210005'forzipcode（6）向数据库student的表中插入数据独立实践：将赵明同学的入学成绩更新成与学号为0401040123的孔荣同学的入学成绩相同usestudentgoupdatestud_infosetmark=(selectmarkfromstud_infowherestud_id='0401040123')wherename='赵明'（7）删除数据库student的表数据2）删除计算机工程系所有学生的成绩记录Deletefromstud_infowheresubstring(stud_id,3,2)='01'P962选择最合适的答案(1)D(2)D(3)B(4)A(5)C(6)A(7)C(8)AP1163.实验内容及步骤（1）SELECT语句的基本使用1）略2）略3）略4）Selectstud_idas学号,nameas姓名,addressas地址,telcodeas电话fromstud_infowheregender=’女’6）Selecttecher_id,name,tech_titlefromteacher_infowherenamelike'王％'独立实践：Selectteacher_id,course_idfromteacher_infoWheretelcodelike'%3460%'7)Selectstud_id,course_id,gradefromstud_gradeWheregradebetween80and90独立实践usestudentgoSelectstud_id,name,address,zipcodefromstud_infowherebirthdaybetween'01-01-1987'and'12-31-1987'(2)子查询的使用1)Select*fromteacher_infoWheresubstring(teach_id,1,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系'）独立实践：usestudentgoSelect*fromteacher_infoWheresubstring(teacher_id,1,4)=(selectjysh_idfromstaffroom_infoWherejysh_name='计算机应用')2)usestudentgoselectteach_id,namefromteacher_infoWheresubstring(teach_id,1,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系'）Andcourse_id=(selectcourse_idfromlesson_infoWherecourse_name='计算机专业英语')提示练习：Select*fromstud_gradeWheresubstring(stud_id,3,2)=(selectDeptcodefromdept_codewheredept_name='计算机工程')andcourese_id=(selectcourse_idfromlesson_infowherecoure_name='计算机专业英语')3)usestudentgoSelectstud_id,name,age,telcode,addressFromstud_infoWheresubstring(stud_id,3,4)=(selectsubstring(speccode,3,4)fromspecialty_codewherespecname='计算机应用技术')提示练习：usestudentgoSeSelectteach_id,name,course_idFromteacher_infoWheresubsring(teach_id,1,2)=(selectdeptcodefromdept_namewheredeptname='计算机工程系')Andsubstring(teach_id,3,2)=(selectsubstring(jysh_id,3,2)fromstaff_infowherejysh_name='计算机应用')4)usestudentgoselectname,telephone,course_idfromteacher_infowheresubstring(teacher_id,1,2)in(selectdeptcodefromdept_codewheredeptname='计算机工程系')andcourse_id=(selectcourse_idfromlesson_infowherecourse_name='多媒体技术')独立实践：usestudentgoselectstud_id,name,gradefromstud_gradewheresubstring(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')andcourse_id=(selectcourse_idfromlesson_infowherecourse_name='多媒体技术')（3）连接查询的使用1)usestudentgoselect*fromstud_infoinnerjoinstud_gradeonstud_info.stud_id=stud_grade.stud_id独立实践：usestudentgoselect*fromteach_scheduleinnerjoinlesson_infoonteach_schedule.course_id=lesson_info.course_id2)usestudentgoselectlesson_info.course_name,teacher_info.name,teacher_info.teacher_idfromteacher_infoleftouterjoinlesson_infoonteacher_info.course_id=lesson_info.course_id独立实践：usestudentgoselectteacher_info.teacher_id,teacher_info.name,teach_schedule.course_id,course_date,course_week,room_id,deptcodefromteacher_inforightouterjointeach_scheduleonteacher_info.teacher_id=teach_schedule.teacher_id（4）数据汇总1)selectavg(salary)as计算机工程系教师平均工资fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')独立实践：selectmax(age)as计算机工程系教师最大年龄,min(age)计算机工程系教师最小年龄fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')2)selectavg(age)as平均年龄fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')独立实践：selectmax(mark)入学最高分,min(mark)入学最低分fromstud_infowheresubstring(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')3)selectcount(*)fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')独立实践：usestudentgoselectsum(salary)fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')andcourse_id=(selectcourse_idfromlesson_infowherecourse_name='多媒体技术')（5）GROUPBY及ORDERBY子句的使用1)usestudentgoselecttech_title职称,substring(teacher_id,1,4),count(teacher_id)人数fromteacher_infogroupbytech_title,substring(teacher_id,1,4)独立实践usestudentgoselectgender,count(stud_id)fromstud_infowheresubstring(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')groupbygender2)selectteacher_id,name,tech_title,agefromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')andtech_title='讲师'orderbyageasc独立实践：select*fromstud_infowheresubstring(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')orderbybirthdayascP1202.选择最合适的答案（1）D(2)C(3)B(4)C(5)D(6)B(7)A(8)C(9)D(本题没行都没有错误，只是第3句和第4句放错了顺序。)p1213.按照下列题目要求，写出对应的的sql语句(1)usestudentgoselectname,gender,addressfromstud_info(2)usestudentgoinsertintostud_gradevalues('0401010717','代燕','0401010104',60)(3)usestudentgoselect*fromstud_gradewheregradein(85,86,88)(4)usestudentgoselect*fromstud_gradewheregradebetween60and80(5)usestudentgoselectstud_id,course_id,gradefromstud_gradewheregrade=(selectmax(grade)fromstud_grade)(6)usestudentgoselect*fromstud_infoorderbymarkdesc(7)usestudentgoselect*fromstud_infowheresubstring(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')andleft(stud_id,6)=(