《SQL Server 2005实用教程》实验答案

实验二2.实验内容（1）CREA TE DA TABASE StuInfoONPRIMARY (NAME = StuInfo _Data,，FILENAME = 'D:\ StuInfo.Mdf',SIZE =3MB,MAXSIZE = 10MB,FILEGROWTH =1 MB)LOG ON(NAME = StuInfo _Log，FILENAME = 'D:\ StuInfo _Log.Ldf',SIZE = 1MB,MAXSIZE =5MB,FILEGROWTH = 1MB)GO（2）--创建学生表T_StudentUSE StuinfoGOCREATE TABLE t_student(S_number char(8) NOT NULL PRIMARY KEY,S_name char(10) NOT NULL,Sex char(2) NULL CHECK(Sex='男' or Sex='女'),Birthday datetime ,Nation nvarchar(10) ,Politics varchar(10) ,Department nvarchar(12) ,Address varchar(60) ,PostalCode nvarchar(10) ,Phone varchar(24) ,)--创建课程表T_CourseUSE StuinfoGOCREATE TABLE t_course(C_number char(4) NOT NULL PRIMARY KEY,C_name char(10) NOT NULL,Teacher char(10) ,Hours int NOT NULL,Credit int ,Type nvarchar(12) ,)--创建课程表T_ScoreUSE StuinfoGOCREATE TABLE t_score(S_number char(8) NOT NULL REFERENCES t_student (S_number),C_number char(4) NOT NULL REFERENCES t_course (C_number),Score numeric(5, 1) CHECK(Score>=0 and Score<=100),CONSTRAINT PK_t_score PRIMARY KEY (S_number ,C_number) )（3）在SSMS中打开表，按照表3.15、3.16、3.167的内容直接输入数据即可。

（4）INSERT INTO t_scoreVALUES('040104','2',70)显示错误信息，不能保存该条记录。

因为T_Score表的S_Number列与表T_Student 的S_Number列存在着外键约束关系，而表T_Student不存在S_Number为040104的记录。

（5）INSERT INTO t_scoreVALUES('040104','2',70)能执行，向t_score表添加了该条数据。

（6）update dbo.t_studentset S_number='040105'where S_number='040101'显示错误信息，不能修改该条记录。

因为T_Score表的S_Number列与表T_Student 的S_Number列存在着外键约束关系，而表T_Score存在S_Number为'040101'的记录，为保证表T_Score数据的完整性，不能修改表T_Student中S_Number为'040101'的记录。

（7）delete t_coursewhere C_number='5'显示错误信息，不能删除该条记录。

因为T_Score表的C_Number列与表T_Course 的C_Number列存在着外键约束关系，为保证表T_Score数据的完整性，不能删除表T_Course中C_Number为5的记录。

（8）法一：首先删除T_Score表中C_Number=’5’的所有记录，然后再删除表T_Course 中C_Number为5的记录。

法二：修改T_Score表与T_Course表关于C_Number的外键约束，设置其“INSERT 和UPDATE规范”的“删除规则”为“层叠”。

（9）USE StuinfoALTER TABLE t_studentADD CONSTRAINT CK_Politics CHECK(Politics='中共党员' or Politics='团员' or Politics='民主党派' or Politics='群众')ALTER TABLE t_studentADD CONSTRAINT DF_Politics DEFAULT('团员') FOR Politics（10）EXEC SP_DETACH_DB 'Stuinfo', 'true'EXEC SP_ATTACH_DB 'Stuinfo','C:\StuInfo.Mdf'实验三3．实验步骤（1）USE stuinfogoSELECT * FROM t_student ORDER BY s_number DESC（2）USE stuinfogoSELECT s_name 姓名, birthday 出生日期,year(getdate())-year(birthday) 年龄,c_name 课程名称, score 成绩FROM t_student,t_course,t_scoreWHERE t_student.s_number=t_score.s_numberand t_course.c_number=t_score.c_number（3）USE stuinfogoSELECT s_name 姓名,c_number 课程编号, score 成绩FROM t_student,t_scoreWHERE t_student.s_number=t_score.s_numberand s_name='李宏'COMPUTE avg(score)COMPUTE sum(score)（4）USE stuinfogoSELECT * FROM t_studentWHERE year(getdate())-year(birthday)>20 and sex='女'（5）USE stuinfogoSELECT t_student.s_number,s_name,sum(score)FROM t_student,t_scoreWHERE t_score.s_number=t_student.s_numberGROUP BY t_student.s_number,s_nameHA VING sex='男'4．思考与练习（1）use stuinfogoSelect s_name,birthday, year(getdate())-year(birthday) 年龄from t_student Where sex='男'use stuinfogoSelect s_name,datename(year,birthday)+'年'+datename(month,birthday)+'月' as 出生年月, year(getdate())-year(birthday) 年龄from t_student Where sex='男'（2）use stuinfogoselect *,count(sex) 人数 from t_studentgroup by s_number,birthday,sex,s_name having sex='女'compute sum(count(sex))(3)use stuinfogoselect t_course.c_name 课程名称,t_score.score 成绩from t_course,t_scorewhere t_course.c_number=t_score.c_numberand c_name='SQL Server'compute avg(score)compute sum(score)goselect count(*) 'sql server及格学生人数' from t_scorewhere score>=60 and c_number='1'select count(*) 'sql server不及格学生人数' from t_scorewhere score<60 and c_number='1'(4)use stuinfogoselect c_name,sex,avg(score) 平均分,sum(score) 总分from t_student,t_course,t_scorewhere t_course.c_number=t_score.c_numberand t_student.s_number=t_score.s_numberand c_name='SQL Server'group by sex,c_name(5)Use stuinfoGoSelect t_student.s_name 姓名,t_student.sex 性别,t_course.c_name 课程名称,t_score.score 成绩From t_student, t_course, t_scoreWhere t_student.s_number=t_score.s_number andt_score.c_number=t_course.c_number and s_name like '李%' and sex='男'(6)Use stuinfoGoSelect t_student.s_name,t_course.c_name,t_score.scoreFrom t_student,t_course,t_scoreWhere t_student.s_number=t_score.s_number andt_score.c_number=t_course.c_number and score<607.Use stuinfoGoSelect top 40 percent s_number,s_name into t_student1from t_studentSelect * from t_student2实验四3．实验步骤(1) （略）(2)(3)CREA TE unique clustered INDEX i_name ON t_user(s_name)(4)use stuinfogocreate index i_t_scoreon t_user(s_number、c_number)WITHPAD_INDEX,FILLFACTOR=50,(5) sp_rename 't_user.i_name','index_s_c'(6)DBCC DBREINDEX ( 't_user',i_name,60 )（7）（8）drop index t_user.i_score实验五3.实验步骤（1）（2）use stuinfogocreate view v_stu_sex0asselect *from t_studentwhere Politics='团员' and sex='女'（3）create view v_stu_sex1asselect *from t_studentwhere Politics='团员' and sex='男'with check option（4）实验六2.实验内容（1）select getdate()(2)USE STUINFOGO--将刘致朋的出生日期由1985年5月8日更改为1985年8月5日UPDATE t_studentSET Birthday='1985-8-5'WHERE S_name='刘志鹏'--如果没有发生记录更新，则发出警告信息IF @@ROWCOUNT=0PRINT '警告：没有发生记录更新'(3)USE STUINFOGO--将黄方方的政治面貌更新为“党员”UPDATE t_studentSET Politics='党员'WHERE Politics ='团员'--检查是否出现限制检查冲突IF @@ERROR=547PRINT '出现限制检查冲突，请检查需要更新的数据限制'(4)if exists(select * from t_student where s_name='张三')select * from t_student where s_name='张三'elseprint '在数据库中无此人信息!'(5)SELECT s_number,c_name,score,等级=CASEWHEN score<60 THEN '不及格'WHEN score>=60 AND score<70 THEN '及格'WHEN score>=70 AND score<80 THEN '中等'WHEN score>=80 AND score<90 THEN '良好'WHEN score>=90 THEN '优秀'ELSE '无成绩'ENDFROM t_course JOIN t_score ON t_course.c_number=t_score.c_number where c_name='电子商务'（6）--声明游标DECLARE stu_cursor CURSORGLOBAL SCROLL DYNAMICFORSELECT t_student.s_number,s_name,c_name,scoreFROM t_student JOIN t_score ON t_student.s_number=t_score.s_numberjoin t_course on t_course.c_number=t_score.c_number --打开游标OPEN stu_cursor--第一次读取，得到结果集的首行记录FETCH NEXT FROM stu_cursor--循环读取结果集中剩余的数据行WHILE @@FETCH_STA TUS=0BEGINFETCH NEXT FROM stu_cursorEND--关闭游标stu_cursorCLOSE stu_cursor--释放（删除）游标stu_cursorDEALLOCA TE stu_cursor（7）（8）WAITFOR DELAY '00:00:05'select * from t_coursewhere type='专业核心课'实验七2.实验内容（1）错误！未找到引用源。