学生信息管理(内附答案)

学生信息管理系统
2．任务概述
○1 新生入校，首先报到并进行登记注册，然后到财务部门缴费。根据学生
被录取的专业，持缴费单到所在系部报到注册。
○2 到系或部注册后，根据选报专业分配到班级
○3 进入正常大学生活，参加各种教学活动，学期期末要求每位同学参加各
科的期末考试。
○4 考试结束，由各系或部将每位学生的考试成绩及其他一些相关信息记录
到每个同学的档案中
根据前面得到的各个关系，现把它们转化为数据表。
① 学生信息表 ②成绩表 ③课程表
表1 学生信息表的结构

表2 成绩表
表2课程表
3．评判标准
序号 任务 分数
1 每张表设置相应的主键，表也表之间设置相应的外键 20
2 用于check约束限制性别项只能输入“男”或“女” 10
3 用insert 语句插入相应三个表的内容 10
4 创建视图查询各个学生的学号、姓名、课程名、成绩 20
5 查询平均成绩大于70分的课程，要求显示课程号和平均成绩 20
6 创建一个存储过程，要求有两个输入参数，输入学生的学号、姓名，显示相应选择的课程名和成绩 20

答案如下：
------------------------------------------
/**** (六)学生信息管理系统 ****/
------------------------------------------

---------------创建数据库--------------
CREATE DATABASE StuManageSystem
GO

USE StuManageSystem
GO

---------------创建学生信息表--------------
CREATE TABLE StudentInfo
(
stuID INT PRIMARY KEY, --学号
[name] CHAR(12) NOT NULL, --姓名
sex BIT DEFAULT 1, --性别
age INT CHECK( age > 0 ), --年龄
dept VARCHAR(50), --所在院系
class VARCHAR(20), --班级名
enrolmentTime DATETIME --入学时间
)

-----------------创建课程表----------------
CREATE TABLE CourseInfo
(
courseID CHAR(8) PRIMARY KEY, --课程号
courseName VARCHAR(20), --课程名
prefixCourse CHAR(8) --先修课
)

-----------------创建成绩表----------------
CREATE TABLE AcademicAchievement
(
stuID INT FOREIGN KEY --学号
REFERENCES StudentInfo(stuID),
courseID CHAR(8) FOREIGN KEY --课程号
REFERENCES CourseInfo(courseID),
achievement INT CHECK(achievement BETWEEN 0 AND 100) --成绩>=0 & 成绩<=100
)

----------------插入三个表的内容------------
INSERT INTO StudentInfo(stuID,name,sex,age,dept,class,enrolmentTime)
VALUES (1,'张三',1,20,'计算机系','软件001班','2011-02-16')

INSERT INTO StudentInfo(stuID,name,sex,age,dept,class,enrolmentTime)
VALUES (2,'王五',0,18,'计算机系','软件001班','2011-02-18')

INSERT INTO CourseInfo(courseID,courseName,prefixCourse)
VALUES ('COMP0002','计算机系统原理','COMP0001')

INSERT INTO CourseInfo(courseID,courseName,prefixCourse)
VALUES ('COMP0001','C语言',NULL)

INSERT INTO AcademicAchievement(stuID,courseID,achievement)
VALUES (1,'COMP0001',85)

INSERT INTO AcademicAchievement(stuID,courseID,achievement)
VALUES (2,'COMP0002',95)

-----------查询各个学生的学号、姓名、课程名、成绩-------
SELECT T1.stuID,T1.name,T3.courseName,T2.achievement
FROM StudentInfo AS T1
INNER JOIN AcademicAchievement AS T2
ON T1.stuID = T2.stuID
INNER JOIN CourseInfo AS T3
ON T2.courseID = T3.courseID

----查询平均成绩大于70分的课程，要求显示课程号和平均成绩---
SELECT T1.courseID,AVG(achievement)
FROM CourseInfo AS T1
INNER JOIN AcademicAchievement AS T2
ON T1.courseID = T2.courseID
GROUP BY T1.courseID
HAVING AVG(T2.achievement)>70