SQL第五章习题

use [Stu1]

create table people (
num char(9) primary key ,
pname char(9) ,
age smallint check(age<=60),
pmoney smallint,
position char(9),
jobnum char (9),

)

create table 部门(
jobnum char(9) ,
名称 char(9),
经理名 char(9),
电话 char(9),
constraint constraint_jobnum primary key (jobnum)
)

alter table 部门 add constraint from_people_jobnum foreign key(jobnum) references  on people(jobnum )  on update cascade 

第二题
(1)

 create table score (
   学科 char(9),
   分数段 char(9),

   人数 smallint ,
   primary  key (学科,分数段)
   )

if exists(select * from sys.objects where name='pro_tran')
 drop procedure pro_tran
--不能一起用
 create procedure pro_tran 
 as 
 begin transaction trans
 declare 
 @num60 smallint,
 @num60_80 smallint,
 @num80_100 smallint ;
 
 select @num60=count(*) from SC where Grade <60 and Cno ='离散'

 select @num60_80=count(*) from SC where Grade <80 and Grade >60 and Cno ='离散'
 select @num80_100=count(*) from SC where Grade <100 and Grade>80 and   Cno ='离散'

 begin 

 insert into score values('离散','60-80',@num60_80)
 insert into score values('离散','80-100',@num80_100)
commit transaction trans --这一句和begin对应
return 
--记得结束
 end

测试

exec pro_tran
select *from score 

(2)任意的平均成绩

 create table avgscore(
 学科 char(9) primary key, 
 成绩 smallint ,

 )

if exists(select * from sys.objects where name='pro_tran')
drop procedure pro_tran

create procedure pro_tran
@cno char(9)
as 
begin transaction trans
declare 
@num  smallint;
select @num=avg(Grade)
from SC 
where Cno =@cno
insert into avgscore values(@cno,@num)
commit transaction trans 
return ;

exec pro_tran 
@cno='离散'
select *from avgscore
exec pro_tran 
@cno='数学'
select *from avgscore

(3)

alter table SC add scoreposition char(2)

if exists(select * from sys.objects where name='pro_tran')
drop procedure pro_tran

create procedure pro_tran

as 
begin transaction trans
update SC set scoreposition ='a'
where Grade >=90 and Grade<=100
update SC set scoreposition ='b'
where Grade >=80 and Grade<90
update SC set scoreposition ='c'
where Grade >=70 and Grade<80
update SC set scoreposition ='d'
where Grade >=60 and Grade<70
update SC set scoreposition ='d'
where  Grade<60
commit transaction trans 
return ;

exec pro_tran 

select *from SC

总结
存储过程的创建结构：
create procedure <名称>
<定义变量>–类似函数形参
–begin transaction <名称>
declare --局部变量
begin
<代码体>
commit transaction <名称>
|- - return ;
end
注意条件在存储过程中的重要性。