go
insert xskc
values('0103','0203','01161111',95)
go
insert xskc
values('0103','0203','01161112',75)
go
insert xskc
values('0103','0203','01161113',80)
go
insert xskc
values('0104','0204','01161104',100)
go
insert xskc
values('0104','0204','01161114',90)
go
insert xskc
values('0104','0204','01161115',89)
go
insert xskc
values('0104','0204','01161116',79)
go
insert xueshen
values('01161101','ssd','b','0101' ,'1981-03-06','0501' )
go
insert xueshen
values('01161105','smd','g','0101','1981-04-06','0501' )
go
insert xueshen
values('01161106','spd','g','0101','1981-04-12','0501' )
go
insert xueshen
values('01161107','smd','b','0101','1981-12-06','0501' )
go
insert xueshen
values('01161102','yyd','b','0102','1981-11-27' ,'0502' )
go
insert xueshen
values('01161108','yld','b','0102','1981-04-24' ,'0502' )
go
insert xueshen
values('01161109','ynd','b','0102' ,'1981-07-06' ,'0502' )
go
insert xueshen
values('01161110','ywd','g','0102','1981-04-10' ,'0502' )
go
insert xueshen
values('01161103','kkd','g','0103','1981-08-06' ,'0503' )
go
insert xueshen
values('01161111','kwd','g','0103','1981-09-06' ,'0503' )
go
insert xueshen
values('01161112','kpd','g','0103','1981-04-09' ,'0503' )
go
insert xueshen
values('01161113','kmd','g','0103','1981-05-06' ,'0503' )
go
insert xueshen
values('01161104','wmd','g','0104','1981-04-16' ,'0504' )
go
insert xueshen
values('01161114','wpd','g','0104','1981-08-06' ,'0504' )
go
insert xueshen
values('01161115','wld','g','0104','1981-04-29' ,'0504' )
go
insert xueshen
values('01161116','wgd','g','0104','1981-09-16' ,'0504' )
go
/*1删除主表的主键值时，级联删除从表中与之相对应的外键值*/
create trigger id_delete_banji
on banji
for delete
as
delete jskc
from jskc , deleted
where jskc.bj_id=deleted.bj_id
delete xskc
from xskc ,deleted
where xskc.bj_id=deleted.bj_id
delete xueshen
from xueshen, deleted
where xueshen.bj_id=deleted.bj_id
go
/*2计算某班级的某一门课程的平均成绩，要求使用存储过程来实现*/
create procedure pro_chenji
@banjiid char(8) ,@kechenid char(8)
as
select avg(chengji) from banji,xskc
where banji.bj_id=@banjiid and xskc.kc_id=@kechenid and banji.bj_id=xskc.bj_id
return 0
go
/*3在学生—课程表，教师—课程表中，增加一个自动标识列，能唯一标识表中的每一行*/
alter table xskc
add xh numeric(5,0) identity
go
alter table jskc
add xh numeric(5,0) identity
go
/*4创建一个显示如下信息的视图 学号、姓名、性别、出生日期、班级名称、联系方式、课程名称、成绩。
将数据窗口建在该视图上。*/
create view view_mai
as
select a.xuehao,a.name,a.xb,a.rq ,b.bj_name,b.phone,c.kc_name,d.chengji
from xueshen a,banji b,kecheng c,xskc d
where a.bj_id=d.bj_id and b.bj_id=d.bj_id and c.kc_id=d.kc_id
go
/*5对数据库表列的约束，非常重要的列，在client和server端都进行约束。对于在录入数据时，需要立即响应对错的约束，应该用delphir完成*/
create rule bj_id
as @bj_id between "0000" and "4444"
go
sp_bindrule bj_id, "banji.bj_id"
go
create rule xb
as
@sex="b"
or
@sex="g"
go
sp_bindrule xb , "xueshen.xb"
go
/*6成事务处理和并发处理*/
create table table_a(
x smallint null,
y smallint null)
go
insert table_a
values(1,100)
go
insert table_a
values(2,100)
go
create table table_b(
z smallint null)
go
