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

3G一班学生开张重走长征路活动，目的地北京，其中1到40号为男生，41到65号为女生，男生从广州出发，行程2100公里，女生从长沙出发，行程1100公里，之后从北京集体回到广州，计算全部学生的总行程。
Create database calss3g1
On 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 c3g1
Values(‘1’,’男’,’2100’)

Insert into c3g1
Values(‘40’,’男’,’2100’)

Insert into c3g1
Values(‘41’,’女’,’1100’)
Insert into c3g1
Values(‘65’,’女’,’1100’)

Select sum(go_km)+2100*65 from c3g1

2,将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_mn
@a int =1,@b int=2,@c int =3,x float(4)
sum:
while(@x<=0.99)
begin
select @y=@a*@x*@x+@b*@x+@c
insert into A
values(‘@a’,’@b’,’@c’,’@x’,’@y’)
goto sum
end

3 某超市盘点日用品百货，食品，家电，服装。将库存R，销量S与总进货I，算后存入数据。

Create table t
(
m Narvchar(8),
R int.
S int,
I int
)
Go
Create procedure P_chaoshi
@m nvarchar(8),@r int,@s int
As
Declare @i int
Set @i=@s+@r
Insert into t values(‘@m’,’@r’,’@s’)
Exec p_chaoshi ‘食品’，‘100’，‘200’
Exec p_chaoshi ‘日用百货’，‘300’，‘100’
Exec p_chaoshi ‘家电’，‘200’，‘200’
Exec p_chaoshi ‘服装’，‘500’，‘200’

4，设计一个计算器，完成四则运算。
create procedure p1
@a float,@b float,@op char(1)
as
declare @c float
if(@op='+')
set @c=@a+@b
if(@op='-')
set @c=@a-@b
if(@op='*')
set @c=@a*@b
if(@op='/')and @b<>0
set @c=@a/@b
print @c
go
exec p1 @a=2,@op='/',@b=1
解法2
create procedure p_a
@a float,@b float,@op char(2)
as
declare @c float
set @c=case @op
when @op='+'then '@a+@b'
when @op='-'then '@a-@b'
when @op='*'then '@a*@b'
when @op='/'then '@a/@b'
end
go
5由1加到100，并且不计偶数。
create procedure p_sum
as
declare @i int
set @i=1
ag:
if(@i%2=1)and(@i<=100)
set @i=@i+1
goto ag
end
print @i
go

6 已知SQRT(i)的算术平方根，求1+SQRT（100）

create function p1(@i int)
returns fioat
as
begin
declare @c fioat
set @c=SQRT(@i)+1
return
end
go
select p1(100)

7,在3g1班中，选出数据库成绩最高前三名，并且备注标注‘优秀学生’

Create table c3g1
(
姓名 nvarchar（6）,
性别 char（2），
成绩 float.
)
go
Select *from c3g1
Order by (成绩) desc
go
Select identity(int,1,1) as ‘名次’，姓名,性别,成绩 into c3g3 from c3g1
Alter table c3g3
Alter column 备注 nvarchar(4)
go
Update c3g3
Set 备注=’优秀学生’
Where 名次<=3
Go

8证明cos2a+sin2a=1
create function p(@a float(8))
returns nvarchar(10)
as
begin
declare @c nvarchar(10),@b float(8)
if(@a<6.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)
go

9某线路由A,B,C,D,E,F,G各站点组成，A是起点，G是终点，求中途站相邻路程。
create procedure p_nm
as
select identity(int,1,1) as 'x' , S,T into t2 from t1 where S<>'A'
Declare @loop int
set @loop=1
while @loop<=(select count(x) from t2)
Begin
select S,T into RT1 from t1
where T=(select T from t2 where x=@loop)
select S,T into RT2 from t1
where S=(select T from t2 where x=@loop)
select a.S,a.T,b.S,b.T from RT1 a,RT2 b
select @loop=@loop+1
end
go

EXEC p_nm