2-6《学生管理系统》成绩管理模块

Create Database Student
on Primary
(name='Student',
filename='D:\boss\Student.mdf')
log on
(name='Student_log',
filename='D:\boss\Student_log.ldf')

use Student
go

Create table T_student_info
(
student_no CHAR(6)  not Null,
student_name  char(20) not Null,
sex char(2) not Null,
birth datetime not Null,
enter_date int not Null,
adress varchar(50) not Null,
)


Create table T_course_info
(
course_no  char(8)  not Null,
course_name char(50) not Null,
credit int not Null,
classhour int not Null,

)

Create table T_student_scores
(
course_no  char(8) not Null,
student_no char(6) not Null,
ordinary_scores decimal not Null,
end_scores decimal not Null,
total_scores decimal not Null,
)
--创建数据表间的关系及约束--
alter table T_student_info
add constraint PK_student_no primary key(student_no)

alter table T_course_info
add constraint PK_T_course_info primary key(course_no)

alter table T_student_scores
add constraint PK_T_student_scores primary key(student_no,course_no)

alter table T_student_scores
add constraint FK_T_student_scores_course_no foreign key(course_no ) references T_course_info(course_no )

alter table T_student_scores
add constraint FK_T_student_scores_student_no foreign key(student_no ) references T_student_info(student_no )

--①向每个表插入 3 条测试数据（样本数据包含下面题目中使用的数据）；--
insert into T_student_info values('10012','埃克','男','03/23/1991','2008','湖南长沙劳动东路 168 号')
insert into T_course_info values('101','计算机','10','250')
insert into T_student_scores values('101','10012','90','91','98')

--查询出出生年份在 1988 年-1995 年的所有学生信息；--
select *from [dbo].[T_student_info] where [birth] between '1988' and '1995'

--创建视图查询选修了“计算机”的学生姓名、平时成绩、期末成绩、总评成绩；--
go
create view 学生
as
select [course_name]课程名,[student_name]学生姓名,[ordinary_scores]平时成绩,[end_scores]期末成绩,[total_scores]总评成绩 from [dbo].[T_student_scores],
[dbo].[T_student_info],[dbo].[T_course_info]where [dbo].[T_student_info].student_no=
[dbo].[T_student_scores].student_no and [dbo].[T_course_info].course_no=[dbo].[T_student_scores].course_no 
and [course_name]='计算机'
go
--删除学号为“10012”的学生的所有成绩；--
delete from [dbo].[T_student_scores] where [student_no]='10012'

--创建存储过程，统计每门课程的选课人数、最高分、平均分。--
go
CREATE PROC 统计每门课程
(
@course_name char(50),
@ordinary_scores decimal ,
@end_scores decimal,
@total_scores decimal
)
as
select [course_name]课程,count(*)选课人数,max(total_scores)最高分,avg(total_scores)平均分 from [dbo].[T_course_info],[dbo].[T_student_scores]
group by [course_name]
go