《华工数据库实验题_(2).doc》由会员分享，可在线阅读，更多相关《华工数据库实验题_(2).doc（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、 .1,创建Student数据库，包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下划线表示主键，斜体表示外键)，并插入一定数据。答：createtable Students(SNO varchar(100)primarykey,SNAME varchar(100)null,SEX varchar(100)null,BDATE datetimenull,HEIGHT dec
2、imalnull,DEPARTMENT varchar(100)null)gocreatetable Courses(CNO varchar(100)primarykey,CNAME varchar(100)null,LHOUR intnull,CREDIT intnull,SEMESTER varchar(100)null)goCREATETABLE dbo.SC(SNO varchar(100)NOTNULL,CNO varchar(100)NOTNULL,GRADE int NULL,CONSTRAINT PK_SC PRIMARYKEYCLUSTERED(SNO ASC,CNO ASC
3、)WITH(PAD_INDEX =OFF, STATISTICS_NORECOMPUTE =OFF, IGNORE_DUP_KEY =OFF, ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS =ON)ON PRIMARY)ON PRIMARYGOALTERTABLE dbo.SC WITHCHECKADDCONSTRAINT FK_SC_Courses FOREIGNKEY(CNO)REFERENCES dbo.Courses (CNO)GOALTERTABLE dbo.SC CHECKCONSTRAINT FK_SC_CoursesGOALTERTABLE dbo
4、.SC WITHCHECKADDCONSTRAINT FK_SC_Students FOREIGNKEY(SNO)REFERENCES dbo.Students (SNO)GOALTERTABLE dbo.SC CHECKCONSTRAINT FK_SC_Students2完成如下的查询要求与更新的要求。（1）查询身高大于1.80m的男生的学号和；答：select SNO,SNAME from Studentswhere HEIGHT1.8（2）查询计算机系秋季所开课程的课程号和学分数；答：select CNO,CREDIT from Courses where SEMESTER=秋季（3）查
5、询选修计算机系秋季所开课程的男生的、课程号、学分数、成绩；答：select s.SNAME,SCO,c.CREDIT,SC.GRADE from students sinnerjoin SC on sc.SNO=s.SNOinnerjoin Courses c on scO=cOwhere s.DEPARTMENT=计算机系and s.SEX=男and c.SEMESTER=秋季（4）查询至少选修一门电机系课程的女生的（假设电机系课程的课程号以EE开头）；答：selectdistinct s.sname from Students s,sc where s.sno=sc.sno and s.s
6、ex=女and sco likeEE%（5）查询每位学生已选修课程的门数和总平均成绩；答：selectcount(cO)as 课程门数,avg(SC.GRADE)as 总平均成绩from students sinnerjoin SC on sc.SNO=s.SNOinnerjoin Courses c on scO=cOgroupby s.SNO（6）查询每门课程选课的学生人数,最高成绩,最低成绩和平均成绩；答：selectame,count(cno),max(grade),min(grade),avg(grade)from students natural join sc natural j
7、oin coursesgroupby chane;（7）查询所有课程的成绩都在80分以上的学生的、学号、且按学号升序排列；答：select sname,sno from students natural join(select sno.min(grade)as mini from sc groupby sno)where mini80 orderby(sno);（8）查询缺成绩的学生的，缺成绩的课程号与其学分数；答：select sname,courseso,creditfrom students,courses,scwhere students.sno=sc.sno and courseso
8、=sco and grade isnull;（9）查询有一门以上(含一门)三个学分以上课程的成绩低于70分的学生的；答：select sname from students,courses,scwhere students.sno=sc.sno and courseso=sc.cho and credit=3 and grade70;（10）查询1984年1986年出生的学生的,总平均成绩与已修学分数。答：select smane,avg(grade),sun(credit)from students natural join sc natural join courseswhere bdat
9、e between1984-00-00and1987-00-00groupby sname;（11） 在STUDENT和SC关系中，删去SNO以01开关的所有记录。答：delete sc where SNO like%01%delete Students where SNO like%01%（12）在关系中增加以下记录：答：insertinto students values(0409101,何平,女,1987-03-02,1.62,)insertinto students values(0408130,向阳,男,1986-12-11,1.75,)（13）将课程CS-221的学分数增为，讲课时
10、数增为答：update courses set credit=3 whereo=GS-221update courses set credit=60 whereo=GS-2213补充题：(1) 统计各系的男生和女生的人数。答：select department,sum(case wgen sex=男then 1 else 0 end),sum(case wgen sex=女then 1 else 0 end),count(sno)from students groupby department orderby department;(2) 列出学习过编译原理，数据库或体系结构课程，且这些课程的
11、成绩之一在90分以上的学生的名字。答：select snamefrom students natural join sc natural join courseswhereame=编译原理orame=数据库orame=体系结构and grade90;(3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。答：selectcount(distinci sc.sno)from courses,sc.sno notin(select sc.sno from courseso=sco andame=电子技术)and sco in(selecto from courses wheream
12、e=数字逻辑orame=数字电路)(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。答：selectdistinct courseso,coursesame,sno,grade from courses leftjoin sc on(courseso-sco)groupby courseso,coursesame,sno,gradeorderby courseso,coursesame,sno,grade;(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)答：select sname,rfrom(select
13、sname,avg(grade)asfrom students,sc where students.sno=sc.sno groupby sname,students.sno orderby r desc)where rownum=1;4选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之
14、前用INSERT语句插入表中。实验二：数据库的安全和完整性约束实验要求：1采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以与不与格的课程数。答：createtable Credits(SNO varchar(100),SumCredit int,NoPass int)2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程与成绩的详细信息。答：createview Student_Gradeasselect s.SNAM
15、E,cAME,SC.GRADE from students sinnerjoin SC on sc.SNO=s.SNOinnerjoin Courses c on scO=cO2. 在数据库中创建以下触发器：1） Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不与格的课程数。答：createtrigger Upd_Credit on SC forinsertasdeclare SNO varchar(100),CNO varchar(100),GRADE int,NoPass int,CREDIT ints
16、elect SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(casewhen GRADE3groupby s.SNO4) CAL_GPA (SNO，GPA output)要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下：GRADE(G) GRADEPOINT(GP)G=85 485G=75 375G=60 260G 1GPA= (GP*CREDIT)/ CREDIT)答：createprocedure CAL_GPASNO varchar(100),GPA decimaloutputasdeclare SUM_CREDIT int,AVG_GRADE i
17、nt,All_CREDIT intselect SUM_CREDIT=SumCredit from Creditswhere SNO=SNOselect AVG_GRADE=avg(casewhen SC.GRADE=85 then 4 when 85SC.GRADE and SC.GRADE=75 then 3 when 75SC.GRADE and SC.GRADE=60 then 2 when 60SC.GRADE then 1 end)from Students sinnerjoin SC on sc.SNO=s.SNOwhere s.SNO=SNO and SC.GRADE3grou
18、pby s.SNOselect GPA=(AVG_GRADE*SUM_CREDIT)/All_CREDIT3选做题： 使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。 要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO，与显示查询的结果。(如采用VC+，VB等)实验四：事务的管理（选作）实验要求：1 采用实验一的建库脚本和数据插入脚本创建Student数据库。2 测试事务隔离级别，要求： 分别设置不同的隔离级别,包括：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL READ ONLY;两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以与幻象四种情况。3备份与恢复 备份数据库答：BACKUPDATABASE testTOdisk=c:test 删除sc表答：DROPTABLE SC 恢复到删除之前答: USEmasterGORESTOREDATABASE test_wtFROMdisk=c:test_wtGO9 / 9