数据库实验3答案.pdf

《数据库实验3答案.pdf》由会员分享，可在线阅读，更多相关《数据库实验3答案.pdf（13页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、-实验三：交互式 SQL 语句的使用 1、实验目的 1掌握数据库对象的操作过程，包括创立、修改、删除 2熟悉表的各种操作，包括插入、修改、删除、查询 3熟练掌握常用 SQL 语句的根本语法 2、实验平台 使用 SQL Server 提供的 Microsoft SQL Server Management Studio 工具，交互式使用 SQL 语句。3 实验容及要求 选择如下一个应用背景之一：学生选课系统 习题 3、4、和 5 中使用的数据库 其它你熟悉的应用 1建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。2要求认真进展实验，记录各实验用例及执行结果。3深入

2、了解各个操作的功能。实验要求包括如下方面的容：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 表保存学生选课信息，其构

3、造如下表：表 1.Student 表构造 列名称 用途 类型 长度 约束 备注 Sno*字符 8 主键 Sname 字符 8 Sse*性别 字符 2 Sage 年龄 整型 -Sdept 所在系 字符 20 Sclass 班级 字符 4 表 2.Course 表构造 列名称 用途 类型 长度 约束 备注 o 课程号 字符 4 主键 ame 课程名 字符 40 Cpno 先修课程号 字符 4 Ccredit 学分 整型 表 3.SC 表构造 列名称 用途 类型 长度 约束 备注 Sno*字符 8 外键 o 课程号 字符 4 Grade 成绩 整型 1创立、修改及删除根本表 1创立 Student

4、表 CREATETABLEStudent(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8),Sse*CHAR(2)NOTNULL,SageINT,SdeptCHAR(20);2创立 Course 表 CREATETABLECourse(oCHAR(4)PRIMARYKEY,ameCHAR(40)NOTNULL,CpnoCHAR(4),CcreditSMALLINT,);3创立 SC 表 CREATETABLESC(SnoCHAR(8)FOREIGNKEY(Sno)REFERENCESStudent(Sno),-oCHAR(4),GradeSMALLINT,);4创立员工表 E

5、mployee CREATETABLEEmployee(编号CHAR(8)PRIMARYKEY,VARCHAR(8)notnull 部门CHR40,工资numeric(8,2),生日datetime，职称char(20),);指出该语句中的错误并改正后执行。5检查表是否创立成功 SELECT*FROMStudent SELECT*FROMCourse SELECT*FROMSC SELECT*FROMEmployee 6修改表构造及约束 增加班级列 ALTERTABLEStudentADDSclasschar(4)修改年龄列 ALTERTABLEStudentALTERCOLUMNSagesm

6、allint 增加约束 ALTERTABLECourseADDUNIQUE(ame)7删除表 DROPTABLEEmployee 2创立索引 1为 Course 表按课程名称创立索引 CREATEINDE*iameOnCourse(ame)2为 Student 表按学生创立唯一索引 CREATEUNIQUEINDE*iSnameONStudent(Sname)3为 SC 表按*和课程号创立聚集索引 CREATECLUSTEREDINDE*iSnooOnSC(Sno,odesc)-4为 Course 表按课程号创立唯一索引 请自己完成该操作 3创立视图 建立信息系学生的视图：CREATEVIEW

7、IS_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.课程信息表 课程

8、号 课程名 先修课程号 学分 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,晨

9、,女,19,CS,1001)INSERTINTOStudent(Sno,Sname,Sse*,Sage,Sdept,Sclass)VALUES(20100021,王敏,女,18,MA,1002)INSERTINTOStudent(Sno,Sname,Sse*,Sage,Sdept,sclass)VALUES(20100031,立,男,19,IS,1003)INSERTINTOStudent(Sno,Sname,Sse*,sclass)VALUES(20100003,洋,女,1001)检查以下语句中的错误，并改正：INSERT INTO Student(Sno,Sname,Sse*,Sage,S

10、dept,sclass)VALUES(20100010,斌,男,19,IS,1005)INSERT INTO Student VALUES(20100022,明明,19,男,CS,1002)2插入到Course表 INSERTINTOCourse(o,ame,Cpno,Ccredit)VALUES(1,数据库系统原理,5,4)INSERTINTOCourse(o,ame,Cpno,Ccredit)VALUES(2,高等数学,null,2)INSERTINTOCourse(o,ame,Cpno,Ccredit)VALUES(3,管理信息系统,1,4)请写出插入其余行的插入语句，并插入数据。3插入

11、到SC表 INSERTINTOSCVALUES(20100001,1,92)INSERTINTOSCVALUES(20100002,2,80)INSERTINTOSC(Sno,o)VALUES(20100003,1)INSERTINTOSC(Sno,o,Grade)VALUES(20100010,3,null)-请写出插入其余行的插入语句，并运行。4多行插入到表中 创立存一个表，保存学生的*、和年龄：CREATETABLEcs_Student(*char(8),char(8),年龄smallint);插入数据行：INSERTINTOcs_Student SELECTSno,Sname,Sage

12、 FROMstudentWhereSdept=CS;5检查插入到表中的数据 SELECT*FROMStudent SELECT*FROMCourse SELECT*FROMSC 2 修改数据 1将学生 20100001 的年龄改为 22 岁。UPDATEstudentSETSage=22 WHERESno=20100001;2将所有学生的年龄增加一岁。UPDATEStudentSETSage=Sage+1 3填写斌同学的管理信息系统课程的成绩 UPDATESCSETGrade=85 WHERESno=20100010ANDo=3 4将计算机科学系全体学生的成绩加 5 分 UPDATEscSET

13、Grade=Grade+5 WHERECS=(selectSdeptfromstudentwherestudent.Sno=sc.Sno);5请自己完成如下操作 将晨同学的 2 号课程成绩修改为 80 将20100021同学的*修改为20100025 6检查数据是否修改 3.删除数据-1删除*为 201000022 的学生记录 DELETEFROMStudentWHERESno=20100022 2删除*20100001 学生的 1 号课程选课记录 将选课信息复制到一个临时表 tmpSC 中：SELECT*INTOtmpSCFROMSC 在 tmpSC 中执行删除操作：DELETEFROMtm

14、pSCWHERESno=20100001ando=1 3删除临时表中 20100002 学生的全部选课记录 请自己完成该操作。4删除计算机科学系所有学生的选课记录 DELETEFROMtmpSCWHERECS=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno);5删除全部选课记录 DELETEFROMtmpSC 6检查数据是否删除 3.3 数据查询操作 完成如下查询操作：1 单表查询 1按指定目标列查询 查询学生的详细记录：SELECT*FROMStudent;查询学生的*、和年龄 SELECTSno,Sname,SageFROMStudent

15、;2目标列包含表达式的查询 查询全体学生的、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,Year of Birth:,2004-Sage,LOWER(Sdept)FROMStudent;3查询结果集中修改列名称 查询全体学生的、出生年份和所有系，要求用小写字母表示所有系名。-SELECTSname,Year of Birth:asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)FROMStudent;4取消重复行 查询选修了课程的学生*：比拟ALL和DISTINCT的区别 SELECTSnoFROMSC;SELECTDIS

16、TINCTSnoFROMSC;5简单条件查询 查询计算机科学系全体学生的 SELECTSnameFROMStudent WHERESdept=CS;6按围查询 查询年龄在2023岁之间的学生的、系别和年龄 SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN 20 AND 23 7查询属性值属于指定集合的行 查询信息系IS、数学系MA和计算机科学系CS学生的和性别 SELECTSname,Sse*FROMStudentWHERESdeptIN(IS,MA,CS);8模糊查询 查询所有姓学生的、*和性别 SELECTSname,Sno,Sse*FRO

17、MStudentWHERESnameLIKE%9查询空值 查询缺少成绩的学生的*和相应的课程号 SELECTSno,oFROMscWHEREGradeisnull;10多重条件查询 查询计算机科学系年龄在岁以下的学生 SELECTSnameFROMstudentWHERESdept=CSandSage 90 4完成下面的查询 统计每个同学的*、选课数、平均成绩和最高成绩 统计每个班的每门课的选课人数、平均成绩和最高成绩 3.连接查询 1在 WHERE 中指定连接条件 查询每个参加选课的学生信息及其选修课程的情况 SELECTStudent.Sno,Sname,Sse*,Sage,Sdept,o

18、,Grade FROMStudent,SC WHEREStudent.Sno=SC.Sno-查询每一门课的间接先修课 SELECT*FROMcoursefirst,coursesecond WHEREfirst.Cpno=second.o;SELECTfirst.o,second.CpnoFROMcoursefirst,coursesecond WHEREfirst.Cpno=second.o;2在 FROM 中指定连接条件 查询每个参加选课的学生信息及其选修课程的情况 SELECTStudent.Sno,Sname,Sse*,Sage,Sdept,o,Grade FROMStudentJOI

19、NSCON(Student.Sno=SC.Sno)3使用外连接查询 查询每个学生信息及其选修课程的情况 SELECTStudent.Sno,Sname,Sse*,Sage,Sdept,o,Grade FROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno)4复合条件连接查询 查询选修号课程且成绩在分以上的所有学生 SELECTStudent.Sno,Sname FROMStudentjoinSCON(Student.Sno=SC.Sno)/*连接条件*/WHERESC.o=2ANDSC.Grade 90;/*过滤条件*/5多表查询 查询每个学生的*、选修

20、的课程名及成绩 SELECTStudent.Sno,Sname,ame,Grade FROMStudent,SC,Course WHEREStudent.Sno=SC.SnoANDSC.o=Course.o;6完成以下查询 查询选修了 2 号课程的同学的*和 查询各门课程的课程号、课程名称以及选课学生的*查询选修了数据库系统原理课程的同学的*和和成绩 4.嵌套查询 1由 In 引出的子查询 查询与晨在同一个系学习的学生 SELECTSno,Sname,Sdept FROMStudent WHERESdeptIN(SELECTSdeptFROMStudent WHERESname=晨);2由比拟

21、运算符引出的子查询 找出每个学生超过他选修课程平均成绩的课程号。SELECTSno,oFROMSC*WHEREGrade=(SELECTAVG(Grade)FROMSCy-WHEREy.Sno=*.Sno);3带修饰符的比拟运算符引出的子查询 查询其他系中比计算机科学系所有学生年龄都小的学生及年龄。SELECTSname,SageFROMStudent WHERESageALL(SELECTSageFROMStudentWHERESdept=CS)ANDSdeptCS;4由 E*ISTS 引出的子查询 查询所有选修了1号课程的学生 SELECTSnameFROMStudent WHEREE*I

22、STS(SELECT*FROMSCWHERESno=Student.SnoANDo=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 E*CEPT SELECT*FROMStudentWHERESage=S_G.Gavg 3查询每个学生的*、选修的课程名及成绩 SELECTSno,Sname,ame,Grade FROM*K_VIEW 4比拟使用视图查询和直接从基表查询的优点