--学生管理系统题库--一、数据库和表的创建--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．为学生信息表设置约束：学生入学年龄必须在0~25岁之间。
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．为课程信息表设置约束：学分必须介于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．统计并列示每个学生的学号、姓名以及该学生选修课程的门数信息。