ok数据库实验题 平时作业

edure Upd_GradeР@SNO varchar(100),Р@CNO varchar(100),Р@GRADE INTРasРUPDATE SCРset SNO=@SNO,РCNO=@CNO,РGRADE=@GRADEРwhere SNO=@SNO O=@CNOРDisp_Student (SNO,SUM_CREDIT output,AVG_GRADE output) Р要求:根据SNO参数显示该学生的有关信息,包括:Рa)学号,姓名,性别,年龄,身高,系别,所有选修的课程及成绩;Рb)显示输出参数SUM_CREDIT(表示选修课程的总学分)及AVG_GRADE (表示3学分以上的课程的平均成绩)。Р答:create procedure Upd_GradeР@SNO varchar(100),Р@SUM_CREDIT INT output,Р@AVG_GRADE int outputРasРselect * from Students sРinner join SC on sc.SNO=s.SNOРinner join Courses c on O=OРwhere s.SNO=@SNOРselect @SUM_CREDIT=SumCredit from CreditsРwhere SNO=@SNOРselect @AVG_GRADE=avg(SC.GRADE) from Students sРinner join SC on sc.SNO=s.SNOРwhere s.SNO=@SNO and SC.GRADE>3Рgroup by s.SNOРCAL_GPA (SNO,GPA output)Р要求:根据SNO参数, 输出并显示该学生的GPA值。计算方法如下:РGRADE(G) GRADEPOINT(GP)РG>=85 4Р85>G>=75 3Р75>G>=60 2