execute sp_foreignkey student_course,student,student_id
execute sp_foreignkey courses,material,material_id
execute sp_foreignkey teacher,department,department_id
execute sp_foreignkey teacher,specialty,specialty_id
/*插入数据*/
insert class values('00001','01计算机应用一班','王健','26959228')
insert class values('00002','01生物食品一班','王明','26959200')
insert class values('00003','01先进制造CAD','高吉','26959322')
go
select * from class
go
insert courses values('1001','面向对象程序设计',2,'A001')
insert courses values('1002','大学英语',6,'A002')
insert courses values('1003','大型数据库',4,'A003')
insert courses values('1004','毛泽东思想概论',4,'A004')
go
select * from courses
go
insert department values('01161','计算机系','刘兴东','张明','26956101')
insert department values('01160','生物系','何丽','王武','26956111')
insert department values('01167','先进制造系','李平','赵敏','26956299')
go
select * from department
go
insert material values('A001','C++高级编程','机械工业出版社',30)
insert material values('A002','走遍美国','外文出版社',28)
insert material values('A003','sybase数据库实例教程','机械工业出版社',35)
insert material values('A004','毛泽东思想概论','教育出版社',30)
go
select * from material
go
insert teacher values('T001','张三','男',42,'高级教师','14','01161')
insert teacher values('T002','李四','男',38,'高级教师','05','01160')
insert teacher values('T003','王五','女',40,'高级教师','05','01167')
insert teacher values('T004','李军品','女',22,'高级教师','22','01161')
go
select * from teacher
go
insert specialty values('14','应用','徐露','26956777','01161')
insert specialty values('05','食品','吴晋','26956007','01160')
insert specialty values('22','CAD','何莉','26956907','01167')
go
select * from specialty
go
insert teacher_course values('T001','1001','A001',72,'14')
insert teacher_course values('T002','1002','A002',60,'05')
insert teacher_course values('T003','1003','A003',64,'05')
insert teacher_course values('T004','1004','A004',70,'22')
go
select * from teacher_course
go
insert student values('011611443','巫奇贤','男','00001','14','01161')
insert student values('011600507','张玉娟','女','00002','05','01160')
insert student values('011672212','张娟','女','00003','22','01167')
insert student values('011611442','何均','女','00001','14','01161')
insert student values('011600517','李元','女','00002','05','01160')
insert student values('011672202','王郁夫','男','00003','22','01167')
go
select * from student
go
insert student_course values('1001','011611443',90)
insert student_course values('1002','011611443',89)
insert student_course values('1003','011611443',70)
insert student_course values('1004','011611443',88)
insert student_course values('1001','011600507',60)
insert student_course values('1002','011600507',59)
insert student_course values('1003','011600507',42)
insert student_course values('1004','011600507',28)
insert student_course values('1001','011672212',86)
insert student_course values('1002','011672212',89)
insert student_course values('1003','011672212',72)
insert student_course values('1004','011672212',98)
go
select * from student_course
go
/*删除主表的主键值时，级联删除从表中与之相对应的外键值*/
create trigger tri_delete_classid
on class
for delete
as
delete student from student,deleted where student.class_id=deleted.class_id
delete class where class_id='0116114'
/*计算某班级的某一门课程的平均成绩，要求使用存储过程来实现*/
create procedure pro_avemark
@course_id char(8),@class_id char(8)
as
select avg(mark) from st
