SQL-Sever-2005-习题与答案.doc

一1使用SQL Server管理平台中创建studentsdb数据库，并修改studentsdb数据文件和日志的初始大小、最大文件大小、增长容量及文件路径。2使用Transact-SQL语句CREATE DATABASE创建studb数据库，指定数据文件大小、最大文件大小、增长容量及文件路径。3使用Transact-SQL语句ALTER DATABASE完成修改数据库设置、增加日志文件及更名等操作。4使用SQL Server管理平台中在studentsdb数据库中创建数据表student_info（其数据结构如图1-2所示），并增加部分数据。5使用Transact-SQL语句CREATE TABLE在studentsdb数据库中创建grade表，其数据结构如图1-4所示。6使用Transact-SQL语句ALTER TABLE修改grade表的“学号”列，使之为非空。7使用Transact-SQL语句ALTER.ADD为studentsdb数据库的grade表添加一个名为“备注”的数据列，其数据类型为VARCHAR(20)8使用Transact-SQL语句INSERT INTO.VALUES向studentsdb数据库的grade表插入数据：学号为0004，课程编号为0001，分数为80。9使用Transact-SQL语句DELETE删除studentsdb数据库的grade表中学号为0004的成绩记录。10使用Transact-SQL语句UPDATE将studentsdb数据库的grade表中学号为0003、课程编号为0005的分数修改为90的成绩记录。二1在studentsdb数据库中使用SELECT语句进行基本查询。(1) 在student_info表中，查询每个学生的学号、姓名、出生日期信息。(2) 查询学号为0002 的学生的姓名和家庭住址。(3) 找出所有男同学的学号和姓名。2使用SELECT语句进行条件查询。(1) 在grade表中查找分数在8090范围内的学生的学号和分数。(2)在grade表中查询课程编号为0003的学生的平均分。(3)在grade表中查询学习各门课程的人数。(4)查询所有姓“张”的学生的学号和姓名。3对student_info表，按性别顺序列出学生的学号、姓名、性别、出生日期及家庭住址，性别相同的按学号由小到大排序。4使用GROUP BY查询子句列出各个学生的平均成绩。5在student_info表中查找与“刘卫平”性别相同的所有学生的姓名、出生日期。6使用IN子查询，查找所修课程编号为0002、0005的学生学号、姓名、性别。7查询学习“C语言程序设计”课程的学生的学号、姓名、分数。8查询每个学生的所选课程的最高成绩，要求列出学号、姓名、课程编号、分数。9查询所有学生的总成绩，要求列出学号、姓名、总成绩，没有选修课程的学生的总成绩为空。10为grade表添加数据行： 学号为0004、课程编号为0006、分数为76。查询所有课程的选修情况，要求列出课程编号、课程名称、选修人数，curriculum表中没有的课程列值为空。三1使用Transact-SQL语句为studentsdb数据库的student_info表和curriculum表创建主键索引。2使用SQL Server管理平台按curriculum表的课程编号列创建唯一性索引。3使用Transact-SQL语句为studentsdb数据库的grade表的“分数”字段创建一个非聚集索引，命名为grade_index。4为studentsdb数据库的grade表的“学号”和“课程编号”字段创建一个复合唯一索引，命名为grade_id_c_ind。5使用SQL Server管理平台对grade表创建一个聚集索引和唯一索引。6使用SQL Server系统存储过程sp_helpindex查看grade表和student_info表上的索引信息。7使用Transact-SQL语句DROP INDEX删除索引grade_ind。8在studentsdb数据库中，以student_info表为基础，使用SQL Server管理平台建立名为v_stu_i的视图，使视图显示学生姓名、性别、家庭住址。9在studentsdb数据库中，使用Transact-SQL语句CREATE VIEW建立一个名为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语句DROP VIEW删除视图v_stu_c和v_stu_g。四1为studentsdb数据库创建一个规则，限制所输入的数据为7位09的数字。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数据库中用CREATE TABLE语句创建表stu_con，并同时创建约束。表结构为：stu_con(学号char(4)，姓名char(8)，性别char(2)，出生日期datatime)。约束要求为：将学号设置为主键（PRIMARY KEY），主键名为pk_sid；为姓名添加唯一约束（UNIQUE），约束名为uk_name；为性别添加默认约束（DEFAULT），默认名称为df_sex，其值为“男”。为出生日期添加属性值约束（CHECK），约束名为ck_bday，其检查条件为：出生日期1988-1-1。6在查询设计器中，为studentsdb数据库的grade表添加外键约束（FOREIGN KEY），要求将“学号”设置为外键，参照表为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编写代码计算并显示1100之间的所有能被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表命名为stu2SELECT * INTO stu2 FROM student_infoGO-为stu2表创建一个INSERT型触发器stu_trCREATE TRIGGER stu_trON stu2 FOR INSERTASDECLARE max char(4)SET max=(SELECT MAX(学号) FROM stu2)SET max=max+1UPDATE stu2 SET 学号=REPLICATE(0,4-len(max)+maxFROM stu2 INNER JOIN inserted on stu2.学号=inserted.学号执行以上代码，查看studentsdb数据库中是否有stu2表，展开stu2，查看其触发器项中是否有stu_str触发器。在查询设计器的编辑窗口输入以下代码： INSERT INTO stu2(学号,姓名,性别) VALUES(0001,张主,女)运行以上代码，查看stu2表的变化情况，为什么插入记录的学号值发生了改变？答案一crate database studbon(name=studb1_dat, filename=g:studentsstudb1.ndf, size=5Mb, maxsize=10Mb, filegrowth=1Mb)alter database studbadd file(name=studb1_dat, filename=g:studentsstudb1.ndf, size=5Mb, maxsize=10Mb, filegrowth=1Mb)use studentdbcreate table grade(学号char(4), 课程编号char(4), 分数char (5)alter table gradealter column 学号char(4) not nullalter table grade add 备注 varchar(20)insert into grade(学号,课程编号,分数)values (0004,0001,80)delete from grade where 学号=0001update grade set 备注=成绩良好 where 学号=0004二select 学号,姓名,出生日期from student_infoselect 姓名,家庭住址from student_info where 学号=0002select 学号,姓名from student_info where 性别=男select 学号,分数from grade where 分数between 80 and 90select avg(分数) from grade where 课程编号=0003select 课程编号,count(*) as 人数from grade group by 课程编号select 学号,姓名from student_info where 姓名like 张%select 学号,姓名,性别,出生日期,家庭住址from student_info order by 性别,学号select 学号,avg(分数) as 平均分from grade group by 学号select 姓名,出生日期from student_info where 性别=(select 性别from student_info where 姓名=刘卫平) select 学号,姓名,性别from student_info where 学号in (select 学号from grade where 课程编号in (0002,0005)select a.学号,b.姓名,a.课程编号,a.分数from grade a inner join student_info b on a.学号=b.学号where a.分数in (select max(分数) from grade group by 学号select a.学号,姓名,sum(分数) as 总成绩from student_info a left outer join grade b on a.学号=b.学号group by a.学号,a.姓名insert into grade values (0004,0006,76)select a.课程编号,b.课程名称,count(a.学号) as 选修人数from grade a left outer join curriculum b on a.课程编号=b.课程编号group by a.课程编号,b.课程名称三3.1 alter table student_info add constraint pk_no primary key(学号)alter table curriculum add constraint pk_kc primary key(课程编号)3.2 略3.3 create index grade_index on grade(分数)3.4 create unique index grade_id_ind on grade(学号,课程编号)3.5 略3.6 exec sp_helpindex grade exec sp_helpindex student_info3.7 drop index grade.grade_index3.8 略3.9 create view v_stu_cas select a.学号,a.姓名,b.课程编号 from student_info a inner join grade b on a.学号=b.学号select * from v_stu_c3.10 create view v_stu_gas SELECT a.学号,a.姓名,b.课程编号,b.分数,c.课程名称FROM student_info a INNER JOIN grade b ON a.学号=b.学号INNER JOIN curriculum c ON b.课程编号= c.课程编号3.11 select 学号,姓名,课程名称,分数from v_stu_g where 学号=00013.12 alter view v_stu_cas SELECT a.学号, a.姓名, COUNT(b.课程编号) AS 人数 FROM student_info a INNER JOIN grade b ON a.学号= b.学号 GROUP BY a.学号,a.姓名3.13 insert into v_stu_i values (0015,陈婷,女)3.14 update v_stu_g set 分数=84 where 姓名=刘卫平 and 课程名称=高等数学3.15 DROP VIEW v_stu_c,v_stu_g四/* 4.1 */SELECT * INTO stu_phone FROM student_infoALTER TABLE stu_phone ADD 电话号码CHAR(7) NULLCREATE rule phone_ruleASphone LIKE 0-90-90-90-90-90-90-9sp_bindrule phone_rule ,stu_phone.电话号码INSERT INTO stu_phone(学号,姓名,电话号码) VALUES(0009,王国强,1234567)/* 4.2 */CREATE rule stusex_ruleASsex in (男,女)sp_bindrule stusex_rule,stu_phone.性别/* 4.3 */sp_helptext stusex_rulesp_rename stusex_rule,stu_s_rule/* 4.4 */ALTER TABLE student_info ADD 院系CHAR(10) NULLcreate default stu_d_df as 信息学院sp_bindefault stu_d_df,student_info.院系sp_unbindefault student_info.院系drop default stu_d_df/* 4.5 */create table stu_con(学号char(4), 姓名char(8), 性别char(2), 出生日期datetime, constraint pk_sid primary key (学号), constraint uk_name unique (姓名), constraint ck_bday check (出生日期1988-1-1)alter table stu_conadd constraint df_sex default 男 for 性别/* 4.6 */alter table gradeadd constraint fk_sid foreign key (学号)references student_info(学号)五select 学号,分数,等级=case when 分数=90 then A when 分数=80 and 分数=70 and 分数=60 and 分数70 then D else Eendfrom gradedeclare a float,b float,c floatselect a=max(分数),b=min(分数),c=avg(分数)from grade a inner join curriculum b on a.课程编号=b.课程编号where 课程名称=高等数学select a,b,cdeclare n int,s intset s=0set n=1while n=10begin set s=s+n set n=n+1endprint 1+2+3+.+10=+str(s,2)declare i int,n int,s int set i=1set n=0set s=0while i=90 then A when 分数=80 and 分数=70 and 分数=60 and 分数70 then D else Eendfrom gradedeclare n int,s intset s=0set n=1while n=10begin set s=s+n set n=n+1endprint 1+2+3+.+10=+str(s,2)declare i int,n int,s int set i=1set n=0set s=0while i=100begin if i%7=0 begin set n=