SQL学生管理系统题库与答案

SQL学生管理系统题库/*一、数据库和表的创建*/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 (DA TEDIFF(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 学生信息.学号,姓名,A VG(成绩) 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 学号HA VING COUNT (学号)>=2 GO/*19.统计选修了'A08001'课程的学生的平均年龄。

*/ SELECT A VG(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 视图功能：列示课程编号、课程名称、学分、成绩信息。