【C#基础】【SQL数据库】小项目练习（高校数据库管理系统）

高校信息管理系统数据库设计

 

任务一、理解需求

 

北京某高校拟开发一套高校信息管理系统，该系统包括学生档案管理、学生成绩管理、教员信息管理、教务排课等模块，实现学生档案录入、修改、查询，成绩录入、修改、查询，教务排课管理等功能。要进行后台数据库的设计，以“学生成绩管理”为例，创建一个数据库。此模块具有成绩输入，成绩查询，名次排序，分数统计等功能。

 

现要求对“学生成绩管理”子模块进行数据库的设计并实现。

任务二、创建数据库

要求：数据库保存在F:\project目录下，数据库名为Student,主数据文件文件增长率为15%, 最大增长为1G，文件初始大小为3M。日志文件增长率为10% 。

利用企业管理器创建数据库实现此步骤。

 

任务三、创建相关表和约束

所有的表的设计如下：（建表操作用企业管理器完成）

1）与考试相关的学生信息表（stuInfo）如下：

字段名称

数据类型

说明

stuName

字符

学生姓名，该列必填，要考虑姓氏可能是两个字的，如欧阳俊雄

stuNo

字符

学号，主键，该列必填，学号不能重复，且必须是S253XX格式( X的取值范围为0-9之间的数字 )

stuSex

字符

学生性别，该列必填，且只能是“男”或“女”。因为男生较多，默认为“男”

stuAge

数字

学生年龄，该列必填，必须在15～50岁之间

stuSeat

数字

学生的座位号，该列必填，不用人工输入，采用自动编号方式

stuAddress

文本

学生地址，该列可不填，如没有填写，默认为“地址不详”

stuNative

字符

学员民族，该列可不填.

2）与成绩相关的学生成绩表（stuMarks）如下：

字段名称

数据类型

说明

examNo

字符

考号，主键，该列必填，且不能重复，必须是E200507XXXX格式 ( X表示为0-9之间的任意数字 )

stuNo

字符

学号，该列必填，且必须在学生基本信息表中有相关记录

writtenExam

数字

学生笔试成绩，数据必须是0～100分之间，不填默认为0分，可为null

labExam

数字

学生机试成绩，数据必须是0～100之间，不填默认为0分，可为null

 

任务四、插入数据

（数据插入，修改，删除，查询操作用查询分析器实现，保存SQL脚本）

学生基本信息表的测试数据如下：

stuName

stuNo

stuSex

stuAge

stuSeat（自动编号）

stuAddress

stuNative

张秋丽

s25301

男

18

 

北京海淀

 

李斯文

S25303

女

22

 

河南洛阳

苗族

李文才

S25302

男

31

 

 

 

欧阳俊雄

S25304

男

28

 

江西太原

 

张铭泽

S25305

男

22

 

广东惠州

 

曹丽

S25306

女

20

 

广东珠海

土家族

说明：

Ø  每位学生的座位号不用插入，从1开始自动编号

Ø  学生李文才的地址不详，所以地址一栏为空(提示：应该为其取缺省值’ 地址不详’)

Ø  民族信息没有的表示为null

学生成绩表的测试数据如下：

examNo

stuNo

writtenExam

labExam

E2005070001

S25301

80

58

E2005070002

S25302

50

缺考

E2005070003

S25303

97

82

E2005070004

S25304

请假

请假

E2005070005

S25305

80

60

E2005070006

S25306

93

92

说明：

Ø  考号为E2005070002的学员机试缺考

       提示：无故缺考者成绩按0分计算

Ø  欧阳俊雄（学号为S25304）因事请假，没有报考

       提示：请假者成绩录入空值，编号为E2005070004

 

任务五、数据查询

1.        查询两个表中的所有记录。检查数据插入是否正确。

2.        查询所有男学员的姓名，年龄。

3.        查询笔试成绩优秀的学员情况（75-100之间），显示学号、笔试成绩，并按笔试分数由高到底排序，笔试分数相同的，按机试成绩由高到低排序，笔试机试成绩都相同的，按学号末位由低到高排列。

4.        查询参加本次考试的学员成绩，包括学员姓名、学号，笔试成绩及机试成绩

5.        统计笔试考试平均分和机试考试平均分

6.        统计实际参加本次考试的学员人数.

7.        查询没有通过考试的人数（笔试或机试小于60分）

8.        查询学员成绩、显示学号、笔试成绩、机试成绩及该学员笔试机试平均分

9.        查询本次考试中，广东省学员的笔试平均分和机试平均分

10.    查询本次考试中，男女学员笔试最高分.

11.    排名次（按平均分从高到低排序），显示学号和平均分。（平均分=（机试+笔试）/2）

12.    排名次（按平均分从高到低排序），显示姓名、笔试成绩、机试成绩和平均分

13.    根据平均分显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分。

 

任务六、数据修改

a)        由于笔试试卷难度过大，改试卷后经商议决定：所有学员的笔试成绩都提高5分，超过100分的按100分计算（提示：可分两步进行）。

b)  因工作人员失误，没有为学员民族(stuNative)字段设置默认值为”汉族”，导致其它民族学员信息顺利录入，而汉族学员该字段值为空值，现在请通过采用较好的办法将汉族学员的信息修改。

 

任务七、执行数据库的分离和附加操作

 

 

 

 【在这里只上传SQL查询了，数据库就不传了】

use Studentgo--任务四、插入数据--学生信息表insert into stuInfo values ('张秋丽','S25301','男',18,'北京海淀',null)insert into stuInfo values ('李斯文','S25303','女',22,'河南洛阳','苗族')insert into stuInfo values ('李文才','S25302','男',31,default,null)insert into stuInfo values ('欧阳俊雄','S25304','男',28,'江西太原',null)insert into stuInfo values ('张铭泽','S25305','男',22,'广东惠州',null)insert into stuInfo values ('曹丽','S25306','女',20,'广东珠海','土家族')--学生成绩表insert into stuMarks values ('E2005070001','S25301',80,58)insert into stuMarks values ('E2005070002','S25302',50,0)insert into stuMarks values ('E2005070003','S25303',97,82)insert into stuMarks values ('E2005070004','S25304',null,null)insert into stuMarks values ('E2005070005','S25305',80,60)insert into stuMarks values ('E2005070006','S25306',93,92)--任务五、数据查询--1.查询两个表中的所有记录。检查数据插入是否正确。select * from stuInfoselect * from stuMarks--2.查询所有男学员的姓名，年龄。select stuName 姓名,stuAge 年龄 from stuInfo where stuSex='男'--3.查询笔试成绩优秀的学员情况（75-100之间），显示学号、笔试成绩，并按笔试分数由高到底排序，笔试分数相同的，按机试成绩由高到低排序，笔试机试成绩都相同的，按学号末位由低到高排列。select stuNo 学号,writtenExam 笔试成绩 from stuMarks where writtenExam>=75 and writtenExam<=100order by writtenExam desc,labExam desc,right(stuNo,1) asc--4.查询参加本次考试的学员成绩，包括学员姓名、学号，笔试成绩及机试成绩select stuName 学员姓名,stuInfo.stuNo 学号,writtenExam 笔试成绩,labExam 机试成绩 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNo--5.统计笔试考试平均分和机试考试平均分select avg(writtenExam) 笔试考试平均分,avg(labExam) 机试考试平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNo--6.统计实际参加本次考试的学员人数.select count(writtenExam) 实际参考人数 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNo--7.查询没有通过考试的人数（笔试或机试小于60分）select count(*) 未通过人数 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNowhere writtenExam<60 or labExam<60--8.查询学员成绩、显示学号、笔试成绩、机试成绩及该学员笔试机试平均分select stuName 学员姓名,stuInfo.stuNo 学号,writtenExam 笔试成绩,labExam 机试成绩,((writtenExam+labExam)/2) 该学员笔试机试平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNo--9.查询本次考试中，广东省学员的笔试平均分和机试平均分select avg(writtenExam) 笔试考试平均分,avg(labExam) 机试考试平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNowhere stuAddress like '%广东%'--10.查询本次考试中，男女学员笔试最高分.select stuSex 学员性别,max(writtenExam) 笔试最高分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNogroup by stuSex--11.排名次（按平均分从高到低排序），显示学号和平均分。（平均分=（机试+笔试）/2）select stuInfo.stuNo 学号,((writtenExam+labExam)/2) 平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNoorder by ((writtenExam+labExam)/2) desc--12.排名次（按平均分从高到低排序），显示姓名、笔试成绩、机试成绩和平均分select stuName 学员姓名,writtenExam 笔试成绩,labExam 机试成绩,((writtenExam+labExam)/2) 平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNoorder by ((writtenExam+labExam)/2) desc--13.根据平均分显示前两名信息，包括姓名、笔试成绩、机试成绩、平均分。select top 2 stuName 学员姓名,writtenExam 笔试成绩,labExam 机试成绩,((writtenExam+labExam)/2) 平均分 from stuInfoinner join stuMarks on stuInfo.stuNo=stuMarks.stuNoorder by ((writtenExam+labExam)/2) desc--六、数据修改--a)由于笔试试卷难度过大，改试卷后经商议决定：所有学员的笔试成绩都提高5分，超过100分的按100分计算（提示：可分两步进行）。update stuMarks set writtenExam=100 where writtenExam>=95update stuMarks set writtenExam=writtenExam+5 where writtenExam<95--b)因工作人员失误，没有为学员民族(stuNative)字段设置默认值为”汉族”，导致其它民族学员信息顺利录入，而汉族学员该字段值为空值，现在请通过采用较好的办法将汉族学员的信息修改。update stuInfo set stuNative='汉族' where stuNative is null--七、保存,执行数据库的分离和附加操作