SQLSever习题与答案.docx
《SQLSever习题与答案.docx》由会员分享，可在线阅读，更多相关《SQLSever习题与答案.docx（18页珍藏版）》请在冰点文库上搜索。
SQLSever习题与答案
SQL-Sever-2005-习题与答案
一
1．使用SQLServer管理平台中创建studentsdb数据库，并修改studentsdb数据文件和日志的初始大小、最大文件大小、增长容量及文件路径。
2．使用Transact-SQL语句CREATEDATABASE创建studb数据库，指定数据文件大小、最大文件大小、增长容量及文件路径。
3．使用Transact-SQL语句ALTERDATABASE完成修改数据库设置、增加日志文件及更名等操作。
4．使用SQLServer管理平台中在studentsdb数据库中创建数据表student_info（其数据结构如图1-2所示），并增加部分数据。
5．使用Transact-SQL语句CREATETABLE在studentsdb数据库中创建grade表，其数据结构如图1-4所示。
6．使用Transact-SQL语句ALTERTABLE修改
相同的所有学生的姓名、出生日期。
6．使用IN子查询，查找所修课程编号为0002、0005的学生学号、姓名、性别。
7．查询学习“C语言程序设计”课程的学生的学号、姓名、分数。
8．查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。
9．查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。
10．为grade表添加数据行：
学号为0004、课程编号为0006、分数为76。
查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。
三
1．使用Transact-SQL语句为studentsdb数据库的student_info表和curriculum表创建主键索引。
2．使用SQLServer管理平台按curriculum表的课程编号列创建唯一性索引。
3．使用Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引，命名为grade_index。
4．为studentsdb数据库的grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_id_c_ind。
5．使用SQLServer管理平台对grade表创建一个聚集索引和唯一索引。
6．使用SQLServer系统存储过程sp_helpindex查看grade表和student_info表上的索引信息。
7．使用Transact-SQL语句DROPINDEX删除索引grade_ind。
8．在studentsdb数据库中，以student_info表为基础，使用SQLServer管理平台建立名为v_stu_i的视图，使视图显示学生姓名、性别、家庭住址。
9在studentsdb数据库中，使用Transact-SQL语句CREATEVIEW建立一个名为v_stu_c的视图，显示学生的学号、姓名、所学课程的课程编号，并利用视图查询学号为0003的学生情况。
10．基于student_info表、curriculum表和grade表，建立一个名为v_stu_g的视图，视图中具有所有学生的学号、姓名、课程名称、分数。
11．使用视图v_stu_g查询学号为0001的学生的所有课程与成绩。
12．使用Transact-SQL语句修改视图v_stu_c，使之显示学号、姓名、每个学生所学课程数目。
13．利用视图v_stu_i为student_info表添加一行数据：
学号为0015、姓名为陈婷、性别为女。
14．利用视图v_stu_g修改姓名为刘卫平的学生的高等数学的分数为84。
15．使用Transact-SQL语句DROPVIEW删除视图v_stu_c和v_stu_g。
四
1．为studentsdb数据库创建一个规则，限制所输入的数据为7位0~9的数字。
2．创建一个规则stusex_rule，将其绑定到stu_phone表的“性别”列上，保证输入的性别值只能是“男”或“女”。
3．使用系统存储过程sp_help查询stusex_rule规则列表，使用sp_helptext查询stusex_rule规则的文本，使用sp_rename将stusex_rule规则更名为stu_s_rule。
4．为student_info表添加一列，命名为“院系”，创建一个默认对象stu_d_df，将其绑定到student_info表的“院系”列上，使其默认值为“信息院”，对student_info表进行插入操作，操作完成后，删除该默认对象。
5．在studentsdb数据库中用CREATETABLE语句创建表stu_con，并同时创建约束。
表结构为：
stu_con（学号char（4），姓名char（8），性别char
（2），出生日期datatime）。
约束要求为：
将学号设置为主键（PRIMARYKEY），主键名为pk_sid；为姓名添加唯一约束（UNIQUE），约束名为uk_name；为性别添加默认约束（DEFAULT），默认名称为df_sex，其值为“男”。
为出生日期添加属性值约束（CHECK），约束名为ck_bday，其检查条件为：
出生日期>'1988-1-1'。
6．在查询设计器中，为studentsdb数据库的grade表添加外键约束（FOREIGNKEY），要求将“学号”设置为外键，参照表为student_info，外键名称为fk_sid。
五
1．在grade表中，使用适当函数找出“高等数学”课程的最高分、最低分和平均分。
2．查询grade表。
如果分数大于等于90，显示A；如果分数大于等于80小于90，显示B；如果分数大于等于70小于80，显示C；如果分数大于等于60小于70，显示D；其他显示E。
3．编写代码计算并显示@n=1+2+3+…+20。
4．编写代码计算并显示1~100之间的所有能被7整除的数的个数及总和。
5．编写程序，定义一个游标cur_stu，通过读取cur_stu数据行，计算student_info表中男、女的人数。
六
1．使用studentsdb数据库中的student_info表、curriculum表、grade表。
①创建一个存储过程stu_grade，查询学号为0001的学生的姓名、课程名称、分数。
②执行存储过程stu_grade，查询0001学生的姓名、课程名称、分数。
③使用系统存储过程sp_rename将存储过程stu_grade更名为stu_g。
2．使用student_info表、curriculum表、grade表。
①创建一个带参数的存储过程stu_g_p，当任意输入一个学生的姓名时，将从3个表中返回该学生的学号、选修的课程名称和课程成绩。
②执行存储过程stu_g_p，查询“刘卫平”的学号、选修课程和课程成绩。
③使用系统存储过程sp_helptext，查看存储过程stu_g_p的文本信息。
3．使用grade表。
①创建一个存储过程stu_g_r，当输入一个学生的学号时，通过返回输出参数获取该学生各门课程的平均成绩。
②执行存储过程stu_g_r，输入学号0002。
③显示0002号学生的平均成绩。
4．输入以下代码，复制student_info表命名为stu2，为stu2表创建一个触发器stu_tr，当stu2表插入一条记录时，为该记录生成一个学号，该学号为学号列数据的最大值加1。
--复制student_info表命名为stu2
SELECT*INTOstu2FROMstudent_info
GO
--为stu2表创建一个INSERT型触发器stu_tr
CREATETRIGGERstu_tr
ONstu2FORINSERT
AS
DECLARE@maxchar（4）
SET@max=（SELECTMAX（学号）FROMstu2）
SET@max=@max+1
UPDATEstu2SET学号=REPLICATE（'0',4-len（@max））+@max
FROMstu2INNERJOINinsertedonstu2.学号=inserted.学号
执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。
在查询设计器的编辑窗口输入以下代码：
INSERTINTOstu2（学号,姓名,性别）VALUES（'0001','张主','女'）
运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？
答案
一
cratedatabasestudb
on
（name=studb1_dat,
filename='g:
\students\studb1.ndf',
size=5Mb,
maxsize=10Mb,
filegrowth=1Mb
）
alterdatabasestudb
addfile
（name=studb1_dat,
filename='g:
\students\studb1.ndf',
size=5Mb,
maxsize=10Mb,
filegrowth=1Mb
）
usestudentdb
createtablegrade
（学号char（4）,
课程编号char（4）,
分数char（5）
）
altertablegrade
altercolumn学号char（4）notnull
altertablegradeadd备注varchar（20）
insertintograde
（学号,课程编号,分数）
values（'0004','0001','80'）
deletefromgradewhere学号='0001'
updategrade
set备注='成绩良好'where学号='0004'
二
select学号,姓名,出生日期fromstudent_info
select姓名,家庭住址fromstudent_infowhere学号='0002'
select学号,姓名fromstudent_infowhere性别='男'
select学号,分数fromgradewhere分数between80and90
selectavg（分数）fromgradewhere课程编号='0003'
select课程编号,count（*）as人数fromgradegroupby课程编号
select学号,姓名fromstudent_infowhere姓名like'张%'
select学号,姓名,性别,出生日期,家庭住址fromstudent_infoorderby性别,学号
select学号,avg（分数）as平均分fromgradegroupby学号
select姓名,出生日期fromstudent_info
where性别=（select性别fromstudent_infowhere姓名='刘卫平'）
select学号,姓名,性别fromstudent_infowhere学号in
（select学号fromgradewhere课程编号in（'0002','0005'））
selecta.学号,b.姓名,a.课程编号,a.分数
fromgradeainnerjoinstudent_infobona.学号=b.学号
wherea.分数in（selectmax（分数）fromgradegroupby学号
selecta.学号,姓名,sum（分数）as总成绩
fromstudent_infoaleftouterjoingradebona.学号=b.学号
groupbya.学号,a.姓名
insertintogradevalues（'0004','0006',76）
selecta.课程编号,b.课程名称,count（a.学号）as选修人数
fromgradealeftouterjoincurriculumbona.课程编号=b.课程编号
groupbya.课程编号,b.课程名称
三
3.1altertablestudent_infoaddconstraintpk_noprimarykey（学号）
altertablecurriculumaddconstraintpk_kcprimarykey（课程编号）
3.2略
3.3createindexgrade_indexongrade（分数）
3.4createuniqueindexgrade_id_indongrade（学号,课程编号）
3.5略
3.6execsp_helpindexgrade
execsp_helpindexstudent_info
3.7dropindexgrade.grade_index
3.8略
3.9createviewv_stu_c
as
selecta.学号,a.姓名,b.课程编号
fromstudent_infoainnerjoingradeb
ona.学号=b.学号
select*fromv_stu_c
3.10createviewv_stu_g
as
SELECTa.学号,a.姓名,b.课程编号,b.分数,c.课程名称
FROMstudent_infoaINNERJOINgradebONa.学号=b.学号
INNERJOINcurriculumcONb.课程编号=c.课程编号
3.11select学号,姓名,课程名称,分数fromv_stu_gwhere学号='0001'
3.12alterviewv_stu_c
as
SELECTa.学号,a.姓名,COUNT（b.课程编号）AS人数
FROMstudent_infoaINNERJOINgradeb
ONa.学号=b.学号
GROUPBYa.学号,a.姓名
3.13insertintov_stu_ivalues（'0015','陈婷','女'）
3.14updatev_stu_gset分数=84where姓名='刘卫平'and课程名称='高等数学'
3.15DROPVIEWv_stu_c,v_stu_g
四
/*4.1*/
SELECT*INTOstu_phoneFROMstudent_info
ALTERTABLEstu_phoneADD电话号码CHAR（7）NULL
CREATErulephone_rule
AS
@phoneLIKE'[0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
sp_bindrulephone_rule,'stu_phone.电话号码'
INSERTINTOstu_phone（学号,姓名,电话号码）VALUES（'0009','王国强','1234567'）
/*4.2*/
CREATErulestusex_rule
AS
@sexin（'男','女'）
sp_bindrulestusex_rule,'stu_phone.性别'
/*4.3*/
sp_helptextstusex_rule
sp_renamestusex_rule,stu_s_rule
/*4.4*/
ALTERTABLEstudent_infoADD院系CHAR（10）NULL
createdefaultstu_d_dfas'信息学院'
sp_bindefaultstu_d_df,'student_info.院系'
sp_unbindefault'student_info.院系'
dropdefaultstu_d_df
/*4.5*/
createtablestu_con
（学号char（4）,
姓名char（8）,
性别char
（2）,
出生日期datetime,
constraintpk_sidprimarykey（学号）,
constraintuk_nameunique（姓名）,
constraintck_bdaycheck（出生日期>'1988-1-1'）
）
altertablestu_con
addconstraintdf_sexdefault'男'for性别
/*4.6*/
altertablegrade
addconstraintfk_sidforeignkey（学号）
referencesstudent_info（学号）
五
select学号,分数,等级=
case
when分数>=90then'A'
when分数>=80and分数<90then'B'
when分数>=70and分数<80then'C'
when分数>=60and分数<70then'D'
else'E'
end
fromgrade
declare@afloat,@bfloat,@cfloat
select@a=max（分数）,@b=min（分数）,@c=avg（分数）
fromgradeainnerjoincurriculumbona.课程编号=b.课程编号
where课程名称='高等数学'
select@a,@b,@c
declare@nint,@sint
set@s=0
set@n=1
while@n<=10
begin
set@s=@s+@n
set@n=@n+1
end
print'1+2+3+...+10='+str（@s,2）
declare@iint,@nint,@sint
set@i=1
set@n=0
set@s=0
while@i<=100
begin
if@i%7=0
begin
set@n=@n+1
set@s=@s+@i
end
set@i=@i+1
end
select@nas个数,@sas总和
declare@sexchar
（2）,@n1int,@n2int
set@n1=0
set@n2=0
declarecur_stucursorforselect性别fromstudent_info
opencur_stu
while@@fetch_status=0
begin
if@sex='男'
set@n1=@n1+1
else
set@n2=@n2+1
fetchnextfromcur_stuinto@sex
end
select@n1as男,@n2as女
closecur_stu
deallocatecur_stu
六
declare@afloat,@bfloat,@cfloat
select@a=max（分数）,@b=min（分数）,@c=avg（分数）
fromgradeainnerjoincurriculumbona.课程编号=b.课程编号
where课程名称='高等数学'
select@a,@b,@c
select学号,分数,等级=
case
when分数>=90then'A'
when分数>=80and分数<90then'B'
when分数>=70and分数<80then'C'
when分数>=60and分数<70then'D'
else'E'
end
fromgrade
declare@nint,@sint
set@s=0
set@n=1
while@n<=10
begin
set@s=@s+@n
set@n=@n+1
end
print'1+2+3+...+10='+str（@s,2）
declare@iint,@nint,@sint
set@i=1
set@n=0
set@s=0
while@i<=100
begin
if@i%7=0
begin
set@n=@n+1
set@s=@s+@i
end
set@i=@i+1
end
select@nas个数,@sas总和
declare@sexchar
（2）,@n1int,@n2int
set@n1=0
set@n2=0
declarecur_stucursorforselect性别fromstudent_info
opencur_stu
while@@fetch_status=0
begin
fetchnextfromcur_stuinto@sex
if@sex='男'
set@n1=@n1+1
else
set@n2=@n2+1
end
select@n1as男,@n2as女
closecur_stu
deallocatecur_stu
createprocedurestu_gradeas
select姓名,课程名称,分数
fromstudent_infosinnerjoingradegons.学号=g.学号
innerjoincurriculumcong.课程编号=c.课程编号
wheres.学号='0001'
executestu_grade
sp_rename'stu_grade','stu_g'
createprocedurestu_p_g@stu_namechar（8）as
select姓名,课程名称,分数
fromstudent_infosinnerjoingradegons.学号=g.学号
innerjoincurriculumcong.课程编号=c.课程编号
wheres.姓名like@stu_name
executestu_p_g'刘卫平'
sp_helptextstu_p_g
createprocedurestu_g_r@stu_nochar（4）,@stu_avgfloatoutput
as
select@stu_avg=avg（分数）
fromgrade
where学号=@stu_no
declare@avg1float
executestu_g_r'0002',@avg1
select@avg1
升级会员 