当前位置：文档库 › 学生管理系统题库

学生管理系统题库

/*一、数据库和表的创建*/
CREATE DATABASE 学生管理
ON PRIMARY
(
NAME=学生管理,
FILENAME ='F:\Temp\学生管理题库\JKX1112\学生管理.MDF',
SIZE=3MB,
MAXSIZE=100MB,
FILEGROWTH=5%
)
LOG ON
(
NAME=学生管理_log,
FILENAME ='F:\Temp\学生管理题库\JKX1112\学生管理_log.LDF',
SIZE=3MB,
MAXSIZE=50MB,
FILEGROWTH=1MB
)
GO
USE 学生管理
GO
CREATE TABLE 学生信息
(
学号 char(12) NOT NULL ,
姓名 nvarchar(10) NOT NULL,
性别 nchar(1),
出生日期 datetime,
政治面貌 bit,
学校履历 ntext
)
GO
CREATE TABLE 课程信息
(
课程编号 char(6) NOT NULL ,
课程名称 nvarchar(20) NOT NULL,
学分 tinyint,
限选人数 tinyint,
已选人数 tinyint
)
GO
CREATE TABLE 成绩信息
(
学号 char(12) NOT NULL ,
课程编号 char(6) NOT NULL,
成绩 decimal(5,2)
)
GO
/*二、表约束的创建*/
/*1．为学生信息表声明主码：将学号字段设置为学生信息表的主键。*/
ALTER TABLE 学生信息
ADD CONSTRAINT PK_XSXX_XH PRIMARY KEY (学号)
GO
/*2．为课程信息表声明主码：将课程编号字段设置为课程信息表的主键。*/
ALTER TABLE 课程信息
ADD CONSTRAINT PK_KCXX_KCBH PRIMARY KEY (课程编号)
GO
/*3．为成绩信息表声明主码：将（学号，课程编号）联合字段设置为成绩信息表的主键。*/
ALTER TABLE 成绩信息
ADD CONSTRAINT PK_CJXX_XHKCBH PRIMARY KEY (学号,课程编号)
GO
/*4．为成绩信息表声明外码：为学生信息表和成绩信息表创建关联关系。*/
ALTER TABLE 成绩信息
ADD CONSTRAINT FK_CJXX_XH FOREIGN KEY (学号) REFERENCES 学生信息(学号)
GO
/*5．为成绩信息表声明外码：为课程信息表和成绩信息表创建关联关系。*/
ALTER TABLE 成绩信息
ADD CONSTRAINT FK_CJXX_KCBH FOREIGN KEY (课程编号) REFERENCES 课程信息(课程编号)
GO
/*6．为学生信息表设置约束：性别字段只能输入“男”或“女”。*/
ALTER TABLE 学生信息
ADD CONSTRAINT CK_XSXX_XB CHECK (性别='男'OR 性别='女')
GO
/*7．为学生信息表设置约束：学生入学年龄必须在0~25岁之间。*/
ALTER TABLE 学生信息
ADD CONSTRAINT CK_XSXX_CSRQ CHECK (DATEDIFF(YYYY,出生日期,GETDATE()) BETWEEN 0 AND 25 )
GO
/*8．为课程信息表设置约束：学分必须介于0~5之间。*/
ALTER TABLE 课程信息
ADD CONSTRAINT CK_KCXX_XF CHECK (学分 BETWEEN 0 AND 5 )
GO
/*9．为课程信息表设置约束：已选人数必须介于0到限选人数之间。*/
ALTER TABLE 课程信息
ADD CONSTRAINT CK_KCXX_YXRS CHECK (已选人数 BETWEEN 0 AND 限选人数 )
GO
/*10．为成绩信息表设置约束：成绩必须介于0~100之间。*/
ALTER TABLE 成绩信息
ADD CONSTRAINT CK_CJXX_CJ CHECK (成绩 BETWEEN 0 AND 100)
GO
/*11．为学生信息表设置约束：将性别字段的默认值设置为“女”。*/
ALTER TABLE 学生信息
ADD CONSTRAINT DF_XSXX_XB DEFAULT '女' FOR 性别
GO
/*12．为学

生信息表设置约束：将政治面貌字段的默认值设置为0。*/
ALTER TABLE 学生信息
ADD CONSTRAINT DF_XSXX_ZZMM DEFAULT 0 FOR 政治面貌
GO
/*13．为课程信息表设置约束：将限选人数字段的默认值设置为250。*/
ALTER TABLE 课程信息
ADD CONSTRAINT DF_KCXX_XXRS DEFAULT 250 FOR 限选人数
GO
/*14．为课程信息表设置约束：将已选人数字段的默认值设置为0。*/
ALTER TABLE 课程信息
ADD CONSTRAINT DF_KCXX_YXRS DEFAULT 0 FOR 已选人数
GO
/*三、数据查询、视图的创建*/
/*1．列示学生信息表中前3条学生的基本信息。*/
SELECT TOP 3 * FROM 学生信息
GO
/*2．列示学生信息表中学生的学号、姓名、年龄字段的基本信息。*/
SELECT 学号,姓名,DATEDIFF(YYYY,出生日期,GETDATE()) AS 年龄 FROM 学生信息
GO
/*3．列示性别为“女”并且是党员的学生的基本信息。*/
SELECT * FROM 学生信息 WHERE 性别='女'AND 政治面貌=1
GO
/*4．列示学分超过2的课程的课程编号、课程名称、学分、已选人数等信息。*/
SELECT 课程编号,课程名称,学分,已选人数 FROM 课程信息 WHERE 学分>2
GO
/*5．列示姓“王”的学生的所有信息。*/
SELECT * FROM 学生信息 WHERE 姓名 LIKE '王%'
GO
/*6．列示姓名中带“俊”字的学生的所有信息。*/
SELECT * FROM 学生信息 WHERE 姓名 LIKE '%俊%'
GO
/*7．列示所有课程的基本信息，并按降序排列。*/
SELECT * FROM 课程信息 ORDER BY 学分 DESC
GO
/*8．列示所有学生的基本信息并按照年龄升序排列。*/
SELECT * ,DATEDIFF(YYYY,出生日期,GETDATE())AS 年龄 FROM 学生信息 ORDER BY 年龄
GO
/*9．列示学号为“200801010001”的学生选修课程的课程编号、课程名称、学分、成绩等信息，并按照成绩的降序排列。*/
SELECT 课程信息.课程编号,课程名称,学分,成绩 FROM 课程信息,成绩信息
WHERE 课程信息.课程编号=成绩信息.课程编号 ORDER BY 成绩 DESC
GO
/*10．列示选修了课程编号为“A08001”的学生的学号、姓名、性别、出生日期等信息，并按照出生日期升序排列。*/
SELECT 学生信息.学号,姓名,性别,出生日期 FROM 学生信息,成绩信息,课程信息
WHERE 学生信息.学号 = 成绩信息.学号 AND 成绩信息.课程编号 = 课程信息.课程编号 AND 课程信息.课程编号='A08001' ORDER BY 出生日期 ASC
GO
/*11．统计并列示每门课程的课程编号以及选修该课程的总人数。*/
SELECT 课程编号,COUNT (学号)AS 人数 FROM 成绩信息 GROUP BY 课程编号
GO
/*12．统计并列示每个学生的学号、姓名以及该学生选修课程的门数信息。*/
SELECT 学生信息.学号,姓名,COUNT(*) AS 课程的门数 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号 JOIN 课程信息 ON 成绩信息.课程编号=课程信息.课程编号
GROUP BY 学生信息.学

号,姓名
GO
/*13．列示每个学生的学号、姓名以及该学生目前所有选修课程的平均成绩信息。*/
SELECT 学生信息.学号,姓名,AVG(成绩) AS 平均成绩 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号
GROUP BY 学生信息.学号,姓名
GO
/*14．统计并列示每个学生的学号、姓名以及该学生选修课程的总学分。*/
SELECT 学生信息.学号,姓名,SUM(学分) AS 总学分 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号 JOIN 课程信息 ON 课程信息.课程编号=成绩信息.课程编号
GROUP BY 学生信息.学号,姓名
GO
/*15.列示学分最高的课程编号、课程名称。*/
SELECT 课程编号,课程名称 FROM 课程信息 WHERE 学分 =(SELECT MAX(学分) FROM 课程信息)
GO
/*16.列示'200801010001'学生成绩最高的课程编号。*/
SELECT 课程编号 FROM 成绩信息 WHERE 成绩=(SELECT MAX(成绩)FROM 成绩信息 WHERE 学号='200801010001')
GO
/*17.列示'200801010001'学生成绩最高的课程编号、课程名称。*/
SELECT 课程信息.课程编号,课程名称 FROM 成绩信息 JOIN 课程信息 ON 成绩信息.课程编号=课程信息.课程编号
WHERE 成绩=(SELECT MAX(成绩)FROM 成绩信息 WHERE 学号='200801010001')
GO
/*18.统计至少选修两门课程的学生学号。*/
SELECT 学号 FROM 成绩信息 GROUP BY 学号 HAVING COUNT (学号)>=2
GO
/*19.统计选修了'A08001'课程的学生的平均年龄。*/
SELECT AVG(DATEDIFF(YYYY,出生日期,GETDATE()))AS 平均年龄 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号
WHERE 课程编号='A08001'
GO
/*20．创建课程成绩信息查询的视图，其视图名为：CGQuery 视图功能：列示学号、姓名、性别、政治面貌、课程编号、成绩的信息。*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='CGQuery' AND TYPE='V')
DROP VIEW CGQuery
GO
CREATE VIEW CGQuery
AS
SELECT 学生信息.学号,姓名,性别,政治面貌,成绩信息.课程编号,成绩 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号
GO
--查看视图
SELECT * FROM CGQuery
GO
/*21．创建学生成绩信息查询的视图，其视图名为：SGQuery 视图功能：列示课程编号、课程名称、学分、成绩信息。*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='SGQuery' AND TYPE ='V')
DROP VIEW SGQuery
GO
CREATE VIEW SGQuery
AS
SELECT 课程信息.课程编号,课程名称,学分,成绩 FROM 课程信息 JOIN 成绩信息 ON 课程信息.课程编号=成绩信息.课程编号
GO
--查看视图
SELECT * FROM SGQuery
GO
/*四、数据更新*/
/*1．为学生信息表添加一条学生数据，其具体信息如下：
学号：200801010005，姓名：小沈阳，性别：男，出生日期：1988-09-21。*/
INSERT INTO 学生信息(学号,姓名,性别,出生日期) VALUES('200801010005','小沈阳','男','1988-09-21' )

GO
/*2．为课程信息表添加一条课程数据，其具体信息如下：
课程编号：A08004，课程名称：网页设计，学分：3*/
INSERT INTO 课程信息(课程编号,课程名称,学分) VALUES ('A08004','网页设计',3)
GO
/*3．为成绩信息表添加一条数据，其具体信息如下：
学号：200801010004，课程名称：A08003，成绩：90*/
INSERT INTO 成绩信息 VALUES ('200801010004','A08003',90)
GO
/*4．将学号为200801010003的学生的学校履历更新为：2008~2009年第一学期获得一等奖学金。*/
UPDATE 学生信息 SET 学校履历='2008~2009年第一学期获得一等奖学金' WHERE 学号='200801010003'
GO
/*5．将课程编号为A08003的限选人数更新为200。*/
UPDATE 课程信息 SET 限选人数 =200 WHERE 课程编号='A08003'
GO
/*6．将学号为200801010001的学生所修课程编号为A08003的课程成绩修改为86分。*/
UPDATE 成绩信息 SET 成绩=86 WHERE 学号='200801010001'AND 课程编号='A08003'
GO
/*7．删除成绩信息表中成绩不及格的记录信息。*/
DELETE FROM 成绩信息 WHERE 成绩<60
GO
/*8．删除所有党员的信息。*/
ALTER TABLE 成绩信息
DROP CONSTRAINT FK_CJXX_XH
DELETE FROM 学生信息 WHERE 政治面貌=1 --可先删除外键约束再删除所有党员的信息。
GO
/*9．删除已选人数小于20的课程信息。*/
--DELETE FROM 课程信息 WHERE 已选人数<20
--执行了课程信息就没数据了
GO
/*10.将选修'电子商务'课程不及格的学生成绩全改为空值。*/
UPDATE 成绩信息 SET 成绩=NULL WHERE 课程编号 IN(SELECT 课程编号 FROM 课程信息 WHERE 课程名称='电子商务')AND 成绩<60
GO
/*五、T-SQL编程*/
/*1．创建一个标量函数getname，用于从学生信息表中根据学号返回学生姓名.*/
CREATE FUNCTION getname(@XH CHAR(12))
RETURNS VARCHAR(10) AS
BEGIN
DECLARE @XM VARCHAR(10)
SELECT @XM=姓名 FROM 学生信息 WHERE 学号=@XH
RETURN @XM
END
GO
--执行
PRINT DBO.getname ('200801010002')
GO
/*2．创建一个内联表函数getstudent，用于从数据库中根据成绩返回学生的，姓名，所修课程名称。*/
CREATE FUNCTION getstudent(@CJ DECIMAL(5,2))
RETURNS TABLE AS
RETURN (SELECT 学生信息.学号,姓名,课程名称 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号 JOIN 课程信息 ON 成绩信息.课程编号=课程信息.课程编号 WHERE 成绩=@CJ )
GO
--执行
SELECT * FROM getstudent(90)
GO
/*3. 定义一个名为“mytrans”的事务，提交该事务后，删除“学生信息”表中学号为“200801010004”的学生信息，同时也
删除“成绩信息”表中的与之对应的信息。如果正确，提示“成功”；如果错误，回滚到原来状态，提示“错误”。*/
BEGIN TRANSACTION mytrans
SAVE TRAN MYPOINT
DECLARE @XH CHAR(12)
SET @XH='200801010004'
DELETE FROM 学生信息 WHERE 学号=@XH
I

F @@ERROR<>0
BEGIN
PRINT '错误'
ROLLBACK TRAN MYPOINT
END
ELSE
BEGIN
DELETE FROM 成绩信息 WHERE 学号=@XH
IF @@ERROR<>0
BEGIN
PRINT '错误'
ROLLBACK TRAN MYPOINT
END
ELSE
BEGIN
PRINT '成功'
COMMIT TRAN
END
END
GO
/*4. 声明一滚动动态游标cursor_update，要求结果集为学生的学号、姓名、性别、出生时间、选修课程编号和成绩。*/
DECLARE cursor_update CURSOR
SCROLL DYNAMIC
FOR
SELECT 学生信息.学号,姓名,性别,出生日期,成绩信息.课程编号,成绩 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号
OPEN cursor_update
DECLARE @XH CHAR(12),@XM CHAR(6),@XB CHAR(2),@CSSJ DATETIME,@KCBH CHAR(6),@CJ DECIMAL(5,2)
PRINT '学号'+SPACE(12)+'姓名'+SPACE(6)+'性别'+SPACE(4)+'出生时间'+SPACE(8)+'课程编号'+SPACE(6)+'成绩'
FETCH NEXT FROM cursor_update INTO @XH,@XM,@XB,@CSSJ,@KCBH,@CJ
WHILE @@FETCH_STATUS=0
BEGIN
PRINT @XH+SPACE(4)+@XM+SPACE(4)+@XB+SPACE(6)+CONVERT( CHAR(10),@CSSJ,120)+SPACE(6)+@KCBH+SPACE(8)+CONVERT(CHAR(6) ,@CJ)
FETCH NEXT FROM cursor_update INTO @XH,@XM,@XB,@CSSJ,@KCBH,@CJ
END
CLOSE cursor_update
DEALLOCATE cursor_update
/*六、存储过程*/
/*1．创建存储过程。
功能：根据学号查询该学号所代表学生选修课程的成绩信息。
存储过程名：StuGradeQuery
输入参数：学号
返回查询结果集包括：课程编号、课程名称、学分、成绩*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='StuGradeQuery' AND TYPE='P')
DROP PROCEDURE StuGradeQuery
GO
CREATE PROCEDURE StuGradeQuery
@XH CHAR(12)
AS
SELECT 课程信息.课程编号,课程名称,学分,成绩 FROM 课程信息 JOIN 成绩信息 ON 课程信息.课程编号=成绩信息.课程编号 WHERE 学号=@XH
GO
--执行
EXEC DBO.StuGradeQuery @XH='200801010002'
GO
/*2．创建存储过程。
功能：根据课程编号查询选修该课程的学生成绩信息。
存储过程名：CourseGradeQuery
输入参数：课程编号
返回查询结果集包括：学号、姓名、性别、成绩*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CourseGradeQuery' AND TYPE='P')
DROP PROCEDURE CourseGradeQuery
GO
CREATE PROCEDURE CourseGradeQuery
@KCBH CHAR(6)
AS
SELECT 学生信息.学号,姓名,性别,成绩 FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号 WHERE 课程编号=@KCBH
GO
--执行
EXEC DBO.CourseGradeQuery @KCBH='A08001'
GO
/*3．创建存储过程。
功能：根据学号查询该学号所代表的学生信息。
存储过程名：StuInfoQuery
输入参数：学号
返回查询结果集包括：姓名、性别、出生日期、政治面貌。*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='StuInfoQuery' AND TYPE='P')
DROP PROCEDURE StuInfoQuery
GO
CREATE PROCEDURE StuInfoQuery
@XH CHAR(12)
AS
SELECT 姓名,性别,出生日期,政治

面貌 FROM 学生信息 WHERE 学号=@XH
GO
--执行
EXEC DBO.StuInfoQuery @XH='200801010002'
GO
/*4．创建存储过程。
功能：根据课程编号查询该课程编号所代表的课程信息。
存储过程名：
输入参数：课程编号
返回查询结果集包括：课程编号、课程名称、学分、限选人数*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='CourseInfoQuery' AND TYPE='P')
DROP PROCEDURE CourseInfoQuery
GO
CREATE PROCEDURE CourseInfoQuery
@KCBH CHAR(6)
AS
SELECT 课程编号,课程名称,学分,限选人数 FROM 课程信息 WHERE 课程编号=@KCBH
GO
--执行
EXEC DBO.CourseInfoQuery @KCBH='A08001'
GO
/*5．创建存储过程。
功能：根据学号查询该学生的总学分。如果总学分>5，则显示“修完规定学分”，返回1；否则显示“没有修完规定学分”，返回2。
存储过程名：TotalScoreQuery
输入参数：学号*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME ='TotalScoreQuery' AND TYPE ='P')
DROP PROCEDURE TotalScoreQuery
GO
CREATE PROCEDURE TotalScoreQuery
@XH CHAR(12)
AS
DECLARE @ZXF TINYINT,@XM VARCHAR(10)
SELECT @ZXF=SUM(学分) FROM 课程信息 JOIN 成绩信息 ON 课程信息.课程编号=成绩信息.课程编号 WHERE 学号=@XH
SELECT @XM=姓名 FROM 学生信息 WHERE 学号=@XH
IF @ZXF>5
BEGIN
PRINT @XH+'的学生'+@XM+'修完规定学分'
RETURN 1
END
ELSE
BEGIN
PRINT @XH+'的学生'+@XM+'没有修完规定学分'
RETURN 2
END
GO
--执行
DECLARE @JG INT,@XH CHAR(12)
SET @XH='200801010002'
EXEC @JG=DBO.TotalScoreQuery @XH
PRINT @XH+'的返回值是:'+CONVERT(CHAR(2), @JG)
GO
/*七、触发器的创建*/
/*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 ,UPDATE
AS
DECLARE @XF TINYINT
SELECT @XF=学分 FROM INSERTED
IF @XF>5
BEGIN
PRINT '数据更新失败！由于您添加或更新的课程超过5个学分。'
ROLLBACK
END
GO
--删除约束
ALTER TABLE 课程信息
DROP CONSTRAINT CK_KCXX_XF
GO
--调试
INSERT INTO 课程信息 VALUES('A00001','计算机',6,250,0 )
GO
UPDATE 课程信息 SET 学分=6 WHERE 课程编号='A08002'
GO
DROP TRIGGER UpdateCourceTrigger
GO
/*2．功能：当学生选课时向成绩表添加一条记录时，自动将该门课程的已选人数增加1。
触发器名字：AddCourseTrigger
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='AddCourseTrigger' AND TYPE='TR')
DROP TRIGGER AddCourseTrigger
GO
CREATE TRIGGER AddCourseTrigger
ON 成绩信息 AFTER INSERT
A

S
UPDATE 课程信息 SET 已选人数=已选人数+1 WHERE 课程编号=(SELECT 课程编号 FROM INSERTED)
GO
--调试
INSERT INTO 成绩信息 VALUES ('200801010003','A08004',56)
GO
DROP TRIGGER AddCourseTrigger
GO
/*3．功能：当学生选课时从成绩表删除误选课程时，自动将该门课程的已选人数减少1。
触发器名字：DeleteCourseTrigger
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='DeleteCourseTrigger' AND TYPE='TR')
DROP TRIGGER DeleteCourseTrigger
GO
CREATE TRIGGER DeleteCourseTrigger
ON 成绩信息 AFTER DELETE
AS
UPDATE 课程信息 SET 已选人数=已选人数-1 WHERE 课程编号=(SELECT 课程编号 FROM DELETED)
GO
--调试
DELETE FROM 成绩信息 WHERE 学号='200801010003' AND 课程编号='A08004'
GO
DROP TRIGGER DeleteCourseTrigger
GO
/*4．功能：当向“成绩信息”表中插入一条记录时，检查该记录的“学号”在“学生信息”表中是否存在，
检查“课程编号”在“课程信息”表中是否存在，如果有一项不存在，则提示以下信息：数据插入失败，违背数据的一致性。
触发器名字：InsertGradeTrigger
*/
IF EXISTS (SELECT NAME FROM SYSOBJECTS WHERE NAME='InsertGradeTrigger' AND TYPE ='TR')
DROP TRIGGER InsertGradeTrigger
GO
CREATE TRIGGER InsertGradeTrigger
ON 成绩信息 FOR INSERT
AS
IF NOT EXISTS(SELECT 学号 FROM 学生信息 WHERE 学号=(SELECT 学号 FROM INSERTED))
BEGIN
PRINT '数据插入失败，违背数据的一致性。'
ROLLBACK
END
ELSE IF NOT EXISTS(SELECT 课程编号 FROM 课程信息 WHERE 课程编号=(SELECT 课程编号 FROM INSERTED))
BEGIN
PRINT '数据插入失败，违背数据的一致性。'
ROLLBACK
END
ELSE
PRINT '插入成功'
GO
--删除外键约束
ALTER TABLE 成绩信息
DROP CONSTRAINT FK_CJXX_KCBH
GO
--执行
INSERT INTO 成绩信息 VALUES ('100000000001','123456',77)
GO
INSERT INTO 成绩信息 VALUES ('200801010005','A08003',77)
GO
DROP TRIGGER InsertGradeTrigger
GO