数据库编程复习(修改版).doc

3G一班学生开张重走长征路活动，目的地北京，其中1到40号为男生，41到65号为女生，男生从广州出发，行程2100公里，女生从长沙出发，行程1100公里，之后从北京集体回到广州，计算全部学生的总行程。Create database calss3g1On primary ( name=calss3g1_dat,Filename=d:class3g1.mdf,Size=1mb,Maxsize=2mb,Filegrowth=100%)Log on(name=class3g1_log,Filename=d:class3g1.ldf,Size=1mb,Maxsize=2mb)Create table c3g1(SID int,Gender char(2) defaule ,Go_km int )Insert into c3g1Values(1,男,2100)Insert into c3g1Values(40,男,2100)Insert into c3g1Values(41,女,1100)Insert into c3g1Values(65,女,1100)Select sum(go_km)+2100*65 from c3g12,将ax2 +bx+c=y作为自变量，与因变量的关系x从0.1到0.99，将x,y,a,b,c记录在表格A中Create table A(a int,b int,c int,x float(4),y float(4)Create procedure p_mna int =1,b int=2,c int =3,x float(4)sum:while(x=0.99)beginselect y=a*x*x+b*x+cinsert into Avalues(a,b,c,x,y)goto sumend3 某超市盘点日用品百货，食品，家电，服装。将库存R，销量S与总进货I，算后存入数据。Create table t(m Narvchar(8),R int.S int,I int)GoCreate procedure P_chaoshim nvarchar(8),r int,s intAsDeclare i intSet i=s+r Insert into t values(m,r,s)Exec p_chaoshi 食品，100，200Exec p_chaoshi 日用百货，300，100Exec p_chaoshi 家电，200，200Exec p_chaoshi 服装，500，2004，设计一个计算器，完成四则运算。create procedure p1a float,b float,op char(1)asdeclare c floatif(op=+)set c=a+bif(op=-)set c=a-bif(op=*)set c=a*bif(op=/)and b0set c=a/bprint cgoexec p1 a=2,op=/,b=1解法2create procedure p_aa float,b float,op char(2)asdeclare c floatset c=case opwhen op=+then a+bwhen op=-then a-bwhen op=*then a*bwhen op=/then a/bendgo5由1加到100，并且不计偶数。create procedure p_sumasdeclare i intset i=1ag:if(i%2=1)and(i=100)set i=i+1goto agendprint igo6 已知SQRT(i)的算术平方根，求1+SQRT（100）create function p1(i int)returns fioatasbegindeclare c fioatset c=SQRT(i)+1returnendgoselect p1(100) 7,在3g1班中，选出数据库成绩最高前三名，并且备注标注优秀学生Create table c3g1(姓名 nvarchar（6）,性别 char（2），成绩 float.)goSelect *from c3g1 Order by (成绩) descgoSelect identity(int,1,1) as 名次，姓名,性别,成绩 into c3g3 from c3g1 Alter table c3g3Alter column 备注 nvarchar(4)goUpdate c3g3Set 备注=优秀学生Where 名次=3Go8证明cos2a+sin2a=1create function p(a float(8)returns nvarchar(10)asbegin declare c nvarchar(10),b float(8) if(a6.28) begin set b=sin(a)*sin(a)+cos(a)*cos(a) if(b)=1 set c=成立 if(b)1 set c=不成立 end else set c=请输入正确的数据范围go return c end select dbo.p(6.4) go9某线路由A,B,C,D,E,F,G各站点组成，A是起点，G是终点，求中途站相邻路程。create procedure p_nm asselect identity(int,1,1) as x , S,T into t2 from t1 where SADeclare loop intset loop=1while loop=(select count(x) from t2)Beginselect S,T into RT1 from t1where T=(select T from t2 where x=loop)select S,T