学生管理系统题库答案.doc

12-学生管理系统题库-一、数据库和表的创建-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 学生管理GOCREATE 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为学生信息表设置约束：学生入学年龄必须在025岁之间。ALTER TABLE 学生信息ADD CONSTRAINT CK_XS_NL CHECK(YEAR(GETDATE()-YEAR(出生日期) BETWEEN 0 AND 25)ALTER TABLE 学生信息DROP CONSTRAINT CK_XS_NLALTER TABLE 学生信息ADD CONSTRAINT CK_XS_NL CHECK(DATEDIFF(YYYY,出生日期,GETDATE() BETWEEN 0 AND 25)-8为课程信息表设置约束：学分必须介于05之间。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为成绩信息表设置约束：成绩必须介于0100之间。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 学生信息 VALUES(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 课程信息 VALUES(A08001,C#程序设计,4,250,0),(A08002,大型数据库,3,250,0),(A08003,电子商务,2,250,0)-表3：成绩信息表-学号课程编号成绩INSERT INTO 成绩信息 VALUES(200801010001,A08001,89),(200801010001,A08002,94),(200801010001,A08003,58),(200801010002,A08001,88),(200801010003,A08001,79)-1列示学生信息表中前3条学生的基本信息。SELECT * FROM 学生信息-2列示学生信息表中学生的学号、姓名、年龄字段的基本信息。SELECT 学号,姓名,DATEDIFF(YYYY,出生日期,GETDATE() AS 年龄FROM 学生信息SELECT 学号,姓名,YEAR(GETDATE()-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,出生日期,GETDATE() AS 年龄FROM 学生信息ORDER BY DATEDIFF(YYYY,出生日期,GETDATE()-9列示学号为“200801010001”的学生选修课程的课程编号、课程名称、学分、成绩等信息，并按照成绩的降序排列。SELECT 课程信息.课程编号,课程名称,学分,成绩FROM 课程信息,成绩信息WHERE 课程信息.课程编号=成绩信息.课程编号ORDER BY 成绩 DESC-10列示选修了课程编号为“A08001”的学生的学号、姓名、性别、出生日期等信息，并按照出生日期升序排列。SELECT 学生信息.学号,姓名,性别,出生日期FROM 学生信息 JOIN 成绩信息 ON 学生信息.学号=成绩信息.学号 join 课程信息 ON 课程信息.课程编号=成绩信息.课程编号WHERE 成绩信息.课程编号=A08001ORDER BY 出生日期-11统计并列示每门课程的课程编号以及选修该课程的总人数。SELECT 课程编号,COUNT(学号) AS 总人数FROM 成绩信息GROUP BY 课程编号-12统计并列示每个学生的学号、姓名以及该学生选修课程的门数信息。SELECT 学生信息.学号,姓名,COUNT(课程编号) AS 门数FROM 学生信息,成绩信息WHERE 学生信息.学号=成绩信息.学号GROUP BY 学生信息.学号,姓名-13列示每个学生的学号、姓名以及该学生目前所有选修课程的平均成绩信息。SELECT 学生信息.学号,姓名,AVG(成绩) 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 学号 HAVING COUNT(课程编号)=2-19.统计选修了A08001课程的学生的平均年龄。SELECT AVG(DATEDIFF(YEAR,出生日期,GETDATE()FROM 学生信息,成绩信息WHERE 成绩信息.学号=学生信息.学号 AND 成绩信息.课程编号=A08001-20创建课程成绩信息查询的视图，其视图名为：CGQuery-视图功能：列示学号、姓名、性别、政治面貌、课程编号、成绩的信息。CREATE VIEW CGQUERYASSELECT 学生信息.学号,姓名,性别,政治面貌,课程信息.课程编号,成绩FROM 学生信息,成绩信息,课程信息WHERE 学生信息.学号=成绩信息.学号 AND 课程信息.课程编号=成绩信息.课程编号GOSELECT * FROM CGQUERY-21创建学生成绩信息查询的视图，其视图名为：SGQuery-视图功能：列示课程编号、课程名称、学分、成绩信息。CREATE VIEW SGQueryASSELECT 课程信息.课程编号,课程名称,学分,成绩FROM 成绩信息,课程信息WHERE 课程信息.课程编号=成绩信息.课程编号GOSELECT * FROM SGQUERY-四、数据更新-1为学生信息表添加一条学生数据，其具体信息如下：-学号：200801010005，姓名：小沈阳，性别：男，出生日期：1988-09-21。INSERT INTO 学生信息 VALUES(200801010005,小沈阳,男,1988-09-21,NULL,NULL)-2为课程信息表添加一条课程数据，其具体信息如下：-课程编号：A08004，课程名称：网页设计，学分：3INSERT INTO 课程信息 (课程编号,课程名称,学分)VALUES(A08004,网页设计,3)-3为成绩信息表添加一条数据，其具体信息如下：-学号：200801010004，课程名称：A08003，成绩：90INSERT INTO 成绩信息 (学号,课程编号,成绩)VALUES(200801010004,A08003,90)-4将学号为200801010003的学生的学校履历更新为：20082009年第一学期获得一等奖学金。UPDATE 学生信息SET 学校履历=20082009年第一学期获得一等奖学金WHERE 学号=200801010003-5将课程编号为A08003的限选人数更新为200。UPDATE 课程信息SET 限选人数=200WHERE 课程编号=A08003-6将学号为200801010001的学生所修课程编号为A08003的课程成绩修改为86分。UPDATE 成绩信息SET 成绩=86WHERE 学号=200801010001AND 课程编号=A08003-7删除成绩信息表中成绩不及格的记录信息。DELETE FROM 成绩信息WHERE 成绩60 AND 课程编号 IN (SELECT 课程编号 FROM 课程信息 WHERE 课程名称=电子商务)-五、T-SQL编程-1创建一个标量函数getname，用于从学生信息表中根据学号返回学生姓名.CREATE FUNCTION GETNAME(XH CHAR(12)RETURNS NVARCHAR(100)ASBEGINDECLARE XM NVARCHAR(10),STR VARCHAR(100)SELECT XM=姓名 FROM 学生信息 WHERE 学号=XHSELECT STR=XH+的学生的姓名是:+XMRETURN STRENDGOSELECT dbo.GETNAME(200801010002)-2创建一个内联表函数getstudent，用于从数据库中根据成绩返回学生的学号，姓名，所修课程名称。CREATE FUNCTION GETSTDENT(CJ DECIMAL(5,2)RETURNS TABLEAS RETURN SELECT 学生信息.学号,姓名,课程名称FROM 学生信息,成绩信息,课程信息WHERE 学生信息.学号=成绩信息.学号 AND 课程信息.课程编号=成绩信息.课程编号AND 成绩=CJ GOSELECT * FROM dbo.GETSTDENT(90.00)-3. 定义一个名为“mytrans”的事务，提交该事务后，删除“学生信息”表中学号为“200801010004”的学生信息，同时也删除“成绩信息”表中的与之对应的信息。如果正确，提示“成功”；如果错误，回滚到原来状态，提示“错误”。DECLARE ERR1 INTSET ERR1=0BEGIN TRAN MYTRANSSAVE TRAN MYIF EXISTS(SELECT * FROM 学生信息 WHERE 学号=200801010004)BEGINDELETE FROM 学生信息 WHERE 学号=200801010004SET ERR1=ERR1+ERRORIF ERR10 BEGIN PRINT 删除学生信息失败 ROLLBACK TRAN MY ENDELSE BEGIN PRINT 成功 COMMIT TRAN MYTRANS END END ELSE PRINT 没有学生-4. 声明一滚动动态游标cursor_update，要求结果集为学生的学号、姓名、性别、出生时间、选修课程编号和成绩。DECLARE cursor_update CURSORSCROLL DYNAMICFOR SELECT 学生信息.学号,姓名,性别,出生日期,课程信息.课程编号,成绩 FROM 学生信息,成绩信息,课程信息WHERE 学生信息.学号=成绩信息.学号 AND 课程信息.课程编号=成绩信息.课程编号OPEN cursor_updateDECLARE XH CHAR(12)DECLARE XM VARCHAR(10)DECLARE XB CHAR(2)DECLARE CSSJ DATETIMEDECLARE KCBH CHAR(6)DECLARE CJ DECIMAL(5,2)FETCH NEXT FROM cursor_update INTO XH,XM,XB,CSSJ,KCBH,CJPRINT 学号+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,CJENDclose cursor_updatedeallocate cursor_update-六、存储过程-1创建存储过程。- 功能：根据学号查询该学号所代表学生选修课程的成绩信息。-存储过程名：StuGradeQuery-输入参数：学号-返回查询结果集包括：课程编号、课程名称、学分、成绩CREATE PROC StuGradeQuery (XH CHAR(12)ASSELECT 课程信息.课程编号,课程名称,学分,成绩FROM 课程信息,成绩信息,学生信息WHERE 课程信息.课程编号=成绩信息.课程编号 AND 学生信息.学号=成绩信息.学号AND 学生信息.学号=XHGOEXEC StuGradeQuery 200801010002-2创建存储过程。- 功能：根据课程编号查询选修该课程的学生成绩信息。-存储过程名：CourseGradeQuery-输入参数：课程编号-返回查询结果集包括：学号、姓名、性别、成绩CREATE PROC CourseGradeQuery (KCBH CHAR(6)ASSELECT 学生信息.学号,姓名,性别,成绩FROM 成绩信息,学生信息WHERE 学生信息.学号=成绩信息.学号AND 成绩信息.课程编号=KCBHGOEXEC 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 TotalScoreQueryGOCREATE PROC TotalScoreQuery(XH CHAR(12)AS BEGIN DECLARE A INT DECLARE XM VARCHAR(10) SELECT A=SUM(学分),XM=姓名 FROM 课程信息,成绩信息,学生信息WHERE 课程信息.课程编号=成绩信息.课程编号 AND 学生信息.学号=成绩信息.学号GROUP BY 学生信息.学号,姓名 HAVING 学生信息.学号=XH IF A5 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个学分。- 触发器名字：UpdateCourceTriggerIF EXISTS(SELECT NAME FROM SYSOBJECTS WHERE NAME=UpdateCourceTrigger AND TYPE=TR)DROP TRIGGER UpdateCourceTriggerGOCREATE TRIGGER UpdateCourceTrigger ON 课程信息AFTER INSERT,UPDATEAS BEGIN DECLARE XF INT SELECT XF=学分 FROM INSERTED IF XF=5 BEGIN PRINT 数据更新失败！由于您添加或更新的课程超过5个学分 ROLLBACK END END U