where course_id=@course_id
go
exec pro_avemark "1001","00001"
/*按班级查询某门课程的学生成绩.要求显示如下信息:学号、姓名、性别、课程、成绩*/
creat procedure pro_stumark
@class_id char(8),@course_id char(8)
as
select student.student_id,student_name,student_sex,course_id,mark
from student,student_course
where class_id=@class_id
go
pro_stumark “00001”,”1001”
/*按某系、某专业给出教师承担的课程信息要求显示如下信息：
教师ID、姓名、性别、职称、课程、计划学时、学分 */
create procedure pro_teachermessage
@dep_id char(8),@spe_id char(8)
as
select teacher.teacher_id,teacher_name,teacher_sex,title,course_name,time,course_mark
from teacher,teacher_course,courses,department,specialty
where teacher.teacher_id=teacher_course.teacher_id
and teacher_course.course_id=courses.course_id
and department.department_id=@dep_id
and specialty.specialty_id=@spe_id
and teacher.department_id=@dep_id
and teacher.specialty_id=@spe_id
go
pro_teachermessage "01161","14"
/*查询某名教师所承担课程的情况*/
create procedure pro_teacourse
@teacher_id char(8)
as
select teacher_name,title,courses.course_name
from teacher,teacher_course,courses
where teacher_course.course_id=courses.course_id
and teacher_course.teacher_id==@teacher_id
and teacher.teacher_id==@teacher_id
go
pro_teacourse "T001"
/*查询某个班的班长名字和联系电话*/
create procedure pro_master
@class_id char(8)
as
select class_name,master,class_tel from class
where class.class_id=@class_id
go
pro_master "00001"
/*查询某系教学秘书的名字和联系电话*/
create procedure pro_specialty
@specialty_id char(8)
as
select specialty_director ,specialty_tel from specialty
where specialty.specialty_id=@specialty_id
go
pro_speicalty "14"
/*查询某专业教师基本情况
要求显示如下信息：专业ID、专业名称、教师名字、教师性别、职称*/
create procedure pro_teaspecialty
@specialty_id char(8)
as
select specialty.specialty_id,specialty_name,teacher_name,teacher_sex,title
from teacher,specialty
where specialty.specialty_id=@specialty_id
go
pro_teaspecialty "14"
/*具有统计和计算功能：*/
/*统计某系部教师总人数，男教师和女教师各多少名*/
select count(*) from teacher
select count(*) from teacher where teacher_sex='女'
select count(*) from teacher where teacher_sex='男'
/*计算某专业教师的教学工作量*/
select teacher.teacher_name,course_id,time from teacher_course,teacher
where teacher_course.teacher_id=teacher.teacher_id
and teacher_course.specialty_id="14"
/*统计某学期课程的总学分*/
select sum(course_mark) from courses
/*统计某班级学生总人数*/
select count(*) from student where class_id='00001'
/*统计某专业学生总人数*/
select count(*) from student where specialty_id='14'
/*在学生-课程表、教师-课程表中，增加一个自动标识列，能唯一标识表中的每一行*/
/*方法1：在"Database"菜单选择"Options"选项，显示"SSM Dataase"对话框，选中"Auto identity"即可。*/
Alter table student_course
Add id numeric(3,0) identity
Go
Alter table teacher_course
Add id numeric(3,0) identity
Go
Select * from student_course
Select * from teacher_course
/*创建一个显示如下信息的视图：
学号、姓名、性别、出生日期、班级名称、联系方式、课程名称、成绩。将数据窗口建在该视图上*/
create view view_stud
as
select student_name,student_sex,class_name,class_tel,course_name,course_mark,mark
from student,class,courses,student_course
select * from view_stud
/*对数据库表列的约束，非
