2-5《学生管理系统》课程管理模块

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 int identity(1,1),
student_name  char(20),
sex char(2),
birth datetime,
enter_date int,
adress varchar(50),
primary key(student_no),
)

Create table T_course_info
(
course_no  int identity(1,1),
course_name char(50),
credit int,
classhour int,
primary key(course_no),
)

Create table T_student_scores
(
course_no  int ,
student_no int,
ordinary_scores float,
end_scores float,
total_scores float,
)

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 )

--①向 T_student_info 表插入数据：“102011，刘德华，男，03/23/1991，2009，湖南长沙劳动东路 168 号”；--
insert into T_student_info values('刘德华','男','03/23/1991','2009','湖南长沙劳动东路 168 号')

--查询出 1991 年出生的学生信息；--
select*from T_student_info where birth like '%1991%'

--查询选修了“数据结构”的学生姓名、平时成绩、期末成绩、总评成绩；--
select [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]='数据结构'

--④创建视图查询学生的学号、姓名、课程名、总评成绩；--
if exists(select [dbo].[T_student_info].[student_no],[student_name],[course_name],[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  )
drop view 学生
go
create view 学生
as
select [dbo].[T_student_info].[student_no],[student_name],[course_name],[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 
go
select [dbo].[T_student_info].[student_no],[student_name],[course_name],[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 

--统计每门课程的选课人数、最高分、平均分，并将统计结果保存在表 T_total 中。--
select [course_name]课程,count(*)选课人数,max([total_scores])最高分,avg([total_scores])平均分
into  T_total 
from [dbo].[T_course_info],[dbo].[T_student_scores] where 
[dbo].[T_course_info].course_no=[dbo].[T_student_scores].course_no 
group by [course_name]