SQL实验五答案

1、精选文档1. (1) declare a int,b int declare c float declare s1 char(3),s2 char(3)set a=5set b=4set c=4.0set s1='abc'set s2='def'select a*b as'a与b的积', a/b as'a与b的商', a%b as'a与b的余数', a&b as '按位与', a|b as '按位或', ab as '按位异或', a/c as'a与c

2、的商', s1+s2 as's1与s2的连串'(2)select version(3)use stuinfodeclare x char(8)select x=sname from studentwhere sno='105'select x(4)use stuinfodeclare y int,z intselect y=COUNT(*),z=max(degree)from scorewhere cno='3-105'select y as'选课人数',z as'最高成果'2.(1)use stuinfo

3、select sname'姓名',cno '课程名',casewhen degree>=90 then'优'when degree>=80 then'良'when degree>=70 then'中'when degree>=60 then'及格'when degree<60 then'不及格'end as '成果等级'from score,studentwhere score.sno=student.snoorder by cno as

4、c,sname asc(2)use stuinfodeclare a float,b float,c floatset a=(select AVG(degree)from score where sno in(select sno from student where ssex='男')set b=(select AVG(degree)from score where sno in (select sno from student where ssex='女')set c=a/bif c>1.3print '男生比女生成果高多了'if c&

5、lt;=1.3 and c>=0.8print '男生与女生成果差不多'if c<0.8print '女生比男生成果高多了'(3)use stuinfocreate table xsrq (spno char(4) default('3741'), spname char(20) default('苹果MacBook Pro '), scrq date, sl int)use stuinfodeclare m intset m=0while m<20 begin set m=m+1 insert into xsr

6、q(scrq) values (dateadd(DAY,m,getdate() end3. (1)use stuinfogocreate function average(cn char(4)returns floatasbegindeclare aver floatselect aver=(select avg(degree)from score where cno=cn)return averendgodeclare coursel char(6)set coursel='3-105'select dbo.average(coursel) as '3-105号课程的平均成果'go(2)use stuinfogocreate function cj(no char(5)returns tableasreturn (select sname,cno,degree from student,scorewhere student.sno=no and student.sno=score.