SQL Server操作综合题

SQL Server操作综合题

一、数据库，数据表的创建，修改和删除。

1、创建‘学生管理’数据库，其包含的文件包括：

（1）主数据文件‘学管_data1’，初始大小2MB，最大容量6MB，增量2MB。（2）次数据文件‘学管_data2’，初始大小1MB，最大容量不受限制，增量1MB。（3）日志文件‘学管_log1’，初始大小2MB，最大容量3MB，增量百分比20%。create database学生管理

on primary

(name=学管_data1,

FILENAME='D:\学生管理\学管_data1.mdf',

size=3MB,

maxsize=6MB,

filegrowth=2MB

),

filegroup usergroup

(name=学管_data2,

fILENAME='D:\学生管理\学管_data2.ndf',

size=1MB,

filegrowth=1MB

)

log on

(name=学管_log1,

fILENAME='D:\学生管理\学管_1.ldf',

size=2MB,

maxsize=3MB,

filegrowth=20%

)

)

2、修改‘学生管理’数据库：

（1）添加一个新文件组‘Mygroup’。

alter database学生管理

add filegroup Mygroup

（2）将‘学管_data1’的初始大小修改为4MB。

alter database学生管理

modify file

(name=学管_data1,

size=4MB

)

（3）添加一个数据文件‘学管_data3’，放在‘Mygroup’组中。

alter database学生管理

add file

(

name=学管_data3,

filename='d:\学管_data3.ndf')

to filegroup mygroup

（4）添加一个日志文件‘学管_log2’，初始大小2MB，保存在D盘根目录下。alter database学生管理

add log file

(

name=学管_log2,

fILENAME='D:\学生管理\学管_2.ldf',

size=2MB

)

（5）删除‘Mygroup’组。

alter database学生管理

remove filegroup mygroup

（6）删除‘学管_log2’。

alter database学生管理

remove file学管_log2

（7）将‘学生管理’数据库的名称改为‘学管系统数据库’。

exec sp_renamedb'学生管理','学管系统数据库'

create table学生表

(学号Varchar(6)primary key not null,

姓名Varchar(6),

性别Varchar(2)default'男',

出生日期Datetime,

政治面貌Varchar(10)default'党员',

籍贯Varchar(10),

所在系Varchar(4)

)

create table选课表

(学号Varchar(6)not null,

课程号Varchar(4)primary key not null,

成绩Tinyint,

foreign key(学号)references学生表(学号)

)

alter table选课表

ADD CONSTRAINT成绩_CHECK

check

(成绩>=0 and成绩<=100)

4、修改表结构，添加约束

1）给学生表的籍贯字段添加默认值约束，默认值为“陕西”。

create table学生表

(学号Varchar(6)primary key not null,

姓名Varchar(6),

性别Varchar(2)default'男',

出生日期Datetime,

政治面貌Varchar(10)default'党员',

籍贯Varchar(10)default'陕西',

所在系Varchar(4)

)

2）给学生表添加新字段‘手机号码’，并设置唯一值约束。

3）将学生表的‘手机号码’字段长度改为11。

4）删除学生表的‘手机号码’字段。

5、删除学生表，选课表。

6、删除‘学管系统数据库’。

二、查询操作

1.利用主数据（.MDF）文件附加?teachdb?数据库。

2.在查询分析器中打开?teachdb?数据库。

3.使用T-SQL语句对teachdb数据库的数据进行简单查询操作：

1）查询学生表的所有记录。

2）查询学号为‘1005’的学生信息。

3）查询籍贯是四川的学生的学号、姓名和籍贯，并在籍贯字段前添加一个常数列，显示常数“籍贯是：”。

4）查询教师表的职称字段有哪几种取值。

5）查询所有女生的姓名，籍贯，并在结果集中将籍贯字段名称替换成‘生源地’。

6）查询姓名第一个字符是‘张’且姓名共两个字符的学生信息。

7）查询姓‘李’，且名字最后一个字符是‘梅’或‘华’的学生的学号和姓名。

8）查询学生的籍贯字段不为空的记录。

9）查询学生姓名中带有“丽”字或“娜”字的学生学号，姓名信息。

4、聚合和汇总操作：

1）统计学生表中所有男生的信息，并将查询结果字段命名为“男生总人数”。

2）统计课程表中所有记录的最大学分，最小学时数，总学分，平均学时数。

3）按职称分组统计教师表的教师人数。

4）按所在系统计各系总人数，并将总人数超过3人的查询结果返回。

5）查询教师表中2000年以后入职的姓名，政治面貌字段，并按政治面貌分组显示查询结果。6）按课程号计算选课表中各门课程的平均分，总分。

5、多表联接查询：

1）查询参加考试的学生姓名，课程名，成绩信息。

2）查询计算机技术系学生的姓名，选修课程的课程名及成绩。

3）查询教师姓名，所教授课程名，授课评价。

4）查询及格学生的学号，及参加考试的课程名和成绩。

5）查询张宏杰同学选修的所有课程名及成绩。

6）查询所有电子商务系参加电子商务概论课程考试的学生姓名及成绩。

7）分别用左外连接、右外连接、全外连接查询教师职工号，姓名，课程号信息。

5、嵌套查询：

1）查询参加…102?号课程考试的学生信息。

2）查询计算机技术系没有参加…103?课程号考试的学生姓名。

3）查询参加…大学语文?课程考试的学生名单。

4）查询成绩低于85分的学生姓名。

5）查询讲授…网络技术?课程的教师的姓名。

6）查询给学号是1001的学生授课且职称为讲师的教师信息。

7）已有数据表：副教授职称教师表（工号，姓名，性别，职称）

要求将教师表中所有副教授职称的教师对应信息插入到副教授职称教师表中。

8）创建新表：及格成绩表（学号，课程号，成绩），并将选课表中所有及格成绩的记录插入到新表中。

三、视图操作

1）用SQL语句给学生表创建‘党员学生视图’。

2）用SQL语句给成绩表中所有不及格的记录建立一个加密的‘不及格成绩视图’。

3）删除‘党员学生视图’中姓名为NULL的记录。

4）修改‘党员学生视图’的结构，删除该视图的所在系字段。

5）删除不及格成绩视图。

四、T-SQL程序设计

1）定义局部变量，用以保存对教师表中教师人数的统计值。

2）使用局部变量查找教师表中所有职称是‘讲师’的教师姓名，性别和职称。

3）将学生表中学号是‘1004’的学生姓名赋值给变量@varl，并显示@varl。

4）使用字符串函数显示‘某（姓氏）老师新年好！！’

5）通过查询，判断…数据库SQL Server?课程的平均成绩是否大于75分。

6）判断课程表1中学分字段的平均值是否大于等于3。

如果平均学分大于等于3，说明学分设置过高，把所有课程学分减1分。如果平均学分小于3，则显示…学分设置正常！！?。

同时要求修改学分后检查最高学分是否大于6分，如果是，则跳出循环，否则继续循环；

7）查询学生的学号，成绩，并对成绩进行“优，良，中，差”的评价。（90分以上优，75~89良，60~74中，60以下差）

五、存储过程

1）创建一个存储过程‘学生人数_proc’，用于显示学生表的学生人数。

2）修改存储过程‘学生人数_proc’，只输出‘计算机技术’专业的人数。

3）创建一个带参数的存储过程‘课程表数据输入_proc’，用来给课程表输入新的课程号，课程名。4）通过‘课程表数据输入_proc’，给课程表添加一条新记录（‘119’,‘连锁超市’）。

5）创建一个加密的存储过程‘学生成绩_proc，用于查询teachdb数据库中每个参加考试的学生各门课程的成绩，包含字段：姓名，课程名，成绩。

6）将‘课程表数据输入_proc’更名为‘输入课程表数据_proc’。

7）创建一个带输出参数的存储过程‘某学生平均成绩_proc’。用于显示指定学生的平均成绩。

六、触发器

1）创建一个触发器…删除课程表记录_tri?：实现当删除课程表1中某门课程的记录时，对应于选课表1中相关该课程的所有记录也均删除。

执行：删除选课表1中课程号为‘102’的记录。通过查询选课表1和课程表1，查看触发器执行的效果。

2）创建一个触发器…授课表记录操作_tri?：当向授课表中插入新纪录时检查该纪录的职工号在教师表中是否存在，同时检查课程号在课程表中是否存在，若有一项不满足条件，则返回错误信息。

执行：给授课表插入一条职工号为‘020’，课程号为‘132’的新数据，查看触发器的执情况；

再给授课表插入一条职工号为‘015’，课程号为‘119’的新数据，查看触发器的执行情况。3）创建触发器…修改学生表1数据_tri?：实现当修改学生表1的记录时，返回提示信息…学生表1的数据被修改！！?。

执行：将学生表1中学号为‘1001’的学生性别改成‘女’。查询触发器执行情况。

4）修改…修改学生表1数据_tri?，添加insert激发条件。

执行：给学生表1的学号，姓名，性别字段添加对应的新数据（'1043','周潇潇','女'），查看触发器执行情况。

5）删除…修改学生表数据_tri?。