最新 SQL综合实验一（下附完整脚本）

最新 SQL综合实验一 (下附完整脚本)

姓名 xxx 学号 2013106xxx 成绩_____

一、用Transact-SQL语句定义数据库：

1、创建名为School的数据库，该数据库包含二个大小为2MB的数据文件（文

件名为：<你的姓名>_school_dat1和<你的姓名>_school_dat2）和一个大小为1MB的事务日志文件（文件名为：<你的姓名>_school_log）。限制数据文件大小为5MB、日志文件为2MB。对于所有文件，允许20%的文件增长。请将创建数据库的Transact-SQL脚本存为<你的姓名>_1_01.sql文件。

2、修改数据文件<你的姓名>_school_dat1，增加其大小至3MB,并允许按

0.5MB(512KB)的增量增至最大大小6MB；删除数据文件<你的姓名>_school_dat2；添加一个新的1MB的数据文件（文件名为：<你的姓名>_school_dat3）,允许它以1MB增量增至磁盘被充满。请将Transact-SQL脚本存为<你的姓名>_1_02.sql文件。

二、用Transact-SQL语句在School数据库中定义表：

1、根据所给的学生表建立表名为<你的姓名>_student的表,T-SQL脚本存为<你的姓名>_2_01.sql文件。要求：

（1）设置学号列数据类型为char(11)，主键约束，约束名为pk_xh； （2）设置身份证号码列唯一约束，约束名为un_xh；

（3）在<你的姓名>_student表的姓名列上创建一个非聚集索引inx_sname。 （4）其它列根据学生表的数据定义。

2、根据所给的课程表用Transact-SQL语句建立表名为<你的姓名>_course的表, Transact-SQL脚本存为<你的姓名>_2_02.sql文件。要求：

（1）设置课程代号列数据类型为char(5)，主键约束，约束名为pk_kcdh。

（2）设置检查约束，约束名为ck_ccd,条件为课程代号的第一位只能是数字字符“0”到“4”，（表示学年），第二位只能是数字字符“0”、“1”（表示上下学期）。 （3）设置周学时列默认值为3，学分默认值为2。 （4）其它列根据课程表的数据定义。

3、根据所给的成绩表建立表名为<你的姓名>_grade的表，Transact-SQL脚本存为<你的姓名>_2_03.sql文件。要求：

（1）参考学生表和课程表的设置，考察并设置成绩表的主键约束，约束名为pk_id。 （2）设置学号列外键约束并级联删除和级联更新，约束名为fk_xh。

（3）设置课程代号列外键约束并级联删除和级联更新，约束名为fk_kcdh。

（4）设置检查约束，约束名为ck_gcj,条件为平时、期中和期末成绩的和不大于300。 （5）其它列根据课程表的数据定义。

4、修改表的定义，Transact-SQL脚本存为<你的姓名>_2_04.sql文件。要求：

（1）删除<你的姓名>_student表的特长列；添加出生日期列，允许为空，并要求输入的值不能大于当前日期。

（2）增加<你的姓名>_course表的检查约束ck_czx,条件为周学时数大于等于学分数。

三、用Transact-SQL语句在School数据库中操作表：

1、将所给的学生表（特长列已删除，增加了出生日期列）、课程表和成绩表的

数据增加到已建的相应表中。Transact-SQL脚本存为<你的姓名>_3_01.sql文件。

2、 修改学生表的学号列，在学号前加两位“19”，在第四位后加入二位“10”。

例如：学号“9607039”改为“19960710039”；即学号编码代表的含义为：前四位表示是年级，如：1996级；接着的三位表示专业，如：071；后四位表示编号，如：0039。Transact-SQL脚本存为<你的姓名>_3_02.sql文件。 3、追加你自己的自然情况资料到<你的姓名>_student表中，追加你自己《计算机基础（一）》，《英语（一）》，《体育》课程的平时，期中，期末成绩到<你的姓名>_grade表中。Transact-SQL 脚本存为<你的姓名>_3_03.sql文件。 4、创建显示学号、姓名、籍贯、家庭住址和奖励列的查询，要求：地址列的标题显示为“家庭住址”；查询条件是籍贯为“云南”，或者是家庭住址在“江岸小区”，且住在“4单元”的学生。结果按学号降序排序。Transact-SQL脚本存为<你的姓名>_3_04.sql文件。

5、查询1998级并且姓“李”的学生的学号和姓名。Transact-SQL脚本存为<你的姓名>_3_05.sql文件。

6、查询每名学生每门课程的总评分（总评分为平时占10%，期中占20%，期末占70%），显示学号，课程代号和总评分。Transact-SQL脚本存为<你的姓名>_3_06.sql文件。

7、 创建存储过程，查询各类学分（如：4，3）的课程数。Transact-SQL脚本存为<你的姓名>_3_07.sql文件。

8、查询各学年的课程数，学时总数和学分总数。Transact-SQL脚本存为<你的姓名>_3_08.sql文件。

9、 查询选修了三门以上（含三门）课程的学生的学号。Transact-SQL脚本存为<你的姓名>_3_09.sql文件。

10、创建视图，显示每名学生的姓名、选修的课程门数、总周学时数、总学分数和加权平均分的查询，Transact-SQL脚本存为<你的姓名>_3_10.sql文件。要求：

（1）计算加权平均分的公式：

?第i门课程总评分 ?第i门课程学分i?1i＝１N?第i门课程学分Ｎ

（2）结果按加权平均分降序排序。

11、创建统计课程选修人数、最高总评分和最低总评分的查询，Transact-SQL脚本存为<你的姓名>_3_11.sql文件。要求：

（1）所有选修该门课程的学生的总评分必须都及格。 （2）结果按选修人数降序排序； （3）将查询生成表<你的姓名>_sum。

12、创建查询，Transact-SQL脚本存为<你的姓名>_3_12.sql文件。要求： （1）计算并显示每一门课程的课程名、最高总评分及对应的学生姓名。

（2）按课程名升序排序。

（3）将查询生成表<你的姓名>_report。

13、创建存储过程，Transact-SQL脚本存为<你的姓名>_3_13.sql文件。要求：

（1）通过输入参数学生姓名（如“章山\），筛选出该学生的基本信息。

（2）对不存在此学生姓名的输入值，必须作一检测，打印信息“不存在此学生”。 14、为学生表创建触发器，Transact-SQL脚本存为<你的姓名>_3_14.sql文件。要求： （1）当用户成功删除该表中的一条或多条记录时，触发器自动删除成绩表中与之有关的 记录。

（2）在创建触发器之前要判断是否有同名的触发器存在，若存在则删除之。

脚本文件

1.01

CREATE DATABASE School ON PRIMARY

(NAME=xxx_school_dat1,

FILENAME='d:\\sql_data1\\xxx_school_dat1.mdf', SIZE=2, MAXSIZE=5,

FILEGROWTH=20%), (NAME=xxx_school_dat2,

FILENAME='d:\\sql_data2\\xxx_school_dat2.ndf', SIZE=2, MAXSIZE=5, FILEGROWTH=20%) LOG ON

(NAME=xxx_school_log,

FILENAME='d:\\sql_log\\xxx_school_log.ldf', SIZE=1, MAXSIZE=2, FILEGROWTH=20%)

1.02.

ALTER DATABASE School MODIFY FILE

(NAME=xxx_school_dat1, SIZE=3, MAXSIZE=6,