SQL Server 实用教程实验答案.docx

试验二2.试验内容（1） CREATE DATABASE StuInfo ON PRIMARY (NAME = StuInfo _Data,，      = 'D: StuInfo.Mdf',     SIZE =3MB, MAXSIZE = 10MB,      =1 MB) LOG ON (NAME = StuInfo _Log ，      = 'D: StuInfo _Log.Ldf',     SIZE = 1MB,     MAXSIZE =5MB,     = 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_score VALUES('040104','2',70)显示错误信息，不能保存该条记录。因为T_Score表的S_Number列与表T_Student的S_Number列存在着外键约束关系，而表T_Student不存在S_Number为040104的记录。（5） INSERT INTO t_score VALUES('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_number and 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_number and 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_nameHAVING 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_number and c_name='SQL Server'compute avg(score)compute sum(score)goselect count(*) 'sql server与格学生人数' from t_score where score>=60 and c_number='1'select count(*) 'sql server不与格学生人数' from t_score where 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 and t_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 and t_score.c_number=t_course.c_number and score<607. Use stuinfoGoSelect top 40 percent s_number,s_name into t_student1 from t_studentSelect * from t_student2试验四3试验步骤(1) （略）(2)(3)CREATE unique clustered INDEX i_name ON t_user(s_name)(4)use stuinfogocreate index i_t_scoreon t_user(s_number、c_number)WITH PAD_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_sex0as select *from t_studentwhere Politics='团员' and sex='女'（3）create view v_stu_sex1as select *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=547 PRINT '出现限制检查冲突，请检查须要更新的数据限制'(4)if exists(select * from t_student where s_name='张三') select * from t_student where s_name='张三'else print '在数据库中无此人信息!'(5)SELECT s_number,c_name,score,等级= CASE WHEN 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 '无成果' END FROM t_course JOIN t_score ON t_course.c_number=t_score.c_numberwhere c_name='电子商务'（6）-声明游标DECLARE stu_cursor CURSORGLOBAL SCROLL DYNAMICFOR SELECT t_student.s_number,s_name,c_name,score FROM t_student JOIN t_score ON t_student.s_number=t_score.s_number join t_course on t_course.c_number=t_score.c_number-翻开游标OPEN stu_cursor-第一次读取，得到结果集的首行记录FETCH NEXT FROM stu_cursor-循环读取结果集中剩余的数据行WHILE FETCH_STATUS=0BEGINFETCH NEXT FROM stu_cursorEND-关闭游标stu_cursorCLOSE stu_cursor-释放（删除）游标stu_cursorDEALLOCATE stu_cursor（7）（8）WAITFOR DELAY '00:00:05'select * from t_coursewhere type='专业核心课'试验七2.试验内容（1） USE STUINFOGO-创立存储过程Proc_scoreCREATE PROC Proc_scoreWITH RECOMPILE,ENCRYPTIONASSELECT S_number,scoreFROM t_score WHERE C_number='2'-执行存储过程Proc_scoreEXEC Proc_scoreUSE STUINFOGO -创立存储过程Proc_listCREATE PROC Proc_listcno char(4) ASSELECT TOP 5 WITH TIES S_number,scoreFROM t_score WHERE C_number=cnoORDER BY score DESC,S_number ASC-执行存储过程Proc_list，查询“1”号课程成果排名前5位的学生成果记录。EXEC Proc_list '1'其中，“1”为要传递给存储过程Proc_list的输入参数。创立与执行带输入与输出参数的存储过程ProcAvgScoreCREATE PROC ProcAvgScorecno char(4),maxscore real OUTPUT,minscore real OUTPUT,avgscore numeric(5,2) OUTPUT ASSELECT maxscore=MAX(score),minscore=MIN(score),avgscore=AVG(score)FROM t_score WHERE C_number=cnoGROUP BY C_number/*执行存储过程ProcAvgScore，查询选修“1”号课程学生成果的最高分，最低分与平均分*/DECLARE maxscore real,minscore real,avgscore numeric(5,2) EXEC ProcAvgScore '1',maxscore OUTPUT,minscore OUTPUT,avgscore OUTPUT PRINT '该门课程成果最高分为：'+CAST(maxscore AS char(6)PRINT '该门课程成果最高分为：'+CAST(minscore AS char(6)PRINT '该门课程成果最高分为：'+CAST(avgscore AS char(6)（2）运用T-SQL语句查看、修改与删除存储过程 查看存储过程 a.查看加密存储过程的定义 EXEC sp_helptext Proc_score 留意视察执行结果。可以查看到该存储过程的定义文本吗？ b.查看未加密存储过程的定义 EXEC sp_helptext Proc_listEXEC sp_helptext ProcAvgScorec.查看有关存储过程的信息。 EXEC sp_help Proc_score EXEC sp_help Proc_list EXEC sp_help ProcAvgScore（2）修改存储过程Proc_score USE STUINFO GOALTER PROC Proc_scoreWITH RECOMPILEASSELECT S_number,scoreFROM t_score WHERE C_number='1'（3）删除上述创立的存储过程 DROP PROC Proc_score,Proc_list,ProcAvgScore思索与练习：1、USE STUINFOGOCREATE PROC insert_tri Cnum Char (4),Cname char (16),Teacher char (10) =NULL,Hours int,Credit int =NULL,Type Nvarchar(12)ASIF Cnum IS NULL OR Cname IS NULL OR Hours IS NULL BEGIN PRINT '你必需供应课程号，课程名，课程学时！' PRINT '(授课老师，学分，课程类型可以为空。)' RETURN ENDBEGIN TRANSACTIONINSERT t_course (C_number,C_name, Teacher, Hours, Credit,Type) VALUES (Cnum,Cname,Teacher,Hours,Credit,Type)IF error <> 0BEGINROLLBACK TRANRETURNEND PRINT '新课程已经添加'COMMIT TRANSACTIONGO2、USE STUINFOGOCREATE PROC GetCredit Sno char(8),cname char(16),score real OUTPUT,GetCredit int OUTPUTASSELECT score =score, GetCredit =CASE WHEN score <60 THEN 0 ELSE Credit ENDFROM t_course JOIN t_score ON t_course.C_number=t_score.C_numberWHERE t_score.S_number=Sno AND C_name=cname-执行存储过程DECLARE score real,GetCredit intEXEC GetCredit '040101','电子商务', score OUTPUT,GetCredit OUTPUTPRINT '成果='+CONVERT(varchar(6),score)+' ，所获学分='+CONVERT (char(2), GetCredit)试验八2试验内容（1）USE STUINFOGO-创立存放副本的表CREATE TABLE score_bak (S_number char(8) NOT NULL , C_number char(4) NOT NULL, PRIMARY KEY (S_number,C_number), Score REAL )-创立INSERT触发器CREATE TRIGGER Score_insertON t_scoreFOR INSERTASIF ROWCOUNT<>0BEGININSERT INTO score_bakSELECT * FROM insertedEND -测试INSERT触发器INSERT INTO t_score VALUES('040102','9',86.0)（2）USE STUINFOGOCREATE TRIGGER tri_UPDATE_stuON t_studentFOR UPDATEASDECLARE oldsno char(8),newsno char(8)SELECT oldsno=deleted.S_number,newsno=inserted.S_number FROM deleted,inserted WHERE deleted.S_name=inserted.S_namePRINT '打算级联更新t_score表中相关成果记录的学生信息'UPDATE t_scoreSET S_number=newsnoWHERE S_number=oldsnoPRINT '已经级联更新t_score表中原学号为'+oldsno+'的成果记录!'-测试UPDATE触发器UPDATE t_studentSET S_number='040155'WHERE S_number='040101'（3）USE STUINFOGOCREATE TRIGGER stu_deleteON t_studentFOR DELETEASDECLARE sno char(8)SELECT sno=deleted.S_number FROM deletedDELETE FROM t_score WHERE S_number=sno-测试DELETE触发器delete FROM t_student WHERE S_number='040155'（4） 创立包含数据库原理成果信息的表与包含计算机网络成果信息的表：SELECT * INTO DBScore FROM t_score WHERE C_number=(SELECT C_number FROM t_course WHERE C_name='数据库原理')SELECT * INTO NetScore FROM t_score WHERE C_number=(SELECT C_number FROM t_course WHERE C_name='电子商务')GO 创立视图，使得视图包含上述两表的数据：CREATE VIEW ScoreViewASSELECT * FROM DBScore UNIONSELECT * FROM NetScoreGO 创立一个在ScoreView视图上的INSTEAD OF触发器：CREATE TRIGGER Score_UpdateON ScoreViewINSTEAD OF UPDATEASDECLARE cno char(4)SET cno=(SELECT C_number FROM inserted) IF cno=(SELECT C_number FROM t_course WHERE C_name='数据库原理') BEGIN UPDATE DBScore SET DBScore.score=inserted.score FROM DBScore JOIN inserted ON DBScore.C_number=inserted.C_number AND DBScore.S_number=inserted.S_number ENDELSE IF cno=(SELECT C_number FROM t_course WHERE C_name='电子商务') BEGIN UPDATE NetScore SET NetScore.score=inserted.score FROM NetScore JOIN inserted ON NetScore.C_number=inserted.C_number AND NetScore.S_number=inserted.S_number END-通过更新视图，测试INSTEAD OF触发器UPDATE ScoreView SET score=65 WHERE S_number='040102'AND C_number='5'SELECT * FROM ScoreView WHERE S_number='040102'AND C_number='5'SELECT * FROM NetScore WHERE S_number='040102'AND C_number='5'3思索与练习（1）USE STUINFOGOCREATE TRIGGER Score_update2ON t_courseFOR UPDATEASIF UPDATE(credit)BEGIN RAISERROR('制止用户修改credit列！',10,1) ROLLBACK TRANSACTIONEND（2）USE STUINFOGOCREATE TRIGGER Delete_scoreON t_courseFOR DELETEASBEGINDELETE t_score FROM t_score, deletedWHERE t_score.C_number=deleted.C_numberEND（3）USE STUINFOGOCREATE TRIGGER tri_insertON t_scoreFOR INSERTASBEGINDECLARE credit int,score realSELECT score=score FROM insertedSELECT credit=t_course.credit FROM t_course,inserted WHERE t_course.C_number= inserted.C_numberIF score>=60 BEGIN UPDATE t_score SET credit=credit FROM t_score,inserted WHERE t_score.S_number=inserted.S_number ENDEND试验九2试验内容（1）USE StuinfoGOCREATE FUNCTION Fn_Grad(ClassNo Char(6)RETURNS Fn_Grad TABLE(课程编号 Char(4) PRIMARY KEY NOT NULL,平均成果 Decimal(6,2) NOT NULLAS BEGIN INSERT Fn_Grad SELECT C_Number AS 课程编号,AVG(score) AS 平均成果 FROM T_Score WHERE (LEFT(S_Number,6)=ClassNo) GROUP BY C_Number RETURNEND-测试SELECT * FROM dbo.Fn_Grad('0401')-返回结果是0401班的成果统计结果。（2）Declare err IntUSE SalesBEGIN TRANSACTION UPDATE T1 SET T1.数量= T1.数量+ GG.数量 FROM T1,GG WHERE T1.编号= GG.编号 SELECT err=errorIF err!=0 -更新失败，回滚全部操作 BEGIN ROLLBACK TRANSACTION RETURNENDINSERT T1 SELECT * FROM GG WHERE NOT (编号 IN (SELECT 编号 FROM T1)SELECT err=errorIF err!=0 -更新失败，回滚全部操作 BEGIN ROLLBACK TRANSACTION RETURNEND COMMIT TRANSACTION试验十第 31 页