数据库实验3答案Word文件下载.docx
《数据库实验3答案Word文件下载.docx》由会员分享，可在线阅读，更多相关《数据库实验3答案Word文件下载.docx（15页珍藏版）》请在冰豆网上搜索。
创建视图
视图查询
参考示例：
建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。
一、数据定义
创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：
表1.Student表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
主键
Sname
姓名
Ssex
性别
2
Sage
年龄
整型
Sdept
所在系
20
Sclass
班级
4
表2.Course表结构
Cno
课程号
Cname
课程名
40
Cpno
先修课程号
Ccredit
学分
表3.SC表结构
外键
Grade
成绩
1．创建、修改及删除基本表
（1）创建Student表
CREATETABLEStudent
（SnoCHAR（8）PRIMARYKEY,
SnameCHAR（8）,
SsexCHAR
（2）NOTNULL,
SageINT,
SdeptCHAR（20）
）;
（2）创建Course表
CREATETABLECourse
（CnoCHAR（4）PRIMARYKEY,
CnameCHAR（40）NOTNULL,
CpnoCHAR（4）,
CcreditSMALLINT,
（3）创建SC表
CREATETABLESC
（SnoCHAR（8）FOREIGNKEY（Sno）REFERENCESStudent（Sno）,
CnoCHAR（4）,
GradeSMALLINT,
（4）创建员工表Employee
CREATETABLEEmployee
（
编号CHAR（8）PRIMARYKEY,
姓名VARCHAR（8）notnull
部门CHR（40）,
工资numeric（8,2）,
生日datetime，
职称char（20）,
指出该语句中的错误并改正后执行。
（5）检查表是否创建成功
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
SELECT*FROMEmployee
（6）修改表结构及约束
●增加班级列
ALTERTABLEStudentADDSclasschar（4）
●修改年龄列
ALTERTABLEStudentALTERCOLUMNSagesmallint
●增加约束
ALTERTABLECourseADDUNIQUE（Cname）
（7）删除表
DROPTABLEEmployee
2．创建索引
（1）为Course表按课程名称创建索引
CREATEINDEXiCnameOnCourse（Cname）
（2）为Student表按学生姓名创建唯一索引
CREATEUNIQUEINDEXiSnameONStudent（Sname）
（3）为SC表按学号和课程号创建聚集索引
CREATECLUSTEREDINDEXiSnoCnoOnSC（Sno,Cnodesc）
（4）为Course表按课程号创建唯一索引
请自己完成该操作
3．创建视图
建立信息系学生的视图：
CREATEVIEWIS_Student
AS
SELECTSno,Sname,SageFROMStudent
WHERESdept='
IS'
;
将如下表格中的数据分别插入到数据库相应的表中：
表4.学生基本信息表
20100001
李勇
男
CS
1001
20100002
刘晨
女
19
20100021
王敏
18
MA
1002
20100031
张立
IS
1003
20100003
刘洋
20100010
赵斌
1005
20100022
张明明
表5.课程信息表
先修课程号
1
数据库系统原理
56
高等数学
3
管理信息系统
操作系统原理
6
5
数据结构
7
数据处理
C语言
表6.学生选课信息表
92
85
88
90
80
（1）插入到Student表
INSERTINTOStudentVALUES（'
20100001'
'
李勇'
男'
20,'
CS'
1001'
）
20100002'
刘晨'
女'
19,'
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）VALUES（'
20100021'
王敏'
18,'
MA'
1002'
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,sclass）VALUES（'
20100031'
张立'
1003'
INSERTINTOStudent（Sno,Sname,Ssex,sclass）VALUES（'
20100003'
刘洋'
检查下列语句中的错误，并改正：
INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,sclass）VALUES（'
20100010'
赵斌,'
19'
1005'
INSERTINTOStudentVALUES（'
20100022'
张明明'
（2）插入到Course表
INSERTINTOCourse（Cno,Cname,Cpno,Ccredit）VALUES（'
1'
数据库系统原理'
5'
4）
2'
高等数学'
null,2）
3'
管理信息系统'
请写出插入其余行的插入语句，并插入数据。
（3）插入到SC表
INSERTINTOSCVALUES（'
92）
80）
INSERTINTOSC（Sno,Cno）VALUES（'
INSERTINTOSC（Sno,Cno,Grade）VALUES（'
null）
请写出插入其余行的插入语句，并运行。
（4）多行插入到表中
创建存一个表，保存学生的学号、姓名和年龄：
CREATETABLEcs_Student
学号char（8）,
姓名char（8）,
年龄smallint
插入数据行：
INSERTINTOcs_Student
SELECTSno,Sname,Sage
FROMstudentWhereSdept='
（5）检查插入到表中的数据
（1）将学生20100001的年龄改为22岁。
UPDATEstudentSETSage=22WHERESno='
（2）将所有学生的年龄增加一岁。
UPDATEStudentSETSage=Sage+1
（3）填写赵斌同学的管理信息系统课程的成绩
UPDATESCSETGrade=85
WHERESno='
ANDCno='
（4）将计算机科学系全体学生的成绩加5分
UPDATEscSETGrade=Grade+5
WHERE'
=（selectSdeptfromstudentwherestudent.Sno=sc.Sno）;
（5）请自己完成如下操作
●将刘晨同学的2号课程成绩修改为80
●将“20100021”同学的学号修改为“20100025”
（6）检查数据是否修改
（1）删除学号为201000022的学生记录
DELETEFROMStudentWHERESno='
（2）删除学号20100001学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中：
SELECT*INTOtmpSCFROMSC
在tmpSC中执行删除操作：
DELETEFROMtmpSCWHERESno='
andCno='
（3）删除临时表中20100002学生的全部选课记录
请自己完成该操作。
（4）删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'
=（selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno）;
（5）删除全部选课记录
DELETEFROMtmpSC
（6）检查数据是否删除
完成如下查询操作：
（1）按指定目标列查询
●查询学生的详细记录：
SELECT*FROMStudent;
●查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent;
（2）目标列包含表达式的查询
●查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。
SELECTSname,'
YearofBirth:
'
2004-Sage,LOWER（Sdept）FROMStudent;
（3）查询结果集中修改列名称
'
asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER（Sdept）
FROMStudent;
（4）取消重复行
●查询选修了课程的学生学号：
比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC;
（5）简单条件查询
●查询计算机科学系全体学生的名单
SELECTSnameFROMStudentWHERESdept='
（6）按范围查询
●查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23
（7）查询属性值属于指定集合的行
●查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别
SELECTSname,SsexFROMStudentWHERESdeptIN（'
（8）模糊查询
●查询所有姓刘学生的姓名、学号和性别
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'
刘%'
（9）查询空值
●查询缺少成绩的学生的学号和相应的课程号
SELECTSno,CnoFROMscWHEREGradeisnull;
（10）多重条件查询
●查询计算机科学系年龄在岁以下的学生姓名
SELECTSnameFROMstudentWHERESdept='
andSage<
20;
（11）结果集排序
●查询全体学生情况，查询结果按所在系的系号升序排列，同一系中的学生按年龄降序排列
SELECT*FROMStudentORDERBYSdept,SageDESC;
（12）完成下列查询
●查询学生基本信息，结果集属性名使用汉字
●查询信息系且年龄大于23岁同学的学号和姓名
●查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系
●查询年龄不在21~24岁之间的学生的姓名、系别和年龄
（1）聚集函数的使用
●查询学生总人数
SELECTCOUNT（*）FROMStudent;
●查询选修了课程的学生人数
SELECTCOUNT（DISTINCTSno）FROMSC
●查询最高分
SELECTMAX（Grade）FROMSC
（2）聚集函数作用于部分行
●统计2号课程的总分、均分和最高分
SELECTSUM（grade）总分,AVG（grade）均分,MAX（grade）最高分
FROMscWHERECno='
（3）分组统计
●统计各门课程的选课人数、均分和最高分
selectcno课程号,count（*）人数,AVG（grade）均分,MAX（grade）最高分
fromscgroupbyCno
●统计均分大于90的课程
havingAVG（grade）>
90
（4）完成下面的查询
●统计每个同学的学号、选课数、平均成绩和最高成绩
●统计每个班的每门课的选课人数、平均成绩和最高成绩
（1）在WHERE中指定连接条件
●查询每个参加选课的学生信息及其选修课程的情况
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudent,SC
WHEREStudent.Sno=SC.Sno
●查询每一门课的间接先修课
SELECT*FROMcoursefirst,coursesecond
WHEREfirst.Cpno=second.Cno;
SELECTfirst.Cno,second.CpnoFROMcoursefirst,coursesecond
（2）在FROM中指定连接条件
FROMStudentJOINSCON（Student.Sno=SC.Sno）
（3）使用外连接查询
●查询每个学生信息及其选修课程的情况
FROMStudentLEFTOUTERJOINSCON（Student.Sno=SC.Sno）
（4）复合条件连接查询
●查询选修号课程且成绩在分以上的所有学生
SELECTStudent.Sno,Sname
FROMStudentjoinSCON（Student.Sno=SC.Sno）/*连接条件*/
WHERESC.Cno='
ANDSC.Grade>
90;
/*过滤条件*/
（5）多表查询
●查询每个学生的学号、姓名、选修的课程名及成绩
SELECTStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;
（6）完成下列查询
●查询选修了2号课程的同学的学号和姓名
●查询各门课程的课程号、课程名称以及选课学生的学号
●查询选修了数据库系统原理课程的同学的学号和姓名和成绩
（1）由In引出的子查询
●查询与“刘晨”在同一个系学习的学生
SELECTSno,Sname,SdeptFROMStudent
WHERESdeptIN（SELECTSdeptFROMStudentWHERESname='
（2）由比较运算符引出的子查询
●找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,CnoFROMSCx
WHEREGrade>
=（SELECTAVG（Grade）FROMSCy
WHEREy.Sno=x.Sno）;
（3）带修饰符的比较运算符引出的子查询
●查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECTSname,SageFROMStudent
WHERESage<
ALL（SELECTSageFROMStudentWHERESdept='
ANDSdept<
>
（4）由EXISTS引出的子查询
●查询所有选修了1号课程的学生姓名
SELECTSnameFROMStudent
WHEREEXISTS（SELECT*FROMSCWHERESno=Student.SnoANDCno='
（1）集合并
●查询计算机科学系的学生及年龄不大于19岁的学生
SELECT*FROMStudentWHERESdept='
UNION
SELECT*FROMStudentWHERESage<
=19
（2）集合交
●查询计算机科学系且年龄不大于19岁的学生
INTERSECT
（3）集合差
●查询计算机科学系且年龄大于19岁的学生
EXCEPT
=19;
视图操作
建立视图并基于视图进行查询：
1.创建视图
（1）建立学生基本信息视图
CREATEVIEWStudent_VIEW（学号,姓名,性别,年龄,系,班级）
SELECTSno,Sname,Ssex,Sage,Sdept,Sclass
（2）建立学生均分视图
CREATEVIEWS_G（Sno,Gavg）
SELECTSno,avg（Grade）
FROMSCGROUPBYSno;
（3）建立选课信息视图
CREATEVIEWXK_VIEW
SELECTStudent.*,Course.*,Grade
2.视图查询
（1）查询学生基本信息
SELECT*FROMStudent_VIEW
（2）找出每个学生超过他选修课程平均成绩的课程号
SELECTSC.Sno,Cno,grade
FROMSC,S_G
WHERESC.Sno=S_G.SnoandGrade>
=S_G.Gavg
（3）查询每个学生的学号、姓名、选修的课程名及成绩
SELECTSno,Sname,Cname,Grade
FROMXK_VIEW
（4）比较使用视图查询和直接从基表查询的优点
升级会员 