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

1、有一些实验内容较为简单，或在课堂上讲过的题目并没有提供答案，请自行完成没有提供答案的各章习题。P943.实验内容和步骤（4）建立约束独立实践：为表studnfo的zipcode列建立default约束usestudentgoaltertablestudnfoaddconstraintzipcode_dfltdefault'210005'forzipcode（6）向数据库student的表中插入数据usestudentgoupdatestud_infowherename='赵明'（7）删除数据库student的表数据2）删除计算机工程系所有学生的成绩记录Delet

2、efromstud_infowheresubstring（studP1163.实验内容及步骤（1）SELECT语句的基本使用1 ）略2 ）略3 ）略4）Selectstuddas学号,nameas姓名,addressas地址,telcodeas电话fromstudnfowheregender='女'6）Selecttecher_id,name,tech_titlefromteacher_infowherenamelike'王'独立实践：Selectteacher_id,course_idfromteacher_infoWheretelcodelike'%

3、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_code独立实践

4、：usestudentgoSelect*fromteacher_infoWheresubstring(teacher_id,1,4)=(selectjysh_idfromstaffroom_infoWherejysh_name='计算机应用')2)usestudentgoselectteach_id,namefromteacher_infoWheresubstring(teach_id,1,2)=wheredeptname='计算机工程系')(selectdeptcodeAndcourse_id=fromdept_codewheredeptname='计

5、算机工程系')(selectcourse_idfromlesson_infoWherecourse_name提示练习：='计算机专业英语')Select*fromstud_gradeWheresubstringDeptcodefrom(stud_iddept_code,3,2)=(whereselectdept_name='计算机工程')andcourese_id业英语')3)=(selectcourseidfromlesson_infowherecoure_name='计算机专usestudentgoSelectstud_id,name

6、,age,telcode,addressFromstud_infoWheresubstring,3,4)=(selectsubstringspecialty_code提示练习：usestudent(stud_idwherespecname='计算机应用技术')(speccode,3,4)fromgoSeSelectteach_id,name,course_idFromteacher_infoWheresubsring(teach_id,1,2)=from(selectdeptcodeAndsubstring(teach_iddept_namewheredeptname,3,2)

7、='计算机工程系')(selectsubstring(jysh_id,3,2)fromstaff_info4)wherejysh_name='计算机应用')goselectusestudentgoselectstudidwheresubstring计算机工程系)name,gradefrom(studd,3,2)=(stud_gradeselectdeptcodefromdept_codewheredeptnameandcourse_id=(')(3)连接查询的使用selectcourseidfromlessoninfowherecoursename=&#

8、39;多媒体技术fromteacher_infowheresubstring(teacher_id,1,2)in(selectdeptcodefromdept_codewheredeptname='计算机工程系')course_id=(selectcourse_idfromlesson_infowherecourse_name独立实践：usestudentname,telephone,course_idand='多媒体技术')1)usestudentgoselect*fromstudinfoinnerjoinstud_gradeon=独立实践:usestuden

9、tgoselect*fromteachscheduleinnerjoinlessoninfoon2)usestudentgoselectfromteacherinfoleftouterjoinlessoninfoon=独立实践:usestudenton=goselectcourse_date,course_week,room_id,deptcodefromteacherinforightouterjointeachschedule(4)数据汇总1)selectavg(salary)as计算机工程系教师平均工资fromteacher_infowhereleft(teacher_id,2)=(se

10、lectdeptcodefromdept_codewheredeptname='计算机工程系')独立实践：selectmax(age)as计算机工程系教师最大年龄,min(age)计算机工程系教师最小年龄fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')2)selectavg(age)as平均年龄fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codew

11、heredeptname='计算机工程系')独立实践：selectmax(mark)入学最高分,min(mark)入学最低分fromstud_infofromdept_codewheredeptname=wheresubstring(stud_id,3,2)=(selectdeptcode计算机工程系')3)selectcount(*)fromteacher_infowhereleft(teacher_id,2)=(selectdeptcodefromdept_codewheredeptname='计算机工程系')独立实践：usestudentgosel

12、ectsum(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)人数fromteach

13、er_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)=(selectdeptcodef

14、romdept_codewheredeptname='计算机工程系')andtech_title='讲师'orderbyage独立实践：select*ascfromstud_infowheresubstring计算机工程系')orderbybirthdayp1213.按照下列题目要求，(1)usestudent(stud_id,3,2)=(selectdeptcodefromdept_codewheredeptnameasc写出对应的的sql语句goselectname,gender,addressfromstud_info(2)usestudentgo

15、insertintostud_gradevalues(,'代燕',60)(3)usestudentgoselect*fromstud_gradewheregradein(85,86,88)(4)usestudentgoselect*fromstud_gradewheregradebetween60and80(5)usestudentgoselectstud_idfromstud_grade(6),course_id),gradefromstud_gradewheregrade=(selectmax(grade)usestudentgoselect*fromstud_infoor

16、derbymarkdesc(7)usestudentgoselect*fromstud_infowhere(stud_iddept_codeandleft(机网络技术wherestud_id')anddeptname,6)=(selectsubstringsubstring='计算机工程系')speccodefromspecialty_code(stud_id,7,2)='02',3,2)=(selectdeptcodefromwherespecname='计算selectcount(stud_id)wheresubstring(stud_idf

17、romstud_info,3,2)=(selectdeptcodefromdept_codewheredeptname计算机工程系')andleft(stud_id机网络技术')and,6)=(selectspeccodefromsubstring(stud_id7,2)='02'specialty_codewherespecname='计算(9)usestudentgoselectstud_idfromstud_gradegroupbystud_idhaving(10)usestudentmin(grade)>60andmax(grade)<

18、;80goselect*fromstud_gradewheregrade>(selectgradefromstud_gradewherestud_idandcourse_id=)(11)usestudentgoselectstud_idname,birthdayfromstud_infowhereyear(birthday)=(selectyear(birthday(12)fromstud_infowherestud_id=)usestudentgodeclareaint,bchar(10)selectfrom(stud_idwhereselectselect(13)a=countstu

19、d_gradeb=namea学生人数,b老师姓名courseidfromteacher_infowherecourse_idusestudentgoselect,deptnamewheresubstring(15)fromteacher_info,dept_codeusestudentgoselectteacher_idwhere(teacher_id,gradeandsubstring(teacher_id计算机工程系')(16)usestudentgoselectname,genderfromwheresubstring(teacher_id(select,1,2)=fromtea

20、ch_schedule,1,2)=(selectdeptcodeteacher_info,1,2)indeptcodefromdept_codewheredeptnamestud_gradefromdept_codewheredeptnamein('计算机工程系','机电工程系')go(17)usestudentgoselectcourse_name,stud_id,name,gradefromlesson_info,stud_gradewhere=andgrade>(selectgradefromstud_gradewherecourse_id=)and

21、=orderbygradedesc(18)usestudentgoselect*fromstud_gradeorderbysubstring(stud_id,7,4)(19)usestudentgoupdatestud_gradesetgrade=grade+5wherecourse_id=(20)deletefromstud_infowherename='张源P134-1353.实验内容和步骤(1)创建视图1)usestudentgocreateviewstud_info_femaleasselect*fromstud_infowheregender='女goselect*f

22、romstud_info_female2)usestudentgocreateviewstud_info_birthdayasselectstud_id,name,birthdaygo3)usestudentgocreateviewstud_grade_averagefromstud_info（学号,平均成绩）asselectstud_id,avg(grade)fromstud_gradegroupbystud_idselect*from(2)使用视图2)stud_grade_averageselectstud_idstud_info_birthday或,name,year(getdate()

23、-year(birthday)asagefromselectstud_id,name,datediff(year,birthday,getdate()asagefromstud_info_birthday3)insert4)update5)delete_fromp1362.按照题目要求写岀下列SQL命令，并在机器上进行测试(1)usestudentgocreateviewstud_viewasselectstud_id,name,gradefromstud_infoinnerjoinstud_gradeon=wheresubstring(studd,3,2)='01'withc

24、heckoptionp1863)usestudentgocreatetriggerteacher_tri2onteacher_infofordeleteasif(selectcount(*)fromteach_scheduleinnerjoindeletedon=>0beginraiserror('youcannotdeletetheteacher_infowiththeorderrecord.thetransactionwillbecancelled',10,1)rollbacktransactionend或usestudentgocreatetriggerteache

25、r_tri2onteacher_infofordeleteasselectintotable_bakkfromdeletedif(selectcount(*)fromteach_scheduleinnerjointable_bakkon=table_bakk.teacher_id)>0beginraiserror('youcannotdeletetheteacher_infowiththeorderrecord.thetransactionwillbecancelled',10,1)rollbacktransactionend或usestudentgocreatetrig

26、gerteacher_tri2onteacher_infofordeleteasifexists(select*fromteach_scheduleinnerjoindeletedon=deleted.teacher_id)beginraiserror('youcannotdeletetheteacher_infowiththeorderrecord.thetransactionwillbecancelled',10,1)rollbacktransactionend独立实践：编写一个触发器，在对student数据库中的表数据表stud_info执行插入、更新和删除操作后给岀相应

27、的提示。declareaint,bintselecta=count(*)frominsertedselectb=count(*)fromdeletedif(a>0andb=0)raiserror('你执行的是插入操作！',10,1)elseif(a>0andb>0)raiserror('你执行的是更新操作！',10,1)elseif(a=0andb>0)raiserror('你执行的是删除操作！',10,1)P1872.按题目要求写岀T-SQL语句，并在机器上进行测试(1)usestudentgocreateprocedu

28、reteache_age40(tagetinyint,ttechchar(10)asselect*fromteacher_infowhereage>tageandtechtitle=ttechexecteache_age40tage=40,ttech='副教授'usestudent(2)gocreateprocedurestud_gender(sexchar(2),numtinyintoutput)asselectnum=count(gender)fromstud_info(3)declarestu_numtinyintexecstud_gender'男'

29、,stu_numoutputselectstu_num(4)usestudentwheregender=sex(2)gocreatetriggerteacher_delonteacher_infofordeleteasdeclarecouridchar(10)selectcourid=course_idfromdeletedifcouridisnotnullbeginraiserror('该教师已经授课，无法删除！',10,1)rollbacktransactionend(5)usestudentgocreatetriggerteacher_cancel_updateforinsert,updateasdeclarecouridchar(10)selectcourid=course_idfrominsertedifnotexists(select*fromlesson_infobeginraiserror('暂未开设这门课程！',