数据库原理实验答案.docx

1、数据库原理实验答案 实 验 报 告 课程名称： 数据库原理 系部名称： 计算机 专业班级： 网络工程 学生姓名： 学 号： 指导教师： 实验（ 一 ）实验名称： 数据库表的建立 实验时间： 2013-3-4 一、实验目的：本实验的目的是使熟悉SQL SERVER的常用数据类型，加深对SQL和SQL语言的查询语句的理解。1、熟练掌握简单表的创建。2、掌握主键约束、外键约束及及check约束的用法；3、掌握默认值约束的应用；4、了解规则、触发器的使用。二、实验内容：1、 使用查询分析器，用ddl建立studentinfo数据库2、 在studentinfo 数据库中定义3个表，students,

2、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成绩整数允许

3、为空值具体约束为：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表中存在，则

4、删除该字段对应的记录；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则该字段值为“规

5、模稍小”，否则显示“规模很小”；13) 按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；14) 显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；15) 显示选修的课程数大于3的各个学生的选修课程数；16) 按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；选做题：17) 显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；18) 显示选修每门课程的及格人数;19) 显示被选修最多的课程数和最少的课程数；20) 显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生

6、人数、女生人数”；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

7、 院系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)

8、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 课程数最少e

9、ndfrom 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四、主要问题和解决方法：这次的实验相对前面的两次实验就显得困难了许多，约束、限制的条件也不相

10、同，对于题目的问题也比较不适应，所以做起来有点困难，不过参考书上的例子，最后还是做出来了。五、测试数据及结果：123456789106、心得体会与自我评价：虽然这一次的实验有很大的困难，但是克服了种种困难后完成了本次的实验，感觉收获很多，不仅进一步掌握了SQL的使用，还学会了使用SQL中的SELECT关键字。实验（ 四 ）实验名称： 数据库连接查询 实验时间： 2013-4-29 一、实验目的：1、掌握查询语句的一般格式；2、熟练掌握单表查询、连接查询、集合查询、统计查询和嵌套查询。3、熟悉连接查询嵌套查询等的应用。2、实验内容：设有一图书馆数据库,其中包括3个表,即图书表、读者表、和借阅表。

11、三个表的结构如下图书表列名说明数据类型约束说明书号图书唯一的编号字符串，长度为20主键书名图书的名称字符串，长度为50空值作者图书的编著者名字符串，长度为30空值出版社图书的出版社字符串，长度为30空值单价出版社确定的图书的单价浮点型，float空值读者表列名说明数据类型约束说明读者号读者唯一的编号字符串，长度为10主键姓名读者姓名字符串，长度为8非空值性别读者的性别字符串，长度为2非空值办公电话读者性别字符串，长度为8空值部门读者办公电话字符串，长度为30空值借阅表列名说明数据类型约束说明读者号读者唯一的编号字符串，长度为10主键书号图书唯一的编号字符串，长度为20主键借出日期借出图书的日期

12、字符串，长度为8，为yymmdd非空值归还日期归还图书的日期字符串，长度为8，为yymmdd空值（1）针对以上3个表，用SQL语言完成以下各项多表连接查询1） 查询借了书的读者的基本信息及其借阅情况2） 查询读者的读者号、姓名、借阅的图书名、借出日期、归还日期3） 查询借阅了机械工业出版社出版，并且书名中包含数据库三个字的图书的读者，并显示读者号、姓名、书名、出版社，借出日期、归还日期 4） 查询办公室电话为88320701的所有读者的借阅情况，要求包括借阅了图书的读者和没有借阅的读者，显示他们的读者号、姓名、书名及其借阅日期（2）用SQL语言完成以下各项子查询：1）查询单价小于平均单价的图书

13、的书号，书名及其出版社2）查询单价比机械工业出版社最高单价还高的图书书名及单价3）查询人民邮电出版社单价比机械工业出版社最低单价还低的图书书名及单价4）查询已归还的图书的信息5）查询从未被借阅过的图书信息6）查询正在借阅的图书信息7）查询至少借阅了1本机械工业出版社出版的图书的读者的读者号、姓名8）查询与王平的办公电话相同的读者的姓名3、源程序：1select 读者.*,借阅.*from 读者,借阅where 读者.读者号=借阅.读者号;2select 读者.读者号,姓名,图书.书名,借阅.借出日期,归还日期from 读者,图书,借阅where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号;3select 读者.读者号,姓名,图书.书名,出版社,借阅.借出日期,归还日期from 读者,图书,借阅where 读者.读者号=借阅.读者号 and 图书.书号=借阅.书号 and 图书.出版社=机械工业出版社