数据库原理实验答案.doc

实 验 报 告 课程名称： 数据库原理 系部名称： 计算机 专业班级： 网络工程 学生姓名： 学 号： 指导教师： 实验（ 一 ）实验名称： 数据库表的建立 实验时间： 2013-3-4 一、实验目的：本实验的目的是使熟悉SQL SERVER的常用数据类型，加深对SQL和SQL语言的查询语句的理解。1、熟练掌握简单表的创建。2、掌握主键约束、外键约束及及check约束的用法；3、掌握默认值约束的应用；4、了解规则、触发器的使用。二、实验内容：1、 使用查询分析器，用ddl建立studentinfo数据库2、 在studentinfo 数据库中定义3个表，students, courses,和sc,表的具体要求如下：用ddl建立上述3个表并增加如下约束： students,表列名说明数据类型约束说明Sno学号字符串,长度为10主键Sname姓名字符串,长度为8非空值Ssex性别字符串,长度为2非空值Sage年龄整数允许空值Sdept所在系字符串,长度为15非空值Courses表列名说明数据类型约束说明Cno课程号字符串，长度为6主键Cname课程名字符串，长度为20非空值PreCno先修课程号字符串，长度为6允许为空值Credits学分整数允许为空值SC表列名说明数据类型约束说明Sno学号字符串,长度为10主键Cno课程号字符串，长度为6主键Grade成绩整数允许为空值具体约束为：1、 请用至少2种方法定义stu数据库中student表的主键sno；2、 为表course中的字段cname添加唯一值约束；3、 对于数据表sc的sno、cno字段定义为外码，使之与表student的主码sno及表course的主码cno对应，实现如下参照完整性：1) 删除student表中记录的同时删除sc表中与该记录sno字段值相同的记录；2) 修改student表某记录的sno时，若sc表中与该字段值对应的有若干条记录，则拒绝修改；3) 修改course表cno字段值时，该字段在sc表中的对应值也应修改；4) 删除course表一条记录时，若该字段在在sc表中存在，则删除该字段对应的记录；5) 向sc表添加记录时，如果该记录的sno字段的值在student中不存在，则拒绝插入；4、定义check约束，要求学生学号sno必须为10位字符，且不能以0开头，第二三位皆为0；check(sno like000_ )5、定义stu数据库中student表中学生年龄值在16-25范围内；6、定义stu数据库中student表中学生姓名长度在2-8之间；check(len(sname)=2 and len(Sname)=16 and Sage=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”， 若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；13) 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；14) 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；15) 显示选修的课程数大于3的各个学生的选修课程数；16) 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；选做题：17) 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；18) 显示选修每门课程的及格人数;19) 显示被选修最多的课程数和最少的课程数；20) 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；21) 列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；三、源程序：1select student.*from studentwhere sname not like刘%2select student.*from studentwhere sname like沈 3select student.*from studentwhere sage(select AVG(sage)from student)order by sage desc11select sno 学号,sname 姓名,ssex 性别,出生年份=year(GETDATE()-sage,sdept 院系from studentorder by sage desc12select sdept, sdept=casewhen COUNT(distinct sno)=5then 规模很大when COUNT(distinct sno)=4and COUNT(distinct sno)=2and COUNT(distinct sno)4 then 规模小when COUNT(distinct sno)=70 and Grade316select cno,总人数=COUNT(distinct sno),最高成绩=MAX(grade),最低成绩=MIN(grade),平均成绩=AVG(grade)from scgroup by cnoorder by cno desc17select sno,平均成绩=AVG(grade)from scgroup by snohaving AVG(Grade)(select AVG(Grade) from sc where sno=200515001)18select cno,count(sno) as 合格人数from scwhere grade60group by cno19select sno,选课数=casewhen MAX(Cno)=COUNT(Cno)then 课程数最多when MIN(Cno)=COUNT(Cno)then 课程数最少endfrom scgroup by snohaving MAX(Cno)=COUNT(Cno)or MIN(Cno)=COUNT(Cno)20select sdept 院系名称,男生人数=sum(case when ssex=男 then 1 else 0 end),女生人数=sum(case when ssex=女 then 1 else 0 end) from student group by sdept21select sno,平均成绩=AVG(grade)from scwhere Grade=2四、主要问题和解决方法：这次的实验相对前面的两次实验就显得困难了许多，约束、限制的条件也不相同，对于题目的问题也比较不适应，所以做起来有点困难，不过参考书上的例子，最后还是做出来了。五、测试数据及结果：123456789106、 心得体会与自我评价：虽然这一次的实验有很大的困难，但是克服了种种困难后完成了本次的实验，感觉收获很多，不仅进一步掌握了SQL的使用，还学会了使用SQL中的SELECT关键字。实验（ 四 ）实验名称： 数据库连接查询 实验时间： 2013-4-29 一、实验目的：1、 掌握查询语句的一般格式；2、 熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。3、熟悉连接查询嵌套查询等的应用。2、 实验内容：设有一图书馆数据库,其中包括3个表,即图书表、读者表、和借阅表。三个表的结构如下图书表列名说明数据类型约束说明书号图书唯一的编号字符串，长度为20主键书名图书的名称字符串，长度为50空值作者图书的编著者名字符串，长度为30空值出版社图书的出版社字符串，长度为30空值单价出版社确定的图书的单价浮点型，float空值读者表列名说明数据类型约束说明读者号读者唯一的编号字符串，长度为10主键姓名读者姓名字符串，长度为8非空值性别读者的性别字符串，长度为2非空值办公电话读者性别字符串，长度为8空值部门读者办公电话字符串，长度为30空值借阅表列名说明数据类型约束说明读者号读者唯一的编号字符串，长度为10主键书号图书唯一的编号字符串，长度为20主键借出日期借出图书的日期字符串，长度为8，为yymmdd非空值归还日期归还图书的日期字符串，长度为8，为yymmdd空值（1）针对以上3个表，用SQL语言完成以下各项多表连接查询1） 查询借了书的读者的基本信息及其借阅情况2） 查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期3） 查询借阅了机械工业出版社出版，并且书名中包含数据库三个字的图书的读者，并显示读者号、姓名、书名、出版社，借出日期、归还日期 4） 查询办公室电话为88320701的所有读者的借阅情况，要求包括借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及其借阅日期（2）用SQL语言完成以下各项子查询：1）查询单价小于平均单价的图书的书号，书名及其出版社2）查询单价比机械工业出版社最高单价还高的图书书名及单价3）查询人民邮电出版社单价比机械工业出版社最低单价还低的图书书名及单价4）查询已归还的图书的信息5）查询从未被借阅过的图书信息6）查询正在借阅的图书信息7）查询至少借阅了1本机械工业出版社出版的图书的读者的读者号、姓名8）查询与王平的办公电话相同的读者的姓名3、 源程序：1select 读者.*,借阅.*from 读者,借阅where 读者.读者号=借阅.读者号;2select 读者.读者号,姓名,图书.书名,借阅.借出日期,归还日期from 读者,图书,借阅where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号;3select 读者.读者号,姓名,图书.书名,出版社,借阅.借出日期,归还日期from 读者,图书,借阅where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号 and 图书.出版社=机械工业出版社 and 书名 like%数据库%;4select 读者.读者号,姓名,图书.书名,借阅.借出日期from 读者,图书,借阅where 读者.读者号*=借阅.读者号 and 办公电话=88320701;5select 书号,书名,出版社from 图书where 单价(select max(单价)from 图书 where 出版社=机械工业出版社);7select 书名,单价from 图书where 出版社=人民邮电出版社 and 单价(select min(单价)from 图书 where 出版社=机械工业出版社);8select 图书.*from 图书,借阅where 借阅.归还日期 is not null and 借阅.书号=图书.书号;9select distinct 图书.*from 图书,借阅where 借阅.书号图书.书号;10select 图书.*from 图书,借阅where 借阅.归还日期 is null and 借阅.书号=图书.书号;11select 读者.读者号,姓名,书名from 图书,借阅,读者where 借阅.书号=图书.书号 and 读者.读者号=借阅.读者号 and 出版社=机械工业出版社;group by 读者.读者号,姓名having count(书名)=1;12select r2.姓名from 读者 r1,读者 r2where r1.办公电话=r2.办公电话 and r1.姓名=王平；四、主要问题和解决方法：这一次的实验难度有加大了，看了书上的例子也有很多不明白的地方，通过和同学讨论，上网查找资料，最后完成了本次的实验。五、测试数据及结果：1s2346、 心得体会与自我评价：这一次的实验，内容比较多，运用了新的表，刚接触的时候感觉比较难，有挺多的地方不明白，通过上网查询资料和与同学讨论得出了一些结论，也有了一些心得，感觉还好。实验（ 五 ）实验名称： 数据更新 实验时间： 2013-5-27 一、实验目的：1. 熟悉数据表结构及使用特点；2. 熟悉使用Management Stuio界面方式管理数据表数据；3. 熟悉使用T-SQL语句管理数据表数据。二、实验内容：以课本指定的数据库为例，并依据数据表的结构创建相对应的数据表（student、course、sc），请分别使用Management Stuio界面方式及T-SQL 语句实现进行以下操作：1. 向student表中插入一条记录，值为：20010115,stefen,25岁,男,艺术系2. 向sc表中新增一条记录，值为：学号为20010115,所选课程号为C23. 修改CS系姓名为“李勇”的学生姓名为“李咏”；4. 修改课程“数据处理”的学分为3学分；5. 将选修课程“1”的同学成绩加5分；6. 将选修课程“大学英语”的同学成绩加5分；7. 将学号为“200515010”的学生信息重新设置为“王丹丹、女、20、MA”；8. 向student表中增加记录：（200515026，王婧婧、女、21，CS）；9. 删除数据表student中无系别的学生记录；10. 删除数据表student中计算机系年龄大于25的男同学的记录；11. 删除数据表course中学分低于1学分的课程信息；思考题：插入数据记录除了Insert 还有什么方法？如何导出数据表中的数据？3、 源程序：1 INSERTINTO Student(Sno,Sname,Sage,Ssex,Sdept)VALUES(20010115,stefeN,25,M,Art)2INSERTINTO SC(Sno,Cno)VALUES(20010115,C2)3UPDATE StudentSET Sname=李永WHERE Sname=李勇and Sdept=cs4UPDATE course SET Ccredit=3 WHERE Cname=数据处理5UPDATE SCSET Grade = Grade + 5WHERE Cno=16UPDATE sc SET Grade=Grade=5 WHERE 大学英语 = (SELECT Cname FROM course WHERE course.Cno=sc.Cno)7UPDATE StudentSET sname=王丹丹,Ssex=女,Sdept=MAWHERE Sno=2005150108Insert into studentvalues(200515026,王婧婧,女,21,CS); 9delete from studentwhere Sdept=10delete from studentwhere Ssex=男 and Sage25 and Sdept=CS;11delete from