ok数据库实验题-平时作业

《数据库实验题》实实验验一一：：交交互互式式SSQQLL的的使使用用实验要求：1,创建Student数据库，包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下划线表示主键，斜体表示外键)，并插入一定数据。答：createtableStudents(SNOvarchar(100)primarykey,SNAMEvarchar(100)null,SEXvarchar(100)null,BDATEdatetimenull,HEIGHTdecimalnull,DEPARTMENTvarchar(100)null)gocreatetableCourses(CNOvarchar(100)primarykey,CNAMEvarchar(100)null,LHOURintnull,CREDITintnull,SEMESTERvarchar(100)null)goCREATETABLE[dbo].[SC]([SNO]varchar(100)NOTNULL,[CNO]varchar(100)NOTNULL,[GRADE][int]NULL,CONSTRAINT[PK_SC]PRIMARYKEYCLUSTERED([SNO]ASC,[CNO]ASC)WITH(PAD_INDEX=OFF,STATISTICS_NORECOMPUTE=OFF,IGNORE_DUP_KEY=OFF,ALLOW_ROW_LOCKS=ON,ALLOW_PAGE_LOCKS=ON)ON[PRIMARY])ON[PRIMARY]GOALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Courses]FOREIGNKEY([CNO])REFERENCES[dbo].[Courses]([CNO])GOALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Courses]GOALTERTABLE[dbo].[SC]WITHCHECKADDCONSTRAINT[FK_SC_Students]FOREIGNKEY([SNO])REFERENCES[dbo].[Students]([SNO])GOALTERTABLE[dbo].[SC]CHECKCONSTRAINT[FK_SC_Students]2．完成如下的查询要求及更新的要求。（1）查询身高大于1.80m的男生的学号和姓名；答：selectSNO,SNAMEfromStudentswhereHEIGHT1.8（2）查询计算机系秋季所开课程的课程号和学分数；答：selectCNO,CREDITfromCourseswhereSEMESTER='秋季'（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；答：selects.SNAME,SC.CNO,c.CREDIT,SC.GRADEfromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNOwheres.DEPARTMENT='计算机系'ands.SEX='男'andc.SEMESTER='秋季'（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；答：selectdistincts.snamefromStudentss,scwheres.sno=sc.snoands.sex='女'andsc.cnolike'EE%'（5）查询每位学生已选修课程的门数和总平均成绩；答：selectcount(c.CNO)as课程门数,avg(SC.GRADE)as总平均成绩fromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNOgroupbys.SNO（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；答：（7）查询所有课程的成绩都在80分以上的学生的姓名、学号、且按学号升序排列；答：（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；答：（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的姓名；答：（10）查询1984年~1986年出生的学生的姓名,总平均成绩及已修学分数。答：（11）在STUDENT和SC关系中，删去SNO以’01’开关的所有记录。答：deletescwhereSNOlike'%01%'deleteStudentswhereSNOlike'%01%'（12）在ＳＴＵＤＥＮＴ关系中增加以下记录：0409101何平女1987-03-021.620408130向阳男1986-12-111.75答：（13）将课程CS-221的学分数增为３，讲课时数增为６０答：3．补充题：(1)统计各系的男生和女生的人数。答：(2)列出学习过‘编译原理’，‘数据库’或‘体系结构’课程，且这些课程的成绩之一在90分以上的学生的名字。答：(3)列出未修选‘电子技术’课程，但选修了‘数字电路’或‘数字逻辑’课程的学生数。答：(4)按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。答：(5)列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOPn子句)答：4．选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1)修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2)设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。实实验验二二：：数数据据库库的的安安全全和和完完整整性性约约束束实验要求：1．采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。答：createtableCredits(SNOvarchar(100),SumCreditint,NoPassint)2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。答：createviewStudent_Gradeasselects.SNAME,c.CNAME,SC.GRADEfromstudentssinnerjoinSConsc.SNO=s.SNOinnerjoinCoursesconsc.CNO=c.CNO2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。答：createtriggerUpd_CreditonSCforinsertasdeclare@SNOvarchar(100),@CNOvarchar(100),@GRADEint,@NoPassint,@CREDITintselect@SNO=SNO,@CNO=CNO,@GRADE=GRADE,@NoPass=(casewhenGRADE60then1else0end)frominsertedselect@CREDIT=CREDITfromCourseswhereCNO=@CNOupdateCreditssetSumCredit=SumCredit+@CREDIT,NoPass=NoPass+@NoPasswhereSNO=@SNO2）Upd_StuView（Insteadof触发器）要求：当对视图Student_Grade作插入数据项操作时，自动触发Upd_StuView，完成对SC表的插入操作。如：当执行InsertintoStudent_Gradevalues(‘王刚’，’数据库’，54)则触发器完成另一插入操作：InsertintoSCvalues(‘980201’，’CS-110’，54)另外，需要检查当前插入的学生和课程是否已在Students，和Courses表中存在，如不存在，不执行任何操作，并提示用户错误信息。答：createtriggerUpd_StuViewonStudent_Gradeforinsertasdeclare@SNAMEvarchar(100),@CNAMEvarchar(100),@GRADEintselect@SNAME=SNAME,@CNAME=CNAME,@GRADE=GRADEfrominsertedIF(EXISTS(SELECT*FROMStudentsWHERESNAME=@SNAME)ANDEXISTS(SELECT*FROMCoursesWHERECNAME=@CNAME))BEGINInsertintoSCselect(SELECTTOP1SNOFROMStudentsWHERESNAME=@SNAME),(SELECTTOP1CNOFROMCoursesWHERECNAME=@CNAME),@GRADEEND3）PK_SC,FK_SC_SNO,FK_SC_CNO)（选做）要求：首先删除SC中所有主键和外键定义，用触发器实现表SC上的主键（SNO，CNO）和外键SNO，CNO的约束定义。3．为Student数据库设计安全机制。要求：在该数据库系统中，有三类用户：1)学生，权限包括：查询所有的课程信息，根据学号和课程号来查询成绩。但不允许修改任何数据。(必做)只能查询自己的成绩，不能查询别人的成绩。（选做）2）老师：权限包括：查询有关学生及成绩的所有信息，有关课程的所有信息，但不允许修改任何数据。答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseBEGINIF((selectuserfromdual)='老师')THENRAISE_APPLICATION_ERROR(-20506,'您没有权限对学生表进行修改.')3）教务员：权限包括：查询和修改任何有关学生和课程的信息，但不允许查询和修改数据库中其它任何表，视图等数据库对象。答：CREATETRIGGERsecure_studentBEFOREUPDATEORDELETEONdatabaseEXCEPTStudentsBEGINIF((selectuserfromdual)='教务员')THENRAISE_APPLICATION_ERROR(-20506,'您没有权限进行修改.')要求：安全控制必须仅由数据库一端来实现，不考虑由应用程序来控制。为此，需要创建三个用户，登录时密码验证；分别授予各类权限，并测试权限的控制是否有效。实实验验三三：：SSQQLL编编程程实验要求：1．采用实验一的建库脚本和数据插入脚本创建Student数据库。2．在数据库中创建以下存储过程：1)Add_Student(SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT)要求：根据输入参数，插入一条学生记录。答：createprocedureAdd_Student@SNOvarchar(100),@SNAMEvarchar(100),@SEXvarchar(10),@BIRTHDAYdatetime,@HEIGHTdecimal,@DEPTvarchar(100)asinsertintoStudentsvalues(@SNO,SNO,@SNAME,@SEX,@BIRTHDAY,@HEIGHT,@DEPT)2)Upd_Grade(SNO,CNO,GRADE)要求：根据输入参数