数据库复习数据实验解答

1、实验一（1）用 SQL 语句完成实验内容 2-5 的。* 实验内容 2：1)CREATE DATABASE students具体格式：CREATE DATABASE students ON PRIMARY( NAME=students_Data, FILENAME=E: students_Data.MDF, SIZE=10,MAXSIZE=300, FILEGROWTH=5) LOG ON( NAME=students_Log, FILENAME=E: students_Log.MDF, SIZE=10,MAXSIZE=200, FILEGROWTH=2)2)DROP DATABASE Stu

2、dents 3)CREATE DATABASE bookdb4) sp_helpdb bookdb* 实验内容 3：1) USE bookdbcreate tab (Book_idookprimary key,Book_namechar (50)not null,Price float notnull,Publisher char (26) not null,City char (20) not null)项目内容数据库所有者8C62E96C274F451Administrator数据库大小1.24 MB可用空间0.51MB数据文件名Bookdb_data数据文件物理存放位置C:Program

3、FilesSQL ServerMSSQLdatabookdb_data.mdf日志文件名Bookdb_log日志文件物理存放位置C:ProgramFilesSQL ServerMSSQLdatabookdb_log.LDF2) create table Orderform(Order_idprimary key,Book_idnot null,Book_numbernotnull,Order_date datetime not null,Cnt_idnot null)3) create table Authors(Author_idprimary key,Author_name char (8

4、) not null,Address char(50) ,phone char (15) )4) create table Cnts(Cnt_idprimary key,Cnt_name char (8) not null,Address char (50)not null)* 实验内容 4：1)alter tabookadd author_idnot null default 2) alter tabookdrop city3) alter tabookadd constra constraunqBkname UNIQUE(Book_name), chkPricecheck（price0 a

5、nd price=60（5）创建一个基于视图的视图 S_CS，基于（3）中建立的视图CS，定义一个包含学生、学生所选课程数目和平均成绩的视图。create view S_CS (t,avg_sc) asselect sno,count(*),avg(score) from CSgroup by sno（6）创建带表达式的视图，由学生几个属性组成。、所选课程名、所有课程成绩再多 5 分这create view BT_SC( select student.sname, from student,course,scame,newscore) as ame,sc.score+5where studen

6、t.sno=sc.sno ando（7）insert元组（99004，C3，59）到视图 CS 中。o CS values（99004,C3,59）（8）删除视图 CS。 drop view CS实验二1、student 表中析原因并进行修改。元组(99006, , 女, 15, CS) ，给出操作结果，分INSERT Student(sno,sname,ssex,sage,sdept)VALUES(99006, , 女, 15, CS)显示：消息 2627，级别 14，状态 1，第 1 行了 PRIMARY KEY 约束PK_STUDENT。不能在对象d入重复键。语句已终止。TUDENT 中

7、插修改：将学号改成与已有学号不会重复的值。2、求学生的选课人数和学生平均成绩，并把结果存入数据表tot_score。使用SELECTO 和 INSERTO 两种方法实现。（1）SELECTO 语句SELECT cno,count(*) as cnt_sc,avg(score) as avg_sc O tot_scoreFROM SC GROUP BY cno（2）INSERTO 语句create table tot_score (cno char(2),cnt_sc,avg_sc float)INSERTO tot_scoreSELECT cno,count(*),avg(score) FRO

8、M SCGROUP BY cno3、Management Studio：右击 SC 表，在菜单中选择“打开表”，在列出的数据中直接修改用 T-SQL 语句：UPDATE SCSET score=score+3WHERE sno=99002 AND cno=C24、1）查询IS系所有学生的 select sname，并按学号升序排列。from studentwhere sdept=IS order by sno2）查询学分为 2 或 3，且课程名称以”数”开头的课程。select cno from coursewhere cname like 数%and (credit=2 or credit=

9、3);3）查询所有学生的及其所选课程的平均成绩，按平均成绩降序排列。select sno,avg(score) from scgroup by snoorder by avg(score) desc4）查询至少选修了三门课程的学生 select snofrom sc group by snohaving count(*)=3。5）查询所有选了“信息系统”的学生 select snofrom sc,course。whereo andame= 信息系统6）求出选择了同一课程的学生对。 select distinct x.sno,y.sno from sc x, sc ywhereo and x.s

10、noy.sno7）查询选修了学号 99002 的学生所选的某门课程的学生select distinct y.sno from sc x, sc y。whereo and x.sno=99002 and y.sno990028）分别用等值连接和谓词 IN 两种方式查询和成绩。等值连接：为的学生所选课程的selecto,sc.scorefrom student, sc where sname= and sc.sno=student.sno谓词 IN：select cno,scorefrom sc where sno in (select sno from student where sname=)

11、9）查询选修“数据库”课程的成绩比为“”学生高的所有学生的编号和。select sno,sname from student where sno in (select c1.sno from sc as c1,sc as c2 where c1.scorec2.score andoand c2.sno=(select sno from student where sname=)ando=(select cno from course where cname=数据库) )10）查询学分比课程“数学”多的课程的名称。selectame from course as c1, course as c2

12、where c1.creditc2.credit andame=数学5、用 T-SQL 语句完成下面的要求：用 WHILE 语句实现计算 10000 减 1、减 2、减 3，一直减到 50 的结果，并显示最终结果。DECLARE x,ySELECT x=10000,y=1 WHILE (y=0且=0 AND val=where not exists(select * from course where credit ( select credit from course whereo)3）查询没有学生选的课程名称。 select cname from course where cno not

13、in (select cno from sc )4）找出选修课程“数据库”的学生选修的所有课程名称。 select cname from coursewhere cno in (select cno from sc where sno in (selectsnofromcourse,scwherecname= 数 据 库 ando) )5）查询选修了 99002 学生选修的所有课程的学生。select distinct sname from student where not exists (select * from sc as c1 where c1.sno= 99002 and not

14、exists (select * from sc as c2wherec2.sno=student.snoandoandstudent.snoc1.sno) )6）实现集合交运算，查询既选修课程“数学”又选修课程“信息系统”的学生。select x.sno from sc as x,sc as ywhere (o=(select cno from course where cname=数学)ando=(select cno from course where cname=信息系统)and x.sno=y.sno )（2）参见实验指导书（三）的“安全性管理”。（3）用触发器 T1 实现表间的参照

15、完整性，当向 sc 表或修改一条时，检查作。sno 字段的值在 student 表是否存在，若不存在，则取消或修改操CREATE TRIGGER T1 on SC FOR INSERT,UPDATEAS BEGINIF ( (SELECT sno FROM inserted) NOT IN (SELECT sno FROM student)ROLLBACK END（4）建立一个在 student 表上的触发器，当向 student 表一条时，自动显示 student 表中的。CREATE TRIGGER T_Stu on STUDENT FOR INSERTASSELECT sno,sname

16、 FROM inserted（5）建立一个在 student 表上的触发器 T3，要求中已的最小 sage 值大。CREATE TRIGGER T3 on STUDENT FOR INSERTAS的 sage 值必须比表IF (SELECT sage FROM inserted)= ( SELECT min(sage) FROMstudent ) )BEGIN PRInsert Fail!ROLLBACK TRAN ENDION演示触发器作用：INSERTSTUDENT(Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native)OVALUES (99019,男

17、,14,CS,工程,2680916,山东)（6）创建递归触发器，删除 STUDENT表里的学生时，将该学号的学生也从其选课表 SC 里头全部删除，并给出删除是否执行的(假设表之间未建关系约束)。CREATE TRIGGER Tt_SC on SC FOR DELETEASPRdelete SC is executing!DECLARE num char(5) ,msg char(30)SELECT num=sno FROM deleted GROUP BY sno IF rowcount=0BEGINPRno rows affected!RETURN ENDSELECT msg=deletin

18、g SC for sno=+numPR GOmsg再建立另一个触发器：CREATE TRIGGER Tr_S on STUDENT FOR DELETEASPRdelete STUDENT is executing!DECLARE num char(5) ,msg char(30) SELECT num=sno FROM deletedSELECT msg=deleting STUDENT for sno=+numPRmsgDELETE SC WHERE sno=num GO演示触发器作用：DELETE FROM STUDENT WHERE sno=99003（7）在基本表 student 中

19、创建一个游标，按学号排序取出男生的学号和DECLARE Sno_cur CURSOR。FOR SELECT sno,sname BY snoGOOPEN sno_curFROM STUDENT WHERE ssex=男 ORDERDECLARE sno char(5),sname char(8)FETCH NEXT FROM sno_curO sno,snameWHILE fetch_s BEGINus=0PRsno+snameFETCH NEXT FROM sno_cur ENDCLOSE sno_cur DEALLOCATE sno_curO sno,sname（8）在基本表 course

20、 中创建一个游标，先取出表的第三条。DECLARE C_cur CURSOR SCROLL FOR SELECT * FROM COURSEOPEN C_curFETCH ABSOLUTE 3 FROM C_cur FETCH RELATIVE 2 FROM C_curCLOSE C_cur DEALLOCATE C_cur，再取出后两条（9）在基本表 SC 中创建一个游标，若学生成绩低于平均成绩，则成绩增加 10%。 DECLARE upSC_cur CURSOR DYNAMICFOR SELECT score FROM SC FOR UPDATE GODECLARE avg_score f

21、loat,score SELECT avg_score=avg(score) FROM SC OPEN upSC_curFETCH NEXT FROM upSC_curO scoreWHILE fetch_s BEGINus=0IF (scoreavg_score)UPDATESCSETscore=score*1.10WHERECURRENTOFupSC_curFETCH NEXT FROM upSC_cur ENDCLOSE upSC_cur DEALLOCATE upSC_cur GOO score（10）题目有误，略。说明：CREATE PROC 过程中不能包含 CREATE DEFAU

22、LT、CREATE RULE等语句（11）创建向表 student 进行操作的过程，并通过过程调用将下列数据添加到 student 表中(99008,女,19,MA,计算数学,2680076,江苏)创建过程：USE SCMIS GOCREATE PROC student_add( varchar(10),ochar(5),amechar(8),sSsexchar(2),sSdeptsSMajor varchar(10), sSage varchar(30)ASBEGININSERTO student VALUES,sPhone char(7),sNative(o,ame,sSsex, sSde

23、pt,sSMajor, sSage,sPhone,sNative) ENDRETURN GO过程调用：(99008,女, MA,计算数学, 19,2680076,江苏)EXEC student_add 99008,江苏,女, MA,计算数学, 19, 2680076,（12）创建一个过程，按照给定的系名，返回该系学生的最大和平均年龄。如果指定系有学生，则返回 1;否则，返回 0。USE SCMIS GOCREATE PROC age_proc(ins_sdeptvarchar(10),age_max OUTPUT)ASIF EXISTSOUTPUT,age_avgfloat(SELECT *

24、FROM STUDENT WHERE Sdept=ins_sdept) BEGINSELECT age_max =MAX(sage),age_avg=AVG(sage) FROM STUDENTWHERE Sdept=ins_sdept RETURN(1)END ELSERETURN(0)GO过程调用：DECLAREreturn_sus,agemax,ageavg floatEXECreturn_sus=age_procIS,agemaxOUTPUT,ageavgOUTPUTSELECT return_susSELECT agemax,ageavg实验四（1）创建一个返回 table 的函数，

25、函数的参数是学生学生的信息。ins_sname，返回该/* 定义一个带输入参数*/ame 的视图，选修指定课程的学生选课情况USE SCMIS GOCREATE FUNCTION StuByName (ins_sname char(8) RETURNS TABLEASRETURN (SELECT *FROM Student SWHEREame=ins_sname)/* 执行 */SELECT * FROM StuByName ()（2）创建一个内嵌表值函数 fn02_sname，函数的参数是学生ins_sname， ”的情况，写出返回的表 调用语句。了指定学生的具体信息。通过函数调用查看“CR

26、EATE FUNCTION fn02_sName (ins_sname char(8) RETURNS TABLEASRETURN (SELECT *FROM Student SWHEREame=ins_sname)/* 执行 */SELECT * FROM fn02_sName ()（3）创建一个多语句表值函数 fn03_sname，完成题(2)的要求。CREATE FUNCTIONfn03_sname (ins_sname char(8)RETURNS tb_Student TABLE(Stu_NO char(5) PRIMARY KEY,Stu_NAME char(8), Stu_SEX

27、 char(2),Stu_DEPT varchar(10),Stu_MAJOR varchar(10),Stu_AGE,Stu_NATIVE varchar(30),Stu_PHONE char(7) )AS BEGIN INSERTtb_studentSELECT*FROMStudentWHERESname=ins_snameRETURN END/*执行 */SELECT * FROM fn03_sName ()（4）创建一个事务，为课程号C2的所有学生成绩加 10%。BEGRANUPDATE SC SET score=score*1.1 WHERE Cno=C2 COMMIT TRAN（5

28、）创建一个事务，先将学生表中专业以“电子”开头的学生学号前三位号码990改为992，然后回滚。再将专业以“信息”开头的学生学号前三位号码990改为996。BEGRANUPDATE STUDENTSET sno=992+right(sno,2) WHERE smajor LIKE电子% ROLLBACK TRANBEGRANUPDATE STUDENTSET sno=996+right(sno,2) WHERE smajor LIKE信息% COMMIT TRANGO（6）建立一个在 SC 表上的 Update 触发器，其中包含事务 Up_Tran。当发现更新学号或者更新分数小于 60 分时，让

29、事务回滚，不允许更新。CREATE TRIGGER tr_SC04 ON SC FOR UPDATEASDECLARE scoreBEGRANIF UPDATE(sno) BEGINPRcan not change Sno!ROLLBACK TRAN ENDIF UPDATE(score) BEGINSELECT score=score FROM inserted IF score60BEGINPRcan not be updated!ROLLBACK TRAN ENDEND GO测试：UPDATE SC SET SCORE=36 WHERE score=70（7）对数据库 BookDB 的备份与还原操作 （给出操作的主要截图）1）将 BookDB 数据库的故障还原模型设为“完整”打开数据库 BookDB 的属性，单击“选项”，更改“恢复模式”为“完整”。2）建立一个备份设备 Book_dev，对应的物理文件名为 D:Book_dev.bak3) 为 Book 数据库做完全