学生管理系统题库答案

--学生管理系统题库

--一、数据库和表的创建

--1．创建“学生管理”数据库，只有一个数据文件和一个日志文件，文件名称分别为“学生管理”和“学生管理_log”，初始大小都为3MB,增长方式分别为5%和1MB，数据文件最大为100MB，日志文件最大可增大到50MB。

CREATE DATABASE 学生管理

ON

(

NAME=学生管理,

FILENAME='D:\考试\学生管理.MDF',

SIZE=3MB,

MAXSIZE=100MB,

FILEGROWTH=5%

)

LOG ON

(

NAME=学生管理_LOG,

FILENAME='D:\考试\学生管理_log.LDF',

SIZE=3MB,

MAXSIZE=50MB,

FILEGROWTH=1MB

)

--2.学生信息表

--学号char（12）非空，姓名nvarchar（10）非空，性别nchar（1），出生日期datetime，政治面貌bit，学校履历ntext。

USE 学生管理

GO

CREATE TABLE 学生信息

(

学号char(12) NOT NULL,

姓名nvarchar(10)NOT NULL,

性别nchar(1),

出生日期datetime,

政治面貌bit,

学校履历ntext

)

--3．课程信息表

--课程编号char（6）非空，课程名称nvarchar（20）非空，学分tinyint，限选人数tinyint，已选人数tinyint。

CREATE TABLE 课程信息

(

课程编号char(6) NOT NULL,

课程名称nvarchar(20) NOT NULL,

学分tinyint,

限选人数tinyint,

已选人数tinyint

)

--4．成绩信息表

--学号参照“学生信息”表“学号”字段取值，

--课程编号参照“课程信息”表“课程编号”字段取值，

--成绩decimal（5，2）。

CREATE TABLE 成绩信息

(

学号char(12)NOT NULL,

课程编号char(6) NOT NULL,

成绩decimal(5,2)

)

--二、表约束的创建

--1．为学生信息表声明主码：将学号字段设置为学生信息表的主键。

ALTER TABLE 学生信息

ADD CONSTRAINT PK_XS_XH PRIMARY KEY(学号)

--2．为课程信息表声明主码：将课程编号字段设置为课程信息表的主键。

ALTER TABLE 课程信息

ADD CONSTRAINT PK_KC_KCBH PRIMARY KEY(课程编号)

--3．为成绩信息表声明主码：将（学号，课程编号）联合字段设置为成绩信息表的主键。ALTER TABLE 成绩信息

ADD CONSTRAINT PK_CJ_XHKCBH PRIMARY KEY(学号,课程编号)

--4．为成绩信息表声明外码：为学生信息表和成绩信息表创建关联关系。

ALTER TABLE 成绩信息

ADD CONSTRAINT FK_CJ_XH FOREIGN KEY(学号) REFERENCES 学生信息(学号) on delete cascade

--5．为成绩信息表声明外码：为课程信息表和成绩信息表创建关联关系。

ALTER TABLE 成绩信息

ADD CONSTRAINT FK_CJ_KCBH FOREIGN KEY(课程编号) REFERENCES 课程信息(课程编号)

--6．为学生信息表设置约束：性别字段只能输入“男”或“女”。

ALTER TABLE 学生信息

ADD CONSTRAINT CK_XS_XB CHECK(性别in ('男','女'))

--7．为学生信息表设置约束：学生入学年龄必须在0~25岁之间。

ALTER TABLE 学生信息

ADD CONSTRAINT CK_XS_NL CHECK(YEAR(GETDATE())-YEAR(出生日期) BETWEEN 0 AND 25)

ALTER TABLE 学生信息

DROP CONSTRAINT CK_XS_NL

ALTER TABLE 学生信息

ADD CONSTRAINT CK_XS_NL CHECK(DATEDIFF("YYYY",出生日期,GETDATE()) BETWEEN 0 AND 25)

--8．为课程信息表设置约束：学分必须介于0~5之间。

ALTER TABLE 课程信息

ADD CONSTRAINT CK_KC_XF CHECK(学分BETWEEN 0 AND 5)

--9．为课程信息表设置约束：已选人数必须介于0到限选人数之间。

ALTER TABLE 课程信息

ADD CONSTRAINT CK_KC_YXRS CHECK(已选人数BETWEEN 0 AND 限选人数) --10．为成绩信息表设置约束：成绩必须介于0~100之间。

ALTER TABLE 成绩信息

ADD CONSTRAINT CK_KC_CJ CHECK(成绩BETWEEN 0 AND 100)

--11．为学生信息表设置约束：将性别字段的默认值设置为“女”。

ALTER TABLE 学生信息

ADD CONSTRAINT D_XS_XB DEFAULT '女' FOR 性别

--12．为学生信息表设置约束：将政治面貌字段的默认值设置为0。

ALTER TABLE 学生信息

ADD CONSTRAINT D_XS_ZZMM DEFAULT 0 FOR 政治面貌

--13．为课程信息表设置约束：将限选人数字段的默认值设置为250。

ALTER TABLE 课程信息

ADD CONSTRAINT D_KC_XXRS DEFAULT 250 FOR 限选人数

--14．为课程信息表设置约束：将已选人数字段的默认值设置为0。

ALTER TABLE 课程信息

ADD CONSTRAINT D_KC_YXRS DEFAULT 0 FOR 已选人数

--三、数据查询、视图的创建、索引的创建

--为方便数据查询，现将本试题涉及的3个表的基本数据列示如下：

--表1：学生信息表

--学号姓名性别出生日期政治面貌学校履历

INSERT INTO 学生信息V ALUES

('200801010001','王琳琳','女','1990-03-14',1,NULL),

('200801010002','江军','男','1989-04-21',0,NULL),

('200801010003','沈俊逸','男','1991-01-03',0,NULL),

('200801010004','赵妞','女','1990-05-12',0,NULL)

--表2：课程信息表

--课程编号课程名称学分限选人数已选人数

INSERT INTO 课程信息V ALUES

('A08001','C#程序设计',4,250,0),

('A08002','大型数据库',3,250,0),

('A08003','电子商务',2,250,0)

--表3：成绩信息表

--学号课程编号成绩

INSERT INTO 成绩信息V ALUES

('200801010001','A08001',89),

('200801010001','A08002',94),

('200801010001','A08003',58),

('200801010002','A08001',88),

('200801010003','A08001',79)

--1．列示学生信息表中前3条学生的基本信息。

SELECT * FROM 学生信息

--2．列示学生信息表中学生的学号、姓名、年龄字段的基本信息。

SELECT 学号,姓名,DATEDIFF("YYYY",出生日期,GETDA TE()) AS 年龄

FROM 学生信息

SELECT 学号,姓名,YEAR(GETDA TE())-YEAR(出生日期) AS 年龄

FROM 学生信息

--3．列示性别为“女”并且是党员的学生的基本信息。

SELECT * FROM 学生信息

WHERE 性别='女' AND 政治面貌=1

--4．列示学分超过2的课程的课程编号、课程名称、学分、已选人数等信息。

SELECT 课程编号,课程名称,学分,已选人数

FROM 课程信息WHERE 学分>2

--5．列示姓“王”的学生的所有信息。

SELECT * FROM 学生信息

WHERE 姓名LIKE '王%'

--6．列示姓名中带“俊”字的学生的所有信息。

SELECT * FROM 学生信息

WHERE 姓名LIKE '%俊%'

--7．列示所有课程的基本信息，并按学分降序排列。

SELECT * FROM 课程信息

ORDER BY 学分DESC

--8．列示所有学生的基本信息并按照年龄升序排列。

SELECT *,DATEDIFF("YYYY",出生日期,GETDA TE()) AS 年龄

FROM 学生信息

ORDER BY DATEDIFF("YYYY",出生日期,GETDA TE())

--9．列示学号为“200801010001”的学生选修课程的课程编号、课程名称、学分、成绩等信息，并按照成绩的降序排列。

SELECT 课程信息.课程编号,课程名称,学分,成绩

FROM 课程信息,成绩信息

WHERE 课程信息.课程编号=成绩信息.课程编号

ORDER BY 成绩DESC

--10．列示选修了课程编号为“A08001”的学生的学号、姓名、性别、出生日期等信息，并按照出生日期升序排列。

SELECT 学生信息.学号,姓名,性别,出生日期

FROM 学生信息JOIN 成绩信息ON 学生信息.学号=成绩信息.学号join 课程信息ON 课程信息.课程编号=成绩信息.课程编号

WHERE 成绩信息.课程编号='A08001'

ORDER BY 出生日期

--11．统计并列示每门课程的课程编号以及选修该课程的总人数。

SELECT 课程编号,COUNT(学号) AS 总人数

FROM 成绩信息

GROUP BY 课程编号

--12．统计并列示每个学生的学号、姓名以及该学生选修课程的门数信息。

SELECT 学生信息.学号,姓名,COUNT(课程编号) AS 门数

FROM 学生信息,成绩信息

WHERE 学生信息.学号=成绩信息.学号

GROUP BY 学生信息.学号,姓名

--13．列示每个学生的学号、姓名以及该学生目前所有选修课程的平均成绩信息。SELECT 学生信息.学号,姓名,A VG(成绩) AS 平均成绩

FROM 学生信息,成绩信息

WHERE 学生信息.学号=成绩信息.学号

GROUP BY 学生信息.学号,姓名

--14．统计并列示每个学生的学号、姓名以及该学生选修课程的总学分。

SELECT 学生信息.学号,姓名,SUM(学分) AS 总学分

FROM 学生信息,成绩信息,课程信息

WHERE 学生信息.学号=成绩信息.学号AND 课程信息.课程编号=成绩信息.课程编号GROUP BY 学生信息.学号,姓名

--15.列示学分最高的课程编号、课程名称。

SELECT 课程编号,课程名称

FROM 课程信息

WHERE 学分=(SELECT MAX(学分) FROM 课程信息)

--16.列示'200801010001'学生成绩最高的课程编号。

SELECT 课程编号,成绩

FROM 成绩信息

WHERE 学号='200801010001' AND 成绩=(SELECT MAX(成绩) FROM 成绩信息)

--17.列示'200801010001'学生成绩最高的课程编号、课程名称。

SELECT 课程信息.课程编号,课程名称,成绩

FROM 学生信息,成绩信息,课程信息

WHERE 学生信息.学号=成绩信息.学号AND 课程信息.课程编号=成绩信息.课程编号AND 学生信息.学号='200801010001' AND 成绩=(SELECT MAX(成绩) FROM 成绩信息) --18.统计至少选修两门课程的学生学号。

SELECT 学号

FROM 成绩信息

GROUP BY 学号

HA VING COUNT(课程编号)>=2

--19.统计选修了'A08001'课程的学生的平均年龄。

SELECT A VG(DATEDIFF(YEAR,出生日期,GETDA TE()))

FROM 学生信息,成绩信息

WHERE 成绩信息.学号=学生信息.学号AND 成绩信息.课程编号='A08001'

--20．创建课程成绩信息查询的视图，其视图名为：CGQuery

--视图功能：列示学号、姓名、性别、政治面貌、课程编号、成绩的信息。

CREATE VIEW CGQUERY

AS

SELECT 学生信息.学号,姓名,性别,政治面貌,课程信息.课程编号,成绩

FROM 学生信息,成绩信息,课程信息

WHERE 学生信息.学号=成绩信息.学号AND 课程信息.课程编号=成绩信息.课程编号GO

SELECT * FROM CGQUERY

--21．创建学生成绩信息查询的视图，其视图名为：SGQuery

--视图功能：列示课程编号、课程名称、学分、成绩信息。

CREATE VIEW SGQuery

AS

SELECT 课程信息.课程编号,课程名称,学分,成绩

FROM 成绩信息,课程信息

WHERE 课程信息.课程编号=成绩信息.课程编号

GO

SELECT * FROM SGQUERY

--四、数据更新

--1．为学生信息表添加一条学生数据，其具体信息如下：

--学号：200801010005，姓名：小沈阳，性别：男，出生日期：1988-09-21。

INSERT INTO 学生信息V ALUES('200801010005','小沈阳','男','1988-09-21',NULL,NULL) --2．为课程信息表添加一条课程数据，其具体信息如下：

--课程编号：A08004，课程名称：网页设计，学分：3

INSERT INTO 课程信息(课程编号,课程名称,学分)V ALUES('A08004','网页设计',3)

--3．为成绩信息表添加一条数据，其具体信息如下：

--学号：200801010004，课程名称：A08003，成绩：90

INSERT INTO 成绩信息(学号,课程编号,成绩)V ALUES('200801010004','A08003',90)

--4．将学号为200801010003的学生的学校履历更新为：2008~2009年第一学期获得一等奖学金。

UPDATE 学生信息

SET 学校履历='2008~2009年第一学期获得一等奖学金'

WHERE 学号='200801010003'

--5．将课程编号为A08003的限选人数更新为200。

UPDATE 课程信息

SET 限选人数=200

WHERE 课程编号='A08003'

--6．将学号为200801010001的学生所修课程编号为A08003的课程成绩修改为86分。UPDATE 成绩信息

SET 成绩=86

WHERE 学号='200801010001'AND 课程编号='A08003'

--7．删除成绩信息表中成绩不及格的记录信息。

DELETE FROM 成绩信息

WHERE 成绩<60

--8．删除所有党员的信息。

DELETE FROM 学生信息

WHERE 政治面貌=1

--9．删除已选人数小于20的课程信息。

--10.将选修'电子商务'课程不及格的学生成绩全改为空值。

update 成绩信息

set 成绩=null

WHERE 成绩>60 AND 课程编号IN (SELECT 课程编号FROM 课程信息WHERE 课

程名称='电子商务')

--五、T-SQL编程

--1．创建一个标量函数getname，用于从学生信息表中根据学号返回学生姓名.

CREATE FUNCTION GETNAME(@XH CHAR(12))

RETURNS NV ARCHAR(100)

AS

BEGIN

DECLARE @XM NV ARCHAR(10),@STR V ARCHAR(100)

SELECT @XM=姓名FROM 学生信息WHERE 学号=@XH

SELECT @STR=@XH+'的学生的姓名是:'+@XM

RETURN @STR

END

GO

SELECT dbo.GETNAME('200801010002')

--2．创建一个内联表函数getstudent，用于从数据库中根据成绩返回学生的学号，姓名，所修课程名称。

CREATE FUNCTION GETSTDENT(@CJ DECIMAL(5,2))

RETURNS TABLE

AS

RETURN

SELECT 学生信息.学号,姓名,课程名称

FROM 学生信息,成绩信息,课程信息

WHERE 学生信息.学号=成绩信息.学号AND 课程信息.课程编号=成绩信息.课程编号AND 成绩=@CJ

GO

SELECT * FROM dbo.GETSTDENT(90.00)

--3. 定义一个名为“mytrans”的事务，提交该事务后，删除“学生信息”表中学号为“200801010004”的学生信息，同时也删除“成绩信息”表中的与之对应的信息。如果正确，提示“成功”；如果错误，回滚到原来状态，提示“错误”。

DECLARE @ERR1 INT

SET @ERR1=0

BEGIN TRAN MYTRANS

SA VE TRAN MY

IF EXISTS(SELECT * FROM 学生信息WHERE 学号='200801010004')

BEGIN

DELETE FROM 学生信息WHERE 学号='200801010004'

SET @ERR1=@ERR1+@@ERROR

IF @ERR1<>0

BEGIN

PRINT '删除学生信息失败'

ROLLBACK TRAN MY

END

ELSE

BEGIN

PRINT '成功'

COMMIT TRAN MYTRANS

END

END

ELSE PRINT '没有学生'

--4. 声明一滚动动态游标cursor_update，要求结果集为学生的学号、姓名、性别、出生时间、选修课程编号和成绩。

DECLARE cursor_update CURSOR

SCROLL DYNAMIC

FOR

SELECT 学生信息.学号,姓名,性别,出生日期,课程信息.课程编号,成绩

FROM 学生信息,成绩信息,课程信息

WHERE 学生信息.学号=成绩信息.学号AND 课程信息.课程编号=成绩信息.课程编号OPEN cursor_update

DECLARE @XH CHAR(12)

DECLARE @XM V ARCHAR(10)

DECLARE @XB CHAR(2)

DECLARE @CSSJ DATETIME

DECLARE @KCBH CHAR(6)

DECLARE @CJ DECIMAL(5,2)

FETCH NEXT FROM cursor_update INTO @XH,@XM,@XB,@CSSJ,@KCBH,@CJ

PRINT '学号'+SPACE(13)+'姓名'+SPACE(5)+'性别'+SPACE(5)+'出生时间'+SPACE(8)+'课程编号'+SPACE(6)+'成绩'

WHILE(@@FETCH_STATUS=0)

BEGIN

PRINT

@XH+SPACE(5)+@XM+SPACE(10-2*LEN(@XM))+@XB+SPACE(6)+CONVERT(CHAR(10 ),@CSSJ,120)+SPACE(7)+@KCBH+SPACE(7)+CONVERT(CHAR(8),@CJ)

FETCH NEXT FROM cursor_update INTO @XH,@XM,@XB,@CSSJ,@KCBH,@CJ

END

close cursor_update

deallocate cursor_update

--六、存储过程

--1．创建存储过程。

-- 功能：根据学号查询该学号所代表学生选修课程的成绩信息。

--存储过程名：StuGradeQuery

--输入参数：学号

--返回查询结果集包括：课程编号、课程名称、学分、成绩

CREATE PROC StuGradeQuery (@XH CHAR(12))

AS

SELECT 课程信息.课程编号,课程名称,学分,成绩

FROM 课程信息,成绩信息,学生信息

WHERE 课程信息.课程编号=成绩信息.课程编号AND 学生信息.学号=成绩信息.学号AND 学生信息.学号=@XH

GO

EXEC StuGradeQuery '200801010002'

--2．创建存储过程。

-- 功能：根据课程编号查询选修该课程的学生成绩信息。

--存储过程名：CourseGradeQuery

--输入参数：课程编号

--返回查询结果集包括：学号、姓名、性别、成绩

CREATE PROC CourseGradeQuery (@KCBH CHAR(6))

AS

SELECT 学生信息.学号,姓名,性别,成绩

FROM 成绩信息,学生信息

WHERE 学生信息.学号=成绩信息.学号

AND 成绩信息.课程编号=@KCBH

GO

EXEC CourseGradeQuery 'A08001'

--3．创建存储过程。

-- 功能：根据学号查询该学号所代表的学生信息。

--存储过程名：StuInfoQuery

--输入参数：学号

--返回查询结果集包括：姓名、性别、出生日期、政治面貌。

CREATE PROC STUINFOQUERY(@XH Char(12))

AS

SELECT 姓名,性别,出生日期,政治面貌

FROM 学生信息

WHERE 学号=@XH

GO

EXEC STUINFOQUERY '200801010002'

--4．创建存储过程。

-- 功能：根据课程编号查询该课程编号所代表的课程信息。

--存储过程名：CourseInfoQuery

--输入参数：课程编号

--返回查询结果集包括：课程编号、课程名称、学分、限选人数

CREATE PROC CourseInfoQuery(@KCBH CHAR(6))

as

SELECT 课程编号,课程名称,学分,限选人数

FROM 课程信息

WHERE 课程编号=@KCBH

GO

EXEC CourseInfoQuery 'A08001'

--5．创建存储过程。

-- 功能：根据学号查询该学生的总学分。如果总学分>5，则显示“修完规定学分”，返回1；否则显示“没有修完规定学分”，返回2。

-- 存储过程名：TotalScoreQuery

-- 输入参数：学号

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='TotalScoreQuery' AND TYPE='P')

DROP PROC TotalScoreQuery

GO

CREATE PROC TotalScoreQuery(@XH CHAR(12))

AS

BEGIN

DECLARE @A INT

DECLARE @XM V ARCHAR(10)

SELECT @A=SUM(学分),@XM=姓名

FROM 课程信息,成绩信息,学生信息

WHERE 课程信息.课程编号=成绩信息.课程编号AND 学生信息.学号=成绩信息.学号GROUP BY 学生信息.学号,姓名HA VING 学生信息.学号=@XH

IF @A>5

BEGIN

PRINT @XH+'的学生'+@XM+'修完规定学分'

RETURN 1

END

ELSE

BEGIN

PRINT @XH+'的学生'+@XM+'没有修完规定学分'

RETURN 2

END

END

GO

DECLARE @B INT

EXEC @B=TotalScoreQuery '200801010002'

PRINT '200801010002的返回值是:'+CONVERT(CHAR(1),@B)

--七、触发器的创建

--1．功能：当添加新课程或更新课程信息后，判断该课程的学分是否超过5个。如果超过5个学分，则提示以下信息：数据更新失败！由于您添加或更新的课程超过5个学分。

-- 触发器名字：UpdateCourceTrigger

IF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME='UpdateCourceTrigger' AND TYPE='TR')

DROP TRIGGER UpdateCourceTrigger

GO

CREATE TRIGGER UpdateCourceTrigger ON 课程信息

AFTER INSERT,UPDA TE

AS

BEGIN

DECLARE @XF INT

SELECT @XF=学分FROM INSERTED

IF @XF=5

BEGIN

PRINT '数据更新失败！由于您添加或更新的课程超过5个学分'

ROLLBACK

END

END

UPDATE 课程信息

SET 学分=5 WHERE 课程编号='A08001'

--2．功能：当学生选课时向成绩表添加一条记录时，自动将该门课程的已选人数增加1。-- 触发器名字：AddCourseTrigger

CREATE TRIGGER AddCourseTrigger ON 成绩信息

AFTER INSERT

AS

UPDATE 课程信息

SET 已选人数=已选人数+1

WHERE 课程信息.课程编号IN (SELECT 课程编号FROM INSERTED)

GO

INSERT INTO 成绩信息V ALUES('200801010005','A08002',90)

--3．功能：当学生选课时从成绩表删除误选课程时，自动将该门课程的已选人数减少1。-- 触发器名字：DeleteCourseTrigger

CREATE TRIGGER DeleteCourseTrigger ON 成绩信息

AFTER DELETE

AS

UPDATE 课程信息

SET 已选人数=已选人数-1

WHERE 课程信息.课程编号IN (SELECT 课程编号FROM DELETED)

GO

DELETE FROM 成绩信息WHERE 学号='200801010005'

--4．功能：当向“成绩信息”表中插入一条记录时，检查该记录的“学号”在“学生信息”表中是否存在，检查“课程编号”在“课程信息”表中是否存在，如果有一项不存在，则提示以下信息：数据插入失败，违背数据的一致性。

-- 触发器名字：InsertGradeTrigger

CREATE TRIGGER InsertGradeTrigger ON 成绩信息

AFTER INSERT

AS

DECLARE @XH CHAR(12),@KCBH CHAR(6)

SELECT @XH=学号FROM INSERTED

SELECT @KCBH=课程编号FROM INSERTED

IF @XH NOT IN (SELECT 学号FROM 学生信息) OR @KCBH NOT IN (SELECT 课程编号FROM 课程信息)

BEGIN

RAISERROR('数据插入失败，违背数据的一致性',16,1)

ROLLBACK

END

INSERT INTO 成绩信息V ALUES('200801010002','A08001',80)

INSERT INTO 成绩信息V ALUES('200801010001','A08001',80)

INSERT INTO 成绩信息V ALUES('200801010002','A08007',80)