新SQL——SERVER实验练习答案.docx

SQL-Server 实验答案3 SC表数据的输入SnoCnoGrade30021081128三参照完整性约束 掌握表之间建立外码后，对被参照表的如下操作会有何影响: 修改主码、插入新行、删除新行？ 对参照表添加新行、删除行、修改外码值有何影响？ 掌握级联修改、级联删除的概念。注意:表SC的Sno是外不表SC的Cno是外不 参照 student 的 sno。参照 course 的 cno。1输入实验前的数据 学生表StudentSnoSnameSsexSageSdept4001赵尹男20SX4002杨开女20JSJ课程表courseCnoCnameCpnoCcredit1088Java51089数学3学生选课 SCSnoCnoGrade400110889040021088862试验过程1）在SC表中添加新行:SnoCnoGrade4001106676记录试验结果.，写出出现此结果的原因.不能添加，因为在cno是外码，参照course的cno ,但在course中没有1066课程。2）在student表中添加新行SnoSnameSsexSageSdept4003赵辉男21SX记录试验结果.，写出出现此结果的原因. 可以输入3）删除student表的4001 , 4002学生记录试验结果.，写出出现此结果的原因.两个学生不能被删除，因为sc的外码sno参照student的sno, sc中已经有4001, 4002学生的数 据，因此不能删除。思考： 删除SC表的记录有限制吗？没有 采取什么技术能使不能成功执行的命令变得可以执行，且使数据库保持数据完整性。级联删除4)把student表的学号4003改为4018,4001改为4021。记录试验结果.，写出出现此结果的原因.4003可以改为4018,4001不能改为4021因为sc的外码sno参照student的sno, sc中已经有4001的数据，但没有4003的选课数据。思考：采取什么技术能使本题不能执行的命令可以执行，且使数据库保持数据完整性。级联修改5)把sc表中的如下记录的学号从4001改为4011。SnoCnoGrade4001108890记录试验结果.，写出出现此结果的原因.不能修改,因为sc的外码sno参照student的sno, 4011在student中不存在。如不成功，则可以采取什么方法来实现此要求。需要在student表中添加4011学生。 如不成功，那么把4001修改为4003,能成功吗？能成功！思考：参照完整性规则中,外码可以为空，但SC表中的外码可以为空吗?为什么？举一个外码可以为空的例子。不可以，因为sc表的主码为sno+cno,即sno,eno为主属性，所以不能为空。试验五索引目的：掌握索引的建立、删除的方法。一创建索引1建student的索引为姓名建立索引，索引名：Ix_student_sname为系科建立索引，索引名：Ix_student_sdeptcreate index ix_student_sname ON student(sname)create index ix_student_sdept ON student(sdept)2 SC的索引为课程号建立索引：ix_sc_cnocreate index ix_sc_cno ON sc(cno)3 Course的索引为课程名建立唯一性索引：Ix_course_cnamecreate unique index ix_course_cname ON course( cname)4如何SP_HELP查看索引刚才建立的索引？ 如何在企业管理器中查看索引？二 册U除索弓I course表的索引IX_course_cnameDROP INDEX course.ix_course_cname三 思考：如何把索引IX_student_sname修改为唯一性索引？ 可以使用企业管理器 或先删除索引，再重新建立。*四思考建立索引的目的1输入下列存储过程，该程序生成大量数据供测试： create procedure usp makedata asdeclare nCnt int , sNo varchar(6) , sname varchar(8) set nCnt =12000 一计数器 while nCnt<999999 beginset nCnt = nCnt + 1set sNo = convert(varchar(6) , nCnt) set sName ='张'+snoinsert into student (sno, sname, ssex, sage) values ( sno,酝name,'男'，20) endreturn2 exec usp_makedata -生成测试数据 3输入下述测试程序：create procedure usp_test as declare nCount int , ©data int set nCount=0while nCount<100 beginselect data二count (*) from student where sname '张 3800' or sname'张 8800' set nCount =nCount + 1 end4测试1)建立姓名的索引，查看运行时间(8秒).create index ix_student_sname on student (sname) -建立索弓I exec usp test2)删除姓名索引，查看运行时间(2分11秒)，比较与1)的时间长短。 drop index student, ix_studcnt_sname-一册U除索弓Iexec usp_test试验六更新数据目的：掌握insert, update , delete语句的使用。一 insert1写出把下述学生的信息添加到student表中的命令。学号姓名性别年龄系科4001赵茵男20SX4002杨华女21Insert into student (sno,sname,ssex,sage,sdept) values ('4001赵茵、'男',20,'SX')Insert into student (sno,sname,ssex,sage) values ('4002 了杨华丁女21)2批量插入数据1) 建立一个新表sc_name ，有属性sno , sname ,ssex , eno , grade 。CREATE TABLE sc_name (Sno char(6),Sname varchar(20),Ssex char(2), eno char(4), grade int)2) 把 SX 系学生的 sno,sname,ssex, eno , grade 插入到表 sc_name 中。Insert into sc_name (sno,sname,ssex,eno , grade)select student.sno,sname , ssex,eno,grade from student,sc where student.sno=sc.sno and sdept='SX'3)察看sc_name表的数据 select * from sc_name二Update1修改0001学生的系科为：JSJUpdate student set sdept=,JSJ, where sno='0001'2把陈小明的年龄加1岁，性别改为女。Update student set sage=sage+l , ssex='女' where sname二' 陈小明' 3修改李文庆的1001课程的成绩为93分update sc set grade=93 where cno=,1001' and sno in ( select sno from student where sname二 李文庆')4把“数据库原理”课的成绩减去1分 update sc set grade=grade - 1 where eno in (select eno from course where cname='数据库原理')三 Delete1删除所有JSJ系的男生delete from student where sdept='JSJ'2删除“数据库原理”的课的选课纪录Delete from sc where eno in （select eno from course where cname='数据库原理）思考：修改数据的命令与修改表结构的命令有何区别？试验七Sql查询语句目的：掌握Select查询语句。一单表1查询年龄在19至21岁之间的女生的学号,姓名，年龄,按年龄从大到小排列。select sno, sname, sage from studentwhere sage between 19 and 21 and ssex=' 女' order by sage desc2查询姓名中第戎2个字为“明”字的学生学号、性别。select sname , ssex from student where sname like 明3查询1001课程没有成绩的学生学号、课程号select sno, eno from sc where grade is null and cno=, 1001,4查询JSJ、SX. WL系的学生学号，姓名，结果按系及学号排列select sno, sname from student where sdept in ( 'JSJ' , ' SX' , ' WL')order by sdept,sno5按10分制查询学生的sno, eno, 10分制成绩(1-10 分 为 1 , 11-20 分为 2 , 30-39 分为 3,。90-100 为 10)select sno , eno , grade/10. 0+1 as level from sc6查询student表中的学生共分布在那几个系中。(distinct)select distinct sdept from student7查询0001号学生1001, 1002课程的成绩。Select eno from sc where sno='0001' and (eno='1001? or eno='1002?)二统计1查询姓名中有“明”字的学生人数。select count(*) from student where sname like '明%2计算'js系的平均年龄及最大年龄。Select avg(sage) , max(sage) from student Where sdept=? JSr3计算每一门课的总分、平均分，最高分、最低分，按平均分由高到低排列 select eno, sum(grade), avg(grade), max(grade), min(grade) from sc group by enoorder by avg(grade) desc4计算1001, 1002课程的平均分。Select eno , avg(grade) from sc where eno in ( '1001 ,1002')Group by eno5查询平均分大于80分的学生学号及平均分select sc. sno , avg(grade) from scgroup by sc.sno having avg(grade)>806统计选修课程超过2门的学生学号select sno from sc group by sno having count (*)>27统计有10位成绩大于85分以上的课程号。Select eno from scwhere grade>85group by eno having count (*) =108统计平均分不及格的学生学号select sno from sc group by sno having avg(grade)<609统计有大于两门课不及格的学生学号select sno from sc where grade<60group by sno having count(*) >2三连接1查询JSJ系的学生选修的课程号select eno from student, sc where student. sno=sc. sno and sdept=, JSJ'2查询选修1002课程的学生的学生姓名(不用嵌套及嵌套2种方法)a: select sname from student,sc where student, sno = sc. sno and cno=, 1002b: select sname from student where sno in (select sno from sc where cno=, 1002')3查询数据库原理不及格的学生学号及成绩select sno,grade from sc ,coursewhere sc eno=course, eno and cname=' 数据库原理'4查询选修“数据库原理”课且成绩80以上的学生姓名(不用嵌套及嵌套2种方法)a: select sname from student , sc , coursewhere student. sno=sc. sno and sc. eno = course, eno and grade>80 and cname='数据库原理，b: select sname from student where sno in ( select sno from sc where grade>80 and eno in ( select eno from course where cname=,数据库原理，)5查询平均分不及格的学生的学号，姓名，平均分。select sno, max(sname) , avg(grade) as avggrade from sc , studentwhere student. sno=sc. snogroup by student, snohaving avg(grade) <606查询女学生平均分高于75分的学生姓名。A: Select sname from student where ssex=,女' and sno in ( Select sno from sc group by sno having avg(grade)>75)B: Select max(sname ) from sc,student where student.sno=sc.sno and Ssex=,女Group by student.sno having avg(grade)>75 7查询男学生学号、姓名、课程号、成绩。（一门课程也没有选修的男学生也要列出，不能遗漏） select student, sno, sname, eno, grade from student left join sc ON student.sno=sc. sno and ssex=男'四嵌套、相关及其他1查询平均分不及格的学生人数select count(*) from student where sno in (select sno from sc group by sno having avg(grade)<60 )2查询没有选修1002课程的学生的学生姓名select sname from student where sno not in(select sno from sc where eno='1002')student0001 aa X0002 bb今0003 cc X sc00011001000110020002100100031002select sname from student where not exists (select * from sc where eno='1002' and sc. sno=student. sno)3查询平均分最高的学生学号及平均分(2种方法TOP , any , all)a: select top 1 sno, avg(grade) from sc group by sno order by avg(grade) descB: select sno, avg(grade) from sc group by snohaving avg(grade) = (select top 1 avg(grade) from sc group by sno order by avg(grade) desc )c: select sno, avg(grade) from sc group by snohaving avg(grade) >=all ( select avg(grade) from sc group by sno )*4查询没有选修1001, 1002课程的学生姓名。Select sname from student where not exists (Select * from course where eno in ( '1001' ，' 1002' ) andNot exists ( select * from sc where sno=student,sno and eno二course,eno ) )5查询1002课程第一名的学生学号(2种方法)a: select top 1 sno from sc eno='1002' order by grade descb: select sno from sc where eno='1002' andgrade >=all (select grade from sc where eno='1002')6查询平均分前三名的学生学号select top 3 sno from sc group by sno order by avg(grade) desc7查询JSJ系的学生与年龄不大于19岁的学生的差集a: select * from student where sdept=, JSJ' and sage>19b: select * from student where sdept='JSJ'except select * from student where sage<198查询1001号课程大于90分的学生学号、姓名及平均分大于85分的学生学号、姓名select student.sno,sname from student,sc where eno='1001' and grade>90unionselect sno,sname from student where sno in (select sno from sc group by sno having avg(grade)>85 )9查询每门课程成绩都高于该门课程平均分的学生学号select sno from student where sno not in (select sno from sc X where grade<(select avg(grade) from sc Y where Y.sno二X.sno)select sno from student where sno not in (select sno from sc X where grade < (select avg(grade) from sc where eno=X.eno )10查询大于本系科平均年龄的学生姓名select sname from student X where sage > (select avg(sage) from student y where sdept=x.sdept)试验八视图目的：掌握视图的建立、使用。1建立学生学号、姓名、性别、课程号、成绩的视图v_sc 查看V_sc中的数据。Create view v_sc (sno , sname,ssex , eno, grade ) asSelect student.sno , sname,ssex , eno , grade from student, scWhere student.sno=sc.snoSelect * from v_sc1建立学生学号、姓名、出生年月的视图v_age查看V_age中的数据。Create view v_age (sno,sname, sbirth) asSelect sno , sname , 2008 - sage from studentSelect * from v_age2建立6JSJ9系的学生学号、姓名、性别、年龄的视图V_JSJCreate view vjsj (sno,sname,ssex, sage) asSelect sno,sname,ssex,sage from student where sdept=,JSJ,3建立每门课程的平均分的视图V.avggradeCreate view v_avgGrade(cno, grade 1 ) asSelect eno , avg(grade) from sc group by eno4将视图vjsj中李文庆的年龄改为21岁Update vjsj set sage=sage+l where sname二'李文庆'5察看student中李文庆的年龄查看v_age中李文庆的出生年月Select * from student where sname='李文庆'Select * from v_age where sname='李文庆6查询每门课程的及格率Create view vl (eno , cntl) asSelect eno, count(*) from sc group by enoCreate view v2 (eno , cntl) asSelect eno, count(*) from sc where grade>=60 group by enoSelect vLeno , cnt2* 1.0 / cntl from vl,v2 where o=o思考：1利用VJSJ视图，可以更新sx的学生的年龄吗？写出理由 如： update vjsj set sage=25 where sno=, 000490004号学生为SX系.试验九安全性控制实验目的：掌握Sql-server的授权机制.1）建立新用户mary ,密码1234Sp addLogin 4mary? ,1234'2）授予mary可以访问School数据库的权力选择school数据库Sp_grantDBaccess mary3）以 mary 登录 sql-server ,执行select * from student ，记录执行结果,说明原因。无法查到数据，因为mary没有查询student的权限。4）将course的查询、更改权限授予mary上海师范大学计算机系Grant select , update on course to mary5)把查询student表和修改学生学号的权限授予用户mary,且他能将此权限转授他人。Grant select , update(sno) on student to mary with grant option6)把对course表的更改权限从mary收回Revoke update on course from mary7)把第5)小题授予mary的权限收回。revoke select , update (sno) on student from mary cascade8) mary只能查询'1001'号课程的学生成绩,请问如何授权Create view vscl (sno,eno,grade) asSelect sno, eno,grade from sc where eno=0001?Grant select on vscl to mary思考：1 sp_addlogin , sp_grantdbaccess 语句的区别.2如有200个人需要授权，SQL-SERVER如何简化授权机制。试验十存储过程目的：掌握存储过程的概念、编程及使用1编写一个存储过程usp.avgage ,向客户端返回每个系科的学生平均年龄。系科平均年龄JSJ21SX201)编写存储过程的代码Create procedure usp avgage asSelect sdept, avg(sage) from student group by sdept2)调试、运行该存储过程。Usp_avgage2编写一个存储过程usp_sdept,传入一个系科代码，返回该系的平均年龄，人数 Create procedure usp_sdept dept char(10) asSelect avg (sage), count(*) from student where sdept=dept3编写存储过程usp_updateGrade ,传入参数为课程号，处理逻辑：对传入的这门课,进行如下处理：如某学生该门课成绩80 ,则加2分如某学生该门课成绩60 ,则加1分如某学生该门课成绩二60，则减1分并且返回此门课的每个学生的最新成绩：学号 成绩.Create procedure usp_updateGrade cno char (4) asUpdate sc set grade=grade + 2 where cno=cno and grade>80Update sc set grade=grade + 1 where cno=cno and grade between 60 and 80Update sc set grade=grade -1 where cno=cno and grade<=80Select sno , grade from sc where cno=cno return5编写存储过程usp_comp_age ,比较0001, 0002学生的年龄的高低，输出：XXXX学生的年龄 大注意：XXXX为学生的姓名Create procedure usp comp age asdeclare agel int , age2 intdeclare namel char (10) , name2 char (10) 一临时存储两个人的姓名select agel=sage , namel = sname from student where sno='0001' select age2=sage, name2 = sname from student where sno='0002?if agel > age2print name 1 + '学生的年龄大' elseprint name2 + ，学生的年龄大' return7编写存储过程usp_comp_agel ,比较两个学生的年龄的高低，两个学生的学号有参数输入，最 后输出：XXXX学生的年龄大。注意：XXXX为学生的姓名Create procedure usp_comp_agel nol char (6), no2 char(6) asdeclare agel int , age2 intdeclare namel char (10) , name2 char (10) 一临时存储两个人的姓名select agel=sage , namel = sname from student where sno=nol select age2=sage, name2 = sname from student where sno=no2if agel > age2print ©name 1 + '学生的年龄大 elseprint name2 + ，学生的年龄大 return10编写存储过程usp_comp_age2 ,比较两个学生的年龄的高低，两个学生的学号有参数输入, 最后把年龄大的学生的姓名、性别返回客户端。Create procedure usp comp agel no2 char (6),no2 char (6) asdeclare agel int , age2 intdeclare namel char (10) , name2 char (10) 一临时存储两个人的姓名select agel=sage , namel = sname from student where sno=nolselect age2=sage, name2 = sname from student where sno=no2if agel > age2select sname ,ssex from student where sno=nolelseselect sname ,ssex from student where sno=no2return 12编写存储过程usp_tl,传入参数为学号，把该学号的课程1001的成绩减到58分。每次只能减1 分，用循环完成。create procedure usp_tl no char (6) asdeclare age intset age=100while age>58BEGINSELECT age = sage from student where sno=noIf age>58Update sage=sage -1 where sno=noENDRETURN以下不需要4编写存储过程usp_disp ,传入参数为课程号，处理逻辑：返回每个学生的成绩等级。 成绩二90为优，成绩=80为良，成绩二70为中，成绩二60为及格，成绩二60为不及格。返回结果如下：学号课程号成绩等第0001100191 优0001100278create procedure udpdisp cno char(4) as一建立临时表存储结果create table #tmp (sno char(4), eno char (4), grade int , level char (6) 一建立某门课程的游标declare curl cursor for select sno,grade from sc where cno=cnodeclare sno char (4) , nGrade intdeclare ©sLevel char(6) 一临时存储某学生的成绩等级open curlfetch next from curl into sno , nGrade while fetch status =0 begin一处理一行数据 if nGrade>=90set sLevel ='优'else if nGrade>=80 set sLevel ='良'else if nGrade>=70 set sLevel ='中'else if nGrade>=80set sLevel =，及格， elseset sLevel ='不及格'一把结果写入临时表insert into #tmp(sno, eno, grade, level)一读出游标第一行数据values (sno, cno, nGrade, sLevel)fetch next from curl into sno , ©nGrade-读出游标下一行数据end close curl deallocate curlselect * from #tmp-返回结果给客户端drop table #tmp一删除临时表return5编写一个存储过程，传入参数为学号，执行后，把该学号的学生按如下格式输出成绩: (注意：只有一行)学号 姓名1001课程 1002课程 1003课程 平均分6编写一个存储过程，传入参数为系科，执行后，把该系科的学生按如下格式输出学生成绩:学号 姓名1001课程1002课程 1003课程 平均分create procedure usp_grade dept char (15) ascreate table #tmp (sno char(4), sname char (10), gl int null, g2 int null , g3 int null , pj int null )declare no char(4) , name char (10), nGl int , nG2 int , nG3 int declare curl cursor forselect sno , sname from student where sdept = dept-游标某一个系的学生open curlfetch next from curl into no , name while fetch status、。beginsno=no and eno=100T sno=no and eno='1002' sno二no and eno=1003,select nGl=grade from sc whereselect nG2=grade from sc where select nG3=grade from sc whereinsertinto#tmp(sno, sname, gl, g2, g3, pj)values(no, ©name, nGl, nG2, nG3, (nGl+n