2022年数据库第二次作业.pdf

《2022年数据库第二次作业.pdf》由会员分享，可在线阅读，更多相关《2022年数据库第二次作业.pdf（12页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、数据库第二次作业 上机实验题实验一：交互式 SQL的使用实验要求 ：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

2、varchar(100) null, BDATE datetime null, HEIGHT decimal null, DEPARTMENT varchar(100) null ) go create table Courses ( CNO varchar(100) primary key, CNAME varchar(100) null, LHOUR int null, CREDIT int null, SEMESTER varchar(100) null ) go 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - -

3、- - -第 1 页，共 12 页 - - - - - - - - - - CREATE TABLE dbo.SC( SNO varchar(100) NOT NULL, CNO varchar(100) NOT NULL, GRADE int NULL, CONSTRAINT PK_SC PRIMARY KEY CLUSTERED ( SNO ASC, CNO ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =

4、ON) ON PRIMARY) ON PRIMARY GO ALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Courses FOREIGN KEY(CNO) REFERENCES dbo.Courses(CNO) GO ALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Courses GO ALTER TABLE dbo.SC WITH CHECK ADD CONSTRAINT FK_SC_Students FOREIGN KEY(SNO) REFERENCES dbo.Students (SNO) GO

5、ALTER TABLE dbo.SC CHECK CONSTRAINT FK_SC_Students2完成如下的查询要求及更新的要求。（1）查询身高大于的男生的学号和姓名；答: select SNO,SNAME from Students where HEIGHT（2）查询计算机系秋季所开课程的课程号和学分数；精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 2 页，共 12 页 - - - - - - - - - - （3）答：（4）select CNO,CREDIT from Courses where

6、 SEMESTER= 秋季 （5）查询选修计算机系秋季所开课程的男生的姓名、课程号、学分数、成绩；（6）答：（7）select , from students s （8）inner join SC on = （9）inner jpin Courses c on =c,CNO （10）where = 计算机系 and = 男and = 秋季 （11）查询至少选修一门电机系课程的女生的姓名（假设电机系课程的课程号以EE开头） ；（12）答：（13）select distinct from Students s,sc where = and = 女 and like EE% （14）查询每位学生已选

7、修课程的门数和总平均成绩；（15）答：（16）select count as 课程门数 ,avg as 总平均成绩from students s （17）inner join SC on = （18）inner join Courses c on = （19）groupby（6）查询每门课程选课的学生人数,最高成绩 ,最低成绩和平均成绩；答：selectcname,count(cno),max(grade),min(grade),avg(grade) fromstudentsnaturaljoinscnaturaljoincourses groupbychane;（7）查询所有课程的成绩都在8

8、0 分以上的学生的姓名、学号、且按学号升序排列；答：selectsname,snofromstudentsnaturaljoin(select(grade)as mini fromscgroupbysno)wheremini80orderby(sno);（8）查询缺成绩的学生的姓名，缺成绩的课程号及其学分数；答：select sname,credit 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 3 页，共 12 页 - - - - - - - - - - from students,courses,s

9、cwhere = and = and grade is null;（9）查询有一门以上 (含一门 )三个学分以上课程的成绩低于70 分的学生的姓名；（10）答：（11）select sname from students,courses,sc （12）where = and = and credit=3 and grade70;（10）查询 1984 年1986年出生的学生的姓名 ,总平均成绩及已修学分数。答：select sname,avg(grade),sum(credit) from students natural join sc natural join courses where

10、bdate between 1984-00-00and 1987-00-00groud by sname（11） 在 STUDENT 和 SC关系中，删去 SNO以01开关的所有记录。答：delete sc where SNO like %01%delete Students where SNO like %01%（12）在关系中增加以下记录： 答：insert into students values insert into students values （13）将课程 CS-221的学分数增为，讲课时数增为（14）答：（15）update courses set credit=3 whe

11、re cno= CS-211（16）update courses set 1hour=60 where cno= CS-2113补充题：(1) 统计各系的男生和女生的人数。答: 精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 4 页，共 12 页 - - - - - - - - - - select department,sum(case wgen sex=男 then 1 else 0 end),sum(case wgen sex= 女 then 1 else 0 end),count(sno) fro

12、m students group by department order by deparment;(2) 列出学习过编译原理 ， 数据库或体系结构课程，且这些课程的成绩之一在90 分以上的学生的名字。答：select snme from students natural join sc natural join courses where cname= 编辑原理 or cname= 体系结构 and grade90(3) 列出未修选电子技术课程，但选修了数字电路或数字逻辑课程的学生数。答：select count(distinci from courses, not in (select f

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

14、rom(select sname,avg(grade)as from students,sc where = group by sname, order by r desc) where rownum=1;4选做题： 对每门课增加“先修课程”的属性，用来表示某一门课程的先修课程，每门课程应可记录多于一门的先修课程。要求：1) 修改表结构的定义，应尽量避免数据冗余，建立必要的主键，外键。2) 设计并插入必要的测试数据，完成以下查询：精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 5 页，共 12 页 - -

15、 - - - - - - - - 列出有资格选修数据库课程的所有学生。(该学生已经选修过数据库课程的所有先修课，并达到合格成绩。 )注意： 须设计每个查询的测试数据，并在查询之前用INSERT 语句插入表中。实验二：数据库的安全和完整性约束实验要求 ：1采用实验一的建库脚本和数据插入脚本创建Student 数据库 ,并完成以下操作：1）新增表Credits(SNO,SumCredit,NoPass)，表示每学生已通过选修课程的合计学分数，以及不及格的课程数。答：create table Credits(SNO varchar(100),SumCredit int,NoPass int) 2）创

16、建视图 Student_Grade(Sname,Cname,Grade) ，表示学生选修课程及成绩的详细信息。答：create view Student_Grade as select , from students s inner join SC on = inner join Courses c on =2.在数据库中创建以下触发器：1）Upd_Credit要求：当在 SC表中插入一条选课成绩，自动触发Upd_Credit，完成在 Credits 表中修改该学生的合计学分数和不及格的课程数。答: create trigger Upd_Credit on SC for insert as d

17、eclare SNO varchar(100),CNO varchar(100),GRADE int,NoPass int,CREDIT int select SNO=SNO,CNO=CNO,GRADE=GRADE,NoPass=(case when GRADE3 group by 4) CAL_GPA (SNO，GPA output)要求：根据 SNO参数, 输出并显示该学生的GPA值。计算方法如下：GRADE(G) GRADEPOINT(GP)G=85 4精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第

18、 10 页，共 12 页 - - - - - - - - - - 85G=75 375G=60 260G 1GPA= ( GP*CREDIT)/ CREDIT) 答: create procedure CAL_GPA SNO varchar(100), GPA decimal output as declare SUM_CREDIT int,AVG_GRADE int,ALL_CREDIT int select SUM_CREDIT=SumCredit from Credits where SNO=SNO select AVG_GRADE=avg(case when =85 then 4 wh

19、en 85 and =75 then 3 when 75 and =60 then 2 when 60 then 1 end) from Students s inner join SC on = where =SNO and 3 group by select GPA=(AVG_GRADE*SUM_CREDIT)/ALL_CREDIT3选做题：使用其它程序设计语言编程，连接数据库并提交SQL语句，显示查询结果。要求：实现上题中的第3)小题要求，设计一个图形界面来输入查询的参数SNO ，及显示查询的结果。(如采用 VC+ ，VB等)实验四：事务的管理（选作）实验要求 ：1 采用实验一的建库脚本

20、和数据插入脚本创建Student 数据库。2 测试事务隔离级别，要求：精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 11 页，共 12 页 - - - - - - - - - - 分别设置不同的隔离级别,包括：SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;SET TRANSACTION ISOLATION LEVEL READ COMMITTED;SET TRANSACTION ISOLATION LEVEL READ ONLY;两个并发事务交错执行的程序，能分别显示每种隔离级别下，是否出现丢失更新，脏读，读值不可复现以及幻象四种情况。3备份与恢复备份数据库答: BACKUP DATABASE test TO disk = c:test 删除 sc表答: DROP TABLE SC恢复到删除之前答: USE master GO RESTORE DATABASE test_wt FROM disk = c:test_wtGO精品资料 - - - 欢迎下载 - - - - - - - - - - - 欢迎下载 名师归纳 - - - - - - - - - -第 12 页，共 12 页 - - - - - - - - - -