数据库原理实验答案.docx
《数据库原理实验答案.docx》由会员分享，可在线阅读，更多相关《数据库原理实验答案.docx（37页珍藏版）》请在冰豆网上搜索。
数据库原理实验答案
实验报告
课程名称：
数据库原理
系部名称：
计算机
专业班级：
网络工程
学生姓名：
学号：
指导教师：
实验
（一）
实验名称：
数据库表的建立实验时间：
2013-3-4
一、实验目的：
本实验的目的是使熟悉SQLSERVER的常用数据类型，加深对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（snolike‘[^0]00__________’）
5、定义stu数据库中student表中学生年龄值在16-25范围内；
6、定义stu数据库中student表中学生姓名长度在2-8之间；check（len（sname）<8）
7、定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
8、定义stu数据库student表中学生年龄值默认值为20；
2.studentinfo数据库的3个表students,courses,sc表中输入若干纪录，内容为：
students
sno
sname
ssex
sage
sdept
20010101
Hone
M
19
Computer
20010102
Sue
F
20
Computer
20010103
Smith
M
19
Math
20030101
Allen
M
18
Automation
20030102
Deepa
F
21
Art
courses
cno
cname
ProCno
Credits
C1
English
4
C2
Math
C1
2
C3
Database
C2
2
Sc
Sno
Cno
Grade
20010101
C1
90
20010102
C1
88
20010102
C2
94
20010102
C2
62
4、在上面的基础上分别向studentinfo数据库中的students,courses,sc表中添加少5条数据。
须满足三大约束条件
三、源程序：
createdatabasestudents
createtableStudents（
Snochar（10）primarykey,
Snamechar（8）check（len（Sname）>=2andlen（Sname）<=8）,
Ssexchar
（2）check（Ssexin（'男','女'））,
Sageintcheck（Sage>=16andSage<=25）default20,
Sdeptchar（15）notnull
）
createtableCourses（
Cnochar（6）primarykey,
Cnamechar（20）unique,
PreCnochar（6）null,
Creditsintnull
）
createtableSC（
Snochar（10）,
Cnochar（6）,
Gradeintnull,
primarykey（Sno,Cno）,
foreignkey（Sno）referencesStudents（Sno）
ondeletecascade,
foreignkey（Cno）referencesCourses（Cno）
ondeletecascade
onupdatecascade
）
四、主要问题和解决方法：
我们都对数据库不陌生，但是使用sql语句来使用数据库的还是第一次，感觉挺新鲜。
第一次使用，问题的难度不大，都可以自主完成。
五、测试数据及结果：
与预测结果一样。
6、心得体会与自我评价：
首次使用sql，感觉还好，但是我知道这只是一个开始，后面还有更加复杂的指令，这些都是从基础开始的，所以，即使这一次简单，还是要认真的对待。
实验
（二）
实验名称：
数据库的完整性约束实验时间：
2013-3-18
一、实验目的：
1. 掌握主键约束、外键约束及及check约束的用法；
2. 掌握默认值约束的应用；
3.了解规则、触发器的使用。
2、实验内容：
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必须为9位数字字符，且不能以0开头，第二三位皆为0；（不用做）
5、定义stu数据库中student表中学生年龄值在16-25范围内；
6、定义stu数据库中student表中学生姓名长度在2-8之间；
7、定义stu数据库中student表中学生性别列中只能输入“男”或“女”；
8、定义stu数据库student表中学生年龄值默认值为20；
三、源程序：
createdatabasestudentinfo
createtablestudents
（Snochar（10）primarykey,
Snamechar（8）,
Ssexchar
（2）,
Sagesmallint,
Sdeptchar（15））
createtablecourses
（Cnochar（6）primarykey,
Cnamechar（20）notnull,
PreCnochar（6）,
Ccreditssmallint）;
createtableSC
（Snochar（10）,
Cnochar（6）,
Gradesmallint,
primarykey（Sno,Cno）,
foreignkey（Sno）referencesstudents（Sno）,
foreignkey（Cno）referencescourses（Cno））;
INSERT
INTOStudents（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'20010101','Hone','M',19,'Computer'）;
INSERT
INTOStudents（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'20010102','Sue','F',20,'Computer'）;
INSERT
INTOStudents（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'20010103','Smith','M',19,'Math'）;
INSERT
INTOStudents（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'20030101','Allen','M',18,'Automation'）;
INSERT
INTOStudents（Sno,Sname,Ssex,Sage,Sdept）
VALUES（'20030102','Deepa','F',21,'Art'）;
四、主要问题和解决方法：
对于sql语句，在之前也有过了一些接触，但是没有深入的去了解，涉及到了一些复杂的sql语句时，还是感到陌生，不过相信多加练习就会好的。
五、测试数据及结果：
与预测结果一样。
6、心得体会与自我评价：
这次的实验是在上次的实验的基础上加上他的一些约束条件，相对来说也是比较简单的，操作也相对容易。
实验（三）
实验名称：
数据库单表查询实验时间：
2013-4-1
一、实验目的：
1.掌握SELECT语句的基本语法和查询条件表示方法；
2.掌握查询条件表达式和使用方法；
3.掌握GROUPBY子句的作用和使用方法；
4.掌握HAVING子句的作用和使用方法；
5.掌握ORDERBY子句的作用和使用方法。
二、实验内容：
1、以数据库原理实验三数据库中数据为基础，请使用T-SQL语句实现以下操作：
1）列出所有不姓刘的所有学生；
2）列出姓“沈”且全名为3个汉字的学生；
3）显示在1985年以后出生的学生的基本信息；
4）按照“性别、学号、姓名、年龄、院系”的顺序列出学生信息，其中性别按以下规定显示：
性别为男显示为男生，性别为女显示为女生，其他显示为“条件不明”；
5）查询出课程名含有“数据”字串的所有课程基本信息；
6）显示学号第八位或者第九位是1、2、3、4或者9的学生的学号、姓名、性别、年龄及院系；
7）列出选修了‘1’课程的学生，按成绩的降序排列；
8）列出同时选修“1”号课程和“2”号课程的所有学生的学号；
9）列出课程表中全部信息，按先修课的升序排列；
10）列出年龄超过平均值的所有学生名单，按年龄的降序显示；
11）按照出生年份升序显示所有学生的学号、姓名、性别、出生年份及院系，在结果集中列标题分别指定为“学号，姓名，性别，出生年份，院系”；
12）显示所有院系（要求不能重复，不包括空值），并在结果集中增加一列字段“院系规模”，其中若该院系人数>=5则该字段值为“规模很大”，若该院系人数大于等于4小于5则该字段值为“规模一般”，若该院系人数大于等于2小于4则该字段值为“规模稍小”，否则显示“规模很小”；
13）按照课程号、成绩降序显示课程成绩在70-80之间的学生的学号、课程号及成绩；
14）显示学生信息表中的学生总人数及平均年龄，在结果集中列标题分别指定为“学生总人数，平均年龄”；
15）显示选修的课程数大于3的各个学生的选修课程数；
16）按课程号降序显示选修各个课程的总人数、最高成绩、最低成绩及平均成绩；
选做题：
17）显示平均成绩大于“200515001”学生平均成绩的各个学生的学号、平均成绩；
18）显示选修每门课程的及格人数;
19）显示被选修最多的课程数和最少的课程数；
20）显示各个院系男女生人数，其中在结果集中列标题分别指定为“院系名称、男生人数、女生人数”；
21）列出有二门以上课程（含两门）不及格的学生的学号及该学生的平均成绩；
三、源程序：
1
selectstudent.*
fromstudent
wheresnamenotlike'刘%'
2
selectstudent.*
fromstudent
wheresnamelike'沈'
3
selectstudent.*
fromstudent
wheresage<28
4
selectssex=
casewhenssex='男'then'男生'
whenssex='女'then'女生'
else'条件不明'
end,sno,sname,sdept
fromstudent
5
selectcname
fromcourse
wherecnamelike'%数据%'
6
selectsno,sname,ssex,sage,sdept
fromstudent
wheresnolike'[12349]%'orsnolike'[12349]%'
7
selectstudent.sno,sname,grade
fromstudent,sc
whereCno='1'
orderbyGradedesc
8
selectsno,Cno
fromsc
wherecno=1orcno=2
9
select*
fromcourse
orderbycpnoasc
10
selectsno,sname
fromstudent
wheresage>（selectAVG（sage）fromstudent）
orderbysagedesc
11
selectsno学号,sname姓名,ssex性别,出生年份=year（GETDATE（））-sage,sdept院系
fromstudent
orderbysagedesc
12
selectsdept,sdept=
case
whenCOUNT（distinctsno）>=5then'规模很大'
whenCOUNT（distinctsno）>=4andCOUNT（distinctsno）<5then'规模一般'
whenCOUNT（distinctsno）>=2andCOUNT（distinctsno）<4then'规模小'
whenCOUNT（distinctsno）<2then'规模很小'
end
fromstudent
groupbysdept
13
selectsno,cno,grade
fromsc
whereGrade>=70andGrade<=80
orderbyCnodesc,Gradedesc
14
select学生总人数=COUNT（distinctsno）,平均年龄=AVG（sage）
fromstudent
15
selectsno,选修的课程数=COUNT（Cno）
fromsc
groupbysno
havingCOUNT（Cno）>3
16
selectcno,总人数=COUNT（distinctsno）,最高成绩=MAX（grade）,最低成绩=MIN（grade）,平均成绩=AVG（grade）
fromsc
groupbycno
orderbycnodesc
17
selectsno,平均成绩=AVG（grade）
fromsc
groupbysno
havingAVG（Grade）>（selectAVG（Grade）fromscwheresno='200515001'）
18
selectcno,count（sno）as合格人数
fromsc
wheregrade>60
groupbycno
19
selectsno,选课数=
case
whenMAX（Cno）=COUNT（Cno）then'课程数最多'
whenMIN（Cno）=COUNT（Cno）then'课程数最少'
end
fromsc
groupbysno
havingMAX（Cno）=COUNT（Cno）orMIN（Cno）=COUNT（Cno）
20
selectsdept院系名称,
男生人数=sum（casewhenssex='男'then1else0end）,
女生人数=sum（casewhenssex='女'then1else0end）
fromstudentgroupbysdept
21
selectsno,平均成绩=AVG（grade）
fromsc
whereGrade<60
groupbysno
havingCOUNT（*）>=2
四、主要问题和解决方法：
这次的实验相对前面的两次实验就显得困难了许多，约束、限制的条件也不相同，对于题目的问题也比较不适应，所以做起来有点困难，不过参考书上的例子，最后还是做出来了。
五、测试数据及结果：
1
2
3
4
5
6
7
8
9
10
6、心得体会与自我评价：
虽然这一次的实验有很大的困难，但是克服了种种困难后完成了本次的实验，感觉收获很多，不仅进一步掌握了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、源程序：
1
select读者.*,借阅.*
from读者,借阅
where读者.读者号=借阅.读者号;
2
select读者.读者号,姓名,图书.书名,借阅.借出日期,归还日期
from读者,图书,借阅
where读者.读者号=借阅.读者号and图书.书号=借阅.书号;
3
select读者.读者号,姓名,图书.书名,出版社,借阅.借出日期,归还日期
from读者,图书,借阅
where读者.读者号=借阅.读者号and图书.书号=借阅.书号
and图书.出版社='机械工业出版社'
升级会员 