【无标题】

sql代码

R10-1 添加一条学生记录

本题目要求编写 Insert语句，在stu表中添加一条学生记录：

学号：S012，姓名：周强，性别：1，其它属性为NULL.

insert into stu values('S012','周强','1',null,null,null);

R10-2 添加学生成绩记录

本题目要求编写insert语句，

在sc表中，为学号为S012的学生添加以下学生选课成绩记录。

选修C001课程，成绩为90。

选修C002课程，成绩为空。

注意：每条inser语句应以"；"结束。

insert into sc

values('S012','C001','90');

insert into sc

values('S012','C002',null);

R10-3 批量插入学生记录

本题目要求编写Insert语句，

将stu表中的软件工程专业（专业号mno为'02'）的学生记录批量插入到一个学生简表softstu表中。

insert into softstu(sno,sname)

select stu.sno,stu.sname from stu

where mno='02';

R10-4 检索部分教师并插入新表

本题目要求编写 Insert语句，

检索所授每门课程平均成绩均大于70分的教师姓名，并将检索的值插入一个新的教师表faculty表中

insert into faculty(tname)

select distinct cou.teacher from

sc inner join cou on cou.cno=sc.cno

where cou.teacher not in

(

select cou.teacher from

sc inner join cou on cou.cno=sc.cno

group by sc.cno

having avg(grade)<=70

);

R10-5 插入学生总学分表

本题目要求编写Insert语句，

计算每位同学获得的总学分，并将所有学生的总学分按学号升序排序后一起插入到totalcredit表中。

注意：

1）当某门课程成绩在60分以上时才能合计计入总学分

2）如果某学生尚未选修任何课程时，总学分计为0，并插入到totalcredit表中。

insert into totalcredit

select sno,sum(credit)

from(

select stu.sno sno,case whensc.grade>=60 then credit else 0 end credit

from stu left outer join scon stu.sno=sc.sno left outer join cou on sc.cno=cou.cno

group bystu.sno,credit,grade) a

group by sno;

R10-6 修改女生成绩

本题目要求编写UPDATE语句，

把所有低于７５分的女生成绩提高5%;

update sc,stu set grade = grade*1.05

where stu.sno=sc.sno and sc.grade<75 and stu.sex=0;

R10-7 修改学生选课成绩

本题目要求编写UPDATE语句，

在SC表中修改‘C001’课程的成绩，若成绩小于70分则提高5%，若成绩在70分以上（含70分）则提高4%

update sc

set grade = if(grade<70,grade*1.05,grade*1.04)

where cno ='C001';

R10-8 添加成绩等级rank字段

本题目要求编写UPDATE语句，

填写SC表中的成绩等级rank字段，按60分以下为E,60-69为D,70-79为C,80-89为B,90及以上为A，其它情况为NULL。

update sc

set rank=case

when sc.grade<60 then 'E'

when sc.grade>=60 andsc.grade<70 then 'D'

when sc.grade>=70 andsc.grade<80 then 'C'

when sc.grade>=80 andsc.grade<90 then 'B'

when sc.grade>=90 then 'A'

else null

end

R10-9 计算并填写学生获得的总学分

本题目要求编写UPDATE语句，

计算每位学生已获得的总学分并填写在stu表中的totalcredit字段。

其中，总学分为每个学生通过的选修课程的学分数总和，注意：只有在60分以上的选课成绩才能获得该门课程的学分数，每门课程的学分数在cou表中credit字段。

UPDATE stu,(select sno,sum(credit) SUM

from(

select stu.sno sno,case whensc.grade>=60 then credit else NULL end credit

from stu left outer join scon stu.sno=sc.sno left outer join cou on sc.cno=cou.cno

group bystu.sno,credit,grade) a

group by sno) B

SET stu.totalcredit = B.SUM

where stu.sno = B.sno

R10-10 删除成绩为空的学生选课记录

本题目要求编写DELETE语句，

在SC表中删除成绩为NULL的学生选课记录。

delete from sc where sc.grade is null;

R10-11 C1-1新增一个区域

在区域表（region）中添加一条记录：区域编号（RegionID）为5，区域描述（RegionDescription）为Center

提示：请使用INSERT语句作答。

insert into region values(5,'Center');

R10-12 C1-2新增订单统计信息

统计订单表（orders）中顾客的订单数量，并插入results表中

提示：请使用INSERT语句作答。

insert into results(CustomerID,OrderCount)

select CustomerID,count(CustomerID) from orders

group by CustomerID;

R10-13 C2-1修改订单运费

修改订单表（orders）中的运费，将每单运费（Freight）增加50%

提示：请使用UPDATE语句作答。

update orders set Freight = Freight*1.5;

R10-14 C2-2修改特定职工的订单运费

修改订单表（orders）中员工编号（EmployeeID）为3和4的员工的订单，将每单运费（Freight）减少5%

提示：请使用UPDATE语句作答。

update orders set Freight = Freight*0.95

where EmployeeID in(3,4);

R10-15 C2-3根据运费调整订单单价

对于运费（Freight）超过30.00的订单，将其相关的订单详情表（orderdetails）中产品的单价（UnitPrice）提高15%

提示：请使用UPDATE语句作答。

update orderdetails set UnitPrice = UnitPrice *1.15

where OrderID in (

select OrderID from orders

where Freight>30.00

);

R10-16 C2-4修改订货数量最少的产品单价

对于订单详情表（orderdetails）中订货数量最少的产品，将其单价下调1元

提示：请使用UPDATE语句作答。

Warning：本题可能涉及mysql中有关You can't specify target tablefor update in FROM clause的错误，需要适当调整语句写法

update orderdetails set UnitPrice=UnitPrice-1

order by Quantity asc

limit 1;

R10-17 C3-1删除特定城市的顾客信息

删除顾客表(customers)中城市（City）为London的顾客信息

提示：请使用DELETE语句作答。

delete from customers

where City='London';

R10-18 C3-2删除没有下过订单的顾客信息(暂无满分答案)

删除顾客表(customers)中没有下过订单的顾客信息

提示：请使用DELETE语句作答。

delete from customers

where CustomerID not in

(

select CustomerID

from orders

);

R10-19 查询选修‘C语言’课程的学生（MSSQL）

本题目要求编写SQL语句，

检索出所有选修'C语言'课程的学生成绩记录，输出结果集按照成绩降序排序。

提示：MSSQLServer 评测SQL语句。

分别尝试用以下几种方式实现。

1）采用连接查询

2）采用嵌套查询

3）采用EXIST查询

SELECT

stu.sname 姓名,

sc.grade 成绩

FROM

stu,

sc

WHERE

stu.sno = sc.sno

AND cno = ( SELECT cno FROM couWHERE cname = N'C语言')

ORDER BY

grade DESC;

--EXIST查询

SELECT

stu.sname 姓名,

sc.grade 成绩

FROM

stu,

sc

WHERE

stu.sno = sc.sno

AND EXISTS ( SELECT * FROM couWHERE sc.cno = cno AND cname = N'C语言' )

ORDER BY

grade DESC;

R10-20 删除Product数据表指定商品记录

要求编写SQL语句， 删除Product表中销售价格大于等于100的商品信息。

delete from Product

where sale_price>=100;

Else

1.现有如下关系：

S(SNO,SNAME,AGE,SEX,SDEPT)

SC(SNO,CNO,GRADE)

C(CNO,CNAME,CDEPT,TNAME)

下面用关系代数表达式表达每个查询语句。关系代数表达式使用的符号：σπ×∞÷∪∩－∧∨≠（复制粘贴使用）

LIU老师所教授课程的课程号，课程名。

π CNO, CNAME (σ TNAME = 'LIU' (C))

2.现有如下关系：

S(SNO,SNAME,AGE,SEX,SDEPT)

SC(SNO,CNO,GRADE)

C(CNO,CNAME,CDEPT,TNAME)

下面用关系代数表达式表达每个查询语句。关系代数表达式使用的符号：σπ×∞÷∪∩－∧∨≠（复制粘贴使用）

查询年龄大于23岁的男学生的学号和姓名。

π SNO, SNAME (σ AGE > 23 ∧ SEX = 'M' (S))

3.下面是一个电子商务网站购物模式，包括了三个表（主码用下划线标出）。

客户表：Client (Cno, Cname, Csex, Cage, Cclass),各属性分别代表顾客号（char(10)）、姓名、性别、年龄及客户类别。

商品表：Goods (Gno, Gname, Gprice) , 各属性分别代表商品号(char(5))、名称及价格。

购物表：CG：（Cno, Gno, QTY ）, 各属性分别代表顾客号、商品号及购物数量。

请用关系代数完成下面的操作：

1、查找价格在900-1000元之间的商品名称。

πGname(σGprice>=900∧Gprice<=1000(Goods))

2.查询购买了“长虹彩电”的顾客号码。

10-1 查询学生表所有学生记录

本题目要求编写SQL语句，

检索出stu表中所有学生记录。

select * from stu

10-2 查询每个仓库的编号及其获得的订单数量

本题目要求编写SQL语句，

查询每个仓库的编号及其获得的订单数量。

提示：请使用SELECT语句作答。

select Wno,count(Wno) cnt

from employee,orders

WHERE Wno="A01" and employee.Eid=orders.Eid

GROUP BY Wno

10-3 删除重量大于100的产品

本题目要求编写SQL语句，

删除重量大于100的产品。

DELETE

FROM

product

where Weight > 100

10-4 建立’A01’仓库的员工信息视图vEmp

本题目要求编写SQL语句，

建立’A01’仓库的员工信息视图vEmp。

create view vEmp

as select *

from employee

where Wno = 'A01'

10-5 查询所有员工中最高工资和最低工资

本题目要求编写SQL语句，

检索出employee表中所有员工中最高工资和最低工资。

提示：请使用SELECT语句作答。

select

max(Salary) as max_Salary ,

min(Salary) as min_Salary

from employee

10-6 查询Students表中的所有系名，要求结果中系名不重复

本题目要求编写SQL语句，

检索出students表中所有系名的记录，要求结果中系名不重复。

提示：请使用SELECT语句作答。

SELECT DISTINCT sdept

FROM students

10-7 查询所有姓“刘”的学生信息

本题目要求编写SQL语句，

检索出students表中所有姓“刘”的学生信息。

提示：请使用SELECT语句作答。

SELECT*

FROM

students

WHERE

sname LIKE '刘%';

10-8 统计不及格人数超过3人的课程号和人数

本题目要求编写SQL语句，

统计出``sc```表中不及格人数超过3人的课程号和人数，要求结果中列名分别显示“课程号”、“不及格人数”。

提示：请使用SELECT语句作答。

select cno as 课程号,count(*) as 不及格人数

from sc

where score<60

group by cno

having count(*)>3;

10-9 向订单表中插入一条新记录

向订单表中插入一条新记录，订单编号是10331，顾客编号是J101，员工编号是023。

INSERT INTO

订单(订单编号,顾客编号,员工编号)

VALUES

('10331','J101','023');

10-10 统计每个学生的选课门数和考试总成绩，并按选课门数升序排列

本题目要求编写SQL语句，

统计``sc```表中，每个学生的选课门数和考试总成绩，并按选课门数升序排列，要求结果中列名分别显示"学号“、“选课门数”、“考试总成绩”。

提示：请使用SELECT语句作答。

select sno as 学号,count(*)as 选课门数,sum(score) as 考试总成绩

from sc

group by sno

10-11 将员工表中编号为133的员工的性别修改为“男”

将员工表中编号为133的员工的性别修改为“男”

update 员工

set 性别 = '男' where 员工编号 = 133;

10-12 删除选修C语言课程的女生成绩记录

本题目要求编写DELETE语句，删除选修’C语言’课程的女生的选课记录。

DELETE

FROM sc

WHERE sno IN(SELECT sno

FROM stu

WHERE sex=0) AND

cno IN(SELECT cno

FROM cou

WHERE cname="C语言" );

10-13 将student表中的数计学院的学生信息插入到stu表中。

将student表中的数计学院的学生信息插入到stu表中。

insert

into stu

select *

from student

where dept='数计学院'

10-14 查询商品表，先按商品分类升序排列，再按商品价格降序排列

本题目要求编写SQL语句，查询sh_goods表，先按商品分类category_id升序排列，对于相同分类的商品再按商品价格price降序排列 。

提示：请使用SELECT语句作答。

select name,category_id,price

from sh_goods

order by category_id asc,price desc

10-15 检索没被学生选修的课程编号和课程名称。

检索没被学生选修的课程编号和课程名称。

提示：请使用SELECT语句作答。

select distinct cno,cname

from course

where cno not in (select cno from score)

主观题

8-1 基本表的建立

学生数据库有三个关系模式：学生表S（sno，sname，age）、课程表C（cno，cname，gredit）、选课表SC（sno，cno，score）。SC表中sno和cno联合作为主键，类型都是char（8），分别引用S的sno和C的cno；score是分数，类型为int，要求0<=score<=100。

1、用SQL语言写出创建SC表的语句。

create table SC

(sno char(8),

cno char(8),

score int,check（score>'0'and score<='100'),

primary key(Sno,Cno),

foreign key(sno)references S(sno),

foreign key(cno)references C(cno)

);

2、用SQL语言在SC表的score属性上建立一个普通升序索引。

CREATE INDEX idx_score ON SC(score ASC);

3、在S表上有一个触发事件为update或delete的触发器，请写出一条可触发该触发器执行的语句。

CREATE TRIGGER delete_sc_trigger

AFTER DELETE ON S

FOR EACH ROW

BEGIN

DELETE FROM SC WHERE sno =OLD.sno;

END;

---这个触发器会在S表中进行删除操作后执行，针对每一行旧的记录，删除SC表中对应该学生的选课记录。--

8-2 模式规范化

有关系模式R(A，B，C，D，E)，函数依赖集F={A->C，B->D，B->G，D->E}。

1、判断该关系模式最高属于第几范式，并说明理由。

2、按规范的分解方法将该关系模式分解为3NF，要求写出分解后每个模式的码和函数依赖集。

1、该关系模式最高属于第三范式（3NF）。

理由如下：

该关系模式不存在非主属性对主属性的传递依赖关系。

该关系模式不存在主属性对主属性的部分依赖关系。

2、按规范的分解方法将该关系模式分解为3NF：

步骤1：根据函数依赖集 F，得到关系模式R1(A, C)，R2(B, D, E)，R3(B, G)。

步骤2：检查每个新模式的主属性是否是码，如果不是，则将其加入到码中。

R1 的主属性是 A 和 C，都不是码，因此 R1 的新码为AC。

R2 的主属性是 B 和 D，其中 B 是码，因此 R2 的新码为 BDE。

R3 的主属性是 B 和 G，其中 B 是码，因此 R3 的新码为 BG。

步骤3：检查每个新模式中的非主属性是否只依赖于该模式的主属性，如果不是，则将其移到新的关系模式中。

在 R1 中，C 仅依赖于主属性 A，因此 R1 满足 3NF。

在 R2 中，E 依赖于主属性 D，因此将 E 移到新的关系模式 R4(D, E) 中。

在 R3 中，G 仅依赖于主属性 B，因此 R3 满足 3NF。

步骤4：检查每个新模式是否符合 BCNF，如果不符合，则进一步分解。

R2 的新模式 R5(B, D) 不符合 BCNF，因为 B->D，B 不是 R5 的码，因此需要进一步分解。分解为 R5(B, D) 和 R6(B, E)。

最终分解得到以下三个关系模式：

R1(A, C)

R5(B, D)

R4(D, E)

其中，R1、R5、R4 都符合 3NF 和 BCNF。

8-3 数据库建模

有下面的数据库信息：

图书信息包括：书号、书名、作者、数量、出版社、单价、架号。出版社信息包括：出版社号、社名、地址、电话、邮编、信箱。读者信息包括：借书证号、姓名、单位。一个出版社可以出版多种书籍，但每本书只能在一个出版社出版，应有出版日期和责任编辑。每个读者可以借阅多本图书，每本图书可以有多人借阅。借阅信息包括：借书日期、还书日期、是否续借。

1、根据以上信息，画出ER图。可省略属性，但是联系的属性不能省略。

2、根据ER图转换为关系模式的一般原则，将上面的ER图转换为关系模式，并注明码和外码。

2、根据ER图，可以得到如下关系模式：

书籍信息表 Book（book_id, book_name, author, quantity,publisher_id, price, location, publish_date, editor）

book_id: 书号，主键

publisher_id: 出版社号，外键（参考出版社信息表）

出版社信息表 Publisher（publisher_id, publisher_name,address, phone, zipcode, email）

publisher_id: 出版社号，主键

读者信息表 Reader（card_id, reader_name, unit）

card_id: 借书证号，主键

借阅信息表 Borrow（card_id, book_id, borrow_date,return_date, renew）

card_id: 借书证号，外键（参考读者信息表）

book_id: 书号，外键（参考书籍信息表）

其中，Book和Publisher之间的关系为一对多关系，一个出版社可以出版多种书籍，但每本书只能在一个出版社出版；Reader和Borrow之间的关系为一对多关系，每个读者可以借阅多本图书，每本图书可以有多人借阅。

ER图绘制

某旅行社管理系统涉及的部分信息如下：

景点：景点编号、景点名称、地点、景点描述

线路：线路编号、线路名称、线路描述

导游：工号、姓名、等级

团队：团队编号、人数、开始日期、截止日期

其中：每条线路有多个景点组成，不同线路的景点存在交叉；每条线路有多名导游，但一名导游只负责一条线路；每条线路可同时存在多个旅游团队，但每个团队只旅游一条线路。

请根据需求分析情况回答下面的问题。

1、对该数据库进行概念结构设计（ER图）时应该有 30.1 个实体，有 30.2 个联系。

2、实体景点、导游、团队对应的属性个数分别是 30.3 、 30.4 、 30.5 。

3、线路和导游之间的联系是30.6 ；线路和景点之间的浏览联系是 30.7 ；线路和团队之间的联系是 30.8 。

4、根据ER分析结果，至少应生成 30.9 个关系模式。其中导游关系模式的主码是 30.10 ，外码是 30.11 ；浏览联系模式的主码由 30.12 个属性共同构成。其中 30.13 和 30.14 分别是外码。

答案：

30.1: 4

30.2: 3

30.3: 4

30.4: 3

30.5: 2

30.6: 一对多

30.7: 多对多

30.8: 一对多

30.9: 5

30.10: 工号

30.11: 线路编号

30.12: 2

30.13: 线路编号

30.14: 景点编号

根据转换规则，将ER模型转换为关系模型，要求在每个关系模式后面，用文字标注每个关系模型的主键和外键(没有标注无)(6分)。

线路(Line)：线路编号（主键）、线路名称、线路描述

景点(Spot)：景点编号（主键）、景点名称、地点、景点描述

线路-景点关系(Line_Spot)：线路编号（外键）、景点编号（外键）

导游(Guide)：工号（主键）、姓名、等级、线路编号（外键）

团队(Group)：团队编号（主键）、人数、开始日期、截止日期、线路编号（外键）

其中，Line_Spot为一个关系模式，其主键由线路编号和景点编号组成，同时也是对应关系的外键。Guide和Group表都有线路编号作为外键，与Line表建立关联。

数据库基础知识应用

湖北省襄阳市某银行信息系统的数据库部分关系模式如下所示：

客户（客户号，姓名，性别，地址，邮编，电话）

账户（账户号，客户号，开户支行号，余额）

支行（支行号，支行名称，城市，资产总额）

交易（交易号，账户号，业务金额，交易日期）

其中，业务金额为正值表示客户向账户存款；为负值表示取款。

请回答如下问题:

1、创建账户关系的SQL语句，其中:账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。账户余额不能小于1.00元，给出a,b处的内容（4分）

Create table 账户（

账户号 char(19) a

客户号 char(19) b

开户支行号char(19) NOT NULL ，

余额 float CHECK(余额>1.00)）;

CREATE TABLE 账户(

账户号 CHAR(19) PRIMARY KEY,

客户号 CHAR(19) NOT NULL,

开户支行号 CHAR(19) NOT NULL,

余额 FLOAT CHECK (余额 >= 1.00)

);

2. 余额 float CHECK(余额>1.00)的文字含义是什么？（2分）

余额 FLOAT CHECK (余额 >= 1.00) 的文字含义是，限制余额字段的值必须为大于等于1.00的浮点数。

3、交易关系模式的主键是什么？（2分）

主键是交易号。

4、阅读代码，用文字解释功能。（2分）

Create unique index index1 on 账号(客户号)

代码的功能是在账户关系的客户号字段上创建一个唯一索引，以确保每个客户只能拥有一个账户。这可以提高查询效率和数据完整性，避免数据冗余和不一致性。当插入或更新数据时，数据库会检查索引的唯一性，如果违反了唯一性，则会抛出异常。

5、用文字解释下面语句的功能（2分）。

Create view view1 as

Select distinct 客户.*

From 客户，账户，支行，交易

Where 客户. 客户号=账户. 客户号 and 交易.账户号=账户.账户号 and 支行.城市='襄阳市’and 交易.交易日期LIKE'2021-11-%'and 账户.开户支行号=支行.支行号;

该语句的功能是创建一个名为 view1 的视图。该视图包含所有客户的信息，这些客户在某银行的信息系统中拥有账户，且在交易表中存在账户的交易记录。同时，这些客户所在的支行位于襄阳市，并且这些交易记录的交易日期在 2021 年 11 月份。该视图是通过连接客户表、账户表、支行表和交易表而创建的，并使用了 distinct 关键字来确保每个客户只出现一次。

6、用文字解释下面语句的功能（2分）

Grant select,update,insert,delete

On table 交易

To 银行柜员

该语句的功能是为交易表创建授权，赋予银行柜员对交易表进行查询、更新、插入和删除的权限。

7、阅读代码，用文字解释功能 （4分）

CREATE PROCEDURE VALIDATE @USERNAME CHAR(20),@PASSWORD CHAR(20),@LEGAL BIT OUTPUT

AS

IF EXISTS(SELECT * FROM Account WHERE SNAME = @USERNAME AND PWD = @PASSWORD)

SELECT @LEGAL = 1

ELSE

SELECT @LEGAL = 0

代码创建了一个存储过程，名为VALIDATE，该过程接受三个参数：@USERNAME，@PASSWORD和 @LEGAL，其中 @USERNAME 和 @PASSWORD 分别代表账户名和密码，@LEGAL 代表验证结果的输出参数，如果验证成功，将输出 1，否则输出 0。该存储过程的功能是检查账户表中是否存在对应账户名和密码的记录，如果存在，则将 @LEGAL 设置为 1，否则将 @LEGAL设置为 0。

8、写出程序员调用上面的PROCEDURE的代码(2分）

DECLARE @LEGAL BIT

EXECUTE VALIDATE 'username', 'password', @LEGAL OUTPUT

IF @LEGAL = 1

BEGIN

PRINT 'Validation succeeded.'

END

ELSE

BEGIN

PRINT 'Validation failed.'

END

规范化分析

1、数据库逻辑设计，现有如下关系模式： Teacher(Tno, Tname, Tel, Dpartment, Bno，Bname,BorrowDate,ReDate, Backup)。

字段说明： Tno--教师编号；Tname--教师姓名； Tel--电话；Dpartment--所在部门； Bno--图书编号；Bname--书名； BorrowDate--借书日期；ReDate--还书日期； Backup--备注； 语义如下： 每个教师有唯一的教师编号， 每本图书有唯一的图书编号，一个教师可以借阅多本图书， 一本图书可以被多个教师多次借阅。 试回答：

（1）该关系模式设计存在什么问题？（2分）

该关系模式存在冗余数据，Bno、Bname、BorrowDate、ReDate和Backup属性依赖于Tno，造成了数据的冗余存储。

（2）写出该关系模式的侯选码。（2分）

教师编号Tno和图书编号Bno组成侯选码。

（3）写出该关系模式存在的函数依赖(4分)

存在以下函数依赖：

• Tno -> Tname, Tel, Dpartment

• Bno -> Bname

• (Tno, Bno) -> BorrowDate, ReDate, Backup

（4）该关系模式最高满足第几范式？并说明理由。（3分）

该关系模式最高满足第二范式。因为所有非主属性都完全依赖于候选码(Tno, Bno)，并且没有部分依赖。

（5）如果该关系模式不满足3NF，将该关系模式分解为满足3NF的关系模式集。（5分）

将该关系模式分解为满足3NF的关系模式集如下：

• Teacher(Tno, Tname, Tel, Dpartment)

• Book(Bno, Bname)

• Borrow(Tno, Bno, BorrowDate, ReDate, Backup) 其中，Borrow是由Teacher和Book分解而来，避免了冗余存储，满足第三范式。

封锁协议

如图所示的事务调度，其中事务T1、T2仅对数据项A、B进行操作，问答以下问题：

T1	T2X
Xlock(B)	Title
read(B)	Text
B=B-50
write(B)
	Slock(A)
	read(A)
	Slock(B)
Xlock(A)

（1）、事务T1、T2仅对数据项A、B进行操作，会发生什么，说出理由(2分)

事务T1首先对B进行X锁定，事务T2X读取数据项B的值，随后对B进行S锁定。事务T1尝试读取数据项B的值时被阻塞。由于事务T2X对B加了S锁，事务T1无法获得B的X锁，造成死锁。因此该事务调度会产生死锁。

（2）、叙述两段锁协议的内容，分析图中的调度是否满足两段锁协议？（4分）

两段锁协议（2PL）是指一个事务在操作数据之前，先获取所有需要用到的锁，直到事务结束并提交后才释放所有锁。这个协议被设计用来防止事务读取到其他事务还没有提交的脏数据，同时也可以防止事务之间出现死锁。两段锁协议分为两个阶段：锁请求阶段和锁释放阶段。

在锁请求阶段，一个事务只能请求获取锁，不能释放锁。在锁释放阶段，一个事务只能释放锁，不能再请求获取锁。事务在获取锁后才能执行读写操作，同时在释放锁前不允许其他事务获取锁。只有当一个事务获取了所有需要的锁，才可以进行操作，否则等待其他事务释放锁。

从图中的调度来看，T1先对B加X锁，随后尝试读取B的值时被阻塞，等待T2X释放B的S锁。T2X先对B进行S锁，然后对A加S锁，这符合两段锁协议的要求。因此该事务调度满足两段锁协议。

（3）、如果要从事务T1,T2中进行回滚以解除死锁，从代价最小的角度考虑，应回滚事务的方案有四种:

方案1:回滚T1

方案2:回滚T2

方案3:回滚T1和T2

方案4:回滚T1或T2

你选择那种？说明理由。(2分)

四个方案的代价如下：

方案1: 回滚T1，需要回滚一次操作，代价为1。

方案2: 回滚T2，需要回滚三次操作，代价为3。

方案3: 回滚T1和T2，需要回滚四次操作，代价为4。

方案4: 回滚T1或T2，需要回滚一次或三次操作，代价为1或3。

因此，从代价最小的角度考虑，应该选择方案1或方案4。由于T1已经对B加了X锁，因此选择方案1，回滚T1，可以更快地解除死锁。

湖北省襄阳市某银行信息系统的数据库部分关系模式如下所示：

客户（客户号，姓名，性别，地址，邮编，电话）

账户（账户号，客户号，开户支行号，余额）

支行（支行号，支行名称，城市，资产总额）

交易（交易号，账户号，业务金额，交易日期）

其中，业务金额为正值表示客户向账户存款；为负值表示取款。

请回答如下问题:

（1）、用文字解释下面关系代数的含义。(4分）

Π电话（σ姓名=‘张三’∧ 邮编=‘441100’(客户)）

Π姓名，电话(σ交易日期=’2021-11-11’∧业务金额<0(客户∞账户∞交易))

• 第一个关系代数表示选择客户表中姓名为“张三”且邮编为“441100”的记录，然后从结果中投影出电话号码。

• 第二个关系代数表示选择交易表中交易日期为“2021-11-11”且业务金额小于0的记录，然后将其连接（join）到账户表和客户表，最终从结果中投影出姓名和电话号码。

（2）、已知某大学在该银行发放教职工工资，员工工资表employee(ID,name,salary)，从下面段代码中任选一个，用文字解释其代码含义(4分).

SELECT employee.name, employee.salary

FROM employee

WHERE employee.salary > (SELECT AVG(salary) FROM employee)

这个SQL查询将会从员工表(employee)中选出薪资高于该表平均薪资的员工，并返回他们的姓名和工资。

（3）已知员工工资表中10536号员工的工资为8000元，依据下面代码写出一条可以让trigger执行的的SQL语句（2分）。

UPDATE employee SET salary = 8000 WHERE ID = 10536

这条SQL语句会将员工工资表(employee)中员工ID为10536的工资更新为8000，如果新工资小于旧工资，则会触发触发器(tr3)中的rollback语句。

1. create trigger tr3

on employee

after update

as

declare @new_salary float,@old_salary float

select @old_salary=salary from deleted

select @new_salary=salary from inserted

if @new_salary-@old_salary<0

begin

rollback

end

2. creart trigger tr3

on employee

after update

as

if new. salary< old.salary

Then

rollback

end

10-1 查询图书表中全部图书的最低售价

本题目要求编写SQL语句，查询图书表中全部图书的最低售价。

提示：请使用SELECT语句作答。

select min(售价)

as 最低售价

from 图书;

10-2 查询图书表中的图书的条形码和书名，要求结果按条形码升序排序

本题目要求编写SQL语句，查询图书表中的图书的条形码和书名，要求结果按条形码升序排序

提示：请使用SELECT语句作答。

select 条形码,书名

from 图书

order by 条形码;

10-3 查询图书表中全部图书的平均售价

本题目要求编写SQL语句，查询图书表中全部图书的平均售价。

提示：请使用SELECT语句作答。

select avg(售价)

as 平均售价

from 图书;

10-4 查询图书表中全部图书的最高售价、最低售价和平均售价

本题目要求编写SQL语句，查询图书表中全部图书的最高售价、最低售价和平均售价。

提示：请使用SELECT语句作答。

select

max(售价) as 最高售价,

min(售价) as 最低售价,

avg(售价) as 平均售价

from 图书;

10-5 查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列

本题目要求编写SQL语句，查询图书的条形码，书名，出版社和出版日期，要求结果按出版社升序排列，出版社相同的数据按出版日期降序排列

提示：请使用SELECT语句作答。

select 条形码,书名,出版社,出版日期

from 图书

order by 出版社,出版日期 desc;

10-6 查询员工表全部信息

查询员工表全部信息。

select 员工编号,姓名,性别,出生日期,入职日期,电话

from 员工;

10-7 查询学生表所有学生记录

本题目要求编写SQL语句，

检索出stu表中所有学生记录。

select *

from stu;

10-8 查询学生表中的女生信息

本题目要求编写SQL语句，

检索出stu 表中所有的女生记录。注意：sex为1时表示 男生，sex为0时表示女生。

select sno as 学号,

sname as 姓名

from stu

where sex=0

10-9 查询学生表中部分信息

本题目要求编写SQL语句，

检索出stu 表中学生的学号、姓名、性别.

注意：性别为'1'时显示‘男’，性别为‘0’时显示 ‘女’。

select sno,sname,(case when sex =1 then '男' else '女' end ) as sex

from stu;

10-10 查询xsda表中所有女生的记录

请编写SQL语句，检索出xsda表中所有符合性别为”女”的记录。

select *

from xsda

where 性别='女';

10-11 查询xsda表中的学号、姓名、性别三项信息，结果按照女生优先的顺序显示。

查询xsda表中的学号、姓名、性别三项信息，结果按照女生优先的顺序显示。

select 学号,姓名,性别

from xsda

order by 性别;

10-12 查询zgda表中所有女教授的信息

查询zgda表中所有女教授的信息。

select *

from zgda

where 职称='教授' and 性别='女';

10-13 查询学生表中大于19岁的女生

本题目要求编写SQL语句，

检索出stu表中所有年龄大于19岁的女生的记录。注意：sex为1时表示 男生，sex为0时表示女生。

计算年龄时以年计算，不考虑出生月份。

提示：假定当前日期为‘2020-03-01'

select sno 学号 ,sname 姓名,sex 性别, mno 专业,(year('2020-03-01')-year(birdate))as 年龄,memo 备注

from stu

where sex = 0 and year('2020-03-01')-year(birdate)>19;

10-14 查询平均成绩高于75分的学生

本题目要求编写SQL语句，

在sc表 中查询平均成绩高于75分的学生。

select sc.sno as 学号,avg(grade)

as 平均成绩

from sc

group by sno

having avg(grade)>75

order by 学号;

10-15 查询选修‘C语言’课程的学生

检索出所有选修'C语言'课程的学生成绩记录，输出结果集按照成绩降序排序。

分别尝试用以下几种方式实现。

1）采用连接查询

2）采用嵌套查询

3）采用EXIST查询

SELECT sname as 姓名,gradeas 成绩

FROM stu,cou,sc

WHERE cname='C语言'AND sc.sno=stu.sno AND sc.cno=cou.cno

ORDER BY grade DESC

10-16 查询平均成绩以上的课程

本题目要求编写SQL语句，

查询每个同学超过他选修的平均成绩的课程。

请使用：

1）用相关子查询实现

2）使用派生表实现。

SELECT a.sno as 学号,cou.cnameas 课程名,a.grade as 成绩

FROM sc a,cou

WHERE grade>(SELECT AVG(grade)

FROM sc b

WHERE b.sno=a.sno) ANDa.cno=cou.cno

10-17 查询图书表中所有记录

本题目要求编写SQL语句，检索出图书表中所有记录。

提示：请使用SELECT语句作答。

select 条形码,书名,作者,出版社,出版日期,售价

from 图书;

10-18 查询图书表中李凯所著的图书，要求查询结果中包括条形码，书名，作者，出版社4列。

这本题目要求编写SQL语句，查询图书表中李凯所著图书，要求查询结果中包括条形码，书名，作者，出版社4列。

提示：请使用SELECT语句作答。

select 条形码,书名,作者,出版社

from 图书

where 作者='李凯';

10-19 查询图书表中全部图书的最高售价

本题目要求编写SQL语句，查询图书表中全部图书的最高售价。

提示：请使用SELECT语句作答。

select max(售价)

as 最高售价

from 图书;

10-20 通过三个表，查询读者借阅图书的信息

通过图书表，读者表和借阅表，查询读者的借阅情况，要求结果中包含以下字段：账号，姓名，条形码，书名，借书日期和还书日期。

select b.账号,姓名,a.条形码,书名,借书日期,还书日期

from 图书 as a,借阅 as b,读者 as c

where a.条形码=b.条形码 and b.账号=c.账号;

10-21 查询读者表中男女读者各自的人数

在读者表中，查询男女性读者各多少人。

select 性别 ,count(性别) as 人数

from 读者

group by 性别;

10-22 在读者表中查询余额最高的读者的全部信息

在读者表中查询余额最高的读者的全部信息。

提示：请使用嵌套查询作答，不能用top。

select *

from 读者

order by 余额 desc

limit 1;

10-23 在读者表中查询账户余额的最高值和最低值

在读者表中查询账户余额的最高值和最低值

select

max(余额) as 最高余额,

min(余额) as 最低余额

from 读者;

10-24 在读者表中查询账号和姓名

在读者表中查询账号和姓名。

select 账号,姓名

from 读者;

10-25 查询图书表中售价最高的图书的全部信息

查询图书表中售价最高的图书的全部信息。

提示：这是一个嵌套查询。

select *

from 图书

order by 售价 desc

limit 1;

10-27 在顾客表中查询各个城市的顾客数目

在顾客表中查询各个城市的顾客数目。

select 城市,count(*) as 顾客数

from 顾客

group by 城市;

10-28 在订单表中查询运费的平均值

在订单表中查询运费的平均值。

select avg(运费)

as 平均运费

from 订单;

10-29 在员工表中查询入职最晚的员工的编号，姓名和入职日期

在员工表中查询入职最晚的员工的编号，姓名和入职日期。

提示：这是一个嵌套查询。

select 员工编号,姓名,入职日期

from 员工

where 入职日期 = (select max(入职日期) from 员工);