数据库系统实验解答.docx
《数据库系统实验解答.docx》由会员分享，可在线阅读，更多相关《数据库系统实验解答.docx（21页珍藏版）》请在冰点文库上搜索。
数据库系统实验解答
实验一
（1）用SQL语句完成实验内容2-5的各种操作。
*实验内容2：
1）CREATEDATABASEstudents
具体格式：
CREATEDATABASEstudents
ONPRIMARY
（NAME=students_Data,
FILENAME=’E:
\students_Data.MDF’,
SIZE=10,
MAXSIZE=300,
FILEGROWTH=5）
LOGON
（NAME=students_Log,
FILENAME=’E:
\students_Log.MDF’,
SIZE=10,
MAXSIZE=200,
FILEGROWTH=2）
）
2）DROPDATABASEStudents
3）CREATEDATABASEbookdb
4）sp_helpdbbookdb
项目
内容
数据库所有者
8C62E96C274F451\Administrator
数据库大小
1.24MB
可用空间
0.51MB
数据文件名
Bookdb_data
数据文件物理存放位置
C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\data\bookdb_data.mdf
日志文件名
Bookdb_log
日志文件物理存放位置
C:
\ProgramFiles\MicrosoftSQLServer\MSSQL\data\bookdb_log.LDF
*实验内容3：
1）USEbookdb
createtablebook
（Book_idintprimarykey,Book_namechar（50）notnull,Pricefloatnotnull,Publisherchar（26）notnull,Citychar（20）notnull）
2）createtableOrderform
（Order_idintprimarykey,Book_idintnotnull,Book_numberintnotnull,Order_datedatetimenotnull,Client_idintnotnull）
3）createtableAuthors
（Author_idintprimarykey,Author_namechar（8）notnull,Addresschar（50）,Telphonechar（15））
4）createtableClients
（Client_idintprimarykey,Client_namechar（8）notnull,Addresschar（50）notnull）
*实验内容4：
1）altertablebook
addauthor_idintnotnulldefault''
2）altertablebook
dropcity
3）altertablebook
addconstraintunqBknameUNIQUE（Book_name）,
constraintchkPricecheck（price>0andprice<100）
添加约束，要求Book_name取值不能重复，Price的取值在0~100之间。
*实验内容5：
createindexix_authorsonauthors（author_name）
Createindexix_OrderformonOrderform
（Order_idasc,Order_datedesc）with（FILLFACTOR=10）
（2）创建所有学生的基本信息和选课信息的视图S_C_all。
CREATEVIEWS_C_allAS
SELECTstudent.*,o,sc.score
FROMstudent,scWHEREstudent.sno=sc.sno
（3）基于上述视图S_C_all查询选修C2课程的学生姓名及其所在系。
。
selectsno,sdeptfromS_C_allwherecno=’C2’
（4）创建一个视图CS，包含选课成绩及格的学生编号、所选课程号和该课程成绩。
createviewCSas
selectsno,cno,scorefromscwherescore>=60
（5）创建一个基于视图的视图S_CS，基于（3）中建立的视图CS，定义一个包含学生编号、学生所选课程数目和平均成绩的视图。
createviewS_CS（sno,cnt,avg_sc）as
selectsno,count（*）,avg（score）
fromCS
groupbysno
（6）创建带表达式的视图，由学生姓名、所选课程名、所有课程成绩再多5分这几个属性组成。
createviewBT_SC（sname,cname,newscore）as
selectstudent.sname,ame,sc.score+5
fromstudent,course,sc
wherestudent.sno=sc.snoando=o
（7）插入元组（'99004’，’C3’，59）到视图CS中。
insertintoCSvalues（'99004’,’C3’,59）
（8）删除视图CS。
dropviewCS
实验二
1、student表中插入元组（'99006','郭启芳','女',15,'CS'），给出操作结果，分析原因并进行修改。
INSERTStudent（sno,sname,ssex,sage,sdept）
VALUES（'99006','郭启芳','女',15,'CS'）
显示：
消息2627，级别14，状态1，第1行
违反了PRIMARYKEY约束'PK_STUDENT'。
不能在对象'dbo.STUDENT'中插入重复键。
语句已终止。
修改：
将学号改成与已有学号不会重复的值。
2、求学生的选课人数和学生平均成绩，并把结果存入数据表tot_score。
使用SELECTINTO和INSERTINTO两种方法实现。
（1）SELECTINTO语句
SELECTcno,count（*）ascnt_sc,avg（score）asavg_sc
INTOtot_score
FROMSC
GROUPBYcno
（2）INSERTINTO语句
createtabletot_score
（cnochar
（2）,cnt_scint,avg_scfloat）
INSERTINTOtot_score
SELECTcno,count（*）,avg（score）
FROMSC
GROUPBYcno
3、ManagementStudio：
右击SC表，在菜单中选择“打开表”，在列出的数据中直接修改
用T-SQL语句：
UPDATESC
SETscore=score+3
WHEREsno='99002'ANDcno='C2'
4、
1）查询’IS’系所有学生的姓名，并按学号升序排列。
selectsname
fromstudent
wheresdept=’IS’orderbysno
2）查询学分为2或3，且课程名称以”数”开头的课程编号。
selectcno
fromcourse
wherecnamelike'数%'and（credit='2'orcredit='3'）;
3）查询所有学生的编号及其所选课程的平均成绩，按平均成绩降序排列。
selectsno,avg（score）
fromsc
groupbysno
orderbyavg（score）desc
4）查询至少选修了三门课程的学生编号。
selectsno
fromsc
groupbysno
havingcount（*）>=3
5）查询所有选了“信息系统”的学生编号。
selectsno
fromsc,course
whereo=oandame='信息系统'
6）求出选择了同一课程的学生对。
selectdistinctx.sno,y.sno
fromscx,scy
whereo=oandx.sno7）查询选修了学号99002的学生所选的某门课程的学生编号。
selectdistincty.sno
fromscx,scy
whereo=oandx.sno='99002'andy.sno<>'99002'
8）分别用等值连接和谓词IN两种方式查询姓名为’张立’的学生所选课程的编号和成绩。
等值连接：
selecto,sc.score
fromstudent,sc
wheresname=’张立’andsc.sno=student.sno
谓词IN：
selectcno,score
fromsc
wheresnoin（
selectsnofromstudentwheresname=’张立’）
9）查询选修“数据库”课程的成绩比姓名为“张立”学生高的所有学生的编号和姓名。
selectsno,sname
fromstudent
wheresnoin（
selectc1.snofromscasc1,scasc2
wherec1.score>c2.scoreando=o
andc2.sno=（selectsnofromstudentwheresname=’张立’）
ando=（selectcnofromcoursewherecname=’数据库’））
10）查询学分比课程“数学”多的课程的名称。
selectamefromcourseasc1,courseasc2
wherec1.credit>c2.creditandame=’数学’
5、用T-SQL语句完成下面的要求：
用WHILE语句实现计算10000减1、减2、减3，…，一直减到50的结果，并显示最终结果。
DECLARE@xint,@yint
SELECT@x=10000,@y=1
WHILE（@y<=50）
BEGIN
SET@x=@x-@y
SET@y=@y+1
END
PRINT@x
6、用T-SQL语句完成下面的要求，并保存为脚本文件。
使用CASE语句实现：
声明变量@x，@y为字符型，长度均为6，为@x赋初值‘abc’，分情况判断：
当@x=‘a’时，@y=’1’+‘abc:
‘
当@x=‘b’时，@y=’2’+‘^bc:
‘
当@x=‘c’时，@y=’3’+‘abc:
‘
否则，@y=’no’
用函数显示@y去掉尾部空格的结果。
解法一：
DECLARE@xchar（6）,@ychar（6）
SET@x='abc'
SELECT@y=
CASE@x
WHEN'a'THEN'1'+'abc:
'
WHEN'b'THEN'2'+'^bc:
'
WHEN'c'THEN'3'+'abc:
'
ELSE'no'
END
SELECT@y=rtrim（@y）
PRINT@y
解法二：
DECLARE@xchar（6）,@ychar（6）
SET@x='c'
SELECT@y=
CASE
WHEN@x='a'THEN'1'+'abc:
'
WHEN@x='b'THEN'2'+'^bc:
'
WHEN@x='c'THEN'3'+'abc:
'
ELSE'no'
END
SELECT@y=rtrim（@y）
PRINT@y
7、在ManagementStudio中查看查询分析器创建的默认对象dept_defa。
8、通过T-SQL语句解除student表的SDEPT字段与默认对象dept_defa的绑定关系，然后删除对象dept_defa。
sp_unbindefault'student.sdept'
dropdefaultdept_defa
9、运用规则对象score_rul实现对sc表score字段的值约束，使score值>=0且<=100。
CREATERULEscore_rulAS@val>=0AND@val<=100
GO
EXECsp_bindrule‘score_rul’,’SC.score’
GO
10、在查询分析器中定义一数据类型为八位字符型，且非空，用于描述学生姓名。
sp_addtypetyp_ch_name,'char（8）','notnull'
实验三
（1）用T-SQL语句完成下面的查询要求：
1）列出所有课程的名称和选修学生的编号。
（用右外连接完成）
右外连接：
Selectame,sc.sno
fromscrightouterjoincourse
ono=o
左外连接：
Selectame,sc.sno
fromcourseleftouterjoinsc
ono=o
2）查询学分最高的课程的编号和选修该课程的学生学号。
selectcno,snofromsc
wherecnoin（
selectcnofromcourse
wherecredit=（selectmax（credit）fromcourse））
或：
selecto,snofromsc
wherenotexists（
select*fromcoursewherecredit>
（selectcreditfromcoursewhereo=o））
3）查询没有学生选的课程名称。
selectcnamefromcourse
wherecnonotin（
selectcnofromsc）
4）找出选修课程“数据库”的学生选修的所有课程名称。
selectcnamefromcourse
wherecnoin（
selectcnofromscwheresnoin（
selectsnofromcourse,scwherecname='数据库'ando=o））
5）查询选修了99002学生选修的所有课程的学生编号。
selectdistinctsnamefromstudentwherenotexists（
select*fromscasc1wherec1.sno='99002'andnotexists（
select*fromscasc2
wherec2.sno=student.snoando=oandstudent.sno<>c1.sno））
6）实现集合交运算，查询既选修课程“数学”又选修课程“信息系统”的学生编号。
selectx.snofromscasx,scasy
where（o=（selectcnofromcoursewherecname='数学'）
ando=（selectcnofromcoursewherecname='信息系统'）
andx.sno=y.sno）
（2）参见实验指导书（三）的“安全性管理”。
（3）用触发器T1实现表间的参照完整性，当向sc表插入或修改一条记录时，检查记录sno字段的值在student表是否存在，若不存在，则取消插入或修改操作。
CREATETRIGGERT1onSC
FORINSERT,UPDATE
AS
BEGIN
IF（（SELECTsnoFROMinserted）NOTIN
（SELECTsnoFROMstudent））
ROLLBACK
END
（4）建立一个在student表上的触发器，当向student表插入一条记录时，自动显示student表中的记录。
CREATETRIGGERT_StuonSTUDENT
FORINSERT
AS
SELECTsno,snameFROMinserted
（5）建立一个在student表上的触发器T3，要求插入记录的sage值必须比表中已记录的最小sage值大。
CREATETRIGGERT3onSTUDENT
FORINSERT
AS
IF（（SELECTsageFROMinserted）<=（SELECTmin（sage）FROMstudent））
BEGIN
PRINT'InsertFail!
'
ROLLBACKTRANSACTION
END
演示触发器作用：
INSERTINTOSTUDENT（Sno,Sname,Ssex,Sage,Sdept,SMajor,Phone,Native）
VALUES（'99019','钱群','男',14,'CS','软件工程',2680916,'山东'）
（6）创建递归触发器，删除STUDENT表里的学生时，将该学号的学生也从其选课表SC里头全部删除，并给出删除是否执行的报告（假设表之间未建关系约束）。
CREATETRIGGERTt_SConSC
FORDELETE
AS
PRINT‘deleteSCisexecuting!
’
DECLARE@numchar（5）,@msgchar（30）
SELECT@num=snoFROMdeletedGROUPBYsno
IF@@rowcount=0
BEGIN
PRINT‘norowsaffected!
’
RETURN
END
SELECT@msg=’deletingSCforsno=’+@num’
PRINT@msg
GO
再建立另一个触发器：
CREATETRIGGERTr_SonSTUDENT
FORDELETE
AS
PRINT‘deleteSTUDENTisexecuting!
’
DECLARE@numchar（5）,@msgchar（30）
SELECT@num=snoFROMdeleted
SELECT@msg=’deletingSTUDENTforsno=’+@num’
PRINT@msg
DELETESCWHEREsno=@num
GO
演示触发器作用：
DELETEFROMSTUDENTWHEREsno=’99003’
（7）在基本表student中创建一个游标，按学号排序取出男生的学号和姓名。
DECLARESno_curCURSOR
FORSELECTsno,snameFROMSTUDENTWHEREssex=’男’ORDERBYsno
GO
OPENsno_cur
DECLARE@snochar（5）,@snamechar（8）
FETCHNEXTFROMsno_curINTO@sno,@sname
WHILE@@fetch_status=0
BEGIN
PRINT@sno+@sname
FETCHNEXTFROMsno_curINTO@sno,@sname
END
CLOSEsno_cur
DEALLOCATEsno_cur
（8）在基本表course中创建一个游标，先取出表的第三条记录，再取出后两条记录。
DECLAREC_curCURSORSCROLL
FORSELECT*FROMCOURSE
OPENC_cur
FETCHABSOLUTE3FROMC_cur
FETCHRELATIVE2FROMC_cur
CLOSEC_cur
DEALLOCATEC_cur
（9）在基本表SC中创建一个游标，若学生成绩低于平均成绩，则成绩增加10%。
DECLAREupSC_curCURSORDYNAMIC
FORSELECTscoreFROMSCFORUPDATE
GO
DECLARE@avg_scorefloat,@scoreint
SELECT@avg_score=avg（score）FROMSC
OPENupSC_cur
FETCHNEXTFROMupSC_curINTO@score
WHILE@@fetch_status=0
BEGIN
IF（@score<@avg_score）
UPDATESCSETscore=score*1.10WHERECURRENTOFupSC_cur
FETCHNEXTFROMupSC_curINTO@score
END
CLOSEupSC_cur
DEALLOCATEupSC_cur
GO
（10）题目有误，答案略。
说明：
CREATEPROC过程中不能包含CREATEDEFAULT、CREATERULE等语句
（11）创建向表student进行插入操作的存储过程，并通过过程调用将下列数据添加到student表中
（'99008','郭凌雨','女',19,'MA','计算数学',2680076,'江苏'）
创建存储过程：
USESCMIS
GO
CREATEPROCstudent_add
（@sSnochar（5）,@sSnamechar（8）,@sSsexchar
（2）,@sSdeptvarchar（10）,
@sSMajorvarchar（10）,@sSageint,@sPhonechar（7）,@sNativevarchar（30））
AS
BEGIN
INSERTINTOstudentVALUES
（@sSno,@sSname,@sSsex,@sSdept,@sSMajor,@sSage,
@sPhone,@sNative）
END
RETURN
GO
过程调用：
（'99008','郭凌雨','女','MA','计算数学',19,2680076,'江苏'）
EXECs
升级会员 