2022年数据库实验题 .pdf

数据库实验题实验一：交互式 SQ L 的使用实验要求 ：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 ,SNAME varchar(100)null,SEXvarchar(100)null,BDATEdatetimenull,HEIGHTdecimalnull,DEPARTMENTvarchar(100)null)go createtableCourses(CNOvarchar(100)primarykey ,CNAME varchar(100)null,LHOUR intnull,CREDITintnull,SEMESTER varchar(100)null)go CREATE TABLEdbo.SC(SNO varchar(100)NOTNULL,CNO varchar(100)NOTNULL,GRADEintNULL,CONSTRAINT PK_SC PRIMARY KEYCLUSTERED(SNO ASC,CNO ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE= OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS= ON, ALLOW_PAGE_LOCKS= ON) ONPRIMARY) ONPRIMARYGO ALTERTABLEdbo.SCWITHCHECK ADD CONSTRAINT FK_SC_Courses名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 1 页，共 9 页 - - - - - - - - - FOREIGN KEY(CNO)REFERENCESdbo.Courses(CNO)GOALTERTABLEdbo.SCCHECK CONSTRAINT FK_SC_CoursesGOALTERTABLEdbo.SCWITHCHECK ADD CONSTRAINT FK_SC_StudentsFOREIGNKEY(SNO)REFERENCESdbo.Students(SNO)GOALTERTABLEdbo.SCCHECK CONSTRAINT FK_SC_Students 2完成如下的查询要求及更新的要求。（1）查询身高大于 1.80m的男生的学号和姓名；答：selectSNO,SNAME from Studentswhere HEIGHT1.8 （2）查询计算机系秋季所开课程的课程号和学分数；答：selectCNO,CREDIT from Courses where SEMESTER=秋季 （3）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；答：selects.SNAME,SC.CNO,c.CREDIT,SC.GRADEfrom studentssinnerjoinSC on sc.SNO=s.SNO innerjoinCourses c on sc.CNO=c.CNO （4）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头）；答：selectdistincts.sname from Studentss,sc where s.sno=sc.snoand s.sex=女 olikeEE% （5）查询每位学生已选修课程的门数和总平均成绩；答 ： selectcount(c.CNO)as课 程 门 数 ,avg(SC.GRADE) as总 平 均 成 绩from studentssinnerjoinSC on sc.SNO=s.SNOinnerjoinCourses c on sc.CNO=c.CNOgroup by s.SNO （6）查询每门课程选课的学生人数, 最高成绩 , 最低成绩和平均成绩；答：SELECT CNAME,COUNT,MAX,MIN,AVG FROM STUDENTS NATURAL JOIN SC NATURAL JOIN COURSES GROUP BY CNAME; （7）查询所有课程的成绩都在80 分以上的学生的姓名、 学号、且按学号升序排列；答：SELECT SNAME,SNO FROM STUDENTS NATURAL JOIN 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 2 页，共 9 页 - - - - - - - - - SELECT SNO.MIN AS MINI FROM SC GROUP BY SNO WHERE MINI80 ORDER BY; （8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；答：SELECT SNAME COURSES.CNO,CREDIT FROM STUDENTS,COURSES,SC WHERE STUDENTS.SNO=SC.SNO AND COURSES.CNO AND GRADE IS NULL; （9）查询有一门以上 (含一门 ) 三个学分以上课程的成绩低于70 分的学生的姓名；答：SELECT SNAME FROM STUDENTS,COURSES,SC WHERE STUDENTS.SNO=SC.SNO AND CREDIT=3 AND GREAD70 （10）查询 1984 年1986年出生的学生的姓名 , 总平均成绩及已修学分数。答：select sname,avg(grade),sum(credit) from students natural join sc natural join courses where bdate between 1984-00-00 and 1987-00-00 groud by sname （11） 在 STUDENT 和 SC关系中，删去 SNO 以01开关的所有记录。答：deletesc where SNOlike%01% deleteStudentswhere SNOlike%01% （12）在关系中增加以下记录： 答：insert into students values insert into students values （13）将课程 CS-221的学分数增为，讲课时数增为答：update courses set credit=3 where cno=CS-211update courses Set 1hour=60 where cno=CS-211名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 3 页，共 9 页 - - - - - - - - - 3补充题：(1) 统计各系的男生和女生的人数。答：SELECT DEPARTMENT, SUM, SUM, COUNT 总人数FROM Students GROUP BY DEPARTMENT ORDER BY DEPARTMENT (2) 列出学习过编译原理，数据库或体系结构课程，且这些课程的成绩之一在 90 分以上的学生的名字。答：select sname From students natural join sc natural join courses Where cname= 编辑原理 or cname= 体系结构 and grade90 (3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。答：SELECT COUNT FROM Courses,SC WHERE SC.SNO NOT IN SELECT SC.SNO FROM Courses,SC WHERE Courses.CNO=SC.CNO AND CNAME=电子技术 AND Courses.CNO=SC.CNO AND SC.CON IN (4) 按课程排序列出所有学生的成绩，尚无学生选修的课程，也需要列出，相关的学生成绩用 NULL表示。答：SELECT DISTINCT COURSES.CNO,COURSES.CNAME,SNO,GRADE FROM COURSES LEFT JOIN SC ON GROUP BY COURSES.CNO,COURSES.CNAME,SNO,GRADE ORDER BY COURSES.CNO,COURSES.CNAME,SNO,GRADE (5) 列出平均成绩最高的学生名字和成绩。(SELECT 句中不得使用 TOP n子句) 答：SELECT SNAME,r FROM SELECT SNAME,AVG AS FROM STUDENTS,SC 名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 4 页，共 9 页 - - - - - - - - - WHERE STUDENTS.SNO=SC.SNO GROUP BY SNAME,STUDENTS.SNC ORDER BY r DESC WHERE ROWNUM=1; 4选做题： 对每门课增加 ?先修课程 ?的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：列出有资格选修数据库课程的所有学生。( 该学生已经选修过数据库课程的所有先修课，并达到合格成绩。) 注意： 须设计每个查询的测试数据，并在查询之前用INSERT语句插入表中。答：（ 1）create table Credits (SNO varchar(100),SumCredit int, NoPass int) （2）create view Student_Gradeas Select s.SNAME,c.CNAME,SC.GRADE from students sInner join SC onsc.SNO=s.SNOInner join Courses c on sc.CNO=c.CNO 实验二：数据库的安全和完整性约束实验要求 ：1采用实验一的建库脚本和数据插入脚本创建Student数据库 , 并完成以下操作：1）新增表 Credits(SNO,SumCredit,NoPass) ，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。2）创建视图 Student_Grade(Sname,Cname,Grade)，表示学生选修课程及成绩的详细信息。2.在数据库中创建以下触发器：1）Upd_Credit 要求： 当在 SC表中插入一条选课成绩， 自动触发 Upd_Credit ， 完成在 Credits表中修改该学生的合计学分数和不及格的课程数。2）Upd_StuView （Instead of触发器）要 求 ： 当 对 视 图Student_Grade作 插 入 数 据 项 操 作 时 ， 自 动 触 发Upd_StuView，完成对 SC表的插入操作。如：当执行 Insert into Student_Grade values(王刚，数据库，54) 则触发器完成另一插入操作： Insert into SC values(980201 ， CS-110 ，54) 另外，需要检查当前插入的学生和课程是否已在Students ，和 Courses 表中存在，如不存在，不执行任何操作，并提示用户错误信息。名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 5 页，共 9 页 - - - - - - - - - 3）PK_SC,FK_SC_SNO,FK_SC_CNO) （选做）要求：首先删除SC 中所有主键和外键定义，用触发器实现表SC上的主键（SNO ，CNO ）和外键 SNO ，CNO 的约束定义。答：（1）create trigger Upd_Credit on SC for insertas Declare SNO varchar(100),CNO varchar(100),GRADEint,NoPass int ,CREDIT intselectSNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(casewhen GRADE=85 4 85G=75 3 75G=60 2 60G 1 GPA= (GP*CREDIT)/ CREDIT) 答：1）createprocedureAdd_StudentSNO varchar(100),名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 7 页，共 9 页 - - - - - - - - - SNAME varchar(100),SEX varchar(10),BIRTHDAY datetime,HEIGHT decimal,DEPT varchar(100)asInsert intoStudents values(SNO, SNO,SNAME,SEX,BIRTHDAY,HEIGHT,DEPT2）createprocedureUpd_GradeSNO varchar(100),CNO varchar(100),GRADE INTasUPDATE SC setSNO=SNO,CNO=CNO,GRADE=GRADEwhere SNO=SNOand CNO=CNO 3）createprocedureUpd_GradeSNO varchar(100),SUM_CREDITINT output,AVG_GRADEintoutputasselect* from StudentssinnerjoinSC on sc.SNO=s.SNOinnerjoinCourses c on sc.CNO=c.CNOwhere s.SNO=SNOselectAVG_GRADE=avg(casewhen SC.GRADE=85 then 4 when 85SC.GRADEand SC.GRADE=75 then 3 when 75SC.GRADE and SC. GRADE=60 then 2 when 60SC.GRADE then 1 end) from StudentssinnerjoinSC on sc.SNO=s.SNOwhere s.SNO=SNOand SC.GRADE3group by s.SNO名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 8 页，共 9 页 - - - - - - - - - 3选做题：使用其它程序设计语言编程，连接数据库并提交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备份与恢复备份数据库删除 sc 表恢复到删除之前名师资料总结 - - -精品资料欢迎下载 - - - - - - - - - - - - - - - - - - 名师精心整理 - - - - - - - 第 9 页，共 9 页 - - - - - - - - -