最新数据库实验3答案.docx
《最新数据库实验3答案.docx》由会员分享,可在线阅读,更多相关《最新数据库实验3答案.docx(18页珍藏版)》请在冰豆网上搜索。
![最新数据库实验3答案.docx](https://file1.bdocx.com/fileroot1/2023-7/6/f22f77f8-662c-45a1-8978-26bfefed83d5/f22f77f8-662c-45a1-8978-26bfefed83d51.gif)
最新数据库实验3答案
实验三:
交互式SQL语句的使用
1、实验目的
(1)掌握数据库对象的操作过程,包括创建、修改、删除
(2)熟悉表的各种操作,包括插入、修改、删除、查询
(3)熟练掌握常用SQL语句的基本语法
2、实验平台
使用SQLServer提供的MicrosoftSQLServerManagementStudio工具,交互式使用SQL语句。
3 实验内容及要求
选择如下一个应用背景之一:
●学生选课系统
●习题3、4、和5中使用的数据库
●其它你熟悉的应用
(1)建立一个数据库和相关的表、索引、视图等数据库对象,练习对表、索引和视图的各种操作。
(2)要求认真进行实验,记录各实验用例及执行结果。
(3)深入了解各个操作的功能。
实验要求包括如下方面的内容:
3.1 数据定义
1. 基本表的创建、修改及删除
2. 索引的创建
3. 视图的创建
3.2 数据操作
完成各类更新操作包括:
1. 插入数据
2. 修改数据
3.删除数据
3.3 数据查询操作
完成各类查询操作
1. 单表查询
2. 分组统计
3.连接查询
4.嵌套查询
5.集合查询
3.4 数据操作
1. 创建视图
2. 视图查询
参考示例:
建立一个学生选课数据库,练习对表、视图和索引等数据库对象的各种操作。
一、数据定义
创建学生选课数据库ST,包括三个基本表,其中Student表保存学生基本信息,Course表保存课程信息,SC表保存学生选课信息,其结构如下表:
表1.Student表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
主键
Sname
姓名
字符
8
Ssex
性别
字符
2
Sage
年龄
整型
Sdept
所在系
字符
20
Sclass
班级
字符
4
表2.Course表结构
列名称
用途
类型
长度
约束
备注
Cno
课程号
字符
4
主键
Cname
课程名
字符
40
Cpno
先修课程号
字符
4
Ccredit
学分
整型
表3.SC表结构
列名称
用途
类型
长度
约束
备注
Sno
学号
字符
8
外键
Cno
课程号
字符
4
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';
3.2 数据操作
1. 插入数据
将如下表格中的数据分别插入到数据库相应的表中:
表4.学生基本信息表
学号
姓名
性别
年龄
所在系
班级
20100001
李勇
男
20
CS
1001
20100002
刘晨
女
19
CS
1001
20100021
王敏
女
18
MA
1002
20100031
张立
男
19
IS
1003
20100003
刘洋
女
1001
20100010
赵斌
男
19
IS
1005
20100022
张明明
男
19
CS
1002
表5.课程信息表
课程号
课程名
先修课程号
学分
1
数据库系统原理
56
4
2
高等数学
2
3
管理信息系统
1
4
4
操作系统原理
6
3
5
数据结构
7
4
6
数据处理
2
7
C语言
4
表6.学生选课信息表
学号
课程号
成绩
20100001
1
92
20100001
2
85
20100001
3
88
20100002
1
90
20100002
2
80
20100003
1
20100010
3
(1)插入到Student表
INSERTINTOStudentVALUES('20100001','李勇','男',20,'CS','1001')
INSERTINTOStudentVALUES('20100002','刘晨','女',19,'CS','1001')
INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,Sclass)VALUES('20100021','王敏','女',18,'MA','1002')
INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUES('20100031','张立','男',19,'IS','1003')
INSERTINTOStudent(Sno,Sname,Ssex,sclass)VALUES('20100003','刘洋','女','1001')
检查下列语句中的错误,并改正:
INSERTINTOStudent(Sno,Sname,Ssex,Sage,Sdept,sclass)VALUES('20100010',赵斌,'男','19','IS','1005')
INSERTINTOStudentVALUES('20100022','张明明',19,'男','CS','1002')
(2)插入到Course表
INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('1','数据库系统原理','5',4)
INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('2','高等数学',null,2)
手工艺制品是我国一种传统文化的象征,它品种多样,方式新颖,制作简单,深受广大学生朋友的喜欢。
当今大学生的消费行为表现在追求新颖,追求时尚。
追求个性,表现自我的消费趋向:
购买行为有较强的感情色彩,比起男生热衷于的网络游戏,极限运动,手工艺制品更得女生的喜欢。
INSERTINTOCourse(Cno,Cname,Cpno,Ccredit)VALUES('3','管理信息系统','1',4)
请写出插入其余行的插入语句,并插入数据。
(二)上海的人口环境对饰品消费的影响(3)插入到SC表
INSERTINTOSCVALUES('20100001','1',92)
综上所述,DIY手工艺品市场致所以受到认可、欢迎的原因就在于此。
我们认为:
这一市场的消费需求的容量是极大的,具有很大的发展潜力,我们的这一创业项目具有成功的前提。
INSERTINTOSCVALUES('20100002','2',80)
随科技的迅速发展,人们的生活日益趋向便捷、快速,方便,对于我国传统的手工艺制作,也很少有人问津,因此,我组想借此创业机会,在校园内开个DIY创意小屋。
它包括编织、刺绣、串珠等,让我们传统的手工制作也能走进大学,丰富我们的生活。
INSERTINTOSC(Sno,Cno)VALUES('20100003','1')
INSERTINTOSC(Sno,Cno,Grade)VALUES('20100010','3',null)
2.www。
cer。
net/artide/2003082213089728。
shtml。
请写出插入其余行的插入语句,并运行。
4、宏观营销环境分析(4)多行插入到表中
3、消费“多样化”创建存一个表,保存学生的学号、姓名和年龄:
但这些困难并非能够否定我们创业项目的可行性。
盖茨是由一个普通退学学生变成了世界首富,李嘉诚是由一个穷人变成了华人富豪第一人,他们的成功表述一个简单的道理:
如果你有能力,你可以从身无分文变成超级富豪;如果你无能,你也可以从超级富豪变成穷光蛋。
CREATETABLEcs_Student
我们从小学、中学到大学,学的知识总是限制在一定范围内,缺乏在商业统计、会计,理财税收等方面的知识;也无法把自己的创意准确而清晰地表达出来,缺少个性化的信息传递。
对目标市场和竞争对手情况缺乏了解,分析时采用的数据经不起推敲,没有说服力等。
这些都反映出我们大学生创业知识的缺乏;(
5、你认为一件DIY手工艺制品在什么价位可以接受?
学号char(8),
姓名char(8),
年龄smallint
);
插入数据行:
INSERTINTOcs_Student
SELECTSno,Sname,Sage
FROMstudentWhereSdept='CS';
(5)检查插入到表中的数据
SELECT*FROMStudent
SELECT*FROMCourse
SELECT*FROMSC
2. 修改数据
(1)将学生20100001的年龄改为22岁。
UPDATEstudentSETSage=22WHERESno='20100001';
(2)将所有学生的年龄增加一岁。
UPDATEStudentSETSage=Sage+1
(3)填写赵斌同学的管理信息系统课程的成绩
UPDATESCSETGrade=85
WHERESno='20100010'ANDCno='3'
(4)将计算机科学系全体学生的成绩加5分
UPDATEscSETGrade=Grade+5
WHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=sc.Sno);
(5)请自己完成如下操作
●将刘晨同学的2号课程成绩修改为80
●将“20100021”同学的学号修改为“20100025”
(6)检查数据是否修改
3.删除数据
(1)删除学号为201000022的学生记录
DELETEFROMStudentWHERESno='20100022'
(2)删除学号20100001学生的1号课程选课记录
将选课信息复制到一个临时表tmpSC中:
SELECT*INTOtmpSCFROMSC
在tmpSC中执行删除操作:
DELETEFROMtmpSCWHERESno='20100001'andCno='1'
(3)删除临时表中20100002学生的全部选课记录
请自己完成该操作。
(4)删除计算机科学系所有学生的选课记录
DELETEFROMtmpSCWHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno);
(5)删除全部选课记录
DELETEFROMtmpSC
(6)检查数据是否删除
3.3 数据查询操作
完成如下查询操作:
1. 单表查询
(1)按指定目标列查询
●查询学生的详细记录:
SELECT*FROMStudent;
●查询学生的学号、姓名和年龄
SELECTSno,Sname,SageFROMStudent;
(2)目标列包含表达式的查询
●查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
',2004-Sage,LOWER(Sdept)FROMStudent;
(3)查询结果集中修改列名称
●查询全体学生的姓名、出生年份和所有系,要求用小写字母表示所有系名。
SELECTSname,'YearofBirth:
'asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)
FROMStudent;
(4)取消重复行
●查询选修了课程的学生学号:
比较ALL和DISTINCT的区别
SELECTSnoFROMSC;
SELECTDISTINCTSnoFROMSC;
(5)简单条件查询
●查询计算机科学系全体学生的名单
SELECTSnameFROMStudentWHERESdept='CS';
(6)按范围查询
●查询年龄在20~23岁之间的学生的姓名、系别和年龄
SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23
(7)查询属性值属于指定集合的行
●查询信息系(IS)、数学系(MA)和计算机科学系(CS)学生的姓名和性别
SELECTSname,SsexFROMStudentWHERESdeptIN('IS','MA','CS');
(8)模糊查询
●查询所有姓刘学生的姓名、学号和性别
SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'刘%'
(9)查询空值
●查询缺少成绩的学生的学号和相应的课程号
SELECTSno,CnoFROMscWHEREGradeisnull;
(10)多重条件查询
●查询计算机科学系年龄在岁以下的学生姓名
SELECTSnameFROMstudentWHERESdept='CS'andSage<20;
(11)结果集排序
●查询全体学生情况,查询结果按所在系的系号升序排列,同一系中的学生按年龄降序排列
SELECT*FROMStudentORDERBYSdept,SageDESC;
(12)完成下列查询
●查询学生基本信息,结果集属性名使用汉字
●查询信息系且年龄大于23岁同学的学号和姓名
●查询年龄是17、18、20、23岁同学的学号、姓名、年龄和所在系
●查询年龄不在21~24岁之间的学生的姓名、系别和年龄
2. 分组统计
(1)聚集函数的使用
●查询学生总人数
SELECTCOUNT(*)FROMStudent;
●查询选修了课程的学生人数
SELECTCOUNT(DISTINCTSno)FROMSC
●查询最高分
SELECTMAX(Grade)FROMSC
(2)聚集函数作用于部分行
●统计2号课程的总分、均分和最高分
SELECTSUM(grade)总分,AVG(grade)均分,MAX(grade)最高分
FROMscWHERECno='2'
(3)分组统计
●统计各门课程的选课人数、均分和最高分
selectcno课程号,count(*)人数,AVG(grade)均分,MAX(grade)最高分
fromscgroupbyCno
●统计均分大于90的课程
selectcno课程号,count(*)人数,AVG(grade)均分,MAX(grade)最高分
fromscgroupbyCno
havingAVG(grade)>90
(4)完成下面的查询
●统计每个同学的学号、选课数、平均成绩和最高成绩
●统计每个班的每门课的选课人数、平均成绩和最高成绩
3.连接查询
(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
WHEREfirst.Cpno=second.Cno;
(2)在FROM中指定连接条件
●查询每个参加选课的学生信息及其选修课程的情况
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentJOINSCON(Student.Sno=SC.Sno)
(3)使用外连接查询
●查询每个学生信息及其选修课程的情况
SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,Grade
FROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno)
(4)复合条件连接查询
●查询选修号课程且成绩在分以上的所有学生
SELECTStudent.Sno,Sname
FROMStudentjoinSCON(Student.Sno=SC.Sno)/*连接条件*/
WHERESC.Cno='2'ANDSC.Grade>90;/*过滤条件*/
(5)多表查询
●查询每个学生的学号、姓名、选修的课程名及成绩
SELECTStudent.Sno,Sname,Cname,Grade
FROMStudent,SC,Course
WHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;
(6)完成下列查询
●查询选修了2号课程的同学的学号和姓名
●查询各门课程的课程号、课程名称以及选课学生的学号
●查询选修了数据库系统原理课程的同学的学号和姓名和成绩
4.嵌套查询
(1)由In引出的子查询
●查询与“刘晨”在同一个系学习的学生
SELECTSno,Sname,SdeptFROMStudent
WHERESdeptIN(SELECTSdeptFROMStudentWHERESname='刘晨');
(2)由比较运算符引出的子查询
●找出每个学生超过他选修课程平均成绩的课程号。
SELECTSno,CnoFROMSCx
WHEREGrade>=(SELECTAVG(Grade)FROMSCy
WHEREy.Sno=x.Sno);
(3)带修饰符的比较运算符引出的子查询
●查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。
SELECTSname,SageFROMStudent
WHERESageANDSdept<>'CS';
(4)由EXISTS引出的子查询
●查询所有选修了1号课程的学生姓名
SELECTSnameFROMStudent
WHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')
5.集合查询
(1)集合并
●查询计算机科学系的学生及年龄不大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
UNION
SELECT*FROMStudentWHERESage<=19
(2)集合交
●查询计算机科学系且年龄不大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
INTERSECT
SELECT*FROMStudentWHERESage<=19
(3)集合差
●查询计算机科学系且年龄大于19岁的学生
SELECT*FROMStudentWHERESdept='CS'
EXCEPT
SELECT*FROMStudentWHERESage<=19;
3.4 视图操作
建立视图并基于视图进行查询:
1.创建视图
(1)建立学生基本信息视图
CREATEVIEWStudent_VIEW(学号,姓名,性别,年龄,系,班级)
AS
SELECTSno,Sname,Ssex,Sage,Sdept,Sclass
FROMStudent;
(2)建立学生均分视图
CREATEVIEWS_G(Sno,Gavg)
AS
SELECT