华工数据库实验题.docx

1、华工数据库实验题1,创建Student数据库，包括Students,Courses,SC表，表结构如下：Students(SNO,SNAME,SEX,BDATE,HEIGHT,DEPARTMENT)Courses(CNO,CNAME,LHOUR,CREDIT,SEMESTER)SC(SNO,CNO,GRADE)(注：下划线表示主键，斜体表示外键)，并插入一定数据。答：create table Students(SNO varchar(100) primary key ,SNAME varchar(100) null,SEX varchar(100) null,BDATE datetime nu

2、ll,HEIGHT decimal null,DEPARTMENT varchar(100) null)gocreate table Courses(CNO varchar(100) primary key ,CNAME varchar(100) null,LHOUR int null,CREDIT int null,SEMESTER varchar(100) null)goCREATE TABLE dbo.SC( SNO varchar(100) NOT NULL, CNO varchar(100) NOT NULL, GRADE int NULL, CONSTRAINT PK_SC PRI

3、MARY KEY CLUSTERED ( 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 PRIMARYGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Courses FOREIGN KEY(CNO)REFERENCES dbo.Courses (CNO)GOALTER TABLE d

4、bo.SC CHECK CONSTRAINT FK_SC_CoursesGOALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Students FOREIGN KEY(SNO)REFERENCES dbo.Students (SNO)GOALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Students2完成如下的查询要求及更新的要求。（1）查询身高大于1.80m的男生的学号和姓名；答：select SNO,SNAME from Students where HEIGHT1.8（2）查询计算机系秋季所开课程的

5、课程号和学分数；答：select CNO,CREDIT from Courses where SEMESTER=秋季（3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；答：select s.SNAME,SC.CNO,c.CREDIT,SC.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNOwhere s.DEPARTMENT=计算机系 and s.SEX=男 and c.SEMESTER=秋季（4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课

6、程号以EE开头）；答：select distinct s.sname from Students s,sc where s.sno=sc.sno and s.sex=女 and o like EE%(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 group by department order by department;(2) 列出学习过编译原理，数据库或体系

7、结构课程，且这些课程的成绩之一在90分以上的学生的名字。答：select snamefrom students natural join sc natural join courseswhere cname=编译原理 or cname=数据库 or cname=体系结构 and grade90;(3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。答：select count(distinci sc.sno) from courses,sc.sno not in(select sc.sno from o=o and cname=电子技术)and o in (select cn

8、o from courses where cname=数字逻辑 or cname=数字电路)(4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用NULL表示。答：select distinct o,ame,sno,grade from courses left join sc on (o-o)group by o,ame,sno,gradeorder by o,ame,sno,grade;(5) 列出平均成绩最高的学生名字和成绩。(SELECT句中不得使用TOP n子句)答：select sname,rfrom(select sname,avg(grade)a

9、s from students,sc where students.sno=sc.sno group by sname,students.sno order by r desc)where rownum=1;4选做题：对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。)注意：须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中

10、。实验二：数据库的安全和完整性约束实验要求：1采用实验一的建库脚本和数据插入脚本创建Student数据库,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。答：create table Credits(SNO varchar(100),SumCredit int,NoPass int)2）创建视图Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。答：create view Student_Gradeas select s.SNAME,c.CNAME,S

11、C.GRADE from students sinner join SC on sc.SNO=s.SNOinner join Courses c on sc.CNO=c.CNO2.在数据库中创建以下触发器：1）Upd_Credit要求：当在SC表中插入一条选课成绩，自动触发Upd_Credit，完成在Credits表中修改该学生的合计学分数和不及格的课程数。答：create trigger Upd_Credit on SC for insertas declare SNO varchar(100),CNO varchar(100),GRADE int ,NoPass int ,CREDIT i

12、nt select SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(case when GRADE3group by s.SNO4)CAL_GPA (SNO，GPA output)要求：根据SNO参数, 输出并显示该学生的GPA值。计算方法如下：GRADE(G) GRADEPOINT(GP)G=85 485G=75 375G=60 260G 1GPA= (GP*CREDIT)/ CREDIT)答：create procedure CAL_GPASNO varchar(100),GPA decimal outputasdeclare SUM_CREDIT int,AVG_

13、GRADE int,All_CREDIT intselect SUM_CREDIT=SumCredit from Creditswhere SNO=SNOselect AVG_GRADE=avg(case when 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 sinner join SC on sc.SNO=s.SNOwhere s.SNO=SNO and SC.

14、GRADE3group by 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备份与恢复备份数据库答：BACKUP DATABASE testTO disk = c:test删除sc表答：DROP TABLE SC恢复到删除之前答: USE masterGORESTORE DATABASE test_wtFROM disk = c:test_wtGO