6.18数据库复习（2）

第4章 数据操作

1．查询SC表中的全部数据。

Select *from sc

2．查询计算机系学生的姓名·年龄。

Select sname，sage from student where sdept ＝计算机系＇

3．查询成绩在70～80分的学生的学号、课程号和成绩。

Select sno,cno,grade from sc where grade between 70 and 80

4．查询计算机系年龄在18～20岁的男学生的姓名·年龄。

Select sname,sage from student

where sdept＝ 计算机系＇and sage between 18 and 20

Select max(grade) fron sc where cno-co0

5．查询课程号为“c001”的课程的最高的分数。。

6．查询计算机系学生的最大年龄和最小年龄。

Select max（sage） 最大年龄，min（sage）最小年龄 from student Where sdept＝计算机系＇

7．统计每个系的学生人数。

Select sdept，count（＊）人数 from student

Group by sdept

8．统计每门课程的选课人数和考试最高分。

Select cno，count（＊）选课人数，max（grade）最高分 from sc

Group by cno

9．统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。

Select sno，count（＊）选课门数，sum（grade） from sc

Group by sno order by count(*)asc

10．查询总成绩超过200分的学生，要求列出学号、总成绩。

Select sno，sum（grade） 总成绩 from sc

Group by sno having sum(grade)> 200

 

11．查询选修“C002”号课程的学生的姓名·所在系。

Select sname,sdept from student s join sc on s.sno=sc.sno Where cno='C002'

 

12．查询成绩80分以上的学生的姓名。课程号和成绩，并按成绩降序排列结果。Select sname,cno,grade

From student s join sc on s.sno= sc.sno

Where grade > 80

Order by grade desc

 

13．查询哪些课程没有学生选修，要求列出课程号和课程名。

Select c.cno,cname from course c left join sc on c.cno=sc.cno Where sc.cno is null

14．查询与Java在同一学期开设的课程的课程名和开课学期。

Select cl.cname, c1.semester from course c1

join course c2 on c1.semester = c2.semester

Where c2.cname = 'Java' and c1.cname != 'Java'

15．查询与李勇年龄相同的学生的姓名。所在系和年龄。

Select s1.sname, s1.sdept,s1.sage from student s1

join student s2 on s1.sage = s2.sage

Where s2．sname＝ ＇李勇＇and s1．sname ！＝“李勇＇

16．用子查询实现如下查询：

（1）查询选修了“C001”号课程的学生的姓名·所在系。

Select sname,sdept from student

Where sno in (select sno from sc where cno = 'C001')

（2）查询数学系成绩80分以上的学生的学号、姓名。课程号和成绩。

Select sno,sname,cno, grade from student s

Join sc on s.sno = sc.sno

Where sdept ＝＇数学系＇ and sno in（

Select sno from sc where grade > 80)

（3）查询计算机系考试成绩最高的学生的姓名。

·elect sname from student s join sc on s.sno = sc.sno Where sdept ＝ ＇计算机系＇and grade＝（

Select max(grade) from sc join student s on s.sno=sc.sno

 

Where sdept ＝“计算机系＇）

（4）查询年龄最大的男学生的姓名·年龄。

Select sname,sage from student

Where sage = (select max(sage) from student

where ssex＝男＇）

and ssex＝男＇

（5）查询数据结构考试成绩最高的学生的姓名。所在系、性别和成绩。

Select sname, sdept,ssex,grade from student s

Join sc on s.sno = sc.sno

Join course c on c.cno=sc.cno

Where cname＝数据结构＇

And grade in(

Select max(grade) from sc join course c on c.cno=sc.cno

Where cname＝数据结构＇）

17．查询没有选修Java课程的学生的姓名·所在系。

Select sname,sdept from student

Where sno not in(

Select sno from sc join course c on c.cno=sc.cno

where/cname/avadocin.com

18．查询计算机系没有选课的学生的姓名·性别。

Select sname,ssex from student

Where sdept ＝计算机系＇

And sno not in(

Select sno from sc)

19．创建一个新表，表名为test＿t，其结构为（COL1，COL 2，COL 3），其中，

COL1：整型，允许空值。

COL2：字符型，长度为10，不允许空值。

COL3：字符型，长度为10，允许空值。

试写出按行插入如下数据的语句（空白处表示空值）。

COL1	COL2	COL3
	B1
1	B2	C2
2	B3

Create table test_t(

COL1 int,

COL2 char(10) not null,

COL3 char(10))

Insert into test_t(COL2) values('B1')

Insert into test_t(COL1,COL2) values(1,'B2')

Insert into test_t values(2,'B3',NULL)

20．删除考试成绩低于50分的学生的选课记录。

Delete from sc where grade <50

21．删除没人选的课程的基本信息。

Delete from course where cno not in(

Select cno from course)

22．删除计算机系Java成绩不及格学生的Java选课记录。

Delete from sc

From sc join student s on s.sno = sc.sno

Join course c on c.cno = sc.cno

where cname = 'Java' and grade < 60

and sdept＝计算机系

23．将第2学期开设的所有课程的学分增加2分。。

Update Course set credit= credit +2

Where semester=2

24．将Java课程的学分改为3分。

Update course set credit= 3

where cname = 'Java'

25．将计算机系学生的年龄增加1岁。

Update Student set sage = sage+1

where sdept＝计算机系＇

26．将信息系学生的“计算机文化学”课程的考试成绩加5分。

Update SC set grade = grade +5

From student s join sc on s.sno = sc.sno

Join course c on c.cno=sc.cno

Where sdept＝ ＇信息系＇and cname＝计算机文化学＇

 

27．查询每个系年龄大于等于20的学生人数，并将结果保存到一个新永久表Dept＿Age中。Select sdept，count（＊）人数 into Dept＿Age from student

Where sage >=20

Group by sdept

 

第5章 索引和视图

 

3．使用第3章建立的Student、Course和SC表，写出创建满足下述要求的视图的SQL语句。

（1）查询学生的学号、姓名。所在系、课程号、课程名、课程学分。

Create view v1 As

Select s.sno,sname,sdept,c.cno,cname,credit

From student s join sc on s.sno = sc.sno

Join course c on c.cno=sc.cno

（2）查询学生的学号、姓名。·修的课程名和考试成绩。

Create view v2 As

Select s.sno,sname,cname,grade

From student s join sc on s.sno = sc.sno

Join course c on c.cno=sc.cno

（3）统计每个学生的选课门数，要求列出学生学号和选课门数。

Create view v3 As

Select sno,count (*) as total

From sc group by sno

（4）统计每个学生的修课总学分，要求列出学生学号和总学分（说明：考试成绩大于等于60才可获得此门课程的学分）。

Create view v4 As

Select sno,sum(credit) as total_credit

From sno join course c on c.cno = sc.cno

Where grade >=60

Group by sno

（5）查询计算机系JAVA考试成绩最高的学生的学号、姓名··AVA考试成绩。

Create view v5 As

Select top 1 with ties s.sno, sname, grade

From student s join sc on s.sno = sc.sno

Join course c on c.cno=sc.cno

Where sdept ＝ ＇计算机系＇and cname ＝ ＇java＇

Order by grade desc

4．利用第3题建立的视图，完成如下查询：

（1）查询考试成绩大于等于90分的学生的姓名。课程名和成绩。

Select sname,cname,grade From v2 where grade >= 90

（2）查询选课门数超过3门的学生的学号和选课门数。

Select * from v3 where total >= 3

（3）查询计算机系选课门数超过3门的学生的姓名··课门数。

Select sname,total from v3 join student s on s.sno = v3.sno Where sdept＝计算机系＇and total ＞＝3

（4）查询修课总学分超过10分的学生的学号、姓名。所在系和修课总学分。

Select v4.sno,sname,sdept,total_credit

From v4 join student s on s.sno= v4.sno

Where total_credit >= 10

（5）查询年龄大于等于20岁的学生中，修课总学分超过10分的学生的姓名，年龄、所在系和修课总学分。

Select sname, sage, sdept,total_credit

From v4 join student s on s.sno =v4.sno

Where sage >= 20 and total_credit >= 10

5．修改第3题（4）定义的视图，使其查询每个学生的学号、总学分以及总的选课门数。Alter view v4 As

Select sno,sum(credit) as total_credit,count(*) as total_cno

From sc join course c on c.cno = sc.cno Group by sno

6．修改第3题（5）定义的视图，使其统计全体学生中JAVA考试成绩最高的学生的学号、姓

名、所在系和JAVA考试成绩。

 

Alter view v5 As

Select top 1 with ties s.sno, sname, sdept, grade

From student s join sc on s.sno= sc.sno

Join course c on c.cno=sc.cno

Where cname = 'java'

Order by grade desc

 

第6章 关系数据库规范化理论

 

6．设有关系模式：学生修课（学号，姓名，所在系，性别，课程号，课程名，学分，成绩）。设一个学生可以选多门课程，一门课程可以被多名学生选。每个学生对每门课程有唯一的考试成绩。一个学生有唯一的所在系，每门课程有唯一的课程名和学分。请指出此关系模式的候选码，判断此关系模式是第几范式的，若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。

 

答：候选码：（学号，课程号）

 

第一范式。因为有：学号→姓名，··存在部分函数依赖（学号，课程号）p→姓名

··范式关系模式：

学生（学号，姓名，所在系，性别）

课程（课程号，课程名，学分）

考试（学号，课程号，成绩），学号为引用学生的外码，课程号为引用课程的外码。

 

7．设有关系模式：学生（学号，姓名，所在系，班号，班主任，系主任），其语义为：一个学生只在一个系的一个班学习，一个系只有一个系主任，一个人只担任一个系的系主任；一个班只有一名班主任，但一名教师可以担任多个班的班主任；一个系可以有多个班。请指出此关系模式的候选码，判断此关系模式是第几范式的，若不是第三范式的，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。

答：候选码：学号

属于第二范式。

但有：学号→班号，班号→班主任，因此存在传递函数依赖：学号传递→班主任。第三范式关系模式：

学生（学号，姓名，所在系，班号），班号为引用班的外码，所在系为引用系的外码。班（班号，班主任）

系（系名，系主任）

8．设有关系模式：教师授课（课程号，课程名，学分，教师号，教师名，职称，授课时数，授课学年），其语义为：一门课程（由课程号决定）有确定的课程名和学分，每名教师（·教师号决定）有确定的教师名和职称，每门课程可以由多名教师讲授，每名教师也可以讲授多门课程，在同一学年每个教师对每门课程只讲授一次，且有确定的授课时数。指出此关系模式的候选码，判断此关系模式属于第几范式，若不属于第三范式，请将其规范化为第三范式关系模式，并指出分解后的每个关系模式的主码和外码。。

答：候选码：（课程号，授课教师号）

属于第一范式。因为有：课程号→课程名，因此存在部分函数依赖关系：（课程号，授课教师号）p→课程名

第三范式关系模式：

课程（课程号，课程名，学分）

教师（教师号，教师名，职称）

授课（课程号，教师号，授课时数，授课学年），课程号为引用课程的外码，教师号为引用教师的外码。

 

第7章 数据库保护

1．试说明事务的概念及四个特征。

答：事务的概念及四个特征为原子性、一致性、隔离性和持久性。原子性指事务的原子性是指事务是数据库的逻辑工作单位，事务中的操作，要么都做，要么都不做。一致性指定事务执行的结果必须是使数据库从一个一致性状态变到另一个一致性状态。隔离性指数据库中一个事务的执行不能被其他事务干扰。持久性指事务一旦提交，则其对数据库中数据的改变就是永久的。

 

3．并发控制的措施是什么？

答：加锁。

4．设有如下三个事务：

T1:B=A+1;T2:B=B*2;T3:A=B+1

（1）设A的初值为2，B的初值为1，如果这三个事务并发的执行，则可能的正确的执行结果有哪些?

T¡→T2→T3:A=7:B=6

T¡→Tз→T2:A=4:B=6

T₂→T¡→T3:A = 3;B=3

T₂→Tз→T:A=3 B=4

Tз→T→T:A=2 B=6

Tз→T₂→T:A=2 B=3

(2) 给出一种遵守两段锁协议的并发调度策略。

5．当某个事务对某段数据加了S锁之后，在此事务释放锁之前，其他事务可以对此段数据加什么锁？

答：S锁（共享锁）。

 

7．三级封锁协议分别是什么？各级封锁协议的主要区别是什么？每一级封锁协议能保证

什么？

答：一级封锁协议：对事务T要修改的数据加X锁，直到事务结束（包括正常结束和非正常三级封锁协议：一级封锁协议加上事务T对要读取的数据加S锁，并直到事务结束才释放。

三个封锁协议的区别是在对读数据的加锁上。

一级封锁协议可以防止丢失修改；二级封锁协议除了可以防止丢失修改外，还可以防止读“脏”数据；三级封锁协议除了可以防止丢失修改和不读“脏”数据之外，还进一步防止了不可重复读。

8．什么是可串行化调度？如何判断一个并行执行的结果是否是正确的？

答：多个事务的并发执行是正确的，当且仅当其结果与按某一顺序的串行执行的结果相同，称这种调度为可串行化的调度。

如果并发调度的执行结果和某个串行调度结果一样，这个并发调度就是正确的调度。

9．两段锁的含义是什么？

答：两段锁是指所有的事务必须分为两个阶段对数据进行加锁和解锁，具体内容如下：

·在对任何数据进行读、写操作之前，首先要获得对该数据的封锁。

·在释放一个封锁之后，事务不再申请和获得任何其他锁。。

10．数据库故障大致分为几类？

答：主要有三类：事务内部的故障；系统故障；其他故障。

 

 

第8章 数据库设计

 

8．图7-28（a）～（d）所示为某企业信息管理系统中的局部E-R图，请将这些局部E-R图合并为一个全局E-R图，并指明各实体以及联系的属性，标明联系的种类（注：为使图形简洁明了，在全局E-R图中可只画出实体和联系，属性单独用文字描述）。将合并后的E-R图转换为符合3NF要求的关系模式，并说明主码和外码。

9．将下列E-R图转换为符合3NF的关系模式，并指出每个关系模式的主码和外码。

（1）图8-27所示为描述图书、读者以及读者借阅图书的E-R图。

答：对图书、读者E-R模型，转换后的关系模式为：

图书（书号，书名，出版日期，作者）

··（读者编号，读者姓名，联系电话，所在单位）

借阅（书号，读者编号，借书日期）书号为引用图书的外码，读者编号为引用读者的外码。

(2)

厂家（厂家编号，联系地址，联系电话）

商店（商店编号，商店名，联系电话）

商品（商品编号，商品名称，库存量，商品分类），

订购（商店号，厂家编号，商品编号，订购日期，订购数量），商店号为引用商店的外码，商品编号为引用商品的外码，厂家编号为引用厂家的外码。

(3)

学生（学号，姓名，性别，专业，社团号，参加日期），社团号是引用社团的外码。社团（社团号，社团名，电话，性质)