2-7 《学生管理系统》选课管理模块

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

use StudentDB
go

Create table T_student
(
Stud_id VARCHAR(10) not Null,
Stud_name  varchar(20) not Null,
Stud_sex  char(2) not Null,
Birth_date datetime not Null,
Entry_Date datetime not Null,
Mobile  varchar(11) not Null,
Birth_place varchar(30) not Null,
Reserve char(50) not Null,
)


Create table T_course
(
Course_id varchar(10) not Null,
Course_name varchar(100) not Null,
Teacher_name varchar(30) not Null,
)

Create table T_score
(
Score_id bigint not Null,
Course_id varchar(10) not Null,
Stud_id  VARCHAR(10) not Null,
Score decimal not Null,
)

--添加主键，外键--
alter table T_student
add constraint PK_T_student primary key(Stud_id)

alter table T_course
add constraint PK_T_course primary key(Course_id)

alter table T_score
add constraint PK_T_score primary key(Score_id )

alter table  T_score
add constraint FK_T_score_Course_id foreign key(Course_id) references T_course(Course_id )
alter table  T_score
add constraint FK_T_score_Stud_id  foreign key(Stud_id ) references T_student(Stud_id  )


--①向每个表插入 3 条测试数据（样本数据包含下面题目中使用的数据）；--
insert into T_student values('100111','米莫尼','男','04/03/1990','4/5/2020','1346739241','汉','好学生')
insert into T_student values('100221','米尼','女','04/09/1999','4/5/2020','1344356241','汉','好学生')
insert into T_student values('100333','米谢谢','女','04/09/2000','4/5/2020','1344351231','汉','好学生')

insert into T_course values('108','数据库原理','你晓得')
insert into T_course values('111','日语','不晓得')
insert into T_course values('105','软件技术','有关的')
insert into T_course values('115','应用程序','没关系')

insert into T_score values('10086','108','100111','95')
insert into T_score values('10087','111','100221','91')
insert into T_score values('10017','105','100333','90')

--②查询出所有选修了“数据库原理”课程的学生学号、姓名和籍贯；--
select [Course_name],[dbo].[T_student].[Stud_id],[Stud_name],[Birth_place] from [dbo].[T_course],[dbo].[T_student],[dbo].[T_score] where[dbo].[T_course].Course_id=[dbo].[T_score].Course_id and 
[dbo].[T_score].Stud_id=[dbo].[T_student].Stud_id and [Course_name]='数据库原理'

--③删除所有选修“日语”的同学的选课记录；--************有错误********
delete from T_course where '日语' in(select [Course_name] from [dbo].[T_course],[dbo].[T_student],[dbo].[T_score] 
where[dbo].[T_course].Course_id=[dbo].[T_score].Course_id and 
[dbo].[T_score].Stud_id=[dbo].[T_student].Stud_id 
 )

 --查询出“数据库原理”这门课的最高成绩；--
 select [Course_name]课程,max(Score)最高分 from [dbo].[T_score],[dbo].[T_course] where
  [dbo].[T_score].[Course_id]=[T_course].Course_id and [Course_name]='数据库原理'
  group by [Course_name]

  --查询有哪些课程没有被任何同学选修；--
  select Course_id,Course_name from [dbo].[T_course] where Course_id not in(
  select [dbo].[T_score].Course_id
  from [dbo].[T_score])

  --创建视图，查询指定课程名称的平均成绩--
  go
create view 平均成绩
as
select [Course_name]课程,avg([Score])平均分 from[dbo].[T_course],[dbo].[T_score] where [dbo].[T_course].[Course_id]=[T_score].Course_id
and [Course_name]='软件技术'
group by  [Course_name]
go