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
2-7 《学生管理系统》选课管理模块
最新推荐文章于 2023-05-25 20:37:28 发布