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

《SQL Server 实用教程实验答案.docx》由会员分享，可在线阅读，更多相关《SQL Server 实用教程实验答案.docx（31页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、试验二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

2、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 PRIMA

3、RY 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

4、Score20 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 stui

5、nfogoSelect 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

6、_name 课程名称,t_score.score 成果 from t_course,t_scorewhere t_course.c_number=t_score.c_number and c_name=SQL Servercompute avg(score)compute sum(score)goselect count(*) sql server与格学生人数 from t_score where score=60 and c_number=1select count(*) sql server不与格学生人数 from t_score where score60 and c_number=1(

7、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_scor

8、e.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

9、_score.c_number=t_course.c_number and score607. 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 P

10、AD_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=团员

11、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 =团员-检查是否出现限制检

12、查冲突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 AND score=70 AND score=80 AND score=90 THEN 优秀 ELSE 无成果 END FROM t_course JOIN t_score

13、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-第一次

14、读取，得到结果集的首行记录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:05select * from t_coursewhere type=专业核心课试验七2.试验内容（1） USE STUINFOGO-创立存储过程Proc_scoreCREATE PROC

15、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

16、，查询“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_numb

17、er=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 该门课程成果最高分为：+

18、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_scor

19、e 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(

20、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 新课程已经添加CO

21、MMIT 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=cna

22、me-执行存储过程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_num

23、ber,C_number), Score REAL )-创立INSERT触发器CREATE TRIGGER Score_insertON t_scoreFOR INSERTASIF ROWCOUNT0BEGININSERT 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),new

24、sno 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=040155WHE

25、RE 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_sc

26、ore 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触发器：CR

27、EATE 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 DBSc

28、ore.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 ScoreVie

29、w SET score=65 WHERE S_number=040102AND C_number=5SELECT * FROM ScoreView WHERE S_number=040102AND C_number=5SELECT * FROM NetScore WHERE S_number=040102AND C_number=53思索与练习（1）USE STUINFOGOCREATE TRIGGER Score_update2ON t_courseFOR UPDATEASIF UPDATE(credit)BEGIN RAISERROR(制止用户修改credit列！,10,1) ROLLBA

30、CK 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 insertedSELEC

31、T 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(课程编号

32、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 页