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

1、有一些实验内容较为简单，或在课堂上讲过的题目并没有提供答案，请自行完成没有提供答案的各章习题。P943.实验内容和步骤（4 ）建立约束独立实践：为表studnfo 的zipcode 列建立default 约束use studentgoalter tablestud_i nfoadd con stra intzipcode_dfltdefault '210005' for zipcode（6 ）向数据库student的表中插入数据use studentgoupdate stud_ infowhere name ='赵明'（7）删除数据库student的表数据2）删

2、除计算机工程系所有学生的成绩记录Delete from stud_i nfo where substring（studP1163.实验内容及步骤（1）SELECT语句的基本使用1 ）略2 ）略3 ）略4）Select studd as学号,name as 姓名,address as 地址,telcode as 电话 from stud_info wheregender= '女'6）Select techer_id, name,tech_title from teacher_ infowhere name like '王 '独立实践：Select teacher_

3、id,course_id from teacher_ infoWhere telcode like '%3460%'7）Select stud_id,course_id,grade from stud_gradeWhere grade betwee n 80 and 90独立实践use studentgoSelect stud_id ,n ame,address,zipcode from stud_ info where birthday betwee n'01-01-1987'a nd'12-31-1987'子查询的使用1）Select * f

4、rom teacher_infoWhere substring（teach_id ,1,2 ）=( select deptcode 独立实践： use studentfrom dept_codewhere deptname =' 计算机工程系 ' )goSelect * from teacher_infoWhere substring ( select jysh_id Where jysh_name 2) use student( teacher_id , 1, 4)= from staffroom_info=' 计算机应用 ' )goselect teach_

5、id, name from teacher_infoWhere substring( teach_id , 1, 2 )=( select deptcode And course_id =from dept_codewhere deptname =' 计算机工程系 ' )( select course_idWhere course_name 提示练习：from lesson_info=' 计算机专业英语 ' )Select * fromstud_gradeWhere substring( stud_id, 3, 2)=(selectDeptcode fromde

6、pt_codewheredept_nameand courese_id=( selectcourse_id from3)use student=' 计算机工程 ' )lesson_info where coure_name =' 计算机专业英语 ' )goSelectstud_id, name , age , telcode, addressFromstud_infoWheresubstring( stud_id , 3, 4)=(select substring( speccode, 3, 4) from specialty_codewherespecname

7、 =' 计算机应用技术 ' ) 提示练习：use student goSe use student goSelect teach_id, name,course_idFrom teacher_infoWhere subsring( teach_id, 1, 2)=( select deptcodefromdept_namewhere deptnameAnd substring (teach_id, 3, 2)=( select substring( jysh_id , 3, 2)from staff_info4)=' 计算机工程系 ' ) where jysh_

8、name=' 计算机应用 ' )fromteacher_infowheresubstring( teacher_id , 1, 2)in( selectdeptcodefrom dept_codewhere deptname =' 计算机工程系 ' ) andcourse_id =( selectcourse_idfromlesson_info where course_name =' 多媒体技术select name, telephone , course_id独立实践：)use studentgoselectstudd,name , grade fr

9、omstud_gradewhere substr ing(stud_id , 3, 2)=(selectdeptcodefrom dept_codewheredept name ='计算机工程系')and course_id=(select course_idfromless on_infowhere course_name='多媒体技术)(3 )连接查询的使用1)use studentgoselect* from stud_infoinner join stud_gradeon stud info. stud id =stud grade . stud id独立实践：

10、use studentgoselect* from teach_scheduleinnerjoinlesson_infoon teach_schedule . course_id=lesson_info. course_id2)use studentgoselectlesson_info. course_name, teacher_info. name , teacher_info. teacher_idfrom teacher_ infoleftouter joi nless on_infoon teacher_ info.course_id= less onnfo.course_id独立实

11、践：use studentgoselectteacher_ infoteacher_id,teacher_ , teach_schedule.course_id, course_date,course_week , roomd,deptcodefrom teacher_ inforightouter joi nteach_scheduleon teacher_info . teacher_id =teach_schedule . teacher_id(4 )数据汇总1)selectavg (salary)as计算机工程系教师平均工资fromteacher_infowherel

12、eft(teacher_id,2)=( selectdeptcodefromdept_codewheredept name='计算机工程系')独立实践：selectmax (age )as计算机工程系教师最大年龄，min ( age )计算机工程系教师最小年龄fromteacher_infowhereleft(teacher_id,2)=( selectdeptcodefromdept_codewheredept name='计算机工程系')2)selectavg(age )as平均年龄fromteacher_infowhereleft(teacher_id,2

13、)=( selectdeptcodefromdept_codewheredept name='计算机工程系')独立实践：select max( mark ) 入学最高分 ,min ( mark )入学最低分from stud_i nfowhere substring ( stud_id , 3, 2)=( select deptcode from dept_code where deptname =' 计算机工程 系 ' )3)select count (*) fromteacher_infowhere left( teacher_id, 2)=( select

14、deptcodefromdept_codewheredeptname =' 计算机工程系')独立实践：use studentgoselect sum( salary )from teacher_infowhere left( teacher_id, 2)=( selectdeptcodefromdept_codewheredeptname =' 计算机工程系')and course_id =( selectcourse_idfrom lesson_infowherecourse_name =' 多媒体技术 ' )(5) GROUP BY及ORDE

15、R BY子句的使用1)use studentgoselect tech_title职称 , substring( teacher_id, 1,4), count( teacher_id) 人数 fromteacher_infogroup by tech_title , substring ( teacher_id , 1, 4) 独立实践use student goselect gender , count ( stud_id ) from stud_infofrom dept_codewhere deptname =' 计算机工程where substring ( stud_id ,

16、 3, 2)=( select deptcode 系 ' )group by gender2)select teacher_id , name , tech_title , agewhere left(teacher_id, 2)=( select deptcodefrom dept_codeand tech_title=' 讲师 'order by ageasc独立实践：from teacher_infowhere deptname =' 计算机工程系 ' )select * from stud_infowhere substring( stud_id

17、 , 3, 2)=( select deptcodefrom dept_code系 ' )order by birthdayascp1213. 按照下列题目要求，写出对应的的sql语句( 1)where deptname =' 计算机工程use student goselect name, gender , address from stud_info ( 2) use student goinsertinto stud_gradevalues (, ' 代燕 ' , 60 )( 3)use studentgoselect * fromstud_gradewhe

18、regradein( 85 , 86 , 88 )( 4)use studentgoselect * fromstud_gradewheregradebetween60 and80( 5)use studentgoselect stud_id, course_id, gradefromstud_gradewheregrade=( selectmax( grade ) fromstud_grade )( 6)use studentgoselect * fromstud_infoorderby markdesc( 7)use studentgoselect *fromstud_infowheres

19、ubstring( stud_id, 3, 2)=(selectdeptcodefrom dept_code wheredeptname=' 计算机工程系 ' )and left( ' ) and selectstud_id , 6)=( select substring ( stud_id count ( stud_id ),7,fromspeccode2)= '02'stud_infofrom specialty_codewhere specname =' 计算机网络技术where 系 ' )substring( stud_id, 3

20、,2)=( selectdeptcode from dept_code where deptname =' 计算机工程and')( 9)left( stud_id and substringspeccodeusestudentgo selectstud_id, 6)=( select( stud_id , 7, 2)= '02'from specialty_codewhere specname =' 计算机网络技术from stud_gradegroup havingby stud_idmin ( grade )> 60 and max( grad

21、e )<80( 10) use studentgoselect * fromstud_gradewhere grade>( select grade from stud_gradewhere stud_idandcourse_id =)( 11) use studentgoselect stud_idfrom stud_info, name, birthday where stud_idfrom stud_info where year ( birthday )=( select year ( birthday )=)(12) use studentgodeclare a int

22、,b char ( 10)selecta = count( stud_id)fromstud_gradewherecourse_id=5'selectb = namefrom teacher_infowhere course_id=5selecta 学生人数 , b 老师姓名( 13) use studentgoselect teacher_infowhere substring( 15). name , deptname from teacher_info ( teacher_id, dept_code, 1, 2)= dept_code . deptcodeuse studentg

23、oselect teacher_id, stud_gradewhere teach_schedule. course. course_id , grade from teach_schedule id =stud_grade . course_id, stud_gradeand substring ( teacher_id 程系 ' )( 16), 1, 2)=( selectdeptcodefrom dept_codewhere deptname=' 计算机工use studentgo selectwherename, gender fromsubstring ( teach

24、er_idteacher_info, 1, 2)in( select (17) use studentdeptcodefrom dept_codewheredeptnamein( ' 计算机工程系 ',' 机电工程系 ' )goselect course_name, gradewhere lesson_info, stud_id , name. course_id =stud_gradefrom lesson_info, stud_grade. course_idand grade >( select and stud_grade order by gra

25、de ( 18) use studentgrade from stud_gradewhere course_id=). course_iddescgoselect * fromstud_grade( stud_id , 7, 4)order by substring ( 19)use student gowhere course_idupdate stud_grade set grade = grade +5( 20)delete from stud_infowhere name =' 张源P134-135 3. 实验内容和步骤( 1) 创建视图1)use studentgocreat

26、e view studnfo_femaleasselect * from studnfowhere gender ='女goselect * from studnfo_female2)use studentgocreate view stud_info_birthdayasselect stud_id , name , birthdayfrom stud_i nfogo3)use studentgocreate view stud_grade_averageselectstudd,avg ( grade ) from stud_gradegroupby studdgoselect* f

27、romstud_grade_average(2)使用视图2)selectstudd,name , year ( getdate ()- year(birthday)as agefrom stud_ in fo_birthday或selectstudd,name , datediff( year , birthday,getdate()asage from stud_i nfo_birthday(学号，平均成绩)as3)in sert4)update5)delete_fromp13 62.按照题目要求写岀下列SQL命令，并在机器上进行测试(1)use studentgocreate view s

28、tud_viewasselect stud_id , name, grade from stud_info stud_info. stud_id =stud_grade . gradewhere substring ( studd, 3, 2)= '01'with checkopti oninner join stud_grade onp1863)use studentgocreate trigger teacher_tri2on teacher_info for deleteasif( selectcount (*) from teach_scheduleinnerjoind

29、eletedonteach_schedule. teacher_id= deleted. teacher_id)>beginraiserror( 'you can not deletetheteacher_infowith the orderrecord.the transactionwill be cancelled', 10, 1)endrollbacktransactionuse studentgocreate triggerteacher_tri2on teacher_infofor deleteasselect* intotable_bakkfrom delet

30、edif ( selectcount (*) fromteach_scheduleinnerjointable_bakkonteach_schedule. teacher_id= table_bakk. teacher_id)> 0beginraiserror( 'you can not delete theteacher_infowith theorderrecord.the transactionwill be cancelled', 10, 1)endrollbacktransactionuse studentgocreate triggerteacher_tri2

31、onteacher_infofordeleteasifexists( select* fromteach_scheduleinnerjoindeletedonteach_schedule. teacher_id= deleted. teacher_idbeginraiserror( 'you cannot deletethe teacher_infowith theorderrecord.the transactionwill be cancelled', 10, 1)rollbacktransactionend独立实践：编写一个触发器，在对student数据库中的表数据表 s

32、tud_info执行插入更新和删除操作后给出相应的提示。declarea int, bintselecta = count(*)frominsertedselectb = count(*)fromdeletedif ( a>0 and b=0)raiserror( ' 你执行的是插入操作！' , 10 , 1)else if ( a>0 and b>0)raiserror(' 你执行的是更新操作！' , 10, 1)else if ( a=0 and b>0) raiserror ( ' 你执行的是删除操作！ ' , 10

33、, 1)P1872.按题目要求写岀T-SQL语句，并在机器上进行测试( 1)use studentgo create procedure teache_age40 ( tage tinyint , ttech char ( 10) asselect * from teacher_info where age >tage and tech_title= ttechexec teache_age40 tage =40, ttech =' 副教授 '(2) use student go create procedure stud_gender ( sex char ( 2),

34、num tinyint output ) asselect num = count ( gender ) from stud_info where gender =sex (2)( 3)declare stu_num tinyintexec stud_gender' 男 ' , stu_num outputselect stu_num( 4)use studentgocreate trigger teacher_del on teacher_infofor deleteasdeclare courid char ( 10)select courid = course_idfrom deletedif courid is not nullbeginraiserror ( ' 该教师已经授课，无法删除！ ' , 10, 1) rollback transactionend( 5)use studentgocreate trigger teacher_cancel_update on teacher_info for insert , updateasdeclarecouridchar( 10)selectcourid= course_id