资源描述：
《SQLServer实验指导(第三版)习题8答案》由会员上传分享，免费在线阅读，更多相关内容在工程资料-天天文库。
1、usejiaoxuedbgo--pagel54实验・・1利用学生姓名查询该牛选修的课程名，成绩，以及任课教师姓名ifobject_id(1pro_l1)isnotnulldropprocedurepro_lgocreateprocedurepro_l(@snchar(8))asbeginselectcnzscoreztnfromstudentzsczcourseztczteacherwherestudent・sno=sc・snoandsc.cno=course・enoandtc.tno=teacher・tnoandcourse・cno=tc.en
2、oandsn=@snend--查询declare@snchar(8)execpro_l1张建国1一2查询某系的学生的最大年龄和最小年龄--selectdept,max(age)maxage,min(age)minagefromstudentgroupbydeptifobject_id(1pro_21)isnotnulldropprocedurepro_2gocreateprocedurepro_2(@deptchar(10),@maxagetinyintoutput,@minagetinyintoutput)asbeginselect@maxag
3、e=max(age),@minage=min(age)fromstudentwheredept=@deptend--查询declare@deptchar(10),@maxagetinyint,@minagetinyintexecpro_2「计算机J@maxageoutputz@minageoutputprint@maxageprint@minage「3利川学生姓名和课程名检索该住该课程的成绩ifobject_id(rpro_31)isnotnulldropprocedurepro_3gocreateprocedurepro_3(@snchar(8
4、),@cnchar(10),@scoretinyintoutput)asbeginselect@score=scorefromstudent,sc,coursewheresn=@snandcn=@cnend--查询declare@snchar(8),@cnchar(10)z@scoretinyintexecpro_3'弓长建国'J数据库J@scoreoutputprint@score・・4根据职称查询人数，并给出''副教授〃的人数ifobject_id(1pro_41)isnotnulldropprocedurepro_4gocreateproc
5、edurepro_4(@profchar(10),@countsmallintoutput)asbeginselect@count=count(*)fromteacherwhereprof=@profend--查询declare@profchar(10)z@countsmallintexecpro_4〔副教授J@countoutputprint@count「5统计某系某职称的人数，平均年龄，平均工资，最高工资ifobject_id(1pro_51)isnotnulldropprocedurepro_5gocreateprocedurepro_5@
6、deptchar(10),@profchar(10)z@countsmallintoutputz@avgagetinyintoutput,@avgsalsmallintoutput,@maxsalsmallintoutput)asbeginselect@coucount(*),@avgage=avg(age),@avgsal=avg(sal),@maxsal=max(sal)fromteacherwheredept=@deptandprof=@profend■■查询declare@deptchar(10),@profchar(10),@count
7、smallint,@avgagetinyint,@avgsalsmallint,@maxsalsmallintexecpro_5'计算机J「讲师>,@countoutput,@avgageoutputr@avgsaloutput,@maxsaloutputprint@countprint@avgageprint@avgsalprint@maxsal--6查询某系的教师人数，平均年龄和学生人数--selectteacher.dept,count(distincttno)t,avg(teacher.age)avgage,count(distincts
8、no)sfromteacher,studentwhereteacher・dept:=student•deptgroupbyteacher
