数据库实验3答案Word文件下载.docx

1、创建视图视图查询参考示例：建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。一、数据定义 创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1. Student表结构列名称用途类型长度约束备注Sno学号字符8主键Sname姓名Ssex性别2Sage年龄整型Sdept所在系20Sclass班级4表2. Course表结构Cno课程号Cname课程名40Cpno先修课程号 Ccredit学分表3. SC表结构外键Grade成绩1创建、修改及删除基本表 （1）创建Student表CREA

2、TETABLEStudent（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）创建员工表Employ

3、eeCREATETABLEEmployee（编号CHAR（8）PRIMARYKEY,姓名VARCHAR（8）notnull部门CHR（40）,工资numeric（8,2）,生日datetime，职称char（20）,指出该语句中的错误并改正后执行。（5）检查表是否创建成功SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSCSELECT*FROMEmployee（6）修改表结构及约束增加班级列ALTERTABLEStudentADDSclasschar（4）修改年龄列ALTERTABLEStudentALTERCOLUMNSagesmallint增加约

4、束ALTERTABLECourseADDUNIQUE（Cname）（7）删除表 DROPTABLEEmployee2创建索引（1）为Course表按课程名称创建索引CREATEINDEXiCnameOnCourse（Cname）（2）为Student表按学生姓名创建唯一索引CREATEUNIQUEINDEXiSnameONStudent（Sname）（3）为SC表按学号和课程号创建聚集索引CREATECLUSTEREDINDEXiSnoCnoOnSC（Sno,Cnodesc）（4）为Course表按课程号创建唯一索引请自己完成该操作3创建视图建立信息系学生的视图：CREATEVIEWIS_St

5、udentASSELECTSno,Sname,SageFROMStudentWHERESdept=IS;将如下表格中的数据分别插入到数据库相应的表中：表4.学生基本信息表20100001李勇男CS100120100002刘晨女1920100021王敏18MA100220100031张立IS100320100003刘洋20100010赵斌100520100022张明明表5.课程信息表先修课程号1数据库系统原理56高等数学3管理信息系统操作系统原理65数据结构7数据处理C语言表6.学生选课信息表9285889080（1）插入到Student表INSERTINTOStudentVALUES（2010

6、0001,李勇男,20,CS1001）20100002刘晨女,19,INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,Sclass）VALUES（20100021王敏,18,MA1002INSERTINTOStudent（Sno,Sname,Ssex,Sage,Sdept,sclass）VALUES（20100031张立1003INSERTINTOStudent（Sno,Sname,Ssex,sclass）VALUES（20100003刘洋检查下列语句中的错误，并改正：INSERT INTO Student（Sno,Sname,Ssex,Sage,Sdep

7、t,sclass） VALUES（20100010,赵斌,191005INSERT INTO Student VALUES（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）请写出插入其余行的插入

8、语句，并运行。（4）多行插入到表中创建存一个表，保存学生的学号、姓名和年龄：CREATETABLEcs_Student学号char（8）,姓名char（8）,年龄smallint插入数据行：INSERTINTOcs_StudentSELECTSno,Sname,SageFROMstudentWhereSdept=（5）检查插入到表中的数据（1）将学生20100001的年龄改为22岁。UPDATEstudentSETSage= 22 WHERESno=（2）将所有学生的年龄增加一岁。UPDATEStudentSETSage=Sage+1（3）填写赵斌同学的管理信息系统课程的成绩UPDATESCS

9、ETGrade= 85WHERESno=ANDCno=（4）将计算机科学系全体学生的成绩加5分UPDATEscSETGrade=Grade+ 5WHERE=（selectSdeptfromstudentwherestudent.Sno=sc.Sno）;（5）请自己完成如下操作将刘晨同学的2号课程成绩修改为80将“20100021”同学的学号修改为“20100025”（6）检查数据是否修改（1）删除学号为201000022的学生记录DELETEFROMStudentWHERESno=（2）删除学号20100001学生的1号课程选课记录将选课信息复制到一个临时表tmpSC中：SELECT*INTO

10、tmpSCFROMSC在tmpSC中执行删除操作：DELETEFROMtmpSCWHERESno=andCno=（3）删除临时表中20100002学生的全部选课记录请自己完成该操作。（4）删除计算机科学系所有学生的选课记录DELETEFROMtmpSCWHERE=（selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno）;（5）删除全部选课记录DELETEFROMtmpSC（6）检查数据是否删除 完成如下查询操作：（1）按指定目标列查询查询学生的详细记录：SELECT*FROMStudent;查询学生的学号、姓名和年龄SELECTSno,Sname,S

11、ageFROMStudent;（2）目标列包含表达式的查询查询全体学生的姓名、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,Year of Birth: ,2004-Sage,LOWER（Sdept）FROMStudent;（3）查询结果集中修改列名称asBIRTH, 2000-SageBIRTHDAY,DEPARTMENT=LOWER（Sdept）FROMStudent;（4）取消重复行查询选修了课程的学生学号：比较ALL和DISTINCT的区别SELECTSnoFROMSC;SELECTDISTINCTSnoFROMSC;（5）简单条件查询查询计算机科学系全体学生的

12、名单SELECTSnameFROMStudent WHERESdept=（6）按范围查询查询年龄在2023岁之间的学生的姓名、系别和年龄SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN 20 AND 23（7）查询属性值属于指定集合的行查询信息系（IS）、数学系（MA）和计算机科学系（CS）学生的姓名和性别SELECTSname,SsexFROMStudentWHERESdeptIN（8）模糊查询查询所有姓刘学生的姓名、学号和性别SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE刘%（9）查询空值查询缺少

13、成绩的学生的学号和相应的课程号SELECTSno,CnoFROMscWHEREGradeisnull;（10）多重条件查询查询计算机科学系年龄在岁以下的学生姓名SELECTSnameFROMstudentWHERESdept=andSage 90（4）完成下面的查询统计每个同学的学号、选课数、平均成绩和最高成绩统计每个班的每门课的选课人数、平均成绩和最高成绩 （1）在WHERE中指定连接条件查询每个参加选课的学生信息及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=S

14、C.Sno查询每一门课的间接先修课SELECT*FROMcoursefirst,coursesecondWHEREfirst.Cpno=second.Cno;SELECTfirst.Cno,second.CpnoFROMcoursefirst,coursesecond（2）在FROM中指定连接条件FROMStudentJOINSCON （Student.Sno=SC.Sno）（3）使用外连接查询查询每个学生信息及其选修课程的情况FROMStudentLEFTOUTERJOINSCON （Student.Sno=SC.Sno）（4）复合条件连接查询查询选修号课程且成绩在分以上的所有学生SELEC

15、TStudent.Sno,SnameFROMStudentjoinSCON （Student.Sno=SC.Sno）/* 连接条件*/WHERESC.Cno=ANDSC.Grade 90;/* 过滤条件*/（5）多表查询查询每个学生的学号、姓名、选修的课程名及成绩SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;（6）完成下列查询查询选修了2号课程的同学的学号和姓名查询各门课程的课程号、课程名称以及选课学生的学号查询选修了数据库系统原理课程的同学

16、的学号和姓名和成绩 （1）由In引出的子查询查询与“刘晨”在同一个系学习的学生SELECTSno,Sname,Sdept FROMStudentWHERESdeptIN（SELECTSdeptFROMStudent WHERESname= （2）由比较运算符引出的子查询找出每个学生超过他选修课程平均成绩的课程号。SELECTSno,CnoFROMSCxWHEREGrade=（SELECTAVG（Grade）FROMSCyWHEREy.Sno=x.Sno）; （3）带修饰符的比较运算符引出的子查询查询其他系中比计算机科学系所有学生年龄都小的学生姓名及年龄。SELECTSname,SageFROM

17、StudentWHERESageALL（SELECTSageFROMStudentWHERESdept=ANDSdept （4）由EXISTS引出的子查询查询所有选修了1号课程的学生姓名SELECTSnameFROMStudentWHEREEXISTS（SELECT*FROMSCWHERESno=Student.SnoANDCno= （1）集合并查询计算机科学系的学生及年龄不大于19岁的学生SELECT*FROMStudentWHERESdept=UNIONSELECT*FROMStudentWHERESage=S_G.Gavg（3）查询每个学生的学号、姓名、选修的课程名及成绩SELECTSno,Sname,Cname,GradeFROMXK_VIEW（4）比较使用视图查询和直接从基表查询的优点