数据库实验3答案

实验三：交互式SQL语句的使用1、实验目的（1）掌握数据库对象的操作过程，包括创建、修改、删除（2）熟悉表的各种操作，包括插入、修改、删除、查询（3）熟练掌握常用SQL语句的基本语法2、实验平台使用SQLServer提供的MicrosoftSQLServerManagementStudio工具，交互式使用SQL语句。3实验容及要求选择如下一个应用背景之一：学生选课系统习题3、4、和5中使用的数据库其它你熟悉的应用（1）建立一个数据库和相关的表、索引、视图等数据库对象，练习对表、索引和视图的各种操作。（2）要求认真进行实验，记录各实验用例及执行结果。（3）深入了解各个操作的功能。实验要求包括如下方面的容：数据定义基本表的创建、修改及删除索引的创建视图的创建数据操作完成各类更新操作包括:修改数据删除数据数据查询操作完成各类查询操作单表查询分组统计连接查询嵌套查询集合查询数据操作创建视图视图查询参考示例：建立一个学生选课数据库，练习对表、视图和索引等数据库对象的各种操作。一、数据定义创建学生选课数据库ST，包括三个基本表，其中Student表保存学生基本信息，Course表保存课程信息，SC表保存学生选课信息，其结构如下表：表1.Student表结构列名称用途类型长度约束备注Sno学号字符8主键Sname字符8Ssex性别字符2Sage年龄整型Sdept所在系字符20Sclass班级字符4列名称用途类型长度约束备注Cno课程号字符4主键Cname课程名字符40Cpno先修课程号字符4Ccredit学分整型表3.SC表结构列名称用途类型长度约束备注Sno学号字符8外键Cno课程号字符4Grade成绩整型创建、修改及删除基本表创建Student表CREATETABLEStudent(SnoCHAR(8)PRIMARYKEY,SnameCHAR(8),SsexCHAR(2)NOTNULL,SageINT,SdeptCHAR(20));创建Course表CREATETABLECourse(CnoCHAR(4)PRIMARYKEY,CnameCHAR(40)NOTNULL,CpnoCHAR(4),Ccredit);SMALLINT,创建SC表CREATETABLESC(SnoCHAR(8)FOREIGNKEY(Sno)REFERENCESStudent(Sno),CnoCHAR(4),GradeSMALLINT,);创建员工表EmployeeCREATETABLEEmployee(编号CHAR(8)PRIMARYKEY,VARCHAR(8)notnull部门CHR(40),工资numeric(8,2),生日datetime职称char(20),);指出该语句中的错误并改正后执行。检查表是否创建成功SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSCSELECT*FROMEmployee修改表结构及约束•增加班级列ALTERTABLEStudentADDSclasschar(4)•修改年龄列ALTERTABLEStudentALTERCOLUMNSagesmallint•增加约束ALTERTABLECourseADDUNIQUE(Cname)删除表DROPTABLEEmployee创建索引为Course表按课程名称创建索引CREATEINDEXiCnameOnCourse(Cname)为Student表按学生创建唯一索引CREATEUNIQUEINDEXiSnameONStudent(Sname)为SC表按学号和课程号创建聚集索引CREATECLUSTEREDINDEXiSnoCnoOnSC(Sno,Cnodesc)为Course表按课程号创建唯一索引请自己完成该操作创建视图

建立信息系学生的视图：CREATEVIEWIS_StudentASSELECTSno,Sname,SageFROMStudentWHERESdept='IS';3.2数据操作插入数据将如下表格中的数据分别插入到数据库相应的表中:表6.学生选课信息表学号课程号表6.学生选课信息表学号课程号成绩学号课程号成绩学号性别年龄所在系班级20100001勇男20CS100120100002晨女19CS100120100021王敏女18MA100220100031立男19IS100320100003洋女100120100010斌男19IS100520100022明明男19CS1002表5.课程信息表课程号课程名先修课程号学分1数据库系统原理5642高等数学23管理信息系统144操作系统原理635数据结构746数据处理27C语言42010000119220100001285201000013882010000219020100002280201000031201000103插入到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')插入到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)请写出插入其余行的插入语句，并插入数据。插入到SC表INSERTINTOSCVALUES('20100001','1',92)INSERTINTOSCVALUES('20100002','2',80)INSERTINTOSC(Sno,Cno)VALUES('20100003','1')INSERTINTOSC(Sno,Cno,Grade)VALUES('20100010','3',null)请写出插入其余行的插入语句，并运行。多行插入到表中创建存一个表，保存学生的学号、和年龄：CREATETABLEcs_Student(学号char(8),char(8),年龄smallint);插入数据行：INSERTINTOcs_StudentSELECTSno,Sname,SageFROMstudentWhereSdept='CS';检查插入到表中的数据SELECT*FROMStudentSELECT*FROMCourseSELECT*FROMSC修改数据将学生20100001的年龄改为22岁。UPDATEstudentSETSage=22WHERESno='20100001'；将所有学生的年龄增加一岁。UPDATEStudentSETSage=Sage+1填写斌同学的管理信息系统课程的成绩UPDATESCSETGrade=85WHERESno='20100010'ANDCno='3'将计算机科学系全体学生的成绩加5分UPDATEscSETGrade=Grade+5WHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=sc.Sno)；请自己完成如下操作•将晨同学的2号课程成绩修改为80•将“20100021”同学的学号修改为“20100025”检查数据是否修改删除数据删除学号为201000022的学生记录DELETEFROMStudentWHERESno='20100022'删除学号20100001学生的1号课程选课记录将选课信息复制到一个临时表tmpSC中：在tmpSC中执行删除操作：DELETEFROMtmpSCWHERESno='20100001'andCno='1删除临时表中20100002学生的全部选课记录请自己完成该操作。删除计算机科学系所有学生的选课记录DELETEFROMtmpSCWHERE'CS'=(selectSdeptfromstudentwherestudent.Sno=tmpSC.Sno);删除全部选课记录DELETEFROMtmpSC检查数据是否删除3.3数据查询操作完成如下查询操作：单表查询按指定目标列查询•查询学生的详细记录：SELECT*FROMStudent；•查询学生的学号、和年龄SELECTSno,Sname,SageFROMStudent；目标列包含表达式的查询•查询全体学生的、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth：',2004-Sage,LOWER(Sdept)FROMStudent；查询结果集中修改列名称•查询全体学生的、出生年份和所有系，要求用小写字母表示所有系名。SELECTSname,'YearofBirth：'asBIRTH,2000-SageBIRTHDAY,DEPARTMENT=LOWER(Sdept)FROMStudent；取消重复行•查询选修了课程的学生学号：比较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岁之间的学生的、系别和年龄分组统计（1）聚集函数的使用查询学生总人数SELECTCOUNT(*)FROMStudent;查询选修了课程的学生人数SELECTCOUNT(DISTINCTSno)FROMSC查询最高分SELECTMAX(Grade)FROMSC聚集函数作用于部分行统计2号课程的总分、均分和最高分SELECTSUM(grade)总分,AVG(grade)均分,MAX(grade)最高分FROMscWHERECno='2'分组统计统计各门课程的选课人数、均分和最高分selecto课程号，count(*)人数,AVG(grade)均分,MAX(grade)最高分fromscgroupbyCno统计均分大于90的课程selecto课程号，count(*)人数,AVG(grade)均分,MAX(grade)最高分fromscgroupbyCnohavingAVG(grade)>90完成下面的查询统计每个同学的学号、选课数、平均成绩和最高成绩统计每个班的每门课的选课人数、平均成绩和最高成绩连接查询在WHERE中指定连接条件查询每个参加选课的学生信息及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudent,SCWHEREStudent.Sno=SC.Sno查询每一门课的间接先修课SELECT*FROMcoursefirst,coursesecondWHEREfirst.Cpno=second.Cno;SELECTfirst.Cno,second.CpnoFROMcoursefirst,coursesecondWHEREfirst.Cpno=second.Cno;在FROM中指定连接条件查询每个参加选课的学生信息及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentJOINSCON(Student.Sno=SC.Sno)使用外连接查询•查询每个学生信息及其选修课程的情况SELECTStudent.Sno,Sname,Ssex,Sage,Sdept,Cno,GradeFROMStudentLEFTOUTERJOINSCON(Student.Sno=SC.Sno)复合条件连接查询查询选修号课程且成绩在分以上的所有学生SELECTStudent.Sno,SnameFROMStudentjoinSCON(Student.Sno=SC.Sno)/*连接条件*/WHERESC.Cno='2'ANDSC.Grade>90;/*过滤条件*/多表查询查询每个学生的学号、、选修的课程名及成绩SELECTStudent.Sno,Sname,Cname,GradeFROMStudent,SC,CourseWHEREStudent.Sno=SC.SnoANDSC.Cno=Course.Cno;完成下列查询查询选修了2号课程的同学的学号和查询各门课程的课程号、课程名称以及选课学生的学号查询选修了数据库系统原理课程的同学的学号和和成绩嵌套查询由In引出的子查询查询与“晨''在同一个系学习的学生SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN(SELECTSdeptFROMStudentWHERESname='晨');由比较运算符引出的子查询找出每个学生超过他选修课程平均成绩的课程号。SELECTSno,CnoFROMSCxWHEREGrade>=(SELECTAVG(Grade)FROMSCyWHEREy.Sno=x.Sno)；带修饰符的比较运算符引出的子查询查询其他系中比计算机科学系所有学生年龄都小的学生及年龄。SELECTSname,SageFROMStudentWHERESage<ALL(SELECTSageFROMStudentWHERESdept='CS')ANDSdept<>'CS'；由EXISTS引出的子查询查询所有选修了1号课程的学生SELECTSnameFROMStudentWHEREEXISTS(SELECT*FROMSCWHERESno=Student.SnoANDCno='1')集合查询集合并查询计算机科学系的学生及年龄不大于19岁的学生SELECT*FROMStudentWHERESdept='CS'UNIONS