数据库习题

《网络数据库SQL》自检自测题答案有一个“学生－课程”数据库，数据库中包括三个表：(1)“学生”表Student由学号（Sno）、姓名（Sname）、性别（Ssex）、年龄（Sage）、所在系（Sdept）五个属性组成，可记为：Student(Sno,Sname,Ssex,Sage,Sdept)Sno为关键字。(2)“课程”表Course由课程号（Cno）、课程名（Cname）、先修课号（Cpno）、学分（Ccredit）四个属性组成，可记为：Course(Cno,Cname,Cpno,Ccredit)Cno为关键字。(3)“学生选课”表SC由学号（Sno）、课程号（Cno）、成绩（Grade）三个属性组成，可记为：SC(Sno,Cno,Grade)(SNO,CNO)为关键字。完成下列操作：1、建立一个“学生”表Student，它由学号Sno、姓名Sname、性别Ssex、年龄Sage、所在系Sdept五个属性组成，其中学号属性不能为空，并且其值是唯一的。CREATETABLEStudent(SnoCHAR(5)NOTNULLUNIQUE,SnameCHAR(20),SsexCHAR(1),SageINT,SdeptCHAR(15));2、向Student表增加“入学时间”列，其数据类型为日期型。ALTERTABLEStudentADDScomeDATE;3、删除Student表DROPTABLEStudent；4、查询全体学生的学号与姓名SELECTSno,SnameFROMStudent;5、查询全体学生的详细记录SELECT*FROMStudent;6、查所有选修过课的学生的学号SELECTDISTINCTSnoFROMSC;7、查所有年龄在20岁以下的学生姓名及其年龄SELECTSname,SageFROMStudentWHERESage20;8、查考试成绩有不及格的学生的学号SELECTDISTINCTSnoFROMCourseWHEREGrade60;9、查询年龄在20至23岁之间的学生的姓名、系别、和年龄SELECTSname,Sdept,SageFROMStudentWHERESageBETWEEN20AND23;10、查所有姓刘的学生的姓名、学号和性别SELECTSname,Sno,SsexFROMStudentWHERESnameLIKE'刘%';11、查姓“欧阳”且全名为三个汉字的学生的姓名SELECTSnameFROMStudentWHERESnameLIKE'欧阳__';12、查询选修了3号课程的学生的学号及其成绩，查询结果按分数的降序排列SELECTSno,GradeFROMSCWHERECno='3'ORDERBYGradeDESC;13、计算1号课程的学生平均成绩SELECTAVG(Grade)FROMSCWHERECno='1';14、查询学习1号课程的学生最高分数SELECTMAX(Grade)FROMSCWHERECno='1';15、查询与“刘晨”在同一个系学习的学生SELECTSno,Sname,SdeptFROMStudentWHERESdeptIN（SELECTSdeptFROMStudentWHERESname='刘晨'）;16、将一个新学生记录（学号：95020；姓名：陈冬；性别：男；所在系：IS；年龄：18岁）插入Student表中INSERTINTOStudentVALUES('95020','陈冬','男','IS',18);17、将学生95001的年龄改为22岁UPDATEStudentSETSage=22WHERESno='95001';18、将计算机科学系全体学生的成绩置零UPDATESCSETGrade=0WHERE‘CS’=(SELECTSdeptFROMStudentWHEREStudent.Sno=SC.Sno)；19、删除学号为95019的学生记录DELETEFROMStudentWHERESno=‘95019’;20、删除计算机科学系所有学生的选课记录DELETEFROMSCWHERE'CS'=(SELETESdeptFROMStudentWHEREStudent.Sno=SC.Sno);21、创建一存储过程getDetailByName，通过输入参数学生姓名（如“章山），筛选出该学生的基本信息,对不存在此学生姓名的输入值，必须作一检测，打印信息“不存在此学生”。（1）创建：CreateproceduregetDetailByName@namenvarchar(10)ASIF(SELECTCOUNT(*)FROMStudentWHERESname=@Name)0SELECT*FROMStudentWhereSname=@NameELSESELECT警示='不存在姓名为'+@Name+'的学生资料'22、创建Student表按学号升序建立唯一索引CreateUniqueIndexStusnoonStudent（Sno）23、创建Course表按课程号升序建立唯一索引CreateUniqueIndexCoucnoonCourse（Cno）24、创建SC表按学号升序和课程号降序建立唯一索引CreateUniqueIndexScnoonSC（SnoASC，CnoDESC）25、建立信息系学生的视图CreatViewIS_StudentASSelectSno,Sname,SageFromStudentWhereSdept=”IS”26、建立所有女生记录的视图CreatViewF_Student(stdnum,name,sex,age,dept)ASSelect*FromStudentWhereSsex=”女”27、创建了一个名为stu_cou数据库，该数据库的主数据文件逻辑名称为“stu_cou_data”，物理文件名为“stu_cou.mdf”，路径为D:\sql,初始大小为10MB，最大尺寸为无限大，增长速度为10%；数据库的日志文件逻辑名称为“stu_cou_log”，物理文件名为“stu_cou.ldf”，路径为D:\sql,初始大小为1MB，最大尺寸为5MB，增长速度为1MB。Createdatabasestu_couonprimary(name=stu_cou_data,filename='d:\sql\stu_cou.mdf',size=10,maxsize=unlimited,filegrowth=10%)logon(name=stu_cou_log,filename='d:\sql\stu_cou.ldf',size=1,maxsize=5,filegrowth=1)28、使用sp_addumpdevice创建一个备份设备stucbac，这是一个磁盘文件，其物理名称为“D:\sql\stuc.bak”，将stu_cou数据库备份到该设备上。Execsp_addumpdevice‘disk’,’stucbac’,’D:\sql\stuc.bak’BackupDatabasestu_coutostucbac29、查询选修了课程名为“信息系统”的学生学号和姓名selectSno,Snamefromstudentwheresnoin(selectSnofromSCwhereCnoin(selectCnofromCoursewhereCname=’信息系统’))或者selectStudent.Sno,SnamefromStudent,SC,CoursewhereStudent.Sno=SC.SnoandSC.Con=Course.CnoandCourse.Cname=‘信息系统’30、查询其他系中比IS系任一学生年龄小的学生名单。SelectSanme,SageFromWhereSageANY(SelectSagefromStudentwhereSdept=’IS’)andSdept’IS’31、查询student表中的所有信息，将查询结果保存到当前数据库中的新数据表re_stu中。Select*intore_stufromstudent32、查询出所有所有学生的学号、姓名、性别、年龄、所在系，而且请使用中文作为查询结果的各字段的名称。SelectSnoas学号,Snameas姓名,Ssexas性别，Sageas年龄,Sdeptas所在系FromStudent或者Select学号=Sno,姓名=Sname,性别=Ssex，年龄=Sage,所在系=SdeptFromStudent33、在表student中增加一个缺省约束，当向student表插入一行数据时，如果没有为性别（Ssex）列提供数据，那么自动插入“女”值。AltertablestudentAddCONSTRAINTdefault_student_sexDEFAULT‘男’forsex34、定义一个检查约束，确保在性别（Ssex）列中只能接受性别数据（‘男’和‘女’）AltertablestudentAddCONSTRAINTCheck_student_sexCHECK(sex=‘男’orsex=‘女’)35、在student表中增加一个主键约束，指定学号（Sno）为主键值，并且还创建一个聚簇索引。AltertablestudentAddCONSTRAINTPK_student_snoPRIMARYKEYCLUSTERED(sno)36、定义一个外键约束，把学生选课表（S_C）中的Sno列和学生表（Student）中的Sno列关联起来。AltertableS_CAddCONSTRAINTS_C_studentFOREIGNKEY(sno)REFERENCESstudent(sno)PRIMARYKEY约束例如，下面的SQL语句创建一个名为student的表，其中指定student_number为主键：USEtestGOCREATETABLEstudent(sutdent_numberintPRIMARYKEY,student_namechar(30))GO2FOREIGNKEY约束例如，下面就是一个使用FOREIGNKEY约束的例子：CREATETABLEproduct(product_numberint,student_numberintFOREIGNKEYREFERENCESstudent(student_number)ONDELETENOACTION)GO3UNIQUE约束例如，下面的SQL语句创建了一个test2表，其中指定了c1字段不能包含重复的值：USEtestGOCREATETABLEtest2(c1intUNIQUE,c2int)GOINSERTtest2VALUES(1,100)GO如果再插入一行：INSERTtest2VALUES(1,200)4CHECK约束例如，下面的SQL语句创建一个成绩（score）表，其中使用CHECK约束来限定成绩只能在0～100分之间：CREATETABLEscore(sutdent_numberint,scoreintNOTNULLCHECK(score=0ANDscore默认值.1在创建表时指定默认值例如，下面在test数据库中创建一个datetest表，其中c2指定默认值未当前日期：USEtestGOCREATETABLEdatetest(c1int,c2datetimeDEFAULT(getdate()))然后插入一行数据：INSERTdatetest(c1)VALUES(1)SELECT*FROMdatetest同样，可以给bookdb数据库中的orderform表中的order_date字段加上默认值：USEbookdbGOALTERTABLEorderformADDCONSTRAINTDateDfltDEFAULTgetdate()FORorder_date.2使用默认对象.2.1创建默认对象2．使用CREATEDEFAULT语句例如，使用下面的SQL语句也可以创建address_default默认对象：