https://blog.youkuaiyun.com/qk11201/article/details/129496562
name like'--'名字是两个字的；like'%张'名字姓张的
插入语句：insert into 表 values ('Z001','大胜教育',null,'淄博',null);
修改语句 update 表 set 售价=售价*(1.05) （目的） where 出版社 = '科学出版社'（条件）
删除语句：delete from 表 where sno = '1911102'（条件）
多表查询：
10-1 查询部分专业的学生
检索出 stu表中‘计算机工程’或‘软件工程’专业的学生的记录，结果集按学号升序排序。select stu.sno 学号,stu.sname 姓名,sex 性别, major.mname 专业
from stu join major on stu.mno=major.mno
where major.mname = '计算机工程' or major.mname = '软件工程'
order by sno asc
10-2 查询各专业学生的平均成绩
统计各专业的学生选课的平均成绩，如果某专业尚未有任何学生选修课程或成绩为空时，平均分计为0。输出结果集按照major表中的mno升序排序。
select mname 专业,ifnull(avg(grade),0)平均成绩
from major left outer join(
select mno,grade from stu,sc
where stu.sno=sc.sno)as a on major.mno=a.mno
group by major.mno
order by major.mno;
10-3 查询平均成绩高于75分的学生
在sc表 中查询平均成绩高于75分的学生。
select sno 学号,AVG(grade) 平均成绩
from sc
group by sno
having AVG(grade)>75
10-4 查询选修某两门课程的学生
检索出 sc表中至少选修了’C001’与’C002’课程的学生学号。
select a.sno 学号
from sc a right join sc b on a.sno=b.sno
where a.cno='C001' and b.cno='C002'
group by a.sno
10-5 查询S001学生选修而S003学生未选修的课程
检索出 sc表中学号为S001的学生选修的而S003学号学生未选修的课程号。
select distinct sc.cno 课程号
from sc
where cno in(
select cno
from sc
where sno = 'S001'
) and not cno in(
select cno
from sc
where sno = 'S003'
);
10-6 查询选修张老师讲授所有课程的学生
查询选修了张老师所讲授的所有课程的学生。
select sname
from stu
where sno in(
select sno
from sc
where cno in(
select cou.cno
from cou
where teacher = '张老师'
)
group by sno
having count(sno) = (
select count(cno) from cou where teacher='张老师'
));
10-7 spj-查询没有使用天津供应商生产的红色零件的工程
本题目要求编写SQL语句，在SPJ数据库中，查询没有使用天津供应商生产的红色零件的工程号jno。
select jno
from j
where jno not in
(
select jno
from spj,p,s
where (spj.sno=s.sno and s.city='天津') and (spj.pno=p.pno and p.color='红')
)
0-8 spj-查找在同一个城市的所有工程项目
本题目要求编写SQL语句，在SPJ数据库中，查找在同一个城市的所有工程项目jname，输出结果集以城市名city升序排序, 城市 名相同的再以工程项目jname升序排序。
select city,jname
from j
where city in(select
city from j group by city
having count(city)>1)
order by city asc,jname asc
10-16 查询选修人数超过2人且成绩都在60分以上的课程
本题目要求编写SQL语句，查询选修人数超过2人且成绩都在60分以上的课程的课程名、最高成绩、最低成绩和平均成绩。
select sc.cno 课程号,cname 课程名,max(grade) 最高成绩,min(grade) 最低成绩, avg(grade) 平均成绩
from sc,cou
where sc.cno=cou.cno and not exists
(select cno
from sc
where sc.cno=cou.cno and (grade is null or grade<60))
GROUP BY sc.cno
having count(sno)>=2
10-17 检索所有选修了40008课的学生学号和成绩，查询结果按学号升序排列。
检索所有选修了40008课的学生学号和成绩，查询结果按学号升序排列。
select distinct sno 学号,grade 成绩
from score
where cno = '40008'
10-18 找出所有姓“张”的学生学号、姓名、院部和联系电话。
找出所有姓“张”的学生学号、姓名、院部和联系电话。
select sno 学号,sname 姓名,dept 院部,phone 电话
from student
where sname like '张%';
10-19 查询所授每门课程平均成绩均在70分以上的教师(MSSQL)
查询所授每门课程的平均成绩均在70分以上( 含70分)的教师。
select teacher
from cou
left join sc on cou.cno=sc.cno
where teacher not in
(select a.teacher
from
(SELECT cou.cno,sno,cname,credit,grade,teacher
FROM cou
left join sc on cou.cno=sc.cno
GROUP BY cou.cno,sno,cname,credit,grade,teacher) a
GROUP BY cno,teacher
having avg(grade)<70)
and grade is not null
GROUP BY teacher
10-20 计算“0000001”课程的平均分、最高分和最低分
计算``sc```表中“0000001”课程的平均分、最高分和最低分，要求结果中列名分别显示“平均分”、“最高分”、“最低分”。
select avg(score) 平均分,max(score) 最高分,min(score) 最低分
from sc
where cno = '0000001';
10-21 统计各系的老师人数，并按人数升序排序
统计出``teachers```表中各系的老师人数，并按人数升序排序，要求结果中列名分别显示“系别”、“教师人数”。
select tdept 系别,count(tname) 教师人数
from teachers
group by tdept;
单表查询：
10-2 查询每种产品的名称和重量(用kg表示)
检索出product表中每种产品的名称和重量(用kg表示)。
SELECT PName,Weight_g*0.001 Weight_kg
FROM product;
10-5 查询既不是’上海’，也不是’北京’和’天津’的供货商信息
检索出supplier表中所有符合既不是’上海’，也不是’北京’和’天津’的供货商信息的记录。
SELECT Sid,SName,City
FROM supplier
WHERE City NOT IN('上海','北京','天津');
10-6 查询产品名中包含’M’且重量大于30的产品编号
检索出product表中所有符合包含’M’且重量大于30产品编号。
SELECT Pid
FROM product
WHERE PName like 'M%'AND Weight>30;
10-10 查询Product表中进货价格在50至150之间的商品记录
要求编写SQL语句，查询Product表中进货价格在50至150之间的商品记录。注意：查询结果中应包含50和150元的商品记录。
SELECT *
FROM Product
WHERE purchase_price BETWEEN 50 AND 150;
主观题：
湖北省襄阳市某银行信息系统的数据库部分关系模式如下所示：
客户（客户号，姓名，性别，地址，邮编，电话）
账户（账户号，客户号，开户支行号，余额）
支行（支行号，支行名称，城市，资产总额）
交易（交易号，账户号，业务金额，交易日期）
其中，业务金额为正值表示客户向账户存款；为负值表示取款。
请回答如下问题:
1、 创建账户关系的SQL语句，其中:账户号唯一识别一个账户，客户号为客户关系的唯一标识，且不能为空。给出a,b处的内容（4分）
Create table 账户（
账户号 char(19) a ，
客户号 char(19) b ，
开户支行号 char(19) NOT NULL ，
余额 float CHECK(余额>1.00)）;
2. 余额 float CHECK(余额>1.00)的含义是什么？（2分）
3、交易关系模式的主键是什么？（2分）
4、阅读代码，解释功能。（2分）
Create unique index index1 on 账号(客户号)
5、解释下面语句的功能（4分）。
Create view view1 as
Select distinct 客户.*
From 客户，账户，支行，交易
Where 客户. 客户号=账户. 客户号
and 交易.账户号=账户.账户号
and 支行.城市='襄阳市’
and 交易.交易日期 LIKE'2021-11-%'
and 账户.开户支行号=支行.支行号;
6、解释下面语句的功能（2分）
Grand select,update,insert,delete
On 交易
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
8、写出如何调用7中创建的PROCEDURE的SQL语句(2分）
答：
1.PrimaryKey
FroeignKey
2.限制余额字段的值必须为大于1.00的浮点数
3.交易号
4.代码的功能是在账户关系的客户号字段上创建一个唯一索引，以确保每个客户只能拥有一个账户。这可以提高查询效率和数据完整性，避免数据冗余和不一致性。当插入或更新数据时，数据库会检查索引的唯一性，如果违反了唯一性，则会抛出异常。
5.该语句的功能是创建一个名为 view1 的视图。该视图包含所有客户的信息，这些客户在某银行的信息系统中拥有账户，且在交易表中存在账户的交易记录。同时，这些客户所在的支行位于襄阳市，并且这些交易记录的交易日期在 2021 年 11 月份。该视图是通过连接客户表、账户表、支行表和交易表而创建的，并使用了 distinct 关键字来确保每个客户只出现一次。
6.该语句的功能是为交易表创建授权，赋予银行柜员对交易表进行查询、更新、插入和删除的权限。
7.代码创建了一个存储过程，名为VALIDATE，该过程接受三个参数：@USERNAME，@PASSWORD和 @LEGAL，其中 @USERNAME 和 @PASSWORD 分别代表账户名和密码，@LEGAL 代表验证结果的输出参数，如果验证成功，将输出 1，否则输出 0。该存储过程的功能是检查账户表中是否存在对应账户名和密码的记录，如果存在，则将 @LEGAL 设置为 1，否则将 @LEGAL设置为 0。
8.
DECLARE @LEGAL BIT
EXECUTE VALIDATE 'username', 'password', @LEGAL OUTPUT
IF @LEGAL = 1
BEGIN
PRINT 'Validation succeeded.'
END
ELSE
BEGIN
PRINT 'Validation failed.'
END
8-2 课程目标2：系统分析（关系代数）
分数 10
全屏浏览题目
作者 康长青
单位 湖北文理学院
3、湖北省襄阳市某银行信息系统的数据库部分关系模式如下所示：
客户（客户号，姓名，性别，地址，邮编，电话）
账户（账户号，客户号，开户支行号，余额）
支行（支行号，支行名称，城市，资产总额）
交易（交易号，账户号，业务金额，交易日期）
其中，业务金额为正值表示客户向账户存款；为负值表示取款。
请回答如下问题:
(1)、用文字解释下面关系代数的含义。(4分）
Π电话（σ姓名=‘张三’∧ 邮编=‘441100’(客户)）
Π姓名，电话(σ交易日期=’2021-11-11’ ∧业务金额<0(客户∞账户∞交易))
(2)、已知某大学在该银行发放教职工工资，员工工资表employee(ID,name,salary)，从下面代码段中任选一个，用文字解释其代码含义(4分)
(3)、已知员工工资表中10536号员工的工资为8000元，依据下面代码写出一条可以让trigger执行的的SQL语句（2分）。
-
- 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
- create trigger tr3
- creart trigger tr3
on employee
after update
as
if new. salary< old.salary
then
rollback
end
答：
1.第一个关系代数表示选择客户表中姓名为“张三”且邮编为“441100”的记录，然后从结果中投影出电话号码。
第二个关系代数表示选择交易表中交易日期为“2021-11-11”且业务金额小于0的记录，然后将其连接（join）到账户表和客户表，最终从结果中投影出姓名和电话号码。
2.SELECT employee.name, employee.salary
FROM employee
WHERE employee.salary > (SELECT AVG(salary) FROM employee)
这个SQL查询将会从员工表(employee)中选出薪资高于该表平均薪资的员工，并返回他们的姓名和工资。
3、UPDATE employee SET salary = 8000 WHERE ID = 10536
这条SQL语句会将员工工资表(employee)中员工ID为10536的工资更新为8000，如果新工资小于旧工资，则会触发触发器(tr3)中的rollback语句。
8-3 课程目标2：数据库系统分析和设计
分数 16
全屏浏览题目
作者 康长青
单位 湖北文理学院
1、数据库逻辑设计，现有如下关系模式：
Teacher(Tno, Tname, Tel, Dpartment, Bno，Bname, BorrowDate,RDate, Backup)。
字段说明：
Tno--教师编号；
Tname--教师姓名；
Tel--电话；
Dpartment--所在部门；
Bno--图书编号；
Bname--书名；
BorrowDate--借书日期和时间；
RDate--还书日期和时间；
语义如下：
每个教师有唯一的教师编号，
每本图书有唯一的图书编号，
一个教师可以借阅多本图书，
一本图书可以被多个教师多次借阅。
每个教师借每一本书有一个借书记录。
教师可以借还同一本书多次。
试回答：
（1）该关系模式设计存在什么问题？（2分）
（2）写出该关系模式的侯选码。（2分）
（3）写出该关系模式存在的函数依赖(4分)
（4）该关系模式最高满足第几范式？并说明理由。（3分）
（5）如果该关系模式不满足3NF，将该关系模式分解为满足3NF的关系模式集。（3分）
（6） 数据库逻辑设计阶段的主要任务是什么？（2分）
答：
（1）该关系模式存在冗余数据，Bno、Bname、BorrowDate、ReDate和Backup属性依赖于Tno，造成了数据的冗余存储。
（2）教师编号Tno和图书编号Bno组成侯选码。
（3）存在以下函数依赖：
Tno -> Tname, Tel, Dpartment
Bno -> Bname
(Tno, Bno) -> BorrowDate, ReDate, Backup
（4）该关系模式最高满足第二范式。因为所有非主属性都完全依赖于候选码(Tno, Bno)，并且没有部分依赖。
（5）
Teacher(Tno, Tname, Tel, Dpartment)
Book(Bno, Bname)
Borrow(Tno, Bno, BorrowDate, ReDate, Backup) 其中，Borrow是由Teacher和Book分解而来，避免了冗余存储，满足第三范式。
8-5 课程目标2:系统分析
分数 8
全屏浏览题目
作者 康长青
单位 湖北文理学院
并发控制分析（8分）,如图所示的事务调度，其中事务T1、T2仅对数据项A、B进行操作，问答以下问题：
（1）、事务T1、T2仅对数据项A、B进行操作，会发生什么，说出理由(2分)
（2）、叙述两段锁协议的内容，分析图中的调度是否满足两段锁协议？（4分）
（3）、如果要从事务T1,T2中进行回滚来解决(1)中出现的问题，从代价最小的角度考虑，应回滚事务的方案有四种:
方案1:回滚T1
方案2:回滚T2
方案3:回滚T1和T2
方案4:回滚T1或T2
，你选择那种？说明理由。(2分)
答：
（1）、事务T1首先对B进行X锁定，事务T2X读取数据项B的值，随后对B进行S锁定。事务T1尝试读取数据项B的值时被阻塞。由于事务T2X对B加了S锁，事务T1无法获得B的X锁，造成死锁。因此该事务调度会产生死锁。
（2）、
两段锁协议（2PL）是指一个事务在操作数据之前，先获取所有需要用到的锁，直到事务结束并提交后才释放所有锁。这个协议被设计用来防止事务读取到其他事务还没有提交的脏数据，同时也可以防止事务之间出现死锁。两段锁协议分为两个阶段：锁请求阶段和锁释放阶段。
在锁请求阶段，一个事务只能请求获取锁，不能释放锁。在锁释放阶段，一个事务只能释放锁，不能再请求获取锁。事务在获取锁后才能执行读写操作，同时在释放锁前不允许其他事务获取锁。只有当一个事务获取了所有需要的锁，才可以进行操作，否则等待其他事务释放锁。
从图中的调度来看，T1先对B加X锁，随后尝试读取B的值时被阻塞，等待T2X释放B的S锁。T2X先对B进行S锁，然后对A加S锁，这符合两段锁协议的要求。因此该事务调度满足两段锁协议。
（3）、四个方案的代价如下：
方案1: 回滚T1，需要回滚一次操作，代价为1。
方案2: 回滚T2，需要回滚三次操作，代价为3。
方案3: 回滚T1和T2，需要回滚四次操作，代价为4。
方案4: 回滚T1或T2，需要回滚一次或三次操作，代价为1或3。
因此，从代价最小的角度考虑，应该选择方案1或方案4。由于T1已经对B加了X锁，因此选择方案1，回滚T1，可以更快地解除死锁。
8-4 课程目标2:数据库建模与分析
分数 16
全屏浏览题目
作者 康长青
单位 湖北文理学院
某旅行社管理系统涉及的部分信息如下：
景点：景点编号、景点名称、地点、景点描述
线路：线路编号、线路名称、线路描述
导游：工号、姓名、等级
团队：团队编号、人数、开始日期、截止日期
其中：
每条线路有多个景点组成，不同线路的景点存在交叉；
每条线路有多名导游，但一名导游只负责一条线路；
每条线路可同时存在多个旅游团队，但每个团队只旅游一条线路。
(1)利用word,画图工具用贴图形式，建立一个反映上述局部应用的ER模型，要求标注联系类型,(选择一个实体画出其属性，可省略其他的实体属性，联系属性不可省略)。(10分)
(2)根据转换规则，将ER模型转换为关系模型，要求在每个关系模式后面，标注每个关系模型的主键和外键(没有标注无)(6分)。
(1)
(2)景点(景点编号、景点名称、地点、景点描述)
线路(线路编号、线路名称、线路描述)
导游（工号、姓名、等级）
团队（团队编号、人数、开始日期、截止日期）
10-21 查询选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩
本题目要求编写SQL语句，
检索出sc表、course表中选修课名中含有“数据库”三个字的课程且成绩在80～90分之间的学生学号及成绩。
提示：请使用join连接作答。
select sno,score
from sc join course on sc.cno = course.cno
where cname like'数据库%'AND score between 80 and 90
删除选修C语言课程的女生成绩记录
本题目要求编写DELET语句，删除选修’C语言’课程的女生的选课记录。
扫一扫
22万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
